2023，这些MySQL相关面试题你不能不看！！！

5.1、MySQL 的事务隔离级别有哪些？分别主要⽤于解决脏读、不可重复读、幻读。

脏读：⼀个事务读取到另⼀个事务还未提交的数据。

不可重复读：在⼀个事务中多次读取同⼀个数据时，结果出现不⼀致。

幻读：在⼀个事务中使⽤相同的 SQL 两次读取，第⼆次读取到了其他事务新插⼊的⾏。

不可重复读注重于数据的修改，⽽幻读注重于数据的插⼊。⽤于解决什么问题？

4ba9c0499cccc881394c96f40aa268d.png5.2、MySQL 的可重复读怎么实现的？

使⽤ MVCC 实现的，即 Mutil-Version Concurrency Control，多版本并发控制。关于 MVCC，⽐较常⻅的说法如下，包括《⾼性能 MySQL》也是这么介绍的。

InnoDB 在每⾏记录后⾯保存两个隐藏的列，分别保存了数据⾏的创建版本号和删除版本号。每开始⼀个新的事务，系统版本号都会递增。事务开始时刻的版本号会作为事务的版本号，⽤来和查询到的每⾏记录的版本号对⽐。在可重复读级别下，MVCC是如何操作的：

SELECT：必须同时满⾜以下两个条件，才能查询到。

  • 1）只查版本号早于当前版本的数据⾏；

  • 2）⾏的删除版本要么未定义，要么⼤于当前事务版本号。

INSERT：为插⼊的每⼀⾏保存当前系统版本号作为创建版本号。

DELETE：为删除的每⼀⾏保存当前系统版本号作为删除版本号。

UPDATE：插⼊⼀条新数据，保存当前系统版本号作为创建版本号。同时保存当前系统版本号作为原来的数据⾏删除版本号。

MVCC 只作⽤于 RC（Read Committed）和 RR（Repeatable Read）级别，因为 RU（ReadUncommitted）总是读取最新的数据版本，⽽不是符合当前事务版本的数据⾏。⽽ Serializable 则会对所有读取的⾏都加锁。这两种级别都不需要 MVCC 的帮助。

5.3、MVCC 解决了幻读了没有？

幻读：在⼀个事务中使⽤相同的 SQL 两次读取，第⼆次读取到了其他事务新插⼊的⾏，则称为发⽣了幻读。

例如：

  • 1）事务1第⼀次查询：select * from user where id < 10 时查到了 id = 1 的数据

  • 2）事务2插⼊了 id = 2 的数据

  • 3）事务1使⽤同样的语句第⼆次查询时，查到了 id = 1、id = 2 的数据，出现了幻读。

谈到幻读，⾸先我们要引⼊“快照读”和“当前读”的概念：快照读：⽣成⼀个事务快照（ReadView），之后都从这个快照获取数据。普通 select 语句就是快照读。

当前读：读取数据的最新版本。常⻅的 update/insert/delete、还有 select ... for update、select ...lock in share mode 都是当前读。

对于快照读，MVCC 因为因为从 ReadView 读取，所以必然不会看到新插⼊的⾏，所以天然就解决了幻读的问题。

⽽对于当前读的幻读，MVCC 是⽆法解决的。需要使⽤ Gap Lock 或 Next-Key Lock（Gap Lock +Record Lock）来解决。

其实原理也很简单，⽤上⾯的例⼦稍微修改下以触发当前读：select * from user where id < 10 forupdate，当使⽤了 Gap Lock 时，Gap 锁会锁住 id < 10 的整个范围，因此其他事务⽆法插⼊ id < 10的数据，从⽽防⽌了幻读。

5.4、经常有⼈说 Repeatable Read 解决了幻读是什么情况？

SQL 标准中规定的 RR 并不能消除幻读，但是 MySQL 的 RR 可以，靠的就是 Gap 锁。在 RR 级别

下，Gap 锁是默认开启的，⽽在 RC 级别下，Gap 锁是关闭的。

5.5、什么是索引？

MySQL 官⽅对索引的定义为：索引（Index）是帮助 MySQL ⾼效获取数据的数据结构。简单的理解，索引类似于字典里面的目录。

5.6、常见的索引类型有哪些？

常⻅的索引类型有：hash、b树、b+树。

hash：底层就是 hash 表。进⾏查找时，根据 key 调⽤hash 函数获得对应的 hashcode，根据hashcode 找到对应的数据⾏地址，根据地址拿到对应的数据。

B树：B树是⼀种多路搜索树，n 路搜索树代表每个节点最多有 n 个⼦节点。每个节点存储 key + 指向下⼀层节点的指针+ 指向 key 数据记录的地址。查找时，从根结点向下进⾏查找，直到找到对应的key。

B+树：B+树是b树的变种，主要区别在于：B+树的⾮叶⼦节点只存储 key + 指向下⼀层节点的指针。

另外，B+树的叶⼦节点之间通过指针来连接，构成⼀个有序链表，因此对整棵树的遍历只需要⼀次线性遍历叶⼦结点即可。

f9ba679a68d61aa70856bd1f695115d.png5.7、为什么MySQL数据库要⽤B+树存储索引？⽽不⽤红⿊树、B树、Hash？

红⿊树：如果在内存中，红⿊树的查找效率⽐B树更⾼，但是涉及到磁盘操作，B树就更优了。因为红⿊树是⼆叉树，数据量⼤时树的层数很⾼，从树的根结点向下寻找的过程，每读1个节点，都相当于⼀次IO操作，因此红⿊树的I/O操作会⽐B树多的多。

hash 索引：如果只查询单个值的话，hash 索引的效率⾮常⾼。但是 hash 索引有⼏个问题：

1）不⽀持范围查询；

2）不⽀持索引值的排序操作；

3）不⽀持联合索引的最左匹配规则。

B树索引：B树索相⽐于B+树，在进⾏范围查询时，需要做局部的中序遍历，可能要跨层访问，跨层访问代表着要进⾏额外的磁盘I/O操作；另外，B树的⾮叶⼦节点存放了数据记录的地址，会导致存放的节点更少，树的层数变⾼。

5.8、MySQL 中的索引叶子节点存放的是什么？

MyISAM和InnoDB都是采⽤的B+树作为索引结构，但是叶⼦节点的存储上有些不同。

MyISAM：主键索引和辅助索引（普通索引）的叶⼦节点都是存放 key 和 key 对应数据⾏的地址。在MyISAM 中，主键索引和辅助索引没有任何区别。

InnoDB：主键索引存放的是 key 和 key 对应的数据⾏。辅助索引存放的是 key 和 key 对应的主键值。因此在使⽤辅助索引时，通常需要检索两次索引，⾸先检索辅助索引获得主键值，然后⽤主键值到主键索引中检索获得记录。

5.9、什么是聚簇索引（聚集索引）？

聚簇索引并不是⼀种单独的索引类型，⽽是⼀种数据存储⽅式。聚簇索引将索引和数据⾏放到了⼀块，找到索引也就找到了数据。因为⽆需进⾏回表操作，所以效率很⾼。

InnoDB 中必然会有，且只会有⼀个聚簇索引。通常是主键，如果没有主键，则优先选择⾮空的唯⼀索引，如果唯⼀索引也没有，则会创建⼀个隐藏的row_id 作为聚簇索引。⾄于为啥会只有⼀个聚簇索引，其实很简单，因为我们的数据只会存储⼀份。

⽽⾮聚簇索引则将数据存储和索引分开，找到索引后，需要通过对应的地址找到对应的数据⾏。MyISAM 的索引⽅式就是⾮聚簇索引。

5.10、什么是回表查询？

InnoDB 中，对于主键索引，只需要⾛⼀遍主键索引的查询就能在叶⼦节点拿到数据。

⽽对于普通索引，叶⼦节点存储的是 key + 主键值，因此需要再⾛⼀次主键索引，通过主键索引找到

⾏记录，这就是所谓的回表查询，先定位主键值，再定位⾏记录。

5.11、⾛普通索引，⼀定会出现回表查询吗？

不⼀定，如果查询语句所要求的字段全部命中了索引，那么就不必再进⾏回表查询。

很容易理解，有⼀个 user 表，主键为 id，name 为普通索引，则再执⾏：select id, name from userwhere name = 'joonwhee' 时，通过name 的索引就能拿到 id 和 name了，因此⽆需再回表去查数据⾏了。

5.12、那你知道什么是覆盖索引（索引覆盖）吗？

覆盖索引是 SQL-Server 中的⼀种说法，上⾯讲的例⼦其实就实现了覆盖索引。具体的：当索引上包含了查询语句中的所有列时，我们⽆需进⾏回表查询就能拿到所有的请求数据，因此速度会很快。

假设你定义⼀个联合索引：

🏆 CREATE INDEX idx_name_age ON user(name,age);

查询名称为 joon 的年龄：

🏆 select name,age from user where name = 'joon';

上述语句中，查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。

5.13、联合索引（复合索引）的底层实现？最佳左前缀原则？

联合索引底层还是使⽤B+树索引，并且还是只有⼀棵树，只是此时的排序会：⾸先按照第⼀个索引排

序，在第⼀个索引相同的情况下，再按第⼆个索引排序，依次类推。

这也是为什么有“最佳左前缀原则”的原因，因为右边（后⾯）的索引都是在左边（前⾯）的索引排

序的基础上进⾏排序的，如果没有左边的索引，单独看右边的索引，其实是⽆序的。

还是以字典为例，我们如果要查第2个字⺟为 k 的，通过⽬录是⽆法快速找的，因为⾸字⺟ A - Z ⾥⾯

都可能包含第2个字⺟为 k 的。

5.14、union 和 union all 的区别

union all：对两个结果集直接进⾏并集操作，记录可能有重复，不会进⾏排序。

union：对两个结果集进⾏并集操作，会进⾏去重，记录不会重复，按字段的默认规则排序。因此，从效率上说，UNION ALL 要⽐ UNION 更快。

5.15、B+树中⼀个节点到底多⼤合适？

1⻚或⻚的倍数最为合适。因为如果⼀个节点的⼤⼩⼩于1⻚，那么读取这个节点的时候其实也会读出1⻚，造成资源的浪费。所以为了不造成浪费，所以把⼀个节点的⼤⼩控制在1⻚、2⻚、3⻚等倍数⻚⼤⼩最为合适。

这⾥说的“⻚”是 MySQL ⾃定义的单位（和操作系统类似），MySQL 的 Innodb 引擎中1⻚的默认⼤⼩是16k，可以使⽤命令SHOW GLOBAL STATUS LIKE 'Innodb_page_size' 查看。

1a7c3516d1decb159e08208fcac91f0.png5.16、那 MySQL 中B+树的⼀个节点⼤⼩为多⼤呢？

在 MySQL 中 B+ 树的⼀个节点⼤⼩为“1⻚”，也就是16k。

5.17、什么⼀个节点为1页就够了？

Innodb中，B+树中的⼀个节点存储的内容是：

• ⾮叶⼦节点：key + 指针叶⼦节点：数据⾏（key 通常是数据的主键）

对于叶⼦节点：我们假设1⾏数据⼤⼩为1k（对于普通业务绝对够了），那么1⻚能存16条数据

对于⾮叶⼦节点：key 使⽤ bigint 则为8字节，指针在 MySQL 中为6字节，⼀共是14字节，则16k

能存放 16 1024 / 14 = 1170个。那么⼀颗⾼度为3的B+树能存储的数据为：1170 1170 * 16

= 21902400（千万级）

所以在 InnoDB 中B+树⾼度⼀般为3层时，就能满⾜千万级的数据存储。在查找数据时⼀次⻚的查找代表⼀次IO，所以通过主键索引查询通常只需要1-3次 IO 操作即可查找到数据。千万级别对于⼀般的业务来说已经⾜够了，所以⼀个节点为1⻚，也就是16k是⽐较合理的。

5.18、什么是 Buffer Pool？

Buffer Pool 是 InnoDB 维护的⼀个缓存区域，⽤来缓存数据和索引在内存中，主要⽤来加速数据的读

写，如果 Buffer Pool 越⼤，那么 MySQL 就越像⼀个内存数据库，默认⼤⼩为 128M。

InnoDB 会将那些热点数据和⼀些 InnoDB 认为即将访问到的数据存在 Buffer Pool 中，以提升数据

的读取性能。

InnoDB 在修改数据时，如果数据的⻚在 Buffer Pool 中，则会直接修改 Buffer Pool，此时我们称这

个⻚为脏⻚，InnoDB 会以⼀定的频率将脏⻚刷新到磁盘，这样可以尽量减少磁盘I/O，提升性能。

5.19、InnoDB 四⼤特性知道吗？

插⼊缓冲（

insert buffer）：

索引是存储在磁盘上的，所以对于索引的操作需要涉及磁盘操作。如果我们使⽤⾃增主键，那么在插

⼊主键索引（聚簇索引）时，只需不断追加即可，不需要磁盘的随机 I/O。但是如果我们使⽤的是普

通索引，⼤概率是⽆序的，此时就涉及到磁盘的随机 I/O，⽽随机I/O的性能是⽐较差的（Kafka 官⽅

数据：磁盘顺序I/O的性能是磁盘随机I/O的4000~5000倍）。

因此，InnoDB 存储引擎设计了 Insert Buffer ，对于⾮聚集索引的插⼊或更新操作，不是每⼀次直接

插⼊到索引⻚中，⽽是先判断插⼊的⾮聚集索引⻚是否在缓冲池（

Buffer pool）中，若在，则直接插

⼊；若不在，则先放⼊到⼀个 Insert Buffer 对象中，然后再以⼀定的频率和情况进⾏ Insert Buffer

和辅助索引⻚⼦节点的 merge（合并）操作，这时通常能将多个插⼊合并到⼀个操作中（因为在⼀个

索引⻚中），这就⼤⼤提⾼了对于⾮聚集索引插⼊的性能。

插⼊缓冲的使⽤需要满⾜以下两个条件：1）索引是辅助索引；2）索引不是唯⼀的。

因为在插⼊缓冲时，数据库不会去查找索引⻚来判断插⼊的记录的唯⼀性。如果去查找肯定⼜会有随

机读取的情况发⽣，从⽽导致 Insert Buffer 失去了意义。

⼆次写（

double write）：

脏⻚刷盘⻛险：InnoDB 的 page size⼀般是16KB，操作系统写⽂件是以4KB作为单位，那么每写⼀

个 InnoDB 的 page 到磁盘上，操作系统需要写4个块。于是可能出现16K的数据，写⼊4K 时，发⽣

了系统断电或系统崩溃，只有⼀部分写是成功的，这就是 partial page write（部分⻚写⼊）问题。

这时会出现数据不完整的问题。

这时是⽆法通过 redo log 恢复的，因为 redo log 记录的是对⻚的物理修改，如果⻚本⾝已经损坏，

重做⽇志也⽆能为⼒。

doublewrite 就是⽤来解决该问题的。doublewrite 由两部分组成，⼀部分为内存中的 doublewrite

buffer，其⼤⼩为2MB，另⼀部分是磁盘上共享表空间中连续的128个⻚，即2个区(extent)，⼤⼩也

是2M。

为了解决 partial page write 问题，当 MySQL 将脏数据刷新到磁盘的时候，会进⾏以下操作：

1）先将脏数据复制到内存中的 doublewrite buffer

2）之后通过 doublewrite buffer 再分2次，每次1MB写⼊到共享表空间的磁盘上（顺序写，性能很

⾼）

3）完成第⼆步之后，⻢上调⽤ fsync 函数，将doublewrite buffer中的脏⻚数据写⼊实际的各个表空

间⽂件（离散写）。

如果操作系统在将⻚写⼊磁盘的过程中发⽣崩溃，InnoDB 再次启动后，发现了⼀个 page 数据已经

损坏，InnoDB 存储引擎可以从共享表空间的 doublewrite 中找到该⻚的⼀个最近的副本，⽤于进⾏

