SQL调优怎么生成海量测试数据

小编给大家分享一下SQL调优怎么生成海量测试数据，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

场景，如果出现慢SQL，需要DBA加索引优化，怎么知道加的索引是有效的呢？这需要一遍遍的试验和调整，总不能直接拿线上的数据库测试吧，一般方法是在测试环境建立测试表，然后从线上的从库拷贝一些数据进测试环境，接着再进行加索引和explain

但有时候，导出的数据量少，执行计划看不出效果，导出数据量多，又会冲刷线上机器的buffer pool和影响IO，如果有个工具能够直接生成数据就好了，生成跟线上一样的100万，或者1000万就好了

以前sysbench压力测试，有一个生成数据的功能，生成100万数据是这样的

sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=localhost \--mysql-password=test prepare

但它生成表结构是固定的，进行压力测试的SQL语句也是固定的,无法调试线上的SQL语句

CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL auto_increment, `k` int(10) unsigned NOT NULL default '0', `c` char(120) NOT NULL default '', `pad` char(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `k` (`k`));

能否有一个创建用户自定义的表结构，并且对这个表结构生成上百千万数据的工具呢？有一个叫datagen的工具，链接在文章末尾

drwxr-xr-x. 2 root mysql     4096 Sep 27  2016 bizsqldrwxr-xr-x. 2 root mysql     4096 May 31 20:51 conf-rw-r--r--. 1 root mysql 23698092 Sep 27  2016 datagen.jar-rwxr-xr-x. 1 root mysql      147 Sep 27  2016 datagen.sh-rw-rw-r--. 1 root mysql    31599 May 31 20:54 envbuilder.log-rw-r--r--. 1 root mysql     1741 May 31 20:53 example.schema-rw-r--r--. 1 root mysql     1336 May 31 09:42 example.schema_backup-rw-r--r--. 1 root mysql     2062 Sep 27  2016 readme

方法很简单的2步，把你想要的表结构和想要生成多少条数据，写入到example.schema文件，比如这样，如果想要生成100万条数据，在表末尾加入注释/*{RC{1000000}}*/

CREATE TABLE `test`.`tbl_test` (`post_id` BIGINT(20) DEFAULT '0'  ,`star` INTEGER(10) DEFAULT '0'  ,`view_count` INTEGER(11) DEFAULT '0'  ,`bean` INTEGER(11) DEFAULT '0'  ,`nearby` INTEGER(11) DEFAULT '0'  ,PRIMARY KEY (post_id) ,INDEX (poster_uid)) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 /*{RC{1000000}}*/;

第2步，填写连接测试数据库的账号密码，只需要加入一行

vi conf/datagen.xml                                                                                                                              

接着运行shell脚本，往测试库建表，插入数据

[root@localhost datagen]# /bin/bash datagen.sh

[2017-05-31 08:53:15][WARN ] [DataGen :184] - Parsing ddl...[2017-05-31 08:53:15][WARN ] [DataGen :187] - Creating table...[2017-05-31 08:53:15][WARN ] [MultiThreadPrepareDataComparator:508] - Preparing generators...[2017-05-31 08:53:15][WARN ] [MultiThreadPrepareDataComparator:510] - Generating dynamic data...[2017-05-31 08:54:34][WARN ] [MultiThreadPrepareDataComparator:526] - Generate done.

在测试库，就会出现100万条数据了

mysql> select count(*) from test.tbl_test;+----------+| count(*) |+----------+|  1000000 |+----------+1 row in set (0.16 sec)

现在就可以加索引，explain线上真实的SQL语句了

mysql> explain select post_id  from test.tbl_test where post_type <> 1 and check_status = 9 and flag = 1 and post_time < 1496178301 order by post_time asc limit 200; \G+----+-------------+----------+-------+---------------+-----------+---------+------+--------+-------------+| id | select_type | table    | type  | possible_keys | key       | key_len | ref  | rows   | Extra       |+----+-------------+----------+-------+---------------+-----------+---------+------+--------+-------------+|  1 | SIMPLE      | tbl_test | range | post_time     | post_time | 9       | NULL | 501491 | Using where |+----+-------------+----------+-------+---------------+-----------+---------+------+--------+-------------+1 row in set (0.00 sec)ERROR: No query specified

加索引

mysql>  alter table test.tbl_test add index idx_f(check_status,flag,post_type,post_time);           Query OK, 0 rows affected (4.45 sec)Records: 0  Duplicates: 0  Warnings: 0

再来一次explain，扫描50万行变2行

mysql> explain select post_id  from test.tbl_test where post_type <> 1 and check_status = 9 and flag = 1 and post_time < 1496178301 order by post_time asc limit 200; \G+----+-------------+----------+-------+-----------------+-------+---------+------+------+------------------------------------------+| id | select_type | table    | type  | possible_keys   | key   | key_len | ref  | rows | Extra                                    |+----+-------------+----------+-------+-----------------+-------+---------+------+------+------------------------------------------+|  1 | SIMPLE      | tbl_test | range | post_time,idx_f | idx_f | 15      | NULL |    2 | Using where; Using index; Using filesort |+----+-------------+----------+-------+-----------------+-------+---------+------+------+------------------------------------------+1 row in set (0.00 sec)

等调试好索引以后，确定能优化SQL以后，再往线上环境去加索引

当然还有一些很强大的功能

比如某个字段，只出现规定的几个值，比如状态status字段0,1,2，以及每个状态出现的概率

比如模拟线上的用户UID，可以限制某个字段随机数的范围,从00000001到899999999之间等

以上是"SQL调优怎么生成海量测试数据"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！