mysql 分组查询慢_MySQL 慢查询的特征表现及优化方式

我们将超过指定时间的SQL语句查询称为慢查询

一、慢查询的体现

慢查询主要体现在慢上，通常意义上来讲，只要返回时间大于 >1 sec上的查询都可以称为慢查询。

慢查询会导致CPU，内存消耗过高。数据库服务器压力陡然过大，那么大部分情况来讲，肯定是由某些慢查询导致的。

查看/设置“慢查询”的时间定义

mysql> show variables like "long%";

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| long_query_time | 0.000100 |

+-----------------+----------+

1 row in set (0.00 sec)

如上述语句输出，“慢查询”的时间定义为0.0001秒(方便测试，一般设置为1-10秒)。使用下面语句定义“慢查询”时间

mysql> set long_query_time=0.0001;

Query OK, 0 rows affected (0.00 sec)

开启“慢查询”记录功能

mysql> show variables like "slow%";

+---------------------+------------------------------------+

| Variable_name | Value |

+---------------------+------------------------------------+

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | /opt/mysql/data/localhost-slow.log |

+---------------------+------------------------------------+

3 rows in set (0.00 sec)

上述语句查看“慢查询”的配置信息，你可以自定义日志文件的存放，但必须将 slow_query_log 全局变量设置为“ON”状态，执行以下语句

mysql> set global slow_query_log=ON;

Query OK, 0 rows affected (0.01 sec)

结果：

mysql> show variables like "slow%";

+---------------------+------------------------------------+

| Variable_name | Value |

+---------------------+------------------------------------+

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /opt/mysql/data/localhost-slow.log |

+---------------------+------------------------------------+

3 rows in set (0.00 sec)

那么哪些条件可以导致慢查询呢？或者说根据何种条件判断，优化慢查询。仅从SQL语句方面阐述慢查询，MySQL系统级别的设置暂不考虑。

二、返回列数太多

返回列数太多

EXPLAIN SELECT `happy_ni_nis`.*

FROM `happy_ni_nis`

ORDER BY n_total DESC LIMIT 10\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: happy_ni_nis

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 66493186

Extra: Using filesort

1 row in set (0.01 sec)

优化建议： 添加 关于 n_total 的索引

mysql> alter table happy_ni_nis add index `idx_of_n_total` (`n_total`, `id`);

Query OK, 0 rows affected (7 min 48.27 sec)

Records: 0 Duplicates: 0 Warnings: 0

EXPLAIN SELECT `happy_ni_nis`.*

FROM `happy_ni_nis`

ORDER BY n_total DESC LIMIT 10\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: happy_ni_nis

type: index

possible_keys: NULL

key: idx_of_n_total

key_len: 8

ref: NULL

rows: 10

Extra:

1 row in set (0.01 sec)

但是某些情况下，返回列数比较多，也不代表是慢查询。

SELECT `cd_happys`.*

FROM `cd_happys`

WHERE `cd_happys`.`p_status` = 4

AND `cd_happys`.`status` IN (0,

1,

2,

3,

4)

AND (c_time <= '2015-05-15 23:30:39'

AND update_time <= '2015-05-15 23:30:39')

ORDER BY `cd_happys`.`id` ASC LIMIT 1000

+----+-------------+-----------------+-------+---------------------------------------+---------+---------+-----+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+-------+---------------------------------------+---------+---------+-----+------+-------------+

| 1 | SIMPLE | cd_happys | index | idx_uptime_seneditor,idx_c_time | PRIMARY | 4 | | 2000 | Using where |

+----+-------------+-----------------+-------+---------------------------------------+---------+---------+-----+------+-------------+

平均查询时间：6 sec

添加一个 ``idx_of_p_status_status_c_time(p_status,status,c_time,id) 索引

mysql> explain SELECT SQL_NO_CACHE `cd_happys`.* FROM `cd_happys` WHERE `cd_happys`.`p_status` = 4 AND `cd_happys`.`status` IN (0, 1, 2, 3, 4) AND (c_time <= '2015-05-15 23:30:39' and update_time <= '2015-05-15 23:30:39') LIMIT 1000\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: cd_happys

type: range

possible_keys: idx_uptime_seneditor,idx_c_time,idx_of_p_status_sanzu_check_status_signup_status,idx_of_p_status_status_c_time

key: idx_of_p_status_status_c_time

key_len: 16

ref: NULL

rows: 3782

Extra: Using where

1 row in set (0.01 sec)

平均查询时间：0.4 sec

三、分页条件，过大的offset

EXPLAIN SELECT `happys`.*

FROM `happys`

INNER JOIN `happy_infos` ON `happy_infos`.`happy_id` = `happys`.`id`

WHERE

(happys.end_time > '2015-08-13 14:08:10')

AND (happys.j_tag_id > 0)

AND (happy_infos.my_image_url = '')

ORDER BY happys.updated_at DESC LIMIT 100

OFFSET 28900\G;

+----+-------------+----------