MySQL数据库中的索引（各种索引的区别、索引索引失效原因、索引优化、面试中常问的问题详解）

索引模块

---

1.为什么要使用索引？

• 当查询数据量较小的表时（如只有几十行数据的表），全表扫面的速度依旧很快，当访问数据库较大的表时全表扫面的效率会十分低下，所以要使用索引的方式。

• 避免全表扫描，快速查询数据：设计思想类似于字典

2.什么样的信息能成为索引？

• 主键、唯一键、普通键等（具有较好的区分度的字段都可以）

---

3.索引的数据结构

---

二叉查找树（不适合作为索引）

特点：左子树小于右子树

查找方式：二分查找

时间复杂度：平均 O ( log n ) 最坏情况下：O ( n )，会变成线性的二叉树

缺陷：

1.最坏情况下变成线性二叉树，时间复杂度变成O ( n )

2.在树查找时，每读取一次数据就会发生一次I/O，二叉树的深度越深，I/O的次数越多，而二叉树每个节点最多有两个孩子，若用二叉树作为索引，树的深度会很深，I/O性能消耗太大。

优化思考：把树的节点的数目增加，形成一个多叉树

B树（不适合作为索引）

定义：

• 根节点至少包括两个孩子

• 树中每个节点最多含有m个孩子（m >= 2）

• 除根节点和叶子节点外，其他每个节点至少有 ceil(m/2) 个孩子，ceil为向上取值

• 所有叶子节点都位于同一层

• 假设每个非终端结点中包含有n个关键字信息，其中

  a）Ki（i=1…n）为关键字，且关键字按顺序升序排序K（i-1）<Ki

  说明：如第二行每个节点左边的必须小于右边的（如：8 < 12）

  b）关键字的个数n必须满足：[ceil（m/2）-1]<=n<=m-1

  说明：关键字的个数永远比它孩子的数量少一个（如第二行，每个节点关键字个数为2，孩子数为三）

  c）非叶子结点的指针：P[1]，P[2]……P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]
  指向关键字大于K[M-1]的子树，其它Pi]指向关键字属于（K[i-1]，Ki]）的子树

  说明：每个关键字的最左边的孩子节点的大小都小于此关键字（如：8的孩子节点3，5小于8）；每个关键字的最右边的孩子节点都大于此关键字所在节点所有的数（如：12的孩子节点13和15大于12，8）；关键字子节点中间数的大小在夫父节点所在的大小范围内（如：9，10在8-12的范围内）

时间复杂度：O ( log n )

Q：若插入数据打乱的树的现有结构，树会进行什么变化？

• 通过合并、分裂、上移、下移节点来维持B树的约束

B+树（常用索引）：

定义：基本与B树相同，但是要求

• 非叶子节点的子树指针与关键字个数相同
• 非叶子节点的字数指针P[i]，指向关键字值（ K [i] , K [i+1] ）的子树
  • 说明：如P2指针指向的子树中所有的值必须小于P3子树的所有树的值
• 非叶子节点仅用来索引（这样能存更多的关键字），数据都保存在叶子节点中
• 所有叶子节点均有一个链指针指向下一个叶子节点（这样做是为了支持范围统计）
  • 说明：如搜索大于等于10的数，直接走到P2节点下进行搜索然后直接使用链指针继续横向跨子树进行往后搜索，不需要回到P2节点再从P3…下一个P1…P2…P3这样一步一步搜索，提高效率。

Q：为什么B+树比B树更适合做索引（MySQL）

• 磁盘读写代价低：非叶子节点仅用来索引（这样能存更多的关键字），数据都保存在叶子节点中，相对与B树能存放更多的关键字，磁盘读写代价低（树的深度低，读的关键字更多）

• B+树的查询效率更加稳定，是稳定的O ( log n )

• B+树更有利于对数据库的扫描，如进行范围查询，在普通查询时性能也更好

Hash（哈希索引，非主流索引）

特点：类似于HashMap

