5年Java开发经验，面试挂在MySQL InnoDB上！大厂究竟多看重MySQL？

最新推荐文章于 2022-12-05 11:33:46 发布

Java架构技术官

最新推荐文章于 2022-12-05 11:33:46 发布

阅读量163

点赞数

文章标签： mysql 面试 java 索引

本文链接：https://blog.csdn.net/rexuejava/article/details/108301017

版权

前一段时间好兄弟找工作，面试 Java 资深研发工程师岗位，接到了不少大厂的面试邀请，有顺利接到 offer 的，也有半道儿面试被卡掉的。但最想去的企业却因为 MySQL表存储引擎 InnoDB ，与 offer 失之交臂。

相关的面试问题也背了不少，但在实际的回答中还是欠点意思。虽然工作多年，但搞不懂背后的原理其实还是很吃亏，很多内容哪怕背过了答案，其实还是一知半解，不能很快的直击问题的本质。

MySQL 在面试中高频出现，所以弄懂它真的非常有必要。为了帮助更多人理解MySQL，所以我们这次就针对MySQL InnoDB 实现原理进行深入剖析来对MySQL有更多的认识。

InnoDB

熟悉MySQL的人，都知道InnoDB存储引擎，如大家所知，Redo Log是innodb的核心事务日志之一，innodb写入Redo Log后就会提交事务，而非写入到Datafile。之后innodb再异步地将新事务的数据异步地写入Datafile，真正存储起来。

InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

Innodb是事务性数据库的首选引擎，支持ACID事物，支持行级锁定，高性能处理大量数据。

Innodb给mysql提供了具有 ’事务、回滚和崩溃修复能力、多版本并发控制的事务安全型表。

InnoDB 架构

InnoDB兼具高可靠性和高性能。

在MySQL 5.6中，InnoDB是默认的官方推荐的存储引擎。

InnoDB的整体架构图：

