【Mysql】小白大话Mysql B-Tree 索引

目录

先验知识

建立索引可以参考哪些规则？

有无索引的区别及索引字段的选择：

哪些地方的索引可能会用到呢？

估算SQL语句执行的时间

三星索引

一张总结图

---

参考1：[灵魂拷问]MySQL面试高频100问(工程师方向)_Java团长在csdn的博客-CSDN博客

参考2：面试官:谈谈你对mysql索引的认识？_孤独烟的博客-CSDN博客

参考3： 不懂数据库索引的底层原理？那是因为你心里没点b树

先验知识

索引是一种空间换取时间的手段，会占用存储空间，会使插入更新变慢。索引是根据某业务sql或某些业务sql准备的。

索引的缺点：

1. 占用磁盘空间。
2. 对dml（插入、修改、删除）操作有影响，会变慢。

各种索引：

1. 普通索引：允许重复的值出现
2. 唯一索引：除了不能有重复的数据记录外，和普通索引一样
3. 主键索引：是随着设定主键而创建的，也就是说把某个列设为主键的时候，这个时候数据库会给该列创建索引，注意！！主键索引是唯一的且不能是null值
4. 全文索引：整个表中的文本域都是索引

添加索引
1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

在mysql中，索引的建立用到了B+树。

B树：利用< 、> 分区递归地查找直到找到节点。

B+树：利用<、>=分区递归地找到在最下层有序数据流（最下层节点）所要找的值的位置。

B树与B+树动态演示：Data Structure Visualization 

索引分类1

（按节点数据分）聚簇索引与非聚簇索引

聚簇索引：最下层节点跟的是对应的其它字段信息。

非聚簇索引：最下层节点跟的是对应主键或定位值。

mysql中，主键是聚簇索引，其他索引是非聚簇索引

非聚簇索引依赖于聚簇索引发挥作用，因为非聚簇索引最下层节点跟的是对应主键，找到了主键还需要利用主键的聚簇索引找到该主键与对应数据

那如果没有主键怎么办？其他数据怎么找？

是这样的，mysql中如果没指定主键，mysql会在内部虚拟的创建主键，类似自增id，也就是mysql的主键是一定会有的，就算你不声明，因为它需要通过聚簇索引来寻找完整的行记录

所以，mysql有一条最佳实践：

一定要自己创建主键。能够用上主键的查询是最快的，mysql内部也会尽量用主键索引。

索引分类2

（从数据结构角度）: B+树索引、hash索引、FULLTEXT索引、R-Tree索引

索引分类3

（从索引类型角度）普通索引、唯一索引、主键索引、全文索引、复合索引

插播：

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.

建立索引可以参考哪些规则？

(1)索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能

(2)避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引

(3)数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

(4)在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度

(5)在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。

有无索引的区别及索引字段的选择：

举个例：

注：salary、sex是普通字段

SELECT * FROM test WHERE salary = 30000

• 无索引：那就只能一行一行找了
• 有索引: 通过salary字段的索引构建的B+树找到 salary=30000 的节点，因为普通字段的索引是非聚簇的，所以此时找到的是salary=30000的节点及对应的主键值（定位值），因为主键值（定位值）是唯一的，所以再用此主键值通过主键索引的B+树找到所需要的数据。

这里有个问题：如果语句是下面这样的呢？

SELECT * FROM test WHERE sex = '男'

• 无索引：那也只能一行一行找了
• 有索引: 通过sex字段的索引构建的B+树找到 sex = '男' 的节点，因为普通字段的索引是非聚簇的，所以此时找到的是 sex = '男' 的节点及对应的主键值（定位值），但因为sex只有男和女，所以找到符合条件的节点会很多，会有很多（sex=‘男’+对应的主键值），所以还要用这些主键值通过主键索引的B+树分别一个一个找到所需要的数据。

可以看出：

索引虽可以加快检索，但建在哪个字段，哪些字段也是很值得考究的，因为你看，Salary工资的值是比较多的，而sex的值只有男和女，对于sex这种取值太少的字段，某种“过滤性”太差”，也就是过滤掉的太少，导致（“男”+对于主键值）条数太多，有太多的主键值还要继续找才可以找到对应记录，而Salary过滤性好就要找的主键值和对应记录比sex要找的少得多。所以这里又引申出一个概念叫做过滤因子。

过滤因子(大白话)：描述的是关键字（on、where）的选择性，即满足条件的记录占总数的比例，主要依赖列值的分布情况。（只考虑普通字段，因为mysql中如果没指定主键，mysql会在内部虚拟的创建主键，类似自增id，也就是mysql的主键是一定会有的，就算你不声明，因为它需要通过聚簇索引来寻找完整的行记录）

• 对于单个字段: 建索引的时候选择取值较分散、较多的，也就是能让where等语句的过滤因子比较大的。
• 对于多个字段：
  • 如果字段无相关性，则某过滤因子（A,B）= 过滤因子（A） * 过滤因子（B）
  • 如果字段有相关性，则某过滤因子（A,B）= min （过滤因子（A） * 过滤因子（B））  木桶的短板效应嘛 A∩B

