MySQL 高级(2): 索引优化（关联，排序，分组，案例）

2022-04-23 02:45:39

准备

【优化总结口诀】
全值匹配要记牢，最左前缀不能忘；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
引号不可丢，丢了就失效。

在讲解之前，先创建两个表：

CREATE TABLE IF NOT EXISTS `class` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
	`card` INT(10) UNSIGNED NOT NULL, 
	PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
	`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`card` INT(10) UNSIGNED NOT NULL, 
	PRIMARY KEY (`bookid`)
);

插入数据：

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

关联查询优化

左联查询（left join）

查询 class.card = book.card 的所有 class：
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

可以看到类型为 all，接下来我们建立索引，对其进行优化：此时只需要 card 的索引即可。那么是添加左边的表的card为索引？还是添加右边的表呢？
添加右边的表 book 的card为索引
```
ALTER TABLE `book` ADD INDEX idx_card( `card`);
```
查询结果如下：
添加左边的表 class的card为索引
在此之前，应该先删除已有的 book 索引：：drop index idx_card on book;
添加 class索引：
```
ALTER TABLE `book` ADD INDEX idx_card( `card`);
```
查询结果如下：
总结：
① 在优化关联查询时，只有在被驱动表上建立索引才有效！
②left join 时，左侧的为驱动表，右侧为被驱动表！

内联查询（inner join）

运行下面两条查询语句观察结果：

EXPLAIN SELECT * FROM book inner join class on class.card=book.card;
和 
EXPLAIN SELECT * FROM class  inner join book  on class.card=book.card;

结果如下：

结论1：可以看到两个查询字段调换顺序，结果是一样的！
在 book 表中，删除 9 条记录然后查询结果：

结论2：：inner join 时，mysql 会自己帮你把小结果集的表选为驱动表，将大结果集的表设置为非驱动表。在非驱动表上加索引才有用
使用 straight_join 观察结果：

结论3：：straight_join::效果和 inner join 一样，但是会强制将左侧作为驱动表！

四关联案例查询分析

场景1：

先查询再左联（将子查询部分驱动表位置）

EXPLAIN SELECT ed.name '人物',c.name '掌门' FROM
(SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed
LEFT JOIN t_emp c on ed.ceo= c.id;

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

先左联在查询（将子查询部分放入了被驱动表中）

EXPLAIN SELECT e.name '人物',tmp.name '掌门' FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp
ON e.deptId=tmp.did;

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

上述两个案例，第一个查询效率较高，且有优化的余地。第二个案例中，子查询作为被驱动表，由于子查询是虚表，无法建立索引，因此不能优化。
结论：
1、子查询尽量不要放在被驱动表，有可能使用不到索引；
2、left join时，尽量让实体表作为被驱动表。

场景2：

不用子查询，直接左联

EXPLAIN SELECT e1.name '人物',e2.name '掌门' FROM t_emp e1
LEFT JOIN t_dept d on e1.deptid = d.id
LEFT JOIN t_emp e2 on d.ceo = e2.id ;

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

使用子查询

Explain SELECT e2.name '人物', (SELECT e1.name FROM t_emp e1 where e1.id= d.ceo) '掌门' from t_emp e2 LEFT JOIN t_dept d on e2.deptid=d.id;

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

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

子查询优化

原场景：

取所有不为掌门人的员工，按年龄分组！

select age as '年龄', count(*) as '人数' from t_emp where id not in (select ceo from t_dept where ceo is not null)

结果：

优化方案1：
解决 dept 表的全表扫描，建立 ceo 字段的索引：
进一步优化：

去除子查询，改为多表关联

 select age as '年龄',count(*) as '人数' from emp e left join dept d on e.id=d.ceo where d.id is null group by age;

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

排序分组优化

where 条件和 on 的判断这些过滤条件，作为优先优化的部门，是要被先考虑的！其次，如果有分组和排序，那么也要考虑 grouo by 和 order by。

MySQL 支持二种方式的排序 FileSort 和 Index。Index（使用已建立好的索引直接查找即可）效率高.它指MySQL扫描索引本身完成排序。FileSort（使用快排）方式效率较低。
ORDER BY满足两情况，会使用Index方式排序:
- ORDER BY 语句使用索引最左前列
- 使用 Where 子句与 Order BY 子句条件列组合满足索引最左前列即最左匹配法则
- where 子句中如果出现索引的范围查询(即explain中出现range)会导致 order by 索引失效。（范围之后全失效）

无过滤不索引

无过滤，不索引。where，limt 都相当于一种过滤条件，只有拥有这些过滤条件才能使用上索引！

实例： 创建索引

create index idx_age_deptid_name on emp (age,deptid,name);

有 where 的查询语句 : explain select * from emp where age=40 order by deptid;
无 where 的查询语句 : explain select * from emp where age=40 order by deptid;
无limit 的查询语句： explain select * from emp order by age,deptid;
有 limit 的查询语句： explain select * from emp order by age,deptid limit 10;

顺序错，必排序:（filesort）

结论1： 当排序中存在为建立索引的字段，会产生 filesort

索引中都有的字段： ①explain select * from emp where age=45 order by deptid,name;
出现 empno 这种索中没有的字段时：②explain select * from emp where age=45 order by deptid,empno;

结论2： where 两侧列的顺序可以变换，效果相同，但是 order by 列的顺序不能随便变换！

与结论1中的第一个查询相对应，这次修改 order by 后面的列名顺序：③explain select * from emp where age=45 order by name,deptid;

结论3： 最左匹配。必须先 age 再id。顺序错，还是会调用 filesort。

代码如下：④explain select * from emp where deptid=45 order by age;

方向反，必排序（filesort）

在order by 是都选择正序或者逆序都可以直接使用 index 树：
①explain select * from emp where age=45 order by deptid desc, name desc ;
在 orderby 时，使用相反的排序方法（一个正序一个逆序）就会造成 filesort
②explain select * from emp where age=45 order by deptid asc, name desc ;

索引的选择（排序 or 过滤？）

①首先，清除 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;

此时没有索引，使用了 filesort
③全表扫描肯定是不被允许的，因此我们要考虑优化。
思路： 首先需要让 where 的过滤条件，用上索引；
查询中，age 和 empno 是查询的过滤条件，而 name 则是排序的字段，因此我们来创建一个此三个字段的复合索引：
create index idx_age_empno_name on emp(age,empno,name);

empno 是范围查询，因此导致了索引失效，所以 name 字段仍然无法使用索引排序。
由于所有的排序都是在条件过滤之后才执行的，所以此时 empno 的范围查询和索引排序中只能有一个能够使用索引。
结论： 当范围条件和 group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。 反之，亦然。

filesort

具体的排序算法

①双路排序
MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据：
- 读取行指针和 orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
- 从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。
- 简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。
②：单路排序
- 从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，
- 它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
- 即一次性取出需要的数据，然后排序

单路排序的问题

由于单路是后出的，总体而言好过双路。但是存在以下问题：

在 sort_buffer 中，单路排序比双路排序要多占用很多空间
因为单路排序是把所有字段都取出来, 有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。
结论： 本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

为了能够减少 IO 的次数，我们可以通过下面这些操作进行调节：

①增大sort_buffer_size参数的设置。增大缓存的大小，自然可以使一次 IO 读取更多条数据进行操作。两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些, 所以要提高sort_buffer_size。
②增大 max_length_for_sort_data 参数的设置：由于排序需要一定的内存空间，因此每次排序的条数必须小于 max_length_for_sort_data。该参数值一定小于 sort_butter_size。通过增加一次性排序的条数，保证一个 sort_buffer 中的数据能够更快排序完成。
减少 select 后面的查询的字段:
当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的
算法——单路排序，否则用老算法——多路排序。

覆盖索引的使用

SQL 只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

group by

group by 使用索引的原则几乎跟 order by 一致，唯一区别是 groupby 即使没有过滤条件用到索引，也可以直接使用索引。

索引优化案例

案例一：列出自己的掌门比自己年龄小的人员

select e1.name empname,e1.age empage,e2.name ceoname,e2.age ceoage
# 内联，找到有门派的所有成员（内联不包含没有门派的成员）
from t_emp e1 inner join t_dept d on e1.deptid=d.id
# 内联：找到每个门派对应的掌门的具体信息
inner join t_emp e2 on d.ceo=e2.id
# 判断过滤
where e1.age>e2.age;

如上所示，两个被驱动表都用上了索引。

案例二：列出所有年龄低于自己门派平均年龄的人员

思路： 先取门派的平均年龄，再跟自己的年龄做对比！

select e1.name from t_emp e1
inner join
(select deptid,AVG(age) avgage from t_emp# 内联一个虚表tmp，通过子查询获得
group by deptid) tmp
on e1.deptid=tmp.deptid # 找到 每位成员与之对应的门派
where e1.age<tmp.avgage;# 筛选

结果显示（大表结果）：

可以看到此时没有建立索引，type 为 all
索引优化：
①：首先我们必须先确定谁是驱动表谁是被驱动表：
因为是 inner join,因此会自动将小表作为驱动表，也就是说，分组后的 tmp 是驱动表，而 e1 是被驱动表；由于子查询不为被驱动表，因此可以在被驱动表上进行建立索引进行优化
②：由于该语句主要使用了 deptid 字段，因此需要建立 deptid 为索引。
③而在 e1 中，需要查询 deptid 和 age 两个字段，因此这两个字段也需要建立索引
总结：创建 deptid 和 age 的符合索引: create index idx_deptid_age e on emp(deptid,age);
建立索引的结果如下：

案例三：列出至少有 2 个年龄大于 40 岁的成员的门派

思路： 先查询大于 40 岁的成员，然后按照门派分组，然后再判断至少有 2 个的门派！

select d.deptName,count(*)
from t_emp e inner join t_dept d
on e.deptid=d.id
where e.age>40
group by d.id,d.deptName
having count(*)>=2

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

优化：
①两表关联，我们可以考虑将小表作为驱动表（即dept 为驱动表，emp 为被驱动表）。
②group by 的字段 id,deptName 还可以建立索引: create index idx_id_deptName on dept(id,deptName);
③被驱动表的 deptid 作为关联字段，可以建立索引：create index idx_deptid on emp(deptid);
create index idx_id_deptname on dept(id,deptName);

案例四: 至少有 2 位非掌门人成员的门派

思路：先找所有成员与之对应的门派，然后将这些成员与门派信息进行左联。只有是掌门人的成员具有门派信息。不是掌门人的成员的门派信息位 null。找到这些null，进行分组和判断。
```
select d2.deptName from t_emp e inner join t_dept d2 on e.deptid=d2.id
left join t_dept d on e.id=d.ceo
where d.id is null and e.deptid is not null
group by d2.deptName,d2.id
having count(*)>=2;
```
优化分析： 三个表关联，然后做 group by 分组！
①group by 的字段，可以加上索引：create index idx_deptname_id on dept(deptName,id);
②可以将部门表作为驱动表
③第一次 join 时(内联小表驱动大表)，e 表作为被驱动表，可以将 deptid 设置索引：create index idx_deptid on emp(deptid);
④最有一次 join 中(左联，左边驱动右边驱动)，使用了 dept 表作为被驱动表，查询 ceo 字段，因此可以在 ceo 上面建立索引create index idx_ceo on dept(ceo);

案例五：列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否

思路： 使用左联，然后显示所有成员的门派情况。（是掌门才显示门派情况，不是就显示 null）

select e.name,case when d.id is null then '否' else '是' end '是否为掌门' from t_emp e
left join t_dept d
on e.id=d.ceo;

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

索引优化：
在被驱动表 t_dept 中的 ceo 上建立索引即可。

案例六：列出全部门派，并增加一列备注“老鸟 or 菜鸟”，若门派的平均值年龄>40 显示“老鸟”，否则显示“菜鸟”

思路： 先从 emp 表求出，各门派的平均年龄，分组，然后在关联 dept 表，使用 if 函数进行判断！

select d.deptName,if(avg(age)>40,'老鸟','菜鸟') from t_emp e 
inner join t_dept d on d.id=e.deptid
group by d.deptName,d.id

MySQL 高级(2): 索引优化（关联，排序，分组，案例）

优化：
①使用 dept 作为驱动表
②在 dept 上建立 deptName 和 id 的索引：create index idx_deptName_id on dept(deptName,id);
③在 emp 上建立 deptid 字段的索引： create index index_deptid on emp(deptid);

案例七：显示每个门派年龄最大的人

思路： 先查询所有的员工，根据员工的 deptid 进行分组，找到每组中的最大年龄，形成新表 tmp（包含 depid，和当前最大年龄）。将员工表和 tmp 表内联，以depid和age同时相等为条件。最后就能获得想要的表。
```
select * from T_emp e  
inner join 
(select deptid,max(age) maxage from t_emp group by deptid) tmp
on e.deptid = tmp.deptid and e.age=tmp.maxage
```
优化思路：
①子查询中，emp 表根据 deptid 进行分组，因此可以建立 deptid 字段的索引；
②inner join 查询中，关联了 age 和 deptid，因此可以在 deptid,age 字段建立索引
create index idx_deptid_age on emp(deptid,age);

码农公寓

准备

关联查询优化

左联查询（left join）

内联查询（inner join）

四关联案例查询分析

子查询优化

排序分组优化

无过滤不索引

顺序错，必排序:（filesort）

方向反，必排序（filesort）

索引的选择（排序 or 过滤？）

filesort

具体的排序算法

单路排序的问题

覆盖索引的使用

group by

索引优化案例

案例一：列出自己的掌门比自己年龄小的人员

案例二：列出所有年龄低于自己门派平均年龄的人员

案例三：列出至少有 2 个年龄大于 40 岁的成员的门派

案例四: 至少有 2 位非掌门人成员的门派

案例五：列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否

案例六：列出全部门派，并增加一列备注“老鸟 or 菜鸟”，若门派的平均值年龄>40 显示“老鸟”，否则显示“菜鸟”

案例七：显示每个门派年龄最大的人

相关文章