Mysql 优化之海量数据分析

现在的信息时代，有大量的数据需要被查询并分析。这里所指的海量数据是至少几百万条甚至上千条的是数据返回。基于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; 

结果参照下图：