《MySQL大智小技》2021版_部分3
5墨值下载
第 3 章 技术分享
285
3.26 Xtrabackup 不备份 binlog 怎么保证一致性？
作者：胡呈清
公司大佬出的考核题中有个有意思的问题：
已知：MySQL 的内部两阶段提交，是为了解决 binlog 和 redo log 的一致性（在 crash recovery 的过
程中, 如果发现某个事务的 redo log 已经完成 prepare 阶段, 但未完成 commit，那么会验证该事务是否
在 binlog 中，如存在，则进行提交，否则进行回滚）。
又已知：Xtrabackup 在恢复备份后，会进行类似于 crash recovery 的动作(将备份的 redo log 的内容回
放到数据中, 并对事务进行提交/回滚)，那么 Xtrabackup 为什么不需要备份 binlog 文件？
想了一下好像不是能一句话说清楚的，我来尝试解答下。
答：
备份时全局锁阶段做的操作：
2020-08-17T09:58:36.167905+08:00 2116 Query FLUSH TABLES WITH READ LOCK
2020-08-17T09:58:36.490928+08:00 2116 Query SHOW VARIABLES
2020-08-17T09:58:36.498670+08:00 2116 Query SHOW SLAVE STATUS
2020-08-17T09:58:36.499435+08:00 2116 Query SHOW MASTER STATUS
2020-08-17T09:58:36.499747+08:00 2116 Query SHOW VARIABLES
2020-08-17T09:58:36.503341+08:00 2116 Query FLUSH NO_WRITE_TO_BINLOG ENGINE
LOGS
2020-08-17T09:58:36.704273+08:00 2116 Query UNLOCK TABLES
这里最主要是要保证两点：
非事务数据之间一致性；
数据和 binlog 位点的一致性。
其中 FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS，是防止 innodb_flush_log_at_trx_commit 不等
于 1，redo log 没有刷到磁盘。而 SHOW MASTER STATUS 则是为了获取 binlog 位点。
关键在：xtrabackup 只需要保证“数据和 binlog 位点的一致”，而不是“数据和 binlog 的一致”。
crash recovery 过程要保证“数据和 binlog 的一致”，因为 crash 后不能出现事务重做提交而 binlog 没记
录的情况，这样会导致从库丢失数据。那备份需要考虑的是什么？有两点：
备份恢复后，再基于 binlog 做精确恢复时，--start-position 的位置是正确的，不会重放、漏掉事
物；
3.27 为什么 SELECT 查询选择全表扫描，而不走索引？
286
备份恢复后，作为从库向主库复制数据时，复制起始位置是正确的，不会重放、漏掉事物。
其实这两点都一样，就是要保证备份时 “数据和 binlog 位点的一致”。xtrabackup 是怎么实现的呢？
首先我们得知道事务二阶段提交过程中的 3 个队列 flush、sync、commit 都有排他锁，一个大事务 comm
it 可能需要几秒钟，那么此时执行 FTWRL 是会被阻塞的，commit 结束后才能取得全局锁，取得全局
锁后，执行 commit 会被阻塞：
这保证了 xtrabackup 备份的 redo log 中只有两种事务：已经完成提交的，和还没开始提交的（未执行
commit 的事务可能被后台线程刷盘），不会出现 prepare 状态的事务。另外还有一个知识点：GTID 的
生成和写 binlog 缓存是在二阶段提交的 binlog flush 阶段做的。结合起来则说明：FTWRL 后 执行 sho
w master status 获取 binlog 位点，只有完成提交的事务才会在其中，所以这保证了 binlog 位点和 binlo
g 的一致。
所以在 xtrabackup 的恢复过程，不需要处理 prepare 状态的事务，也就不需要再验证该事务是否在 binl
og 中了。
3.27 为什么 SELECT 查询选择全表扫描，而不走索引？
作者：晏泽
1、基本结论
SQL 的执行成本（cost）是 MySQL 优化器选择 SQL 执行计划时一个重要考量因素。当优化器认为使
用索引的成本高于全表扫描的时候，优化器将会选择全表扫描，而不是使用索引。下面通过一个实验来
说明。
2、问题现象
如下结构的一张表，表中约有 104w 行数据：
CREATE TABLE `test03` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
第 3 章 技术分享
287
`dept` tinyint(4) NOT NULL COMMENT '部门 id',
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
`create_time` datetime NOT NULL COMMENT '注册时间',
`last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
PRIMARY KEY (`id`),
KEY `ct_index` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1048577 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMM
ENT='测试表'
查询 1，并未用到 ct_index(create_time) 索引:
type 为 ALL ，而不是 range
rows 行数和全表行数接近
# 查询 1
mysql> explain select * from test03 where create_time > '2021-10-01 02:04:36';
+----+-------------+--------+------------+------+---------------+------+---------+-----
-+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
| rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+-----
-+---------+----------+-------------+
| 1 | SIMPLE | test03 | NULL | ALL | ct_index | NULL | NULL | NULL |
1045955 | 50.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+-----
-+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
而查询 2，则用到了 ct_index(create_time) 索引:
# 查询 2
mysql> explain select * from test03 where create_time < '2021-01-01 02:04:36';
+----+-------------+--------+------------+-------+---------------+----------+---------
+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len |
ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------+---------
+------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | range | ct_index | ct_index | 5 |
NULL | 169 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+----------+---------
+------+------+----------+-----------------------+
3、获得 SQL 优化器处理信息
of 65
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文档被以下合辑收录
相关文档
评论