MySQL知识点总结

本文内容比较多，建议通过右栏的目录选择需要的章节，其中带¥的为面试常问的知识点

一. 通用模块

1. MySQL体系结构

• 连接器：管理连接，权限验证（连接分为长连接和短连接）
• 解析器：词法分析，语法分析
• 查询缓存：解析完后若命中则直接返回结果（由于对一个表的更新，这个表上所有的查询缓存都会被清空，导致命中率低，MySQL 8.0 后废除）
• 优化器：执行计划生成，索引选择
• 存储引擎：真正负责数据的存取（底层物理结构），是基于表而不是基于库

2. ¥MyISAM和InnoDB存储引擎

• 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
• 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
• 是否支持外键： MyISAM不支持，而InnoDB支持。
• 是否支持MVCC ：仅 InnoDB 支持。

适用场景：

• MyISAM适合作很多count计算，插入不频繁，查询非常频繁，不需要事务支持，也不在乎崩溃后的安全恢复问题的场景
• 大部分情况下使用InnoDB

3. MySQL数据类型

1. 整型：tinyint(1)，smallint(2)，mediumint(3)，int(4)，bigint(8)
2. 实数型：float(4)，double(8) 用于近似计算
   decimal(每32位存9个数字),本质是二进制字符串，用于精确计算（如钱）
3. 字符串类型：varchar 变长（用1或2个额外字节记录字符串长度）
   char 定长（适用于较小字符串，同长字符串）
   binary，varbinary
4. 枚举：实际存储时只存储一个常数，查询时根据该整数查询映射
5. 日期和时间：datetime(8) 与时区无关
   timestamp(4) 时间戳，与时间相关（尽量使用）
