MySQL面试题（二）

16.一个B+树中大概能存放多少条索引记录？

MySQL设计者将⼀个B+Tree的节点的⼤⼩设置为等于⼀个⻚. (这样做的⽬的是每个节点只需要⼀次I/O就可以完全载⼊), InnoDB的⼀个⻚的⼤⼩是16KB,所以每个节点的⼤⼩也是16KB, 并且B+Tree的根节点是保存在内存中的,⼦节点才是存储在磁盘上.

添加图片注释，不超过 140 字（可选）

假设⼀个B+树⾼为2，即存在⼀个根节点和若⼲个叶⼦节点，那么这棵B+树的存放总记录数为：

根节点指针数*单个叶⼦节点记录⾏数.

计算根节点指针数: 假设表的主键为INT类型,占⽤的就是4个字节,或者是BIGINT占⽤8个字节, 指针⼤⼩为6个字节,那么⼀个⻚(就是B+Tree中的⼀个节点) ,⼤概可以存储: 16384B / (4B+6B) = 1638 ,⼀个节点最多可以存储1638个索引指针.

计算每个叶⼦节点的记录数:我们假设⼀⾏记录的数据⼤⼩为1k,那么⼀⻚就可以存储16⾏数据,16KB / 1KB = 16.

⼀颗⾼度为2的B+Tree可以存放的记录数为: 1638 * 16=26208 条数据记录, 同样的原理可以推算出⼀个⾼度3的B+Tree可以存放: 1638 *1638 * 16 = 42928704条这样的记录.

所以InnoDB中的B+Tree⾼度⼀般为1-3层,就可以满⾜千万级别的数据存储,在查找数据时⼀次⻚的查找代表⼀次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

17.explain ⽤过吗，有哪些主要字段？

使⽤ EXPLAIN 关键字可以模拟优化器来执⾏SQL查询语句，从⽽知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能 瓶颈。

MySQL查询过程

添加图片注释，不超过 140 字（可选）

通过explain我们可以获得以下信息：

1.表的读取顺序

2.数据读取操作的操作类型

3.哪些索引可以被使⽤

4.哪些索引真正被使⽤

5.表的直接引⽤每张表的有多少⾏被优化器查询了

Explain使⽤⽅式: explain+sql语句, 通过执⾏explain可以获得sql语句执⾏的相关信息

explain select * from users;

18.type字段中有哪些常见的值？

type字段在 MySQL 官⽹⽂档描述如下：

The join type. For descriptions of the different types.

type字段显示的是连接类型 ( join type表示的是⽤什么样的⽅式来获取数据)，它描述了找到所需数据所使⽤的扫描⽅式, 是较为重要的⼀个指标。

下⾯给出各种连接类型,按照从最佳类型到最坏类型进⾏排序:

添加图片注释，不超过 140 字（可选）

⼀般来说,需要保证查询⾄少达到 range级别,最好能到ref,否则就要就行SQL的优化调整

下⾯介绍type字段不同值表示的含义：

添加图片注释，不超过 140 字（可选）

19.Extra有哪些主要指标，各自的含义是什么？

Extra 是 EXPLAIN 输出中另外⼀个很重要的列，该列显示MySQL在查询过程中的⼀些详细信息

添加图片注释，不超过 140 字（可选）

20.如何进行分页查询优化？

（1）⼀般性分⻚

⼀般的分⻚查询使⽤简单的 limit ⼦句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

1.第⼀个参数指定第⼀个返回记录⾏的偏移量，注意从0开始；

2.第⼆个参数指定返回记录⾏的最⼤数⽬；

3.如果只给定⼀个参数，它表示返回最⼤的记录⾏数⽬；

思考1：如果偏移量固定，返回记录量对执⾏时间有什么影响？

添加图片注释，不超过 140 字（可选）

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不⼤。随着查询记录量越⼤，所花费的时间也会越来越多。

思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

添加图片注释，不超过 140 字（可选）

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增⼤，查询时间急剧的增加。（这种分⻚查询机制，每次都会从数据库第⼀条记录开始扫描，越往后查询越慢，⽽且查询的数据越多，也会拖慢总查询速度。）

（2）分⻚优化⽅案

优化1: 通过索引进⾏分⻚

直接进⾏limit操作 会产⽣全表扫描,速度很慢. Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

假设ID是连续递增的,我们根据查询的⻚数和查询的记录数可以算出查询的id的范围，然后配合 limit使⽤

EXPLAIN SELECT * FROM user WHERE id >= 100001 LIMIT100;

优化2：利用子查询优化

添加图片注释，不超过 140 字（可选）

原因：使⽤了id做主键⽐较(id>=)，并且⼦查询使⽤了覆盖索引进⾏优化。

21.如何做慢查询优化？

MySQL 慢查询的相关参数解释：

1.slow_query_log：是否开启慢查询⽇志，ON(1)表示开启,OFF(0) 表示关闭。

2.slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询⽇志存储路径。

3.long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录⽇志。

慢查询配置⽅式

1. 默认情况下slow_query_log的值为OFF，表示慢查询⽇志是禁⽤的

添加图片注释，不超过 140 字（可选）

2. 可以通过设置slow_query_log的值来开启

mysql> set global slow_query_log=1;

3. 使⽤ set global slow_query_log=1 开启了慢查询⽇志只对当前数据库⽣效，MySQL重启后则会失效。如果要永久⽣效，就必须修改配置⽂件my.cnf（其它系统变量也是如此）

添加图片注释，不超过 140 字（可选）

4. 那么开启了慢查询⽇志后，什么样的SQL才会记录到慢查询⽇志⾥⾯呢？ 这个是由参数 long_query_time控制，默认情况下long_query_time的值为10秒.

添加图片注释，不超过 140 字（可选）

5. 修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使⽤命令 set globallong_query_time=1 修改后，需要重新连接或新开⼀个会话才能看到修改值。

添加图片注释，不超过 140 字（可选）

6. log_output 参数是指定⽇志的存储⽅式。log_output='FILE' 表示将⽇志存⼊⽂件，默认值是'FILE'。log_output='TABLE' 表示将⽇志存⼊数据库，这样⽇志信息就会被写⼊到 mysql.slow_log 表中。

添加图片注释，不超过 140 字（可选）

MySQL数据库⽀持同时两种⽇志存储⽅式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。⽇志记录到系统的专⽤⽇志表中，要⽐记录到⽂件耗费更多的系统资源，因此对于需要启⽤慢查询⽇志，⼜需要能够获得更⾼的系统性能，那么建议优先记录到⽂件.

7. 系统变量 log-queries-not-using-indexes：未使⽤索引的查询也被记录到慢查询⽇志中（可选项）。如果调优的话，建议开启这个选项。

添加图片注释，不超过 140 字（可选）

添加图片注释，不超过 140 字（可选）

3) 慢查询测试

1. 执⾏ test_index.sql 脚本,监控慢查询⽇志内容

添加图片注释，不超过 140 字（可选）

2. 执⾏下⾯的SQL,执⾏超时 (超过1秒) 我们去查看慢查询⽇志

添加图片注释，不超过 140 字（可选）

3. ⽇志内容

我们得到慢查询⽇志后，最重要的⼀步就是去分析这个⽇志。我们先来看下慢⽇志⾥到底记录了哪些内容。

如下图是慢⽇志⾥其中⼀条SQL的记录内容，可以看到有时间戳，⽤户，查询时⻓及具体的SQL等信息.

添加图片注释，不超过 140 字（可选）

1.Time: 执⾏时间

2.User: ⽤户信息 ,Id信息

3.Query_time: 查询时⻓

4.Lock_time: 等待锁的时⻓

5.Rows_sent:查询结果的⾏数

6.Rows_examined: 查询扫描的⾏数

7.SET timestamp: 时间戳

8.SQL的具体信息

慢查询SQL优化思路

1) SQL性能下降的原因

在⽇常的运维过程中，经常会遇到DBA将⼀些执⾏效率较低的SQL发过来找开发⼈员分析，当我们拿到这个SQL语句之后，在对这些SQL进⾏分析之前，需要明确可能导致SQL执⾏性能下降的原因进⾏分析，执⾏性能下降可以体现在以下两个⽅⾯：

