查询截取分析

查询优化

小表驱动大表

优化原则：小表驱动大表，即小的数据集驱动大的数据集

---------- 原理(RBO) ---------
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

-- exists 写法
select * from A where exists (select 1 from B where B.id = A.id)
-- 等价于
for select * from A
for select * from B where B.id = A.id
-- 结论：当 A 表的数据集小于 B 表的数据集时，用 exists 优于 in
-- 注意：A表和B表的ID字段应建立索引。

EXISTS：SELECT … FROM table WHERE EXISTRS(subquery)
- 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
- EXISTS(subquery)只返回TRUE或FALSE，因此子查询中的SELET * 也可以是SELECT 1 或者其他，广发说法是实际执行时会忽略SELECT清淡，因此没有区别
- EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题
- EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

order by 关键字优化 🧐

order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序

-- 数据准备：
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);

-- 用到了索引，因为带头大哥age都在
select * from tblA where age>20 order by age;
select * from tblA where age>20 order by age,birth;
select * from tblA where birth>'1997-06-27 09:00:00' order by age;
-- 出现了 filesort
select * from tblA where age>20 order by birth;
select * from tblA where age>20 order by birth，age;
select * from tblA order by age asc,birth desc;

Mysql支持两种方式的排序，FileSort和Index，Index效率高，它指Mysql扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY 满足两情况，会使用 Index 方法排序：

ORDER BY 语句使用索引最左前列
使用 Where 子句与 ORDER BY 子句条列组合满足索引最左前列

结论：

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序：

双路排序

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

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

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

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

由于单路是后出的，总体而言好过双路。但是存在以下问题:

在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序(创建 tmp 文件，多路合并)，排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。

结论：本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化策略

增大 sort_buffer_size 参数的设置

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。在1M-8M 之间调整。
增大 max_length_for_sort_data 参数的设置

mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。(1024-8192 之间调整)。
减少 select 后面的查询的字段，不要用 select

当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB（大对象集）类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size。

总结

Mysql两种排序方式：文件排序filesort和扫描有序索引排序index

Mysql能为排序与查询使用相同的索引

KEY a_b_c(a,b,c)
-- order by 能使用索引最左前缀
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc

-- 如果where使用使用的最左前缀定义为常量，则order by能使用索引(不产生filesort)
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 -- 带头大哥a比较猛，仍可以index排序

-- 不能使用索引进行排序的情况
order by a asc,b desc,c desc -- 排序不一致
where g = const order by b,c  -- 丢失a索引
where a = const order by -- 丢失b索引
where a= const order by a,d -- d不是索引的一部分
where a in (...) order by b,c -- 对于排序来说，多个相等条件也是范围查询

group by 关键字优化

group by 使用索引的原则几乎跟 order by 一致，唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引。

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

慢日志查询

是什么：

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

具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

怎么用：

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

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

开启设置

-- 查看慢查询日志是否开启
-- 默认情况下 slow_query_log 的值为 OFF， 表示慢查询日志是禁用的
SHOW VARIABLES LIKE '%slow_query_log%';
   
-- 开启慢查询日志 只对当前数据库生效，如果mysql重启后则会失效
set global slow_query_log=1;
   
-- 查看慢查询设定阈值 单位秒
SHOW VARIABLES LIKE 'long_query_time%';
   
-- 设定慢查询阈值 单位秒
set long_query_time=1;
   
-- 查看超过阈值的sql数目
show global status like '%Slow_queries%';

如永久生效需要修改配置文件my.cnf中[mysqld]下配置

   [mysqld]
   slow_query_log=1 
   # 指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log(未指定的情况下)
   slow_query_log_file=/var/lib/mysql/atguigu-slow.log 
   long_query_time=3
   log_output=FILE

运行查询时间长的sql，打开慢查询日志查看

日志分析工具 mysqldumpslow

