参考:
官方文档:优化sql语句 https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html
一、一条查询语句的执行流程
在处理查询任务时,了解查询的执行流程有助于优化其性能,如果能减少每个流程中不必要的操作,那么查询时间也会提高很多。本篇主要针对执行查询计划部分优化 。
通常来说,一条查询语句的执行过程包括以下几个步骤:
1. 客户端发送查询
客户端通过连接器(如 JDBC、ODBC 或 MySQL 原生连接器)向 MySQL 服务器发送一个 SQL 查询。
2. 解析和预处理
MySQL 服务器接收到查询后,首先对查询进行解析。解析器将查询转换成解析树,并检查语法错误。
3. 查询优化器
解析树被传递给查询优化器。优化器负责生成最优的查询执行计划。它会考虑各种可能的执行路径,并选择成本最低的一条。优化器会考虑以下因素:使用索引、表的连接顺序、子查询优化、其他优化技术(如分区修剪、索引覆盖等)。
4. 查询执行计划生成
优化器生成查询执行计划后,将其传递给查询执行引擎。
5. 查询执行引擎
执行引擎根据生成的执行计划执行查询。它负责:访问存储引擎、从表中读取数据、执行连接、过滤、排序和分组操作、生成最终的结果集。
6. 返回结果给客户端
执行引擎将结果集返回给客户端。如果结果集很大,MySQL 会逐步返回数据,以避免内存占用过多。
7. 关闭连接(如果需要)
查询执行完成后,客户端可以选择关闭连接。
二、优化特定查询
优化特定查询可以显著提高数据库的性能。以下是一些常见的查询优化方法和具体示例。
1. 优化 COUNT()
COUNT()
函数有两种主要作用:统计某列值的数量和统计行数。
COUNT(列名或者列的表达式)
:统计这个表达式有值的行数。COUNT(*)
:直接统计行数。
简单优化:
如果想统计同一列不同值的数量,建议将所有统计写在一个查询中,而不是分开多个查询。这样可以减少数据库扫描的次数,提高效率。
示例:
SELECT COUNT(IF(color = 'blue', 1, NULL)) AS blue_count,
COUNT(IF(color = 'red', 1, NULL)) AS red_count
FROM table_name;
使用近似值:
在一些业务场景中,精确的统计值并不重要,可以使用近似值来代替,减少资源消耗。
- 使用
EXPLAIN
估算行数:
通过EXPLAIN SELECT * FROM table_name;
rows
字段估算总行数。
缓存 COUNT
:
如果需要精确的统计值并且要求快速响应,可以自己维护 COUNT
值,存到数据库表中或者缓存中。然而,这会增加系统复杂性。
- 示例:使用触发器维护计数,当user表插入或删除时更新user_counts表:
CREATE TABLE user_counts ( id INT PRIMARY KEY, count INT NOT NULL ); DELIMITER // CREATE TRIGGER user_insert AFTER INSERT ON user FOR EACH ROW BEGIN UPDATE user_counts SET count = count + 1 WHERE id = 1; END // CREATE TRIGGER user_delete AFTER DELETE ON user FOR EACH ROW BEGIN UPDATE user_counts SET count = count - 1 WHERE id = 1; END // DELIMITER ;
2. 优化 LIMIT OFFSET
使用 LIMIT OFFSET
进行分页操作时,当偏移量很大时,性能会显著下降。一个优化方法是使用覆盖索引查出所需的行,然后再做连接操作查出所需的数据。
示例:
-- 假设有一个带有索引的表 users
-- 索引 (id)
SELECT id FROM users ORDER BY id LIMIT 10000, 10;
-- 使用主键连接获取完整数据 使用索引(id)
SELECT users.*
FROM users
JOIN (
SELECT id FROM users ORDER BY id LIMIT 10000, 10
) AS temp USING (id);
3. 优化 JOIN
确保 ON
或者USING
的列上有索引。在创建索引时考虑连接顺序,在被连接表上索引。
示例:
CREATE INDEX idx_b_c ON B(c);
SELECT A.*, b.*
from A LEFT JOIN B ON A.c = B.c
三、其他通用优化方法
1. 不要请求不必要的行和列
避免使用 SELECT *,只选择需要的列,减少数据传输量。
2. 不要重复查询相同数据
将常用的数据缓存到应用层,避免重复查询。
3. 衡量一个大查询还是多个简单查询
有时将一个复杂查询分解为多个简单查询可以更高效。
4. 分解复杂的连接查询
将复杂的连接查询分解为多个步骤,可以减少中间结果集的大小,提高性能。