MySQL索引知识点&面试常见问题

来源：BiggerBoy

作者：北哥

原文链接：https://mp.weixin.qq.com/s/fucHvdRK5wRrDfBOo6IBGw

大家好我是北哥，今天整理了MySQL索引相关的知识点及面试常见问题及答案，分享给大家。

以下问题及答案没有特殊说明默认都是针对InnoDB存储引擎，如有不对的地方可以留言讨论哦~

什么是索引？

索引就是一种用于快速查找数据的数据结构，是帮助MySQL高效获取数据的排好序的数据结构。

使用比较巧妙的数据结构，利用数据结构的特性来大大减少查找遍历次数

优点：

使用索引可以大大减少检索的数据量，从而加快数据的检索速度，这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

创建索引和维护索引需要额外耗费时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
索引需要使用物理文件存储，所以也会耗费一定磁盘空间。

但是，索引不是银弹，使用索引也不一定百分之百提高查询性能。如果数据量不大，那么使用索引也不一定能够带来很大提升，因为数据库的查询优化器会判断使用索引快还是全表扫描快，这不是我们能控制得了的。

索引使用的是哪种数据结构？

Hash索引、B+树索引。

索引底层数据结构分析请参考我之前的文章《MySQL索引底层数据结构及原理深入分析》

索引为什么不使用二叉树或红黑树？

这里说的二叉树确切的说是二叉查找树，它在一定情况下会退化为链表。例如，如果我们依次插入1、2、3、4、5…，这样它就和链表没差了，复杂度为O（n）了，而我们通常数据库主键就是自增的，所以不使用二叉树作为索引。

Tips：

二叉排序树（Binary Sort Tree），又称二叉查找树（Binary Search Tree），亦称二叉搜索树。是数据结构中的一类。

具有下列性质：

（1）若左子树不空，则左子树上所有结点的值均小于它的根结点的值；

（2）若右子树不空，则右子树上所有结点的值均大于它的根结点的值；

（3）左、右子树也分别为二叉排序树；

至于红黑树，它虽然通过自旋避免出现类似二叉树那样的情况，但是随着数据的不断增多，这棵树的高度会越来越高，而查询遍历的次数和树高有紧密的联系，基本是成正比的趋势。试想一下，一个表几百万的数据，如果使用红黑树作为索引，查询一个数据时，要遍历的次数也是很大的一个数字，而每次遍历对应到计算机上就是一次I/O交互，性能很低，所以没有采用这种数据结构。

深入分析过程请参考我之前的文章《MySQL索引底层数据结构及原理深入分析》

B+Tree和B-Tree有什么区别？

B+Tree的叶子节点存放key和value，非叶子节点只有key，而B-树的所有节点既有key又有value

B+树叶子节点之间有指针，B-树没有。非叶子节点之间跟B-Tree一样没有指针。

你能画一下索引结构吗？

画图是最直观的，面试时如果没要求你画图，也建议你自己边回答边画图，能够让面试官比较直观地理解你的回答。

下图是以InnoDB主键索引为例画地索引结构。

树的每个节点都有两个域：key和value。key保存索引字段，这里就是主键id，value保存的数据分情况而论，非叶子节点存的是下一个数据页的地址，叶子节点存的是索引以外的其余字段。所以对于InnoDB主键索引来说叶子节点存的是完整行数据。

B+树为什么叶子节点为什么不存数据？

非叶子节点只存储key，叶子节点存储了一份完整表的所有行的索引字段，value部分是每个索引元素对应的行记录的位置或行数据本身。这样非叶子节点的每个节点只存储key即索引，对于同一片大小的存储空间就可以存储更多的索引元素。

B+树中为什么有那么多重复的索引值？

目的是提高查找效率，类似于二分查找的思想。一个节点上的索引从左到右顺序递增，提取出中间值，放到非叶子节点上，在查找时，根据索引key的进行区间比较，确定数据落在哪个区间。

什么是聚集索引和非聚集索引？

从宏观定义上来讲：

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。

也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

对于聚集索引来说索引即数据。

非聚集索引：恰恰和聚集索引相反，非聚集索引的索引顺序与物理存储顺序不同。

从索引结构上来讲：

叶子节点包含了完整的数据记录的（节点的key为索引，value为完整的行数据）叫聚集索引（聚簇索引），InnoDB的主键索引就是一个聚集索引，他的索引和数据是在一个文件中。

叶子节点的data域存储的是主键值或行记录的内存地址的叫非聚集索引，存储引擎为MYISAM的表的索引类型为非聚集索引，它的索引和数据是分开存储的。

存储引擎是修饰什么?

mysql常见的存储引擎有InnoDB存储引擎，MYISAM存储引擎，那存储引擎是形容mysql数据库的还是某一张表的？

修饰的是表，尽管数据库级别也有存储引擎选项，但最终还是以表的存储引擎为主的。

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

因为InnoDB表的表数据本身就是存储在一棵B+树上，默认主键作为索引key，行数据为索引value，所以必须要有主键。

那为什么建InnoDB表的时候没有建主键，表也能建成功？

因为数据库会判断是否显式的指定了主键，如果没有的话会看是否有唯一索引，有的话会把唯一索引的列当做聚集索引的索引key，如果没有的话数据库内部会生成一个隐式的，类似于rowID的自增列作为主键，只不过我们看不到。

