《高性能Mysql》学习笔记（4） - 查询性能优化

1 优化数据访问

通常可以往以下两个角度考虑

  1. 确认应用程序是否在检索大量超过需要数据。
  2. 确认mysql服务器层是否在分析大量超过需要的数据行

1.1 是否向服务器请求了不需要的数据

比较典型的

  • 查询不需要的数据
  • 多表关联时返回全部的索引列
  • 总是取出全部列
  • 重复查询相同数据
    • 可以在第一次查询到数据时客户端做缓存起来。

1.2 MYSQL是否在扫描额外数据

在确认了只返回需要的数据后，要看看结果是否扫描了多的数据。对于mysql，衡量查询开销的指标如下

  • 响应时间
    • 是服务时间 + 排队时间之和
      • 排队时间是指服务器等待一些资源而没有真正执行查询耗费的时间，可能等I/O完成，等待锁..
  • 扫描的行数
    • 最理想的情况是扫描行数是和返回行数相同
  • 返回的行数

当发现扫描大量数据但是只返回少量行数，可以看看这几个角度

  • 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
  • 改变库表结构。例如使用单独的汇总表
  • 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询

2 查询重构的方式

最初我们希望在一次查询中数据库能尽可能做更多的事情，这样的逻辑因为过去总是认为网络通信，解析，优化是代价很高的事情。

但是现在并非如此，客户端连接mysql服务器的消耗很低，mysql内部扫描数据很快

2.1 切分查询

对一个大查询分而治之，切分成多个小查询，每个查询功能一样，只完成一小部分，返回一小部分的结果。

例如，大量清理数据，如果一次完成的话，要锁住的数据非常多，可能会占满事务日志，阻塞其他查询。就可以把这个查询切分成多个较小查询。

2.2 分解关联查询

例如这样的关联查询

 SELECT * FROM tag
 JOIN tag_post ON tag_post.tag_id=tag.id
 JOIN post ON tag_post.post_id=post.id
 WHERE tag.tag='mysql';

拆分成

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

这样做的好处：

  • 让缓存的效率更高，更方便缓存单表查询对应结果
  • 查询分解后，可以减少锁的竞争
  • 减少冗余字段的查询，还可以减少网络和内存的消耗
    • 关联查询可能返回字段多，查询的数据多，传输到应用层网络压力较大，如果拆分后，传输网络压力更小。

3. 查询流程

完成的查询流程如下

image-20260330020224480

  1. 客户端发送一条查询给服务器。
  2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。（查询缓存8.0后移除）
  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

3.1 Mysql 客户端/服务器通信协议

Mysql 客户端和服务器的通信是半双工的，这意味着，同一时刻，要么服务器给客户端发送数据，要么客户端给服务器发送数据

客户端通常是通过库函数（jdbc）向服务器发送查询请求。 （客户端 -- mysql连接库函数 --- mysql服务器）

客户端通过库函数发送请求有两种策略

  • 缓存：客户端通过库函数缓存mysql服务器的查询结果，再通过缓存获取结果集，适合小结果集，优点在于不需要保持mysql服务器连接，缓存起来就释放掉，减少服务器的压力
  • 流式（非缓存）：库函数不缓存查询结果，适合大结果集，优点在于对内存优化，延迟低，适合批量处理

查询状态

对于一个mysql连接，或者一个线程，任何时候都有一个状态，说明mysql正在做什么

  • sleep：线程正在等待客户端新的请求。
  • Query：线程等待查询或者正在将结果发给客户端
  • Locked：该线程现在在等待锁
  • Analyzing and statistics：正在收集统计信息，并生成执行计划
  • Copying to tmp table：正在执行查询，将结果复制到临时表
  • sorting result：结果排序
  • Sending data

3.2 查询缓存

通过大小敏感的哈希查找实现的，因为中标率低在mysql 8.0已经被移除了。

如果当前查询刚好命中了查询缓存，在返回结果前会先检查一次用户权限

3.3 语法解析器和预处理

解析器的核心任务是 “读懂 SQL 文本的结构”，不关心表 / 列是否存在，只关心语法是否符合 MySQL 语法规则。
预处理器拿到解析器生成的解析树，做语义层面的深度校验和扩展，把解析树变成更严谨的预处理语法树，为后续优化器做准备。

3.4 查询优化器

到这，语法树已经是合法的了，将有优化器将其转为执行计划

查询优化器会做的：

  1. 解析 语法树，变成执行逻辑（要查那张表，过滤条件是什么，要不要排序）
  2. 准备统计信息
  3. 生成所有的执行方案，计算每个方案的 “成本”，选择最优顺序（这里涉及了一些语句优化）
  4. 生成最终执行计划

MYSQL使用基于成本的优化器，将尝试预测一个查询使用某种执行计划的成本，并选择最小的一个。查询Last_query_cost查看计算当前查询的成本

导致优化器选择错误执行计划的原因：

  • 统计信息不准确
  • Mysql从不考虑并发的其他执行的查询
  • 执行计划中的成本估算不等同于实际执行的成本
  • .....

查询优化器的优化策略：

  • 静态优化：直接对生成的语法树进行解析并且优化，在第一次完成就一直有效
  • 动态优化：跟查询上下文有关，在每次查询中重新评估，算是运行时优化

