Mysql 引擎之常见问题

一、引擎对比篇

1、有哪些常见的MySQL引擎？
InnoDB、MyISAM、NDB、Memory、Archive……
2、InnoDB引擎的特点？
支持行锁、支持外键、支持非锁定读，InnoDB引擎讲数据存放在一个逻辑的表空间，InnoDB引擎支持MVCC，使用next-key locking来避免幻读，提供insert buffer、double write、AHI、read ahead等高性能策略。
3、MyISAM引擎的特点？
不支持事务、表锁设计、支持全文索引，buffer pool只缓存索引文件，不缓存数据文件，MyISAM存储引擎表由MYD（存放数据文件）和MYI（存放索引文件）组成。
4、NDB引擎有哪些特点？
数据全部放在内存中，主键查找速度极快，连接操作在server层完成，对于复杂连接操作需要巨大的网络开销，因此查询速度慢。
5、Memory引擎有哪些特点？
数据全部放在内存中，适合于临时表和维度表，默认使用hash索引，支持表锁，不支持TEXT和BLOB类型，存储VARCHAR按照CHAR的方式进行，耗费内存。MySQL使用Memory作为临时表来存放中间结果集，如果大于Memory存储引擎表的容量，或者含有TEXT或BLOB，就会使用MyISAM把数据存放到磁盘中。

InnoDB引擎组成篇

