当前位置：首页 > article >正文

问：数据库SQL优化实践整理？

article 2025/1/23 13:13:00

SQL语句优化是数据库性能调优的重要方面，通过合理的SQL语句优化，可以显著提升数据库查询的性能。

1. 优化WHERE子句的顺序

原理：WHERE子句中的条件执行顺序影响查询性能。应先写表连接条件，再写过滤条件；能过滤掉最大数量记录的条件应放在最后。

示例：

-- 不优化
SELECT * FROM A
JOIN B ON A.id = B.a_id
WHERE B.status = 'active' AND A.age > 30;

-- 优化
SELECT * FROM A
JOIN B ON A.id = B.a_id
WHERE A.age > 30 AND B.status = 'active';

2. 用EXISTS替代IN，用NOT EXISTS替代NOT IN

原理：EXISTS和NOT EXISTS通常比IN和NOT IN更高效，特别是在子查询返回大量数据时。

示例：

-- 不优化
SELECT * FROM A
WHERE A.id IN (SELECT B.a_id FROM B WHERE B.status = 'active');

-- 优化
SELECT * FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE B.a_id = A.id AND B.status = 'active');

3. 避免在索引列上使用计算

原理：在索引列上进行计算会导致索引失效，从而进行全表扫描。

示例：

-- 不优化
SELECT * FROM A
WHERE YEAR(A.date_column) = 2023;

-- 优化
SELECT * FROM A
WHERE A.date_column >= '2023-01-01' AND A.date_column < '2024-01-01';

4. 避免在索引列上使用IS NULL和IS NOT NULL

原理：在索引列上使用IS NULL或IS NOT NULL可能导致索引失效。

示例：

-- 不优化
SELECT * FROM A
WHERE A.column IS NOT NULL;

-- 优化
SELECT * FROM A
WHERE A.column <> '';

5. 避免全表扫描，建立索引

原理：在WHERE和ORDER BY涉及的列上建立索引，可以显著提高查询性能。

示例：

-- 不优化（无索引）
SELECT * FROM A
WHERE A.age > 30
ORDER BY A.date_column;

-- 优化（建立索引）
CREATE INDEX idx_age ON A(age);
CREATE INDEX idx_date_column ON A(date_column);
SELECT * FROM A
WHERE A.age > 30
ORDER BY A.date_column;

6. 避免在WHERE子句中对字段进行NULL值判断

原理：对字段进行NULL值判断会导致索引失效。

示例：

-- 不优化
SELECT * FROM A
WHERE A.column IS NULL;

-- 优化
SELECT * FROM A
WHERE A.column = 'some_default_value'; -- 使用默认值替代NULL

7. 避免在WHERE子句中对字段进行表达式操作

原理：在WHERE子句中对字段进行表达式操作（如加减乘除）会导致索引失效。

示例：

-- 不优化
SELECT * FROM A
WHERE A.age * 2 > 60;

-- 优化
SELECT * FROM A
WHERE A.age > 30;

8. 使用合适的连接类型

原理：根据业务需求选择合适的连接类型（INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN），避免不必要的表连接。

示例：

-- 不优化（使用不必要的LEFT JOIN）
SELECT A.*, B.*
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.status = 'active';

-- 优化（使用INNER JOIN）
SELECT A.*, B.*
FROM A
INNER JOIN B ON A.id = B.a_id
WHERE B.status = 'active';

9. 避免SELECT *，指定具体列

原理：SELECT * 会返回所有列，导致数据传输量大增，应指定具体需要的列。

示例：

-- 不优化
SELECT * FROM A;

-- 优化
SELECT A.id, A.name, A.age FROM A;

10. 使用LIMIT限制返回行数

原理：当只需要部分数据时，使用LIMIT可以显著减少数据库的负担。

示例：

-- 不优化
SELECT * FROM A
ORDER BY A.date_column;

-- 优化
SELECT * FROM A
ORDER BY A.date_column
LIMIT 10;

11. 避免子查询，尽量使用JOIN

原理：子查询通常执行效率较低，使用JOIN可以优化查询性能。

示例：

-- 不优化（使用子查询）
SELECT * FROM A
WHERE A.id IN (SELECT B.a_id FROM B WHERE B.status = 'active');

-- 优化（使用JOIN）
SELECT A.*
FROM A
JOIN B ON A.id = B.a_id
WHERE B.status = 'active';

12. 使用UNION ALL替代UNION

原理：UNION会进行去重操作，而UNION ALL不会，因此UNION ALL性能更高。

示例：

-- 不优化（使用UNION）
SELECT * FROM A
UNION
SELECT * FROM B;

-- 优化（使用UNION ALL）
SELECT * FROM A
UNION ALL
SELECT * FROM B;