MYSQL优化的类型

  • 重新定义表关联的顺序
  • 将外连接转化成内连接
  • 等价变换规则
  • 提前终止查询
    • 当发现已经满足查询需求时，mysql会立刻终止查询
  • 列表IN的比较
    • IN的比较完全等同多个OR条件的子句，MySQL会通过先排序，然后通过二分查找来确定列表中的值是否满足条件
  • .....

4. MYSQL如何执行关联查询

嵌套循环关联操作

  • mysql现从一个表中取出一条数据，然后嵌套循环到第二个表中寻找匹配行，直到找到所有表中匹配的行为止，然后根据行返回对应的列

image-20260330030135111
Block Nested-Loop Join（BNL）

  • 减少内表扫描次数、降低磁盘 I/O，专门用于没有索引的关联场景。
  • 流程
    • 把驱动表的关联字段 + 查询字段读到 join buffer 里
    • 一次性把 buffer 里的一批数据和内表做匹配
    • 内表只需要扫描 1 次，而不是驱动表行数次

5 关联查询优化

优化器针对多表关联查询，会重新定义关联顺序。

一句话就是“小表驱动大表”

6. 排序优化

当无法使用索引进行排序时，就不可避免需要服务层自己做排序，这就是filesort

  • 当数据量小时，filesort是在内存中进行
    • 当排序的数据量小于“排序缓冲区”，mysql使用内存进行“快速排序”操作
  • 当数据量大了，则需要用到磁盘
    • 如果内存不够排序，mysql先会把数据分块，然后对每个独立的块使用“快速排序”进行排序，并将排序结果放在磁盘上，然后将各个快的结果合并

mysql的两种排序算法

  • 两次传输排序
    • 读取行指针和需要排序字段，对其排序，再根据排序结果读取所需要字段。
  • 单次传输排序
    • 先读取查询所需的所有列，再根据定列进行排序，最后直接返回排序结果。

对于关联查询的排序：

  1. 所有排序的字段都在在一张驱动表，先对驱动表排序，再嵌套循环

  2. 字段不在驱动表，关联后对临时表排序

7. 查询执行引擎

生成执行计划后，查询执行引擎会通过执行计划完成整个查询

优化阶段会为每张表生成Handler对象，等到执行阶段，就通过handler对象操作存储引擎中的handler api（也就是我们之前说的存储引擎查询的接口）来执行执行计划中的指令

8 查询优化器的局限性

  1. Union的限制

    • 限制条件无法从外层下推到内层，例如想将两个子查询都联合起来，再取20条记录，mysql会把两个表都存放在一个临时表中，再取出前20条。
    (SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name)
UNION ALL
(SELECT first name, last_name
FROM sakila.customer
ORDER BY last_name)LIMIT 20;

    只能把limit写进UNION ALL的各个查询中

(SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first name, last_name
FROM sakila.customer
ORDER BY last_name
LIMIT 20)
LIMIT 20;
  • 等值查询
    • 某些时候等值查询会带来意想不到的额外消耗，比如一个非常大的IN()列表，mysql发现有where，on或者using字句时，会把这个值和某个列关联，就会导致优化和查询变慢
  • 最大值最小值优化
  • 在同一个表上查询和更新
    • 不允许在同一个表上查询和更新

9 查询优化器的提示

用提示可以控制最终的查询计划

  • STRAIGHT_JOIN:不需要优化器做表关联，直接按表的顺序关联，可以用与观察不同表关联的成本
  • SQL_SMALL_RESULT & SQL_BIG_RESULT
    • 对select生效，告诉优化器对groupby和distinct如何使用临时表和排序（内存还是磁盘）
  • SQL_CACHE & SQL_NO_CACHE
  • FOR UPDATE
  • USE INDEX,IGNORE INDEX,FORCE INDEX

10 优化特定查询

  • count的优化

    • count作用：统计列值数量，统计行数，当统计列值数量不会计算列值为null的数量****
    • MYISAM维护了count的数字，查询全表数目非常快，但是只有没有任何where条件的count(*)才非常快
    • count的近似值可以用优化器估计的行数来代替

  • 优化关联查询：

    • 确保ON/USING的列上有索引，在关联顺序的第二张表的对应列上面创建索引
    • 确保group by/order by 的表达式只涉及一个表的列

  • 优化子查询

    • 尽可能用关联查询来替代

  • 优化group by，distinct

    • 当有索引的时候都可以通过索引来优化（松散/密集索引扫描）
    • 没有索引需要filesort的时候，可以用SQL_BIG_RESULT,SQL_SMALL_RESULT来按照我们希望的方式运行
    • GROUP BY会默认按照分组字段排序

  • 优化LIMIT分页

    • 可以使用延迟关联的操作,延迟关联是通过先找到排序和limit后的主键生成临时表，再和需要查询更多字段的主表关联。
    //如下limit分页
select film_id，description FROM sakila.film ORDER BY title limit 50,5
//如果这个表非常大，可以改写成
Select film_id,film_description
from sakila.film  
inner join (
	Select film_id from sakila.film
	ORDER BY title
	limit 50,5
) AS lim USING(film_id);

  • 优化union查询

    • 使用UNION ALL，而不是UNION，UNION会对产生的临时表做distinct操作，代价非常高

  • 使用用户自定义变量优化

posted @ 2026-05-01 18:39  不会coding的喵酱  阅读(8)  评论(0)    收藏  举报