MySQL实战14 慢查询优化join、order by、gro
1.慢查询的优化思路
1.1优化更需要优化的SQL
优化SQL是有成本的
高并发低消耗的比低并发高消耗影响更大
优化示例
| 并发形式 | 优化前 | 假设优化后 |
|---|---|---|
| 高并发低消耗 | 每小时10000次，每次20个IO | 每小时节约20000次IO，sql要优化后到18个IO |
| 低并发高消耗 | 每小时10次，每次20000个IO | 每小时节约20000次IO，sql要优化到18000个IO |
显然 前者更容器容易优化
1.2定位优化对象的性能瓶颈
1.3明确的优化目标
1.4 慢查询的优化思路
- 从explain执行计划入手
- 永远用小结果集驱动大的结果集
- 尽可能在索引中完成排序
- 只取出自己需要的列，不要用select *
- 仅使用最有效的过滤条件
- 尽可能避免复杂的join和子查询
- 小心使用order by,group by,distinct 语句
2. join优化
永远用小结果集驱动大的结果集(join操作表小于百万级别)
驱动表的定义
当进行多表连接查询时，[驱动表]的定义为：
1）指定了联解条件时，满足查询条件的记录行数少的表为[驱动表]
2）未指定连接条件时，行数少的表为[驱动表]
mysql关联查询的概念
MySQL表关联的算法是Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，最后合并结果。
join的实现原理
- mysql只支持一种join算法：
Nested-Loop Join(嵌套循环连接)
但Nested-Loop Join有三种变种： -
Simple Nested-Loop Join （简单嵌套循环）
image.png
- Index Nested-Loop Join（索引嵌套循环）
image.png
- Block Nested-Loop Join（快嵌套循环）
image.png
-
Block Nested-Loop Join（3表快嵌套循环）
image.png
join的优化思路
- 尽可能减少join语句中的Nested Loop的循环总次数
- 优先优化Nested Loop的内层循环
- 保证join语句中被驱动表上join条件字段已经被索引
- 无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜join Buffer的设置
country ：106条
city ：600条
explain select * from country join city ;
image.png
country ：106条
city ：600条
country_id 都为索引
explain select * from country join city on country.country_id = city.country_id;
image.png
film_actor ：5462条
film：1000条
film 中 film_id 为索引， film_actor 不为索引
explain select * from film join film_actor on film.film_id = film_actor.film_id;
image.png
join的优化思路总结
- 并发量太高的时候，系统整体性能可能会急剧下降
- 复杂的join语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多
- 复杂的Query语句分拆成多个较为简单的Query语句分布执行（超过3张表 不要用join，一个表一个表的查）
3.order by 排序优化
- order by 字句中的字段加索引(扫描索引即可，内存中完成，逻辑io)
explain select city_id from city order by city_id;
image.png
- 若不加锁索引的话会可能启用一个临时文件辅助排序(落盘，物理io)
- order by排序可利用索引进行优化，order by子句中只需要是索引的前导列都可以
使索引生效，可以直接在索引中排序，不需要在额外的内存或者文件中排序 - 不能利用索引避免额外排序的情况，例如：排序字段中有多个索引，排序顺序和索引键顺序不一致(非前导列)
order by排序算法
对于不能利用索引避免排序的SQL，数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort_buffer_size参数与结果集大小确定。MySQL内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序。
a.常规排序，双路排序
- 从表t1中获取满足WHERE条件的记录
- 对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer
- 如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并写到临时文件中。(排序算法采用的是快速排序算法)
- 若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的
- 循环执行上述过程，直到所有满足条件的记录全部参与排序
扫描排好序的(id,col2)队，即sort buffer，并利用主键id去取SELECT需要返回的其他列(col1,col2,col3)- 将获取的结果集返回给用户。
从上述流程来看，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)的结果集，这个buffer的大小由
sort_buffer_size参数控制。此外一次排序还需要两次IO，一次是取排序字段(id,col2)到sort buffer中，第二次是通过上面取出的主键id再来取其他所需要返回列(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id取(col1,col2,col3)时会产生大量的随机IO。对于第二次IO取MySQL本身会优化，即在取之前先将主键id排序，并放入缓冲区，这个缓存区大小由参数read_rnd_buffer_size控制，然后有序去取记录，将随机IO转为顺序IO。
b.优化排序，单路排序，max_length_for_sort_data
常规排序方式除了排序本身，还需要额外两次IO。优化排序方式相对于常规排序，减少了第二次IO。主要区别在于，一次性取出sql中出现的所有字段放入sort buffer中而不是只取排序需要的字段(id,col2)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次取数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数
max_length_for_sort_data，只有当排序sql里出现的所有字段小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式。
c.优先队列排序
为了得到最终的排序结果，我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式，是否还有优化空间呢？5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式--优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M,N 这类排序的问题，虽然仍然需要所有字段参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。
总结：分别在查询字段、where条件、排序字段上做出各种可能的组合，主要就是看有无索引，索引在以上三个关注点上的生效情况
4.group by 分组优化
由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
category 中的name 没加索引
explain select min(name) from category group by name;
image.png
film 中的 title 加了索引
explain select min(title) from film group by title;
image.png
4.1 group by的类型
- 三种实现类型
Loose Index Scan(松散的索引扫描)
explain select actor_id, max(film_id) FROM film_actor GROUP BY actor_id;
image.png
explain select actor_id, max(film_id) FROM film_actor where film_id > 10 GROUP BY actor_id;
image.png
Tight Index Scan(紧凑的索引扫描)
Using temporary 临时表实现(非索引扫描)
explain select max(first_name) FROM actor GROUP BY first_name;
image.png
5.distinct 分组优化
DISTINCT 实际上和 GROUP BY 操作的实现非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以，DISTINCT 的实现和 GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成 DISTINCT 的时候，MySQL 只能通过临时表来完成。但是，和 GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序。也就是说，在仅仅只是 DISTINCT 操作的 Query 如果无法仅仅利用索引完成操作的时候，MySQL 会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行 filesort 操作。当然，如果我们在进行 DISTINCT 的时候还使用了 GROUP BY 并进行了分组，并使用了类似于 MAX 之类的聚合函数操作，就无法避免 filesort 了。
explain select distinct country_id from city;
image.png
explain select distinct country_id from city where city_id > 100;
image.png
