mysql压力测试并优化_MySQL压力测试索引优化效果演示全过程

1、模拟数据库数据

drop database if exists oldboy;

create database oldboy charset utf8mb4 collate utf8mb4_bin;

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 ;

插入100w条数据：

call rand_data(1000000);

commit;

2、检查数据可用性

select count(*) from oldboy. t_100w;

select table_name,table_rows from information_schema.tables where table_schema='oldboy';

select * from t_100w where id<5;

3、在没有优化之前我们使用mysqlslap来进行压力测试

例子1：模拟100个用户同时做20次查询

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=100 --iterations=1 --create-schema='oldboy' \

--query="select * from oldboy.t_100w where k2='780P'" engine=innodb \

--number-of-queries=2000 -uroot -p123456 -verbose

执行上述数据需要花费600秒

例子2：模拟10个用户同时做20次查询

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=10 --iterations=1 --create-schema='oldboy' \

--query="select * from oldboy.t_100w where k1='ab' order by k2;" engine=innodb \

--number-of-queries=200 -uroot -p123456 -verbose

执行上述数据需要花费59秒

3.查看索引执行计划

use oldboy;

desc t_100w;

show index from t_100w;

例子1：

desc select * from oldboy.t_100w where k2='780P';(type为all)

例子2：

desc select * from oldboy.t_100w where k1='ab' order by k2;(type为all)

4.创建索引优化

例子1：

alter table t_100w add index k2(k2);

desc select * from oldboy.t_100w where k2='780P';(type为ref)

例子2：

alter table t_100w add index k1k2(k1,k2);

desc select * from oldboy.t_100w where k1='ab' order by k2;(type为ref)

5、优化之后使用mysqlslap来进行压力测试

例子1：模拟100个用户同时做20次查询

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=100 --iterations=1 --create-schema='oldboy' \

--query="select * from oldboy.t_100w where k2='780P'" engine=innodb \

--number-of-queries=2000 -uroot -p123456 -verbose

执行上述数据需要花费0.169秒

例子2：模拟10个用户同时做20次查询

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=10 --iterations=1 --create-schema='oldboy' \

--query="select * from oldboy.t_100w where k1='ab' order by k2;" engine=innodb \

--number-of-queries=200 -uroot -p123456 -verbose

执行上述数据需要花费0.124秒