第06章-查询性能优化
设计合理的查询
6.1 查询为什么会慢?
6.2 慢查询基础：优化数据访问
6.2.1 是否数据库请求了不需要的数据
- 查询了不需要的记录
- 多表关联时返回全部列
- 总是取出全部列
- 重复查询相同的数据
6.2.2 是否在扫描额外的记录 衡量指标：
- 响应时间
- 扫描的行数、访问类型
- 返回的行数
如果发现查询需要扫描大量的数据但是只返回少数的行，那么常见的优化技巧：
- 使用覆盖索引（无需回表）
- 改变库表结构。例如使用单独的汇总表
- 重写这个复杂的查询
6.3 重构查询的方式
6.3.1 一个复杂查询还是多个简单查询
6.3.2 切分查询
6.3.3 分解关联查询
- （1）让缓存的效率更高
- （2）减少锁的竞争
- （3）查询效率可能提升
- （4）可以减少冗余记录的查询
- （5）在应用层做关联，更容易对数据库进行拆分，更容易做到高性能和可扩展
- （6）相当于在应用层做了哈希关联
6.4 查询执行的基础
6.4.1 MySQL 客户端 / 服务器通信协议
- MySQL客户端与服务器之间的通信协议是“半双工”的（？）
- 缺点：无法进行流量控制
- max_allowed_packet
查询状态： show full processlist；
- Sleep
- Query
- Locked
- Analyzing and statistics
- Copying to tmp table [on disk]
- Sorting Result
- Sending Data
6.4.2 查询缓存
如果查询缓存是打开的，那么MySQL会优先查询缓存，判断是否命中。 具体是通过一个对大小写敏感的哈希查找实现的。 如果命中了，那么在结果返回前会检查一次用户权限。
6.4.3 查询优化处理
语法解析器和预处理
- 通过关键字将SQL语句进行解析，生成一棵解析树
- MySQL解析器将使用MySQL语法规则验证和解析查询
- 预处理器则根据一些MySQL规则进一步检查解析树是否合法
- 预处理器验证权限
查询优化器
一条查询可以有多种执行方式，最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。
MySQL采用基于成本的优化器，它将尝试预测一个查询使用某个执行计划的成本，并选择其中成本最小的那一个。
使用show status like 'Last_query_cost'; 查看上一条语句 mysql预估的查询成本。
多种原因导致MySQL优化器选择错误的执行计划
- 统计信息不准确
- 执行计划中的成本估算不等同实际执行的成本
- MySQL的最优可能和用户想象中的最优不一样
- 其他并发查询的影响
两种优化策略
- 静态优化
- 对解析树进行分析，并完成优化
- 动态优化
- 和上下文有关
MySQL能够处理的优化类型
- 重新定义关联表的顺序
- 将外连接转化成内连接
- 使用等价变换规则
- 优化COUNT()、MIN()、MAX()
- 用常数替换
- 预估并转化为常数表达式
- 覆盖索引扫描
- 子查询优化
- 提前终止查询
- 等值传播
- 列表IN() 比较
- 将IN() 中的数据排序后用二分
数据和索引的统计信息
MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。 存储引擎提供给优化器的信息有：每个表或索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。
MySQL如何执行关联查询
驱动 泳道图
全外连接就无法通过嵌套循环和回溯的方式完成，这时当发现关联表中没有找到任何匹配行的时候，则可能是因为关联是恰好从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因。
执行计划
MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。
如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。
关联查询优化器
MySQL最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。
- 可以使用straight_join关键字指定顺序
- 如果表太多，那么决定表的顺序，开销也很大（n的阶乘）。 optimizer_search_depth配置了搜索深度，超过限制则会使用贪婪搜索
排序优化
当不能使用索引生成排序的结果时，MySQL需要自己进行排序，在这种情形下，即使是只使用内存，不使用磁盘，也被称为filesort
排序的数据量小于“排序缓存区”，那么MySQL会使用内存进行快速排序
如果超过，那么将会分块进行快速排序，并将结果存放在磁盘上，然后再进行合并排序
MySQL有两种排序算法：
- 两次传输排序
- 第一次读只取需要排序的字段进行排序，第二次则在排序后根据结果取所需要的字段
- 第二次产生大量随机IO
- 单次传输排序
- 读取全部需要的信息进行排序，然后返回结果
- 空间开销较大
当查询需要的所有列的总长度不超过参数max_length_for_sort_data时，MySQL使用单次传输排序。
MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多：
- 如果是varchar，需要分配其完整长度
- 如果使用UTF8字符集，MySQL将会为每个字符预留三个字节。
关联查询需要文件排序时，有两种情况：
- order by子句中所有列都来自关联的第一个表，那么explain的时候，extra字段的值为filesort
- 除此之外的情形explain会显示using temporary; using filesort;
6.4.4 查询执行引擎
执行计划是一个数据结构，查询执行引擎将根据执行计划完成整个查询
查询中每一个表由一个handler实例表示，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等等
6.4.5 返回结果给客户端
可以缓存则缓存
6.5 MySQL查询优化器的局限
6.5.1 关联子查询
IN和EXISTS的改写
如何用好关联子查询？
6.5.2 UNION的限制
6.5.3 索引合并优化
MySQL可以访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行
6.5.4 等值传递
某些时候，等值传递会带来一些意想不到的额外消耗。 例如，有一个非常大的IN()列表，而MySQL优化器发现存在WHERE、ON或者USING子句，将这个列表的值和另一个表的某个列进行关联
6.5.5 并行执行
MySQL无法利用多核特性来并行执行查询
6.5.6 哈希关联
通过建立一个哈希索引来实现哈希关联的效果
6.5.7 松散索引扫描
MySQL无法按照不连续的方式扫描一个索引。
通常MySQL的索引扫描需要定义一个起点和终点
MySQL5.0后的版本，在某些特殊的场景下是可以使用松散索引扫描的。例如在一个分组中找到分组的最大值和最小值。
在MySQL5.6之后的版本，关于松散索引扫描的一些限制将会通过“索引条件下推”的方式解决
6.5.8 最大值和最小值优化
最大值和最小值触发全表扫描，因通过其他等价方式避免
6.5.9 在同一个表上查询和更新
MySQL不允许对同一张表同时进行查询和更新。
但是可以通过临时表的方式“看似”进行绕过
6.6 查询优化器的提示（hint）
- HIGH_PRIORITY和LOW_PRIORITY：告诉当多个语句同时访问一个表时，哪些语句的优先级较高和较低
- DELAYED
- STRAIGHT_JOIN：指定关联顺序
- SQL_SMALL_RESULT：提示使用内存临时表
- SQL_BIG_RESULT：提示使用磁盘临时表
- SQL_BUFFER_RESULT：将查询结果放入临时表中，然后尽快地释放表锁
- SQL_CACHE和SQL_CACHE：告诉MySQL结果集是否缓存在查询缓存中
- SQL_CALC_FOUND_ROWS：不是真的优化器提示，它会让MySQL返回的结果集包含更多的信息
- FOR UPDATE 和 LOCK IN SHARE MODE：不是真的优化器提示，控制SELECT语句的锁机制
- USE INDEX、IGNORE INDEX和FORCE INDEX
MySQL5.0中新增了一些参数用来控制优化器的行为：
- optimizer_search_depth：如果查询长时间处于statistics状态，那么可以考虑调低这个参数
- optimizer_prune_level：默认打开，让优化器根据需要扫描的行数来决定是否跳过某些执行计划
- optimizer_switch
MySQL升级后的验证
6.7 优化特定类型的查询
6.7.1 优化COUNT()查询
COUNT()不统计NULL
COUNT()两个用法：
- 统计某个列值的数量
- 统计行数
如果COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数
6.7.2 优化关联查询
确保ON或者USING子句中的列上有索引
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程
6.7.3 优化子查询
6.7.4 优化GROUP BY和DISTINCT
如果需要对关联查询做分组，并且按照查找表的某个列进行分组，那么通常采用查找表的标识进行分组的效率比其他列更高
6.7.5 优化LIMIT分页
延迟关联
话说limit offset的效率有这么低吗？
6.7.6 优化SQL CALC FOUND ROW
6.7.7 优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好的使用。经常需要手工的将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中
除非确实需要服务器消除重复的行，否则一定要使用UNION ALL，如果没有ALL，那么MySQL会给临时表加上DISTINCT选项。
6.7.8 静态查询分析
Percona Toolkit中的pt-query-advisor
6.7.9 使用用户自定义变量
用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。如果能够用好，发挥其潜力，在某些场景可以写出非常高效的查询语句。
优化排名语句
避免重复查询刚刚更新的数据
统计更新和插入的数量
确定取值的顺序
编写偷懒的UNION
用户自定义变量的其他用处