1、特点？
支持行锁、支持外键、支持非锁定读，InnoDB引擎讲数据存放在一个逻辑的表空间，InnoDB引擎支持MVCC，使用next-key locking来避免幻读，提供insert buffer、double write、AHI、read ahead等高性能策略。
2、有哪些后台线程？
Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。
3、Master Thread的作用？
负责将缓冲池的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并insert buffer、undo页的回收
4、IO Thread的作用？
负责IO请求的回调（MySQL使用AIO来处理写IO请求）
5、IO Thread的分类？
write、read、insert buffer、log IO thread
6、Purge Thread的作用？
回收分配并使用的undo页。
7、Page Cleaner Thread的作用？
刷新脏页到磁盘，以减少Master Thread的工作及对用户查询线程的阻塞。
8、InnoDB内存划分？
大致分为：缓冲池（buffer pool）、LRU List、Free List、Flush List、重做日志缓冲。
9、什么是缓冲池？有什么作用？工作流程？什么是页fix?
简单来说缓冲池就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对于数据库性能的影响。
在数据库进行读取页时，首先将磁盘读到的页放在缓冲池中，这个过程称为页“FIX”在缓冲池中，下一次读取时首先判断页是否在内存中，如果在则称该页在缓冲池中被命中，直接读取。否则，读取磁盘中的页。
对于数据库中页的修改，则首先修改在缓冲池中的页，然后按checkpoint机制率刷新到磁盘上。
10、缓冲池的数据类型？
索引页、数据页、undo页、AHI、insert buffer、lock info、data dictionnary等。
11、可否有多个缓冲池实例？
InnoDB 1.0.x后可以。
12、什么是LRU List？
缓冲池通过LRU算法进行管理，最频繁使用的页在LRU列表的前端，最少使用的在LRU列表尾端。
13、MySQL对于LRU算法的优化？
MySQL在LRU列表中加入了midpoint位置，默认情况下是LRU列表的5/8处，这样做是因为有一些页仅仅只是读取一下，并不是热点数据，所以并不能直接放到首端。
14、什么是Free List？
对于刚启动的MySQL来说，LRU列表是空的，所有的页都存放于Free列表中，当需要从缓冲池中分页时，先查看是否有可用的页，若有则将此页从Free列表删除，放入LRU列表。否则，根据LRU算法，淘汰LRU列表尾端的。
15、什么是page made young？什么是page not made young
页从LRU列表的old部分到new部分，这个过程叫page made young，因为innodb_old_block_time的设置而导致没有从old部分移动到new部分，这个过程叫page not made young。
16、什么是页压缩？
把16KB的页压缩为1KB、2KB、4KB、8KB。
17、什么是unzip_LRU列表？如何从缓冲池分配内存？
管理页大小不为16KB的页的列表。
检查相应KB的unzip_LRU列表（如4KB），检查是否有可利用的空闲页。如果有，使用。否则检查8KB的unzip-LRU列表，若有，把8KB分为两个4KB。若无，请求LRU列表，得到16KB页，分为一个8KB和两个4KB。
18、什么是Flush 列表？与LRU列表有何区别
管理脏页刷新回磁盘。LRU列表管理缓冲池页中的可用性，而Flush列表管理将脏页刷回磁盘，不冲突。
19、什么是重做日志缓冲？
InnodDB首先将重做日志信息放到这个缓冲区，然后按一定频率刷新到重做日志文件。
20、什么情况下会刷新到磁盘？
Master Thread每一秒将重做日志缓冲刷新到重做日志文件。
每个事物提交时会将重做日志缓冲刷新到重做日志文件。
当重做日志缓冲池剩余空间小于1/2时。
21、什么是Write Ahead Log？
事物提交时，先写重做日志，再修改页。
22、什么是checkpoint？
是一个事件，当此事件发生时，会把脏页刷新到磁盘。
22、checkpoint有什么用？
缩短数据库恢复时间
缓冲池不够用时，刷新脏页到磁盘
重做日志不够用时，刷新脏页
23、为什么checkpoint可以缩短数据库恢复时间？
因为当数据库发生宕机时，数据库不需要重做所有的日志，因为checkpoint之前的页都刷新回了磁盘，所以数据库只需要对checkpoint之后的重做日志进行恢复。
23、什么是LSN？
用来标记版本号的。
24、checkpoint分类？有什么区别？
Sharp checkpoint
Fuzzy Checkpoint
Sharp checkpoint发生在数据库关闭时，把所有脏页都刷回到磁盘，若数据库在运行时也使用Sharp checkpoint就会对数据库可用性造成影响。所以InnoDB内部使用Fuzzy Checkpoint进行页的刷新（只刷一部分，而不是所有）。
25、redo log刷新策略？
定义：已经写入redo log的LSN记为redo_lsn，将刷回磁盘最新页的LSN记为checkpoint_lsn。
checkpoint_age = redo_lsn - checkpoint_lsn
anync_water_mark = 0.75 * total_redo_log_file_size
sync_water_mark = 0.9 * total_redo_log_file_size
当checkpoint_age < async_water_mark < sync_water_mark 时，不需要刷新
当async_water_mark < checkpoint_age < sync_water_mark除法Async Flush，从Flush列表中刷新足够的脏页使得满足checkpoint_age < async_water_mark
当checkpoint_age > sync_water_mark时，触发Sync Flush操作，从Flush列表中刷新足够的页回磁盘，使得刷新后满足，checkpoint_age < async_water_mark
26、Master Thread内部的循环组成？
主循环（loop）、后台循环（backgroup loop）、刷新循环（flush loop）、暂停循环（suspend loop）
27、每秒一次的操作包括哪些？
日志缓冲刷新到磁盘，即使这个事务还未提交（总是）
合并插入缓冲（可能）
至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）
如果当前没有用户活动，则切换到backgroud loop（可能）
28、哪种情况下会触发合并插入缓冲？
当前一秒内IO的次数小于5次
29、哪种情况下一秒会刷新100个脏页？
当缓冲池中脏页的比例超过了配置文件中innodb_max_dirty_pages_pct参数的值
30、每十秒的操作包括哪些？
刷新100个脏页到磁盘（可能）
合并至多5个插入缓冲（可能）
将日志缓冲刷新到磁盘（总是）
删除无用的undo页（总是）
刷新100个或者10个脏页（总是）
31、什么情况下十秒会刷新100个脏页？
过去10秒的io操作是否小于200次
32、什么是full pugre？
删除无用的undo页
33、每次full purge最多回收多少页？
20页
34、什么情况下会切换到background loop？
当前没用用户活动或者数据库关闭
35、background loop会执行哪些操作？
删除无用的undo页（总是）
合并20个插入缓冲（总是）
跳回到loop（总是）
不断刷新100个页直到符合条件（可能，跳转到flush loop完成）
36、什么情况下会切换到suspend loop？
什么事都做完了，会把master thread挂起
37、在InnoDB1.2.X版本之前的Master Thread对刷新到磁盘页的数量会按照什么控制？有什么规则？
在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值得5%
在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity
38、innodb有哪些关键特性？
插入缓冲，两次写，自适应哈希索引，异步IO，刷新邻近页
39、什么是insert buffer？
和数据页一样，也是物理页得一个组成部分。对于非聚焦索引的插入或更新操作，不是每一次直接插入到索引页，而是先判断插入的非聚焦索引页是否在缓冲池中，若在，则直接插入；不在，则先放到一个insert buffer对象中。
40、使用insert buffer的条件？
索引是辅助索引
索引不是唯一的
41、insert buffer有什么问题？
在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2
42、什么情况下可能合并insert buffer？
辅助索引页被读取到缓冲池时
insert buffer bitmap页追踪到该辅助索引页已经没有可用空间时
master thread
43、insert buffer和redolog有什么区别？
redo log减少随机写，insert buffer减少随机读
44、double write有什么用？
提高数据页的可靠性
45、什么是double write？
在应用重做日志前，需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，在进行重做。
46、double write的组成？
由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分时物理磁盘上共享表空间中连续的128个页，即2个区，大小也为2MB.
47、double write流程？
在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。这个过程中，因为doublewrite页是连续的，因此这个过程是顺序的，开销不大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写如各个表空间文件中，此时的写入是离散的。
48、为什么log write不需要double write？
因为redo log写入的单位就是512字节，也就是磁盘IO的最小单位，所以无所谓数据损坏。
49、为什么有了redo log还需要double write？
试想，在某个Dirty Page（一般是16K）flush的过程中，发生了系统断电（或者OS崩溃），16K的数据只有8K（操作系统页大小与MySQL不一样）被写到磁盘上，这种现象被称为（partial page writes、torn pages、fractured writes）。一旦partial page writes发生，那么在InnoDB恢复时就很尴尬：在InnoDB的Log file中虽然知道这个数据页被修改了，但是却无法知道这个页被修改到什么程度，和这个页面相关的redo也就无法应用了。
50、如何使用double write恢复页？
InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。
51、什么是自适应哈希索引？
如果监察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引。
52、建立AHI的要求？
对这个页的连续访问模式必须是一样的。
以该模式访问100次。
页通过该模式访问了N次，其中N=页中记录*1/16。
53、什么是AIO，与SyncIO有什么区别？
Sync IO每进行一次IO都需要等待此次操作结束。
AIO当用户发出请求后立即在发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成。
AIO还有一个优势，就是可以IO Merge。
54、什么是刷新邻近页？
当刷新一个脏页时，InnoDB引擎会检测该页所在区的所有页，如果是脏页，那么一起刷新。
55、刷新邻近页有什么好处？
通过AIO可以将多个IO合并为一个IO操作。
56、**刷新邻近页有什么问题？
可能将不怎么脏的页进行了写入，很快这个页又变成了脏页。
固态硬盘有较高的IOPS，可能不需要这个特性。

