MySql教程

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

本文主要是介绍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): 索引优化(关联,排序,分组,案例)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!