现在的信息时代,有大量的数据需要被查询并分析。这里所指的海量数据是至少几百万条甚至上千条的是数据返回。基于Mysql 这类的关系型数据库,查询还可能还涉及多表连查,性能可想而知,要想进行优化,就必须要对网络传输、数据服务器处理时间、CPU性能、索引效率等多方面因素进行一定的分析。从而才能得到真正耗时的症结所在,合理的对症下药,优化Mysql的查询性能。
上一次讲到通过跨列查询导致索引失效,来学习 explain 各个参数。今天是通过存储过程模拟海量数据,来了解profile 对分析海量数据的意义。
1,创建两个表,分别为部门表和员工表
create table dept
(
dno int(5) primary key default 0,
dname varchar(20) not null default '',
loc varchar(30) default ''
) ENGINE = INNODB default charset=utf8
create table emp
(
eid int(5) primary key,
ename varchar(20) not null default '',
job varchar(20) not null default '',
deptno int(5) not null default 0
) ENGINE = INNODB DEFAULT charset = utf8
2,通过存储函数插入海量数据
2.1创建存储函数:randstring(6) -> dsjdjA 用于模拟员工名称(随机字符串)
delimiter $
create function randstring(n int) returns varchar(255)
begin
declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
declare return_str varchar(255) default '' ;
declare i int default 0;
while i<n
do
set return_str = concat(return_str, substring(all_str, FLOOR(1+RAND()*52), 1) );
set i=i+1;
end while;
return return_str;
end $
2.2 产生随机整数的存储函数
delimiter $
create function ran_num() returns int(5)
begin
declare i int default 0;
set i = floor(rand() * 100);
return i;
end $
2.3通过存储过程插入海量数据:emp 表中
create procedure insert_emp(in eid_start int(10), in data_times int(10))
begin
declare i int default 0;
set autocommit = 0; --关闭自动提交
repeat
insert into emp values(eid_start+ i, randstring(5), 'other' ran_num());
set i = i+1;
until i = data_time
end repeat;
commit;
end $
dept 表中
create procedure insert_dept(in dno_start int(10) , in datatimes int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
insert into dept values(dno_start +i, randstring(6), randstring(8));
set i = i+1;
until i = data_times
end repeat;
commit;
end$
执行两个表的插入操作
call insert_emp(1000, 800000);
call insert_dept(10, 30);
3,分析海量数据: profiles
show profile;--默认关闭
show variables like '%profiling%';
set profiling = on -- 再查看是否打开
show profiles;--会记录所有profiling打开之后的,全部SQL查询语句所花费的时间。缺点:不够精确,只能看到总的时间。
--精确诊断:sql诊断
show profile all for query 1;查看指定query_id的sql的执行消耗情况
show profile cpu,block io for query 2;
结果参照下图: