MySql 查询优化 - litter-fish/ReadSource GitHub Wiki
重构查询的方式
- 使用复杂查询还是简单查询
- 切分查询，将大查询分解成小的查询，每次只处理一部分数据。
- 分解关联查询，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联操作。 这样做的好处：
- 让缓存更高效
- 将查询分解后，执行单个查询可以减少锁的竞争
- 查询本身效率也可能会提升
- 可以减少冗余记录的查询
查询执行的基础
一个查询执行的过程：
查询状态：
- Sleep 线程正在等待客户端发送新的请求
- Query 线程正在执行查询请求或正在将结果发送给客户端
- Locked 在Mysql服务层，该线程正在等待表锁。
- Analyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
- Copying to tmp table [on disk] 线程正在执行查询，并且将结果集都复制到一个临时表中，这种状态要么在执行Group by操作，要么在做文件排序操作，或者是union操作。 如果还有on disk 标志，说明正在将内存临时表放到磁盘中
- Sorting Result 线程正在对结果集进行排序
- Sending data 线程可能在多种状态下进行数据传递，或者在生成结果集，或者在向客户端发送结果
查询缓存 通过一个大小写敏感的哈希查找实现。
查询优化处理 解析SQL、预处理、优化SQL执行计划
MySql根据关键字将Sql语句进行解析，并生成一颗对应的“解析树”。 预处理将根据MySql规则进一步检查解析树是否合法。 查询优化器： 一条查询可以有很多的执行计划，最后都返回相同的结果。优化器的作用就是“根据成本”找到其中最优的执行计划。
优化策略： 静态优化策略，直接对解析树进行分析，并完成优化。 动态优化策略，需要结合上下文进行分析。
MySql能够处理的优化类型：
- 重新定义关联表的顺序
- 将外连接转换成内连接
- 使用等价变换规则
- 优化count、min、Max
- 预估并转换成一个常数表达式
- 覆盖索引扫描
- 子查询优化
- 提前终止查询
- 等值传播
- 列表IN的比较 MySql将In列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，复杂度为：O(log n)
Mysql执行关联查询 mysql对任何关联都执行嵌套循环关联操作，即MySql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去。
排序优化
-
两次传输排序算法 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需的数据行
-
单次传输排序算法 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。
如果order by 字句中的所有列都来自关联的第一个表，则会在关联的第一个表的时候就进行文件排序，在explain中的extra中可以看到“Using filesort”。 除此情况，Mysql都会将关联结果存放到一个临时表中，等待所有关联结束，再进行文件排序，此种情况下extra中“Using Temporary；Using filesort”。
查询执行引擎 根据执行计划完成查询的整个过程。
返回结果 如果查询结果可以被缓存，则会进行缓存。 Mysql将结果返回给客户端是一个增量、逐步的过程，每产生一个结果即可以返回给客户端。 服务端无需存储太多的结果，内存不会被占太多； 可以让客户端第一时间获得返回结果。
特定类型的优化
- 优化count查询 count的作用：
- 统计某个列值的数量
- 统计行数
- 优化
查询缓存
缓存完整的select查询结果。缓存命中后，跳过解析、优化、执行阶段。