MySQL——图文版搞懂MySQL的索引是什么？

• 按数据结构分类可分为：B+tree 索引、 Hash 索引、 Full-text 索引。
• 按物理存储分类可分为：聚簇索引(主键索引)、二级索引（辅助索引）。
• 按字段特性分类可分为：主键索引、普通索引、前缀索引。
• 按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

1、常见的索引模型

• Hash 表： 适用于只有等值查询的场景，比如Memcached；
• 有序数组：只看查询效率，有序数组是最好的数据结构，只适用于静态存储引擎；
• 二叉搜索树： 时间复杂度0(log(N))。

2、索引的优缺点？

答： 创建索引可以大大提高系统的性能。

2.1、优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因；
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2.2、缺点

• 创建和维护索引需要耗费时间：这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。
• 索引需要占物理空间：除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3、InnoDB的索引模型？以及为什么要选择B+树？

答： B+树（B+树的高度通常是 1-3）。B树在提高了IO性能的但是并没有解决元素遍历的效率低下的问题，正是为了解决这个问题，B+树应运而生。B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

3.1、B+树的性质：

1、非叶子节点的子树指针与关键字个数相同；
2、非叶子节点的子树指针p[i]，指向关键字值属于[k[i],k[i+1])的子树。(B树是开区间，也就是说 B 树不允许关键字重复， B+ 树允许重复)；
3、为所有叶子节点增加一个链指针；
4、所有关键字都在叶子节点出现(稠密索引)。 (且链表中的关键字恰好是有序的)；
5、非叶子节点相当于是叶子节点的索引(稀疏索引)，叶子节点相当于是存储(关键字)数据的数据层；
6、更适合于文件系统；

3.2、B+树经典面试题

3.2.1、InnoDB 一棵 B+ 树可以存放多少行数据?

答： 约2千万行。

3.2.2、为什么索引结构默认使用B+树，而不是Hash，二叉树，红黑树，B-树?

• Hash哈希，只适合等值查询，不适合范围查询。
• 一般二叉树，可能会特殊化为一个链表，相当于全表扫描。
• 红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。
• B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。

3.3、B树和B+树的区别？

• B-树内部节点是保存数据的；而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据；
• B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是；
• 查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束；
• B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

3.4、B+树是如何减少IO性能的？

• 按B树 和B+树来说，B+树的索引页中全部是都是索引，这样一个数据页中能查询到很多索引降低了下一次去磁盘再拿索引页的可能性, 这样就降低了磁盘的IO了。
• B树在非叶子节点存储数据了，这样我一个索引页上上有数据有索引，肯定效率低了。 这个B树就是一个多叉树而已了。

4、MySQL B+ 树索引和 Hash 索引的区别？

答：由于 Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash** 索引的查询效率要远高于 ****B-Tree**索引。

既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree索引呢？

任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

• Hash 索引仅仅能满足 "=" , "IN" 和 "<=>" 查询，不能使用范围查询： 由于 Hash 索引比较的是进行 Hash运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
• Hash 索引无法被用来避免数据的排序操作： 由于 Hash 索引中存放的是经过 Hash计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
• Hash 索引不能利用部分索引键查询： 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
• Hash 索引在任何时候都不能避免表扫描： 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
• Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B-Tree 索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

5、主键索引和非主键索引？

答： 非主键索引的叶子节点存放的是主键的值，主键索引的叶子节点存放的是整行数据。其中，非主键索引又称为二级索引，主键索引又称为聚簇索引。

6、自增主键和随机主键的索引有什么区别？

• 自增主键： 在进行数据插入时，位置相对固定（B+树中的右下角）增加数据插入效率，减少插入的磁盘 IO 消耗，每页的空间在填满的情况下再去申请下一个空间，底层物理连续性更好，能更好的支持区间查找；
• UUID ： 由于UUID是随机生成的，插入时位置具有一定的不确定性，无序插入，会存在许多内存碎片，内存空间的占用量也会比自增主键大，区间查找也没自增主键性能优。

7、SQL查询语句确定创建哪种类型的索引？如何优化查询？

7.1、MySQL索引类型normal，unique，full text的区别是什么？

• normal ： 表示普通索引，最常见；
• unique ： 表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique；
• full text ： 表示全文搜索的索引。 FULL TEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX也可以。

