mysql 如何控制cpu_MySQL中如何控制SQL具有相同执行路径

最近有客户提出一个比较有意思的问题，生产环境与测试环境数据量相差比较大，导致两个环境中执行路径大不相同，如何能保证这两个环境执行计划相同呢？
这还是一个比较实际的需求，MySQL中没有绑定执行计划功能，并且有时候测试环境与生产环境执行路径不同，可能会引发线上的故障。
能想到的一种方式就是将MySQL中持久化统计信息表进行同步，这样达到两个环境执行路径相同

想法验证

验证方法的可行性，通过一个实验做验证，大致步骤如下:

1. ​表中插入10万条数据，执行一条查询，记录下执行计划
2. 查看mysql.innodb_index_stats中的统计信息，并导出
3. 删除表中数据
4. 手工收集一次统计信息，之后将innodb_stats_auto_recalc设置为off，为了防止之后自动收集统计信息
5. 插入5万条数据，执行一条查询，记录下执行计划
6. 导入步骤2中记录下来的统计信息后，再次查看执行计划，是否与步骤1中的相同

实验步骤

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();

1. 插入数据

mysql> call idata();
Query OK, 1 row affected (10 min 9.89 sec)

1. 查看统计信息及执行计划

将统计信息用mysqldump导出

./mysqldump --host=127.0.0.1--port=3322 --user=root --password=123456 --databases mysql --tables innodb_index_stats  --where "table_name = 't'"

1. 删除表数据，集统计信息，关闭统计信息自动收集

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; //关闭统计信息持久化收集

这时对应的统计信息如下:

1. 再次插入数据

mysql> call idata();
Query OK, 1 row affected (5 min 10.59 sec)

这时再执行相同的查询，就会看到走不到索引了:

1. 导入统计信息

将原有的统计信息删除，并导入备份出来的统计信息

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后还是会重新收集统计信息。