记录生活中的点点滴滴

0%

mysql高级3:查询截取分析

实际项目中,我们肯定要对我们的SQL进行分析并优化,这篇文章就写一下查询截取分析,主要有查询优化、慢查询日志和Show Profile的使用。

实际项目的SQL分析:

  1. 观察,至少跑一天,看看生产的慢SQL情况
  2. 开启慢查询日志,设置阈值,比如超过5秒钟的时间就是慢SQL,并将它抓取出来
  3. Explain+慢SQL分析
  4. show profile 至此基本上能解决90%的问题
  5. 运维经理或者DBA进行SQL数据库服务器的参数调优

总结:

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

查询优化

小表驱动大表

即小的数据集驱动大的数据集

1
2
3
4
5
6
7
8
9
10
select * from A where id in (select id from B);
等价于:for select id from B
for select * from A where (A.id = B.id)
当B表的数据集小于A表的数据集时,用in优于exists

select * from A where id exists (select 1 from B where A.id = B.id);
等价于:for select id from A
for select * where B.id = A.id;
当A表的数据集小于B表的数据集时,用exists优于in
注意:A表与B表的id字段应建立索引

我们用我们的 tbl_emp 和 tbl_dept 表来进行演示:

先看看这两个表的内容:

然后用in的方式,遵循小表驱动大表,那么 dept 表应该在前面,具体的SQL语句如下:

接着我们用exists的方式,遵循小表驱动大表,那么 emp 表应该在前面,具体的SQL语句如下:

这里的select 1,也可以换成 select 2 或者select ‘X’ 等等。

order by优化

尽量使用index方式排序,避免filesort

建表SQL:

1
2
3
4
5
6
7
8
9
create table tblA(
age int,
birth timestamp not null
);
insert into tblA values(22,now());
insert into tblA values(23,now());
insert into tblA values(24,now());
create index idx_A_ageBirth on tblA(age,birth);
select * from tblA;

我们先看看下面几种情况:

再看看下面的情况:

总结

  • MySQL支持两种方式的排序:FileSort 和 Index,Index效率高,它指定MySQL扫描索引本身完成排序;FileSort方式效率低。
  • ORDER BY 满足两种情况,会使用Index方式排序:
    • ORDER BY 语句使用索引最左前列
    • 使用 WHERE 子句与 ORDER BY 子句条件列组合满足索引最左前列

我们尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀原则。

如果不在索引列上,filesort 有两种算法:mysql就要启动双路排序单路排序

双路排序和单路排序

双路排序:Mysql4.1之前是使用双路排序,字面的意思就是两次扫描磁盘,最终得到数据,读取行指针和ORDER BY列,对他们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段,在buffer进行排序,再从磁盘读取其他字段。文件的磁盘IO非常耗时的,所以在Mysql4.1之后,出现了第二种算法,就是单路排序。

执行过程:

以 select * from employees where name = LiLei order by position; 为例

  1. 从索引 name 中找到第一个满足 name = Lilei 的数据id
  2. 根据id从主键索引中取出所有字段,放入到sort_buffer中
  3. 从索引name 中找到下一个满足 name = Lilei 的数据id
  4. 重复第2、3步,知道没有满足条件 name = Lilei 的数据
  5. 在sort_buffer中,对字段 position 进行排序
  6. 返回结果集

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

执行过程:

以 select * from employees where name = LiLei order by position; 为例

  1. 通过索引name取出第一个满足 name = LiLei 的数据id
  2. 通过id查询position和id字段,放入到sort_buffer
  3. 通过索引name取出下一个满足 name = LiLei 的数据id
  4. 重复第2、3步,直到无满足 name = LiLei 条件的数据
  5. 在sort_buffer中按照position排序
  6. 排序后,按照id从主键索引中查询需要返回的结果集
  7. 返回结果集

什么情况下会导致单路排序失效呢?

在sort_buffer中,单路排序要比双路排序占很多空间,因为单路排序把所有的字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能读取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小,再次排序…从而多次I/O。偷鸡不成蚀把米。

提高ORDER BY速度的技巧

  1. ORDER BY时不要使用SELECT *,只查需要的字段。

    • 当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法—单路排序,否则用老算法—多路排序。假设只需要查10个字段,但是SELECT *会查80个字段,那么就容易把sort_buffer缓冲区用满。

    • 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size大小。

  2. 增大sort_buffer_size参数大小
    不管用哪种算法,提高这个参数都会提高效率。当然要根据系统能力去提高,因为这个参数是针对每个进程的。

  3. 增大max_length_for_sort_data参数大小
    提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。

