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

2025-03-08 技术教程

小编给大家分享一下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=testprepare

但它生成表结构是固定的，进行压力测试的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.2rootmysql4096Sep272016bizsqldrwxr-xr-x.2rootmysql4096May3120:51conf-rw-r--r--.1rootmysql23698092Sep272016datagen.jar-rwxr-xr-x.1rootmysql147Sep272016datagen.sh-rw-rw-r--.1rootmysql31599May3120:54envbuilder.log-rw-r--r--.1rootmysql1741May3120:53example.schema-rw-r--r--.1rootmysql1336May3109:42example.schema_backup-rw-r--r--.1rootmysql2062Sep272016readme

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

CREATETABLE`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',PRIMARYKEY(post_id),INDEX(poster_uid))COLLATE='utf8mb4_general_ci'ENGINE=InnoDBDEFAULTCHARSET=utf8mb4/*{RC{1000000}}*/;

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

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

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

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

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

[2017-05-3108:53:15][WARN][DataGen:184]-Parsingddl...[2017-05-3108:53:15][WARN][DataGen:187]-Creatingtable...[2017-05-3108:53:15][WARN][MultiThreadPrepareDataComparator:508]-Preparinggenerators...[2017-05-3108:53:15][WARN][MultiThreadPrepareDataComparator:510]-Generatingdynamicdata...[2017-05-3108:54:34][WARN][MultiThreadPrepareDataComparator:526]-Generatedone.

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

mysql>selectcount(*)fromtest.tbl_test;+----------+|count(*)|+----------+|1000000|+----------+1rowinset(0.16sec)

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

加索引

mysql>altertabletest.tbl_testaddindexidx_f(check_status,flag,post_type,post_time);QueryOK,0rowsaffected(4.45sec)Records:0Duplicates:0Warnings:0

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

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

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

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

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

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