日志文件篇

57、MySQL有哪些常见日志？
error log、binlog、 slow query log、log、redo log、undo log
58、错误日志记载了哪些东西？
MySQL的启动、运行、关闭过程。
59、慢查询日志有什么用？
定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。
60、什么是查询日志？
查询日志记录了所有对MySQL数据库请求的信息，无论这些信息是否得到了正确的执行。
61、什么是bin log？
bin log记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作。
62、若操作没有使数据库发生改变，这类操作是否可能被记录进入bin log？
可能，如update语句命中0行。
63、bin log有什么用？
恢复、复制、审计
64、sync_binlog参数有什么作用？
指定每多少次提交事务，就刷新到磁盘，默认为0；为0的情况下，只会把缓冲刷新到文件缓存，什么时候到磁盘，由OS决定。
65、binlog_format参数有什么用？
指定bin log格式
66、bin log有几种格式？
STATEMENT、ROW、MIXED.
67、bin log的格式都有什么区别？
STATEMENT格式和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。
ROW记录的是表行更改情况。此时可以把事务隔离级别记为READ COMMITTED。
MIXED格式下，MySQL默认使用STATEMENT，有时也会使用ROW。
68、在MIXED模式下，什么情况会使用ROW？
表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录。
使用了UUID()，USER()，CURRENT_USER()，FOUND_ROWS()，ROW_COUNT()等不确定函数。
使用了INSERT DELAY语句。
使用了用户定义函数。
使用了临时表。
69、在NDB引擎下是否可以让bin log格式为STATEMENT?
不可。
70、在Blockhole引擎下是否可让bin log格式为ROW？
不可。
71、如何查看bin log的内容？
使用MySQL提供的工具mysqlbinlog。
72、**什么是表结构定义文件？**有什么用？
MySQL不论采用哪种引擎都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。
76、什么是表空间文件？
InnboDB采用将存储的数据按表空间进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。
77、参数innodb_file_per_table有什么用？
设置此参数后，可以将每个基于InnoDB引擎的表产生一个独立的表空间。
78、独立的表空间存放哪些数据？
仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中。
79、redo log大小有何限制？
redo log大小不能太大，设置太大恢复需要很长时间；不能设置太小，设置太小会导致一个事务的日志需要多次切换重做日志文件。此外，重做日志文件太小会导致频繁地发生async checkpoint，导致性能的抖动。
80、redo log和bin log有什么区别？
redo log是InnoDB引擎专有的日志，bin log是server层的日志，所有引擎都可以使用。
记录内容不同，redo log记录的是每个页的更改的物理情况，bin log无论是哪种格式，记录的都是一个事务的具体操作。
bin log仅仅会在commit前提交，只写盘一次，无论此时事务多大；而redo log会在事务进行的时候被写入到日志文件中。
81、什么是两阶段提交？
引擎将新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。
然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
82、为什么要有两阶段提交？
· 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
` 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
83、为什么两阶段提交先写redo log再写bin log？
由于bin log会传到从库去进行备份，因此，假设先写bin log的情况下，传到了从库，从库执行了bin log，但是主库发生了宕机，redo log还没有写，所以造成了主从不一致。
84、可不可以只要bin log不要redo log？
不行。例如，bin log并不支持恢复数据页。

假设在图中crash处数据库发生crash，由于binlog2没有提交，所以可以恢复，但是由于binlog1已经提交，所以不会被恢复回来（他们属于同一数据页，且此时数据还存放于change buffer中）。

85、可不可以只要redo log不要bin log？
如果只需要崩溃恢复这个功能的话，是可以的。但是，redo log是循环写，历史日志没办法保留。
redo log是InnoDB引擎专属，如果使用其他引擎，就很尴尬。
从库依靠bin log同步主库的数据。
86、正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

索引篇

87、什么是索引？
相当于一个目录。
88、InnoDB支持哪些索引？
B+树索引、全文索引、哈希索引。
89、为什么使用B+树而不是用BST、AVL以及红黑树？
BST可能长歪而变得不平衡，如下图所示，此时BST退化为链表，时间复杂度退化为O(n)》

AVL存在的问题是，旋转操作维持平衡这个动作过于耗时。
红黑树存在的问题是，太高了会消耗大量IO。
B+树，是B树的进化版本：
B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键。在MySQL中，这里所说的真实数据，可能是行的全部数据（如Innodb的聚簇索引），也可能只是行的主键（如Innodb的辅助索引），或者是行所在的地址（如MyIsam的非聚簇索引）。
B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现——一定会在叶节点出现，也可能在非叶节点重复出现。
B+树的叶节点之间通过双向链表链接。
B树中的非叶节点，记录数比子节点个数少1；而B+树中记录数与子节点个数相同。
由此，B+树与B树相比，有以下优势：
· 更少的IO次数：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
· 更适于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
· 更稳定的查询效率：B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。
· B+树也存在劣势：由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛。
90、B树的定义？
对于一颗m阶B树，需要满足以下条件
· 每个节点最多包含 m 个子节点。
· 如果根节点包含子节点，则至少包含 2 个子节点；除根节点外，每个非叶节点至少包含 ceil(m/2) 个子节点。
· 拥有 k 个子节点的非叶节点将包含 k - 1 条记录。
· 所有叶节点都在同一层中。
可以看出，B树的定义，主要是对非叶结点的子节点数量和记录数量的限制。
91、什么是聚集索引？
聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引这个特定决定了索引组织表中数据也是索引的一部分。
92、聚集索引的好处？
聚集索引能在B+树索引的叶子节点上直接找到数据。
对于主键的排序查找和范围查找速度非常快。
93、聚集索引的存储在物理上是连续的吗？为什么？
不是，逻辑上是。
页通过双向链表连接，按照主键的顺序排序；每个页中的记录也是通过双向链表进行维护的。
94、什么是辅助索引？
叶子节点不包含行记录的全部数据，只包含键值以及书签，书签用来告诉InnoDB引擎哪里可以找到与索引相对应的行数据，在InnoDB中书签就是主键。
95、InnoDB是如何决定向右分裂还是向左分裂的？
在Page Header中存在以下几个部分：
PAGE_LAST_INSERT
PAGE_DIRECTION
PAGE_N_DIRECTION
通过这些信息来的。
如果插入是随机的，则取页的中间记录作为分裂点的记录。
若往同一方向进行插入的记录数量为5， 并且目前已经定位到的记录之后还有三条记录，则分裂点的记录为定位到的记录后的第三条记录，否则分裂点记录就是待插入的记录。
96、什么是FIC？
对于辅助索引的创建，InnoDB存储引擎会对创建索引的表上加一个S锁。在创建的过程中，不需要重新建表。
97、FIC有什么问题？
对于辅助索引的添加为只读，不能修改。
对于主键索引还是要进行重进建表，导入数据的操作。
98、什么是OSC？流程？
在线架构改变。
init：初始化阶段，对表做一些验证工作，如检查表是否有主键或外键。
createCopyTable：创建和原表结构一样的新表。
alterCopyTable：对创建的新表进行ALTER TABLE操作，如添加索引。
createDeltasTable：创建deltas表，作用是为下一步创建的触发器使用，对原表的DML操作会被记录到createDeltasTable中。
createTriggers：对原表创建INSERT、UPDATE、DELETE操作的触发器。触发操作产生的记录被写入到deltas表。
startSnpshotXact：开始OSC操作的事务。
selectTableIntoOutfile：将原表数据写入新表。分片操作。
dropNCIndexs：在导入到新表前，删除新表中的所有辅助索引。
loadCopyTable：将导出的分片文件导入到新表。
replayChanges：将OSC过程中原表DML操作的记录应用到新表，这些记录被保存到deltas中。
recreateNCIndexs：重新创建附注索引。
replayChanges：再次进行DML日志的回放操作，这些日志是在上述创建辅助索引过程中新产生的日志。
swapTables：将原表和新表交换名字。
99、OnlineDDL是什么？
允许辅助索引创建的同时，允许INSERT、UPDATE、DELETE等DML操作。
100、除了辅助索引的创建OnlineDDL还可以用在哪里？
该表自增长值
添加或删除外键
列重命名
101、OnlineDDL有哪两种算法？
copy和inplace。
102、OnlineDDL原理？
在执行创建或是删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作写入到一个缓冲。待完成索引创建后再将重做应用到列表上。
103、copy和inplace有什么区别？
对于copy来说，会把原表数据放到tmp_table这个在server层创建的临时表。
对于inplace来说，整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。
104、copy和inplace有什么联系？
DDL 过程如果是 Online 的，就一定是 inplace 的；反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。
105、什么是Cardinality值？
表示索引中不重复记录数量的预估值。并非是准确值。
106、对Cardinality的统计是放在哪里的？
引擎层。
107、Cardinality更新策略？
表中1/16的数据发生改变。
stat_modified_counter > 2 000 000 000。
108、Cardinality统计算法？
InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
109、哪些SQL语句会导致InnoDB重新统计Cardinality？
ANALYZE TABLE
SHOW TABLE STATUS
SHOW INDEX
访问INFOMATION_SCHEMA架构下的表TABLES和STATISTICS
OPTIMIZE TABLE
110、什么是覆盖索引？
从辅助索引中获得查询的记录，而不需要在进行回表。
111、哪些情况可能不走索引？
范围查找，join连接操作
112、什么是Multi-Range Read优化
MMR优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的sql查询语句可带来性能极大的提示。
113、MMR优化有什么好处？
MMR使数据变得较为顺序，在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
减少缓冲池中页被替换的次数。
批量处理对键值的查询操作。
114、对范围查询和join查询，MRR的工作方式？
将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序。
根据RowID的排序顺序来访问实际的数据文件。
具体来说

这张图是按照 Myisam 的索引结构画的，不过对于 Innodb 也同样适用。
对于 Myisam，左边就是字段 age 的二级索引，右边是存储完整行数据的地方。
先到左边的二级索引找，找到第一条符合条件的记录（实际上每个节点是一个页，一个页可以有很多条记录，这里我们假设每个页只有一条），接着到右边去读取这条数据的完整记录。
读取完后，回到左边，继续找下一条符合条件的记录，找到后，再到右边读取，这时发现这条数据跟上一条数据，在物理存储位置上，离的贼远！

对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。
对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。
顺序读带来了几个好处：
1、磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读
比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用。
3、在一次查询中，每一页的数据只会从磁盘读取一次
MySQL 从磁盘读取页的数据后，会把数据放到数据缓冲池，下次如果还用到这个页，就不需要去磁盘读取，直接从内存读。
但是如果不排序，可能你在读取了第 1 页的数据后，会去读取第2、3、4页数据，接着你又要去读取第 1 页的数据，这时你发现第 1 页的数据，已经从缓存中被剔除了，于是又得再去磁盘读取第 1 页的数据。
而转化为顺序读后，你会连续的使用第 1 页的数据，这时候按照 MySQL 的缓存剔除机制，这一页的缓存是不会失效的，直到你利用完这一页的数据，由于是顺序读，在这次查询的余下过程中，你确信不会再用到这一页的数据，可以和这一页数据说告辞了。
顺序读就是通过这三个方面，最大的优化了索引的读取。
别忘了，索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。
115、对于范围查询，MMR优化的流程？
优化器先将查询条件进行拆分，然后再进行数据查找。
例如：select * from t where key1<1000 and key2 = 2000;
他会把他变为(1,2000)，(2,2000)……这样的键值对。
116、什么是ICP优化
当进行索引查询时，根据索引来查找记录，同时判断是否可以根据WHERE条件来过滤，也就是将where的部分放在了引擎层。（索引下推）。
117、什么是锁？与事务有何区别
所谓事务是用户定义的一个数据库操作系列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位，是数据库执行的基本单位，具有ACID的特性。
锁是一种机制，是用来解决由于并发带来的一系列问题的手段。
118、锁一般分为哪两种？
悲观锁和乐观锁。
119、InnoDB中，实现了哪两种行级锁？
共享锁
排它锁
120、什么是意向锁？有什么用？
意向锁是对于数据的上层进行上锁。
例如：对一颗B+树的叶子节点上X锁，那么必须对这张表、数据库、页上IX锁。
121、意向锁有什么好处？
当事务A对一行上锁时，事务B如果也需要获得这把锁，那么不必对表进行扫描判断，而是进入数据库时就进行阻塞。
122、什么是一致性非锁定读？
InnoDB通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此等待行上锁的释放，而是读取一个快照数据。
123、行锁有哪三种算法？
Record Lock：单个行记录上的锁。
Gap Lock：间隙锁。
Next-Key Lock：上面两种的组合。
124、InnoDB加锁的原则？
原则 1：加锁的基本单位是 next-key lock。（next-key lock 是前开后闭区间）。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
125、如何显式关闭Gap Lock
将事物的隔离级别设为READ COMMITTED
将参数innodb_locks_unsafe_for_binlog设为1
126、InnoDB如何解决幻读？
引入Next-Key Locking机制。
127、什么是Phantom Problem
在同一事物下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行。
128、什么是脏读？
一个事务读到另一个事务未提交的数据。
129、什么是不可重复读？
一个事务两次条件相同，但由于其他事物的修改，导致了读到的数据不一致。
130、什么是丢失更新
事务T1修改某一行数据，但未提交
事务T2修改同一行，未提交
事务T1提交
事务T2提交
事务T1的修改被T2覆盖了
131、如何控制阻塞时间？
innodb_lock_wait_timeout参数来控制
132、如何控制是否在等待超时时回滚事务
innodb_rollback_on_timeout参数（静态参数）
133、什么是死锁？
两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
134、死锁出现的条件？
互斥，持有并等待，循环等待，非抢占
135、死锁有哪些避免方法？
死锁预防，死锁避免，死锁检测和恢复，由应用进程处理死锁。
136、什么是wait-for graph？
一种死锁检测算法。
137wait-for graph算法有什么要求？
需要保存两种信息：锁的信息列表，事务等待列表
138、wait-for graph流程？
在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说innodb选择回滚量最小的事务。
139、死锁的概率与哪些因素有关？
系统中事物的数量（n），数量越多越容易死锁。
每个事务操作的数量（r），每个事物操作的数量越多，发生死锁概率越大。
操作数据的集合（R），集合越小，越容易死锁。
140、哪种异常一定会被回滚？
死锁。
141、锁升级是什么？
将众多细粒度锁转换为较少的粗粒度的锁的过程，以削减系统开销。
142、什么是事务？
是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）。
143、事务具有哪些特性？
ACID
144、有哪几种事务？
扁平事务
带有保存点的扁平事务
链事务
嵌套事务
分布式事务
145、链事务与带有保存点的扁平事务有什么区别？
带有保存点的扁平事务能回滚到任务正确的保存点；而链事务中的回滚仅限于当前事务，即只能回滚到最近的一个保存点。
链事务在执行COMMIT后会释放当前事务所持有的锁；带有保存点的扁平事务不影响迄今为止所持有的锁。
146、undo log有什么用？
实现MVCC。
147、innodb_flush_log_at_trx_commit参数有什么用？
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
148、bin log是什么时候记录的?
事务commit时。
149、什么是log block？
在InnoDB中，redo log都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块（block）的方式保存的，这个就叫做redo log block。
150、每个重做日志块实际存储大小为多少？
492字节。（日志块头12字节，日志尾8字节）
151、如何判断log block是否包含新的日志
查看LOG_BLOCK_FIRST_REC_GROUP和LOG_BLOCK_HDR_DATA_LEN的值，如果两个值一样，说明没有新的日志，反之则有。
152、什么是log group
多个重做日志文件组成。
153、redo log file的写入一定是顺序的吗？
不一定，每个redo log file的前2KB部分不保存log block中，保存其他信息，这些信息不一定是顺序的写入。
154、redo log的格式？
redo_log_type：重做日志的类型
space：表空间的ID
page_no：页的偏移量
155、LSN除了标记版本号外表示的含义有哪些？
重做日志写入的总量
checkpoint的位置
156、undo log存放在哪里？
数据库内部的一个特殊段中，这个段称为undo段，undo段位于共享表空间。
157、undo log是否会产生redo log？
会，就算是进行undo操作，也会有redo log的产生。
158、undo存储管理？
InnoDB存储引擎偶rollback segment，每个回滚段种记录了1024个undo log segement，而在每个undo log segment段中进行undo页的申请。共享表空间偏移量为5的页（0，5）记录了所有rollback segment header所在的页，这个页的类型为FIL_PAGE_TYPE_SYS。
159、undo log在事务提交时，会干嘛？
将undo log放入列表中，以供以后的purge操作。
判断undo log所在的页是否可以重用，若可以分配给下个事务使用。
160、事务提交后是否一定会删除undo log？
不一定，必须要等用到此undo log的事务提交后才能删除。在事务提交时仅仅将undo log放入一个链表中，是否可以最终删除，由purge判断。
161、哪种情况下，undo log才可以重用？
当事务提价时，会把undo log放入链表，然后判断undo页的使用空间是否小于3/4，若小于则可以被重用，之后新的undo log记录就在当前undo log的后面。
162、purge操作为什么是一个缓慢的过程？
因为一个undo页可能存放多个事物的undo log，这个过程需要涉及到磁盘的离散读取，所以慢。
163、undo log的格式？
insert undo log
update undo log
164、为什么undo log要分成这两种格式？
因为由于隔离性的原因，在insert事务提交后，undo log可以立即删除；由于MVCC的原因，update undo log不能立即删除。
165、如何查看undo log？
在information_schema下的INNODB_TRX_ROLLBACK_SEGMENT（查看rollback segment）和INNODB_TRX_UNDO（查看事务对应的undo log）
166、delete操作后，什么时候才会被真正删除？
若该行记录不被其他任何事务引用，就可以真正的删除。
167、update undo log的分类？
TRX_UNDO_UPD_EXIST_REC更新non-delete-mark的记录
TRX_UNDO_UPD_DEL_REC将delete的记录标记为not delete
TRX_UNDO_DEL_MARK_REC将记录标记为delete
168、更改主键的具体操作？
将原主键记录标记为删除（产生一个TRX_UNDO_DEL_MARK_REC的undo），之后插入一条新记录（产生一个TRX_UNDO_INSERT_REC的undo）
169、什么是history list？
按照事务提交的顺序将undo log进行组织的一种数据结构。先提交的总在尾端。
170、为什么引入从history list找undo log，然后从undo page中找undo log的设计模式？
避免大量随机读取操作。
171、如何控制history的大小？
通过参数innodb_max_purge_lag
172、如果history list大小大于innodb_max_purge_lag的值，会发生什么？
延缓DML操作。
173、为什么两阶段提交后group commit会失效？
因为引入了prepare_commit_mutex这个锁，导致一个事物在进行redo log在进行fsync时，另一个事务不能把日志写入log buffer。
174、BLGC的步骤？
Flush，将每个事务的bin log写入内存。
Sync，将内存中的bin log刷新到磁盘，若队列中有多个事务，那么一次fsync就完成了二进制日志的写入。
Commit，leader根据顺序调用存储引擎层事物的提交。
175、什么是分布式事务？
允许多个独立的事务资源参与到一个全局的事务中。
176、XA事务的组成？
资源管理器
事务管理器
应用程序
177、分布式事物的提交方式？
使用两阶段提交。
第一段，所有参与全局事物的节点都开始准备，告诉事务管理器准备好了。
第二段，事务管理器告诉资源管理器执行rollback还是commit。如果任何一个不能提交，则所有节点都会被回滚。

备份复制篇

178、根据类型来划分备份方法的情况下，有哪些备份方法？
Hot Backup（热备，读写操作均可执行）
Cold Backup（冷备，读写操作均不可进行）
Warm Backup（温备，读操作可执行；但写操作不可执行）
179、按照备份文件进行划分，备份又可如何划分？
逻辑备份（备份出的文件是可读的，一般是文本文件。内容一般是SQL或表内实际数据组成）
裸文件备份（备份物理文件）
180、按照内容备份，备份又可以怎么划分？
完全备份（对数据库进行一个完整的备份）
增量备份（是指在上次完全备份的基础上，对于更改的数据进行备份）
日志备份（对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做，来完成数据库的point-in-time的恢复工作）
181、冷备具体备份什么？
MySQL数据库的.frm文件，共享表空间文件，独立表空间文件（.ibd），重做日志文件。
182、冷备的优点和缺点？
优点：
备份简单，只要复制相关文件即可。
备份文件易于在不同操作系统，不同MySQL版本上进行恢复。
恢复相当简单，只需要把文件恢复到指定位置即可。
恢复速度快，不需要执行任何SQL语句，也不需要重建索引。
缺点：
InnoDB引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据，如undo段，插入缓冲等信息。
冷备也不是可以轻易地跨平台，操作系统，MySQL的版本、文件大小写敏感和浮点数格式都是问题。
183、什么是mysqldump？
一个完成转存数据库的备份及不同数据库之间的移植的工具。
184、有哪两种逻辑备份方法？
使用mysqldump，使用select …… into outfile语句。
185、什么是ibbackup？
InnoDB引擎官方提供的热备工具，可以同时备份MyISAM和InnoDB。
186、ibbackup原理？
记录备份开始时，InnoDB存储引擎重做日志文件检查点的LSN。
复制共享表空间文件以及独立表空间文件。
记录复制完表空间文件后，InnoDB引擎重做日志文件检查点的LSN。
复制在备份时产生的重做日志。
187、ibbackup有什么优点？
在线备份，不阻塞任何的SQL语句
备份性能好，备份的实质是复制数据库文件和重做日志文件。
支持压缩备份，通过选项，可以支持不同级别的压缩。
跨平台支持。
188、ibbackup对InnoDB引擎表的恢复步骤为？
恢复表空间文件
应用重做日志
189、复制的工作原理？
主服务器把数据更改记录到binlog中。
从服务器把主服务器的binlog复制到自己的中继日志中。
从服务器重做中继日志中的日志，把更改应用到自己的数据库上。
190、复制是完全实时吗？
不是，是异步实时，因为存在执行延时
191、复制除了可以用作备份，还有什么用？
数据分布
读取的负载平衡
数据库备份
高可用和故障转移