常见优化方法及慢查询

exist 和 in

select  ...from table where exist  (子查询)；

select ....from table where 字段 in (子查询);

如果主查询的数据集大，则使用In    效率高

如果子查询的数据集大，则使用exist   效率高

 exist用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
 exist 指定一个子查询，检测行的存在。

order by 优化

using filesort (额外的一次排序) 有两种算法：双路排序、单路排序（根据IO的次数）

Mysql4.1之前  默认 使用  双路排序：扫描2次磁盘（1：从磁盘读取排序字段，对排序字段进行排序 2：扫描其他字段）

                                             -----------IO较消耗性能

Mysql4.1之后  默认 使用  单路排序： 只读取一次（全部字段），在buffer中进行排序（一个缓冲区）  但也有一定的隐患

     ------如果数据量特别大，则无法将所有字段的数据  一次性读取完毕，因此会进行“分片读取，多次读取”；

      ------单路排序  比  双路排序   会占用更多的buffer；

    --------可以考虑调大buffer的容量大小：set  max_length_for_sort_data = 1024  单位byte

提高order by 查询的策略：

1. 选择使用单路、双路；调整buffer的容量大小；
2. 避免select * ...  最好能确定查询的字段
3. 复合索引  不要跨列使用，避免using filesort
4. 保证全部的排序字段  排序的一致性（都是升序  或  降序）

SQL排查  --  慢查询日志      Mysql提供的一种日志记录，用于记录mysql响应时间超过阀值的sql语句（超过10秒）

慢查询日志默认是关闭的： 建议是开发调试   打开  ； 最终部署   关闭

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

   临时开启：

                       set  global  show_query_log  =  1;          -----在内存中开启

                       exit

                       service  mysql  restart

   永久开启：

                      /ect/my.cnf  中追加配置

                      vi  /etc/my.cnf                  打开文件

                      [mysqld]                    在这个文件后面追加两句话  如下：

                      slow_query_log = 1

                      slow_query_log_file = /var/lib/mysql/localhost-slow.log

慢查询阀值：

                    show variables  like  '%long_query_time%'  ;    查看

临时设置阀值：

                   set  global long_query_time  = 5;    ---设置完毕后，重新登陆有效

永久设置阀值：         

                       /ect/my.cnf  中追加配置

                      vi  /etc/my.cnf                  打开文件

                      [mysqld]                    在这个文件后面追加两句话  如下：

                      long_query_time  = 5;

(1)

--------查询超过阀值的SQL：   show  global  status like '%slow_queries%'

--------慢查询的sql被记录在了日志中，因此可以通过日志  查看具体的慢SQL

                 cat  /var/lib/mysql/localhost-slow.log

(2)

-------通过mysqldumpslow工具查看慢SQL，可以通过一些过滤条件，快速查找出需要定位的慢SQL

mysqldumpslow  --help

s: 排序方式                               r: 逆序                           l: 锁定时间                        g: 正则匹配模式

----获取返回记录最多的3个sql

                                   mysqldumpslow  -s  r  -t  3  /var/lib/mysql/localhos-slow.log

----获取访问次数最多的3个sql

                                   mysqldumpslow  -s  c -t  3  /var/lib/mysql/localhos-slow.log

----按照时间排序，前10条包含lefr join查询语句的SQL

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