MySQL八股学习记录3-索引专项from 小林coding

索引概念

一言以蔽之,索引就是数据的目录

索引的分类

种类:聚簇索引、主键索引、二级索引、普通索引、唯一索引、hash索引、B+树索引
按照不同的原理,索引的分类也不同
按照数据结构分类:B+树,Hash索引,Full-text索引
按照物理存储分类:聚簇索引,二级索引
按照字段特性分类:主键索引,唯一索引,普通索引,前缀索引
按照字段个数分类:单列索引,联合索引
InnoDB是MySQL5.5之后的默认引擎,B+树是InooDB引擎使用最多的引擎类型

B+树特点

 只有叶子节点才存放数据,非叶子节点存放索引,每个节点的数据是按照主键的顺序存放的,并且同层节点之间双向链表连接起来,数据库的索引和数据都是存放在磁盘上的,读取一个节点可以当作一个磁盘IO操作,所以B+树由于矮宽的特点,查询效率很高,数据量很大的情况下,查询一个数据依然只需要3-4个磁盘IO

通过二级索引查询数据的过程

 在通过非主键索引查询数据时,需要使用二级索引来查询数据,每个索引会建立一个B+树,这些B+树的叶子节点存储的是与主键的映射,得到主键之后,如果欲查询的数据在叶子节点中没有,(即不是覆盖索引),再查询聚簇索引(回表操作)查到真实的数据

B+树的优势

hash	二叉树	b树
不能范围查询	树过高	1.树过高(非叶节点需要存储信息)2.叶子节点没有链表(无法有效支持范围查询)

按照字段个数进行分类

从字段个数上看,索引可以分为单列索引和联合索引
 联合索引有一个重要的特征就是**最左匹配原则,**因为假设建立了一个联合索引,并且索引ID分别为(a,b,c…),那么B+树的排序准则就是先按照a排序,a相同按照b依次类推,因此b,c只是局部有序的,全局无序
 联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，前面我也用了四个例子说明了
TIPS:建立联合索引时,将区分度高的字段排在前面,这样区分度大的字段更有可能被更多的SQL使用到

索引使用指南

索引需要占用物理空间,并且索引的创建和维护需要耗费时间
适用索引的情况

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
  不适用索引的情况
• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

索引优化指南

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 防止索引失效；

索引失效情况

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。