MySQL专题(八)：聚簇索引和非聚簇索引的深度分析设计索引时候需要考虑到的因素深入研究explain命令得到的sql执行进行分析多表关联的sql语句是如何执行的?

本文链接：https://blog.csdn.net/weixin_46950473/article/details/120036695

本文探讨了MySQL中非主键字段的二级索引运作机制，解释了索引建立过多的利弊，强调了查询时如何利用索引来排序。介绍了回表查询的影响以及覆盖索引的概念。此外，还分享了索引设计的考虑因素，如字段选择和基数。讨论了SQL优化，包括执行计划分析、多表关联查询的执行方式以及MySQL如何根据成本选择执行计划。最后，通过explain命令分析SQL执行计划，提供了SQL调优的方法和思路。

摘要由CSDN通过智能技术生成

前言：

玩转MySQL索引

1.针对主键之外的字段建立的二级索引，又是如何运作的？
2.一个表中是不是索引弄的越多越好？
3.在SQL中进行排序的时候，如何才能使用索引？
4.回表查询对性能的损害以及覆盖索引是什么？
5.几个常见和最基本的索引使用规则
6.设计索引的时候，需要考虑到的因素
7.MySQL的查询语句的执行计划分析,以及SQL优化
8.多表关联的sql语句是如何执行的?
9.MySQL是如何根据成本优化选择执行计划的?
10.深入研究explain命令得到的sql执行进行分析

1.针对主键之外的字段建立的二级索引，又是如何运作的？

1.1：给非主键建立索引的原理，例如name，age这些字段；

1.会将完整数据插入到聚簇索引的叶子节点的数据页中，同时维护好聚簇索引
2.为建立的二级索引，实际上也叫非聚簇索引重新建立一颗B+树例如基于name字段建立了一个索引，那么此时插入数据的时候，就会重新搞一颗B+树，B+树的叶子节点也是数据页，《但是这个数据页中仅仅放的是主键字段和name字段》,同时叶子节点的数据页中的name值都是按照大小排序的，同时下一个数据页的name字段值都大于上一个数据页的name字段值，整体的排序规则和聚簇索引的一样，同时name字段的索引B+树也会构建多层级的索引页，这个索引页中存放的就是下一层的页号和最小的name字段值，整体的规则和聚簇索引是一眼的，只不过数据页中存放的值不一样罢了。

1.2：二级索引也就是非聚簇索引查找方式

搜索的过程和聚簇索引的搜索过程是一样的，一层层的向下查找，一直找到叶子节点的数据页，定位到name对应的主键值，注意：此时找到叶子节点也只是仅仅找到主键值，是无法找到这行数据完整的所有字段的，所以引出了“回表”也就是再根据主键回到聚簇索引中从根节点开始，一路找到数据页，定位到主键对应的完整数据行。

1.3：插入数据时到底如何维护好不同索引的B+树的？

数据页满了，页分裂，索引页满了，索引页分裂原则。

2.一个表中是不是索引弄的越多越好？

友情提示：要是你的产品经理这样给你说，那就原地怼他！

2.1：不管是聚簇索引还是非聚簇索引都有一个不变的原则和定律：

《在一个数据页/索引页中的数据行都是组成一个单向链表的，而且时按照数据大小有序排序的》，《然后数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据大小有序排列的，所以其实B+树索引是一个完全有序的数据结构，无论时页内，还是页之间》

2.2：在MySQL的表中创建其他字段索引的好处和坏处：

好处：根据B+数的数据的规则，查询效率是极高的。
坏处1：空间上：创建一个字段索引，就会创建一个B+树，每一颗B+树都要占用很多的磁盘空间，所以索引搞的太多了，是十分耗费磁盘空间的。
坏处2：不停的增删改，会导致各个数据页之间的值大小可能没顺序，会不断的页分裂，移动，维护页之间的顺序，或者不停的插入数据，索引的数据页就要不停的分裂，不停的增加新的索引页，是非常耗时的。查询确实有所提升，但是牺牲增删改的性能。

3.在SQL中进行排序的时候，如何才能使用索引？

使用order by，group by关键字语句进行排序的时候，如何才能用上索引呢？

如果group by利用了索引，那么group by的操作直接是在索引上进行的，如果没有用到索引，那么group by，order by的操作是先从索引将数据加载到临时文件或者是内存中，然后再进行分组统计操作。特性：先回表再排序。

4.回表查询对性能的损害以及覆盖索引是什么？