主键为什么推荐整形且自增的呢？

推荐整形是因为在进行大小比较时，整形数据比字符串类型数据效率更高，字符串需要对每一个字符进行比较，比如两个很长的串，前面都一样，只有最后一个字符不一样。

推荐自增是因为InnoDB表是根据主键组织数据的，如果不是自增的每次新增数据时都要根据主键值判断该放在哪，也就是会发生数据位置的调整，效率比较低，所以推荐自增，

MySQL一张表最多能存多少数据？

MySQL的数据以页为单位，每一页的大小是16K，假设主键是int类型占4个字节，加上每个非叶子节点的value域（MySQL分配的是6个字节），一页能存多少个索引，16KB * 1024 / (4+6) = 1,638个，假设树高为三层，前两层 1,638 * 1,638 = 2,683,044个索引，第三层有索引又有行数据，假设行数据2K，那么一个数据页可以存16 / 2 = 8个行记录，那么可以存2,683,044 * 8 = 21,464,352，2千多万。这不是绝对的，和行记录的大小有关。

索引和数据存放位置？

和表的存储引擎类型有关。

InnoDB存储引擎：索引和数据存放在xxx.ibd文件。

myisam存储引擎：索引数据存放在xxx.myi文件，表数据存放在xxx.myd文件。

联合索引是怎么存储的？

相比较于单列索引，联合索引中的索引key按索引中的列的顺序依次排列，先按第一列排序，第一列相同再看第二列，依次类推。

如下图，索引列为(部门编号, 职位, 日期)，那么B+树上会先按第一个字段部门编号排序，如果相同，再看第二个字段职位（字符串排序），职位一样再看第三个字段日期。

索引不唯一时key对应的value是列表吗？

不是列表。节点的key会冗余，value是对应的主键值或行记录的内存地址。查询时找到了一个还会继续往后查找，所以查询开销会增加。

创建索引的时候都会考虑哪些因素?

一般对于查询概率比较高，经常作为where条件的字段设置索引。

建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合。

如果需要建立联合索引的话,还需要考虑联合索引中的顺序，此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力。这些都和实际的表结构以及查询方式有关。

① 适合建立索引的列是出现在where 子句中的列，或者连接子句中指定的列；

② 基数较小的类，索引效果较差，没有必要在此列建立索引；

③ 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；

④ 不要过度地索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只需要保持所需要的索引即可，不应过度索引，这样更利于查询。

⑤ 避免where 子句对字段施加函数，否则不会命中索引；

⑥ 在使用InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键而不要使用业务主键；

⑦ 将打算加索引的列设置为Not null，否则将导致引擎放弃使用索引而进入全表扫描；

⑧ 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

其实回答面试官，用上面的的几点即可。下面为拓展知识：

分两种情况来说：

1）哪些情况需要建立索引：

① 主键自动建立唯一索引；

② 频繁作为查询条件的字段应该建立索引

③查询中与其他表关联的字段，外键关系建立索引；

④ 单键/组合索引的选择问题，组合索引的性价比更高些；

⑤ 在经常需要排序的列创建索引，因为索引已排序，这样查询可以利用索引的排序，加快排序查询时间；

2）哪些情况不需要建立索引

① 表记录太少；

② 经常增删查改的表或字段；因为虽然索引提高查询的速度，同时却降低更新表的速度，因为更新表时，MySQL不仅需要保存数据，还要保存一下索引文件，所以会带来很大的性能开销。

③ where 条件里用不到的字段就不创建索引；

④ 过滤性不好的就不要创建索引了，比如说，性别。

本答案来源于CSDN，链接：http://t.csdn.cn/j4jK8

联合索引多个字段之间顺序如何选择？

把识别度最高的字段放到最前面，把查询最频繁的放到最前面。

识别度：重复项越低识别度越高，即通过某个字段能过滤掉的记录越多识别度越高。

什么是最左前缀匹配原则？

在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(field1,field2,field3)，相当于创建了（field1）、(field1,field2)和(field1,field2,field3)三个索引，这就是最左匹配原则。

通俗点讲，就是where条件后的列，从索引列的最左边看，是否能匹配。比如，where条件只有field2，从索引最左边看的话，无法匹配，因为最左边是field1，where后面没有field1，略过了field1直接是field2，就没办法匹配到。从联合索引的存储方式就能直观地感受到了。

什么是索引下推优化？

MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下：people表中（zipcode，lastname，firstname）构成一个索引。SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'筛选出符合条件的索引后再返回到MySQL服务端，然后MySQL服务端基于address LIKE '%Main Street%'来判断数据是否符合条件，这样返回给MySQL服务端的索引数又会减少。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

什么是覆盖索引？

覆盖索引是索引的优化技术，是指查询的字段从索引中即可获得，不用再回表，例如索引(field1,field2,field3)，查询select field2 from table where field1 = 1可以说是覆盖了索引。

覆盖索引即需要查询的字段正好都在索引字段中，那么直接根据该索引，就可以查到数据了，而无需回表查询。

今天就先分享这么多，如有不对，欢迎指正、交流。

欢迎关注公众号：BiggerBoy

巴特西