第三章 MYSQL优化（3）——SQL的优化2

MYSQL优化（3）

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
第一章 MYSQL优化（1）——架构介绍
第二章 MYSQL优化（2）——SQL的优化1
第三章 MYSQL优化（3）——SQL的优化2
第四章 MYSQL优化（4）——锁的机制
第五章 MYSQL优化（5）——主从配置

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

上一章节我们对SQL的where条件和select之后的字段的查询我们做了优化分析，这章节我们继续对排序，分组等进行分析优化

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、查询优化

1.1MYSQL优化的步骤

• 1.慢查询的开启并捕获
• 2.show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
• 3.explain+慢SQL分析
• 4.SQL数据库服务器的参数调优

1.2永远小表驱动大表，类似嵌套循环 Nested Loop

1.exist 与 in 的原理

说明：
EXISTS 语法：SELECT … FROM table WHERE EXISTS(subquery)
1.该语法可以理解为：将查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
2/EXISTS(subquery) 只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别
3.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
4.EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析
结论：
1.永远记住小表驱动大表
2.当 B 表数据集小于 A 表数据集时，使用 in
3.当 A 表数据集小于 B 表数据集时，使用 exist

1.3ORDER BY 优化

只要优化点：ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

1.3.1案例一：建表语句

create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

1.3.2.CASE1：能使用索引进行排序的情况
1.3.2.1只有带头大哥 age
SQL语句一：EXPLAIN SELECT * FROM tblA where age>20 order by age;
SQL语句一： EXPLAIN SELECT * FROM tblA where birth>‘2016-01-28 00:00:00’ order by age;
1.3.2.2.带头大哥 age + 小弟 birth
SQL语句一：EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
1.3.2.3.mysql 默认升序排列，全升序或者全降序，都扛得住
SQL语句一：EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
SQL语句一：EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;

1.3.3.CASE2：不能使用索引进行排序的情况
1.3.3.1带头大哥 age 挂了
SQL语句一：EXPLAIN SELECT * FROM tblA where age>20 order by birth;
1.3.3.2小弟 birth 居然敢在带头大哥 age 前面
EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
1.3.3.3mysql 默认升序排列，如果全升序或者全降序，都 ok ，但是一升一降 mysql 就扛不住了
EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;

1.3.4结论
1.MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低。
2.ORDER BY满足两情况（最佳左前缀原则），会使用Index方式排序
2.1ORDER BY语句使用索引最左前列
2.2使用where子句与OrderBy子句条件列组合满足索引最左前列
3尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

1.3.5对于filesort排序算法：双路排序和单路排序进行简单分析

1.双路排序

1.MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行orderby操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输

2.从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

2.单路排序

1.取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了改进的算法，就是单路排序。

2.从磁盘读取查询需要的所有列，按照将要进行orderby的列，在sort buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

3.结论及引申出的问题：

1.由于单路是改进的算法，总体而言好过双路

2.在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。
3.结论：本来想省一次I/O操作，反而导致了大量的/O操作，反而得不偿失。

4.更深层次的优化策略：

1.增大sort_buffer_size参数的设置

2.增大max_length_for_sort_data参数的设置

1.3.6小结：
１．不使用select *，会给内存带来很大的负担
２．尝试提高 sort_buffer_size----要根据系统的能力去提高，因为这个参数是针对每个进程的
3．尝试提高max_length_for_sort_data提高这个参数—增加用改进算法的概率

1.3.7order by 总结：

1.4、GROUP BY 优化

1. group by实质是先排序后进行分组，遵照索引的最佳左前缀
2. 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
3. where高于having，能写在where限定的条件就不要去having限定了
4. 其余的规则均和 order by 一致

二、慢查询日志

2.1、什么是慢日志

总结：
1对执行慢的SQL进行记录
2超时时间自己配置，默认为10S
3给我们排查SQL带来便利

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

2.2、开始慢日志

1.开启慢日志

1. 查看是否开启SQL：SHOW VARIABLES LIKE ‘%slow_query_log%’;
2. SQL开启语句：set global slow_query_log = 1; 重启后则失效
3. 文件配置：修改my.cnf文件，[mysqld]下增加或修改参数：slow_query_log和slow_query_log_file后，然后重启MySQL服务器。

