Mysql索引

前言

专栏中的 Java 基础技术部分就已经结束了。接下来的课程将围绕着 Java 横向技术展开。 作为 T 字型工程师，前者代表 Java 技术的深度，后者则代表了 Java 技术的广度。Java 横向技术分为数据库、缓存、数据结构与算法、Linux 系统与运维、设计模式、框架、综述七个专题展开，打造 Java 工程师的综合素质，这也是技术人员在职业生涯中向前一步发展成为架构师、技术经理、技术总监所必须掌握的。

事实上，横向技术每个专题都可以扩展成一个专栏，不过专栏面向的是 Java 程序员，因此会根据 Java 程序员的职位要求对各个专题有选择地进行讲解。

首先是第一个专题，也是最重要的专题数据库。

一、知识结构及面试题目分析

作为 Java 开发人员乃至后端开发人员，数据库技术的重要性无须多言，这其中尤以 MySQL 数据库应用最为广泛。作为关系型数据库的代表，MySQL 一直在不断发展壮大中，其涵盖的知点也越来越丰富。本专栏会选择三方面的内容进行讨论：

1. MySQL 索引：大规模高并发业务场景的利器。
2. MySQL 锁：面试中似是而非的知识点集中营；
3. MySQL 进阶：由 MySQL 扩展而开的其他议题。

这三方面的内容分别对应接下来的三个章节，由于各部分内容较多，大家还可以参照扩展阅读部分进行进一步学习。

二、典型面试例题及思路分析

问题 1：MySQL 支持哪些存储引擎？

MySQL 支持的存储引擎主要有四种：

（1）InnoDB 支持事务，行级锁定和外键，是事务型数据库的首选引擎；MySQL5.5.5 之后的默认存储引擎；

