MYSQL查询性能优化

基础知识

笛卡尔积：集合X与Y的所有可能的有序对组成的集合，有序对第一个对象是X成员，第二个对象是Y成员：{Xi, Yj}

查询执行顺序：

Pasted image 20251008223349.png

1. 客户端发送一条查询给服务器
2. 服务器检查缓存，如果命中缓存，则立刻返回结果，否则进入下一阶段。
3. 服务器进行SQL解析，预处理，再由优化器生成对应的执行计划。
4. 调用存储引擎api执行查询。
5. 返回结果给客户端。

通信协议：MySQL客户端与数据库是半双工，客户发送完请求等待响应。

连接查询执行原理：
MySQL的连接查询执行嵌套循环操作，循环迭代外表的数据，再循环内表，对比符合的行输出。
例如：

select tbl1.col1, tbl2.col2
from tbl1 inner join tbl2 using(col3)
where tbl1.col1 in(5, 6)

伪代码：

outer_iter = iter over tbl1 where col in(5,6)
while out_row in outer_iter:
    inner_iter = iter over tbl2 where col3 = outer_row.col3
    while inner_row in inner_iter
        output [out_row.col1, inner_row.col2]
    end
end

MySQL优化器会优化扫描数据交少的表作为外表。
MySQL多表连接查询是一颗左侧深度优先树（嵌套循环），而不是平衡树（并列循环）。

优化层次

• 服务器性能优化
• 表结构设计优化
• SQL优化（主要学习）

SQL查询优化

sql查询优化、表结构设计、索引优化是统一的。
整体思想：SQL的优化就是减少返回列、返回行、减少扫描的行。分而治之思想：大SQL分成多条小SQL，减少阻塞。SQL分批多次执行或返回。

排查与分析SQL

慢SQL
开启慢SQL日志，配置阈值，查看慢sql。可以使用mysqldumpslow或者pt-query-digest分析慢sql日志。

查询执行状况
show processlist 可以查看当前查询的进程、执行的sql语句。

死锁排查
SHOW ENGINE INNODB STATUS; 可以查看最近一次的死锁
SELECT * FROM information_schema.INNODB_TRX 可以查看进行中事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 可以查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 可以查看等待锁事务
SHOW OPEN TABLES where In_use > 0; 查看正在使用的表

分析SQL执行
explain可以分析sql执行计划。
指标：

• type：访问类型，全表扫描还是索引，用了什么索引。
• rows：扫描的行数。
• keys：可能用到的索引。
• Extra：附加信息

关联子查询优化

最重要的建议：尽量使用关联查询代替子查询

1. IN子句
   MySQL5.x版本的IN查询性能非常差，可能会把外表压到子查询内，导致全表查询。通常建议用EXISTS代替IN。
   MySQL8 IN做了半查询优化，IN和JOIN性能差不多了。

什么是半连接（SEMI JOIN）？
核心定义：半连接是一种优化技术，用于处理使用 IN 或 EXISTS 的子查询。它的核心思想是：“只要在右表中找到一个匹配项，就返回左表的当前行，并停止在右表中继续查找”。
例如下列语句，没有半连接，会先执行子查询，得到临时列表，可能很大，用了半连接匹配到第一条符合条件的就返回：

SELECT * FROM products p
WHERE p.id IN (
    SELECT product_id FROM order_items
);

2. UNION子句
   MySQL5.7没办法把UNION ALL的条件推到内层执行，导致查出来一张大的临时表再做条件过滤。

MySQL8可以条件下推，UNION ALL可以流式输出不需要临时表。

3. COUNT查询
   count经常用在分页查找，InnoDB由于并发模型是MVCC，没有全局的统计值，count性能有问题。

常见解决方案：
1.应用层缓存计数值。
2.计算最小的索引数量SELECT COUNT(col) FROM t WHERE col IS NOT NULL;
3.估算而非精确值SHOW TABLE STATUS

4. GROUP BY 和 DISTINCT
   group by的工作原理是对select...where...获取的数据按照分组列做分组。
   group by如果用了非索引列，会用临时表或文件排序。
   用覆盖索引优化group by。

DISTINCT是在select获取结果之后，返回最终结果前distinct去重，一般用排序去重或哈希去重，大数据量会生成临时表。
建议：建立适合的覆盖索引，单列的distinct，用索引扫描去重。用group by代替distinct，mysql对group by优化的更好SELECT col1 FROM t GROUP BY col1;

5. LIMIT分页
   LIMIT的执行其实是根据之前步骤查询返回的数据限制条数，所以select * from tbl limit 10000，10；是会拿10010条然后只要最后10条看着那么逆天。

建议1：记录上次分页最后一条的唯一索引列，从上次的OFFSET开始查。
建议2：覆盖索引
建议3：先查ID再回表
建议4：从业务上避免分页深度
建议5：预分片/预计算分页 （大表分成小表、缓存分页结果）

MySQL 处理 SQL 的标准逻辑执行顺序大致如下（简化版）：

1. FROM / JOIN — 数据源读取与连接
2. WHERE — 行过滤
3. GROUP BY — 数据分组（如果有）
4. HAVING — 分组后过滤
5. SELECT — 列计算
6. DISTINCT — 去重
7. ORDER BY — 排序
8. LIMIT — 限制返回行数