Mysql系列-索引-原创手记-慕课网

前言

面试过程中对于Mysql的考察，索引可以说是必考的，在开发过程中，我们也有可能跟索引打交道，开发过程中我们可能会遇到这种情况，一个sql执行很慢，公司DBA可能会让你优化一下sql语句，我们最直观的想法大概都是加索引吧？为什么加索引就变快了？怎么去加索引，什么时候适合加索引，索引什么会失效，这些也是面试官常常考察的点，接下来跟着fly一起我们一起研究一下索引相关的一些东西吧。

你能谈谈为啥性别列为啥不建议添加索引嘛
为什么Innodb创建一般建议建表的时候有个字段设置为自增主键
Mysql底层InnoDB使用的是聚簇索引，MySAM使用的非聚集索引，这两个存储引擎还有什么区别
Mysql为啥使用B+树

索引是什么

查看MySQL官方文档对索引的定义是这样的，索引(index)是帮助Mysql高效获取数据的数据结构，数据结构这个东西听起来就很高大上了，现在心里我们先有个定义，稍后一起研究下是这个数据结构是什么。

数据结构

数据库主要是用来做数据的存储和查询的，我们大学的时候查询数据常见的一些数据结构如下

Hash:Hash是一种key-value的键值对，根据key取value
数组:有序数组，在等值查询和范围查询场景中的性能就都非常优秀，插入数据差点意思
树：能想到的是二叉树，红黑树，b-tree(InnoDB存储引擎用的就是这个玩意)

上面这么多数据结构，b-Tree树为啥万千宠爱于一身得到Mysql的选择，接下来我们分析一波

二叉树

gaitubao_斜树

如果我们将二叉树当做Mysql底层的数据结构，我们看一下会有什么问题，如图所示现在比如我们想插入数据1，2，3，4，5如果使用二叉树的话，我们会发现最终的数据结构是一个斜树。现在我们想查询5，这个时候我们会从1开始一直遍历到5，这其实就是二叉树退化链表了嘛，而且树的高度为5，我们知道索引是放在内存中的，索引我们需要从磁盘中读取，我们查个5需要从1~5遍历，这样肯定是不行的,因为从磁盘中读数据需要寻址，就是磁道那些玩意，非常浪费时间，数据查询过程中，应该尽量避免读磁盘。

红黑树

红黑树我们在JDK1.8听说个这个玩意，插入数据的时候会按照自己的规则维护一种平衡，现在我们看一下选择红黑树作为Mysql底层的数据结构会出现什么问题，如图所示我们插入1,2,3,4,5之后我们发现红黑树查3次就能查到，这样比二叉树好太多了，但是想一下，如果数据插入到1万呢，我们会发现还是那个问题，索引是放在内存上的，我们需要从磁盘读取的，我们需要降低树的高度，现在红黑树分两个叉，那么能不能分3个，4个，n个呢（更多的分叉其实就是b树系列）

B-tree

每个节点包含键值和数据对象存放的地址指针
索引元素不会重复，节点的值都不相同

在B树上查找数据的流程是这样的，将根节点加载内存中，如图所示根节点上存在的很多有序的元素比如k1,k5,k10，载入内存中之后去查找响应的关键字，如果能查到，则查找成功返回，否则也一定能够确定要查找的关键字位于ki~ki+1之间，这个时候就会取出下一层的节点继续查找，直到到达叶子节点看是否能查到,因为叶子节点有data值，查找之后就能直接返回

Q：Mysql节点中一次能够存多少元素？

A：Mysql默认节点大小为16384，大约16K，实验的时候可以通过 **show global status like ‘Innodb_page_size’**查找相应默认值。

B+tree

B+树是一种B-tree的变种，Mysql的索引选择的就是这个东西，有以下这几个特性

非叶子节点不存data数据，只存储索引，这样非叶子节点就能存储更多的值
叶子节点在同一层使用指针相连并且包含全部的索引字段
数据对象的插入和删除仅在叶子节点上进行，非叶子节点存储冗余索引

B+ 树

Q：为甚非叶子节点存储的是冗余元素而不是像B数那种存data元素？

A：把date数据去掉之后这样非叶子节点就能够存储更多的数据了，这样树就叉分的更多，对应的树肯定变的越低，磁盘I/O次数肯定变少。

在B+树上查找过程是这样的，有两种数据查找方式