(请忽略图中的XTraDB）

InnoDB 的架构分为两块：内存中的结构和磁盘上的结构。InnoDB 使用日志先行策略，将数据修改先在内存中完成，并且将事务记录成重做日志(Redo Log)，转换为顺序IO高效的提交事务。

这里日志先行，说的是日志记录到数据库以后，对应的事务就可以返回给用户，表示事务完成。但是实际上，这个数据可能还只在内存中修改完，并没有刷到磁盘上去。内存是易失的，如果在数据落地前，机器挂了，那么这部分数据就丢失了。

InnoDB 通过 redo 日志来保证数据的一致性。如果保存所有的重做日志，显然可以在系统崩溃时根据日志重建数据。

当然记录所有的重做日志不太现实，所以 InnoDB 引入了检查点机制。即定期检查，保证检查点之前的日志都已经写到磁盘，则下次恢复只需要从检查点开始。

Innodb存储引擎索引的实现原理

在数据库当中，索引就跟书的目录一样用来加快数据的查找速度，对于一个SQL查询操作，根据索引快速过滤掉不符合要求的数据并定位到符合要求的数据，从而不需要扫描整个表来获取所需的数据。
在innodb存储引擎中，主要是基于B+树来实现索引，在非叶子节点存放索引关键字（所以如果建了多个独立索引，则对应多棵B+树，这样对于非主键索引，则叶子节点存放的是主键索引的主键值，需要通过二次查找主键索引所在的B+树获取对应的数据行，这也叫回表查询），在叶子节点存放数据记录（此时为主键索引或者说是聚簇索引，即数据行和索引存放在一起的索引）或者主键索引中的主键值（此时为非聚簇索引），所有的数据记录都在同一层，叶子节点，即数据记录直接之间通过指针相连，构成一个双向链表，从而可以方便地遍历到所有的或者某一范围的数据记录。

B树，B+树

B树和B+树都是多路平衡搜索树，通过在每个节点存放更多的关键字和通过旋转、分裂操作来保持树的平衡来降低树的高度，从而减少数据检索的磁盘访问量。

B+树相对于B树的一个主要的不同点是B+的叶子节点通过指针前后相连，具体为通过双向链表来前后相连，所以非常适合执行范围查找。

innodb存储引擎的聚簇和非聚簇索引都是基于B+树实现的。

主键索引

innodb存储引擎使用主键索引作为表的聚簇索引，聚簇索引的特点是非叶子节点存放主键作为查找关键字，叶子节点存放实际的数据记录本身（也称为数据页），从左到右以关键字的顺序，存放数据记录，故聚簇索引其实就是数据存放的方式，所以每个表只能存在一个聚簇索引，innodb存储引擎的数据表也称为索引组织表。结构如下：（图片引自《MySQL技术内幕：Innodb存储引擎》）

在查询当中，如果是通过主键来查找数据，即使用explain分析SQL的key显示PRIMARY时，查找效率是最高的，因为叶子节点存放的就是数据记录本身，所有可以直接返回，而不需要像非聚簇索引一样需要通过额外回表查询（在主键索引中）获取数据记录。

其次是对于ORDER BY排序操作，不管是正序ASC还是逆序DESC，如果ORDER BY的列是主键，则由于主键索引对应的B+树本身是有序的，故存储引擎返回的数据就是已经根据主键有序的，不需要在MySQL服务器层再进行排序，提高了性能，如果通过explain分析SQL时，extra显示Using filesort，则说明需要在MySQL服务器层进行排序，此时可能需要使用临时表或者外部文件排序，这种情况一般需要想办法优化。

对于基于主键的范围查找，由于聚簇索引的叶子节点已经根据主键的顺序，使用双向链表进行了相连，故可以快速找到某一范围的数据记录。

辅助索引

辅助索引也称为二级索引，是一种非聚簇索引，一般是为了提高某些查询的效率而设计的，即使用该索引列查询时，通过辅助索引来避免全表扫描。由于辅助索引不是聚簇索引，每个表可以存在多个辅助索引，结构如下：

辅助索引的非叶子节存放索引列的关键字，叶子节点存放对应聚簇索引（或者说是主键索引）的主键值。即通过辅助索引定位到需要的数据后，如果不能通过索引覆盖所需列，即通过该辅助索引列来获取该次查询所需的所有数据列，则需要通过该对应聚簇索引的主键值定位到在聚簇索引中的主键，然后再通过该主键值在聚簇索引中找到对应的叶子页，从而获取到对应的数据记录，所以整个过程涉及到先在辅助索引中查找，再在聚簇索引（即主键索引）中查找（回表查询）两个过程。
举个例子：
1. 辅助索引对应的B+树的高度为3，则需要3次磁盘IO来定位到叶子节点，其中叶子节点包含对应聚簇索引的某个主键值；
2. 然后通过叶子节点的对应聚簇索引的主键值，在聚簇索引中找到对应的数据记录，即如果聚簇索引对应的B+树高度也是3，则也需要3次磁盘IO来定位到聚簇索引的叶子页，从而在该叶子页中获取实际的数据记录。
以上过程总共需要进行6次磁盘IO。故如果需要回表查询的数据行较多，则所需的磁盘IO将会成倍增加，查询性能会下降。所以需要在过滤程度高，即重复数据少的列来建立辅助索引。

Cardinality：索引列的数据重复度

由以上分析可知，通过辅助索引进行查询时，如果需要回表查询并且查询的数据行较多时，需要大量的磁盘IO来获取数据，故这种索引不但没有提供查询性能，反而会降低查询性能，并且MySQL优化器在需要返回较多数据行时，也会放弃使用该索引，直接进行全表扫描。所以辅助索引所选择的列需要是重复度低的列，即一般查询后只需要返回一两行数据。如果该列存在太多的重复值，则需要考虑放弃在该列建立辅助索引。

具体可以通过：SHOW INDEX FROM 数据表，的Cardinality的值来判断：

mysql> SHOW INDEX FROM store_order;+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| store_order   |          0 | PRIMARY    |            1 | store_id    | A         |         201 |     NULL | NULL   |      | BTREE      |         |               || store_order   |          1 | idx_expire |            1 | expire_date | A         |          68 |     NULL | NULL   | YES  | BTREE      |         |               || store_order   |          1 | idx_ul     |            1 | ul          | A         |          22 |     NULL | NULL   | YES  | BTREE      |         |               |+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.01 sec)

Cardinality表示索引列的唯一值的估计数量，如果跟数据行的数量接近，则说明该列存在的重复值少，列的过滤性较好；如果相差太大，即Cardinality / 数据行总数，的值太小，如性别列只包含“男”，“女”两个值，则说明该列存在大量重复值，需要考虑是否删除该索引。

覆盖索引

由于回表查询开销较大，故为了减少回表查询的次数，可以在辅助索引中增加查询所需要的所有列，如使用联合索引，这样可以从辅助索引中获取查询所需的所有数据（由于辅助索引的叶子页包含主键值，即使索引没有该主键值，如果只需返回主键值和索引列，则也会使用覆盖索引），不需要回表查询完整的数据行，从而提高性能，这种机制称为覆盖索引。
当使用explain分析查询SQL时，如果extra显示 using index 则说明使用了覆盖索引返回数据，该查询性能较高。
由于索引的存在会增加更新数据的开销，即更新数据时，如增加和删除数据行，需要通过更新对应的辅助索引，故在具体设计时，需要在两者之间取个折中。

