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
评论列表