查询优化

查询慢的原因

网络 CPU IO 上下文切换 系统调用 生成统计信息 锁等待

优化数据访问

查询性能低下的主要原因是访问的数据太多 需要避免检索、传输大量数据

访问了太多行和列

如果是由于访问太多行，则首先应避免写出这种SQL，MySQL会直接返回客户端所查询的全部数据，所以可以使用LIMIT来进行限制

访问了太多的列，则应该审视是否真的需要这些列，其次可以通过覆盖索引扫描的方式来进行优化。

MySQL扫描了过多的数据行

通过检查慢日志记录可以找出扫描行数过多的查询。

也可以通过EXPLAIN语句列出结果rows属性，理想情况下扫描的行和返回的行数量一致。

如果要对扫描行数与返回行数量相差较多的查询进行优化，主要可以通过

  1. 覆盖索引扫描的方式
  2. 改变表结构 使用单独的汇总表之类的
  3. 重写掉这个复杂的查询

重构查询

对于一些操作数据量大的SQL，如果可以将其拆分成几个小SQL，在应用层进行处理，那可以把服务器的压力分摊到各个时间点中。

如可以对一些关联查询进行分解：

  1. 可以利用缓存
  2. 降低锁争用
  3. 应用层处理拥有较高的可扩展性
  4. ...

查询执行

屏幕截图 2021-03-16 152127

一条查询SQL的执行流程：

  1. 先进行缓存查询，如果命中缓存，则直接返回，否则继续
  2. 接下来会对SQL进行解析与处理，将SQL转为AST，语法错误在这个阶段被发现
  3. 接下来走到优化器，这个阶段会根据策略找到MySQL认为最优的执行方式
  4. 最后走到执行引擎这里，有大量的操作需要通过调用存储引擎实现的接口来完成，如调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中，慢查询日志有个rows_examined字段，表示了在执行这条SQL语句时，MySQL扫描了多少行数据

被废弃的查询缓存

从5.6开始 MySQL就禁用了缓存 8.0更是废弃了缓存

为甚要禁用

通信协议

MySQL的数据传输是半双工，一方发送完数据包后，必须等待对方回应才能进行下一步操作。通过设置max_allowed_packet参数来限制数据包大小

每个MySQL连接都有类似于线程的状态，通过这些状态可以发现一些不正常的连接：

SHOW FULL PROCESSLIST

Sleep、Query、Locked... 参考general-thread-states

查询优化处理

sql -> 语法树 -> 查询优化器 -> 选择优化器认为最佳的执行计划 -> 执行

由于种种原因，优化器最后可能会选择错误的执行计划

优化器计算的依据

优化策略

优化类型

关联查询执行

屏幕截图 2021-10-27 172202

排序优化

当排序的数据量过大，则会在文件中进行排序（filesort），使用两次传输排序或者单次传输排序，排序时消耗的临时磁盘空间要比排序的数据本身大得多。所以尽量使用LIMIT子句来降低需要排序的数据量。

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，排序过程是否在内存中完成，取决于排序所需的内存和参数 sort_buffer_size，sort_buffer_size 越小，需要分成的份数越多，也就需要更多的临时文件

全字段排序（单次传输排序）：

  1. 初始化 sort_buffer，放入 select 指定的字段
  2. 不断找出满足记录的数据，取出 select 指定的字段放入 sort_buffer
  3. 对 sort_buffer 中的数据按照排序字段排序

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，就改用两次传输排序

rowid 排序（两次传输排序）：

  1. 初始化 sort_buffer
  2. 找出满足记录的数据的主键，根据主键再回表查到所需要的字段
  3. 对 sort_buffer 中的数据按照排序字段排序

并不是所有的 order by 子句都需要进行排序，如果索引合适，MySQL 可以直接利用索引的有序性直接得到有序结果，甚至还不用回表：

-- 如果在这张表上有个联合索引(city,name)
SELECT city,name FROM person WHERE city = '厦门' ORDER BY name

order by rand()：这个语句需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的，内存临时表排序的时候使用了 rowid 排序方法

  1. 创建一个临时表，除了 select 的字段之外，还有一个一个随机值
  2. 按照随机值排序

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表

优化器的局限

给优化器提示

通过给SQL语句加上一些标志，比如常见的INSERT DELAYED，来明确控制优化器的行为

若非必要，不要使用这些提示，每次MySQL新版本都会加入许多优化策略，这些提示可能会造成优化策略失效

https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

优化特定类型查询

count 查询

COUNT(列或表达式) 计算的是列或表达式有值(非NULL)的数量，而COUNT(*) 计算的就是结果集数。

count(主键 id)：遍历整张表，把每一行的 id 值都取出来拿到 id 后，判断是不可能为空的，就按行累加

count(1)：遍历整张表，但不取值，对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加

count(字段)：对于有可能是 null的，还要把值取出来再判断一下，不是 null 才累加

count(*)：是例外，专门做了优化，不取值。这个count返回的每一行肯定不是 null，按行累加

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)

关联查询

子查询优化

limit 分页大偏移量优化

SELECT * FROM xx 
  INNER JOIN(SELECT id FROM xx LIMIT 999,5)

SELECT * FROM xx WHERE id > 1000 LIMIT 5

优化union查询

除非确实需要服务器消除重复的行，否则一定要使用union all

使用用户自定义变量

SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;
SELECT @last_week;

使用限制：无法使用查询缓存、变量的生存域在连接中

使用索引

索引的原理：

屏幕截图 2020-08-27 093235

MySQL 使用索引的方式：

索引的代价：

挑选索引：

查询优化程序

EXPLAIN SELECT * FROM person WHERE FALSE

有助于优化程序对索引充分利用：

数据类型高效查询

表存储格式高效查询

MyISAM:

MEMORY:

InnoDB：

CREATE TABLE tb (...) ROW_FORMAT = xxx;

高效加载数据

调度 锁定 并发

调度策略：

InnoDB: 行级锁 更精细 并发度更高

MyISAM: 表级锁 不会出现死锁问题