mysql百万级数据测试_2019-06-14 MySQL百万级数据压力测试

先模拟数据：

DROP DATABASE IF EXISTS oldboy;

create database oldboy charset utf8;

use oldboy;

CREATE TABLE t_100w (id INT,num INT,k1 CHAR(2),k2 CHAR(4),dt TIMESTAMP);

DELIMITER //

CREATE PROCEDURE rand_data(IN num INT)

BEGIN

DECLARE str CHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

DECLARE str2 CHAR(2);

DECLARE str4 CHAR(4);

DECLARE i INT DEFAULT 0;

WHILE i

SET str2=CONCAT(SUBSTRING(str,1+FLOOR(RAND()*61),1),SUBSTRING(str,1+FLOOR(RAND()*61),1));

SET str4=CONCAT(SUBSTRING(str,1+FLOOR(RAND()*61),2),SUBSTRING(str,1+FLOOR(RAND()*61),2));

SET i=i+1;

INSERT INTO t_100w VALUES (i,FLOOR(RAND()*num),str2,str4,NOW());

END WHILE;

END;

//

DELIMITER ;

CALL rand_data(1000000);

COMMIT;

#查询数据是否正确

select count(*) from oldboy.t_100w;

----------+

| count(*) |

+----------+

| 1000000 |

+----------+

如上数据已经准备好；

下一步在linux下下执行如下压力测试语句：

[root@Manager ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='CD23'" ENGINE=INNODB --number-of-queries=2000 -uroot -p123 -verbose

mysqlslap: [Warning] Using a password on the command line interface can be ×××ecure.

Benchmark

Running for engine rbose

Average number of seconds to run all queries: 324.570 seconds

Minimum number of seconds to run all queries: 324.570 seconds

Maximum number of seconds to run all queries: 324.570 seconds

Number of clients running queries: 100

Average number of queries per client: 20

可以看到当前没有创建任何索引的情况下查询2000次的时间为324.570秒;

原因没有创建索引：

USE oldboy

DESC SELECT * FROM oldboy.t_100w WHERE k2='CD23'

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE t_100w \N all null null 17 const 292 100.00 \N

SHOW INDEX FROM t_100w

ALTER TABLE t_100w ADD INDEX idx(k2);

DESC SELECT * FROM oldboy.t_100w WHERE k2='CD23'

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE t_100w \N ref idx idx 17 const 292 100.00 \N

现在将查询语句进行优化，添加索引后重新执行命令后结果如下：

[root@Manager ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='CD23'" ENGINE=INNODB --number-of-queries=2000 -uroot -p123 -verbose

mysqlslap: [Warning] Using a password on the command line interface can be ×××ecure.

Benchmark

Running for engine rbose

Average number of seconds to run all queries: 2.219 seconds

Minimum number of seconds to run all queries: 2.219 seconds

Maximum number of seconds to run all queries: 2.219 seconds

Number of clients running queries: 100

Average number of queries per client: 20

我们发现时间由300多秒缩减到只有2s左右，优化明显!极大地改进了用户体验！