mysql索引浅析

比眉伴天荒 2024-04-17 19:45 137阅读 0赞

# mysql索引浅析 #

注：1. 本文上下文索引的语境是在mysql innodb存储引擎下的索引

## 索引的概念 ##

索引是一个数据结构，能够加快sql查询的速度。具体为什么能加快查询，本文后面会详细说明。  
生活中的索引例子：

1.我们在字典中找某个单词，先找找到单词的第一个字母，缩小了26分之一的范围，  
然后之后的字母继续按照这种方式查找。

2.当我进入一个商城的按照分类找到我们要去的具体的店，一般商城统一分类的店会在同一层，并且会在  
一楼有楼层索引，我们寻找的时候，对着楼层索引，很快就能找到我们要找的店在几楼哪个位置。

3.我们进入超市买东西，当想买一瓶冰红茶的时候，我们会走到饮料去的冰箱旁，然后缩小范围  
找到茶类饮料，最后就在很小的一个范围内寻找我们想要的商品。

## 为什么需要索引 ##

从上面生活中的示例，我们可以看到，当数据量比较大的时候，我们希望快速找到想要的数据，  
使用索引能够节省我们的时间。这也是索引出现的原因，**为了加快Sql查询**。

## 索引的数据结构 ##

1.  以上面字典的例子，如果没有26字母的索引，当我们要查询一个单词的时候，  
    我们会从第一页，一页一页匹配到我们要查询的单词。这个查询用数据结构来理解，  
    就是我们遍历整个数组直至符合匹配。我们知道数组的时间复杂度是O(N)。
2.  上面的商城某家店，如果没有楼层索引，但我们已知我们要找的店在那一层，我们直接上到某一层，  
    为了加快找到店的时间，我们从这层楼的中间(假设分布符合最优的二分查找)，往另一边寻找。这里寻找用的数据结构还是数组，  
    算法是二分查找，这里我们相比之前整个数组遍历的时间复杂度少了些,时间复杂度:O(log2n)。

在回到字典的例子，我们现在使用26个字母索引的方式来查找。那么此时我们的数据结构就是平衡二叉树，  
时间复杂度是O(log(N))。

### 选择用那种数据结构呢 ###

思考一下，如果现在Mysql让你设计实现索引的数据结构，你会选哪个呢？

按照上面的分析，你应该很快能给出答案，用平衡树来实现。那么mysql实际的索引（这里上下文的语境指的是innodb存储引擎的索引）选用的是哪个数据结构  
呢？

> 实际上mysql innodb使用的数据结构是B+树，并不是二叉树。

mysql innodb为什么不用二叉树，而使用B+树呢？  
上面的示例中我们忽略了很重的几个因素。

1.  mysql存储表的数据是在磁盘上的，我们知道io操作是很费时的(和cup的时间相比)。

*  磁盘读取数据过程 = 寻道时间 + 旋转延时 + 传输时间
 *  磁盘访问成本是内存的10万倍。

1.  以目前冯诺依曼计算机的结构，cpu是直接和内存打交道的，cup肯定希望每次能多计算些数据。

*  局部性原理

根据上面的信息，如果让我们设计索引的数据结构，要求数据结构：**多路搜索，高度可控**。  
b+树符合这个要求，所以选用b+树符合场景。

![单索引b+树结构][b]

上图中，可以看到，实际的数据存储在**叶子节点**，也就是所有的数据按照顺序树的叶子节点，  
Io读取的时候也能够按照磁盘顺序读取。而**非叶子节点**只保存索引，不保存实际的数据。

![多列索引][format_png]

上图展示了联合索引是如何存储在B+树中的。这里创建的联合索引相当于创建了三个索引分别是：

1.  A，B,C
2.  A,B
3.  A

![聚簇:主键索引][format_png 1]

上图是innodb以主键为索引的B+树结构，如果没有定义主键，innodb会选择一个唯一的非空索引代替。  
如果没有这样的索引，innodb会隐式定义一个主键来生成这种结构。  
上面的这种结构，术语为聚簇索引: 存储逻辑位置和物理位置是一致的。一张表只有一个聚簇索引。

> 术语 ‘聚簇’表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同  
> 的地方，所以一个表只能有一个聚簇索引。

### 回表 ###

从上面三张图中可以看到聚簇索引和普通索引是有一些区别的，聚簇索引存储表的所有的数据，  
我们可以看到普通索引只存储了索引的列以及主键。存储主键的作用是当select返回的列，不在索引  
范围内，会根据主键查出所有的数据，这种术语叫做**回表**。

