MySQL查询截取的深入分析

一、查询优化

1，mysql的调优大纲

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

2，小表驱动大表

　　mysql的join实现原理是，以驱动表的数据为基础，“嵌套循环”去被驱动表匹配记录。驱动表的索引会失效，而被驱动表的索引有效。

#假设 a表10000数据，b表20数据select * from a join b on a.bid =b.id

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

#采用in则是，内表B驱动外表Aselect * from A where id in (select id from B)#采用exists则是，外表A驱动内表Bselect * from A where exists(select 1 from B where B.id = A.id)

永远记住小表驱动大表当 B 表数据集小于 A 表数据集时，使用 in当 A 表数据集小于 B 表数据集时，使用 exist

orderby未命中索引的情况

MySQL支持两种排序方式：Using index和Using filesort。filesort效率较低，而要使用index方式排序需满足两种使用条件尽可能在索引列上完成排序操作，遵照索引的最佳左前缀
- order by语句自身使用索引的最左前列
- 使用where子句与order by子句条件列组合满足最左前列
如果order by不在索引列上，会使用filesort算法：双路排序和单路排序
- MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
- 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

select * from user where name = "zs" order by age#双路排序1)从 name 找到第一个满足 name = 'zs' 的主键id2)根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中3)从name 取下一个满足 name = 'zs' 记录的主键 id4)重复 2、3 直到不满足 name = 'zs'5)对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序6)遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端#单路排序1)从name找到第一个满足 name ='zs' 条件的主键 id2)根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中3)从索引name找到下一个满足 name = 'zs' 条件的主键 id4)重复步骤 2、3 直到不满足 name = 'zs'5)对 sort_buffer 中的数据按照字段 age 进行排序，返回结果给客户端

问题： 由于单路是改进的算法，总体而言好过双路 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。优化策略： 增大sort_buffer_size参数的设置 增大max_length_for_sort_data参数的设置注意事项:　　Order by时select *是一个大忌，只Query需要的字段。因为字段越多在内存中存储的数据也就也多，这样就导致每次I/O能加载的数据列越少。

二、慢查询日志

1，慢查询日志是什么?

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

2，慢查询日志的开启

　　默认情况下，MySQL的慢查询日志是没有开启的。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会影响到性能，慢查询日志支持将日志记录写入文件。

a）开启慢查询日志

#查看是否开启慢日志show variables like 'slow_query_log%';#开启慢查询日志，想要永久有效在my.cnf中设置set global slow_query_log = 1 ;

#查看慢查询日志的阈值时间 默认为10sshow variables like 'long_query_time%';#设置为3s 重启失效，想要永久有效在my.cnf中设置set global long_query_time = 3#再次查看，需要切换窗口查看show variables like 'long_query_time%';

[mysqld]#持久化慢查询日志slow_query_log=1；slow_query_log_file=/var/lib/mysql/hadoop102-slow.loglong_query_time=3；log_output=FILE

#查询等待4sselect sleep(4);

show global status like '%Slow_queries%';

-s：是表示按何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t：即为返回前面多少条的数据
-g：后边搭配一个正则匹配模式，大小写不敏感的

b）常用方法

#得到返回记录集最多的10个SQLmysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log#得到访问次数最多的10个SQLmysqldumpslow -s c -t 10 /var/lib/mysql/hadoop102-slow.log#得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log#这些命令时结合 | 和more使用mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log | more

#查看binlog状态show variables like 'log_bin%';#添加可以信任存储函数创建者set global log_bin_trust_function_creators = 1;

随机产生字符串的函数

# 定义两个 $$ 表示结束 (替换原先的;)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 $$

delimiter $$create function rand_num() returns int(5)begin declare i int default 0; set i=floor(100+rand()*10); return i;end $$

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

#查看 Show Profile 是否开启show variables like ‘profiling%';#开启 Show Profileset profiling=on;

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;select * from empselect * from deptselect * from emp left join dept on emp.deptno = dept.deptno

执行 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：显示交换次数相关开销的信息

返回结果

converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table：创建临时表，mysql 先将拷贝数据到临时表，然后用完再将临时表删除
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
locked：锁表

五、全局查询日志

　　切莫在生产环境配置启用

在my.cnf中配置

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

set global general_log=1;set global log_output='TABLE';

select * from mysql.general_log;