高性能MySQL第六章：查询性能优化

前面的章节介绍了如何设计优良的表结构、建立最好的索引，但是有这些还不够，如果没有合理的查询，这些基础设计仍然没有办法发挥其作用，本章主要介绍了查询设计的一些基本原则，幷介绍了一些优化技巧

一、为什么查询速度会慢

查询任务是由一系列子任务组成的，每个子任务都会消耗一定的时间，包括网络IO，CPU计算，生成统计信息和执行计划，锁等待，其他任何一个节点都有可能导致整个查询任务变慢，所有，优化查询的目的就是减少和消除这些过程所花费的时间

二、优化数据访问

1.不请求不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序会被应用程序丢弃，这会带来一些额外的幷不能带来收益的性能消耗
常见的情况有一下几种

• 查询不需要的记录
• 返回全部的列
• 查询重复的数据，如果总是需要多次执行同一个sql，且返回的结果不变，可以考虑将查询结果缓存起来

避免MySQL扫描额外的记录

在确定只返回需要的数据以后，就需要再进一步确认查询为了返回结果是否扫描了过多的数据，最简单的衡量查询开销的指标如下

• 响应时间
• 扫描行数
• 返回的行数
  这三个指标大致反映了MySQL都在内部执行的情况，并且可以通过查询日志记录获取到相关记录，我们可以通过这三个指标来确定一个查询是否需要优化，比如增加索引，重构查询语句

三、重构查询方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果，而不一定总是需要从MySQL获取一模一样的结果集，有时候可以将查询转换一种写法让其返回一样的结果，但是性能更好，也可以通过另一种方式完成查询，最终达到一样的目的。

1.复杂查询与多个简单查询的取舍

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了，在其他条件都相同的时候，使用尽可能少查询更好，但有时候，将一个大查询分解为多个小查询是很有必要的。

2.切分查询

有时候对于一个大查询，我们需要分而治之，将一个大查询切分为小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果
比如删除旧的数据就是一个很好的例子，定期清除大量数据时，如果用一个大的语句一次性完成的话，可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞其他小的查询，但是如果将一个大的delete语句切分成多个较小的查询可以尽可能小的影响MySQL性能。

分解关联查询

可以对一个表进行一次单表查询，然后将结果在应用程序中进行关联，分解查询就是将一个复杂的查询拆分为几个简单的单独查询，这样操作有如下好处

• 让缓存更高效，方便在系统中进行热点数据的缓存
• 将查询分解后，执行单个查询可以减少锁的竞争
• 在应用层做关联，可以更容易对数据库进行拆分，提升系统的扩展性
• 提升查询效率，更方便进行索引设计

查询执行的基础

在希望mysql能以更高的性能查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的，很多查询优化工作实际上就是遵循一些原则让优化器能够按预想的合理方式运行，当一个MySQL发送一个请求的时候，MySQL做了如下操作

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中缓存，则立刻返回结果，否则进入下一阶段
3. 服务器进行SQL解析，预处理，在由优化器生成对应的执行计划
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5. 将结果返回客户端

1.MySQL客户端/服务器通信协议

MySQL的客户端和服务器之间的通信协议是半双工的，这意味着，在任何一个时刻，要么是由服务器向客服端向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作无法同时发生，所有我们无法也无需将一个消息切成小块独立来发生
查询状态
在进行MySQL连接时，我们可以通过SHOW FULL PROCESSLIST来观察一个连接所处的状态，连接所有可能出现的状态有一下几种

• Sleep 等待客户端发送新的请求
• Query 线程正在执行查询或者将结果发送到客户端
• Locked 表示正在等在表锁
• Analyzing and statistics 线程在搜集存储引擎的统计信息并生成查询的执行计划
• copying to tmp table 线程正在执行查询操作，并将结果集复制到一个临时表中
• sorting result 正在对结果集进行排序
• sending data 线程在多个状态之间传送数据或者在生成结果集

查询缓存

在执行查询时，如果查询缓存是打开的，MySQL会先去检查这个查询是否有缓存数据，如果有，就会直接返回缓存数据，这就是为什么有时候在执行同一条查询语句是，后面会比第一次快很多

查询优化处理

语法解析和预处理
如果查询未命中缓存，就会进入到sql解析过程，MySQL通过关键字将sql语法进行解析，生成一个对应的解析树，幷在这个过程中解析器负责检查关键字是否使用错误或者顺序错误，预处理器则对解析数进行表和数据列进行检测是否存在问题

查询优化器

在解析树没有问题后，优化器就会将其转化为执行计划，并且在众多可以返回相同结果的执行计划中选择最优的一个计划

查询执行引擎

MySQL的查询执行引擎根据生成的执行计划来完成整个查询，这里的执行计划是一个数据结构，执行引擎根据执行计划，逐步执行指令，调用存储引擎的接口来实现查询，也就是handler api，而每一张表都有一个handler实例与之对应

返回结果给客户端

查询执行完成后，就会将结果返回给客户端，并且会将查询的结果进行缓存

五、查询优化器的局限性

优化器有时候幷不能保证对每种查询都是最优的，所以我们需要根据实际情况，优化查询语句，以实现更好的执行效率

