查询优化

查询慢的原因

网络 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: 表级锁 不会出现死锁问题