等待时间⻓

锁表导致查询⼀直处于等待状态，后续我们从MySQL锁的机制去分析SQL执⾏的原理

执⾏时间⻓

添加图片注释，不超过 140 字（可选）

2) 慢查询优化思路

1. 优先选择优化⾼并发执⾏的SQL,因为⾼并发的SQL发⽣问题带来后果更严重

添加图片注释，不超过 140 字（可选）

2. 定位优化对象的性能瓶颈(在优化之前了解性能瓶颈在哪)

添加图片注释，不超过 140 字（可选）

3. 明确优化⽬标

添加图片注释，不超过 140 字（可选）

4. 从explain执⾏计划⼊⼿

添加图片注释，不超过 140 字（可选）

5. 永远⽤⼩的结果集驱动⼤的结果集

添加图片注释，不超过 140 字（可选）

6. 尽可能在索引中完成排序

添加图片注释，不超过 140 字（可选）

7. 只获取⾃⼰需要的列

不要使⽤select * ,select * 很可能不⾛索引,⽽且数据量过⼤

8. 只使⽤最有效的过滤条件

误区 where后⾯的条件越多越好,但实际上是应该⽤最短的路径访问到数据

9. 尽可能避免复杂的join和⼦查询

添加图片注释，不超过 140 字（可选）

10. 合理设计并利⽤索引

添加图片注释，不超过 140 字（可选）

22.Hash索引有哪些优缺点？

MySQL中索引的常⽤数据结构有两种: ⼀种是B+Tree,另⼀种则是Hash.Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的 结构。⾮常适合根据key查找value值，也就是单个key查询，或者说等值查询。

添加图片注释，不超过 140 字（可选）

对于每⼀⾏数据，存储引擎都会对所有的索引列计算⼀个哈希码，哈希码是⼀个较⼩的值,如果出现哈希码值相同的情况会拉出⼀条链表.

Hsah索引的优点

1.因为索引⾃身只需要存储对应的Hash值,所以索引结构⾮常紧凑, 只需要做等值⽐较查询，⽽不包含排序或范围查询的需求，都适合使⽤哈希索引 .

2.没有哈希冲突的情况下,等值查询访问哈希索引的数据⾮常快.(如果发⽣Hash冲突,存储引擎必须遍历链表中的所有⾏指针,逐⾏进⾏⽐较,直到找到所有符合条件的⾏).

Hash索引的缺点

1.哈希索引只包含哈希值和⾏指针，⽽不存储字段值，所以不能使⽤索引中的值来避免读取⾏。

2.哈希索引只⽀持等值⽐较查询。不⽀持任何范围查询和部分索引列匹配查找。

3.哈希索引数据并不是按照索引值顺序存储的，所以也就⽆法⽤于排序。

23.说⼀下InnoDB内存相关的参数优化？

1.1 缓冲池内存⼤⼩配置

⼀个⼤的⽇志缓冲区允许⼤量的事务在提交之前不写⽇志到磁盘。因此，如果你有很多事务的更新，插⼊或删除操作，通过设置这个参数会⼤量的减少磁盘I/O的次数数。

建议: 在专⽤数据库服务器上，可以将缓冲池⼤⼩设置为服务器物理内存的60% - 80%

1.查看缓冲池⼤⼩

添加图片注释，不超过 140 字（可选）

2.在线调整InnoDB缓冲池⼤⼩innodb_buffer_pool_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的⼤⼩.

添加图片注释，不超过 140 字（可选）

1.2监控在线调整缓冲池的进度

添加图片注释，不超过 140 字（可选）

1.3 InnoDB 缓存性能评估

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的缓存命中率来验证。

以下公式计算InnoDB buffer pool 命中率:

添加图片注释，不超过 140 字（可选）

添加图片注释，不超过 140 字（可选）

1.4 Page管理相关参数

查看Page⻚的⼤⼩(默认16KB), innodb_page_size只能在初始化MySQL实例之前配置，不能在之后修改。如果没有指定值，则使⽤默认⻚⾯⼤⼩初始化实例。

添加图片注释，不超过 140 字（可选）

Page⻚管理状态相关参数

