MySQL数据库优化学习笔记--------分析海量数据

首先创建数据库及表

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;

这里有两张表，一个是emp员工表，一个是dept部门表，通过deptno外键关联。

创建存储函数：

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 $ 

• 注意：如果报错：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe，是因为 存储过程/存储函数在创建时 与之前的 开启慢查询日志冲突了 ，通过下面的指令来解决：

//临时解决：开启log_bin_trust_function_creators
show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators = 1;

//永久解决：修改配置文件
vi /etc/my.cnf 
[mysqld]
log_bin_trust_function_creators = 1

存储过程创建结束后，创建产生随机整数的函数：

create function ran_num() returns int(5)
	begin
		declare i int default 0;
		set i =floor( rand()*100 ) ;
		return i ;

	end $

下面开始创建插入数据的存储过程
首先是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_times
		end repeat ;
		commit ;
	end $

下面是dept表：

create procedure insert_dept(in dno_start int(10) ,in data_times 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$

下面开始插入数据，调用之前写好的存储过程函数：

delimiter ; 
call insert_emp(1000,800000) ;
call insert_dept(10,30) ;

发现emp员工表插入了800，000条数据

mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
|   800000 |
+----------+
1 row in set (0.11 sec)

在dept部门表也插入了30条数据

mysql> select count(1) from dept;
+----------+
| count(1) |
+----------+
|       30 |
+----------+
1 row in set (0.00 sec)

分析海量数据

• profiles: 可以查看指令的运行时间，但是不够精确

show profiles ; --默认关闭
	show variables like '%profiling%';
	set profiling = on ; 
	show profiles  ;--会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu  io ）

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00063250 | show variables like '%profiling%' |
|        2 | 0.11150825 | select count(1) from emp          |
+----------+------------+-----------------------------------+
2 rows in set (0.00 sec)

• 精确分析:sql诊断

show profile all for query 上一步查询的的Query_Id
show profile cpu,block io for query 上一步查询的的Query_Id --只看CPU和IO相关的

此时发现CPU和IO都没有花时间

• 全局查询日志 ： 记录开启之后的 全部SQL语句。 （这次全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭）本身比较耗费性能。用这个语句可以查看是否开启，默认关闭：

show variables like '%general_log%';

可以把sql记录在表或者文件中：

--执行的所有SQL记录在表中
set global general_log = 1 ;--开启全局日志
set global log_output='table' ; --设置 将全部的SQL 记录在表中

--执行的所有SQL记录在文件中
set global log_output='file' ;
set global general_log = on ;
set global general_log_file='/tmp/general.log' ;

执行几个语句 发现全局日志中有记录

mysql> select * from  mysql.general_log ;
+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| event_time          | user_host                 | thread_id | server_id | command_type | argument                         |
+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| 2019-10-09 19:55:59 | root[root] @ localhost [] |         8 |         1 | Query        | select * from emp                |
| 2019-10-09 19:56:39 | root[root] @ localhost [] |         8 |         1 | Query        | select count(1) from emp         |
| 2019-10-09 19:57:04 | root[root] @ localhost [] |         8 |         1 | Query        | select * from  mysql.general_log |
+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
3 rows in set (0.00 sec)