如何解决 RDS for MySQL 主库和只读库查询效率不一致问题

主库查询效率比只读慢，如何排查优化主库的查询效率？

主库和从库的执行计划不一致通常有如下原因：

1. 统计信息不准确。
2. 主库有大的事务导致回滚日志过大引起的一致性读慢。
3. 表空间碎片率导致的执行计划不准确。

1. 手动更新统计信息

您可以执行 Analyze table table_name 来手动更新统计信息。需要注意的是，请选择业务上合适的维护时间进行此操作。

2. 增加采样页面数

我们前面提到过参数 innodb_stats_persistent_sample_pages，此参数默认为 20，可以适当调大一些来解决统计信息不准确的问题。 同时，此参数可以在表级别进行设置，例如：

mysql> alter table test0407 STATS_SAMPLE_PAGES=100;;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table test0407;
+----------+------------------------
| Table    | Create Table                            
+----------+------------------------
| test0407 | CREATE TABLE `test0407` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 STATS_SAMPLE_PAGES=100 |
+----------+---------------------------------------------------+
1 row in set (0.04 sec)

3.查看存储引擎的 TRANSACTIONS 信息

mysql> SHOW ENGINE INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
History list length 20
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421540231206736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421540231205816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421540231220536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

1. History列表长度通常很低，通常小于几千，但是写量大的工作负载或长时间运行的事务可能会导致其增加。
2. 查看 TRANSACTION 是否有 ACTIVE 时间较长的是事务。

如果发现有长事务，需要联系业务进行确认是否合理。确认可以清理后，可以使用 KILL 语句终止事务后，执行 explain 命令验证效果。

4.查看表空间碎片率

可以使用 alter table 命令来进行表空间碎片的整理 建议在业务低峰进行操作，避免高并发场景下引起业务抖动。

mysql> alter table sbtest.sbtest4 engine = innodb;
Query OK, 0 rows affected (1.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果是 5.7 的版本，建议客户使用 RDS Online DDL 功能 进行表空间碎片整理

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows