关注「Raymond运维」公众号，并设为「星标」，第一时间获取更多运维等文章，不再错过精彩内容。
正文
MySQL 面试过程中，索引是必不可少的一部分。索引相关的问题基本都是一系列问题，都是先从索引的基本原理，再到索引的使用场景，比如：
索引底层使用了什么数据结构和算法？
为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？
什么时候适用索引？
什么时候不需要创建索引？
什么情况下索引会失效？
有什么优化索引的方法？
MySQL 索引的核心知识点
索引的分类
索引可以按照四个不同的维度来进行分类：
数据结构：B+Tree索引，Hash索引，Full-Text索引
物理存储：聚簇索引（主键索引），非聚簇索引（二级索引）
字段特性：主键索引，唯一索引，普通索引，前缀索引
字段数量：单列索引，联合索引
按数据结构分类
按照数据结构来分类的话MySQL常见的索引类型有 B+Tree索引，Hash索引，Full-Text索引
MySQL常用的存储引擎为InnoDB,InnoDB的默认索引的数据结构类型是B+Tree。
MySQL5.6以后开始支持Full-Text索引
Hash索引
MySQL 的InnoDB不支持Hash索引，但是在内存结构中有自适应hash索引，无需手动创建，系统会根据查询效率自动创建。hash索引对于单行的数据查询，查询效率非常高，但对于涉及排序、分组、比较等复杂的查询语句，效率就会直线下降。
hash索引适用的场景为：
单行记录查询
索引查询
所有记录都能放到内存中
系统会自动创建自适应hash索引
B+Tree索引
MySQL的innodb存储引擎的索引采用B+Tree的算法
为了便于理解B+Tree索引的存储和查询过程，通过一个简单的例子，了解一下B+Tree索引的具体实现。
假设一张表，有如下数据
id 为主键索引
p_no 为二级索引
这张表中的索引是如何存储的？
主键索引
主键索引的B+Tree存储结构图
假设，执行 select * from t where id =5 这样一条语句，该查询语句的条件是找到id=5的这条记录。因为 B+Tree 是一个有序的数据结构，所以可以通过二分查找算法快速定位这条记录，也就是常用的索引查询，具体过程如下：
从根节点开始，将5 与根节点的索引数据(1,10,20) 做对比，5在 1和10 之间，根据二分查找算法，通过指针找到第二层的索引数据（1，4，7）
在第二层的索引值（1，4，7）中查找，5 在 4和7之间，通过指针找到第三层叶子节点的的索引数据（4，5，6）
在叶子节点的索引数据（4，5，6）中找到 id=5的数据。
根据上图可以看出B+Tree 的数据结构特点：
B+树的非叶子节点不存储数据，只存储键值。由于MySQL中是按页进行存储数据的，页的默认大小为16K，如果只存储键值可以存储更多的键值，树的阶数就更大，树型就会更加矮胖，查询数据所需的IO次数就会更少，查找速度就会更快。B+树的键值数等于阶数，由于根节点是常驻内存的，所以只需要2次IO操作就能查找到所需要的数据
B+树的数据都存储在叶子节点，并且数据都是按顺序存储，因此推荐用自增列做主键。
B+树中各节点（数据页）之间通过双向链表连接，叶子节点中的数据通过单向链表连接，这样能快速查找所有数据。
准确地说 innodb中的聚集索引就是上图中的B+树索引
非聚集索引略有不同的地方是 叶子节点不存储数据而是存储列对应的主键，当查找此列数据时还需要通过主键回表去查找数据。
二级索引
对于非主键字段建立索引，称之为二级索引。二级索引在叶子节点存储的不是整行数据，而是二级索引的索引值和主键的键值
在t表中建立 p_no字段为二级索引，同样是采用B+Tree索引，二级索引的存储结构图如下：
二级索引树是按照二级索引顺序存放的，如果二级索引值相同再按照主键索引排序。
select * from t where p_no =1002;
查询过程会首先在二级索引中根据二分查找算法找到 p_no=1002的二级索引记录，由于查询语句中是 "select *" 还需要查询索引值以外的其他字段的数据，因此，会再根据主键索引值到主键索引树中查找对应的叶子节点上的数据。这个过程叫做回表，也就是说找个查询操作需要访问两个索引树才能查到数据。
如果查询的数据在二级索引的叶子节点能查询到，就不需要再到主键索引去查找了。例如：
select id from t where p_no =1002;
这种不需要回到主键索引中查找数据的叫做 覆盖索引，之查找二级索引就能满足需求。
为什么 MySQL InnoDB 选择 B+Tree 作为索引的数据结构？
1、B+Tree VS B Tree
B+Tree 只在叶子节点存储数据，非叶子节点只存储键值，而B Tree 的非叶子节点也存储数据。因此B+Tree 非叶子节点存储的键值就更多，导致树型高度越低，成矮胖状。查询数据所需的磁盘I/O次数就越少，效率越高。
B+Tree 的叶子节点直接采用双向链表连接，适合MySQL中常见的基于范围的顺序查找，而B Tree无法做到这一点。
2、B+Tree VS Hash
Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。
但是 Hash不适合做范围、排序、分组、对比等复杂的SQL查询，所以B+Tree比hash索引 的应用范围更广
3、B+Tree VS 二叉树
二叉树的每个父节点的子节点个数只能是2个，意味着其搜索的复杂度为O(logN)。而B+Tree结构在实际应用中子节点的个数大于100，这就保证了即使数据到达千万级别，B+Tree的高度依然维持在3~4层。二叉树的树型要高出B+Tree很多，因此检索到目标数据所需的磁盘I/O次数要更多。
按物理存储分类
从物理存储的角度来看，索引分为聚簇索引（主键索引）、非聚簇索引（二级索引）。
这两个区别在前面也提到了：
主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。
如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。
按字段特性分类
从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。
主键索引
主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引
顾名思义，唯一索引就是建立在 拥有唯一值的 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
普通索引
普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段唯一。
前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。
按字段数量分类
从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。
建立在一个字段上的索引称为单列索引；
建立在多个字段的索引称为联合索引；
什么时候需要创建索引？
索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：
需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。
什么时候适用索引？
字段有唯一性限制的，比如商品编码、用户id；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
什么时候不需要创建索引？
WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位作用的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，区分度很低的字段，不需要创建索引。比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
索引的优化方法
自增主键
我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？
InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。
如果使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。
另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。
覆盖索引优化
覆盖索引是指 SQL语句 中 select 部分的所有字段在B+Tree索引 的叶子节点上都能找得到。也就是从二级索引中能查询到所有所需的字段，而不需要通过主键索引到数据行中去查找获得，可以避免回表的操作。
假设我们只需要通过商品ID查询商品的名称、价格，有什么方式可以避免回表呢？
我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。
索引字段设置为NOT NULL
为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：
第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，比如进行索引统计时，count 会忽略值为NULL 的行。
第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，如果表中存在允许为 NULL 的字段，在创建索引时需要用1个字节的空间来存储字段允许为NULL
前缀索引优化
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？
使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
不过，前缀索引有一定的局限性，例如：
order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；
微
信
群
WeChat group
为方便大家更好的交流运维等相关技术问题，特创建了微信交流群。需要加群的小伙伴们在关注微信公众号后，点击底部菜单关于→联系我，即可获取加群方式。
博客
客
Blog
CSDN博客
掘金博客
长按识别二维码访问博客网站，查看更多优质原创运维等文章。