添加图片注释，不超过 140 字（可选）

1.pages_data: InnoDB缓冲池中包含数据的⻚数。 该数字包括脏⻚⾯和⼲净⻚⾯。

2.pages_dirty: 显示在内存中修改但尚未写⼊数据⽂件的InnoDB缓冲池数据⻚的数量（脏⻚刷新）。

3.pages_flushed: 表示从InnoDB缓冲池中刷新脏⻚的请求数。

4.pages_free: 显示InnoDB缓冲池中的空闲⻚⾯

5.pages_misc: 缓存池中当前已经被⽤作管理⽤途或hash index⽽不能⽤作为普通数据⻚的数⽬pages_total: 缓存池的⻚总数⽬。单位是page。

24.InnoDB⽇志相关的参数优化了解过吗？

1.⽇志缓冲区相关参数配置

⽇志缓冲区的⼤⼩。⼀般默认值16MB是够⽤的，但如果事务之中含有blog/text等⼤字段，这个缓冲区会被很快填满会引起额外的IO负载。配置更⼤的⽇志缓冲区,可以有效的提⾼MySQL的效率.

innodb_log_buffer_size 缓冲区⼤⼩

添加图片注释，不超过 140 字（可选）

innodb_log_files_in_group ⽇志组⽂件个数⽇志组根据需要来创建。⽽⽇志组的成员则需要⾄少2个，实现循环写⼊并作为冗余策略。

添加图片注释，不超过 140 字（可选）

innodb_log_file_size ⽇志⽂件⼤⼩

参数innodb_log_file_size⽤于设定MySQL⽇志组中每个⽇志⽂件的⼤⼩(默认48M)。此参数是⼀个全局的静态参数，不能动态修改。

参数innodb_log_file_size的最⼤值，⼆进制⽇志⽂件⼤⼩（innodb_log_file_size * innodb_log_files_in_group）不能超过

512GB.所以单个⽇志⽂件的⼤⼩不能超过256G.

添加图片注释，不超过 140 字（可选）

2.⽇志⽂件参数优化

⾸先我们先来看⼀下⽇志⽂件⼤⼩设置对性能的影响

设置过⼩

1. 参数 innodb_log_file_size设置太⼩，就会导致MySQL的⽇志⽂件( redo log）频繁切换，频繁的触发数据库的检查点（Checkpoint），导致刷新脏⻚到磁盘的次数增加。从⽽影响IO性能。

2. 处理⼤事务时，将所有的⽇志⽂件写满了，事务内容还没有写完，这样就会导致⽇志不能切换.

设置过⼤

参数 innodb_log_file_size如果设置太⼤，虽然可以提升IO性能，但是当MySQL由于意外宕机时，⼆进制⽇志很⼤，那么恢复的时间必然很⻓。⽽且这个恢复时间往往不可控，受多⽅⾯因素影响。

优化建议:

如何设置合适的⽇志⽂件⼤⼩ ?

根据实际⽣产场景的优化经验,⼀般是计算⼀段时间内⽣成的事务⽇志（redo log）的⼤⼩， ⽽MySQL的⽇志⽂件的⼤⼩最少应该承载⼀个⼩时的业务⽇志量(官⽹⽂档中有说明)。

想要估计⼀下InnoDB redo log的⼤⼩，需要抓取⼀段时间内LogSequenceNumber（⽇志顺序号）的数据,来计算⼀⼩时内产⽣的⽇志⼤⼩.

Log sequence number

⾃系统修改开始，就不断的⽣成redo⽇志。为了记录⼀共⽣成了多少⽇志，于是mysql设计了全局变量log sequence number，简称lsn，但不是从0开始，是从8704字节开始。

添加图片注释，不超过 140 字（可选）

有了⼀分钟的⽇志量,据此推算⼀⼩时内的⽇志量

添加图片注释，不超过 140 字（可选）

太⼤的缓冲池或⾮常不正常的业务负载可能会计算出⾮常⼤(或⾮常⼩)的⽇志⼤⼩。这也是公式不⾜之处，需要根据判断和经验。但这个计算⽅法是⼀个很好的参考标准。

25.InnoDB IO线程相关参数优化了解过吗？

数据库属于 IO 密集型的应⽤程序，其主要职责就是数据的管理及存储⼯作。从内存中读取⼀个数据库数据的时间是微秒级别，⽽从⼀块普通硬盘上读取⼀个IO是在毫秒级别。要优化数据库，IO操作是必须要优化的，尽可能将磁盘IO转化为内存IO。

1) 参数: query_cache_size&have_query_cache