13. 索引覆盖（Covering Index）

原理：创建包含所有查询字段的索引，可以避免回表查询，提高查询性能。

示例：

-- 不优化（无覆盖索引）
SELECT A.id, A.name FROM A
WHERE A.age > 30;

-- 优化（创建覆盖索引）
CREATE INDEX idx_age_name ON A(age, name);
SELECT A.id, A.name FROM A
WHERE A.age > 30;

14. 避免在WHERE子句中使用OR

原理：OR条件通常较难优化，应尽量使用IN或UNION ALL替代。

示例：

-- 不优化（使用OR）
SELECT * FROM A
WHERE A.status = 'active' OR A.status = 'pending';

-- 优化（使用IN）
SELECT * FROM A
WHERE A.status IN ('active', 'pending');

15. 避免在索引列上使用函数

原理：在索引列上使用函数会导致索引失效。

示例：

-- 不优化
SELECT * FROM A
WHERE LOWER(A.name) = 'john';

-- 优化
SELECT * FROM A
WHERE A.name = 'JOHN'; -- 预先将数据存储为大写或小写

16. 避免在ORDER BY子句中使用多列

原理：ORDER BY多列会增加排序的复杂度，应尽量减少排序的列数。

示例：

-- 不优化（多列排序）
SELECT * FROM A
ORDER BY A.column1, A.column2;

-- 优化（单列排序）
SELECT * FROM A
ORDER BY A.column1;

17. 使用合适的数据库连接池配置

原理：合理的连接池配置（如连接数、超时时间等）可以提高数据库的并发处理能力。

示例：

// 不优化（默认连接池配置）
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");

// 优化（根据实际需求调整连接池配置）
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setMaximumPoolSize(10); // 根据服务器性能调整
config.setConnectionTimeout(30000); // 设置合理的超时时间

18. 避免在WHERE子句中使用LIKE ‘%…’

原理：LIKE ‘%…’ 会导致索引失效，因为无法利用索引的前缀匹配。

示例：

-- 不优化
SELECT * FROM A
WHERE A.name LIKE '%john%';

-- 优化（使用全文索引或其他方式）
-- 创建全文索引（具体语法根据数据库类型而定）
-- 然后使用全文搜索函数（如MATCH...AGAINST）

19. 定期分析和更新统计信息

原理：数据库优化器依赖统计信息进行查询优化，定期更新统计信息可以确保优化器做出正确的决策。

批量插入

原理：批量插入比逐条插入效率更高，因为可以减少与数据库的交互次数。

示例：

-- 不优化（逐条插入）
INSERT INTO A (id, name) VALUES (1, 'Alice');
INSERT INTO A (id, name) VALUES (2, 'Bob');

-- 优化（批量插入）
INSERT INTO A (id, name) VALUES (1, 'Alice'), (2, 'Bob');

使用表的别名

原理：使用表的别名可以简化SQL语句，提高可读性，并减少解析时间。

示例：

-- 不优化（不使用别名）
SELECT A.id, B.name FROM A, B WHERE A.id = B.a_id;

-- 优化（使用别名）
SELECT a.id, b.name FROM A a, B b WHERE a.id = b.a_id;

使用临时表

原理：对于复杂的查询，可以先将中间结果存储在临时表中，然后再对临时表进行查询，这样可以减少查询的复杂度。

示例：

-- 创建临时表并插入数据
CREATE TEMPORARY TABLE temp_a AS SELECT * FROM A WHERE A.age > 30;

-- 对临时表进行查询
SELECT * FROM temp_a WHERE temp_a.name LIKE 'A%';

分区表

原理：将大表拆分成多个小表（分区），可以提高查询性能，因为每个分区可以独立地进行查询和存储。

示例：

-- 创建分区表（具体语法根据数据库类型而定）
CREATE TABLE partitioned_a (
    id INT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id, age)
)
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (30),
    PARTITION p1 VALUES LESS THAN (60),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

使用EXPLAIN分析查询计划

原理：EXPLAIN命令可以显示SQL语句的执行计划，帮助开发者了解查询是如何执行的，从而找出性能瓶颈。

示例：

-- 使用EXPLAIN分析查询计划
EXPLAIN SELECT * FROM A WHERE A.age > 30;

TIPS→笔者会在另外一篇文章当中详细介绍用法。

避免在WHERE子句中使用不等操作符（<>）

原理：使用不等操作符（<>）可能会导致索引失效，因为索引通常用于快速定位等于某个值的记录。

示例：

-- 不优化（使用不等操作符）
SELECT * FROM A WHERE A.age <> 30;

-- 优化（使用其他条件，如果可能）
SELECT * FROM A WHERE A.age > 30 OR A.age < 30;

