其他 MySQL 相关技术沉淀文章

• MySQL Insert 死锁问题研究
• MySQL 自增列 Duplicate Error 问题分析
• MySQL DateTime和Timestamp时区问题

一、基础

1.1 常用索引有哪些

普通索引、唯一索引、主键索引、组合索引、全文索引

1.2 聚集索引和非聚集索引区别

聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）是两种不同类型的数据库索引，它们的区别如下：

1. 聚集索引（Clustered Index）：

   • 聚集索引中的数据行和索引的顺序相同，即它将数据行本身和索引按照相同的排序方式存储在一起。
   • 一个表只能有一个聚集索引，因为数据行只能以一种顺序存储。
   • 聚集索引通常用主键建立，但也可以使用其他具有唯一约束的列。
   • 数据的物理存储顺序与索引的顺序相同，所以聚集索引通常提供较快的数据检索。

2. 非聚集索引（Non-clustered Index）：

   • 非聚集索引与聚集索引分开存储，数据行的顺序与索引的顺序无关。
   • 一个表可以有多个非聚集索引；
   • 非聚集索引包含用于检索的键值和一个指针，指向行数据的实际位置。
   • 非聚集索引通常用于在不同于主键的其他列上提高查询性能。

总结：聚集索引按照索引顺序存储数据行，而非聚集索引将索引与数据行分开存储。一个表只能有一个聚集索引，但可以有多个非聚集索引。在查询性能上，聚集索引通常提供较快的数据检索，而非聚集索引则在其他列上提高查询性能。

1.3 Mysql 为什么使用 B+树（B+Tree）作为存储的数据结构？

MySQL 使用 B+树（B+Tree）作为存储数据结构的主要原因是其在查询性能、空间利用和磁盘IO效率方面的优势。以下是 B+树在数据库中具有优势的几个方面：

1. 查询性能：B+树是一种自平衡的多路搜索树，这意味着它能够在树的深度方面保持相对均衡。这样，在查找数据时，即使数据量非常大，也能保证较低的查询成本。

2. 范围查询：B+树的叶子节点上的记录（或键值）是有序的，这使得它非常适合范围查询。当需要对数据库进行范围查询时，B+树可以从范围起始点开始进行快速检索，并顺序访问叶子节点直到范围结束，性能非常高。

3. 磁盘 IO 效率：数据库系统往往需要将数据持久化到磁盘以保证数据的完整性和安全。B+树的结构使其能够很好地处理磁盘IO。在B+树中，所有的数据记录都存储在叶子节点上，非叶子节点只包含索引信息。这样，在从磁盘读取时IO次数会相对较少，从而提高查询性能。

4. 空间利用：B+树相较于其他树结构，如红黑树或AVL树，更具有空间利用性。因为在B+树内部节点只存储索引信息而不存储实际数据，所以每个内部节点可容纳的索引量更多，树的高度将更低。这就意味着磁盘空间被更有效地利用，查询更快。

1.4 Mysql 的 B+Tree 非叶子节点有多少数据，一般有几层。

InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用 4 个字节）或 BIGINT（占用 8 个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的 K 取值为〖10〗^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护 10^3 _ 10^3 _ 10^3 = 10 亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

1.5 MyISAM、InnoDB 索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

1.6 MyISAM与InnoDB的主要区别？

MyISAM和InnoDB是MySQL数据库中两种常见的存储引擎，它们之间存在着一些主要区别：

1. 事务支持 (Transaction Support)

• MyISAM：MyISAM 存储引擎不支持事务。因此，它不适用于需要处理复杂事务和严格保证数据完整性的场景。

• InnoDB：InnoDB 存储引擎支持事务（ACID 属性），提供了提交、回滚等事务相关的操作，适用于处理复杂业务并确保数据完整性。

2. 行级锁定 (Row-Level Locking)

• MyISAM：MyISAM使用表级锁定，当对表进行写操作（如插入、更新或删除）时，会锁定整个表。这会降低高并发环境下的数据处理能力。

• InnoDB：InnoDB 使用行级锁定，这允许在高并发环境下同时对多个记录进行读写访问，提高了数据库性能。

3. 数据存储和索引

• MyISAM：MyISAM 存储引擎将数据文件和索引文件分开存储。它主要支持非聚集索引。