6. 大型字符串：blob(二进制），text（字符型）
   过大则会用指针指向外部空间，排序时只使用前几个字节
7. 位类型：bit

3.5 mysql 的datetime 和timestamp有什么区别？datetime加索引如何做比较的

• 自动更新日期：datetime默认值时null不会自动更新，如果需要更新就得对datetime进行特殊设置；timestamp默认值时not null，所以它的默认时间时CURRENT_TIMESTAMP，当行数据发生变化的时候采用的时当前时间。
• 日期存储方式：timestamp存储的时间会自动转换为utc(格林威治标准时间)，datetime是不会做出任何转变
• 范围不一样：timestamp支持的范围是1970-01-01 00:00:01到2038-01-19 03:14:07，在数据库用整型的int型存储，int型的范围是2的32次方减1。datetime支持的日期范围是 从0000-00-00 00:00:00 到 9999-12-31 23:59:59

datetime 底层存储实现是 BigInt，索引存储上和 BigInt 的处理是几乎一模一样的，所以 BigInt 支持的索引查询，datetime也支持。

3.7 MySQL null占不占空间？

根据字段类型不同，结果是不同的。例如：varchar，text等类型，字段为null不占用空间。int，char等类型，字段为null也占用空间。不能一概而论。

3.8 MySQl一行最大可以多大？一页多大？

• 表一行最多支持65535字符
• 一页默认大小16K

4. 删除表的方法

1. delete table: delete全表很慢，需要生成回滚日志、写 redo、写 binlog。如果binlog是row格式的，可以恢复数据。
2. truncate table/drop table：速度很快，但binlog 里面就只有一个 truncate/drop 语句，恢复不出数据

5. 删除数据的恢复

1. 误删行：在binlog_format=row 和 binlog_row_image=FULL，由于记录了删除的详细信息，可以Flashback 工具通过反向操作来恢复数据
   步骤：恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。
2. 误删库 / 表：需要使用全量备份，加增量日志
3. 延迟复制备库：延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。比如你把 N 设置为 3600，这就代表了如果主库上有数据被误删了，并且在 1 小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

6. 重建表（内存碎片回收）

• 原因：delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，不能回收表空间的。
• 方法：alter table A engine=InnoDB
• 优化：MySQL 5.6 版本开始引入的 Online DDL，在重建过程中也允许增删改操作
• 流程：

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
5. 用临时文件替换表 A 的数据文件。

7. InnoDB逻辑存储结构

• 表结构：逻辑结构最高层
• 段：数据段，索引段，回滚段
• 区：64个连续页组成的空间，是InnoDB的空间申请单位
• 页：磁盘管理最小单位，默认16K
• 行

8. 视图（View）

• 视图是一个命名的虚表，没有实际的物理存储
• 创建：create view v as select * from t where id>0
• 创建出的视图默认是不会更新的，需要可更新视图则在后面加with check option

9. 临时表

• 创建：create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
• 特点：

1. 一个临时表只能被创建它的 session 访问，对其他线程不可见。session 结束的时候，会自动删除临时表
2. 临时表可以与普通表同名。
3. session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
4. show tables 命令不显示临时表。

• 适合场景：join 优化

10. 内存表

使用Memory引擎的表为内存表，其所有数据都存放在内存中

内存表根据数据的插入顺序存放数据，主键索引上记录的是数据存放的位置（即hash索引）

但是内存表适合用作临时表，因为

1. 相比于 InnoDB 表，使用内存表不需要写磁盘，往表 temp_t 的写数据的速度更快；
2. 索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快；
3. 临时表数据只有 2000 行，占用的内存有限。

所以很多时候在内部会使用内存临时表

11. 分区表

分区表是指将表中的各个部分分配到不同的物理文件中

分区类型：Range,List,Hash（取余）,Key,Columns

适用场景：

1. 方便删除历史数据，删除某一分区数据很方便
2. 可以加速对于在某一分区上执行查询操作的查询

缺点：如果操作不能在一个分区上完成，则需要扫描所有分区，大大降低性能

分区与分库分表：

分区表和手工分表，一个是由 server 层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。

用业务分表，对业务开发同学没有额外的复杂性，对 DBA 也更直观，自然是更好的。

因此应该优先使用手动分库分表

12.缓冲池(Buffer pool)

MySQL会将一些数据页保存在内存但缓冲池中

内存淘汰策略：

• 优化LRU:新插入的数据先放入old区（5/8处），时间超过1s后才会从old区移动到young区，避免查询不常用的数据导致热点数据被淘汰。

13. ¥数据库建表三大范式

• 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
• 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
• 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。

二. 索引

1. ¥索引原理（B树，B+树，B*树）

• 索引搜索原理：从根节点开始，对结点内对关键字序列进行二分查找，找到所属范围后进入该子节点，直至找到对应叶子结点
• B树与B+树结构的区别：B树的关键字集合分布在整棵树中，叶子结点和非叶子结点均存放数据；B+树关键字都在叶子结点链表中，不可能在非叶子结点中命中
• B*树：在B+树的非根节点和非叶子结点在增加指向兄弟的指针，提高了非叶子结点的使用率

2. ¥为什么B+树比B树更适合文件索引？

1. 不同于B树只适合随机检索，B+树同时支持随机检索和顺序检索；B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的
2. B+树的磁盘读写代价更低。B+树的内部结点并没有指向关键字具体信息的指针，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素。
3. B+树的查询效率更加稳定。在B+树中，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

3. ¥聚簇索引与非聚簇索引

InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。

这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址。

在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。

4. ¥聚簇索引的优缺点

优点：
1）减少扫描的数据量，减少IO次数
2）聚簇索引主键自动保持有序，可以避免主键排序
3）主键有序，可以将随机IO转为顺序IO

缺点：
1）插入速度依赖插入顺序
2）插入时存在“页分裂”问题
3）二级索引需包含主键列，占用空间
4）二级索引的访问需两次查找

5. 页分裂与合并（索引维护的开销）

如果索引所在的数据页已经满了，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

6. ¥最左匹配原则

若索引中定义了多个值，索引进行匹配时会根据定义的顺序从左到右匹配直至遇到范围查询或like：

1. 若索引最左侧的列没有用到，则无法使用索引
2. 不能跳过索引中的列，如果跳过则只用前面的列
3. 范围查询右边的列无法继续使用索引

索引为（a,b)
select * from table_name where a > 1 and a < 3 and b > 1; b用不到索引

4. 如果是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了

select * from table_name where a like ‘As%’; //前缀都是排好序的，走索引查询
select * from table_name where a like ‘%As’//全表查询
select * from table_name where a like ‘%As%’//全表查询

7. ¥怎么给字符串字段建索引

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引,不支持范围扫描；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