当然，以下是对上述SQL语句优化方法的进一步补充和详细说明：

使用预编译语句（Prepared Statements）

原理：预编译语句可以提高性能并增加安全性，因为它们可以被数据库预编译并缓存，之后可以多次高效执行，同时防止SQL注入。

示例：

// 不优化（普通语句）
String query = "SELECT * FROM A WHERE A.id = " + id;
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

// 优化（预编译语句）
String query = "SELECT * FROM A WHERE A.id = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();

批量处理

原理：批量处理（如批量插入、批量更新）可以显著减少数据库通信开销，提高处理效率。

示例：

// 不优化（单条插入）
for (int i = 0; i < 1000; i++) {
    String query = "INSERT INTO A (column1) VALUES (" + i + ")";
    Statement stmt = connection.createStatement();
    stmt.executeUpdate(query);
}

// 优化（批量插入）
String query = "INSERT INTO A (column1) VALUES (?)";
PreparedStatement pstmt = connection.prepareStatement(query);
for (int i = 0; i < 1000; i++) {
    pstmt.setInt(1, i);
    pstmt.addBatch();
}
pstmt.executeBatch();

避免不必要的锁

原理：不必要的锁会导致并发性能下降，应尽量使用合适的隔离级别和锁机制。

示例：

-- 不优化（表锁）
LOCK TABLES A WRITE;
-- 执行一些操作
UNLOCK TABLES;

-- 优化（行锁或更合适的隔离级别）
START TRANSACTION;
-- 执行一些操作
COMMIT;

避免过度使用触发器和存储过程

原理：虽然触发器和存储过程可以简化业务逻辑，但过度使用会增加数据库的负担，应尽量在应用层处理业务逻辑。

示例：

-- 不优化（使用触发器）
CREATE TRIGGER before_insert_A
BEFORE INSERT ON A
FOR EACH ROW
BEGIN
    -- 复杂的业务逻辑
END;

-- 优化（在应用层处理业务逻辑）
// 在应用代码中处理业务逻辑，然后执行简单的SQL插入

定期重建索引

原理：随着时间的推移，索引可能会碎片化，定期重建索引可以保持其高效。

示例：

-- 重建索引
ALTER INDEX idx_name ON A REBUILD;

31 使用数据缓存

原理：缓存经常访问的数据可以减少数据库查询次数，提高整体性能。

示例：

// 使用缓存（如Redis）
String value = cache.get("key");
if (value == null) {
    value = database.query("SELECT column FROM A WHERE id = ?", id);
    cache.put("key", value);
}

避免大事务

原理：大事务会长时间占用数据库资源，应尽量将大事务拆分为小事务。

示例：

-- 不优化（大事务）
START TRANSACTION;
-- 执行很多操作
COMMIT;

-- 优化（拆分为小事务）
START TRANSACTION;
-- 执行一部分操作
COMMIT;

START TRANSACTION;
-- 执行另一部分操作
COMMIT;

结语
这些方法涵盖了SQL语句优化的多个方面，包括WHERE子句的优化、连接类型的选择、索引的使用、批量操作、临时表的应用等。通过合理地应用这些方法，可以显著提高数据库查询的性能。具体的优化效果可能因数据库类型、数据量、查询模式等因素而异，因此在实际应用中需要根据具体情况进行调整。

查看全文

http://www.kler.cn/a/370030.html

rocketmq基本架构

30天开发操作系统第 17 天 -- 命令行窗口

“推理”（Inference）在深度学习和机器学习的语境

sql主从同步

【面试】Java 记录一次面试过程三年工作经验

在 vscode + cmake + GNU 工具链的基础上配置 JLINK

python 相关

Android--简易计算器实现

Redis中Lua脚本的使用场景

深度学习领域如何正确地读取视频

java OOP 对象操作

关于＜a-upload-dragger＞实现选择文件夹，上传文件夹中符合要求的文件，并在所有符合要求文件上传完成后统一进行提示。这里面文件是直接上传到七牛云

利用ChatGPT完成2024年MathorCup大数据挑战赛-赛道A初赛：台风预测与分析

springMVC中的请求拦截器

【杂谈】城市规划教育的危与机

力扣中等题——顺次数

ES6 运算符的扩展

mysql 8.0.20 winx64安装配置

我的编程之旅——从新手到大神的蜕变

算法日记 11 day 二叉树

全视通惊艳亮相印度尼西亚国际医疗器械及用品展Hospital Expo

RS485、RS232、RS422的区别

如何在 Elasticsearch Ruby 客户端中使用 ES|QL Helper

游戏引擎中Static,Kinematic,Dynamic三种刚体属性

k8s常用对象简介

如何封装一个可取消的 HTTP 请求？