• InnoDB：InnoDB 以聚集的方式组织存储，它支持聚集索引和非聚集索引。数据和索引按照相同顺序存储，提高了查询性能。

4. 数据完整性 (Data Integrity)

• MyISAM：MyISAM 存储引擎不支持外键约束，无法确保数据间的引用完整性。

• InnoDB：InnoDB 支持外键约束，可以在数据表之间维护引用关系，确保数据的完整性。

5. 崩溃恢复 (Crash Recovery)

• MyISAM：MyISAM 不具备原生的崩溃恢复能力。在数据库意外崩溃或电源故障等情况下，可能造成数据损坏或丢失。

• InnoDB：InnoDB 提供了日志和检查点（Checkpoint）机制，支持故障现场恢复。因此，在数据库崩溃的情况下，InnoDB 更有可能保护数据并重新启动数据库。

6. 全文搜索 (Full-text Search)

• MyISAM：MyISAM 存储引擎提供原生的全文搜索功能。

• InnoDB：早期版本的 InnoDB 不支持全文索引搜索，但从 MySQL 5.6.4 开始，InnoDB 同样支持全文搜索功能。

总结：MyISAM更适合用于读取速度要求高、不要求事务支持和数据完整性严格保障的简单查询场景。InnoDB 更适合事务处理、并发读写较高且需要保证数据完整性的场景。在实际应用中，根据不同的需求和场景选择合适的存储引擎非常重要。

1.7 字段建索引好处？是不是越多越好？

为MySQL字段建索引可以带来一些优势，但并不是索引越多越好。以下是建立索引的好处和需要考虑的因素：

建立索引的好处：

1. 提高查询速度：索引可以显著提高数据查询的速度。通过使用索引，数据库可以避免全表扫描，而是直接定位到符合条件的记录，从而节省查询时间。

2. 加速排序和分组：索引可以帮助数据库引擎对排序和分组操作进行优化，提高查询性能。

3. 支持外键约束：在 InnoDB 存储引擎中，索引用于支持外键约束，确保数据引用的完整性。

然而，在创建索引时，需要权衡一些因素，过多的索引并不一定总是好的。

需要考虑的因素：

1. 存储空间：索引需要额外的存储空间。过多的索引会占用更多的磁盘空间。

2. 数据插入、更新、删除性能：每增加一个索引，数据库在插入、更新或删除数据时，都需要维护相关的索引。过多的索引会导致这些操作的性能降低。

3. 索引维护成本：索引需要维护，包括碎片整理、索引重建等操作。过多的索引意味着更高的维护成本。

总之，创建索引可以提高数据库查询性能，但是过多的索引会导致额外的存储空间和性能开销。在创建索引时，应根据实际需求进行适当的权衡。

1.8 MySQL 慢查询常见原因

MySQL慢查询可能出现的原因有很多。以下是一些常见的慢查询原因：

1. 缺少索引或使用不当：未对查询中的关键字段创建索引、索引选取不当或统计信息不准确，导致 MySQL 无法高效地查询数据，从而降低查询速度。

2. 查询语句设计不合理：复杂的子查询、使用了大量的临时表、笛卡尔积查询等，可能导致查询性能低下。

3. 数据量过大：当表中的数据量过大时，未经优化的查询可能需要处理大量数据，从而降低查询速度。

4. 锁争用：在高并发读写的场景下，如果 MySQL 存储引擎（如 MyISAM）使用表级锁或行级锁锁定不当，可能导致锁争用，进而降低查询性能。

5. 硬件资源限制：如 CPU、内存、磁盘和网络等硬件资源不足，可能导致查询性能低下。

6. MySQL 配置不当：MySQL 的配置参数未经优化，可能在一定程度上影响查询性能（如缓冲区大小不合适、连接数设置不正确等）。

7. 数据分布不均：在使用分布式数据库或分区表时，如果数据分布不均匀，可能导致部分查询性能较低。

8. 过时的统计信息：MySQL 使用统计信息来选择最佳索引。如果统计信息过时或不准确，可能导致 MySQL 选择不佳的查询计划。

针对这些常见的慢查询原因，可以采取相应措施进行优化。例如：创建和优化索引、改进查询语句、调整硬件配置、优化 MySQL参数设置等。同时，可以开启慢查询日志来分析和诊断慢查询问题。