查询效率极高（比B+树还高），但不能作为主流索引

缺点：

• 只能用于对等值的查询，不能实现对范围的查询，即：仅能满足“="，“IN"，不能使用范围查询

  • 因为Hash是通过hash算法计算出的hash值进行查找，因为计算过后的hash值的大小顺序不能保证和原数据一致（还可能会有hash冲突），所以不能使用范围查找

• 不能用于对数据的排序操作（也是因为hash算法的原因）

• 对与组合索引，不能使用部分索引查询

  • 因为计算组合索引hash值时，是对整个组合索引一起做hash运算，计算结果不能拆分(即，abc组合索引，使用ab无法命中索引)

• 不能避免全表扫面

• 不稳定：遇到大量Hash值相等的情况，性能并不一定比B+树高（甚至有可能变成线性结构–链表）

BitMap(位图索引，Oracle数据库支持)

Oracle数据库支持，MySQL数据库不支持

特点：数据结构类似于B+树，速度快

缺陷：锁的粒度较大，在发生CRUD时，为防止别的线程修改数据，会加锁（因为位图与行有严格的关系）

适用场景：

适用于统计情况较多的场景，不适应与高并发的场景

---

4.索引类型

Hash索引：类似于HashMap，特点前面有
普通索引：普通字段使用的索引，通常为B+ Tree结构
唯一索引：字段或组合字段唯一的索引
主键索引：主键字段所使用的索引，非空且唯一性，通常为密集索引
联合索引：多个字段组合一起的索引，使用时要注意保证最左匹配原则
全文索引：全文索引，目前只有MyISAM引擎支持，可以在CHAR、VARCHAR或者TEXT类型的列上创建
覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据（select和where字段上都有索引），而不必通过二级索引查到主键之后再去查询数据（不需要再进行回表操作）

5.密集索引和稀疏索引

---

聚集索引

（1）：聚集索引就是以主键创建的索引

（2）：每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放，实际的数据页只能按照一颗 B+ 树进行排序

（3）：表记录的排列顺序和与索引的排列顺序一致

（4）：聚集索引存储记录是物理上连续存在

（5）：聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多

（7）：更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置

非聚集索引

（1）：除了主键以外的索引

（2）：聚集索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块

（3）：聚簇索引适合排序，非聚簇索引不适合用在排序的场合

（4）：聚集索引存储记录是物理上连续存在，非聚集索引是逻辑上的连续。

Q：使用聚集索引为什么查询速度会变快？

• 使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻

Q：建立聚集索引有什么需要注意的地方吗？

• 在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置，索引此时会重排，会造成很大的资源浪费

Q：密集索引和稀疏索引的区别？

• 密集索引文件中的每个搜索码值都对应一个索引值（也就是说，密集索引的索引列中包含数据，可以直接拿到，不需要回表操作，通常为主键索引）

• 稀疏索引文件只为索引码的某些值建立索引项（部分索引列中包含数据，通常需要回表操作）

• 聚簇索引适合排序，非聚簇索引不适合用在排序的场合，因为聚簇索引叶节点本身就是索引和数据按相同顺序放置在一起，索引序即是数据序，数据序即是索引序，所以很快。非聚簇索引叶节点是保留了一个指向数据的指针，索引本身当然是排序的，但是数据并未排序，数据查询的时候需要消耗额外更多的I/O，所以较慢

Q：非聚集索引最多可以有多少个？

• 每个表你最多可以建立249个非聚簇索引。但是需要消耗大量的硬盘空间和内存

6.联合索引

• 如某个表中建有a,b,c三个字段组成的联合索引，同时使用abc三个字段时会命中索引，使用abc，ab，a，都会命中索引，使用bc，ac，b…都无法命中索引

最左匹配原则：

• 范围查询在使用联合索引时，MySQL会一直从左向右匹配，直到遇到范围查询（> , < , between ,like）就停止匹配，相当于遇到了断点，不走索引了。