8. 建立联合索引时字段顺序

• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

• 对于需要排序的字段放在后面（最左匹配）

• 区分度大的字段放在前面（过滤更多记录）

• 如果既有联合查询，又有基于 a、b 各自的查询这时候，要考虑的原则就是空间了。比如，name 字段是比 age 字段大的 ，建议创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

9. ¥索引覆盖

从辅助索引中可以获得全部所需结果时，不需要再查询聚簇索引中的记录

因此在SQL优化时尽量达到索引覆盖，不必再回表扫描

10. ¥Change Buffer

• 当需要更新一个数据页时，如果数据页在内存中就直接更新；
• 如果这个数据页没有在内存中，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
• 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭的过程中，也会执行 merge 操作。

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多，收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

11. ¥唯一索引和普通索引的区别

1）唯一索引的更新不能使用 change buffer，只有普通索引可以使用。

如果记录要更新的目标页不在内存中：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

因此应尽量使用普通索引

2）在rr隔离级别下唯一索引会退化成行锁，减小锁冲突

12. ¥为什么尽量选用自增主键，而不选用业务内容作为主键

业务内容具有较强的随机性，不一定满足主键的顺序，增大了随机IO的可能

13. ¥自增主键的问题

1. 自增主键存放的位置：

• 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化
• 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

2. 自增键不连续的原因

• 为了提高性能和并发度，MySQL在获取自增主键的值时会加锁，获取完后无须等待语句执行完即释放锁，之后即使语句执行失败，也不会将自增主键回滚

• 因此唯一键冲突，事务回滚均会导致自增主键不连续。

• 另外，在批量申请自增主键时，MySQL会作优化（自增锁优化），也有可能会导致自增主键不连续。

3. 自增主键用完了怎么办

• 再申请下一个 id 时，得到的值保持不变

14. ¥索引失效原因

1. 条件字段函数操作
2. 隐式类型转换（字符与数字比较，字符编码不同）
3. 通配符开头like操作
4. 索引统计信息不准确（analyze table命令，重新统计索引信息）
5. 优化器判断后发现走索引成本太高，不如全表扫描（force index 强行选择索引）

解决方案：修改SQL语句，重建索引，删除索引

15. MMR优化

• 查询辅助索引后，会先将查询结果按主键进行排序，再按主键顺序查找聚集索引，从而将随机IO转为顺序IO
• 可以将某些范围拆分为键值对后进行批量查询

16. 自适应哈希索引

MySQL会自动为热点页建立哈希索引提升效率，该索引用户一般无法控制

当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。

17. 索引下推

1. 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
2. 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

如

SELECT * FROM person  WHERE `name` = "1" AND `address` LIKE "%222" and first_name LIKE "%222";

• 如果不使用索引条件下推优化的话，MySQL只能根据索引查询出name=1的所有行，然后再依次比较是否符合全部条件。
• 当使用了索引条件下推优化技术后，可以通过索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合条件的数据才将整行数据查询出来。查看执行计划时发现extra一栏中有Using index condition信息，说明使用了索引下推。

18. 通常一棵B+树可以存放多少行数据

InnoDB存储引擎最小储存单元为页（Page），一个页的大小是16K。

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时 一次页的查找代表一次IO， 所以通过主键索引查询通常 只需要1-3次IO操作 即可查找到数据。

三. 锁

1. 全局锁

全局锁就是对整个数据库实例加锁。加了全局锁后数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句均被堵塞。

使用场景是：全库逻辑备份，把整库每个表都 select 出来存成文本。

2. ¥表锁/MDL锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

如果一个连接申请MDL 写锁时被阻塞（其他连接正在增删查改），后序申请MDL 读锁的连接都会被阻塞。因此给一个小表加个字段，可能导致整个库挂了。

3. 线上如何安全的给表添加字段

1. 解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
2. 在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

4. ¥行锁

行锁分为共享锁和排它锁：

• 共享锁（S Lock)：事务读一行数据时加共享锁
• 排它锁（X Lock)：事务修改一行数据时加共享锁

X锁与任何锁不兼容，S锁仅与S锁兼容

5. ¥行锁的两阶段锁协议

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

6. ¥多版本并发控制（MVVC)，一致性非锁定读

在读数据时，不需要加锁，而是去读取该行的快照数据，从而减少了锁的冲突，提高了数据库的并发性

读取快照数据时，根据事务隔离级别的不同，读取方式不同：

• READ-COMMITTED：总是读取最新一份快照数据
• REPEATABLE-READ：总是读取事务开始时的行数据版本

7. ¥多版本并发控制（MVVC)的实现原理

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据；

8. 当前读与一致性非锁定读

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”。

select 语句如果加锁（悲观锁），也是当前读。

• select…for update:对读取的行加写锁
• select…lock in share mode:对读取的行加读锁

9. ¥Next-Key Lock

在MySQL默认REPEATABLE-READ隔离级别下，使用Next-Key Lock加锁

为什么需要Next-Key Lock：

• 需要符合可重复读的语义（当前读情况）：如果加锁只锁住该行，那么在该范围内允许插入修改数据，导致幻读产生，不符合可重复读的语义
• 数据和日志在逻辑上的一致性：如果仅锁住当前行，通过binlog得到的最终数据结果和数据库中的结果不一致

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间，间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响了并发度

因此，如果要提高并发度，如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这也是现在不少公司使用的配置组合。

10. 加锁规则

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

11. 死锁与死锁检测检测

MySQL一个事务如果获取不到锁资源会进入等待，若等待时间超过50s（默认）则会开启死锁检测，若死锁检测发现死锁，则会回滚持有最少排它锁的事务，让另一个事务继续执行

但是死锁检测的时间复杂度是O(n)，如果并发线程过多可能会因为死锁检测占用CPU过大导致TPS下降

• 解决方案：临时关闭死锁检测，控制并发度

12. 锁分析

通过数据库中的lock_waits，lock，trx表分析锁

13. MySQL两个账户相互转账会发生什么，如何解决出现的死锁问题

账户转账分为减自己的钱和加对方的钱两个步骤
两个账户同时转账可能会发生A先锁住了自己的行，再去修改B的行时B也锁住了自己的行，从而导致死锁问题

MySQL在一段时间（50s）获取不到锁资源时会去判断死锁，若发现死锁则回滚持有最少排它锁的事务。可以等待MySQL自动解除死锁，更好的方式是在事务调用方就规定超时时间，如果规定时间内没有返回则自动回滚，提醒用户重试。

四. 事务

1. ¥事务的四大特性（ACID)

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务后，数据库从一个正确的状态变化到另一个正确的状态；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

2. ¥并发事务带来的问题

1. 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
2. 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 （理论上对行数据修改时会加行锁，不会发生该问题；但在逻辑上可能会发生）
3. 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
4. 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

3. ¥事务隔离级别有哪些?MySQL的默认隔离级别是?

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

4. MySQL是怎么解决幻读的

在REPEATABLE-READ级别下：

• 对于普通读，通过MVVC读取一致的快照，不会出现幻读
• 对于当前读，通过Next-key给范围加锁，解决了幻读问题

5. ¥事务实现原理

• redolog崩溃恢复保证事务的持久性
• undolog回滚保证事务的原子性

五. 日志

¥1. binlog（二进制日志）

• 需要注意，binlog是MySQL Server层的日志
• binlog的作用：备份，恢复，复制，审计
• binlog格式：

1. statement：只记录SQL原文
2. row：记录具体修改信息，占用空间很大（READ-COMMITTED + row 既可保证数据一致，又可减少锁冲突，是常用配置）
3. mixed：综合以上两种

• sync_binlog 这个参数设置成 1 表示每次事务的 binlog 都持久化到磁盘。设置成 1可以保证 MySQL 异常重启之后 binlog 不丢失。

¥2. redolog(重做日志）

• redolog是InnoDB存储引擎独有的，用来保证持久性
• 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面（操作系统的文件系统缓存中），并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面（由innodb_flush_log_at_trx_commit控制）
• InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，写到末尾就又回到开头循环写。
• crash-safe：有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，可以从redolog中获取记录，使之前提交的记录都不会丢失。

3. innodb_flush_log_at_trx_commit

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

若不设为1，数据库宕机了还可以从操作系统的文件系统缓存中获取redolog实现崩溃恢复；但如果是操作系统宕机了，如果没有记录到磁盘上则该部分数据无法恢复

4. 有了binlog为什么还需要redolog

binlog 日志只能用于归档，没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

5. ¥redo log 和 bin log的区别

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。（因此崩溃恢复需要redo log)
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
• redo log在事务进行过程中不断写入，binlog只在事务提交前写入

