mysql压力测试&怎么快速做出有500w条数据的table？

下面是做500w条数据table的步骤：

[root@localhost tmp]# i=1;while [ $i -le 5000000 ];do echo $i ;let i+=1; done >500w.txt

[root@localhost tmp]# ls
500w.txt
[root@localhost tmp]# tail 500w.txt 
4999991
4999992
4999993
4999994
4999995
4999996
4999997
4999998
4999999
5000000

在mysql中创建名字叫tmp500w的表

mysql> use yyds

Database changed

mysql> create table tmp500w(id int,primary key(id));

导入数据到表tmp500w

mysql> load data infile '/tmp/500w.txt' replace into table tmp500w;#注意500w.txt存放的路径在哪

Query OK, 5000000 rows affected (9.66 sec)

Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0

如果显示错误ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

则在数据库的配置文件my.cnf中，在[mysqld]下添加语句

[root@localhost ziliao]# echo "secure_file_priv=''"  >> /etc/my.cnf 

添加完重启mysqld：     systemctl restart mysqld

或/usr/local/mysql/support-files/mysql.server restart

这时可以看到

mysql> load data infile '/tmp/500w.txt' replace into table tmp500w;
Query OK, 5000000 rows affected (28.91 sec)
Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0

#再创建生成500万数据的表exam
mysql> create table exam(id int,c1 int,c2 varchar(100),primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_yyds |
+----------------+
| exam           |
| tmp500w        |
+----------------+
2 rows in set (0.00 sec)

mysql> desc exam;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| c1    | int(11)      | YES  |     | NULL    |       |
| c2    | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc tmp500w;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

插入500万条数据到表exam
mysql> insert into exam select id,round(rand()*5000000),concat('laoliulinuxyyds',id)from tmp500w;
Query OK, 5000000 rows affected (27.36 sec)
Records: 5000000  Duplicates: 0  Warnings: 0

mysql> select * from exam limit 10;
+----+---------+-------------------+
| id | c1      | c2                |
+----+---------+-------------------+
|  1 | 4736170 | laoliulinuxyyds1  |
|  2 |  831375 | laoliulinuxyyds2  |
|  3 | 4948363 | laoliulinuxyyds3  |
|  4 | 2247689 | laoliulinuxyyds4  |
|  5 | 1393359 | laoliulinuxyyds5  |
|  6 |  223725 | laoliulinuxyyds6  |
|  7 | 1938551 | laoliulinuxyyds7  |
|  8 | 4021580 | laoliulinuxyyds8  |
|  9 | 4292246 | laoliulinuxyyds9  |
| 10 | 4396492 | laoliulinuxyyds10 |
+----+---------+-------------------+
10 rows in set (0.00 sec)

500万条数据的表就创建好了

压测模拟：（这个语句是在[root@localhost ~]# 下执行的）

介绍explain语句：类似模拟查看，会给出explain后面语句运行的相关结果和参数。

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=50  --iterations=1  --create-schema='test' \
--query="select * from yyds.exam where c2='780p'" engine=innodb \
--number-of-queries=200  -uroot -p123 -verbose
#参数解释：
#--concurrency=50 并发量（50个人一起上的意思）
#--iterations=1 语句迭代次数（迭代的实验次数）
#--create-schema，测试的schema，MySQL中schema也就是database
#--query     指定SQL语句
#--number-of-queries，执行的SQL总数量
#这里要保证mysql中数据库yyds，表exam，字段c2要存在

如果显示mysqlslap找不到命令，那就前面加个绝对路径：

/usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf  --concurrency=50 --iterations=1 --create-schema='test'  --query="select * from yyds.exam where c2='780p'" engine=innodb  --number-of-queries=200 -uroot -p -verbose

mysql5.7.35:没有添加索引之前， 要263.303秒，4分多钟，

[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=50  --iterations=1  --create-schema='test' \
> --query="select * from yyds.exam where c2='780p'" engine=innodb \
> --number-of-queries=200  -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 263.303 seconds
        Minimum number of seconds to run all queries: 263.303 seconds
        Maximum number of seconds to run all queries: 263.303 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4

现在给c2建立索引：

mysql> use yyds

mysql> alter table exam add index idx_c2(c2);
Query OK, 0 rows affected (31.90 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查看索引（主要看key列）
mysql> show index from exam\G
*************************** 1. row ***************************
        Table: exam
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4981848
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: exam
   Non_unique: 1
     Key_name: idx_c2
 Seq_in_index: 1
  Column_name: c2
    Collation: A
  Cardinality: 4981393
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

mysql> desc exam;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| c1    | int(11)      | YES  |     | NULL    |       |
| c2    | varchar(100) | YES  | MUL | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

建立索引之后，只需要0.073秒

[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=50  --iterations=1  --create-schema='test' --query="select * from yyds.exam where c2='780p'" engine=innodb --number-of-queries=200  -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 0.073 seconds
        Minimum number of seconds to run all queries: 0.073 seconds
        Maximum number of seconds to run all queries: 0.073 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4

mysql5.6:没有添加索引之前， 要270.309秒

[root@localhost ~]# /usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf  --concurrency=50 --iterations=1 --create-schema='test'  --query="select * from yyds.exam where c2='780p'" engine=innodb  --number-of-queries=200 -uroot -p -verbose
Enter password: 
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 270.309 seconds
        Minimum number of seconds to run all queries: 270.309 seconds
        Maximum number of seconds to run all queries: 270.309 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4

 给c2建立索引之后，只需要0.176秒

[root@localhost ~]# /usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf  --concurrency=50 --iterations=1 --create-schema='test'  --query="select * from yyds.exam where c2='780p'" engine=innodb  --number-of-queries=200 -uroot -p -verbose
Enter password: 
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 0.176 seconds
        Minimum number of seconds to run all queries: 0.176 seconds
        Maximum number of seconds to run all queries: 0.176 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4

对比发现mysql5.7比mysql5.6读取（查询）速度要快