SQL調(diào)優(yōu)怎么生成海量測(cè)試數(shù)據(jù)

小編給大家分享一下SQL調(diào)優(yōu)怎么生成海量測(cè)試數(shù)據(jù)，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

場(chǎng)景，如果出現(xiàn)慢SQL，需要DBA加索引優(yōu)化，怎么知道加的索引是有效的呢？這需要一遍遍的試驗(yàn)和調(diào)整，總不能直接拿線上的數(shù)據(jù)庫(kù)測(cè)試吧，一般方法是在測(cè)試環(huán)境建立測(cè)試表，然后從線上的從庫(kù)拷貝一些數(shù)據(jù)進(jìn)測(cè)試環(huán)境，接著再進(jìn)行加索引和explain

但有時(shí)候，導(dǎo)出的數(shù)據(jù)量少，執(zhí)行計(jì)劃看不出效果，導(dǎo)出數(shù)據(jù)量多，又會(huì)沖刷線上機(jī)器的buffer pool和影響IO，如果有個(gè)工具能夠直接生成數(shù)據(jù)就好了，生成跟線上一樣的100萬，或者1000萬就好了

以前sysbench壓力測(cè)試，有一個(gè)生成數(shù)據(jù)的功能，生成100萬數(shù)據(jù)是這樣的

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

但它生成表結(jié)構(gòu)是固定的，進(jìn)行壓力測(cè)試的SQL語句也是固定的,無法調(diào)試線上的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`));

能否有一個(gè)創(chuàng)建用戶自定義的表結(jié)構(gòu)，并且對(duì)這個(gè)表結(jié)構(gòu)生成上百千萬數(shù)據(jù)的工具呢？有一個(gè)叫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

方法很簡(jiǎn)單的2步，把你想要的表結(jié)構(gòu)和想要生成多少條數(shù)據(jù)，寫入到example.schema文件，比如這樣，如果想要生成100萬條數(shù)據(jù)，在表末尾加入注釋/*{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步，填寫連接測(cè)試數(shù)據(jù)庫(kù)的賬號(hào)密碼，只需要加入一行

<property name="obURL" value="jdbc:mysql://數(shù)據(jù)IP:數(shù)據(jù)庫(kù)端口/數(shù)據(jù)庫(kù)名字?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://數(shù)據(jù)IP:數(shù)據(jù)庫(kù)端口/數(shù)據(jù)庫(kù)名字?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>

接著運(yùn)行shell腳本，往測(cè)試庫(kù)建表，插入數(shù)據(jù)

[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.

在測(cè)試庫(kù)，就會(huì)出現(xiàn)100萬條數(shù)據(jù)了

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

現(xiàn)在就可以加索引，explain線上真實(shí)的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)

等調(diào)試好索引以后，確定能優(yōu)化SQL以后，再往線上環(huán)境去加索引

當(dāng)然還有一些很強(qiáng)大的功能

比如某個(gè)字段，只出現(xiàn)規(guī)定的幾個(gè)值，比如狀態(tài)status字段0,1,2，以及每個(gè)狀態(tài)出現(xiàn)的概率

比如模擬線上的用戶UID，可以限制某個(gè)字段隨機(jī)數(shù)的范圍,從00000001到899999999之間等

以上是“SQL調(diào)優(yōu)怎么生成海量測(cè)試數(shù)據(jù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！