一口吻搞懂MySQL索引一切常识点

国庆时期看了数据库的很多资料和书籍，这点我在总结的数据库文章外面也提过了，而后我发现我对索引的引见不全，所以整顿了一下自己的笔记，选择来个索引完整版，老规矩或许还是没我反常文章格调那么跳，但是干货肯定也能让你有所收获。

索引是什么

长处：

劣势：

主键索引 索引列中的值必需是惟一的，不准许有空值。

普通索引 MySQL中基本索引类型，没有什么限度，准许在定义索引的列中拔出重复值和空值。

惟一索引 索引列中的值必需是惟一的，但是准许为空值。

全文索引 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创立全文索引。字段长度比拟大时，假设创立普通索引，在启动like含糊查问时效率比拟低，这时可以创立全文索引。MyISAM和InnoDB中都可以经常使用全文索引。

空间索引 MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵照OpenGIS几何数据模型规定。

前缀索引 在文本类型如CHAR,VARCHAR,TEXT类列上创立索引时，可以指定索引列的长度，但是数值类型不能指定。

其余（依照索引列数量分类） 单列索引

组合索引

组合索引的经常使用，须要遵照最左前缀婚配准则（最左婚配准则）。普通状况下在条件准许的状况下经常使用组合索引代替多个单列索引经常使用。

Hash表 Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的模式存储数据。咱们经常使用Hash表存储表数据Key可以存储索引列，Value可以存储行记载或许行磁盘地址。Hash表在等值查问时效率很高，时期复杂度为O(1)；但是不支持范围极速查找，范围查找时还是只能经过扫描全表模式。

显然这种并不适宜作为经常须要查找和范围查找的数据库索引经常使用。

二叉查找树 二叉树，我想大家都会在心里有个图。

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

这个特点就是为了保障每次查找都可以这折半而缩小IO次数，但是二叉树就很考验第一个根节点的取值，由于很容易在这个特点下出现咱们并发想出现的状况“树不分叉了”，这就很舒服很不稳固。

显然这种状况不稳固的咱们再选用设计上肯定会防止这种状况的

平衡二叉树 平衡二叉树是驳回二分法思想，平衡二叉查找树除了具有二叉树的特点，最关键的特色是树的左右两个子树的层级最多相差1。在拔出删除数据时经过左旋/右旋操作坚持二叉树的平衡，不会出现左子树很高、右子树很矮的状况。

经常使用平衡二叉查找树查问的性能凑近于二分查找法，时期复杂度是 O(log2n)。查问id=6，只要要两次IO。

就这个特点来看，或许各位会感觉这就很好，可以到达二叉树的理想的状况了。但是依然存在一些疑问：

B树：革新二叉树 MySQL的数据是存储在磁盘文件中的，查问处置数据时，须要先把磁盘中的数据加载到内存中，磁盘IO 操作十分耗时，所以咱们优化的重点就是尽量缩小磁盘 IO 操作。访问二叉树的每个节点就会出现一次性IO，假构想要缩小磁盘IO操作，就须要尽量降落树的高度。那如何降落树的高度呢？

假设key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

由于在MySQL的InnoDB存储引擎一次性IO会读取的一页（自动一页16K）的数据量，而二叉树一次性IO有效数据量只要16字节，空间应用率极低。为了最大化应用一次性IO空间，一个便捷的想法是在每个节点存储多个元素，在每个节点尽或许多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改形成了多叉树，经过参与树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只要要2层就可以（1000*1000=1百万），也就是说只要要2次磁盘IO就可以查问到数据。磁盘IO次数变少了，查问数据的效率也就提高了。

这种数据结构咱们称为B树，B树是一种多叉平衡查找树，如下图关键特点：

举个例子，在b树中查问数据的状况：

假设咱们查问值等于10的数据。查问门路磁盘块1->磁盘块2->磁盘块5。

第一次性磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比拟，10<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比拟，7<10，到磁盘中寻址定位到磁盘块5。

第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比拟，10=10，找到10，取出data，假设data存储的行记载，取出data，查问完结。假设存储的是磁盘地址，还须要依据磁盘地址到磁盘中取出数据，查问中断。

相比二叉平衡查找树，在整个查找环节中，只管数据的比拟次数并没有显著缩小，但是磁盘IO次数会大大缩小。同时，由于咱们的比拟是在内存中启动的，比拟的耗时可以疏忽不计。B树的高度普通2至3层就能满足大局部的运行场景，所以经常使用B树构建索引可以很好的优化查问的效率。

环节如图：

B树索引查问环节

看到这里肯定感觉B树就很理想了，但是晚辈们会通知你依然存在可以优化的中央：

B+树：革新B树 B+树，作为B树的更新版，在B树基础上，MySQL在B树的基础上继续革新，经常使用B+树构建索引。B+树和B树最关键的区别在于非叶子节点能否存储数据的疑问

