绝大多数读场景的优化
为什么查询速度会慢
- 如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快
- 查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端
慢查询优化：优化数据访问
- 两个分析步骤：
- 确认应用程序是否在检索大量超过需要的数据
- 确认MySQL服务器层是否在分析大量超过需要的数据行
- 是否向数据库请求了不需要的数据
- 查询不需要的记录
- 多表关联并返回全部列
- 总是取出全部列
- 重复查询相同的数据
- MySQL是否在扫描额外的记录
- 查询开销三个指标：响应时间、扫描的行数、返回的行数
- 响应时间：服务时间和排队时间之和，“快速上限估计”法
- 扫描的行数：较短的行的访问速度更快，内存中的行也比磁盘中的行的访问 速度要快得多
- 访问类型：EXPLAIN中的type列反应了访问类型；通过增加合适的索引；
- 三种方式应用WHERE条件：在索引中使用WHERE条件来过滤不匹配的记录；使用索引覆盖扫描（Extra中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果；从数据表中返回数据，然后过滤不满足条件的记录（Extra中出现Using Where）
- 需要扫描大量数据但只返回少数的行的优化技巧：使用索引覆盖扫描，改变库表结构，重写复杂的查询
重构查询的方式
MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效
切分查询，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果，可以避免锁住很多数据、占满事务日志、耗尽系统资源、阻塞很多小的但重要的查询
分解关联查询优势：
- 让缓存的效率更高
- 将查询分解后，执行单个查询可以减少锁的竞争
- 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
- 查询本身效率也可能会有所提升
- 可以减少冗余记录的查询
- 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联
分解关联查询的场景：
- 当应用能够方便地缓存单个查询的结果的时候
- 当可以将数据分布到不同的MySQL服务器上的时候
- 当能够使用IN()的方式代替关联查询的时候
- 当查询中使用同一个数据表的时候
查询执行的基础
查询执行路径
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存，如果命中则立刻返回，否则进入下一阶段
- 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
- 将结果返回给客户端
MySQL客户端和服务器之间的通信协议是“半双工”的，无法将一个消息切成小块独立来发送，没法进行流量控制，一旦一端开始发生消息，另一端要接收完整个消息才能响应它
MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力
查询状态，SHOW FULL PROCESSLIST命令查看：
- Sleep，线程正在等待客户端发送新的请求
- Query，线程正在执行查询或者正在将结果发送给客户端
- Locked，在MySQL服务器层，该线程正在等待表锁
- Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划
- Copying to tmp table [on disk]，线程正在执行查询，并且将其结果集都复制到一个临时表中，要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作
- Sorting result，线程正在对结果集进行排序
- Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据
语法解析器和预处理，通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”，解析器将使用MySQL语法规则验证和解析查询，预处理器则根据一些MySQL规则进一步检查解析树是否合法
查询优化器，找到最好的执行计划，使用基本成本的优化器，将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个，使用SHOW STATUS LIKE ‘Last_query_cost’;查看需要多少个数据页的随机查找
导致MySQL查询优化器选择错误的原因：
- 统计信息不准确，Innodb不能维护一个数据表的行数的精确统计信息
- 执行计划中的成本估算不等同于实际执行的成本
- MySQL的最优可能和你想的最优不一样
- MySQL从不考虑其他并发执行的查询
- MySQL也并不是任何时候都是基于成本的优化
- MySQL不会考虑不受其控制的操作的成本
- 优化器有时候无法去估算所有可能的执行计划
MySQL能处理的优化类型：
- 重新定义关联表的顺序
- 将外链接转化成内链接
- 使用等价变换规则
- 优化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”
- 预估并转化为常数表达式，当检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
- 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回需要的数据，而无须查询对应的数据行
- 子查询优化
- 提前终止查询，在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询
- 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上
- 列表IN()的比较，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件
在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息
在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联
对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询
MySQL对任何关联都执行“嵌套循环关联”操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止
全外连接就无法通过嵌套循环和回溯的方式完成，当发现关联表中没有找到任何匹配行的时候，则可能是因为关联恰好从一个没有任何匹配的表开始，MySQL不支持全外连接
关联查询优化器，会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行树的成本，最后返回一个最优的执行计划
如果有超过n个表的关联，那么需要检查n的阶乘关联顺序，称为“搜索空间”，搜索空间的增长速度非常快
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序
当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，MySQL将这个过程称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此
MySQL查询优化器的局限性
- 关联子查询：MySQL的子查询实现得非常糟糕，最糟糕的一类查询是WHERE条件中包含IN()的子查询语句，使用GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表，或者使用EXISTS()来改写
- UNION的限制：有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
- MySQL无法利用多核特性来并行执行查询
- MySQL不支持哈希关联，MariaDB已经实现了哈希关联
- MySQL不支持松散索引扫描，5.0后版本在分组查询中需要找到分组的最大值和最小值时可以使用松散索引扫描
- 对于MIN()和MAX()查询，MySQL的优化做得并不好
查询优化器的提示（hint）
- HIGH_PRIORITY和LOW_PRIORITY，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些
- DELAYED，对INSERT和REPLACE有效，会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入，并不是所有的存储引擎都支持，并且该提示会导致函数LAST_INSERT_ID()无法正常工作
- STRAIGHT_JOIN，可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联，第二个用法则是固定其前后两个表的关联顺序
- SQL_SMALL_RESULT和SQL_BIG_RESULT，只对SELECT语句有效，它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序
- SQL_BUFFER_RESULT，告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁
- SQL_CACHE和SQL_NO_CACHE，告诉MySQL这个结果集是否应该缓存在查询缓存中
- SQL_CALC_FOUND_ROWS，会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集，可以通过函数FOUND_ROW()获得这个值
- FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效，仅InnoDB支持
- USE INDEX、IGNORE INDEX和FORCE INDEX，告诉优化器使用或者不使用哪些索引来查询记录
- MySQL5.0后新增的用来控制优化器行为的参数：
- optimizer_search_depth，控制优化器在穷举执行时的限度
- optimizer_prune_level，让优化器会根据需要扫描的行数来决定是否跳过某些执行计划
- optimizer_switch，包含了一些开启/关闭优化器特性的标志位
优化特定类型的查询
- 优化COUNT()查询
- COUNT()是一个特殊的函数，有两种非常不同的作用：可以统计某个列值的数量，也可以统计行数，在统计列值时要求列值是非空的（不统计NULL）
- COUNT()并不是会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数，当MySQL确认括号内的表达值不可能为空时，实际上就是在统计行数
- MyISAM的COUNT()函数只有没有任何WHERE条件下的COUNT()才非常快
- 使用近似值，如EXPLAIN出来的优化器估算行数
- 使用索引覆盖
- 使用汇总表
- 使用外部缓存系统
- 优化关联查询
- 确保ON或者USING子句中的列上有索引
- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列
- 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方
- 优化子查询：尽可能使用关联查询代替，如果使用MySQL5.6以上或MariaDB则可以忽略这个建议
- 优化GROUP BY和DISTINCT
- 使用索引优化
- 当无法使用索引时，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组
- 尽可能的将WITH ROLLUP（超级聚合）功能移动应用程序中处理
- 优化LIMIT分页
- 最简单的办法是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);
- offset会导致MySQL扫描大量不需要的行然后再抛弃掉，如果可以记录上次取数据的位置，下次就可以直接从该记录的位置开始扫描，可以避免使用offset
- 使用预先计算的汇总表，或者关联到一个冗余表
- 优化UNION查询
- 通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中
- 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL