数据库优化第11讲 - 排序优化&慢查询日志&Show Profile

一、排序的优化

分析

1. 观察,至少跑一天,看看生产的慢SQL情况
2. 开启慢查询日志,设置阙值,比如超过5秒钟的就是慢SQL,并抓取出来
3. explain + 慢SQL分析
4. show profile（相当于体检报告）
5. 进行SQL数据库服务器的参数调优(运维orDBA来做)

总结

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. show profile查询SQL在MySQL服务器里面的执行细节
4. SQL数据库服务器的参数调优

---

永远小表驱动大表

for i in range(5):
	for j in range(1000):
		pass
	
for i in range(1000):
	for j in range(5):
		pass

二、order by优化

order by子句,尽量使用index方式排序,避免使用filesort方式排序
MySQL支持两种方式的排序,filesort和index,index效率高,MySQL扫描索引本身完成排序。filesort方式效率较低

order by 满足两种情况下,会使用index方式排序

• order by 语句使用索引最左前列
• 使用where子句与order by子句条件组合满足索引最左前列

filesort有两种算法-双路排序和单路排序

• 双路排序,MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order
  by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
• 单路排序,从磁盘读取查询需要的所有列,按照order
  by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据,并且把随机IO变成了顺序IO,但是它会使用更多的空间

优化策略调整MySQL参数

增加sort_buffer_size参数设置
增大max_lenght_for_sort_data参数的设置	

提高order by的速度

• order by时select * 是一个大忌,只写需要的字段
• 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时,会用改进后的算法–单路排序
  两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O
• 尝试提高sort_buffer_size
• 尝试提高max_length_for_sort_data

---

索引 a_b_c(a,b,c)

order by a,b
order by a,b,c
order by a desc,b desc,c desc

where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c

order by a asc,b desc,c desc  
where g = const order by b,c   
where a = const order by c    
where a = const order by by a,d 

三、慢查询日志

• MySQL的慢查询日志MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阙值的语句,具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中
• 具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。
• 由他来查看那些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒钟,我们就算是慢SQL,希望能收集超过5秒的SQL,结合之前explain进行全面分析

如何使用

• 默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数
• 当然如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

慢查询日志工具

s:表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
t:即为返回前面多少条的数据

得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

1、创建函数

创建函数，假如报错：this function has none of DETERMINISTIC… 查看参数

set global log_bin_trust_function_creators=1;

# 部门表
create table dept(
            id int primary key auto_increment,
            deptno mediumint not null,
            dname varchar(20) not null,
            loc varchar(13) not null
)engine=innodb default charset=gbk;

#员工表
create table emp(
            id int primary key auto_increment,
            empno mediumint not null,   
            ename varchar(20) not null, 
            job varchar(9) not null, 
            mgr mediumint not null,  
            hiredate DATE not null,  
            sal decimal(7,2) not null, 
            comm decimal(7,2) not  null, 
            deptno mediumint not null   
)engine=innodb default charset=gbk;

随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

随机产生部门编号

DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$	

2、创建存储过程

插入数据

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

插入数据

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES ((START + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

调用存储过程.

delimiter ; 
call insert_dept(100,10);
    
delimiter ; 
call insert_emp(100001,500000);

Show Profile进行SQL分析

Show Profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量

Show Profile分析步骤

1. 是否支持,看看当前MySQL版本是否支持
2. 开启功能,默认是关闭,使用前需要开启

type

all         显示所有的开销信息
block io    显示块IO相关开销
cpu         显示CPU相关开销信息
ipc         显示发送和接收相关开销信息
memory      显示内存相关开销信息
page faults 显示页面错误相关开销信息

参数注意

converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘,危险
locked

全局查询日志

开启命令

set global general_log = 1;

将SQL语句写到表中

set global log_output = 'TABLE';

你所编写的SQL语句,会记录到MySQL库里的genral_log表

select * from mysql.general_log;