1.9 SQL 优化原则，索引注意事项？

以下是一些建议的 SQL 优化原则和索引注意事项：

SQL 优化原则:

1. 减少数据请求量：尽量减少请求的数据量，只请求需要的列和必要的记录。

2. 避免 SELECT * 查询：仅查询所需的列，以减少数据传输和处理的开销。

3. 优化连接和子查询：连接查询时，避免全外连接（Full Outer Join）和笛卡尔积连接，而使用内连接（Inner Join）或左（右）连接（Left/Right Join）。尽量减少子查询，考虑使用连接查询或者临时表代替。

4. 分批处理数据：如果需要处理大量数据，可以使用分页查询、limit 语句或其他批处理技术，以降低数据处理压力。

5. 利用索引：在查询语句中使用索引相关的列，尽量避免对索引字段进行计算、函数或表达式操作，以充分利用现有索引加速查询。

6. 避免隐式类型转换：确保在查询中进行比较或计算的字段类型匹配，以避免隐式类型转换导致查询性能下降。

7. 聚合操作优化：在对大量数据执行聚合查询（如求和、计数等）时，尽量使用索引进行覆盖查询，减少数据扫描量。

索引注意事项:

1. 选择合适的索引列：在查询中常用作过滤、连接和排序的列是创建索引的最佳候选。另外，考虑数据的区分度，数据分布较均匀且重复值较少的列更适合创建索引。

2. 避免过多索引：索引虽然可以提高查询性能，但它会占用额外的存储空间并影响数据的插入、更新和删除操作。因此，应权衡实际情况，充分考虑查询需求和数据变更频率，以避免过多索引。

3. 使用组合索引：在多个字段上经常一起进行查询的场景，可以创建组合索引。但应注意，组合索引的顺序会影响到查询性能。

4. 避免在索引列上进行计算、函数或表达式操作：在 SQL 查询中，尽量不要对索引列进行计算、使用函数或进行表达式操作，这会导致索引失效。

5. 定期评估索引效果：定期评估现有索引的使用情况、查询性能和索引开销等方面，根据实际需求添加、删除或调整索引。

6. 考虑使用部分索引：如果某个列上只有部分数据需要被查询，可以考虑创建部分索引，以降低索引维护成本。

结合实际数据库使用情况调整和优化 SQL 查询语句以及索引策略，有助于提升数据库查询性能和整体性能。所采取的优化措施应根据业务需求、数据量级和具体场景进行调整。

1.10 事务四大特征(ACID)

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

1.11 事务有哪四种隔离级别

读数据一致性及并发副作用	读数据一致性	脏读	不可重复读	幻读
未提交读(read uncommitted)	最低级别，不读物理上顺坏的数据	是	是	是
已提交读(read committed)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

1.12 什么是当前读和快照读？对应SQL怎么实现？

当前读：像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读：像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本


1.13 什么是MVCC（Multi-Version Concurrency Control，即多版本并发控制）？

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种允许多个事务同时访问数据库的技术，旨在提高数据库系统在高并发环境下的性能。MVCC的核心思想是在同一个事务中对同一个数据的多次查询始终保持一致性，而不加锁来实现隔离，从而避免了加锁所带来的性能开销。

MVCC通过为每一行数据生成多个版本，在不同事务中访问不同版本的数据，实现并发控制。每个事务在开始时都会获得一个唯一的事务ID，读取到的数据行版本要满足以下条件：

• 数据行的创建版本要小于等于事务ID。
• 数据行的删除版本要大于等于事务ID，或者未被删除。

这样，每个事务将能以一致的快照（Snapshot）视图访问数据库，并且互不影响。

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

• DB_TRX_ID，6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
• DB_ROLL_PTR，7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
• DB_ROW_ID，6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
• 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了。


二、MySQL 日志基础

2.1 什么是 binlog

• binlog是逻辑日志，记录的是这个语句的原始逻辑/变化，比如给 ID=2 这一行的 c 字段加 1。
• binlog是追加写，不会覆盖之前的数据，可以提供完整的数据归档的能力。

2.2 什么是 redo log

• redo log是物理日志，记录的是在某个数据页上做了什么修改；
• redo log提供crash-safe能力。
• 一般只有4G ，4个文件，循环复写。