关联子查询

select * from file where film_id in (select film_id from film_actor where actor_id = 1)

这里可以优化为

select * from file where film_id in (select group_concat(film_id) from film_actor where actor_id = 1)

union 的限制

有时候，MySQL无法将限制条件从外层下推到内层，是的原本能够限制部分返回结果的条件无法应用到内层查询优化上，比如union中使用limit，取20条数据

（select name from actor order by name）
union all
(select name from customer order by name)
limit 20l

在这里，mysql会把表1的所有数据和表2的所有数据都保存到一个临时表中，然后从临时表中取20条数据，这里可以进行如下优化

（select name from actor order by name limit 20）
union all
(select name from customer order by name limit 20)
limit 20l

优化后，mysql最多只会取40条数据放入临时表，然后取前20条

六、查询优化器提示

如果对优化器选择的执行计划不满意，可以使用优化器提供的提示来控制最终的执行计划
HIGHT_PRIORITY 和 LOW_PRIORITY ：用来设置语句执行的优先级
DELAYED ：对insert和replace有效，MySQL会将使用该提示的语句立即返回给客户端，幷将插入的行数据放入到缓存区，在表空闲的时候再批量插入
straight_join ：可以放在select之后，也可以放在两个关联表的名字之间，可以用来让查询中所有的表按照出现的顺序进行关联
SQL_SMALL_RESULT 和 SQL_BIG_RESULT ：用来告诉优化器对group by 或者distinct如何使用临时表及排序
SQL_BUFFER_RESULT ：将查询结果放入到一个临时表
SQL_CACHE和SQL_NO_CAHCE ：确认查询是否缓存结果
SQL_CALC_FOUND_ROWS ：在查询时，计算结果集总数，幷可以通过found_rows获取该值
FOR UPDATE 和 LOCK IN SHARE MODE ：用来控制select的锁机制，但只对实现了行级锁的存储引擎有效，该提示会多符合条件的行上锁
USE INDEX、IGNORE INDEX和FORCE INDEX ：确定使用或者不使用指定的索引

优化特定类型的查询

1.优化count()

count函数可以用来统计某个列值的数量，也可以用来统计行数，当统计列值时，对于列值为null会排除在外，即统计的是指定列有值的数量，当统计行时，使用count（*）会忽略所有的列，直接统计行数

如果你想统计行数，就用count（*），因为指定列的话，可能由于列存在null导致统计的行数比实际的少

简单的优化

优化前

select count(*) from city where id>5

优化后

select (select count(*) from city)-count(*) from city where id<5

优化效果：在myisam引擎中，第一条语句需要扫描id大于5的所有数据，但是第二条只需要扫描五条数据

查询一个列中不同值的数量

select sum(if(color = 'blue', 0 , 1))as blue, sum(if(color = 'red', 0 , 1)) as red from item

select count(color = 'blue' or null )as blue, count(color = 'red', or null ) as red from item

使用近似值

对于不需要完全精确的值的情况下，可以使用explain预估的值

复杂的优化

使用count(*)一般需要扫描大量的行才能获得精确的结果，，可以考虑使用覆盖索引，或者增加外部缓存系统。

2.优化关联查询

• 确保ON或者USING子句中的列上有索引
• 确保任何group by 和order by 中的表达式只设计到一个表中的列

3.优化子查询

尽可能的使用关联查询来代替子查询，但是5.6以上的版本不需要考虑这个优化建议

4.优化group by 和 distinct

1. 使用索引
2. 无法使用索引时，通过使用提示SQL_SMALL_RESULT 和 SQL_BIG_RESULT来优化临时表的效率
3. 使用标识列作为分组的对象

使用group by 时，查询结果集会自动安装分组的字段进行排序，如果不关心结果集的顺序，可以使用order by null让MySQL不进行排序

5.优化limit

当limit的偏移量非常大的时候，性能会由于扫描的数据过多而影响性能，这时候，可以考虑先通过覆盖索引扫描数据，然后通过关联查找返回所需要的的列
优化前

select  id, name from film order by title limit 50,5

优化后

select id, name
from film
inner join (
select id from film order by title limit 50, 5
)as lim using(id)

6.优化获取limit总行数

1. 不提供页面跳转功能，直接显示下一页，没有查询时，多查询一条数据，比如查询20条，使用limit 0,21,展示前20条数据，并且如果存在第21条数据，则显示下一页的按钮
2. 先获取幷缓存较多的数据，如缓存1000条，每次分页都从缓存中获取，如果操作了1000条，则提供一个找到结果多余1000条的按钮，在去缓存另外的数据

7.优化union查询

1. 如果不需要消除重复的行，尽量使用union all，因为这样可以减少对整个临时表的唯一性检查
2. 将查询条件下推到union子查询中，例如直接冗余查询条件

8.优化select for update

可以使用先update后select的方法来代替select for update，这样可以减少锁的竞争，比如我们需要先查询需要处理的数据，然后对数据进行处理后更新，这时候我们在查询的时候可能会使用for update来锁住将处理的行，但这样会阻塞其他的类似的查询，这时候，我们就可以先将数据更新为我们处理后的状态，然后在查询出来进行真正的处理