随便写写
0%

MySQL中如何控制SQL在测试-生产环境具有相同的执行路径

Posted on Edited on

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

最近有客户提出一个比较有意思的问题，生产环境与测试环境数据量相差比较大，导致两个环境中执行路径大不相同，如何能保证这两个环境执行计划相同呢？

这还是一个比较实际的需求，MySQL中没有绑定执行计划功能，并且有时候测试环境与生产环境执行路径不同，可能会引发线上的故障。

能想到的一种方式就是将MySQL中持久化统计信息表进行同步，这样达到两个环境执行路径相同

想法验证

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

  1. 表中插入10万条数据，执行一条查询，记录下执行计划

  2. 查看mysql.innodb_index_stats中的统计信息，并导出

  3. 删除表中数据

  4. 手工收集一次统计信息，之后将innodb_stats_auto_recalc设置为off，为了防止之后自动收集统计信息

  5. 插入5万条数据，执行一条查询，记录下执行计划

  6. 导入步骤2中记录下来的统计信息后，再次查看执行计划，是否与步骤1中的相同

实验步骤

  1. 创建表及插入函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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. 插入数据
1
2
3
4
mysql> call idata();

Query OK, 1 row affected (10 min 9.89 sec)

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

将统计信息用mysqldump导出

1
./mysqldump --host=127.0.0.1--port=3322 --user=root --password=123456 --databases mysql --tables innodb_index_stats --where "table_name = 't'"
  1. 删除表数据，集统计信息，关闭统计信息自动收集
1
2
3
4
5
6
7
8
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. 再次插入数据
1
2
3
4
mysql> call idata();

Query OK, 1 row affected (5 min 10.59 sec)

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

  1. 导入统计信息

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

1
2
3
4
5
6
7
8
9
10
11
12
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后还是会重新收集统计信息。