索引什么时候失效 和 如何排查慢查询

1.索引什么时候失效？

经常在编写代码时候出现慢查询问题，这说明很多时候索引没有使用对。

记忆口诀：模糊匹配、类型隐转、最左匹配，这三种情况可能导致索引会失效。

以%开头的LIKE语句，模糊搜索。索引没有办法去利用，因为B+树的key没有办法再去直接比较了。

出现隐式类型转换，需要注意参数类型跟SQL查询时候类型是不是一样的，因为两个类型不同没法比较（在 Python 这种动态语言查询中需要注意）

没有满足最左前缀原则（想想为什么是最左匹配？最左前缀原则，如果是多列索引的时候需要满足最左前缀才能利用上索引。例如：多列索引有三个值a, b, c三个字段，如(a, b, c)。有两个key值(1, 2, 3) 和 (1, 2, 4)作比较，先比较第一个都是1相等，再比交第二个都是2相等，最后比较第三个3小于4 ，这时候就知道二叉树往哪个方向。假如不满足最左匹配就没有办法直接比较，如 (a,b,c), (a,b), (a),这三种情况下都是可以使用索引的，但是(b,c)就不可以，因为连第一个比较的基准都没有，根本没有办法再往后比较了）

三种情况总结成一句话，当Mysql B+树的key没有办法去比较的时候，索引就会失效。

2.如何排查慢查询？

   慢查询通常是缺少索引，索引不合理或者业务代码实现导致

① 开启并且查询慢查询日志

         开启慢查询功能

     slow_query_log_file 慢查询日志文件路径

     slow_query_log 用于指定是否打开慢查询日志

     long_query_time 超过多少秒的查询就写入日志

② 通过 explain 排查索引问题

③ 调整数据修改索引；业务代码层限制不合理访问，如：因为数据类型不匹配导致全表扫描；一次获取太多数据等

3.查找分析查询速度慢的原因

   分析SQL查询慢的方法：

     ① 记录慢查询日志。

          分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用 pt-query-digest 工具进行分析。

    ② 使用 show profile。

          开启 set profiling = 1，服务器上执行的所有语句会检测消耗时间，存到临时表中。

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

          show profiles ，查看临时表

          show profile for query 临时表ID，通过临时表的主键ID去查一条sql语句是在什么地方发生慢的操作。

mysql> show profiles;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.00315025 | SELECT DATABASE() |
|        2 | 0.04271325 | select * from a   |
+----------+------------+-------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.001101 |
| checking permissions | 0.000016 |
| Opening tables       | 0.037732 |
| init                 | 0.000040 |
| System lock          | 0.000021 |
| optimizing           | 0.000006 |
| statistics           | 0.002043 |
| preparing            | 0.000025 |
| executing            | 0.000003 |
| Sending data         | 0.001608 |
| end                  | 0.000006 |
| query end            | 0.000007 |
| closing tables       | 0.000013 |
| freeing items        | 0.000071 |
| cleaning up          | 0.000022 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

   ③ 使用show status

         show status会返回一些计数器，show global status查看服务器级别的所有计数。

        有时根据这些计数，可以猜测出哪些操作代价较高或者消耗时间多

   ④ 使用show processlist

        观察是否有大量线程处于不正常的状态或者特征

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost:14778 | test | Query   |    0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.01 sec)

  ⑤ 使用 explain(或 使用别名DESC)

       分析单条SQL语句，注意格式化 \G 输出后不需要加分号。

mysql> explain select * from a;
+----+-------------+-------+------------+------+---------------+------+---------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len |
+----+-------------+-------+------------+------+---------------+------+---------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    |
+----+-------------+-------+------------+------+---------------+------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from a\G                      -- 注意:格式化输出内容，\G相当于;
*************************** 1. row **********
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)