MySQL查询缓存会保存查询返回的完整结果。当查询命中该缓存，会⽴刻返回结果，跳过了解析，优化和执⾏阶段。

查询缓存会跟踪查询中涉及的每个表，如果这些表发⽣变化，那么和这个表相关的所有缓存都将失效。

1. 查看查询缓存是否开启

添加图片注释，不超过 140 字（可选）

2. 开启缓存,在my.ini中添加下⾯⼀⾏参数

添加图片注释，不超过 140 字（可选）

3. 测试能否缓存查询

添加图片注释，不超过 140 字（可选）

1.Qcache_free_blocks:缓存中⽬前剩余的blocks数量（如果值较⼤，则查询缓存中的内存碎⽚过多）

2.Qcache_free_memory:空闲缓存的内存⼤⼩

3.Qcache_hits:命中缓存次数

4.Qcache_inserts: 未命中然后进⾏正常查询

5.Qcache_lowmem_prunes:查询因为内存不⾜⽽被移除出查询缓存记录

6.Qcache_not_cached: 没有被缓存的查询数量

7.Qcache_queries_in_cache:当前缓存中缓存的查询数量

8.Qcache_total_blocks:当前缓存的block数量

优化建议: Query Cache的使⽤需要多个参数配合，其中最为关键的是query_cache_size 和 query_cache_type ，前者设置⽤于缓存 ResultSet的内存⼤⼩，后者设置在何场景下使⽤ Query Cache。

MySQL数据库数据变化相对不多，query_cache_size ⼀般设置为256MB⽐较合适 ,也可以通过计算Query Cache的命中率来进⾏调整

( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )

2. 参数: innodb_max_dirty_pages_pct 该参数是InnoDB 存储引擎⽤来控制buffer pool中脏⻚的百分⽐，当脏⻚数量占⽐超过这个参数设置的值时，InnoDB会启动刷脏⻚的操作。

添加图片注释，不超过 140 字（可选）

优化建议: 该参数⽐例值越⼤，从内存到磁盘的写⼊操作就会相对减少，所以能够⼀定程度下减少写⼊操作的磁盘IO。但是，如果这个⽐例值过⼤，当数据库 Crash 之后重启的时间可能就会很⻓，因为会有⼤量的事务数据需要从⽇志⽂件恢复出来写⼊数据⽂件中.最⼤不建议超过90,⼀般重启恢复的数据在超过1GB的话,启动速度就会变慢.

3) 参数: innodb_old_blocks_pct&innodb_old_blocks_time

innodb_old_blocks_pct ⽤来确定LRU链表中old sublist所占⽐例,默认占⽤37%

添加图片注释，不超过 140 字（可选）

innodb_old_blocks_time ⽤来控制old sublist中page的转移策略，新的page⻚在进⼊LRU链表中时，会先插⼊到old sublist的头部，然后page需要在old sublist中停留innodb_old_blocks_time这么久后，下⼀次对该page的访问才会使其移动到new sublist的头部，默认值1秒.

添加图片注释，不超过 140 字（可选）

优化建议: 在没有⼤表扫描的情况下，并且数据多为频繁使⽤的数据时，我们可以增加innodb_old_blocks_pct的值，并且减⼩innodb_old_blocks_time的值。让数据⻚能够更快和更多的进⼊的热点数据区。

26.什么是写失效？

InnoDB的⻚和操作系统的⻚⼤⼩不⼀致，InnoDB⻚⼤⼩⼀般为16K，操作系统⻚⼤⼩为4K，InnoDB的⻚写⼊到磁盘时，⼀个⻚需要分4次写。

如果存储引擎正在写⼊⻚的数据到磁盘时发⽣了宕机，可能出现⻚只写了⼀部分的情况，⽐如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

