最近有客户提出一个比较有意思的问题,生产环境与测试环境数据量相差比较大,导致两个环境中执行路径大不相同,如何能保证这两个环境执行计划相同呢?
这还是一个比较实际的需求,MySQL中没有绑定执行计划功能,并且有时候测试环境与生产环境执行路径不同,可能会引发线上的故障。
能想到的一种方式就是将MySQL中持久化统计信息表进行同步,这样达到两个环境执行路径相同
想法验证
验证方法的可行性,通过一个实验做验证,大致步骤如下:表中插入10万条数据,执行一条查询,记录下执行计划
查看mysql.innodb_index_stats中的统计信息,并导出
删除表中数据
手工收集一次统计信息,之后将innodb_stats_auto_recalc设置为off,为了防止之后自动收集统计信息
插入5万条数据,执行一条查询,记录下执行计划
导入步骤2中记录下来的统计信息后,再次查看执行计划,是否与步骤1中的相同
实验步骤创建表及插入函数
CREATE TABLE `t` ( `id` int(11) NOT NULL,`a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`)) ENGINE=InnoDB;
delimiter ;;
create procedure idata() begin
declare i int; set i=1; while(i<=100000)do
insert into t values(i, i, i);
set i=i+1; end while;
end;;
delimiter ;
call idata();插入数据
mysql> call idata();
Query OK, 1 row affected (10 min 9.89 sec)查看统计信息及执行计划
将统计信息用mysqldump导出
./mysqldump --host=127.0.0.1--port=3322 --user=root --password=123456 --databases mysql --tables innodb_index_stats --where "table_name = 't'"删除表数据,集统计信息,关闭统计信息自动收集
mysql> delete from t;
Query OK, 100000 rows affected (0.68 sec)
mysql> analyze table dhy.t;
mysql> set global innodb_stats_auto_recalc = off; //关闭统计信息持久化收集
这时对应的统计信息如下:再次插入数据
mysql> call idata();
Query OK, 1 row affected (5 min 10.59 sec)
这时再执行相同的查询,就会看到走不到索引了:导入统计信息
将原有的统计信息删除,并导入备份出来的统计信息
mysql> delete from innodb_index_stats where table_name = 't'; Query OK, 11 rows affected (0.01 sec)
mysql> INSERT INTO `innodb_index_stats` VALUES ('dhy','t','PRIMARY','2020-06-05 09:44:32','n_diff_pfx01',100256,20,'id'),('dhy','t','PRIMARY','2020-06-05 09:44:32','n_leaf_pages',208,NULL,'Number of leaf pages in the index'),('dhy','t','PRIMARY','2020-06-05 09:44:32','size',225,NULL,'Number of pages in the index'),('dhy','t','a','2020-06-05 09:44:32','n_diff_pfx01',100557,20,'a'),('dhy','t','a','2020-06-05 09:44:32','n_diff_pfx02',100710,20,'a,id'),('dhy','t','a','2020-06-05 09:44:32','n_leaf_pages',90,NULL,'Number of leaf pages in the index'),('dhy','t','a','2020-06-05 09:44:32','size',97,NULL,'Number of pages in the index'),('dhy','t','b','2020-06-05 09:44:32','n_diff_pfx01',100710,20,'b'),('dhy','t','b','2020-06-05 09:44:32','n_diff_pfx02',100557,20,'b,id'),('dhy','t','b','2020-06-05 09:44:32','n_leaf_pages',90,NULL,'Number of leaf pages in the index'),('dhy','t','b','2020-06-05 09:44:32','size',97,NULL,'Number of pages in the index');
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
同时需要把innodb_table_stats表中的n_rows修改为10万
mysql> update innodb_table_stats set n_rows = 100000 where table_name = 't';
导入之后直接执行语句,还是走不到索引,通过show index from t;可以看到索引的统计信息还没有更新,需要执行一次flush table(线上谨慎执行或者不建议操作),将统计信息从持久化磁盘上刷新到内存中。
flush table 前:
flsuh table 后:
看到这里已经走到了索引,可以达到我们想要的结果了。
总结
通过更新Innodb两张持久化统计信息表,能够达到测试环境与生产环境具有相同的执行路径,需要注意的是,当再次analyze后还是会重新收集统计信息。