查询截取分析

查询优化

永远小表驱动大表

1. in 查询

   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。B表驱动A表。
2. 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表。
   select ... from table where exists(subquery)
   该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结构（TRUE和FALSE）来决定主查询的数据结果是否得以保留。
3. 提示
   1. exists(subquery) 只返回true和false，因此子查询中的select *也可以是select 1或其他，官方说法是实际执行时会忽略select清单，因此没有区别
   2. exists子查询的实际执行过程可能经过了优化而不是我们理解的逐条对比，如果担忧效率问题，可以实际检验以确定是否有效率问题。
   3. exists子查询往往也可以用条件表达式，其他子查询或者join来代替，何种最优需要具体问题具体分析

order by 关键字优化

1. order by 子句，尽量按照索引字段顺序排序，避免让执行计划中出现 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);
    
   select *from tblA;
   

   
   
   MySQL支持两种排序方式，index 和 FileSort。使用index排序方式，效率高，它指的是MySQL扫描索引本身完成排序。FileSort是文件内排序，效率低下。
   Order By子句满足两种条件，会使用index方式排序。order by 子句后面的字段顺序，遵循了创建索引时的最左前缀法则；where 子句后的字段顺序与order by 子句后面的字段顺序组合，满足最左前缀法则。
   遵循最左前缀法则，尽可能在索引上完成排序。

2. 针对出现FileSort排序，会涉及到两种排序：双路排序和单路排序。

   MySQL 4.1之前，FileSort排序使用的是双路排序算法。双路排序需要有两次磁盘IO操作，才能获取到最终的数据。首先读取行指针和order by列，对它们进行排序。然后扫描排好序的列表，按照列表中的值重新从列表中读取想要的数据。

   MySQL 4.1之后，FileSort排序使用的是单路排序算法。从磁盘上读取查询需要的所有列，按照order by 列在buffer缓冲区上对它们进行排序，然后扫描排序列表将结果输出。它的效率更高一些，避免了第二次取数据。并且把随机IO变成了顺序IO，但是会使用到更多的内存空间，因为它把每一行数据都保持在内存中。

总之，单路排序整体好过双路排序。但是，有时候也有可能会有问题。比如，sort_buffer参数的值小于单次查询所需要的内存，就会导致每次只能取sort_buffer容量大学校的数据，进行排序。排序完后再次取sort_buffer容量的数据，再排序。最后合并排序结果。因此，每次取sort_buffer容量的数据，都会产生一次IO操作，所以会有多次IO操作。导致效率低下。

此时，可以适当增大sort_buffer_size和max_length_for_sort_data参数的值，避免单路排序情况下的多次IO操作。
3. 使用 Order by 排序时，使用 select * 是一个大忌。只查询需要的字段，这点非常重要。否则，可能导致

1. 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用改进后的算法一一单路排序，否注用老算法一一多路排序。
2. 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并，导致多次I/O，但是用单路排序的风险更大一些，所以要提高sort_buffer_size。

group by 关键字优化

1. group by 子句的实质是先排序，后分组。所以，group by 子句中的字段最好遵循最左前缀法则。
2. 当无法使用索引列，增大max_length_for_sort_data参数 和 sort_buffer_size参数的设置
3. where 优先级高于having， 能写在 where 限定的条件下就不要去写 having 限定了

慢查询日志

什么是慢查询日志

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

怎么看慢查询日志

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

2. 查看慢查询日志是否开启，以及如何开启

   show variables like '%slow_query_log%'; # 查看慢查询日志是否开启
   

   
   开启慢查询日志

   set global slow_query_log=1 # 只对当前数据库生效，重启后失效
   

   
   如果要永久生效，就必须修改配置文件my.cnf（其他系统变量也是如此）
   修改my.cnf文件，【mysqld】加或修改参数
   slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将入下两个配置进my.cnf文件

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

   关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

3. 查看什么样的SQL会记录到慢查询日志中

   show variables like 'long_query_time%';
   

   

4. 修改MySQL慢查询日志配置

   my.cnf文件
   [mysqld]下配置
   slow_query_log=1;
   slow_query_log_file=配置慢查询日志路径
   long_query_time=3;
   log_output=file
   

慢日志查询工具 mysqldumpslow

在生产环境中，如果要手工分析日志，查找，分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
参数：

-s: 按照何种方式排序
-c: 访问次数
-I: 锁定实际
-r: 返回记录
-t: 查询时间
-al: 平均锁定时间
-ar: 平均返回记录数
-at: 平均查询时间
-t: 返回前面多少条数据
-g: 正则表达式
案例

1. 得到返回记录集最多的10个SQL

   mysqldumpslow -s r -t 10 /var/lib/mysql/ubuntu-slow.log
   

2. 得到访问次数最多的10个SQL

   mysqldumpslow -s c -t 10 /var/lib/mysql/ubuntu-slow.log
   

3. 得到按照时间排序的前10条里面含有左连接的查询语句

   mysqldumpslow -s r -t 10 -g "left join" /var/lib/mysql/ubuntu-slow.log
   

4. 另外建议在使用这些命令时，结合|和more使用，否则有可能出现爆屏情况

   mysqldumpslow -s r -t 10 /var/lib/mysql/ubuntu-slow.log | more
   

Show Profile

Show Profile 是什么

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

Show Profile的使用步骤

1. 查看当前MySQL是否开启Show Profile

   show variables like 'profiling';
   或者
   show variables like 'profiling%';
   

   
2. 开启 Show Profile 功能

   set profiling=on;
   

3. 显示当前会话中，所有执行过的SQL以及执行时长

   show profiles;
   

4. 显示SQL语句执行的详细过程

   show profile cpu, block io for query id; # id值为执行上面show profiles语句中，显示的id值
   

   show profile后可以加的参数：
   
5. 第4的结果中，如果出现下面四种情况之一，都需要特别注意。
   

全局查询日志

永远不要在生产环境开启该功能

1. 在 mysql 的 my.cnf 中，设置如下：

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

2. 使用命令方式开启

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

   你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看