(2）MyISAM 拥有较高的插入、查询速度，但不支持事务。MySQL5.5.5 之前的默认存储引擎；

（3）Memory 基于散列，存储在内存中，对临时表有用。常见的应用场景是：临时存放数据，数据量不大，并且不需要较高的数据安全性；

（4）Archive 支持高并发的插入操作，但是本身不是事务安全的。常见的应用场景：存储归档数据，如记录日志信息可以使用 Archive。

追问：InnoDB 和 MyISAM 有什么区别呢？

（1）InnoDB 支持事务；而 MyISAM 不支持事物，强调的是性能，查询速度更快；

（2）InnoDB 支持行级锁和表级锁（默认行级锁），而 MyISAM 只支持表级锁；

（3）InnoDB 支持 MVCC, 而 MyISAM 不支持 MVCC；

（4）InnoDB 支持外键，而 MyISAM 不支持外键；

（5）InnoDB 早期版本不支持全文索引（从 MySQL5.6 开始支持全文索引），而 MyISAM 支持；

（7）InnoDB 不保存表的具体行数，count () 时要扫描一遍整个表来计算有多少行；MyISAM 则内置了一个计数器，count () 时它直接从计数器中读。

点评：

这类 “相同点与不同点” 的问题之前出现过多次，这里对这类问题也稍作一下总结，这类问题呢通常大家都是机械式罗列一二三四，这样回答不能说是错误，但是很难出彩。在面试中针对任务问题都不要表现出现死记硬背的样子，可以从三个方面进行着手：一是在语义不失真的情况下尽量口语化一些；二是将各个小点与自己生产实践有机结合，比如说自己在什么情况使用了 MyISAM（当然没有的话就算了）等；三是结合 MySQL 的版本特点，说说 MySQL 最新版本的信息，向面试官透露候选人是经常关注热点技术的人。在此基础上，不同点是七个还是五个反而没那么重要。

再回到这个问题，MySQL 实际上还支持 BLACKHOLE、CSV 等存储引擎（可通过 SHOW ENGINES 命令查看），不过很小众，就不再展开了。

在 MySQL 中，索引就是在存储引擎层实现的，不同存储引擎的索引的工作方式并不一样，即使不同的存储引擎支持同一种类型的索引，其底层的实现也可能不同。而这些存储引擎中以 InnoDB 应用最广泛，很多面试题也围绕着它展开。

问题 2：MySQL 索引底层是什么结构？为什么采用此结构？

mysql 底层采用 B + 树的存储结构。采用 B + 树的原因：

（1）索引文件很大，不能全部存储在内存中，只能存储到磁盘上，因此索引的数据结构要尽量减少查找过程中磁盘 I/O 的存取次数；

（2）数据库系统利用了磁盘预读原理和磁盘预读，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。而 B + 树的高度是 2~4，检索一次最多只需要访问 4 个节点（4 次，即树的高度）。

点评：

数据结构不是凭空产生的，一定会有它的背景和使用场景。那么理想的索引数据结构就需要把磁盘 IO 次数控制在一个很小的数量级，最好是常数数量级。显然一个高度可控的多路搜索树符合此要求，b + 树应运而生。事实上，B + 树索引并不能直接找到具体的行，只是找到被查找行所在的页，然后 DB 通过把整页读入内存（磁盘预读），再在内存中查找（局部性原理，即当一个数据被用到时，其附近的数据也通常会马上被使用）。

为什么不适用 B 树？

下面两张图分别是 B 树和 B + 树的示意图，可以看出有一个很大的区别：B + 树所有的 Data 域在叶子节点，其余节点用来索引，而 B 树是每个索引节点都会有 Data 域；并且 B + 树所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据，从而支持区分查询。在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的遍历操作。

B 树

B + 树

为什么不用哈希表？

（1）哈希表只适用与查找等值查询， 不能支持区分条件（大于小于查询）、模糊查询等；

（2）hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 hash 碰撞，此时效率可能极差。而 B + 树的查询效率比较稳定。

为什么不用红黑树？

红黑树往往高度过大，从页造成磁盘 IO 读写过于频繁，效率低下。而且逻辑上很近的节点（父子）物理上可能很远，无法利用局部性原理。

问题 3：MySQL 支持的索引类型是哪些？

（1）普通索引：用表中的普通列构建的索引，没有任何限制；

（2）唯一索引：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；

（3）主键索引：是一种特殊的唯一索引，根据主键建立索引，不允许重复，不允许空值；

（4）全文索引：通过过建立倒排索引，快速匹配文档的方式。MySQL 5.7.6 之前仅支持英文，MySQL 5.7.6 之后支持中文；

（5）组合索引：又叫联合索引。用多个列组合构建的索引，这多个列中的值不允许有空值。可以在创建表的时候指定，也可以修改表结构。

追问：了解聚集索引和非聚集索引吗？

（1）聚集索引 (clustered index)，又称为主索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。因为数据真正的数据只能有一种排序方式，所以一个表上只能有一个聚簇索引。

（2）非聚集索引 (secondary index)，又称为辅助索引、普通索引，该索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表可以包含多个非聚集索引。

点评：

聚集索引 / 非聚集索引不是一种索引类型，而是一种存储数据的方式。在 InnoDB 中它们还有一个非常重要的区别：聚集索引的叶子节点的的 data 域包含了完整的数据记录，而非聚集索引的叶子节点的 data 域记录着主键的值，因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找，这称之为回表查询。

问题 4：索引什么时候会失效？

索引失效通常有以下原因：

（1）条件中有 or；

（2）like 查询（以 % 开头）；

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

（4）对列进行函数运算（如 where md5 (password) = “xxxx”）；

（5）负向查询条件会导致无法使用索引，比如 NOT IN,NOT LIKE,!= 等；

（6）对于联合索引，不是使用的第一部分 (第一个)，则不会使用索引（最左匹配）；

（7）如果 mysql 评估使用全表扫描要比使用索引快，则不使用索引；

追问 1：表 A 建立了 INDEX (col1,col2,col3) 的联合索引，where 条件中使用 col1 = a1 and col3=c1 索引是否有效？

索引有效。

点评：

这里涉及联合索引的最左前缀匹配原则，顾名思义，它是指在检索数据时从联合索引的最左边开始匹配。如果你创建一个了联合索引 (col1, col2, col3)，那么该索引的任何前缀都会用于查询，即 (col1), (col1, col2), (col1, col2, col3) 都会使用索引查询。

其实最左匹配原则也是和 B + 树的结构相关，比如使用 (col1, col2, col3) 上述索引，B + 树是按照从左到右的顺序来建立搜索树的，比如当检索 (‘a1’,‘b1’,‘c1’) 时，B + 树会优先比较’a1’再来确定下一步的搜索方向，如果 col1 相同再依次比较 col2 和 col3，最后得到检索的数据；但当检索 (‘b1’,‘c1’) 这样的没有 col1 的数据时，B + 树就不知道第一步该查哪个节点，所以不能使用索引。而当检索 (‘a1’,‘c1’) 时，B + 树可以用 col1 来指定搜索方向，即使下一个字段 col2 缺失，也能把所有 col1='a1’的数据找出来，然后再匹配 col3='c1’数据。

追问 2：索引有什么缺点？

（1）索引需要额外的占用物理空间，索引越多，所以空间越多；

（2）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的写入速度；

问题 5：MySQL 中 Float、Decimal 类型存储金额的区别？

Float 是非标准数据类型，存储金额时存储的是近似值，存在精度问题；Decimal 是标准数据类型，存储金额时存储的是精确值（以字符串的形式保存数值）。

点评:

这道题属于 MySQL 的基础知识考察，类似于 “Datetime、Timestamp 存储时间的区别”、“Char、Varchar、Varbinary 存储字符的区别”、“设计一个订单表” 这样的题目，考察候选人在平常项目中对 MySQL 知识点的应用。

回到题目本身，在 MySQL 中，Float,Double 属于非标准数据类型，在 DB 中保存的是近似值；Decimal、Numeric 属于标准数据类型，在 DB 中以字符串的形式保存数值。

Float 数值类型用于表示单精度浮点数值，而 Double 数值类型用于表示双精度浮点数值，二者都是浮点型，而 Decimal 是定点型。浮点型和定点型都可以用类型名称后加（M，D）来表示，M 表示该值的总共长度，称为精度，D 表示小数点后面的长度，称为标度。如 float (7,4) 的 可显示为 - 999.9999，MySQL 保存值时进行四舍五入，如果插入 999.00009，则结果为 999.0001。Float 和 Double 在不指定精度时，默认会按照实际的精度来显示；而 Decimal 在不指定精度时，默认整数为 10，小数为 0。所有在对数据求 SUM () 时会出现不同的结果，Float 和 Double 求 SUM 都会出现很多小数点，而 Decimal 求 SUM 得到的是精准数值。

三、总结

MySQL 索引既是 MySQL 面试中的高频题，也是项目中可以提高生产力的有力工具，实践性很强，因此建议大家在项目中尝试。这是它与下一章节的锁不一样的地方，锁相对来说更抽象一些。

四、扩展阅读及思考题

• 链接: Mysql 索引背后的数据结构及算法原理.
• 链接: Mysql 索引原理及慢查询优化.
• 链接: InnoDB 简介.
• 链接: 详解 Mysql 索引原理及其优化.
• 链接: Mysql 优化：如何避免回表查询？什么是索引覆盖？.
• 链接: 官方文档解释 Mysql 最左匹配原则.

问：什么是索引?

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构，可以帮助我们快速的进行数据的查找。

问：索引是个什么样的数据结构呢?

索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。

问：MySQL支持哪些索引？

因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。

问：Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据；B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据.。

那么可以看出他们有以下的不同:

1. hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

2. hash索引不支持使用索引进行排序，原理同上；

3. hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性；

4. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询；

5. hash索引虽然在等值查询上较快,但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度，而不需要使用hash索引。

问：建立索引的原则有哪些？

选择唯一性索引，为经常需要查询、排序、分组和联合操作的字段建立索引，限制索引的数目，最左前缀匹配原则（非常重要的原则），尽量选择区分度高的列作为索引，字段尽力设置不为null，索引列上不计算。

问：主键、外键和唯一索引的区别

主键：唯一标识一条记录，不能有重复的，不允许为空

外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引：该字段没有重复值，但可以有空值

问：什么是回表查询和覆盖索引？

如果使用普通索引，则查询时间先查找到主键值，之后再通过主键值查找到自己想要的数据；需要扫码两遍所引树；这就是回表查询；

索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表;

问： 非聚簇索引一定会回表查询吗?

不一定。

问：使用索引查询一定能提高查询的性能吗？为什么

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索