005-sql慢查询日志及优化建议

摘要：优化

慢查询日志

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能

参数说明：

slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录

设置步骤

1. 查看基础参数

   show variables like 'slow_query%';
   -- Variable_name  Value
   -- slow_query_log	OFF
   -- slow_query_log_file	/var/lib/mysql/2a623b03f48a-slow.log
   
   show variables like 'long_query_time';
   -- Variable_name  Value
   -- long_query_time	10.000000

2. 设置方法

• 方法一：全局变量设置【即时性的，重启mysql之后失效，常用的】
  将 slow_query_log 全局变量设置为“ON”状态

  set global slow_query_log=1; 或者 set global slow_query_log=ON;

  设置慢查询日志存放的位置

  set global slow_query_log_file='/var/log/mysql/slow.log';

  查询超过1秒就记录

  set global long_query_time=1;

• 方法二：配置文件设置【永久性的】
  修改配置文件my.cnf，在[mysqld]下的下方加入

  [mysqld]
  slow_query_log = ON
  slow_query_log_file = /var/log/mysql/slow.log
  long_query_time = 1

3. 服务重启

   service mysqld restart

   可以使用步骤1中的查看

慢sql分析

打开文件

# Time: 200219  9:09:58
# User@Host: root[root] @  [172.17.0.1]  Id:     7
# Query_time: 5.142734  Lock_time: 0.000080 Rows_sent: 5  Rows_examined: 5000000
SET timestamp=1582103398;
select * from table500w where username='name-23233';

查询的时间，用户，花费的时间，使用的数据库，执行的sql语句等信息。在生产上我们就可以使用这种方式来查看 执行慢的sql

查询慢查询的次数

show status like 'slow_queries';

优化及示例

• 提前过滤
  Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

• 用EXISTS替代IN、用NOT EXISTS替代NOT IN。

• 避免在索引列上使用计算

• 避免在索引列上使用IS NULL和IS NOT NULL

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

超大分页怎么处理?

示例分析

基础分页-慢-大概5s

select * from table500w where age>5 limit 4000000,5;

查看慢sql

# Time: 200219 23:47:01
# User@Host: root[root] @  [172.17.0.1]  Id:    10
# Query_time: 5.141059  Lock_time: 0.000086 Rows_sent: 5  Rows_examined: 4255328
SET timestamp=1582156021;
select * from table500w where age>5 limit 4000000,5;

标准大分页 慢sql

基础分页-优化-1s内

1. 查看执行计划

   explain select * from table500w where age>5 limit 4000000,5;
   -- Id  select_type table type  possiable_key key key_len ref rows  Extra
   -- 1 	SIMPLE	    table500w	  ALL					                  4967713	Using where

   参看:004-mysql explain详解
   发现：简单查询，使用all 全表扫描，涉及行数 4967713

2. 优化-条件增加索引

   alter table table500w add index index_age(age);
   explain select * from table500w where age>5 limit 4000000,5;
   -- Id  select_type table  type  possiable_key   key key_len ref rows  Extra
   -- 1	  SIMPLE	table500w	range	index_age	  index_age	5		      2483856	Using index condition; Using MRR

   发现：简单查询，使用range 索引范围扫描，涉及行数 2483856
   执行查询

   select * from table500w where age>5 limit 4000000,5;

   发现根本查询不出来了，或者查询巨慢
   原因查看：
   上述语句可以理解为

   1. 命中非聚簇索引,通过age 查询 主索引 id
   2. 回读主索引，即聚簇索引查询主索引，然后获取叶子节点数据
   3. 根据limit 取结果

3. 利用关联子查询

   EXPLAIN
   select b.* from table500w b, (select id from table500w where age>5 limit 4000000,5)as a WHERE b.id=a.id;
   -- Id  select_type table    type    possiable_key   key     key_len ref rows  Extra
   -- 1	PRIMARY	    <derived2>ALL					                                2483856	
   -- 1	PRIMARY	    b	        eq_ref	PRIMARY	      PRIMARY	   8	    a.id	1	
   -- 2	DERIVED	    table500w	range	  index_age    	index_age	 5		      2483856	Using where; Using index

   查询后速度：0.88s
   from 取出 a结果 5行，b 表 按条件 筛选 5行。

优化总结

原因：mysql 并不是跳过offset行，而是取 offset + N 行，然后返回，放弃offset行，返回N行，那当offset特别大的时候，效率非常低。
超大的分页一般从两个方向上来解决.

• 数据库层面,快速定位需要获取的id段，然后在关联

  select b.* from table500w b, (select id from table500w where age>5 limit 4000000,5)as a WHERE b.id=a.id;

• 需求界面上控制总页数
  不允许直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.