### 覆盖索引 ###

如果select返回的列只有索引的列，那么就不需要回表，这种查询术语叫**覆盖索引**。  
如果seelct包含索引的列以及主键，也不需要回表，因为普通索引的叶子节点含有主键。

## 创建索引的原则 ##

我们再来看看上面的联合索引的图，我们创建了一个联合索引(A,B,C)三列，在每一个非叶子节点  
中的每一路中也是按照A,B,C排列的。下面那些是可以用到这个联合索引的呢？

1.  select \* from tablename where A = 1 and B = 2 and C = 3;
2.  select \* from tablename where B = 2 and A = 1 and C = 3;
3.  select \* from tablename where C = 3 and B = 2 and A = 1;
4.  select \* from tablename where A = 1;
5.  select \* from tablename where A = 1 and B = 2;
6.  select \* from tablename where B = 2;
7.  select \* from tablename where C = 3;
8.  select \* from tablename where A = 1 and C = 3;
9.  select \* from tablename where B = 2 and C = 3;

上面示例1,2,3(2,3有疑惑的话后下面会解释)是完全用到了这个联合索引，示例4，5，用到部分索引。  
示例6，7，8，9没有用到索引。这个很好理解，当在某层的某一路进行搜索时，先匹配A是否符合，符合后  
在看B是否符合，符合再看C是否符合。示例6，7，8，9在树的根节点匹配是就匹配不到，所以根本就不会  
用到这个索引。

到这里，我们就很自然的得出了索引的原则：**最左前缀匹配法**  
回到示例2，3，为什么也会用到索引呢？我们看一下Mysql的架构图。

![mysql架构图][mysql]

在示例2，3到优化器的时候，时候进行优化排序会变成示例1，因为1,2，3，这个语句中A,B,C写法上谁在前无所谓，  
最终经过优化器执行的时候，是A,B,C的。

到这里我们总结一下建立索引以及编写查询语句是的原则：

*  满足最左前缀匹配法
 *  = 和 in 可以乱序，mysql会优化查询条件顺序以满足索引
 *  尽量选择区分度高的列作为索引（这样数据树的高度就不会很高）
 *  索引列不参与计算，如果参与计算索引无效

## 创建索引的语句 ##

create index index_name on table_name (col1,col2......) 
    
    create unique index index_name on table_name (col1,col2.....)

## 索引不是银弹 ##

尽管索引非常美好，但是我们知道"福祸相依"，虽然索引加快了查询的速度，但是当我们  
插入以及更新表数据的时候，也会更新索引，因为要重新构建索引所以相比未加索引的表，  
加索引的表插入数据和更新表都会增加额外的时间。

当表的查询变慢的时候，我们不能只是一味的增加索引。还应该从其他方面分析后，再来确定  
是否增加索引。

1.  从业务角度出发，是否现在的查询，表设计合理，可还有改进的余地
2.  增加索引
3.  单库单表是否查询压力太大了，是否要一主多从减少一台的压力，分离读写
4.  是否单表的数据量过大，进行表的拆分
5.  单表的拆分，是否有热点数据可以建单独的表，以及使用缓存中间件

[b]: https://imgconvert.csdnimg.cn/aHR0cDovL2Nkbi54dWVsb25namlhbmcuY2x1Yi8lRTUlOEQlOTUlRTUlODglOTclRTclQjQlQTIlRTUlQkMlOTUucG5n?x-oss-process=image/format,png
[format_png]: https://imgconvert.csdnimg.cn/aHR0cDovL2Nkbi54dWVsb25namlhbmcuY2x1Yi8lRTUlQTQlOUElRTUlODglOTclRTclQjQlQTIlRTUlQkMlOTUucG5n?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cDovL2Nkbi54dWVsb25namlhbmcuY2x1Yi8lRTglODElOUElRTclQjAlODc6JUU0JUI4JUJCJUU5JTk0JUFFJUU3JUI0JUEyJUU1JUJDJTk1LnBuZw?x-oss-process=image/format,png
[mysql]: https://imgconvert.csdnimg.cn/aHR0cDovL2Nkbi54dWVsb25namlhbmcuY2x1Yi9teXNxbCVFNiU5RSVCNiVFNiU5RSU4NCVFNSU5QiVCRS5wbmc?x-oss-process=image/format,png