2.3 binlog 和 redo log 区别

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力。

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给 ID=2 这一行的 c 字段加 1。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

2.4 binlog 的写入机制

其实，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把 binlog cache写到binlog文件中。

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。

2.5 redo 的写入机制

事务在执行过程中，生成的redo log是要先写到redo log buffer的。

redo log buffer 里面的内容，是不是每次生成后都要直接持久化到磁盘呢？答案是，不需要。

如果事务执行期间MySQL发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。
那么，另外一个问题是，事务还没提交的时候，redo log buffer中的部分日志有没有可能被持久化到磁盘呢？答案是，确实会有。

这个问题，要从 redo log 可能存在的三种状态说起。这三种状态，对应的就是下图中的三个颜色块。

1. 存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；
2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分；
3. 持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。

日志写到redo log buffer是很快的，wirte到page cache也差不多，但是持久化到磁盘的速度就慢多了。

为了控制redo log的写入策略，InnoDB 提供了innodb_flush_log_at_trx_commit参数，它有三种可能取值：

• 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中 ;
• 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
• 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

2.6 redo log 存储方式，一个没有提交的事务的 redo log 是否有可能写入到磁盘中？

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和checkpoint之间的是还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。

注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁盘的。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo log写入到磁盘中。

1. 一种是，redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。
2. 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，如果 innodb_flush_log_at_trx_commit 设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务A在redo log buffer里的日志一起持久化到磁盘。

2.7 redo log组提交（group commit）机制

日志逻辑序列号（log sequence number，LSN）。LSN是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log，LSN的值就会加上length。
LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redo log。
如图3所示，是三个并发事务 (trx1, trx2, trx3) 在prepare阶段，都写完redo log buffer，持久化到磁盘的过程，对应的LSN分别是50、120和160。

trx1是第一个到达的，会被选为这组的leader；等trx1要开始写盘的时候，这个组里面已经有了三个事务，这时候LSN也变成了160；trx1去写盘的时候，带的就是LSN=160，因此等trx1返回时，所有LSN小于等于160的redo log，都已经被持久化到磁盘；这时候trx2 和trx3就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。

2.8 Binlog 和 Redolog 记录如何保持一致

两阶段提交、redo log prepare write -> binlog write -> redo log commit write

MySQL为了让组提交的效果更好，把redo log做fsync的时间拖到了步骤1之后。也就是说，上面的图变成了这样：

2.9 执行一个 update 语句以后，我再去执行 hexdump 命令直接查看 ibd 文件内容，为什么没有看到数据有改变呢

这可能是因为WAL机制的原因。update语句执行完成后，InnoDB只保证写完了redo log、内存，可能还没来得及将数据写到磁盘。

2.10 为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的

MySQL这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。

而redo log并没有这个要求，中间有生成的日志可以写到redo log buffer中。redo log buffer中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

还有一点，binlog存储是以statement或者row格式存储的，而redo log是以page页格式存储的。page格式，天生就是共有的，而row格式，只跟当前事务相关。

2.11 事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？

不会。因为这时候binlog也还在binlog cache里，没发给备库。crash以后redo log和binlog都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

2.12 MySQL 怎么知道 binlog 是完整的?

一个事务的binlog是有完整格式的：statement格式的binlog，最后会有COMMIT；row格式的binlog，最后会有一个XID event。

另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于 binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。

2.13 redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：如果碰到既有prepare、又有 commit的redo log，就直接提交；如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

2.14 处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计

其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在 binlog 写完以后MySQL发生崩溃，这时候binlog已经写入了，之后就会被从库（或者用这个binlog恢复出来的库）使用。

所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

2.15 如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑

其实，两阶段提交是经典的分布式系统问题，并不是MySQL独有的。如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。对于 InnoDB 引擎来说，如果redo log提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果redo log直接提交，然后binlog写入的时候失败，InnoDB又回滚不了，数据和binlog日志又不一致了。两阶段提交就是为了给所有人一个机会，当每个人都说我 ok的时候，再一起提交。

2.16 能不能只用 redo log，不要 binlog

如果只从崩溃恢复的角度来讲是可以的。你可以把binlog关掉，这样就没有两阶段提交了，但系统依然是crash-safe的。因为binlog有着redo log无法替代的功能。一个是归档。redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。

