查询优化

查询慢的原因#

• 网络
• CPU
• IO
• 上下文切换
• 系统调用
• 生成统计信息
• 锁等待时间

优化数据访问#

减少访问数据量的方式进行优化#

• 确认应用程序是否在检索大量超过需要的数据
• 确认mysql服务器层是否在分析大量超过需要的数据行

是否向数据库请求了不需要的数据#

• 查询不需要的记录
• 多表关联时返回全部列
• 总是取出全部列
• 重复查询相同的数据

执行过程的优化#

查询缓存（MySQL8之后移除）#

LRU淘汰策略

JoinBuffer（待更新）#

排序优化#

两次传输排序

• 第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行
• 这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序
• 需要去读取所有记录而此时更多的是随机IO读取数据成本会比较高
• 两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

单次传输排序

• 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果
• 此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO
• 问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

排序的列的总大小超过 max_length_for_sort_data 定义的字节，mysql会选择双次排序，反之使用单次排序

优化特定类型的查询#

优化count()查询#

• count(1)，count(*)，count(col) 效率是一样的
• myisam的count函数，没有任何where条件的count(*)才是比较快的
• 使用近似值（hyperloglog）
• 复杂的优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统

优化关联查询#

• 确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
• 确保任何的group by和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

优化子查询#

• 子查询的优化最重要的是尽可能使用关联查询代替
• 因为子查询结果会产生一个临时表

优化group by 和 distinct#

• 关联查询做分组，按某个列进行分组，采用查找表标识列分组查找效率高

select actor.first_name, actor.last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.first_name, actor.last_name

select actor.first_name, actor.last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.actor_id;

如果表标识列有重复字段上面这种写法会造成数据合并

using与on的区别，参考文章：https://www.cnblogs.com/YC-L/p/14461585.html

优化limit查询#

• 使用覆盖索引，而不是查询所有的列

explain select film_id,description from film order by title limit 50,5

explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

覆盖索引，参考文章：

优化union查询#

• 如果没有规定过滤重复数据，就用union all
• union会合并重复数据，本质上是mysql在查询过程中使用了distinct