慢查询与 Explain 优化

慢查询

慢查询日志由long_query_time执行时间超过几秒的SQL语句组成.

慢查询日志可用于查找需要很长时间才能执行的查询，因此是优化的候选者。

• 慢查询配置

-- // 查看“慢查询”的配置信息
show variables like "%slow%"; 
 -- // 查看“慢查询”的时间定义
show variables like "long%";  

 -- //设置“慢查询”的时间定义
set long_query_time=2;
-- //开启慢日志
set global slow_query_log='ON';

• 查询慢查询日志的开启状态和慢查询日志储存的位置

mysql> show variables like "%slow%";
+---------------------------+-----------------------------------------------+
| Variable_name             | Value                                         |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF                                           |
| log_slow_slave_statements | OFF                                           |
| slow_launch_time          | 2                                             |
| slow_query_log            | OFF                                           |
| slow_query_log_file       | F:\mysql-5.7.25\data\DESKTOP-7TLR5VO-slow.log |
+---------------------------+-----------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

• 查看慢查询日志

Explain

根据表，列，索引和WHERE子句中的条件的详细信息，MySQL优化器会考虑许多技术来有效地执行SQL查询中涉及的查找。优化程序选择执行最有效查询的操作集称为“ 查询执行计划 ”，也称为 EXPLAIN计划。

EXPLAIN返回SELECT语句中使用的每个表的一行信息 。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。

EXPLAIN输出列 官方文档说明

mysql> explain select * from orders where order_num = 20005\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

MySQL索引使用的注意事项

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。

1.不要在列上使用函数和进行运算

不要在列上使用函数，这将导致索引失效而进行全表扫描。

select * from news where year(publish_time) = 2017

为了使用索引，防止执行全表扫描，可以进行改造。

select * from news where publish_time = '2017-01-01'

还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描。

select * from news where id / 100 = 1

为了使用索引，防止执行全表扫描，可以进行改造。

select * from news where id = 1 * 100

2.尽量避免使用 != 或 not in或 <> 等否定操作符

应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因为这几个操作符都会导致索引失效而进行全表扫描。

3.尽量避免使用 or 来连接条件 ?

应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描。

select * from news where id = 1 or id = 2

4.多个单列索引并不是最佳选择

MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。

假设，有两个单列索引，分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：

select * from news where news_year = 2017 and news_month = 1

事实上，MySQL 只能使用一个单列索引。为了提高性能，可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。

5.复合索引的最左前缀原则

复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

假设，有一个场景只需要针对资讯的月份进行查询，那么，SQL 语句可以写成：

select * from news where news_month = 1

此时，无法使用 news_year_month_idx(news_year, news_month) 索引，因为遵守“最左前缀”原则，在查询条件中没有使用复合索引的第一个字段，索引是不会被使用的。

6.覆盖索引的好处

如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

7.范围查询对多列索引的影响(组合索引)

查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。

举个例子，假设有一个场景需要查询本周发布的资讯文章，其中的条件是必须是启用状态，且发布时间在这周内。那么，SQL 语句可以写成：

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

这种情况下，因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说，news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 。

对于这种情况，我的建议：对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。

例如，上面案例的需求是查询本周发布的资讯文章，因此可以创建一个news_weekth 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询，SQL 可以改写成：

select * from news where  news_weekth = 1 and enable = 1

然而，并不是所有的范围查询都可以进行改造，对于必须使用范围查询但无法改造的情况，我的建议：不必试图用 SQL 来解决所有问题，可以使用其他数据存储技术控制时间轴，例如 Redis 的 SortedSet 有序集合保存时间，或者通过缓存方式缓存查询结果从而提高性能。

8.索引不会包含有NULL值的列 ?

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。

因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。

9.隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

select * from news where date_str = 201701    

因此，要谨记隐式转换的危害，时刻注意通过同类型进行比较。

10.like 语句的索引失效问题

like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案。

--为什么下列SQL不能命中phone索引？

select uid from user where phone=13811223344;

总结

• SQL语句优化
  • 避免嵌套查询(子查询)
  • 避免多表查询
• 索引优化
  • 适当建立索引
  • 合理使用索引
    • 以上所讲的索引的使用
• 数据库结构优化
  • 列,表,库
• 系统配置优化
  • 对mysql各项配置进行调优
• 服务器硬件优化
  • 读写分离(主从复制)

【绽放吧！数据库】有奖征文火热进行中：https://bbs.huaweicloud.com/blogs/285617