一个就是MySQL系统依赖于binlog。binlog作为MySQL一开始就有的功能，被用在了很多地方。其中，MySQL系统高可用的基础，就是binlog复制。

2.17 redo log崩溃恢复？正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢

实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由redo log更新过去”的情况。

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redo log毫无关系。

在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

2.18 redo log buffer 是什么？是先修改内存，还是先写 redo log 文件

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里。

所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。

但是，真正把日志写到redo log文件（文件名是ib_logfile+数字），是在执行commit语句的时候做的。（这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况）

2.19 为什么binlog 是不能“被打断的”的

一个线程只能同时有一个事务在执行。由于这个设定，所以每当执行一个begin/start transaction的时候，就会默认提交上一个事务；这样如果一个事务的binlog被拆开的时候，在备库执行就会被当做多个事务分段执行，这样破坏了原子性，是有问题的。

2.20 undolog干嘛的，什么时候用到？长事务有什么坏处？

undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

undo log 也是 MVCC(多版本并发控制)实现的关键

你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

我见过数据只有20GB，而回滚段有200GB的库。最终只好为了清理回滚段，重建整个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。

2.21 binlog 的三种格式

1. STATEMENT模式（SBR），每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如 sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
2. ROW模式（RBR），不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
3. MIXED模式（MBR）,以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于 STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

STATEMENT

ROW

可以看到，与statement格式的binlog相比，前后的BEGIN和COMMIT是一样的。但是，row格式的binlog里没有了SQL语句的原文，而是替换成了两个event：Table_map和Delete_rows。

2.22 为什么会有 mixed 格式的 binlog？为什么不用mixed格式日志？

因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。但row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。

所以，MySQL就取了个折中方案，也就是有了mixed格式的binlog。mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

也就是说，mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。

因此，如果你的线上MySQL设置的binlog格式是statement的话，那基本上就可以认为这是一个不合理的设置。你至少应该把binlog的格式设置为mixed。

当然我要说的是，现在越来越多的场景要求把MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。

即使我执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。

三、其他

3.1 事务开始的时机？

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot这个命令。

• 第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
• 第二种启动方式，一致性视图是在执行start transaction with consistent snapshot时创建的。

默认autocommit=1，当autocommit为开启状态时，即使没有手动start transaction开启事务，mysql默认也会将用户的操作当做事务即时提交。

3.2 事务数据读取是如何隔离的？

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见；
• 版本已提交，但是是在视图创建后提交的，不可见；
• 版本已提交，而且是在视图创建前提交的，可见。