数据恢复了。

⾃适应哈

希索引(adaptive hash index）：

哈希（

hash）是⼀种⾮常快的查找⽅法，⼀般情况下查找的时间复杂度为

O(1)。但是由于不⽀持范

围查询等条件的限制，InnoDB 并没有采⽤ hash 索引，但是如果能在⼀些特殊场景下使⽤ hash 索

引，则可能是⼀个不错的补充，⽽ InnoDB 正是这么做的。

具体的，InnoDB 会监控对表上索引的查找，如果观察到某些索引被频繁访问，索引成为热数据，建

⽴哈希索引可以带来速度的提升，则建⽴哈希索引，所以称之为⾃适应（

adaptive）的。⾃适应哈希

索引通过缓冲池的 B+ 树构造⽽来，因此建⽴的速度很快。⽽且不需要将整个表都建哈希索引，

InnoDB 会⾃动根据访问的频率和模式来为某些⻚建⽴哈希索引。

预读（

read ahead）：

InnoDB 在 I/O 的优化上有个⽐较重要的特性为预读，当 InnoDB 预计某些 page 可能很快就会需要⽤

到时，它会异步地将这些 page 提前读取到缓冲池（

buffer pool）中，这其实有点像空间局部性的概

念。

空间局部性（

spatial locality）：如果⼀个数据项被访问，那么与它地址相邻的数据项也可能很快被

访问。

InnoDB使⽤两种预读算法来提⾼I/O性能：线性预读（

linear read-ahead）和随机预读

（

randomread-ahead）。

其中，线性预读以 extent（块，1个 extent 等于64个 page）为单位，⽽随机预读放到以 extent 中的

page 为单位。线性预读着眼于将下⼀个extent 提前读取到 buffer pool 中，⽽随机预读着眼于将当前

extent 中的剩余的 page 提前读取到 buffer pool 中。

线性预读（

Linear read-ahead）：线性预读⽅式有⼀个很重要的变量

innodb_read_ahead_threshold，可以控制 Innodb 执⾏预读操作的触发阈值。如果⼀个 extent 中

的被顺序读取的 page 超过或者等于该参数变量时，Innodb将会异步的将下⼀个 extent 读取到

buffer pool中，innodb_read_ahead_threshold 可以设置为0-64（⼀个 extend 上限就是64⻚）的

任何值，默认值为56，值越⾼，访问模式检查越严格。

随机预读（

Random read-ahead）: 随机预读⽅式则是表⽰当同⼀个 extent 中的⼀些 page 在 buffer

pool 中发现时，Innodb 会将该 extent 中的剩余 page ⼀并读到 buffer pool中，由于随机预读⽅式

给 Innodb code 带来了⼀些不必要的复杂性，同时在性能也存在不稳定性，在6.5中已经将这种预读

⽅式废弃。要启⽤此功能，请将配置变量设置 innodb_random_read_ahead 为ON。

5.20、请说⼀下共享锁和排他锁？

共享锁⼜称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同⼀数据可以共享⼀把锁，都能

访问到数据，但是只能读不能修改。

排他锁⼜称为写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如⼀个事务获取了⼀个数

据⾏的排他锁，其他事务就不能再获取该⾏的其他锁，包括共享锁和排他锁，但是获取排他锁的事务

可以对数据就⾏读取和修改

常⻅的⼏种 SQL 语句的加锁情况如下：

🏆 select * from table; #不加锁

update/insert/delete #排他锁

select * from table where id = 1 for update; #id为索引，加排他锁

select * from table where id = 1 lock in share mode; #id为索引，加共享锁

5.21、请说⼀下数据库的⾏锁和表锁？

⾏锁：操作时只锁某⼀（些）⾏，不对其它⾏有影响。开销⼤，加锁慢；会出现死锁；锁定粒度⼩，

表锁：即使操作⼀条记录也会锁住整个表。开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发⽣锁冲突概率⾼，并发度最低。

⻚锁：操作时锁住⼀⻚数据（16kb）。开销和加锁速度介于表锁和⾏锁之间；会出现死锁；锁定粒度介于表锁和⾏锁之间，并发度⼀般。InnoDB 有⾏锁和表锁，MyIsam 只有表锁。

5.22、InnoDB 的⾏锁是怎么实现的？

InnoDB ⾏锁是通过索引上的索引项来实现的。意味者：只有通过索引条件检索数据，InnoDB 才会使⽤⾏级锁，否则，InnoDB将使⽤表锁！

对于主键索引：直接锁住锁住主键索引即可。

对于普通索引：先锁住普通索引，接着锁住主键索引，这是因为⼀张表的索引可能存在多个，通过主键索引才能确保锁是唯⼀的，不然如果同时有2个事务对同1条数据的不同索引分别加锁，那就可能存在2个事务同时操作⼀条数据了。

5.23、InnoDB 锁的算法有哪⼏种？

Record lock：记录锁，单条索引记录上加锁，锁住的永远是索引，⽽⾮记录本⾝。

Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某⼀条索引记录之前或者之后加锁，并不包括该索引记录本⾝。

Next-key lock：Record lock 和 Gap lock 的结合，即除了锁住记录本⾝，也锁住索引之间的间隙。

5.24、MySQL 如何实现悲观锁和乐观锁？

乐观锁：更新时带上版本号（cas更新）

悲观锁：使⽤共享锁和排它锁，select...lock in share mode，selectRfor update。

5.25、InnoDB 和 MyISAM 的区别？

43a039fb35e8e41cd1943b636f16d63.png

5.26、存储引擎的选择？

没有特殊情况，使⽤ InnoDB 即可。如果表中绝⼤多数都只是读查询，可以考虑 MyISAM。

5.27、explain ⽤过吗，有哪些字段分别是啥意思？

explain 字段有：

• id：标识符

• select_type：查询的类型

• table：输出结果集的表

• partitions：匹配的分区

• type：表的连接类型

• possible_keys：查询时，可能使⽤的索引

• key：实际使⽤的索引

• key_len：使⽤的索引字段的⻓度

• ref：列与索引的⽐较

• rows：估计要检查的⾏数

• filtered：按表条件过滤的⾏百分⽐

• Extra：附加信息

5.28、explain 主要关注哪些字段？

主要关注 type、key、row、extra 等字段。主要是看是否使⽤了索引，是否扫描了过多的⾏数，是否出现 Using temporary、Using filesort 等⼀些影响性能的主要指标。

5.29、type 中有哪些常见的值？

按类型排序，从好到坏，常⻅的有：const > eq_ref > ref > range > index > ALL。

const：通过主键或唯⼀键查询，并且结果只有1⾏（也就是⽤等号查询）。因为仅有⼀⾏，所以优化器的其余部分可以将这⼀⾏中的列值视为常量。

•eq_ref：通常出现于两表关联查询时，使⽤主键或者⾮空唯⼀键关联，并且查询条件不是主键或唯⼀键的等号查询。

• ref：通过普通索引查询，并且使⽤的等号查询。range：索引的范围查找（>=、<、in 等）。

• index：全索引扫描。

• All：全表扫描

5.30、如何做慢 SQL 优化？

首先要搞明⽩慢的原因是什么：是查询条件没有命中索引？还是 load 了不需要的数据列？还是数据量太⼤？所以优化也是针对这三个⽅向来的。首先⽤ explain 分析语句的执⾏计划，查看使⽤索引的情况，是不是查询没⾛索引，如果可以加索引解决，优先采⽤加索引解决。

•分析语句，看看是否存在⼀些导致索引失效的⽤法，是否 load 了额外的数据，是否加载了许多结果中并不需要的列，对语句进⾏分析以及重写。

•如果对语句的优化已经⽆法进⾏，可以考虑表中的数据量是否太⼤，如果是的话可以进⾏垂直拆分或者⽔平拆分。

5.31、说说 MySQL 的主从复制？

MySQL主从复制涉及到三个线程，⼀个运⾏在主节点（Log Dump Thread），其余两个（I/OThread，SQL Thread）运⾏在从节点，如下图所⽰

24e6d89412c9df345533b18db6c0ce7.png

主从复制默认是异步的模式，具体过程如下。

1）从节点上的I/O 线程连接主节点，并请求从指定⽇志⽂件（bin log file）的指定位置（bin logposition，或者从最开始的⽇志）之后的⽇志内容；

2）主节点接收到来⾃从节点的 I/O请求后，读取指定⽂件的指定位置之后的⽇志信息，返回给从节点。返回信息中除了⽇志所包含的信息之外，还包括本次返回的信息的 bin-log file 以及 bin-logposition；从节点的 I/O 进程接收到内容后，将接收到的⽇志内容更新到 relay log 中，并将读取到的典bin log file（⽂件名）和position（位置）保存到 master-info ⽂件中，以便在下⼀次读取的时候能够清楚的告诉 Master “我需要从某个bin-log 的个位置开始往后的⽇志内容”；

3）从节点的 SQL 线程检测到 relay-log 中新增加了内容后，会解析 relay-log 的内容，并在本数据库中执⾏。

5.32、异步复制，主库宕机后，数据可能丢失？

可以使⽤半同步复制或全同步复制。

半同步复制：

修改语句写⼊bin log后，不会⽴即给客⼾端返回结果。⽽是⾸先通过log dump 线程将 binlog 发送给从节点，从节点的 I/O 线程收到 binlog 后，写⼊到 relay log，然后返回 ACK 给主节点，主节点收到ACK 后，再返回给客⼾端成功。

92299650930771b488bd003bbe447b8.png

半同步复制的特点：

• 确保事务提交后 binlog ⾄少传输到⼀个从库

• 不保证从库应⽤完这个事务的 binlog

• 性能有⼀定的降低，响应时间会更⻓

• ⽹络异常或从库宕机，卡主主库，直到超时或从库恢复

全同步复制：主节点和所有从节点全部执⾏了该事务并确认才会向客⼾端返回成功。因为需要等待所有从库执⾏完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

5.33、主库写压⼒⼤，从库复制很可能出现延迟？

可以使⽤并⾏复制（并⾏是指从库多个SQL线程并⾏执⾏ relay log），解决从库复制延迟的问题。MySQL 6.7 中引⼊基于组提交的并⾏复制，其核⼼思想：⼀个组提交的事务都是可以并⾏回放，因为这些事务都已进⼊到事务的 prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）。判断事务是否处于⼀个组是通过 last_committed 变量，last_committed 表⽰事务提交的时候，上次事务提交的编号，如果事务具有相同的 last_committed，则表⽰这些事务都在⼀组内，可以进⾏并⾏的回放。

5.34、msyql优化经验

48a0ebfb1f55d31d59a216f5d8cba12.png5.35、mysql的语句优化；

1 .对查询进⾏优化，应尽量避免全表扫描，⾸先应考虑在 where 及 order by 涉及的列上建⽴索
引。
2 .应尽量避免在 where ⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描。
3 .应尽量避免在 where ⼦句中对字段进⾏ null 值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表
扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
4 .应尽量避免在 where ⼦句中使⽤ or 来连接条件，否则将导致引擎放弃使⽤索引⽽进⾏全表扫
描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5 .下⾯的查询也将导致全表扫描：
select id from t where name like '%abc%'
若要提⾼效率，可以考虑全⽂检索。
6 .in 和 not in 也要慎⽤，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能⽤ between 就不要⽤ in 了：
select id from t where num between 1 and 3
7 .如果在 where ⼦句中使⽤参数，也会导致全表扫描。因为SQL只有在运⾏时才会解析局部变量，但
优化程序不能将访问计划的选择推迟到运⾏时；它必须在编译时进⾏选择。然⽽，如果在编译时建⽴访
问计划，变量的值还是未知的，因⽽⽆法作为索引选择的输⼊项。如下⾯语句将进⾏全表扫描：
select id from t where num=@num
可以改为强制查询使⽤索引：
select id from t with(index(索引名)) where num=@num
8 .应尽量避免在 where ⼦句中对字段进⾏表达式操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫
描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9 .应尽量避免在where⼦句中对字段进⾏函数操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。
如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'⽣成
的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10 .不要在 where ⼦句中的“=”左边进⾏函数、算术运算或其他表达式运算，否则系统将可能⽆法正
确使⽤索引。
11 .在使⽤索引字段作为条件时，如果该索引是复合索引，那么必须使⽤到该索引中的第⼀个字段作为
条件时才能保证系统使⽤该索引，否则该索引将不会被使⽤，并且应尽可能的让字段顺序与索引顺序相
⼀致。
12 .不要写⼀些没有意义的查询，如需要⽣成⼀个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
13 .很多时候⽤ exists 代替 in 是⼀个好的选择：
select num from a where num in(select num from b)
⽤下⾯的语句替换：
select num from a where exists(select 1 from b where num=a.num)
14 .并不是所有索引对查询都有效，SQL是根据表中数据来进⾏查询优化的，当索引列有⼤量数据重复
时，SQL查询可能不会去利⽤索引，如⼀表中有字段sex，male、female⼏乎各⼀半，那么即使在sex
上建了索引也对查询效率起不了作⽤。
15 .索引并不是越多越好，索引固然可以提⾼相应的 select 的效率，但同时也降低了 insert 及
update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，
视具体情况⽽定。⼀个表的索引数最好不要超过6个，若太多则应考虑⼀些不常使⽤到的列上建的索引
是否有必要。
16 .应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录
的物理存储顺序，⼀旦该列值改变将导致整个表记录的顺序的调整，会耗费相当⼤的资源。若应⽤系统
需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17 .尽量使⽤数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性
能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个⽐较字符串中每⼀个字符，⽽对于数
字型⽽⾔只需要⽐较⼀次就够了。
18 .尽可能的使⽤ varchar/nvarchar 代替 char/nchar ，因为⾸先变⻓字段存储空间⼩，可以节
省存储空间，其次对于查询来说，在⼀个相对较⼩的字段内搜索效率显然要⾼些。
19 .任何地⽅都不要使⽤ select * from t ，⽤具体的字段列表代替“*”，不要返回⽤不到的任何
字段。
20 .尽量使⽤表变量来代替临时表。如果表变量包含⼤量数据，请注意索引⾮常有限（只有主键索
引）。
21 .避免频繁创建和删除临时表，以减少系统表资源的消耗。
22 .临时表并不是不可使⽤，适当地使⽤它们可以使某些例程更有效，例如，当需要重复引⽤⼤型表或
常⽤表中的某个数据集时。但是，对于⼀次性事件，最好使⽤导出表。
23 .在新建临时表时，如果⼀次性插⼊数据量很⼤，那么可以使⽤ select into 代替 create tab
le，避免造成⼤量 log ，以提⾼速度；如果数据量不⼤，为了缓和系统表的资源，应先create tabl
e，然后insert。
24 .如果使⽤到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，
然后 drop table ，这样可以避免系统表的较⻓时间锁定。
25 .尽量避免使⽤游标，因为游标的效率较差，如果游标操作的数据超过1万⾏，那么就应该考虑改
写。
26 .使⽤基于游标的⽅法或临时表⽅法之前，应先寻找基于集的解决⽅案来解决问题，基于集的⽅法通
常更有效。
27 .与临时表⼀样，游标并不是不可使⽤。对⼩型数据集使⽤ FAST_FORWARD 游标通常要优于其他逐
⾏处理⽅法，尤其是在必须引⽤⼏个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要⽐
使⽤游标执⾏的速度快。如果开发时间允许，基于游标的⽅法和基于集的⽅法都可以尝试⼀下，看哪⼀
种⽅法的效果更好。
28 .在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OF
F 。⽆需在执⾏存储过程和触发器的每个语句后向客⼾端发送 DONE_IN_PROC 消息。
29 .尽量避免向客⼾端返回⼤数据量，若数据量过⼤，应该考虑相应需求是否合理。
30 .尽量避免⼤事务操作，提⾼系统并发能⼒