添加图片注释，不超过 140 字（可选）

双写缓冲区 Doublewrite Buffer

为了解决写失效问题，InnoDB实现了double write buffer Files, 它位于系统表空间，是⼀个存储区域。

在BufferPool的page⻚刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。这样在宕机重启时，如果出现数据⻚损坏，那么在应⽤redo log之前，需要通过该⻚的副本来还原该⻚，然后再进⾏redo log重做，double write实现了InnoDB引擎数据⻚的可靠性.

默认情况下启⽤双写缓冲区，如果要禁⽤Doublewrite 缓冲区，可以将innodb_doublewrite设置为0。

添加图片注释，不超过 140 字（可选）

数据双写流程

添加图片注释，不超过 140 字（可选）

step1：当进⾏缓冲池中的脏⻚刷新到磁盘的操作时,并不会直接写磁盘,每次脏⻚刷新必须要先写double write .

step2：通过memcpy函数将脏⻚复制到内存中的double write buffer .

step3: double write buffer再分两次、每次1MB, 顺序写⼊共享表空间的物理磁盘上, 第⼀次写.

step4: 在完成double write⻚的写⼊后，再将double wirite buffer中的⻚写⼊各个表的独⽴表空间⽂件中(数据⽂件 .ibd), 第⼆次写。

为什么写两次 ?

可能有的同学会有疑问，为啥写两次，刷⼀次数据⽂件保存数据不就可以了，为什么还要写共享表空间 ?其实是因为共享表空间是在ibdbata⽂件中划出2M连续的空间，专⻔给double write刷脏⻚⽤的, 由于在这个过程中，double write⻚的存储是连续的，因此写⼊磁盘为顺序写，性能很⾼；完成double write后，再将脏⻚写⼊实际的各个表空间⽂件，这时写⼊就是离散的了.

27.什么是⾏溢出？

⾏记录格式

1) ⾏格式分类

表的⾏格式决定了它的⾏是如何物理存储的，这反过来⼜会影响查询和DML操作的性能。如果在单个page⻚中容纳更多⾏，查询和索引查找可以更快地⼯作，缓冲池中所需的内存更少，写⼊更新时所需的I/O更少。

InnoDB存储引擎⽀持四种⾏格式：Redundant、Compact、Dynamic 和Compressed .

查询MySQL使⽤的⾏格式,默认为: dynamic

添加图片注释，不超过 140 字（可选）

指定⾏格式语法

添加图片注释，不超过 140 字（可选）

2) COMPACT ⾏记录格式

Compact 设计⽬标是⾼效地存储数据，⼀个⻚中存放的⾏数据越多，其性能就越⾼。

Compact⾏记录由两部分组成: 记录放⼊额外信息 和 记录的真实数据.

添加图片注释，不超过 140 字（可选）

记录额外信息部分

服务器为了描述⼀条记录⽽添加了⼀些额外信息(元数据信息)，这些额外信息分为3类，分别是: 变⻓字段⻓度列表、NULL值列表和记录头信息.

变⻓字段⻓度列表

MySQL⽀持⼀些变⻓的数据类型，⽐如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，这些变⻓的数据类型占⽤的存储空间分为两部分：

1. 真正的数据内容

2. 占⽤的字节数

变⻓字段的⻓度是不固定的，所以在存储数据的时候要把这些数据占⽤的字节数也存起来，读取数据的时候才能根据这个⻓度列表去读取对应⻓度的数据。

在 Compact⾏格式中，把所有变⻓类型的列的⻓度都存放在记录的开头部位形成⼀个列表，按照列的顺序逆序存放,这个列表就是 变⻓字段⻓度列表。

NULL值列表

表中的某些列可能会存储NULL值，如果把这些NULL值都放到记录的真实数据中会⽐较浪费空间，所以Compact⾏格式把这些值为NULL的列存储到NULL值列表中。( 如果表中所有列都不允许为 NULL，就不存在NULL值列表 )

记录头信息

记录头信息是由固定的5个字节组成，5个字节也就是40个⼆进制位，不同的位代表不同的意思，这些头信息会在后⾯的⼀些功能中看到。