# 查看mysqldumpslow的帮助信息
mysqldumpslow --help
# s表示按照何种方式排序 		c访问次数		 l锁定时间
# r返回记录		t查询时间		al平均锁定时间		ar平均返回记录数
# at平均查询时间		t返回前面多少条的数据		g后边搭配一个正则匹配模式，大小写不敏感
# 得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
# 得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
# 得到按照时间排序的前 10 条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
# 另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SHOW PROCESSLIST

查询 mysql 进程列表，可以杀掉故障进程

show processlist;
kill 6;

批量数据脚本

-- 1. 建表
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;
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=utf8;

-- ⚠️ 2. 在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。
-- 查询/设置
-- 如要永久生效，需要写入到配置文件中。在[mysqld]中加上 log_bin_trust_function_creators=1
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

-- 3. 随机产生字符串。 如果要删除函数，则执行:drop function rand_string;
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 $$
-- 4. 随机产生部门编号。 如果要删除函数:drop function rand_num;
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10); 
RETURN i;
END $$

-- 5. 创建存储过程
-- 创建往 emp 表中插入数据的存储过程.  删除 drop PROCEDURE insert_emp;
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 表中插入数据的存储过程
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 $$

-- 6. 调用存储过程
-- 添加数据到部门表
DELIMITER ;
CALL insert_dept(100,10); -- 插入10个部门
-- 添加数据到员工表
DELIMITER ; 
CALL insert_emp(100001,500000); -- 插入50w员工

Show Profile 🧐

show profile 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况；可以用于SQL的调优测量。

利用 show profile 可以查看 sql 的执行周期！

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

开启 profile

-- 查看 profile 是否开启：
show variables like '%profiling%';
-- 如果没有开启，可以执行以下语句开启 
set profiling=1;

使用 profile

-- 查看最近的几次查询。
show profiles;
-- 根据Query_id查看 sql 的具体执行步骤
show profile cpu,block io for query Query_id;

各字段解释：

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

大致的查询流程

mysql 客户端通过协议与 mysql 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，

否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储 SELECT 语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

语法解析器和预处理:首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树”。mysql 解析器将使用 mysql 语法规则验证和解析查询;预处理器则根据一些 mysql 规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。

然后，mysql 默认使用的 BTREE 索引，并且一个大致方向是:无论怎么折腾 sql，至少在目前来说，mysql 最多只用到表中的一个索引。

需要注意的结论

converting HEAP to MyISAM 查询结果太大，内存都不够用了需要往磁盘上搬
Creating tmp table 创建临时表
- 拷贝数据到临时表
- 删除数据
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险⚠️！！
locked

全局查询日志

⚠️！！只允许在测试环境下使用，永远不要在生产环境开启这个功能。

开启方式：

配置文件方式：在mysql的my.cnf中添加

# 开启
general_log=1
# 记录日志文件路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE

命令方式：

set global general_log=1;
set global log_output='TABLE';
-- 此后，所编写的sql语句将会被记录到mysql库里的general_log表，可以用一下命令查看
select * from mysql.general_log;

MySQL数据库高级查询截取分析

MySQL数据库高级查询截取分析

查询截取分析

查询优化

小表驱动大表

order by 关键字优化 🧐

优化策略

总结

group by 关键字优化

慢日志查询

是什么：

怎么用：

日志分析工具 mysqldumpslow

SHOW PROCESSLIST

批量数据脚本

Show Profile 🧐

开启 profile

使用 profile

大致的查询流程

需要注意的结论

全局查询日志

开启方式：

MySQL数据库高级 查询截取分析

查询截取分析

查询优化

小表驱动大表

order by 关键字优化 🧐

优化策略

总结

group by 关键字优化

慢日志查询

是什么：

怎么用：

日志分析工具 mysqldumpslow

SHOW PROCESSLIST

批量数据脚本

Show Profile 🧐

开启 profile

使用 profile

大致的查询流程

需要注意的结论

全局查询日志

开启方式：

MySQL数据库高级查询截取分析