B+树数据结构

B+树的最底层叶子节点蕴含了一切的索引项。从图上可以看到，B+树在查找数据的时刻，由于数据都寄存在最底层的叶子节点上，所以每次查找都须要检索到叶子节点能力查问到数据。

所以在须要查问数据的状况下每次的磁盘的IO跟树高有间接的相关，但是从另一方面来说，由于数据都被放到了叶子节点，放索引的磁盘块锁寄存的索引数量是会跟这参与的，相关于B树来说，B+树的树高通常上状况下是比B树要矮的。

也存在索引笼罩查问的状况，在索引中数据满足了查问语句所须要的所有数据，此时只要要找到索引即可立刻前往，不须要检索到最底层的叶子节点。

举个例子：等值查问

假设咱们查问值等于9的数据。查问门路磁盘块1->磁盘块2->磁盘块6。

第一次性磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比拟，9<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比拟，7<9<12，到磁盘中寻址定位到磁盘块6。

第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比拟，在第三个索引中找到9，取出data，假设data存储的行记载，取出data，查问完结。假设存储的是磁盘地址，还须要依据磁盘地址到磁盘中取出数据，查问中断。（这里须要辨别的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

环节如图：

范围查问：

假设咱们想要查找9和26之间的数据。查找门路是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查问流程一样，出现了三次磁盘IO。

查找到15之后，底层的叶子节点是一个有序列表，咱们从磁盘块6，键值9开局向后遍历挑选一切合乎挑选条件的数据。

第四次磁盘IO：依据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比拟，9<25<26，9<26<=26，将data缓存到结果集。

主键具有惟一性（前面不会有<=26的数据），不需再向后查找，查问中断。将结果集前往给用户。

可以看到B+树可以保障等值和范围查问的极速查找，MySQL的索引就驳回了B+树的数据结构。

引见完了索引数据结构，那必需是要带入到Mysql外面看看实在的经常使用场景的，所以这里剖析Mysql的两种存储引擎的索引成功：MyISAM索引和InnoDB索引

以一个便捷的user表为例。user表存在两个索引，id列为主键索引，age列为普通索引

`id``username``age`

MyIsam_user查问数据

MyISAM的数据文件和索引文件是分开存储的。MyISAM经常使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所内行的磁盘地址。

MyIsam主键索引

表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。

便捷剖析下查问时的磁盘IO状况：

依据主键等值查问数据：

磁盘IO次数：3次索引检索+记载数据检索。

依据主键范围查问数据：

1.先在主键树中从根节点开局检索，将根节点加载到内存，比拟28<75，走左路。（1次磁盘IO）

2.将左子树节点加载到内存中，比拟16<28<47，向下检索。（1次磁盘IO）

3.检索到叶节点，将节点加载到内存中遍历比拟16<28，18<28，28=28<47。查找到值等于28的索引项。

依据磁盘地址从数据文件中失掉行记载缓存到结果集中。（1次磁盘IO）

咱们的查问语句时范围查找，须要向后遍历底层叶子链表，直至抵达最后一个不满足挑选条件。

4.向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比拟，28<47=47，依据磁盘地址从数据文件中失掉行记载缓存到结果集中。（1次磁盘IO）

5.最后失掉两条合乎挑选条件，将查问结果集返给客户端。

磁盘IO次数：4次索引检索+记载数据检索。

备注： 以上剖析仅供参考，MyISAM在查问时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了剖析索引的经常使用环节。

在 MyISAM 中,辅佐索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记载的磁盘地址。只是主键索引的键值是惟一的，而辅佐索引的键值可以重复。

查问数据时，由于辅佐索引的键值不惟一，或许存在多个领有相反的记载，所以即使是等值查问，也须要依照范围查问的模式在辅佐索引树中检索数据。

主键索引（聚簇索引） 每个InnoDB表都有一个聚簇索引 ，聚簇索引经常使用B+树构建，叶子节点存储的数据是整行记载。普通状况下，聚簇索引同等于主键索引，当一个表没有创立主键索引时，InnoDB会智能创立一个ROWID字段来构建聚簇索引。InnoDB创立索引的详细规定如下：

除聚簇索引之外的一切索引都称为辅佐索引。在中InnoDB，辅佐索引中的叶子节点存储的数据是该行的主键值都。在检索时，InnoDB经常使用此主键值在聚簇索引中搜查行记载。

这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。

`id``username``age`

user数据

InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织模式，是聚簇索引。

主键索引的叶子节点会存储数据行，辅佐索引只会存储主键值。

InnoDB主键索引

等值查问数据：

1.先在主键树中从根节点开局检索，将根节点加载到内存，比拟28<75，走左路。（1次磁盘IO）

将左子树节点加载到内存中，比拟16<28<47，向下检索。（1次磁盘IO）