6. redo log崩溃恢复原理

LSN：日志序列号，表示重做日志写入的总量

每个数据页上有一个LSN，表示该页最后刷新时的LSN大小

宕机后如果发现该页的LSN小于重做日志的LSN,且该事务已提交，则会根据日志中LSN的差值进行数据恢复

7. redolog 和 binlog写入的具体流程

1. 执行器先找引擎取 ID=2 这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

8. 为什么redo log 的写入需要两阶段提交

“两阶段提交”的原因：为了让两份日志之间的逻辑一致。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

如先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。

因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

先写 binlog 后写 redo log同理。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

9. ¥undo log

• undo log 用于事务的回滚
• 在对数据库进行修改时，会产生undo log，放在数据库对undo段
• 特点：

1. undo是逻辑日志，通过逻辑日志进行回滚本质就是进行反向操作，因此无法回滚物理上对修改
2. MVVC借助undo实现，通过undo计算得到之前版本的数据
3. undo也会产生redo log

• undo log的删除：
  undo log存放在链表中，但是由于MVVC借助undo log实现，因此本事务的undo log可能会被其他事务引用，因此删除时需要等到该行记录不被其他事务引用时，再通过purge线程删除（供其他事务重用）

11. binlog 组提交机制

为了减少IO次数，可采用组提交（group commit）机制：一次 fsync会顺便把其他事务的日志进行fsync

组提交通过以下参数控制：

• binlog_group_commit_sync_delay：延迟多少微秒后才调用 fsync;
• binlog_group_commit_sync_no_delay_count ：累积多少次以后才调用 fsync。

10. 其他日志

• 错误日志：记录错误信息，警告信息
• 查询日志：记录所有查询请求
• 慢查询日志：记录运行时间较长日志

六. SQL

1. Order排序的原理

Order排序存在三种情况：

• 内存中排序（sort_buffer）
• 磁盘临时文件排序：如果sort_buffer空间不会，则会生成磁盘临时文件供排序使用
• rowid排序：如果查询的数据字段数过多，导致内存中能放的数据很少，则会先选择只有要排序的列（即 name 字段）和主键 id排序。排完序的结果因为少了其他 字段的值，不能直接返回了，需要再回到原表去取数据.

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

2. Join原理，是否应选择Join

基本原理是嵌套循环：根据第一个查出的结果去下一张表上查找。优化器会选择较优的方式选择驱动表和被驱动表

• Index Nested-Loop Join

联结两个表时，如果第二个表上有联结键索引，那么就会根据第一个表中查出的联结键值搜索索引，然后根据索引得到的主键id去第二张表找

在这种情况下：使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

• Simple Nested-Loop Join

联结两个表时，如果第二个表上没有联结键索引，那么就会根据第一个表中查出的联结键值，去第二张表上作全表扫描

• Block Nested-Loop Join

对于上面这种情况，MySQL使用了 Block Nested-Loop Join

1. 把表 t1 的数据读入线程内存 join_buffer 中；
2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的判断是内存操作，速度上会快很多，性能也更好。

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

• 能不能使用 join 语句

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

3. Join语句的优化

Batched Key Access（BKA）：

1. 把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。
2. 将 join_buffer中的 a (关联键）进行递增排序；
3. 排序后的 id 数组，依次到 a 索引中查记录（顺序IO)，并作为结果返回。

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置：set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

• BNL 转 BKA

一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了。

如果不适合建索引，我们可以考虑使用临时表。
使用临时表的大致思路是：

把表 t2 中满足条件的数据放在临时表 tmp_t 中；
为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；让表 t1 和 tmp_t 做 join 操作。

4. EXIST和IN的区别

• in()先做内部查询，根据内部查询结果过滤外部查询
• exists()先做外部查询，再去判断内部查询是否成立
• 内部查询结果较小则用in()，外部查询结果较小则用exists()

5. ¥EXPLAIN各字段含义

• id:标识select所属的行
• select_type:简单查询还是复杂查询
• table：对应行正在访问的表
• type:访问类型