group by优化

  • group by实质是先排序后进行分组,遵照索引建的最佳左前缀
  • 当索引无法使用索引列,增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置
  • where高于having,能写在where限定条件就不要去having限定了

慢查询日志

说明

  • MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体值运行时间超过long_query_time值的SQL,则被记录在慢查询日志中。

  • 默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动设置这个参数

  • 当然,如果不是调优需要的话,不建议开启该设置,因为开启慢查询日志或多或少会给带来一定性能影响,慢查询日志支持将日志记录写入文件

查看并开启

查看:show variables like '%slow_query_log%';

开启:set global slow_query_log = 1;

注意:这样开启了慢查询只对当前数据库生效,如果MySQL重启后则会失效。

如果想要永久生效,就必须修改配置文件my.cnf ,然后重启服务器。

修改my.cnf 文件,[mysqld]下增加或修改参数:

1
2
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/xxx-slow.log

不过不建议这样做。

什么样的SQL才会被记录到日志中呢

这个是由参数 long_query_time 控制,默认为10秒,大于这个时间的才会被记录,等于不会被记录。

修改时间为3秒:

为什么还是10秒,我们修改一下命令 show global variables like '%long_query_time%';

或者我们重新开一个MySQL回话:

演示分析

我们先执行一个执行时间为4s的SQL语句:

然后退出来用cat命令到配置文件中查看一下:

这样我们就找到了慢SQL记录,然后我们就可以进行后续的分析。

我们还可以查询当前有多少条慢SQL,执行命令:show global status like '%Slow_queries%';

日志分析工具mysqldumpslow

实际项目中,我们肯定需要借助其他工具去分析我们的慢查询日志文件,而不是我们自己手动查看分析,这个工具就是mysqldumpslow,我们可以退出mysql查看一下它的帮助信息:

列出一些常用信息:

1
2
3
4
5
6
7
8
9
10
s:是表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后面搭配一个正则表达式,大小写不敏感的

下面列出工作常用参考:

1
2
3
4
5
6
7
8
9
10
11
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx-slow.log

另外建议在使用这些命令时结合 | 和 more 使用,否则可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | more

批量数据脚本

这一节主要完成一个任务,就是往我们的MySQL中插入50w条数据。

首先要明白函数和存储过程的区别:函数必须有返回值,而存储过程没有

好的,下面我们就开始,一步一步完成。

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 新建库
create database bigData;
use bigData;

# 1 建表dept
create table dept(
id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default "",
loc varchar(13) not null default ""
)engine=INNODB default charset=GBK;

# 2 建表emp
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=GBK;

设置参数

如果创建函数报错,由于开启过慢查询日志,因为我们开启了 bin-log,我们就必须为我们的function 指定一个参数。具体设置如下:

1
2
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

注意:这样添加了参数之后,如果MySQL重启,上述参数就会失效,如果想要永久生效,就要改写配置文件。

创建函数

创建函数,保证每条数据都不同

随机产生字符串

1
2
3
4
5
6
7
8
9
10
11
12
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_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(chars_str,FLOOR(1+RAND()*52),1));
set i = i+1;
end while;
return return_str;
end $$

随机产生部门编号

1
2
3
4
5
6
7
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = FLOOR(100+RAND()*10);
return i;
end $$

创建存储过程

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

1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter $$
create procedure insert_emp(IN START int(10),IN max_num int(10))
begin
declare i int default 0;
# 把autocommit设置成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 $$

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

1
2
3
4
5
6
7
8
9
10
11
12
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 $$

调用存储过程

dept表:

1
2
delimiter ;
call insert_dept(100,10);

emp表(插入50w条数据):

1
call insert_emp(100001,500000);

show profile

是什么?

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

默认情况下,参数处于关闭状态,并保存最近15次的运行结果

分析步骤

  1. 是否支持,查看当前版本的MySQL是否支持

    1
    show variables like 'profiling';
  2. 开启功能,默认是关闭的,需要开启

    1
    set profiling = on;

  3. 运行SQL,随便写一些SQL语句

    1
    2
    select * from dept;
    select * from emp;
  4. 查看结果,show profiles;

  5. 诊断SQL:show profile cpu,block io for query 上一步的queryid;

  6. 日常开发中需要注意的结论

    左边Status那一栏如果出现下面一些情况对应的注意事项:

    • converting HEAP to MyISAM 查询结果太大,内存都不够用了,往磁盘上搬了
    • Creating tmp table 创建临时表:数据拷贝到临时表,用完删除
    • Copying to tmp table disk 把内存中临时表复制到磁盘 危险!!!
    • locked

全局查询日志

开启,两个命令:

默认记录到 mysql 下的 general_log 表中,查看如下:

不推荐使用,推荐 show profile,功能更强大!