• 比如：a = 3 and b = 4 and c > 5 and d = 6 如果建立的是（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立（a,b,d,c）的索引则可以用到索引；

• = 和 in是可以乱序的，比如a = 1 and b = 2 and c = 3建立（a,b,c）索引，a,b,c的顺序可以是任意的，因为MySQL的查询优化器会自动优化识别的形式（通常以最左匹配原则优化）。

联合索引的最左匹配原则的成因：

• 联合索引后面的字段是在前面索引的基础上形成的，在进行查询时，后面的索引要根据前面索引才能进行有效的查询。

---

7.为什么索引会失效？

---

（1）：条件是or，如果还想让or条件生效，给or每个字段加个索引

（2）：like开头%

（3）：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引

（4）：where中索引列使用了函数或有运算

• 其他情况欢迎大家留言补充

---

8.数据库事务及隔离级别 详细描述请点击

---

数据库事务特点

ACID： 原子性，一致性，隔离性，永久性

Q：数据库事务说是如何实现的?

（1）：通过预写日志方式实现的，redo和undo机制是数据库实现事务的基础

（2）：redo日志用来在断电/数据库崩溃等状况发生时重演一次刷数据的过程，把redo日志里的数据刷到数据库里，保证了事务 的持久性（Durability）

（3）：undo日志是在事务执行失败的时候撤销对数据库的操作，保证了事务的原子性

Q：数据库事务隔离级别？

（1）：读未提交read-uncommitted-- 脏，不可重复读–幻读 A读取了B未提交的事务，B回滚，A 出现脏读；

（2）：不可重复读read-committed-- 不可重复读–幻读 A只能读B已提交的事务，但是A还没结束，B又更新数据隐式提交，然后A又读了一次出现不可重复读；

（3）：可重复读repeatable-read<默认>-- 幻读 事务开启，不允许其他事务的UPDATE修改操作 A读取B已提交的事务，然而B在该表插入新的行，之后A在读取的时候多出一行，出现幻读；

（4）：串行化serializable–

事务相关详细描述请点击

---

关于索引的其他面试题

---

Q：索引是建立的越多越好吗？

• 数据量小的表不需要建立索引，建立会增加额外的索引开销
• 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
• 更多的索引也需要更多的空间

Q：InnoDB 表对主键生成策略是什么样的？

• 优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id隐藏列作为主键。

Q：数据库索引优缺点

（1）：需要查询，排序，分组和联合操作的字段适合建立索引
（2）：索引多，数据更新表越慢，尽量使用字段值不重复比例大的字段作为索引，联合索引比多个独立索引效率高
（3）：对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引
（4）：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

Q：索引的底层实现是B+树，为何不采用红黑树，B树?

（1）：B+Tree非叶子节点只存储键值信息，降低B+Tree的高度，所有叶子节点之间都有一个链指针，数据记录都存放在叶子节点中

（2）：红黑树这种结构，高度明显要深的多，效率明显比B-Tree差很多

（3）：B+树也存在劣势，由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛

---

PS：欢迎大家留言补充和指正

---

Spring中七种事务的传播特性

（1）Propagation.REQUIRED<默认> 如果当前存在事务，则加入该事务，如果当前不存在事务，则创建一个新的事务。

（2）Propagation.SUPPORTS 如果当前存在事务，则加入该事务；如果当前不存在事务，则以非事务的方式继续运行。

（3）Propagation.MANDATORY 如果当前存在事务，则加入该事务；如果当前不存在事务，则抛出异常。

（4）Propagation.REQUIRES_NEW 重新创建一个新的事务，如果当前存在事务，延缓当前的事务。

（5）Propagation.NOT_SUPPORTED 以非事务的方式运行，如果当前存在事务，暂停当前的事务。

（6）Propagation.NEVER 以非事务的方式运行，如果当前存在事务，则抛出异常。

（7）Propagation.NESTED 如果没有，就新建一个事务；如果有，就在当前事务中嵌套其他事务。