十三、压力测试

1、生成一个100W数据的表，可参考: 快速生成100W数据mysql表

mysql>  select count(*) from vote_record;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.12 sec)

mysql> select * from vote_record limit 5;
+----+----------------------+---------+----------+---------------------+
| id | user_id              | vote_id | group_id | create_time         |
+----+----------------------+---------+----------+---------------------+
|  1 | RszjFhc2pOEE7vZjq0AJ |     633 |       42 | 2021-02-14 18:43:26 |
|  2 | p7cvK4Xq3QVWJG20LBB3 |     113 |       89 | 2021-02-14 18:43:26 |
|  3 | i8DAVFnKtW9LdEpVfjD3 |     107 |       82 | 2021-02-14 18:43:26 |
|  4 | ZNSQpmqRSDmMFNRKYvry |      78 |       22 | 2021-02-14 18:43:26 |
|  5 | 3XrcwQwUPhJDQ92zzZ4e |     629 |       92 | 2021-02-14 18:43:26 |
+----+----------------------+---------+----------+---------------------+
5 rows in set (0.00 sec)

2、未做索引跟优化之前的测试
测试语句如下，注意建表时给user_id列创建了索引，需要删除索引

#删除user_id的辅助索引
mysql> alter table vote_record drop index  index_user_id;

#使用2000个线程进行压力测试查询
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from school.vote_record where user_id='RszjFhc2pOEE7vZjq0AJ'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

#登录mysql可以看到有2000个线程正在查询
mysql> show processlist;

3、花费时间如下

$ mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from school.vote_record where user_id='RszjFhc2pOEE7vZjq0AJ'" engine=innodb \
> --number-of-queries=2000 -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: 398.598 seconds
	Minimum number of seconds to run all queries: 398.598 seconds
	Maximum number of seconds to run all queries: 398.598 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

4、给user_id列创建索引

mysql> alter table vote_record add unique index uidx_id(user_id);

5、花费时间如下

$ mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from school.vote_record where user_id='RszjFhc2pOEE7vZjq0AJ'" engine=innodb \
> --number-of-queries=2000 -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.138 seconds
	Minimum number of seconds to run all queries: 0.138 seconds
	Maximum number of seconds to run all queries: 0.138 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

You have new mail in /var/spool/mail/root

可以看到速度快的一批

学习来自：郭老师博客，老男孩深标DBA课程 第四章