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<num do
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秒