再看这种情况：

注：Id 是我们的primary key

SELECT Id FROM test WHERE salary = 30000

SELECT Id FROM test WHERE sex = '男'

这个时候我们要的字段不是全部字段了，我们只要id就够了。若我们已经再这两个字段上建立了索引，比较一下：

salary ：通过salary字段的索引构建的B+树找到 salary=30000 的节点，因为普通字段的索引是非聚簇的，所以此时找到的是salary=30000的节点及对应的主键值（定位值），因为我们想要的只是主键值，所以我们只要把找到的主键值select出来就好了，根本不用再利用主键的聚簇索引去找其他对应的字段信息。

sex ： 通过sex字段的索引构建的B+树找到 sex = '男' 的节点，因为普通字段的索引是非聚簇的，所以此时找到的是 sex = '男' 的节点及对应的主键值（定位值），因为我们想要的只是主键值，所以我们只要把找到的主键值select出来就好了，根本不用再利用主键的聚簇索引去找其他对应的字段信息。

此时sex找的可能更快，因为它的B+树比较次数比salary少。

所以这里又可以引申出一个概念：全覆盖索引

全覆盖索引(大白话)：如果某条SQL语句select中的列全部被包含在列的索引中，那么这个索引叫做这条SQL语句的全覆盖索引

哪些地方的索引可能会用到呢？

一般有三种情况可能会用到索引：

1.  where中
2. 需排序（group by、order by）
3. 全覆盖索引

那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?

• 使用不等于查询,

• 列参与了数学运算或者函数

• 在字符串like时左边是通配符.类似于'%aaa'.

• 当mysql分析全表扫描比使用索引快的时候不使用索引.

• 当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

ps：没用到的情况mysql就会进行全表扫描。

对单索引：idx(A)

• 用到索引的情况：
  • where A = 'xxx'  (= , > , < ,  >= , <=)
  • where A = 'xxx' and B = 'xxx' (因为此时A有过滤性)
  • where A  like '李%'
  • where A  between 'xxx' and 'uuu'
• 没用到索引的情况：
  • where A = 'xxx'  or  B = 'xxx' (因为此时A可能已经没有了过滤性)
  • where A  like '%李'
  • where A != 'xxx' (此时又得一个一个判断了)

对复合索引：idx(A,B,C)

• 用到索引的情况：
  • where后只有A
  • where后只有A和B
  • where有A和B和C
• 没用到索引的情况：
  • where只有B
  • where只有C
  • where只有B和C

为啥呢？（order by A，B）

因为idx(A,B)先构建了一棵关于A的B+树（带有B的值），再对找到的 相同值的A节点及其B 构建关于B的B+树（因为此时具有相同值的A里带有的B是无序的），构建完同一组A组的B值也就是有序了（不同组A值的B值是无序的，即where A > ‘xxx’ and B = 'xxx'，因为找到的是A的一个范围）。

查找的时候是先按A查找，然后如果对B有要求，然后再进行进一步的抽取。所以如果where没有A，索引idx(A,B,C)是派不上用场的。

如上图，经过第一步构建了一棵关于A的B+树，最下层节点中A是有序的，而B是无序的。

第二步：再对找到的 相同值的A节点及其B 构建关于B的B+树（因为此时具有相同值的A里带有的B是无序的），构建完同一组A组的B值也就是有序了（但不同组A值的B值是无序的）

而如果是idx(A),idx(A)先构建了一棵关于A的B+树，再根据过滤之后剩余的A值和主键值去主键索引寻找关于B的信息，然后再进行过滤。

where中有A和B可以用上索引，但是如果是where A > 0 and B = 3的效果怎么样呢？

如下图，经过第一步构建了一棵关于A的B+树，最下层节点中A是有序的，而B是无序的。

第二步：再对找到的 相同值的A节点及其B 构建关于B的B+树

构建完顺序变为 A值有序，同组A值中B值有序，不同组A值中B值无序

（0,20），（1，2），（1,11），（2,1），（2,3）

查找的时候是先按A查找，此时 A>0 找到的是一个范围，即符合大于0的A就好，所以有（1，2），（1,11），（2,1），（2,3）符合条件，因为对B有要求，所以分为两个相同A值的组：

组一：（1,2），（1,11）

组二：（2,1），（2,3）

第一棵：查找 B=3，找不到

A省略了

第二棵：查找 B =3 ，找到了，再根据此A=2，B=3这个信息去进一步获取信息

A省略了

所以可以看出：如果先查找A时，找到了相同A值的组有很多，对这些每个组都要再去判断B值，此时索引的可用性就大大下降了！

估算SQL语句执行的时间

对主键（聚簇索引）

对非聚簇索引

三星索引

三星索引：好的索引应该长成什么样子

不满足三星索引

过滤因子太小不用索引

一张总结图