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

2021/8/25 19:06:22

编程Tag： mysql 索引 分组 age Values Card emp deptid

本文主要是介绍MySQL 高级(2): 索引优化（关联，排序，分组，案例），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

准备

  • 【优化总结口诀】
    全值匹配要记牢，最左前缀不能忘；
    带头大哥不能死，中间兄弟不能断；
    索引列上少计算，范围之后全失效；
    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;

    在这里插入图片描述

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

    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;

    在这里插入图片描述

  • 上述两个案例，第一个查询效率较高，且有优化的余地。第二个案例中，子查询作为被驱动表，由于子查询是虚表，无法建立索引，因此不能优化。

  • 结论：
    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 ;

    在这里插入图片描述

  • 使用子查询

    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;

    在这里插入图片描述

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

子查询优化

原场景：

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

    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;

    在这里插入图片描述

排序分组优化

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

    在这里插入图片描述

  • 优化：
    ①两表关联，我们可以考虑将小表作为驱动表（即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;

    在这里插入图片描述

  • 索引优化：
    在被驱动表 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

    在这里插入图片描述

  • 优化：
    ①使用 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);

在这里插入图片描述



这篇关于MySQL 高级(2): 索引优化（关联，排序，分组，案例）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程