优化MySQL数据库的三驾马车分别为：

  • 最优秀的表结构设计
  • 良好的索引
  • 恰当的查询语句

6.1 为什么查询速度会很慢

如果把查询比作是一个任务，那么他是由一系列的子任务组成，每个子任务需要消耗时间。如果需要优化查询，其实就是从两个方面来优化，第一 减少子任务；第二 让每个子任务执行更快。

查询的生命周期为：从客户端—->服务器—->在服务器端进行解析—->在服务器端生成执行计划—->在存储引擎端进行执行—->返回结果给客户端

6.2 慢查询基础：优化数据访问

6.2.1 是否向数据库请求了过多的数据

过多的数据可以理解成两种，第一种是获取过多的列，针对SELECT * 这种查询语句，我们需要保持怀疑的态度，应用是否真的需要返回全部的列；第二种是获取过多的列，应用是辅助提供人类的管理，一个列表页一般做有效的分页，针对select语句中不加limit,我们同样持有怀疑的态度，前端页面是否需要接受所有的行

6.2.2 是否扫描了过多的行

其中最重要的三个指标为：

  • 响应时间
  • 扫描的行数
  • 返回的行数

这三个指标通过检查慢日志可以得到。

响应时间
分成排队时间和执行时间，排队时间指的是等待某些资源而没有真正的执行花费掉的时间，可能是IO等待，可能是行锁等。服务时间指的是真正的执行查询花费的时间。

扫描的行数
在使用explain中type列反应了访问类型，一般分成表扫描、索引扫描、范围访问、单值访问和常量

6.3 重构查询的方式

6.3.1 到底是一个复杂的查询还是多个简单查询

这里一般建议使用多个简单查询，因为简单查询可以减少资源的竞争，而来简单查询可以有效的提高查询的利用率

6.3.2 切分查询

这里体现了计算机里面一个很重要的思想，分而治之。不要一口气吃个胖子，将大的操作转化成一个个较小的操作，可以有效的提高执行效率，比方说删除1千万条数据，直接delete肯定是不可取的，最理想的做法就是通过不断循环，每次删除1万条数据。

6.4 查询执行的基础

下图为经典的mysql查询流程图

  • 1.客户端发送了一条命令给服务器
  • 2.服务器端先检查是否有缓存，如果有缓存，然后校验权限，权限通过的话就给客户端返回数据。如果无缓存，那么就继续走下一个阶段
  • 3.SQL被生成解析树，此处只是简单的SQL的改造，通过校验的SQL根据存储引擎接口相关的调用再一次生成通过语法树的解析树，再由优化器选择最优的执行计划
  • 4.MySQL根据优化器提供的执行计划，调用存储引擎的API来执行查询。

6.4.1 MySQL通信协议

MySQL客户端和服务器之间通讯协议是半双工，这也就意味在任何时候，要么是服务器向客户端发送数据，要么是客户端向服务器请求数据，这两个动作不能同时执行。类比成抛球游戏，任何时候只有一方只有求，并且只有控制球的人才能发送数据。
，这里常用设置的参数为max_allowed_packet

查询状态
show full processlist可以看到当前MySQL查询所处的状态，主题对象是MySQL服务端

sleep : 线程正在等待客户端发送数据

query: 线程正在执行查询或者正在将结果发送到客户端

sending data: 这里标识多种情况，线程可能在做个状态之间传送数据，或者生成结果集，或者正在给客户端返回数据

6.4.2 查询缓存

一般建议不开启

6.4.3 查询优化处理

查询优化器

  1. 先执行你的SQL；
  2. show status like '%last_query_cost%';-- 代表查询当前会话的成本计算值

MySQL优化的类型：

  • 1 重新定义表的顺序
  • 2 将外连接转化成内连接
  • 3 等价条件转化
  • 4 优化max函数的min函数
  • 5 索引覆盖查询
  • 6 子查询优化
  • 7 提前终止查询【针对limit提前发现返回值，直接终止后续查询】
  • 8 列表in的查询优化【在MySQL里面in和or条件子句完全不一样，in使用的二分查找的优化方案】

MySQL如何执行关联查询
当前MySQL使用嵌套循环关联操作，即MySQL先在一个表里面取出单条数据，然后再嵌套循环到下一个表中寻找匹配到的上，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列
针对如下SQL:

  1. select tb1.col1,tb2.col2
  2. from tb1 inner join tb2 using(col3)
  3. where tb1.col1 in (5,6)
  4. 第一种实现伪代码

6.查询性能优化 - 图1

第二种绘制的泳道图为
6.查询性能优化 - 图2
从本质上来说，MySQL将所有类型的关联查询都转化成类似的方式进行运行。和很多数据库不同的是，MySQL并不会生成查询字节码来执行查询，而是生成一个查询树，热庵后通过存储引擎执行完成这棵指令树。在关联查询中，最重要的是选择驱动表。

排序优化
主要涉及两种算法：

  • 1.读取行指针和需要排序的字段，对其排序，然后再根据排序结果读取需要的数据行。
    那么这就会涉及到两次读取数据，并且第二次读取数据的时候，因为读取排序列是所有记录的，会产生大量的随机IO.
  • 2.单次传输排序，也是目前默认的排序算法。先读取查询需要的所有的列，然后根据给定的列进行排序，最后直接返回查询结果，设置参数为max_length_for_sort_data。

在关联查询的时候如果需要排序，如果排序的列全部来自同一个表，那么MySQL在关联处理的时候extr出现using filesort。除此之外所有的情况，MySQL都会将关联的结果放到临时表，然后在所有关联结束之后，在进行文件排序，这个时候你会在extr里面看到using temporary,using filesort。

在MySQL5.6及其以后的版本中，如果使用LIMIT，那么MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

6.5 MySQL查询优化器的局限性

6.5.1 松散索引扫描

通常来说MySQL的扫描需要定义一个起点和终点，即使需要的数据只是这个索引中很少数的几个，MySQL需要扫描这个索引里面的每个条目。
假设我们有索引 idx(a,b)
select …… from tb where b between 2 and 3;
正常来说MySQL无法使用索引，只能全表扫描找到匹配的行，数据查找过程如下图：
6.查询性能优化 - 图3

这里我们可以找到更快的方法来执行上面的查询，我们可以先扫描a列的第一个值对应b列的范围，然后再跳转到a列第二个不同值扫描对应b列的范围，这样就无需使用where子句过滤。
6.查询性能优化 - 图4

常用几个关键参数
use index,ignore index,force index: 指定是否告诉优化器使用某个索引

optimizer_search_depth: 穷举执行计划的深度

optimizer_switch:用来控制开启和关闭优化器的标志位

6.6 优化特定类型查询

优化count

  • 1.count: 如果写count(*)代表的统计行数，如果count(列名)代表的是统计某一列的所有行数【这里忽略掉为null的情况】。
  • 2.使用近似值来替代count，explain是不错的选择
  • 3.增加汇总表,比方说生成天月年等统计表
  • 4.增加缓存机制，比方说将今天之前的数据缓存到redis

优化关联查询

  • 1.确保on子句中的列上有索引
  • 2.确保group by和order by表达式只涉及到一个表中的列

优化子查询
最有效的方式就是讲子查询转化成关联查询

优化limit分页

  • 1.延迟关联查询
  • 2.通过传入上一页的最大主键ID

优化union
使用union all替代union操作