mysql索引浅析

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万倍。

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

• 局部性原理

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

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

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

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

上图是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的架构图。

在示例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. 单表的拆分，是否有热点数据可以建单独的表，以及使用缓存中间件