慢SQL的优化建议

慢SQL的优化建议

前言：在工作过程中，本人在处理天眼查数据时遇到了不少慢SQL的问题。此外，也结合同事工作中遇到的问题，在如何优化大数量查询时积累了一点点经验，做一些整理，不足之处，希望一起探讨改进。

1.关于慢SQL日志

（1）设置开启：SET GLOBAL slow_query_log = 1;　　　#默认未开启，开启会影响性能，mysql重启会失效
（2）查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
（3）设置阈值：SET GLOBAL long_query_time=3;
（4）查看阈值：SHOW 【GLOBAL】 VARIABLES LIKE 'long_query_time%';　　#重连或新开一个会话才能看到修改值
（5）通过修改配置文件my.cnf永久生效，在[mysqld]下配置：
　　[mysqld]
　　slow_query_log = 1;　　#开启
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日志地址，缺省文件名host_name-slow.log
　　long_query_time=3;　　  #运行时间超过该值的SQL会被记录，默认值>10
　　log_output=FILE

2.关于SQL语句

1.SQL中尽量避免范围查询，如果必须使用，应当放在AND条件的最后使用。

错误示例

  <if test="begin != null and begin != '' and end != null and end != ''" >
    t.`year_month` between #{begin,jdbcType=VARCHAR} AND  #{end,jdbcType=VARCHAR}
  </if>
  <if test="mobileNumber != null and mobileNumber != ''">
    AND mobile_number = #{mobileNumber,jdbcType=VARCHAR}
  </if>

正确示例

    <if test="mobileNumber != null and mobileNumber != ''">
         mobile_number = #{mobileNumber,jdbcType=VARCHAR}
    </if>
    <if test="begin != null and begin != '' and end != null and end != ''" >
          AND (t.`year_month` between #{begin,jdbcType=VARCHAR} AND  #{end,jdbcType=VARCHAR})
    </if>

2.SQL中尽量避免在索引列上使用运算函数，这样可能会导致不走索引，且查询变慢。部分聚合函数可以走索引，需要具体问题，具体分析。

3.大数据量时，SQL中无法使用like进行模糊查询，尤其不能使用前模糊，这样无法走索引。百万级别数据可以尝试建立全文索引。全文索引如果无法建立，使用ElasticSearch,将需要查询的字段放入ES进行查询，根据返回结果取匹配度最大的数据。

全文索引建立方式：MySQL版本应在5.6及以上
CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)
全文索引使用方式：
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

4.SQL中索引列应尽量避免为null值，虽然有null值仍然可以建立索引，但是null过多查询效率很低，维护索引反而很浪费资源。可以将null默认为一个特定的不影响数据本身的值。

5.查询大量数据时，如果有or条件连接，会造成后面无法走索引，将or修改为union all可以走索引，进而提高查询效率

网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况，其中有一种说法就是指使用了 or 的语句都不能命中索引。
这种说法其实是不够正确的，正确的结论应该是，从 mysql5.0 后，如果在 or 连接的字段上都有独立的索引的话，是可以命中索引的，这里就是用到了 index_merge 特性。

在 mysql5.0 版本以前一条 sql 只能选择使用一个索引，而且如果 sql 中使用了 or 关键字，那么已有的索引就会失效，会走全表扫描。因为无论走哪个索引，mysql 都不能一次性查找出符合条件的数据，所以只能放弃索引。

索引最左前缀（多列索引）遇到范围查询后，后面的字段不走索引。如果每列都建立索引，范围查询后的索引字段也是可以生效的。

6.评估查询出来的数据量是否会出现堆溢出的可能性，如果有，需要换一种实现方式，将一些查询匹配在数据库查询阶段完成。

7.此外，索引的生效和查询条件有关，不同输入参数会导致索引使用情况不同，比如时间范围索引，输入时间跨度很大接近全表扫描时，不走索引。

8.查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。 【注意是可能，和输入条件有关】

比如，你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择TABLE ACCESS FULL，但是如果换成了SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中找到Y的值，因为从B树中就可以找到相应的值。

3.关于SQL查询优化 explain

explain命令用来查看一个SQL语句的执行计划，查看该SQL语句有没有使用上索引，有没有做全表扫描。expain出来的信息有10列，分别是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

1.type表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

ALL

Full Table Scan， MySQL将遍历全表以找到匹配的行

range

只检索给定范围的行，使用一个索引来选择行

const、system

 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

2.possible_keys 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

如果该列是NULL，则没有相关的索引。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

3.key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。

4.rows

表示MySQL根据表统计信息及索引选用情况，估算找到所需的记录所需要读取的行数