联合索引与最左前戳匹配

联合索引是使用多个列作为索引，如（a,b,c)，表示使用a，b，c三个列来作为索引，由B+树的特征可知，索引都是需要符合最左前戳匹配的，故其实相当于建立a，（a,b），(a,b,c)三个索引。
所以在设计联合索引时，除了需要考虑是否可以优化为覆盖索引外，还需要考虑多个列的顺序，一般的经验是：查询频率最高，过滤性最好（重复值较少)的列在前，即左边。

联合索引优化排序order by

除此之外，可以考虑通过联合索引来减少MySQL服务端层的排序，如用户订单表包含联合索引(user_id, buy_date)，单列索引(user_id)：（注意这里只是为了演示联合索引，实际项目，只需联合索引即可，如上所述，(a,b)，相当于a, (a,b)两个索引）：

KEY `idx_user_id` (`user_id`),KEY `idx_user_id_buy_date` (`user_id`,`buy_date`)

如果只是普通的查询某个用户的订单，则innodb会使用user_id索引，如下：

mysql> explain select user_id, order_id  from t_order where user_id = 1;+----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys                    | key         | key_len | ref   | rows | filtered | Extra       |+----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | t_order | NULL       | ref  | idx_user_id,idx_user_id_buy_date | idx_user_id | 4       | const |    4 |   100.00 | Using index |+----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

但是当需要基于购买日期buy_date来排序并取出该用户最近3天的购买记录时，则单列索引user_id和联合索引（user_id, buy_date）都可以使用，innodb会选择使用联合索引，因为在该联合索引中buy_date已经有序了，故不需要再在MySQL服务器层进行一次排序，从而提高了性能，如下：

mysql> explain select user_id, order_id  from t_order where user_id = 1 order by buy_date limit 3;+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+--------------------------+| id | select_type | table   | partitions | type | possible_keys                    | key                  | key_len | ref   | rows | filtered | Extra                    |+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+--------------------------+|  1 | SIMPLE      | t_order | NULL       | ref  | idx_user_id,idx_user_id_buy_date | idx_user_id_buy_date | 4       | const |    4 |   100.00 | Using where; Using index |+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.01 sec)

如果删除idx_user_id_buy_date这个联合索引，则显示Using filesort：

mysql> alter table t_order drop index idx_user_id_buy_date;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select user_id, order_id  from t_order where user_id = 1 order by buy_date limit 3;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+|  1 | SIMPLE      | t_order | NULL       | ALL  | idx_user_id   | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)

InnoDB和ACID模型

A: 原子性(atomicity)

C: 一致性(consistency)

I: 隔离行(isolation)

D: 持久性(durability)

事务的作用: 事务会把数据库从一种一致的状态转换为另一种一致状态。

oracle和sql server的默认隔离级别(read committed),不满足隔离性的要求.

但mysql InnoDB的默认隔离级别(read repeatable),完全满足ACID特性.

ACID其实是mysql的四种特性，见下图：

事务有 ACID 四个属性， InnoDB 是支持事务的，它实现 ACID 的机制如下：

原子性

innodb的原子性主要是通过提供的事务机制实现，与原子性相关的特性有：

Autocommit 设置。
COMMIT 和 ROLLBACK 语句(通过 Undo Log实现)。

一致性

innodb的一致性主要是指保护数据不受系统崩溃影响，相关特性包括：

InnoDB 的双写缓冲区(doublewrite buffer)。
InnoDB 的故障恢复机制(crash recovery)。
Isolation
innodb的隔离性也是主要通过事务机制实现，特别是为事务提供的多种隔离级别，相关特性包括：

Autocommit设置。
SET ISOLATION LEVEL 语句。
InnoDB 锁机制。

持久性

innodb的持久性相关特性：

Redo log。
双写缓冲功能。

可以通过配置项 innodb_doublewrite 开启或者关闭。
配置 innodb_flush_log_at_trx_commit。

用于配置innodb如何写入和刷新 redo 日志缓存到磁盘。

默认为1，表示每次事务提交都会将日志缓存写入并刷到磁盘。

innodb_flush_log_at_timeout 可以配置刷新日志缓存到磁盘的频率，默认是1秒。
配置 sync_binlog。

用于设置同步 binlog 到磁盘的频率，为0表示禁止MySQL同步binlog到磁盘，binlog刷到磁盘的频率由操作系统决定，性能最好但是最不安全。

为1表示每次事务提交前同步到磁盘，性能最差但是最安全。

MySQL文档推荐是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置为 1。
操作系统的 fsync 系统调用。
UPS设备和备份策略等。