前语

本篇文章从数据结构，B+Tree的构建进程，MySQL索引完成，索引为什么那么快，MySQL有哪些索引，集合索引和二级索引的差异，索引失效的原因，EXPLAIN关键字分析，索引实战，索引的优缺陷，什么时候应该加索引，全方面协助读者理解MySQL索引。

一、提出问题

  1. MySQL索引有哪些完成方法
  2. 什么是B+Tree索引，B+Tree索引的构建进程，跟二叉树、红黑树的对比
  3. 为什么数据库存储用B+tree索引，局部性原理是什么，为什么引荐自增id
  4. MySQL有哪些索引，集合索引，二级索引，掩盖索引差异，回表是什么
  5. 怎么加索引，索引的运用场景有哪些，加索引必定好吗
  6. 索引失效的场景，引发什么后果
  7. EXPLAIN关键字检查MySQL是否运用索引
  8. 慢查询优化

接下来会循序渐进，让读者彻底搞懂索引是什么

二、MySQL索引有哪些完成方法

MySQL索引完成方法有：B+tree索引、Hash索引、Full-text索引。

咱们最常用的是B+tree索引，主键索引（也叫聚簇索引）自身便是一个B+tree索引树，非叶子节点存储主键id，叶子节点为一整行数据，叶子节点之间经过双向链表连接支撑范围扫描，一般加的仅有索引，一般索引都是B+tree索引。

Hash索引只能在memory存储引擎下运用，这里不过多描绘，优点是查询快，hash取模O(1)检索，缺陷不支撑范围查询，出现hash抵触功能会下降。

Full-text索引主要对varchar,text加索引，运用倒排索引的方法，与搜索引擎完成方法类似。

三、为什么运用B+tree索引

先说定论，主要由于磁盘读写速度远远低于内存速度，传统的机械硬盘大约慢一万倍，固态硬盘慢100倍，故削减磁盘I/O次数是提高索引功能的要点。

依据局部性原理和磁盘预读，Linux操作系统进行磁盘I/O时，一般次序读写4KB到内存的Page Cache中，之后再在内存中找到对应的数据回来回去，Mysql的B+tree每个节点为16KB，咱们能够把16kb当作磁盘IO的最小单元。

局部性原理表现为：时刻局部性和空间局部性。时刻局部性是指假如程序中的某条指令一旦执行，则不久之后该指令或许再次被执行；假如某数据被拜访，则不久之后该数据或许再次被拜访。空间局部性是指一旦程序拜访了某个存储单元，则不久之后，其邻近的存储单元也将被拜访。

那么为什么挑选B+tree作为索引呢，B+tree下降了磁盘IO次数吗？为什么不必红黑树或许hash索引呢。

关于B+tree的构建进程和红黑树的构建进程我之前写过一篇文章数据结构与算法，在后半段详细描绘了二叉树，AVL树，B+tree的构建进程，这里有模仿数据结构构建进程的网站

红黑树每个节点包容1个key，树的高度为O(log➋ N)，查询复杂度也是O(log➋ N)，1000000条数据，树的高度为1000，最差需求扫描1000次才干查询到对应数据。

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

B+tree每个节点包容M个key，树的高度为O(logm N)，m越大，树高度越低，依照mysql一个page节点存储16kb来算，一个bigint主键是8个字节，一个节点能够包容大约1000个主键（每个节点还存储了其他躲藏信息协助节点内部检索），m便是1000，一千万条数据，树的高度大约是3层，只需求3次磁盘I/O加上几百次内存遍历查找，就能够快速定位到数据，这对查询功能的提高的巨大的，假如没有索引而进行全表扫描的话，大约需求上万次磁盘I/O。

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

故依据局部性原理和磁盘预读，B+tree适合在磁盘文件系统中做检索，红黑树更适合在内存中检索（比方java的hashmap，网络epoll的连接节点存储）。

为什么引荐运用自增ID作为主键呢，B+tree的构建进程是经过割裂和兼并坚持树的稳定的，如上图，若不是次序刺进的，树会进行频频的割裂，导致额定的磁盘IO和CPU运用，能够运用此网站数据结构构建进程手动测试下。

另外mysql的除主键外的一般索引的叶子节点都是id，故id越小一般索引的占用磁盘空间越小，故引荐运用int或bigint来做主键（下文详细讲）。

三、Mysql索引有哪些类型

集合索引，非集合索引，仅有索引，一般索引，联合索引，掩盖索引

1. 集合索引

