MySQL数据库（索引）-EW帮帮网

1、什么是索引?

索引的定义就是帮助存储引擎快速的获取数据的一种数据结构，形象的说就是 索引是数据的目录。

所谓的存储引擎，说白了就是 如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等计数的实现方法。 MySQL 存储引擎有 MyISAM 、InnoDB、Memory，其中 I nnoDB 是在 MySQL 5.5 之后成为默认的存储引擎 。

2、索引的分类 ?

按「数据结构」分类： B+tree索引、Hash索引（hash碰撞）、Full-text索引 。
按「物理存储」分类： 聚簇索引（主键索引）、二级索引（辅助索引） 。
按「字段特性」分类： 主键索引、唯一索引、普通索引、前缀索引 。
按「字段个数」分类： 单列索引、联合索引 。一定要符合最左匹配原则。

一、按「数据结构」分类

MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型：

二、按照物理特性分类

从物理存储的角度来看，索引分为 聚簇索引（主键索引）、 二级索引（辅助索引）。

这两个区别在前面也提到了：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；

二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以， 在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

三、按照字段特性分类

主键索引：建⽴在主键字段上的索引，⼀张表最多只能有⼀个主键索引，不允许有空值。

唯⼀索引：建⽴在 UNIQUE 字段上的索引，⼀张表可以有多个唯⼀索引，索引列的值必须唯⼀，但是允许有空值。

普通索引：建⽴在普通字段上的索引

前缀索引：对字符类型字段的前⼏个字符建⽴的索引，⽽不是在整个字段上建⽴的索引，可以减⼩索引的⼤⼩，适⽤于较⻓列值的情况。

覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index） 。

四、按照字段个数分类

单列索引：建⽴在单列上的索引称为单列索引，⽐如主键索引；

联合索引：由多个列组合⽽成的索引。适⽤于多列的查询条件

使用联合索引时，存在最左匹配原则，也就是按照最左优先的⽅式进⾏索引的匹配。

索引下推优化（index condition pushdown) 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满⾜条件的记录，减少回表次数。

3、什么时候不需要创建索引？什么时候适合索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用 物理空间 ，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的 增删改的效率 ，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行 动态维护 。

什么时候适用索引？

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

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

4、有什么优化索引的方法？

常见的四种方法：前缀索引优化、覆盖索引优化、主键索引设置为自增、索引设置为NOT NULL、防止索引失效。

1. 前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引。

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

前缀索引有一定的局限性，例如：

order by 无法使用前缀索引；

无法把前缀索引用作覆盖索引；

2. 覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，可以建立一个联合索引，即 (商品ID, 名称, 价格) 作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

3. 主键索引最好是自增的

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

1. 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

2. 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

4. 索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用，因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表。

5. 防止索引失效

发生索引失效的情况：

1. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

2. 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；

3. 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

4. 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

5、InnoDB是如何存储数据的？

InnoDB 的数据存储方式基于 磁盘页和 B+树索引结构

数据库的记录是按照行来存储的，但是数据库的读取并不是以行为单位的，否则就一次读取只能处理一行数据，效率会非常低。因此 innoDB的数据是按照数据页为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是 以页为单位，将其整体读入内存。

数据库的io操作的最小单位是页， innodb数据页的默认大小是16KB，意味着数据库的每次读写都是以16kb为单位的，一次最少从磁盘中读取16k的内容到内存中，一次最少把内存中的16k内容刷新到磁盘中。数据库包括七个部分

6、b+树是如何进行查询的？

b+树的特点：只有叶子节点才存放了数据，非叶子节点仅用来存放目录项作为索引。

非叶子节点分为不同层次，通过分层来降低每一层的搜索量。

所有节点按照索引键大小进行排序，构建一个双向链表，便于范围查询。

然后通过二分法对每一层进行查询。

7、聚簇索引和二级索引？

索引可以分成聚簇索引和非聚簇索引（二级索引），它们区别就在于叶子节点存放的是什么数据：

聚簇索引的叶子节点存放的是 实际数据 ，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是 主键值 ，而不是实际数据。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

8、InnoDB是如何创建聚簇索引的？

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

9、为什么mysql采用B+树作为索引？

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘io，io次数越多，所消耗的时间也就越大。 所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。 另外，MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。 所以，要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：能在 尽可能少的磁盘的 I/O 操作中完成查询工作；要能高效地查询某一个记录，也要能 高效地执行范围查找

B+树相比B树，查询底层节点的磁盘 I/O 次数更少，主要原因在于每个非叶子节点存储更多的索引，从而降低了树的高度

MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

10、B+树和B树的区别？为什么不用B树？

单点查询

B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到，而从平均时间代价来看，会比 B+ 树稍快一些。

B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

插入和删除的效率

B+ 树有大量的 冗余节点 ，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快；B 树则不同， B 树没有冗余节点 ，删除节点的时候非常复杂，比如删除根节点中的数据，可能涉及复杂的树的变形，B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。而且 B+ 树会自动平衡，不需要像更多复杂的算法，类似红黑树的旋转操作等。

因此，B+ 树的插入和删除效率更高。

范围查询

B 树和 B+ 树等值查询原理基本一致，先从根节点查找，然后对比目标数据的范围，最后递归的进入子节点查找。

因为 B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助。

而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

因此， 存在大量范围检索的场景，适合使用 B+树， 比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的MongoDB。

11、B+树相对于B树、二叉树、hash索引结构的优势有哪些？

B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3-4 层左右，也就是说一次数据查询操作只需要做 34 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+Tree vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

12、mysql单表不要超过2000w行？

索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

13、索引失效有哪些？

1、当我们使用 左或者左右模糊匹配 的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

2、当我们在查询条件中对索引列使用函数，就会导致索引失效。

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

3、当我们在查询条件中对索引列进行 表达式计算 ，也是无法走索引的。原因同上。

4、 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较（”10” → 10） 。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

5、联合索引要能正确使用需要遵循 最左匹配原则 ，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

6、在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

14、MySQL 使用 like “%x“，索引一定会失效吗？

使用左模糊匹配（like “%xx”）并不一定会走全表扫描，关键还是看数据表中的字段。

如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。

再说一个相似，联合索引要遵循最左匹配才能走索引，但是如果数据库表中的字段都是索引的话，即使查询过程中，没有遵循最左匹配原则，也是走全扫描二级索引树(type=index)

15、索引下推？

索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

没有索引下推之前：

存储引擎层先根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户的主键 ID，然后二次回表查询，获取完整的用户数据；
存储引擎层把所有 zipcode = '431200' 的用户数据全部交给 Server 层，Server 层根据MONTH(birthdate) = 3这一条件再进一步做筛选。

有了索引下推之后：

存储引擎层先根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户，然后直接判断 MONTH(birthdate) = 3，筛选出符合条件的主键 ID；
二次回表查询，根据符合条件的主键 ID 去获取完整的用户数据；
存储引擎层把符合条件的用户数据全部交给 Server 层。

可以看出， 除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量

MySQL数据库（索引）