MySQL索引学习

B-Tree：B树

数据结构中的B树

MySQL中的B树

Innodb 页结构

• Page的作用，减少磁盘IO，一次读取一页到内存中再取出来比较，比读取磁盘中n条数据相比，降低了磁盘IO。【前者1次磁盘IO，后者n次IO】

• page 页 是逻辑结构，一页16KB

页目录的每一条记录，存用户数据区域中每一个组的第一行数据的地址

问题：

页目录：为了加快页内，用户数据区域遍历，找到指定元素。【将用户数据区域的数据分组】（上图）

索引页：为了减少页的遍历，每次找一个页都要将其从磁盘取出来，这样就很浪费时间。【将页与页之间整合分组】（下图）

上图是不是就是B+树的结构（叶子结点的页都是数据页码，上面的页就是索引页）

聚簇索引/非聚簇索引

如下图（B+树），数据和索引是在一起的。

简单概括：

• 聚集索引就是以主键创建的索引
• 非聚集索引就是以非主键创建的索引

区别：

• 聚集索引在叶子节点存储的是表中的数据
• 非聚集索引在叶子节点存储的是主键和索引列
• 使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

非聚集索引也叫做二级索引，不用纠结那么多名词，将其等价就行了~

非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。

• 此时就涉及到了哪个列会走索引，哪个列不走索引的问题了(最左匹配原则–>后面有说)
• 创建多个单列(非聚集)索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)

在创建多列索引中也涉及到了一种特殊的索引–>覆盖索引

• 我们前面知道了，如果不是聚集索引，叶子节点存储的是主键+列值
• 最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
• 覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

比如说：

• 现在我创建了索引(username,age)，在查询数据的时候：select username , age from user where username = 'Java3y' and age = 20。
• 很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了~
• 所以，能使用覆盖索引就尽量使用吧~

全表查找和索引查找

select * from t1 where a = 7

select * from t1 where a > 5; 这条语句会先执行a=5去寻找，然后再返回后面所有大于5的部分

select * from t1 where a < 5; 这条语句会先执行a=5去寻找，然后再返回后面所有小于5的部分

全表查找，走最下面一层。【a不为主键时】

索引查找，从上往下走索引页。【a为主键时】这里的索引都是主键索引

主键和索引的区别

1. 主键是个什么?

我来来具体的东西说吧，书大家都看过吧，没看过的找本翻一下，看下它每页是不是有个页码，我们的数据表主键就相当于是这个页码，明白了吧。

2. 那么索引是什么呢？

我们还拿书来说，索引相当于书的目录，有了目录我们可以很快的知道这本书的基本内容和结构，数据索引也一样，它可以加快数据表的查询速度。

3. 主键主索引类比，及其他们的作用？

• 主键是为了标识数据库记录唯一性，不允许记录重复，且键值不能为空，主键也是一个特殊索引。
• 数据表中只允许有一个主键，但是可以有多个索引。
• 使用主键会数据库会自动创建主索引，也可以在非主键上创建索引，方便查询效率。
• 索引可以提高查询速度，它就相当于字典的目录，可以通过它很快查询到想要的结果，而不需要进行全表扫描。
• 主键索引外索引的值可以为空。
• 主键也可以由多个字段组成，组成复合主键，同时主键肯定也是唯一索引。
• 唯一索引则表示该索引值唯一，可以由一个或几个字段组成，一个表可以有多个唯一索引。

联合索引

• create index idx_t1_bcd on t1(b,c,d);

• 按照b\c\d 3个字段构建的联合索引，结果大小排序如下

• select * from t1 where b=1 and c=1 and d=1; 走索引的路径

最左前缀原则

最左匹配原则：

• 索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
• 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
• 因此，列的排列顺序决定了可命中索引的列数。

例子：

• 如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。(很简单：索引命中只能是相等的情况，不能是范围匹配)

示例1

select * from t1 where c=1 and d=1;// 下面是bcd的联合查询，该表的主键字段为a

上述语句最终走**【全表扫描】**

示例2

可以走索引查询，因为符合最左前缀原则

示例3

走索引查询，因为符合最左前缀原则

示例4

• 下面回表是因为select *，表中有 a、b、c、d、e五个字段，最终叶子结点只能查到bcd(联合索引)和a(主键), e字段要回表才能查询到。

全表扫描或索引扫描都行，但系统会判定那个快，选哪个**【走全表扫描】**

示例5

下面这个就可以走**【索引扫描】**

示例6

下面这个可以走**【索引扫描】**，不查seletc*，而是select b

示例7

下面走**【索引扫描】**，比起全表扫描，取出的数据要少，因为全表扫描的最下面叶子结点存的是全部数据【包含d字段，此时我们其实用不到。

示例8

下面两行都符合最左匹配原则，最左匹配原则不是说where中条件最左，MySQL是很智能的哦。

第一个例子的解释

• 索引扫描的过程

• 全表扫描的过程

索引总结

https://juejin.cn/post/6844903645125820424，作者java3y

索引在数据库中是一个非常重要的知识点！上面谈的其实就是索引最基本的东西，要创建出好的索引要顾及到很多的方面：

• 1，最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。
• 3，尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
• 4，索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP('2016-06-06')。
• 5，尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
• 6，单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

参考资料：

• zhuanlan.zhihu.com/p/23624390–简单理解索引
• blog.csdn.net/mysteryhaoh…– MySQL学习之——索引(普通索引、唯一索引、全文索引、索引匹配原则、索引命中等)
  390](https://link.juejin.cn?target=https%3A%2F%2Fzhuanlan.zhihu.com%2Fp%2F23624390)–简单理解索引
• blog.csdn.net/mysteryhaoh…– MySQL学习之——索引(普通索引、唯一索引、全文索引、索引匹配原则、索引命中等)
• monkeysayhi.github.io/2018/03/06/…—浅谈MySQL的B树索引与索引优化