集合索引，也叫主键索引，简略来说，mysql一张表的一切数据便是一个主键索引，索引的非叶子节点存储主键key，叶子节点存储详细的一行数据，当咱们经过id查询数据时就经过此索引检索，若表没有设置主键，mysql会用一个躲藏字段row_id设置为主键索引。

2. 非集合索引

非集合索引，也叫辅助索引，二级索引，是指除主键索引外的其他索引，比方咱们给一张表加一个索引。

ALTER TABLE `user`
ADD INDEX `idx_name`(`name` ASC) USING BTREE;

二级索引的key为咱们加的字段，比方name，叶子节点是主键id。

当咱们经过二级索引检索数据时，先经过二级索引检索到主键id，再拿着主键id到主键索引中查询到咱们想要的数据，这个进程叫做回表

与回表对应的是掩盖索引，即当咱们在二级索引树上即查询到咱们要的一切数据时，就不需求再回表到主键索引查询数据，这个进程叫掩盖索引，当咱们运用EXPLAIN关键字检查执行计划里有Using index就意味着咱们运用了掩盖索引。

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

仅有索引便是有仅有约束，当表字段有两个相同的值刺进时会报错。

联合索引便是对多个字段一起加索引，需求留意的是索引字段的次序，由于联合索引的构建进程实质是先依据第一个字段进行比较排序，再依据第二个字段比较排序，以此类推。。。假如咱们直接运用第二个字段查询数据，是无法运用该联合索引的，这便是最左前缀匹配准则。

若where b=1 and c=2 and a =3, 加联合索引(a,b,c)一样能够运用联合索引，mysql索引优化器会自动调整次序，只要a=3在查询条件即可。

一般咱们加联合索引是由于大部分场景都是运用这几个字段进行检索，加一个联合索引能够节省表空间，但必须确保第一个字段必定会运用检索，不然索引就失效了。

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

四、怎么加索引、加索引必定好吗

一般咱们运用navicat可视化界面添加索引，对于varchar字段能够指定索引的键长度，削减索引的key巨细，也能够指定索引的正序倒序，或挑选多个字段建立索引（即联合索引）

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

ALTER TABLE `changlf`.`user_test_time`
ADD INDEX `idx_username`(`username`(20) ASC) USING BTREE;

那什么场景需求咱们加索引呢？加索引有什么缺陷呢？

当咱们写一个sql时，where后的条件，join on后面的条件，order by排序字段，group by分组字段，在能够检索的当地，都会运用索引，select，update，delete句子在查询条件下都会运用索引，那咱们这些都加索引吗？

咱们知道，二级索引的key是索引字段，value是主键id，是直接存储在磁盘里的，当咱们加太多的索引，会导致表空间增大，增删改的时候需求重建索引下降功率，所以咱们一张表一般加3-5个索引比较适宜。

哪些字段适合加索引

首要咱们加索引肯定是为了检索，平常查询字段常用的字段才会加索引，可是加索引也要留意功率，需求检查索引的区分度。

索引的区分度等于count(distinct 详细的列) / count( * )，表明字段不重复的比例

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

区分度越大，索引检索的功率越高，仅有索引的区分度是1，而像一些状态字段，性别年龄字段，区分度很低就不适合加索引，大部分都是相同的数据，树比较巨细简直运用不到。

五、索引失效的场景、EXPLAIN关键字检查执行计划

  • 索引的失效场景：
  1. 联合索引不满足最左前缀准则，刚刚提到必须运用第一个字段进行检索。
  2. 对索引字段进行函数计算，如运用concat()，date_format()函数对索引进行修改，索引会失效，一般咱们要将查询条件进行格式化使其匹配索引，而不是对索引字段进行修改。
  3. like ‘%a%’，左%会导致范围检索，like ‘a%’索引能够正常运用。
  4. in, or, between and，这些范围检索函数或许会导致索引失效，mysql有索引优化器，当咱们检索的数据比较少时，比方十几条，仍是会运用索引，当检索到的数据量很大时，mysql以为直接查询主键索引功率更高时，就会放弃运用二级索引。
  5. !=, not in, not null，一切取反的检索条件会导致索引失效
  • EXPLAIN关键字检查执行计划

这里有一篇文章对EXPLAIN关键字运用讲解的很详细，就不过多讲解了。

大约运用便是在sql前面加上EXPLAIN，就能够查询执行时详细会运用哪些索引了。

一文搞懂MySQL索引（实现原理加优化实战，面试必问）

六、总结

本文对MySQL索引从构建原理，到各类索引完成，功能调优，优缺陷进行全方面讲解，期望对读者了解索引有个清晰的认识。

作者：龙猫帝
原文链接：/editor/draf…
版权一切，欢迎保存原文链接进行转载：)