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 bizsql drwxr-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步,填写连接测试数据库的账号密码,只需要加入一行
<property name="obURL" value="jdbc:mysql://数据IP:数据库端口/数据库名字?user=用户名&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=用户名&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之间等
具体可以查看readme的介绍
百度链接: https://pan.baidu.com/s/1pKGQLkB 密码: 6t4u

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
-
上一篇
网页重定向导致防火墙NAT失效问题
某地政府部门A与部门B之间需要建立连接,部门A需要访问部门B的某网站,在部门A与部门B之间使用防火墙进行隔离并使用目的NAT功能。 如图,防火墙左边为部门A,右边为部门B,防火墙由于没有规划互联地址,使用终端网段内一地址作为接口地址,并用此地址作为目的NAT使用。终端访问151接口地址,经过NAT转换后,转为访问部门B服务器。 防火墙配置完成后,测试PING正常,远程桌面正常,但是WEB访问不通。检查防火墙配置未发现问题,在 部门B网络内访问WEB服务正常。将终端的网关由254改为151后,WEB访问正常,但显示网页的IP地址是B部门服务器的真实地址。 在终端上进行抓包,发现服务器返回的报文内,有重定向的信息,告诉终端访问网页的实际地址。 结果终端不再访问防火墙接口地址,而是直接访问真实地址。核心交换机上并没有此条路由,所以不会通,而将网关指定在防火墙上则不需要路由了,可以访问。此问题无法从网络层面进行解决,只能交给WEB维护人员修改。并不是所有网站都有此种情况,具体何种网站会使用到此技术,本人并不十分了解。大家可以试试,在网页里直接访问www.baidu.com,实际弹出的却是...
-
下一篇
nginx前端根据$remote_addr分发方法
需求如下: 域名下面有多台服务器,现针对某个地区做测试,让某个地区的ip用户只访问某一台服务器,单独做测试,如果没问题,全部更新;有问题则影响较小,及时发现问题解决问题; 解决方案: 使用nginx的模块,在前端负载均衡转发的机器上,配置匹配规则; nginx配置vhost里面,域名下面location段,增加一段代码 如果$remote_addr 匹配到ip的话,转发到abc_test_server; server{ listen80; server_nameabc.com.cn; access_log/dev/null; error_log/data/logs/error.log; location/{ proxy_set_headerHost$host; proxy_set_headerX-Real-IP$remote_addr; proxy_set_headerX-Forwarded-For$proxy_add_x_forwarded_for; if($remote_addr~"202.96.134.100") { proxy_passhttp://abc_test_serv...
相关文章
文章评论
共有0条评论来说两句吧...