这时，事务A的查询语句的视图数组是在执行这个语句的时候创建的，时序上(1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

• (1,3) 还没提交，属于情况1，不可见；
• (1,2) 提交了，属于情况3，可见。

3.3 什么是预读？预读为什么有效？

磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。

数据访问，通常都遵循集中读写的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的局部性原理，它表明提前加载是有效的，确实能够减少磁盘IO。

3.4 什么是缓冲池(buffer pool)

1. 缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；
2. 缓冲池通常以页(page)为单位缓存数据；
3. 缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；
4. InnoDB对普通LRU进行了优化：
   • 将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题
   • 页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

3.5 写缓冲(change buffer)

假如要修改页号为40的索引页，而这个页正好不在缓冲池内。加入写缓冲优化后，流程优化为：

1. 在写缓冲中记录这个操作，一次内存操作；
2. 写入redo log，一次磁盘顺序写操作；

除了数据页被访问，还有哪些场景会触发刷写缓冲中的数据呢？

还有这么几种情况，会刷写缓冲中的数据：

1. 有一个后台线程，会认为数据库空闲时；
2. 数据库缓冲池不够用时；
3. 数据库正常关闭时；
4. redo log写满时；

画外音：几乎不会出现redo log写满，此时整个数据库处于无法写入的不可用状态。

先说什么时候不适合，如上文分析，当：

1. 数据库都是唯一索引；
2. 写入一个数据后，会立刻读取它；

这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，本来相应页面就要入缓冲池，此时写缓存反倒成了负担，增加了复杂度。

什么时候适合使用写缓冲，如果：

1. 数据库大部分是非唯一索引；
2. 业务是写多读少，或者不是写后立刻读取；

可以使用写缓冲，将原本每次写入都需要进行磁盘IO的SQL，优化定期批量写磁盘。

画外音：例如，账单流水业务。

参数：innodb_change_buffer_max_size

介绍：配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。

画外音：写多读少的业务，才需要调大这个值，读多写少的业务，25%其实也多了。
 
参数：innodb_change_buffering

介绍：配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等。

带change buffer的更新过程，Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息将上述动作记入redo log中。

change buffer的读过程，要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

3.6 InnoDB自适应哈希

在MySQL运行的过程中，如果InnoDB发现，有很多SQL存在这类很长的寻路，并且有很多SQL会命中相同的页面(page)，InnoDB会在自己的内存缓冲区(Buffer)里，开辟一块区域，建立自适应哈希所有AHI，以加速查询。

系统自己判断应该可以加速查询而建立的，不需要用户手动建立，故称自适应。

既然是哈希，key是什么，value是什么？

• key是索引键值（或者键值前缀）。
• value是索引记录页面位置。

当业务场景为下面几种情况时：

• 很多单行记录查询（例如passport，用户中心等业务）
• 索引范围查询（此时AHI可以快速定位首行记录）
• 所有记录内存能放得下

AHI往往是有效的。

画外音：任何脱离业务的技术方案，都是耍流氓。

当业务有大量like或者join，AHI的维护反而可能成为负担，降低系统效率，此时可以手动关闭AHI功能。

3.7 一个 SQL 查询过程

3.8 一个SQL 更新过程

这个是没有change buffer的情况。有change buffer的情况，看上面change buffer写入。

3.9 Mysql 优化器, 优化器如何选择索引的

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

我们这个简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描行数的时候出问题了。

那么，问题就是：扫描行数是怎么判断的？

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

我们可以使用show index方法，看到一个索引的基数。如图所示。虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。

那么，MySQL是怎样得到索引的基数的呢？这里，我给你简单介绍一下MySQL采样统计的方法。

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

• 设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
• 设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

既然是统计信息不对，那就修正。analyze table t命令，可以用来重新统计索引信息。

3.10 什么是账页，有哪些场景会强制刷脏页？

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。部分刷账页可能导致Mysql抖动。

强制刷脏页的场景

1. InnoDB的redo log写满了。这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
2. 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。你一定会说，这时候难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？这里其实是从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：
   • 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
   • 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。
3. MySQL认为系统“空闲”的时候。当然，MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要合理地安排时间，即使是“生意好”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。
4. MySQL正常关闭的情况。这时候，MySQL 会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

第一种是“redo log 写满了，要 flush 脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

• 第一种是，还没有使用的；
• 第二种是，使用了并且是干净页；
• 第三种是，使用了并且是脏页。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
• 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

3.11 当 MySQL 去更新一行，但是要修改的值跟原来的值是相同的，这时候 MySQL 会真的去执行一次修改吗？

参考

InnoDB 认真执行了“把这个值修改成 (1,2)”这个操作，该加锁的加锁，该更新的更新。

3.12 我查这么多数据，会不会把数据库内存打爆

我经常会被问到这样一个问题：我的主机内存只有 100G，现在要对一个200G的大表做全表扫描，会不会把数据库主机的内存用光了？

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

1. 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k。
2. 重复获取行，直到net_buffer写满，调用网络接口发出去。
3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

也就是说，MySQL是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。

3.13 读写分离，遇到主从不一致的问题常用的解决方案？

1. 强制走主库方案；
2. sleep方案；
3. 判断主备无延迟方案；show slave status ，判断seconds_behind_master是否已经等于0。如果还不等于 0 ，那就必须等到这个参数变为0才能执行查询请求。
4. 配合semi-sync方案，要解决这个问题，就要引入半同步复制，也就是semi-sync replication，
   • 事务提交的时候，主库把binlog发给从库；
   • 从库收到binlog以后，发回给主库一个ack，表示收到了；
   • 主库收到这个ack以后，才能给客户端返回“事务完成”的确认。
   • 也就是说，如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。
5. 等主库位点方案；
   • Master_Log_File和Read_Master_Log_Pos，表示的是读到的主库的最新位点；
   • Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是备库执行的最新位点。
   • 如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成。
6. 等 GTID 方案，对比 GTID 集合确保主备无延迟。
   • Auto_Position=1，表示这对主备关系使用了GTID协议。
   • Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合；
   • Executed_Gtid_Set，是备库所有已经执行完成的GTID集合。

3.14 InnoDB有哪些类型的锁？

排它锁、共享锁、意向排他锁、意向共享锁、自增锁、间隙锁、临键锁、记录锁

3.15 Mysql加锁原则？

为什么我只改一行的语句，锁这么多？

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

3.16 Mysql两阶段锁？

在一个事务里面，分为加锁(lock)阶段和解锁(unlock)阶段,也即所有的lock操作都在unlock操作之前

事务分为两个阶段：

• 第一阶段是获得封锁，也称为扩展阶段；
• 第二阶段是释放封锁，也称为收缩阶段。

也就是说，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

3.17 死锁和死锁检测

这时候，事务A在等待事务B释放id=2 的行锁，而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

3.18 什么是热点行

那如果是我们上面说到的所有事务都要更新同一行的场景呢？ 每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。

根据上面的分析，我们来讨论一下，怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的 CPU资源。

• 一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
• 另一个思路是控制并发度。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有600个客户端，这样即使每个客户端控制到只有5个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到3000。

因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改MySQL源码的人，也可以做在MySQL里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。

要访问的行上有锁，他才要死锁检测。

3.19 mysql 如何对记录进行排序的，内存排序、文件排序，文件合并用归并排序

show variables like '%sort_buffer_size%'; //8M ，生成环境是8M

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

number_of_tmp_files表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要12个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

select from t where city='杭州' order by name limit 1000  ;

1. 初始化sort_buffer，确定放入name、city、age这三个字段；
2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；
6. 对sort_buffer中的数据按照字段name做快速排序；按照排序结果取前1000行返回给客户端。

rowid 排序

如果MySQL认为排序的单行长度太大会怎么做呢？
max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。

1. 始化sort_buffer，确定放入两个字段，即name和id；
2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入sort_buffer中；
4. 从索引city取下一个记录的主键 id；
5. 重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；
6. 对sort_buffer中的数据按照字段name进行排序；
7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

3.20 mysql join 如何实现的。join的时候需要注意什么

show variables like '%join_buffer_size%';  //8M

select * from t1 straight_join t2 on (t1.a=t2.b);

1. 扫描表t1，顺序读取数据行放入join_buffer 中，放完第88行join_buffer满了，继续第2步；
2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
3. 清空join_buffer；
4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

第一个问题：能不能使用 join 语句？

1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

第二个问题是：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
2. 如果是Block Nested-Loop Join算法：
   • 在join_buffer_size足够大的时候，是一样的；
   • 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

3.21 为什么不要大表join？

污染缓存池

由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是，由于我们的 join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。这样，就会导致这个 MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数。

3.22 内存临时表

1. 建表语法是 create temporary table
2. 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的临时表t，对于 session B就是不可见的。
3. 临时表可以与普通表同名。
4. session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
5. show tables命令不显示临时表。

由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。也正是由于这个特性，临时表就特别适合我们文章开头的join优化这种场景。为什么呢？

原因主要包括以下两个方面：

1. 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。
2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

磁盘临时表

其实不是的。tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。

3.23 什么时候会使用临时表

union

(select 1000 as f) union (select id from t1 order by id desc limit 2);

这个语句的执行流程是这样的：

1. 创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。
2. 执行第一个子查询，得到1000这个值，并存入临时表中。
3. 执行第二个子查询：
   • 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
   • 取到第二行id=999，插入临时表成功。
4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

group by 执行流程

select id%10 as m, count(*) as c from t1 group by m;

这个语句的执行流程是这样的：

1. 创建内存临时表，表里有两个字段m和c，主键是m；
2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；
   • 如果临时表中没有主键为x的行，就插入一个记录(x,1);
   • 如果表中有主键为x的行，就将x这一行的c值加1；
3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

3.24 distinct 和 group by 的性能

select a from t group by a order by null;
select distinct a from t;

首先需要说明的是，这种group by的写法，并不是SQL标准的写法。标准的group by语句，是需要在select部分加一个聚合函数，比如：

select a,count(*) from t group by a order by null;

这条语句的逻辑是：按照字段a分组，计算每组的a出现的次数。在这个结果里，由于做的是聚合计算，相同的a只出现一次。

没有了count(*)以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：按照字段a做分组，相同的a的值只返回一行。而这就是distinct的语义，所以不需要执行聚合函数时，distinct和group by这两条语句的语义和执行流程是相同的，因此执行性能也相同。

group by 指导原则

1. 如果对group by语句的结果没有排序要求，要在语句后面加order by null；
2. 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和Using filesort；
3. 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
4. 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

3.25 count(*)、count(主键 id) 和 count(1)

对于count(主键 id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于count(字段)来说：

1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是 null才累加。
   但是count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

所以结论是：按照效率排序的话，count(字段)< count(主键 id) < count(1) ≈count()，所以我建议你，尽量使用 count()。

3.26 什么是内存表？

可见，InnoDB和Memory引擎的数据组织方式是不同的：

• InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id。这种方式，我们称之为索引组织表（Index Organizied Table）。
• 而Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

从中我们可以看出，这两个引擎的一些典型不同：

1. InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
2. 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
3. 数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；
4. InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
5. InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob和Text字段，并且即使定义了 varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。
6. 内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作。
7. 数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。
   由于内存表的这些特性，每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用。比如，如果要在表t1中执行：

基于内存表的特性，我们还分析了它的一个适用场景，就是内存临时表。内存表支持hash索引，这个特性利用起来，对复杂查询的加速效果还是很不错的。

3.27 自增主键为什么不是连续的？什么场景下自增主键可能不连续？

深层次原因是，不判断自增主键是否已存在可减少加锁的时间范围和粒度->为了更高的性能->自增主键不能回退->自增主键不连续

在什么场景下自增主键可能不连续

1. 唯一键冲突
2. 事务回滚
3. 自增主键的批量申请

3.28 使用limit offset 分页时，为什么越往后翻越慢？

但当执行select * from table limit 800000，20的时候数据读取就很慢，limit 800000，20的意思扫描满足条件的800020行，扔掉前面的800000行，返回最后的20行，可想而知这时会很慢，测试了一下达到37.44秒之久。

3.29 慢查询工具 mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow

• s: 是表示按照何种方式排序
• c: 访问次数
• l: 锁定时间
• r: 返回记录
• t: 查询时间
• al:平均锁定时间
• ar:平均返回记录数
• at:平均查询时间
• t:即为返回前面多少条的数据
• g:后边搭配一个正则匹配模式，大小写不敏感的

3.30 SQL语句执行效率的主要因素

1. 数据量
   • SQL执行后返回给客户端的数据量的大小
   • 数据量越大需要扫描的I/O次数越多，数据库服务器的IO更容易成为瓶颈。
2. 取数据的方式
   • 数据在缓存中还是在磁盘上;
   • 是否能够通过全局索引快速寻址;
   • 是否结合谓词条件命中全局索引加速扫描;
3. 数据加工的方式
   • 排序、子查询、聚合、关联等，一般需要先把数据取到临时表中，再对数据进行加工;
   • 对干数据量比较多的计算，会消耗大量计算节点的CPU资源，让数据加工变得更加缓慢;
   • 是否选择了合适的join方式

3.31 SQL优化思路

1. 减少数据扫描 (减少磁盘访问)
   • 尽量在查询中加入一些可以提前过滤数据的谓词条件，比如按照时间过滤数据等，可以减少数据的扫描量，对查询更友好;
   • 在扫描大表数据时是否可以命中索引，减少回表代价，避免全表扫描
2. 返回更少数据 (减少网络传输或磁盘访问)
3. 减少交互次数(减少网络传输)
   • 将数据存放在更快的地方.
   • 某条查询涉及到大表，无法进一步优化，如果返回的数据量不大且变化频率不高但访问频率很高，此时应该考虑将返回的数据放在应用端的缓存当中或者Redis这样的缓存当中，以提高存取速度.
4. 减少服务器CPU开销 (减少CPU及内存开销)
5. 避免大事务操作
6. 利用更多资源 (增加资源)

3.32 DDL 几种形式

show variables like '%join_buffer_size%';  //8M
show variables like '%sort_buffer_size%'; //8M
show variables like '%innodb_buffer_pool_size%'; // 64G