1. 关联查询优化

1.1 left join

①EXPLAIN SELECT FROM class LEFT JOIN book ON class.card = book.card;
image.png
②如何优化？在哪个表上建立索引？
ALTER TABLE book ADD INDEX idx_card( card);
image.png
③删除book 表的索引：drop index idx_card on book;
在class 表上建立索引：alter table class add index idx_card(card);
image.png
结论：
①在优化关联查询时，只有在被驱动表上建立索引才有效！
②left join 时，左侧的为驱动表，右侧为被驱动表！
*

1.2 inner join

①EXPLAIN SELECT FROM book inner join class on class.card=book.card;
image.png
②两个查询字段调换顺序，发现结果也是一样的！
image.png
*③结论：inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。
【tip】

  • 子查询尽量不要放在被驱动表，有可能使用不到索引；
  • left join时，尽量让实体表作为被驱动表。

  • 能够直接多表关联的尽量直接关联，不用子查询！

    2. 排序分组优化

    2.1 索引的选择

    ①首先，清除emp 上面的所有索引，只保留主键索引！
    drop index idx_age_deptid_name on emp;
    ②查询：年龄为30 岁的，且员工编号小于101000 的用户，按用户名称排序
    explain SELECT SQL_NO_CACHE FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
    image.png
    ③全表扫描肯定是不被允许的，因此我们要考虑优化。
    思路：首先需要让where 的过滤条件，用上索引；
    查询中，age.empno 是查询的过滤条件，而name 则是排序的字段，因此我们来创建一个此三个字段的复合索引：
    create index idx_age_empno_name on emp(age,empno,name);
    image.png
    再次查询，发现using filesort 依然存在。
    原因： empno 是范围查询，因此导致了索引失效，所以name 字段无法使用索引排序。
    所以，三个字段的符合索引，没有意义，因为empno 和name 字段只能选择其一！
    ④解决： 鱼与熊掌不可兼得，因此，要么选择empno,要么选择name
    drop index idx_age_empno_name on emp;
    create index idx_age_name on emp(age,name);
    create index idx_age_empno on emp(age,empno);
    两个索引同时存在，mysql 会选择哪个？
    image.png
    explain SELECT SQL_NO_CACHE     FROM emp use index(idx_age_name) WHERE age =30 AND empno <101000 ORDER BY NAME ;
    image.png
    原因：所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，使用empno 字段的范围查询，过滤性更好（empno 从100000 开始）！
    结论： 当范围条件和group by 或者order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

  • 2.2 常见能使用索引进行排序和不能使用索引进行排序的情况image.png

  • 升降序不一致，也无法使用索引排序


2.3 using filesort

2.3.1 mysql 的排序算法

①双路排序

MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的。
②单路排序
从磁盘读取查询需要的所有列，按照order by 列在buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO 变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。（即第一次读取磁盘的时候，就把要查询的列也一并读取了，也就是空间【内存】换时间）
③单路排序的问题
由于单路是后出的，总体而言好过双路。但是存在以下问题：
在sort_buffer 中，方法B 比方法A 要多占用很多空间，因为方法B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer 的容量，导致每次只能取sort_buffer 容量大小的数据，进行排序（创建tmp 文件，多路合并），排完再取取sort_buffer 容量大小，再排……从而多次I/O。
结论：本来想省一次I/O 操作，反而导致了大量的I/O 操作，反而得不偿失。

2.3.2 如何优化

①增大sort_butter_size 参数的设置
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的1M-8M 之间调整。
②增大max_length_for_sort_data 参数的设置
mysql 使用单路排序的前提是排序的字段大小要小于max_length_for_sort_data。
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size 的概率就增大，明显症状是高的磁盘I/O 活动和低的处理器使用率。（1024-8192 之间调整）。
③减少select 后面的查询的字段。
当Query 的字段大小总和小于max_length_for_sort_data 而且排序字段不是TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
两种算法的数据都有可能超出sort_buffer 的容量，超出之后，会创建tmp 文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
【注】
sort_buffer_size是order by字段存储空间；max_length_for_sort_data是排序的所有字段.

3.小表驱动大表

image.png
image.png
image.png
【注】

  • in是吧外表和内表做了hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表不管多大都需要查询，不可避免。
  • exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免。