和B树类似，从根节点开始搜索，不同的是如果非叶子节点等于给定值，搜索并不会停止，会继续搜索一直查找叶子节点位置，无论搜索是否成功，都会走完所有的层
另外一种是按照叶子节点自己拉起的链表顺序搜索

索引分类

索引主要有这些，唯一/非唯一索引，聚集索引/非聚集索引，组合索引

唯一索引是表中一个或者多个字段组成的不可重复的一种索引，表中唯一。非唯一索引相反
聚集索引/非聚集索引是表记录的物理顺序和索引的的顺序是否相同，InnoDB的主键索引就是一个聚集索引，他的索引和数据是绑定在一起的（叶子节点）。MYISAM的是非聚集索引，索引和数据是分开存储的,叶子节点存的不是数据元素，存的是元素的地址，B+树上的位置和元素的物理顺序不一定相同。
组合索引:基于多个字段创建的索引我们称为组合索引

再谈聚集索引/非聚集索引

聚集/非聚集索引都采用了B+树实现方式，数据库我们现在有一张用户表，通过读这个我们谈谈他两个的各自的特点：

聚集索引

如上图所示，聚集索引的主键叶子节点data保留的是完整的数据结构，数据和索引是在一块的，我们通过主键查找相应的记录之后能够直接返回记录的值。非主键索引叶子节点存储的是对应的主键，我们需要进行二次回表去主键B+树上执行一次查询操作。我们想下这个点有什么好处和坏处。

查询快：B+树记录的索引的顺序和数据顺序一致，我们进行范围查询的时候，第一个被查到，相邻的位置就在旁边，磁盘预读原理能加快查询速度
返回数据快，在主键上查询数据，查到就能返回，当然非主键需要进行回表，但是这样设计有个好处，非主键索引叶子节点存放的是主键Id，没有使用数据的地址，这样当数据库中行移动或者数据页分裂的时候，非主键索引就不用有影响，因为你存的是主键id。
对应的维护这个结构费劲，对表进行修改的时候，你这个时候就带把数据插入对应位置，所以必须对数据页进行重排
Mysql的存储引擎InnoDB使用的使用的就是聚集索引

非聚集索引

非聚簇索引

如上图所示，非聚集索引的索引文件只保存了数据记录的地址，索引上的顺序和物理上存储的顺序完全没有关联，而且主键索引和非主键上结构没有什么区别，都存的是地址，遍历的时候都是遍历到叶子节点取出地址，然后去相应地址上找出对应的值。这样做的好处我们想一下为什么，到时候好给面试官battle，好处有这两点

因为主键和非主键叶子节点都是存储的地址，我们通过查找的时候即使通过非主键查找数据也可以不用回表直接返回相应的值
数据和索引是分开的
Mysql的存储引擎MyISAM使用的使用的就是非聚集索引

自增主键

Mysql使用InnoDB创建表的时候，推荐使用整形的自增主键？为什么这样干，我们根据上面的自身点琢磨一下原因

原因是因为InnoDB会将数据和主键索引通过B+树来进行组织，所以我们创建表的时候通常使用一个自增的整形id，如果没有指定，Mysql会默认生成自增的rowid，这样新增加数据的时候，避免非单调的主键插入B+树为了维护相关特性进行分裂调整。数据会增加到当前数据页的后续位置。

索引优点/缺点

优点

提高数据查询效率，合理建立索引之后能够大大增加速度
通过创建唯一索引能够保证库里面数据的唯一性
可以加速表和表的连接，通过join表关联查询的时候
使用分组和排序子句进行数据检索的时候，可以显著减少查询中的分组和排序的时间

缺点

索引需要占用物理空间
索引的维护方面，数据增加删除修改等操作需要动态维护

索引建立的几个原则

尽量选择区分度比较高的列建立索引，这里有个考点可能面试官问你为啥性别列为啥不能建立索引
频繁查询的列适合建立索引
遇到联合索引时候想一下最左匹配原则（下一章将具体讲解联合索引和最左匹配原则是啥，小伙伴对这个名字有个印象）

like模糊查询时候，%在前面的时候才会用到索引，另外两个情况都会让索引失效，具体例子如下

select * from USER us where name l like ‘公众号程序员fly%’ //name上有索引的话会使用到name上的索引
select * from USER us where name l like ‘%公众号程序员fly’ //name上有索引的话索引会失效转为全表扫描
select * from USER us where name l like ‘%公众号程序员fly%’ //name上有索引的话索引会失效转为全表扫描