回表就是根据非聚簇索引在B+树中找到对应的主键，回表后再通过主键进行聚簇索引查找所对应的数据行。select * from table order by xx1,xx2,xx3 limit
10，此时执行引擎就知道了，需要先扫描联合索引的索引树拿到10条数据，接着对10条数据再聚簇索引中查找10次数据。
查询结果集映射是可以有效的避免数据回表的，例如：select name，age，classes from table order by name，age，classes；这种情况下仅仅需要联合索引中的几个字段的值，那么其实就只要扫描联合索引的索引树即可，不需要回表！

5.几个常见和最基本的索引使用规则

where语句的几个字段名称和联合索引的字段一样
最左列匹配例如(a，b)，查询where a =1 and b=2
最左前缀匹配原则，a like “1%”
范围查找规则 where a>1 and b>2
等值匹配+范围匹配规则

总结：一般写sql都是用联合索引的最左侧的多个字段来进行等值匹配+范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者是基于最左侧字段来进行范围搜索，才能使用建好的联合索引。

6.设计索引的时候，需要考虑到的因素

6.1：代码先行原则，索引随后，首先要考虑到的是字段需要经常出现再where条件之后的，联合索引需要从左到右原则实现。

6.2：建立索引，尽量使用基数比较大的字段，就是值比较多的字段，这样才能发挥出B+树快速进行二分查找的优势出来。<精良对字段类型比较小的列来设计索引,字段类型小,就说明字段本省就不占用太多磁盘,此时搜索性能也高>。

6.3：尽量利用一个两复杂的多字段联合索引,满足80%以上的查询,然后用1-2个辅助索引抗下剩余20%的非典型查询,保证99%以上的查询都能充分利用索引,就能保证查询速度和性能

7.MySQL的查询语句的执行计划分析 AND SQL优化

【一图解君愁】

8.多表关联的sql语句是如何执行的?

8.1：其实和单表的查询原理是一样的，只不过变成了多张表而已；

例如：在inner join/right join下on和where是等价的,关联条件就是on,inner join下查询优化器会将on优化为where,例如在left join下on操作不管条件是否满足都会返回左边中的数据再根据where去筛选结果。

8.2：为什么有的时候多表关联查询很慢呢?

两个表关联，先需要从驱动表中根据where条件去筛选一波数据，这个过程如果没给驱动表加索引，万一走一个all全表扫描，岂不是速度很慢？好不容易扫描出一波数据，接着又来一个on和where条件去筛选和比对，效率就更加低了。

9.MySQL是如何根据成本优化选择执行计划的?

MySQL执行单表查询/多表查询的时候，都有很多中计划可以选择，例如全表扫描,索引A,索引B,到底是用哪种执行计划呢?

如何计算SQL执行成本？
9.1：输入命令：

show table status like ‘表名’；

9.2：MySQL会给出维护这个表的一些统计信息，会看到rows和data_length两个信息；

不过对于innodb来说，这个rows是估计值。rows就是表里的记录数data_length就是表的聚簇索引的字节数大小，用data_length/1024就是kb单位的大小,再除16KB就是有多少个数据页,刺死知道数据页的数量和rows记录数,就可以计算全表扫描的成本了。

9.3：索引查询的两种方式[聚簇索引/非聚簇索引]

9.3.1：直接走聚簇索引查询就行
9.3.2：走完非聚簇索引回表后再走聚簇索引

9.4：成本计算：

普通索引查询一个区间,假设加载一个数据页到内存中io操作成本数据1.0,假设读取到100行数据,数据在普通索引搜索CPU成本0.2100普通索引区间查询100条数据陈哥不能就是21;如果需要回表查询就需要去聚簇索引页查询每个id,如果对应一个数据页则需要查询100次,io成本100,同理cpu成本100*0.2,总成本141。

10.深入研究explain命令得到的sql执行进行分析

explain执行计划
explain
explain执行计划参数分析

10.1：extra的深度分析
常见的4种结果：

Using index ：仅仅在二级索引中执行，没有进行回表操作
Using index condition：查询出来的结果还是会进行二次比对，筛选除满足条件的结果集
Using where：没用到索引，直接针对一个表进行扫描，用到了1个或多个where条件筛选《或者是用了索引，但是除了索引之外还需要用其他字段进行where》
NULL：一般ref是否有值，是否使用了常量查询

10.2：题外分析：Join Buffer （看到buffer就是缓冲区就完事了）
Join buffer概念