检索到叶节点，将节点加载到内存中遍历，比拟16<28，18<28，28=28。查找到值等于28的索引项，间接可以失掉整行数据。将改记载前往给客户端。（1次磁盘IO）

磁盘IO数量：3次。

除聚簇索引之外的一切索引都称为辅佐索引，InnoDB的辅佐索引只会存储主键值而非磁盘地址。

以表user_innodb的age列为例，age索引的索引结果如下图。

InnoDB辅佐索引

底层叶子节点的依照（age，id）的顺序排序，先依照age列从小到大排序，age列相反时依照id列从小到大排序。

经常使用辅佐索引须要检索两遍索引：首先检索辅佐索引取得主键，而后经常使用主键到主索引中检索取得记载。

画图剖析等值查问的状况：

InnoDB辅佐索引查问

依据在辅佐索引树中失掉的主键id，到主键索引树检索数据的环节称为回表查问。

磁盘IO数：辅佐索引3次+失掉记载回表3次

组合索引 还是以自己创立的一个表为例：表 abc_innodb，id为主键索引，创立了一个联结索引idx_abc(a,b,c)。

`id``a``b``c``d`

组合索引的数据结构：

组合索引结构1

组合索引的查问环节：

组合索引的查问环节

最左婚配准则：

最左前缀婚配准则和联结索引的索引存储结构和检索模式是有相关的。

在组合索引树中，最底层的叶子节点依照第一列a列从左到右递增陈列，但是b列和c列是无序的，b列只要在a列值相等的状况下小范围内递增有序，而c列只能在a，b两列相等的状况下小范围内递增有序。

就像下面的查问，B+树会先比拟a列来确定下一步应该搜查的方向，往左还是往右。假设a列相反再比拟b列。但是假设查问条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创立的idx_abc(a,b,c)索引，相当于创立了(a)、（a,b）（a,b,c）三个索引。、

组合索引的最左前缀婚配准则：经常使用组合索引查问时，mysql会不时向右婚配直至遇到范围查问(>、<、between、like)就中止婚配。

笼罩索引并不是说是索引结构，笼罩索引是一种很罕用的优化手腕。由于在经常使用辅佐索引的时刻，咱们只可以拿到主键值，相当于失掉数据还须要再依据主键查问主键索引再失掉到数据。但是试想下这么一种状况，在下面abc_innodb表中的组合索引查问时，假设我只要要abc字段的，那是不是象征着咱们查问到组合索引的叶子节点就可以间接前往了，而不须要回表。这种状况就是笼罩索引。

可以看一下口头方案：

笼罩索引的状况：

经常使用到笼罩索引

未经常使用到笼罩索引：

看到这里，你是不是关于自己的sql语句外面的索引的有了更多优化想法呢。

比如：

在InnoDB的存储引擎中，经常使用辅佐索引查问的时刻，由于辅佐索引叶子节点保留的数据不是记载的数据而是记载的主键索引，索引假设须要失掉记载完整数据就肯定须要依据主键值从主键索引继续查问。这个环节咱们成位回表。想想回表肯定是会消耗性能影响性能。那如何防止呢？

经常使用索引笼罩，举个例子：现有User表（id(PK),name(key),sex,address,hobby...）

假设在一个场景下，select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁经常使用到，而user表的其余字段经常使用频率远低于它，在这种状况下，假设咱们在建设 name 字段的索引的时刻，不是经常使用繁多索引，而是经常使用联结索引（name，sex）这样的话再口头这个查问语句是不是依据辅佐索引查问到的结果就可以失掉语句的完整数据。

这样就可以有效地防止了回表再失掉sex的数据。

这里就是一个典型的经常使用笼罩索引的优化战略缩小回表的状况。

联结索引的经常使用 联结索引，在建设索引的时刻，尽量在多个单列索引上判别下能否可以经常使用联结索引。联结索引的经常使用不只可以节俭空间，还可以更容易的经常使用到索引笼罩。

试想一下，索引的字段越多，是不是更容易满足查问须要前往的数据呢。比如联结索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节俭了空间，当然节俭的空间并不是三倍于（a，a_b，a_b_c）三个索引，由于索引树的数据没变，但是索引data字段的数据确实实在的节俭了。

联结索引的创立准则，在创立联结索引的时刻因该把频繁经常使用的列、辨别度高的列放在前面，频繁经常使用代表索引应用率高，辨别度高代表挑选粒度大，这些都是在索引创立的须要思考到的优化场景，也可以在常须要作为查问前往的字段上参与到联结索引中，假设在联结索引上参与一个字段而经常使用到了笼罩索引，那我倡导这种状况下经常使用联结索引。

联结索引的经常使用

好啦以上就是我自己对索引的一些小总结，有点小长有点单调，适宜收藏后缓缓看。

我是敖丙，你知道的越多，你不知道的越多，咱们下期见。