慢查询SQL如何优化

一.什么是慢SQL?

慢SQL指的是Mysql中执行比较慢的SQL,排查慢SQL最常用的方法是通过慢查询日志来查找慢SQL。Mysql的慢查询日志是Mysql提供的一种日志记录，它用来记录Mysql中响应时间超过long_query_time值的sql,long_query_time的默认时间为10s.

二.查看慢SQL是否开启

我么可以使用show variables like '%slow_query_log%'来查看慢查询日志是否开启。

 当slow_query_log   value值为on时，表示慢查询日志功能开启。

慢查询日志如何开启

开启慢查询日志，可以使用如下 MySQL 命令：

mysql> set global slow_query_log=1

但是这种方式只对当前数据库生效，MySQL 一旦重启也会失效，如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf，配置如下：

slow_query_log =1

slow_query_log_file=/tmp/mysql_slow.log

三.SQL性能下降的原因

1.等待时间长

锁表导致查询一直处于等待的状态

2.执行时间长

    （1）索引失效

     （2）查询语句写的差

    （3）关联太多的join

       （4）服务器调优以及各个参数的设置

四.慢查询优化思路:

1.优先选择优化高并发执行的sql,因为高并发的sql产生的问题更加严重

2.定位优化对象的性能瓶颈

(1)IO(数据访问消耗了太多的时间，查看是否使用了索引）

(2)CPU(数据运算花费了太多时间，数据的运算分组，排序是不是有问题）

 (3）网络带宽（加大网络带宽）

3.明确优化目标

4.explain执行计划入手

explain能告诉我们当前sql的执行状态

其中最重要的就是 type 字段，type 值类型如下：

• all — 扫描全表数据
• index — 遍历索引
• range — 索引范围查找
• index_subquery — 在子查询中使用 ref
• unique_subquery — 在子查询中使用 eq_ref
• ref_or_null — 对 null 进行索引的优化的 ref
• fulltext — 使用全文索引
• ref — 使用非唯一索引查找数据
• eq_ref — 在 join 查询中使用主键或唯一索引关联
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

当type的值为all的时候，说明我们索引没走，走的是全表扫描

另外通过其他的一些字段我们可以了解:

(1)表的读取顺序

（2）数据读取操作的操作类型

（3）那些索引可以被使用

（4）那些索引真正被使用

 （5）表的直接引用

 （6）每张表有多少行被优化器查询了

5.永远用小结果驱动大的结果集

 用小表驱动大表

这里我们举个例子:

如果小的循环在外层，那么连接数据库只需要10次，如果大的循环在外边，那么我们连接数据库就需要100次，这样就浪费了资源。

6.尽可能在索引中完成排序

  当我们需要对查询的语句order by的时候，如果order by后面的字段如果在索引列中，因为索引本来就是排好序的，所以速度很快，没有索引的话，就需要从表中拿数据，在内存中进行排序，如果内存空间不够还会发生落盘操作

7.只获取自己需要的列

在进行sql语句查询的时候，我们尽量避免直接select*from 表名，我们需要啥列则获取啥列

8.只使用最有效的过滤条件

并不是where 后面的条件越多越好

9.尽量避免复杂的join连接

10.检查表中数据是否过多，是否需要分库分表了

11.检查数据库实例所在的机器的性能配置，是否太低，可以适当增加资源

12.合理设计并使用索引

如何判定是否需要创建索引？

（1）如果查询的某些字段较为频繁，我们应该为他们设计索引

 （2）更新很频繁的字段不适合创建索引（索引的字段被更新，索引数据也需要更新）

  （3）不会出现在where子句的字段不应该创建索引

 （4）唯一性太差的字段不适合创建索引