All：全表扫描
index：按索引次序全表扫描
range：范围扫描
ref：索引查找
eq-ref：唯一索引查找

• possible_keys：可以使用哪些索引
• key：采用的索引
• key_len：在索引里使用的字节数（以判断使用了索引中的哪几列）
• ref：在key列记录的索引中查找值所用的列或常量
• rows：估计需要读取的行数
• Extra：额外信息

Using index：使用了索引覆盖
Using where：在检索行后再进行过滤（如果能在索引中就进行过滤则不显示）
Using temporary：使用了临时表
Using filesort：使用了外部排序

七. 高可用

¥1. 主从复制的原理

从库通过IO线程获取主库的binlog，然后SQL线程执行binlog中的内容，将主库上执行的操作在从库上执行，达到主从复制

注意：如果开启了主从复制，binlog应设置成 row格式，因为statement模式主从库有可能在执行同一条SQL时采用了不同的执行逻辑导致数据不一致

2. 主主模式下的循环复制问题

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（建议把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。

可以用下面的逻辑，来解决两个节点间的循环复制的问题：

1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

3. ¥主备延迟的来源

• 查看主备延迟数值：在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。

• 主备延迟的来源：

1. 备库所在机器的性能要比主库所在的机器性能差
2. 备库的压力大：备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。

解决方法：1）一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。2）通过 binlog 输出到外部系统，比如 Hadoop
这类系统，让外部系统提供统计类查询的能力。

3. 大事务： 因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。（如一次delete大量数据，大表DDL,都有可能造成主备延迟）
4. 备库执行日志的速度持续低于主库生成日志的速度：这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。

解决方案：调整并行复制策略

5. 网络原因

4. 主备切换方案

• 可靠性优先策略
  1）判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
  2）把主库 A 改成只读状态，即把 readonly 设置为 true；
  3）判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
  4）把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
  5）把业务请求切到备库 B。
  这个切换流程，一般是由专门的 HA 系统来完成的

• 可用性优先策略
  把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。但是有可能造成数据不一致问题，因此一般采用可靠性优先。

5. ¥读写分离实现方案

• JDBC层：直接改写SQL语句，规定SQL语句访问的数据库。写操作访问主库，读操作访问从库。(轻量级框架，如Sharding-JDBC)
• Proxy层：SQL语句执行前先发送给代理，由代理层决定是在主库执行还是在从库执行。（重量级框架，如MyCat)

6. ¥主备延迟导致过期读

由于从库延迟是不能100%避免的，因此客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态，即过期读。

解决方案

1. 强制走主库方案（使用的最多）：对于必须要拿到最新结果的请求，强制将其发到主库上。
2. sleep方案：主库更新后，读从库之前先 sleep 一下再获取。
3. 等主库位点方案：
   1）trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；
   2）选定一个从库执行查询语句；
   3）在从库上执行 select master_pos_wait(File, Position, 1)；
   4）如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
   5）否则，到主库执行查询语句。

¥7. count(*)，count(字段)，count(主键 id)，count(1)的区别

1.执行效果上：

• count(🌟)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

2.性能上
MySQL，在执行 count(*) 操作的时候做了优化。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(🌟) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

性能上：count(字段)<count(主键 id)<count(1)≈count(🌟)，尽量使用 count(🌟)

8. 大表作limit分页很慢怎么办？

1. id连续的情况下，直接用where id>500的方式来解决
2. id不连续的情况下，使用select id from test limit 5000000,1来获取limit起始值，但是实际测试发现效果不大
3. id不连续的情况下，新建一个order_no字段用来计算起始值，效率很高，但是需要解决order_no更新的问题
4. 限制查询页数

八. 故障排查与性能优化

当SQL语句执行很慢时，分为两种情况：

• 一种是该SQL语句一直执行很慢，那么可能是因为SQL语句写的有问题或数据库设计有问题；
• 另一种是该SQL偶尔执行很慢，即时快时慢，那么可能是因为其他原因占用了系统资源导致本次执行较慢

下面将讨论这两种情况的解决方案

¥1. 慢查询优化（SQL语句一直执行很慢）