添加图片注释，不超过 140 字（可选）

1. delete_mask

这个属性标记着当前记录是否被删除，占⽤1个⼆进制位，值为0 的时候代表记录并没有被删除，为1 的时候代表记录被删除掉了

2. min_rec_mask

B+树的每层⾮叶⼦节点中的最⼩记录都会添加该标记。

3. n_owned

代表每个分组⾥，所拥有的记录的数量，⼀般是分组⾥主键最⼤值才有的。

4. heap_no在数据⻚的User Records中插⼊的记录是⼀条⼀条紧凑的排列的，这种紧凑排列的结构⼜被称为堆。为了便于管理这个堆，把记录在堆中的相对位置给定⼀个编号——heap_no。所以heap_no这个属性表示当前记录在本⻚中的位置。

5. record_type

这个属性表示当前记录的类型，⼀共有4种类型的记录， 0 表示普通⽤户记录， 1 表示B+树⾮叶节点记录， 2 表示最⼩记录， 3 表示最⼤记录。

6. next_record

表示从当前记录的真实数据到下⼀条记录的真实数据的地址偏移量，可以理解为指向下⼀条记录地址的指针。值为正数说明下⼀条记录在当前记录后⾯，为负数说明下⼀条记录在当前记录的前⾯。

记录真实数据部分

记录的真实数据除了插⼊的那些列的数据，MySQL会为每个记录默认的添加⼀些列（也称为隐藏列），具体的列如下：

添加图片注释，不超过 140 字（可选） 添加图片注释，不超过 140 字（可选）

⽣成隐藏主键列的⽅式有：

添加图片注释，不超过 140 字（可选）

3) Compact中的⾏溢出机制

什么是行溢出 ?

MySQL中是以⻚为基本单位,进⾏磁盘与内存之间的数据交互的,我们知道⼀个⻚的⼤⼩是16KB,16KB = 16384字节.⽽⼀个varchar(m) 类型列最多可以存储65532个字节,⼀些⼤的数据类型⽐如TEXT可以存储更多.

如果⼀个表中存在这样的⼤字段,那么⼀个⻚就⽆法存储⼀条完整的记录.这时就会发⽣⾏溢出,多出的数据就会存储在另外的溢出⻚中.

总结: 如果某些字段信息过⻓，⽆法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出⻚，该字段被称为⻚外列。

Compact中的⾏溢出机制

InnoDB 规定⼀⻚⾄少存储两条记录(B+树特点)，如果⻚中只能存放下⼀条记录，InnoDB存储引擎会⾃动将⾏数据存放到溢出⻚中.

当发⽣⾏溢出时，数据⻚只保存了前768字节的前缀数据，接着是20个字节的偏移量，指向⾏溢出⻚.

添加图片注释，不超过 140 字（可选）

28.如何进行JOIN优化？

JOIN 是 MySQL ⽤来进⾏联表操作的，⽤来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN 操作有多种⽅式，取决于最终数据的合并效果。常⽤连接⽅式的有以下⼏种:

添加图片注释，不超过 140 字（可选）

什么是驱动表 ?

1.多表关联查询时,第⼀个被处理的表就是驱动表,使⽤驱动表去关联其他表.

2.驱动表的确定⾮常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

驱动表的选择要遵循⼀个规则:

1.在对最终的结果集没有影响的前提下,优先选择结果集最⼩的那张表作为驱动表

3) 三种JOIN算法

1.Simple Nested-Loop Join（ 简单的嵌套循环连接 )

简单来说嵌套循环连接算法就是⼀个双层for 循环 ，通过循环外层表的⾏数据，逐个与内层表的所有⾏数据进⾏⽐较来获取结果.

这种算法是最简单的⽅案，性能也⼀般。对内循环没优化。

例如有这样⼀条SQL:

添加图片注释，不超过 140 字（可选）

转换成代码执⾏时的思路是这样的：

添加图片注释，不超过 140 字（可选）

匹配过程如下图

添加图片注释，不超过 140 字（可选）

SNL 的特点

简单粗暴容易理解，就是通过双层循环⽐较数据来获得结果