2.配置超时时间阈值

1. 查看long_query_time语句：SHOW VARIABLES LIKE ‘long_query_time%’;
2. 设置超时时间语句：set global long_query_time=3; 重启后失效

3.案例说明
1.查看慢 SQL 的阈值时间，默认阈值时间为 10s—SHOW VARIABLES LIKE ‘long_query_time%’;

2.设置慢 SQL 的阈值时间，我们将其设置为 2s—set global long_query_time=2
3.执行慢SQL-----select sleep(4);
4.进入文件夹/var/lib/mysql/ 查询后缀为 -slow.log的文件；说明：docker部署的没做挂载的需要进到容器中，在找相应路径查询文件。

5.也可以通过语句查询到MYSQL目前有多少慢SQL条数，可以作为我们一个性能的指标----show global status like ‘%Slow_queries%’;

2.3、MYSQL日志分析工具 mysqldumpslow

**1工具说明：**在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
2.查看工具具体使用语句：mysqldumpslow --help
参数说明：
s：是表示按何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感的
3.常用的语句说明：
得到返回记录集最多的10个SQL----mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log
得到访问次数最多的10个SQL-----mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句-----mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/Heygo-slow.log
另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏情况-----mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more

三、批量数据脚本说明

案例说明：使用函数，存储过程，批量导入大数据量
1.创建表SQL语句：
CREATE TABLE dept
(
deptno int unsigned primary key auto_increment,
dname varchar(20) not null default “”,
loc varchar(8) not null default “”
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE emp
(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default “”,
job varchar(9) not null default “”,
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;
2.设置参数
创建函数，假如报错：This function has none of DETERMINISTIC………
使用SQL操作:
SQL语句一： show variables like ‘log_bin_trust_function_creators’;
SQL语句二：set global log_bin_trust_function_creators=1;
使用文件配置修改一下文件：
windows下：my.ini --> [mysqld] 节点下加上 log_bin_trust_function_creators=1
linux下：/etc/my.cnf --> [mysqld] 节点下加上 log_bin_trust_function_creators=1
3.创建函数，保证每条数据都不同
：随机产生字符串的函数

delimiter $$ # 两个 $$ 表示结束
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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 $$

4.创建存储过程
4.1创建往emp表中插入数据的存储过程`

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 $$

4.2创建往dept表中插入数据的存储过程

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 $$

5.调用存储过程
5.1向 dept 表中插入 10 条记录
DELIMITER ;
CALL insert_dept(100, 10);
5.2向 emp 表中插入 50w 条记录
DELIMITER ;
CALL insert_emp(100001, 500000);

四、Show Profile

说明：

1. 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
2. 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
3. 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

开启show profile功能

1. show variables like ‘profiling%’; 查看 Show Profile 是否开启
2. set profiling=on; 开启 Show Profile

案例分析：

正常 SQL

select * from tbl_emp;

select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;

select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;

慢 SQL

select * from emp group by id%10 limit 150000;

select * from emp group by id%10 limit 150000;

select * from emp group by id%10 order by 5;

分析：

通过 show profiles; 指令查看结果

show profile cpu, block io for query SQL编号; 查看 SQL 语句执行的具体流程以及每个步骤花费的时间


参数备注：
ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销
CONTEXT SWITCHES：上下文切换相关开销
CPU：显示CPU相关开销信息
IPC：显示发送和接收相关开销信息
MEMORY：显示内存相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS：显示交换次数相关开销的信息

总结：

1 converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。

2 Creating tmp table：创建临时表，mysql 先将拷贝数据到临时表，然后用完再将临时表删除

3 Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！

4 locked：锁表

五、全局查询日志

注：永远不要在生产环境开启这个功能。

5.1.配置启用全局查询日志

在mysql的my.cnf中，设置如下：
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

5.2SQL启用全局查询日志

set global general_log=1;
set global log_output=‘TABLE’;

5.3查询：select * from mysql.general_log;
此后，你所执行的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看