• 索引没设计好：如没有可用的索引，索引未完全匹配上
  解决方案：1）在线下则重新设计索引
  2）在线上则通过alter table紧急添加索引
• 选错索引：由于优化器是通过抽样的方式统计信息，因此可能因为统计信息的错误导致优化器选错索引
  解决方案：1）使用force index强制走规定索引
  2）analyze table 重新统计信息
• SQL写的不好：SQL语句写的不好的原因太多，如本可以使用索引覆盖却回表查找，需要具体问题具体分析（借助EXPLAIN)
  解决方案：1）线下则写出更优的SQL语句
  2）线上则通过query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

1.5 一条sql执行过长时间，你如何优化，从哪些方面

1. 查看sql是否涉及多表的联表或者子查询，如果有，看是否能进行业务拆分，相关字段冗余或者合并成临时表（业务和算法的优化）
2. 涉及链表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合
3. 如果以上两种都不能操作，非要链表查询，那么考虑对相对应的查询条件做索引。加快查询速度
4. 针对数量大的表进行历史表分离（如交易流水表）
5. 数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，mysql有自带的binlog实现 主从同步
6. explain分析sql语句，查看执行计划，分析索引是否用上，分析扫描行数等等
7. 查看mysql执行日志，看看是否有其他方面的问题

2. ¥MySQL抖动（SQL时快时慢）

InnoDB 在处理更新语句的时候，包括写入内存和写redo log磁盘操作。当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为“脏页”。

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 抖动的瞬间，可能就是在刷脏页（flush）。（无论是查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了更新语句，都可能是造成业务端感知到 MySQL“抖”了一下的原因）

触发flush的4种情况：

1. redo log写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写，擦掉部分对应的所有脏页都必须要flush 到磁盘上。（这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。）
2. 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。（这种情况其实是常态。）
3. MySQL 认为系统“空闲”的时候，只要有机会就刷一点“脏页”。
4. MySQL 正常关闭的情况

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

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

所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

刷脏页的速率 = Max(f(脏页比例),f(redolog写盘速度))✖️innodb_io_capacity

因此，为了避免抖动，应该合理地设置 innodb_io_capacity 的值（建议设置成磁盘的 IOPS），并且平时要多关注脏页比例，不要让它经常接近 75%。

3. 刷邻居

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

4. QPS突增的原因及解决方案

有时候由于业务突然出现高峰，或者应用程序 bug，或者缓存失效，导致某个语句的 QPS 突然暴涨，导致 MySQL 压力过大，影响服务。

最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

5. 短连接风暴的原因及解决方案

如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况

解决方案：

1. 先处理掉那些占着连接但是不工作的线程，并且应该优先断开事务外空闲的连接
2. 减少连接过程的消耗：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。但是有一定的安全风险。

6. I/O瓶颈的解决方案

1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2. 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3. 将innodb_flush_log_at_trx_commit 设置为 2(redo log 不 fsync)。这样做的风险是，主机掉电的时候会丢数据。

7. 怎么解决大事务

1. 把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
2. 通过SETMAX_EXECUTION_TIME命令， 来控制每个语句查询的最长时间，避免单个语句意外查询太长时间
3. 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill
4. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题
5. 设置innodb_undo_tablespaces值，将undo log分离到独立的表空间。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

8. SQL注入及预编译

SQL注入攻击是输入参数未经过滤，直接拼接到SQL语句当中解析，执行达到开发者预想之外行为的攻击方式。

PreprareStatement进行预编译，在程序运行时第一次操作数据库之前，SQL已经被数据库编译和解析，对应的执行计划也会缓存下来并以参数化的形式进行查询。当把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1’，数据库也只会将它作为一个参数值来处理而不会作为一个SQL指令，如此，就起到防止SQL注入的作用了。

SELECT * FROM article WHERE id = '-1 OR 1=1'

九. 其他实践问题

1. 如何复制一张表

• insert … select语句（但这种方式会给源表上锁）：
  如insert into t2(c,d) select c,d from t;
• 将数据写到外部文本文件，然后再写回目标表：

1. mysqldump 方法
2. 导出 CSV 文件 + load data
3. 物理拷贝方法：在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

这三种方法的优缺点：

1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：必须是全表拷贝，不能只拷贝部分数据；需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
2. 用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
3. 用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。