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

边肖将与您分享SQL调优如何生成大量测试数据。相信大部分人还不太了解，所以分享这篇文章给大家参考。希望你看完这篇文章后收获多多。让我们一起来发现吧！

场景，如果有慢SQL，需要DBA加索引优化。我们如何知道添加的索引是有效的？这需要反复测试和调整。你不能直接测试在线数据库。一般的方法是在测试环境中建立一个测试表，然后从在线从库中复制一些数据到测试环境中，然后进行索引和解释。

但有时候导出的数据量小，实施方案看不到效果，导出的数据量大，会刷新联机机器的缓冲池，影响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-MySQL-password=test prepare，但是它生成的表结构是固定的，压力测试的SQL语句也是固定的，所以无法调试在线的SQL语句。

CREATETABLE`sbtest `(

` id ` int(10)unsignedNOTNULLauto _ increment，

` k ` int(10)unsignedNOTNULLdefault ' 0 '，

` c`char(120)NOTNULLdefault ' '，

` pad`char(60)NOTNULLdefault ' '，

PRIMARYKEY(`id `),

key ` k `(` k `))；有没有一个工具可以创建一个用户定义的表结构，并为这个表结构生成数百万个数据？有一个名为datagen的工具，它链接在文章的最后。

drwxr-xr-x . 2 root MySQL 4096 sep 272016 bizsql

drwxr-xr-x . 2 root MySQL 4096 may 3120:51 config

-rw-r-r-. 1 root MySQL 23698092 sep 272016 datagen . jar

-rwxr-xr-x . 1 root MySQL 147 sep 272016 datagen . sh

-rw-rw-r-. 1 root MySQL 31599 may 3120:54 env builder . log

-rw-r-r-. 1 root MySQL 1741 may 3120336053示例架构

-rw-r-r-. 1 root MySQL 1336 may 3109336042 example . schema _ backup

-rw-r-r-.1根MySQL 2062 sep272016自述方法是一个简单的两步过程。在example.schema文件中写下您想要的表结构以及您想要生成多少条数据。例如，如果要生成一百万条数据，请在表/* {RC {100000} *的末尾添加注释。

/

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步，填写连接测试数据库的账号密码，只需要加入一行

<property name="obURL" value="jdbc:mysql://数据IP:数据库端口/数据库名字?user=用户名&amp;password=密码"/>

vi conf/datagen.xml 
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
        classpath:org/springframework/beans/factory/xml/spring-beans-2.5.xsd">    
        <bean id="datagen" class="com.alipay.obmeter.tools.DataGen">
           <property name="obURL" value="jdbc:mysql://数据IP:数据库端口/数据库名字?user=用户名&amp;password=密码"/>
                 
           <property name="inputDDL" value="example.schema"/>
           <property name="rowCountPerTable" value="1000000"/>
           <property name="maxThreadCountPerTable" value="20"/>
           <property name="maxThreadCount" value="20"/>
           <property name="dropTableFirst" value="true"/>
           <property name="needFreeze" value="false"/>
           <property name="staticRatio" value="1.0"/>
        </bean>
</beans>

接着运行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调优怎么生成海量测试数据”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！