7.2、实际操作过程中，应该选取表中哪些字段作为索引？

• 选择唯一性索引
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常作为查询条件的字段建立索引
• 限制索引的数目
• 尽量使用数据量少的索引
• 尽量使用前缀来索引
• 删除不再使用或者很少使用的索引

7.3、MySQL索引创建方式

• 普通索引：create index on Tablename(列的列表)

  alter table TableName add index (列的列表) 
  
  create table TableName([...], index [IndexName] (列的列表) 
  

• 唯一性索引：create unique index

  alter ... add unique 
  // 主键：一种唯一性索引，必须指定为primary key 
  

• 全文索引：从3.23.23版开始支持全文索引和全文检索，FULL TEXT

  可以在char、varchar或text类型的列上创建。 
  

• 单列索引、多列索引

  多个单列索引与单个多列索引的查询效果不同，因为： 
  执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。 
  

• 最左前缀(Leftmost Prefixing)：多列索引，例如：fname_lname_age索引，以下的搜索条件MySQL都将使用 fname_lname_age索引：firstname,lastname,age;firstname,lastname;firstname，其他情况将不使用。

8、范围查询会用到索引吗？

答： =,<=不一定走索引，因为优化器会计算，符合条件的值的总和，与总数据量的比值，比值**<=0.30**，才会走索引。

• 并不是给一个列建立了索引，对这个列进行范围查询的时候，就会走索引，他是有一个比例值的。比例值会随着版本、服务器、IO、数据量、数据重复情况而不同。也就是说，同一个版本，同一个库表，此时和下一时刻，比例值就可能不一样。测试中途遇到过该问题。
• MySQL5.6版本的时候，进行了优化，ICP 和 MRR，极大提升性能。
• FORCE INDEX 的作用，特殊情况下，可以只返回索引列。

9、聚集索引和非聚集索引区别(物理结构分类)？

• 聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，一般来说就是我们常说的主键索引。但是聚集索引不一定是主键，但是主键一定是聚集索引：原因是如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，如果也没有这样的唯一索引，InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引；
• 非聚簇索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键；
• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个；
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

10、聚簇索引和主键索引的区别

	主键	聚集索引
用途	强制表的实体完整性	对数据行的排序，方便查询用
一个表多少个	一个表最多一个主键	一个表最多一个聚集索引
是否允许多个字段来定义	一个主键可以多个字段来定义	一个索引可以多个字段来定义
是都允许NULL数据行出现	如果要创建的数据列中数据存在NULL，无法建立主键。创建表时指定的PRIMARY KEY约束列隐式转换为NOT NULL。	没有限制建立聚集索引的列一定必须NOT NULL。也就是可以列的数据时NULL。参看最后一项比较。
是否要求数据必须唯一	要求数据必须唯一	数据即可以唯一，也可以不唯一。看你定义这个索引的 UNIQUE 设置。 (这一点需要看后面的一个比较，虽然你的数据列可能不唯一，但是系统会替你产生一个你看不到的唯一列)
创建的逻辑	数据库在创建主键同时，会自动建立一个唯一索引。 如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立 一个唯一的聚集索引。	如果未使用UNIQUE属性创建聚集索引，数据库引擎将向表自动添加一个四字节uniqueifier列。 必要时，数据库引擎将向行自动添加一个uniqueifier值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

11、什么是覆盖索引？

**答：**覆盖索引（covering index，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

当查询使用覆盖索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

12、什么是组合索引？

答： 由多个字段组成的索引叫组合索引。

在非主键索引中，非主键索引是有序的，相同的非主键索引，还会按照主键排序，所以你建立一个非主键索引就相当于创建了非主键和主键的组合索引

13、什么是回表？覆盖索引解决回表？

答： 在得到查询结果之后，回到主键索引树搜索的过程，我们称为回表。

解决：

覆盖索引： 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一种常用的性能优化手段。

14、最左前缀原则

答： 在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

15、什么时候索引会失效

• 有or必全有索引;
• 复合索引未用左列字段;
• like以%开头;
• 需要类型转换;
• where中索引列有运算;
• where中索引列使用了函数;
• 如果mysql觉得全表扫描更快时（数据少）。