查询效率会⾮常慢,假设 A 表有 N ⾏，B 表有 M ⾏。SNL 的开销如下：

1.A 表扫描 1 次。

2.B 表扫描 M 次。

3.⼀共有 N 个内循环，每个内循环要 M 次，⼀共有内循环 N * M次

2) Index Nested-Loop Join（ 索引嵌套循环连接 ）

Index Nested-Loop Join 其优化的思路: 主要是为了减少内层表数据的匹配次数 , 最⼤的区别在于，⽤来进⾏ join 的字段已经在被驱动表中建⽴了索引。

从原来的 匹配次数 = 外层表⾏数 * 内层表⾏数 , 变成了 匹配次数 = 外层表的⾏数 * 内层表索引的⾼度 ，极⼤的提升了 join的性能。

当 order 表的 user_id 为索引的时候执⾏过程会如下图：

添加图片注释，不超过 140 字（可选）

注意：使⽤Index Nested-Loop Join 算法的前提是匹配的字段必须建⽴了索引。

3) Block Nested-Loop Join( 块嵌套循环连接 )

如果 join 的字段有索引，MySQL 会使⽤ INL 算法。如果没有的话，MySQL 会如何处理？

因为不存在索引了，所以被驱动表需要进⾏扫描。这⾥ MySQL 并不会简单粗暴的应⽤ SNL 算法，⽽是加⼊了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

添加图片注释，不超过 140 字（可选）

1.在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进⾏ join⽤到的列都缓存到 buffer 中。buffer 中的数据有⼀个特点，⾥⾯的记录不需要⼀条⼀条地取出来和 order 表进⾏⽐较，⽽是整个 buffer 和order表进⾏批量⽐较。

2.如果我们把 buffer 的空间开得很⼤，可以容纳下 user 表的所有记录，那么 order 表也只需要访问⼀次。

3.MySQL 默认 buffer ⼤⼩ 256K，如果有 n 个 join 操作，会⽣成 n-1 个join buffer。

添加图片注释，不超过 140 字（可选）

4) JOIN优化总结

1. 永远⽤⼩结果集驱动⼤结果集(其本质就是减少外层循环的数据数量)

2. 为匹配的条件增加索引(减少内层表的循环匹配次数)

3. 增⼤join buffer size的⼤⼩（⼀次缓存的数据越多，那么内层包的扫表次数就越少）

4. 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

29.索引哪些情况下会失效？

1. 查询条件包含 or，会导致索引失效。

2. 隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们where age = “1”，这样就会触发隐式类型转换

3. like 通配符会导致索引失效，注意:”ABC%” 不会失效，会⾛ range 索引，”% ABC” 索引会失效

4. 联合索引，查询时的条件列不是联合索引中的第⼀个列，索引失效。

5. 对索引字段进⾏函数运算。

6. 对索引列运算（如，+、-、*、/），索引失效。

7. 索引字段上使⽤（!= 或者 < >，not in）时，会导致索引失效。

8. 索引字段上使⽤ is null， is not null，可能导致索引失效。

9. 相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循

10. mysql 估计使⽤全表扫描要⽐使⽤索引快，则不使⽤索引。

30.什么是覆盖索引？

覆盖索引是⼀种避免回表查询的优化策略: 只需要在⼀棵索引树上就能获取SQL所需的所有列数据，⽆需回表，速度更快。

具体的实现⽅式:

将被查询的字段建⽴普通索引或者联合索引，这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位⾏记录，避免了回表的情况发⽣。

添加图片注释，不超过 140 字（可选）

添加图片注释，不超过 140 字（可选）

覆盖索引的定义与注意事项:

如果⼀个索引包含了 所有需要查询的字段的值 (不需要回表)，这个索引EXPLAIN SELECT user_name,user_age,user_level FROM usersWHERE user_name = 'tom' AND user_age = 17;就是覆盖索引。

MySQL只能使⽤B+Tree索引做覆盖索引 (因为只有B+树能存储索引列值)

在explain的Extra列, 如果出现 ** Using index 表示 使⽤到了覆盖索引 , 所取的数据完全在索引中就能拿到

篇幅有限，资料已整理成文档，查看下方名片获取！