问题现象
主库查询效率比只读慢,如何排查优化主库的查询效率?
问题分析
主库和从库的执行计划不一致通常有如下原因:
- 统计信息不准确。
- 主库有大的事务导致回滚日志过大引起的一致性读慢。
- 表空间碎片率导致的执行计划不准确。
解决方案
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)
- History列表长度通常很低,通常小于几千,但是写量大的工作负载或长时间运行的事务可能会导致其增加。
- 查看 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