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;


+----+-------------+------------+--------+------------------------------------------------------------+---------------+---------+-----------------+--------+-----------------------------+
| id | select_type | table      | type   | possible_keys                                              | key           | key_len | ref             | rows   | Extra                       |
+----+-------------+------------+--------+------------------------------------------------------------+---------------+---------+-----------------+--------+-----------------------------+
| 1  | SIMPLE      | happys      | range  | PRIMARY,idx_end_time,idx_bg_tag_pub_beg,idx_bg_tag_pub_end | idx_end_time  | 8       |                 | 219114 | Using where; Using filesort |
+----+-------------+------------+--------+------------------------------------------------------------+---------------+---------+-----------------+--------+-----------------------------+
| 1  | SIMPLE      | happy_infos | eq_ref | happy_id_index                                              | happy_id_index | 4       | tao800.happys.id | 1      | Using where                 |
+----+-------------+------------+--------+------------------------------------------------------------+---------------+---------+-----------------+--------+-----------------------------+

平均查询时间：

Empty set (9.78 sec)

优化建议：

1、MySQL里对LIMIT OFFSET的处理方式是，取出OFFSET+LIMIT的所有数据，然后去掉OFFSET，返回底部的LIMIT
使用子查询，使用覆盖索引进行优化。

2、这种方式在offset很高的情况下，
如：limit 100000,20，这样系统会查询100020条，然后把前面的100000条都扔掉，这是开销很大的操作，导致慢查询很慢。

3、使用覆盖索引(convering index) 查询，然后再跟全行做join操作。这样可以直接使用index 得到数据，而不是去查询表，
当找到需要的数据之后，再与全表join获得其他列。

EXPLAIN SELECT SQL_NO_CACHE `happys`.*
FROM `happys`
INNER JOIN
(
SELECT happys.id
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 = ''
LIMIT 100 OFFSET 28900
) AS lim ON lim.id = happys.id
ORDER BY happys.updated_at DESC \G;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: happys
         type: range
possible_keys: PRIMARY,idx_end_time,idx_bg_tag_pub_beg,idx_bg_tag_pub_end
          key: idx_bg_tag_pub_end
      key_len: 4
          ref: NULL
         rows: 425143
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: happy_infos
         type: eq_ref
possible_keys: happy_id_index
          key: happy_id_index
      key_len: 4
          ref: tao800.happys.id
         rows: 1
        Extra: Using where
3 rows in set (0.67 sec)

平均查询时间： 0.67 sec

http://zhidao.baidu.com/link?url=4hVpi7DCSX6bEROu33YQD_Gq2IhERaqOl7yyPASjtixIaRbYhCFEteP4KyJX52RI7QxY0mV_6UM2g8p3KeG4BzJx1z94EozoKW-yewhCDju

http://www.jb51.net/article/27504.htm

http://www.jb51.net/article/33777.htm

http://chinahnzhou.iteye.com/blog/1567537

http://dataunion.org/14895.html

http://blog.idaohang123.com/archives/399

http://www.jbxue.com/db/22798.html

http://www.admin10000.com/document/4797.html

http://www.fienda.com/archives/110

http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down

四、驱动表，临时表，排序

mysql> EXPLAIN SELECT `happy_d_sales`.`happy_id`
    -> FROM `happy_d_sales`
    -> INNER JOIN happys ON happys.id = happy_d_sales.happy_id
    -> AND happys.j_tag_id = happy_d_sales.tag_id
    -> WHERE (status = 1
    ->        AND date = '2015-08-12')
    -> ORDER BY sales DESC LIMIT 300\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: happys
         type: index
possible_keys: PRIMARY,idx_bg_tag_pub_beg,idx_bg_tag_pub_end
          key: idx_bg_tag_pub_beg
      key_len: 20
          ref: NULL
         rows: 850279 <- 返回数据太多，坏味道
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: happy_d_sales
         type: eq_ref
possible_keys: happy_id_date_tag_idx,tag_id_date
          key: happy_id_date_tag_idx
      key_len: 11
          ref: tao800.happys.id,const,tao800.happys.j_tag_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

ERROR:
No query specified

平均查询时间 Empty set (3.90 sec)

EXPLAIN 返回的第一列的表，就是驱动表，在驱动表上排序，非常快。但是如果在非驱动表上，排序，就很慢。
默认情况下，记录就是按照顺序排列好的，不需要进行排序。但是上述结果，从happys 表中取出一些数据，建立临时表，并且还在临时表上进行排序。
所以就会出现 Using temporary; Using filesort 这种情况。

优化建议：

1、减少返回值rows

2、指定正确的驱动表

mysql> ALTER table `happy_d_sales` ADD INDEX `idx_of_date_and_status` (`date`, `status`, `sales`, `id`);
Query OK, 0 rows affected (19.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT `happy_d_sales`.`happy_id`
    -> FROM `happy_d_sales`
    -> STRAIGHT_JOIN happys ON happys.id = happy_d_sales.happy_id
    -> AND happys.j_tag_id = happy_d_sales.tag_id
    -> WHERE  date = '2015-08-12' AND status = 1
    -> ORDER BY `happy_d_sales`.sales DESC LIMIT 300\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: happy_d_sales  <-- 正确的驱动表
         type: ref
possible_keys: happy_id_date_tag_idx,tag_id_date,idx_of_date_and_status
          key: idx_of_date_and_status
      key_len: 7
          ref: const,const
         rows: 1
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: happys
         type: eq_ref
possible_keys: PRIMARY,idx_bg_tag_pub_beg,idx_bg_tag_pub_end
          key: PRIMARY
      key_len: 4
          ref: tao800.happy_d_sales.happy_id
         rows: 1
        Extra: Using where
2 rows in set (0.01 sec)

ERROR:
No query specified

平均查询时间：0.03 sec

五、不合适的group by 分组条件

大表上的group by

CREATE TABLE `p_acc_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0' ,
  `b_amount` int(11) NOT NULL DEFAULT '0' ,
  `r_amount` int(11) NOT NULL DEFAULT '0' ,
  `amount` int(11) NOT NULL DEFAULT '0' ,
  `l_type` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`,`id`),
  KEY `idx_account_type` (`account_id`,`l_type`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3212639 DEFAULT CHARSET=utf8;

该表上有百万条的数据，也有关于 account_id 的索引，但是使用 group by 查询时，确实很慢。

SELECT MAX(id) max_id FROM `p_acc_logs`  WHERE (created_at <= '2015-08-14 00:00:00') GROUP BY account_id

平均查询时间 10sec

六、不合适的order by

EXPLAIN SELECT SQL_NO_CACHE id,
       u_id,
       amount
FROM `t_orders`
WHERE (settlement_time >= '2015-07-01 00:00:00'
       AND settlement_time <= '2015-09-30 23:59:59')
ORDER BY `t_orders`.`id` ASC LIMIT 3000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orders
         type: index
possible_keys: idx_settlement_time
          key: PRIMARY  <--- 注意这里
      key_len: 4
          ref: NULL
         rows: 6705   <---- 注意这里
        Extra: Using where
1 row in set (0.09 sec)

ERROR:
No query specified

平均查询时间：40 sec

CREATE TABLE `t_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `u_id` int(11) NOT NULL DEFAULT '0' ,
  `order_id` varchar(20) NOT NULL DEFAULT '' ,
  `amount` int(11) NOT NULL DEFAULT '0' ,
  `settlement_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' ,
  `d_s_time` datetime DEFAULT '1970-01-01 00:00:00' ,
  `serial_number` bigint(20) unsigned DEFAULT NULL ,
  `order_type` int(11) DEFAULT NULL ,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_t_orders_on_u_id_and_settlement_time_and_id` (`u_id`,`settlement_time`,`id`),
  KEY `index_t_orders_on_order_id_and_order_type_and_id` (`order_id`,`order_type`,`id`),
  KEY `index_t_orders_on_serial_number_and_order_type_and_id` (`serial_number`,`order_type`,`id`),
  KEY `idx_settlement_time` (`settlement_time`,`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

优化建议：

1、去除错误的order by 查询，记录集默认就是按照id升序排列的。

2、使查询语句能够按照正确的方式查询

EXPLAIN SELECT SQL_NO_CACHE id,
       u_id,
       amount
FROM `t_orders`
WHERE (settlement_time >= '2015-07-01 00:00:00'
       AND settlement_time <= '2015-09-30 23:59:59')
LIMIT 3000;


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orders
         type: range
possible_keys: idx_settlement_time
          key: idx_settlement_time <-- 注意这里
      key_len: 8
          ref: NULL
         rows: 12784434 <-- 注意这里
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

平均查询时间：0.16 sec

七、模糊查询

EXPLAIN SELECT `p_d_logs`.*
FROM `p_d_logs`
WHERE (details LIKE '%waiting%'
       AND created_at < '2015-08-09 03:40:35')
  AND (`p_d_logs`.`id` > 297273949)
ORDER BY `p_d_logs`.`id` ASC LIMIT 10000\G

+----+-------------+-------------------+-------+---------------+---------+---------+-----+---------+-------------+
| id | select_type | table             | type  | possible_keys | key     | key_len | ref | rows    | Extra       |
+----+-------------+-------------------+-------+---------------+---------+---------+-----+---------+-------------+
| 1  | SIMPLE      | p_d_logs | range | PRIMARY       | PRIMARY | 4       |     | 3340552 | Using where |
+----+-------------+-------------------+-------+---------------+---------+---------+-----+---------+-------------+

平均查询时间：7.5889787673950195 sec

EXPLAIN SELECT `e_logs`.`loggable_id`
FROM `e_logs`
WHERE (user_name LIKE '%王大傻%'
       AND action_type_id = 0
       AND loggable_type = 'HappyBase') \G;


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_logs
         type: ref
possible_keys: by_loggable_type
          key: by_loggable_type
      key_len: 92
          ref: const
         rows: 684252
        Extra: Using where
1 row in set (10.61 sec)

15738 rows in set (3 min 45.75 sec)

 CREATE TABLE `e_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loggable_type` varchar(30) NOT NULL DEFAULT '' ,
  `loggable_id` int(11) NOT NULL DEFAULT '0' ,
  `u_id` int(11) NOT NULL DEFAULT '0' ,
  `user_name` varchar(24) NOT NULL DEFAULT '' ,
  `create_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' ,
  `execution_type_id` tinyint(4) NOT NULL DEFAULT '0' ,
  `action_type_id` tinyint(4) NOT NULL DEFAULT '0' ,
  `from_url` varchar(200) NOT NULL DEFAULT '' ,
  `updated_changes` longtext ,
  `t_s_id` int(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`),
  KEY `index_e_logs_on_loggable_id_and_loggable_type` (`loggable_id`,`loggable_type`),
  KEY `idx_user_name` (`user_name`,`action_type_id`,`id`),
  KEY `index_e_logs_on_create_time` (`create_time`),
  KEY `by_t_s_id` (`t_s_id`,`id`),
  KEY `by_loggable_type` (`loggable_type`,`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

优化建议：

1、调整查询语句顺序

2、添加合适的索引。删除旧的索引。

alter table e_logs drop index `by_loggable_type`;
Query OK, 0 rows affected (3 min 12.71 sec)


alter table e_logs add index `idx_of_loggable_type_and_type_id` (`loggable_type`, `action_type_id`, `id`);
Query OK, 0 rows affected (6 min 5.70 sec)



EXPLAIN SELECT SQL_NO_CACHE `e_logs`.`loggable_id`
FROM `e_logs`
WHERE (loggable_type = 'HappyBase' AND action_type_id = 0 AND user_name LIKE '王大傻%') \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_logs
         type: range
possible_keys: idx_user_name,idx_of_loggable_type_and_type_id
          key: idx_user_name
      key_len: 75
          ref: NULL
         rows: 39546
        Extra: Using where
1 row in set (0.01 sec)

平均查询时间： 0.13 sec 效率提升 1700 倍

八、检索条件，没有按照索引列查询

索引，中范围查询什么的，是否能使用到索引，详细演示一下

SELECT  happy_id, sum(sales) as all_sales
FROM `happy_d_sales`
WHERE `happy_d_sales`.`status` = 1
AND `happy_d_sales`.`tag_id` IN (xxx,)
GROUP BY happy_id ORDER BY all_sales desc LIMIT 100 OFFSET 0

平均查询时间：20sec

CREATE TABLE `happy_d_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `happy_id` int(11) NOT NULL ,
  `tag_id` int(11) NOT NULL ,
  `sales` int(11) NOT NULL DEFAULT '0' ,
  `status` int(11) NOT NULL DEFAULT '0' ,
  `date` date NOT NULL ,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `r_count` int(11) NOT NULL DEFAULT '0' ,
  `a_t_s_count` int(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `happy_id_date_tag_idx` (`happy_id`,`date`,`tag_id`),
  KEY `tag_id_date` (`tag_id`,`date`) ,
  KEY `idx_of_date_and_status` (`date`,`status`,`sales`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

调整下SQL语句的顺序，并且添加合适的索引，瞬间减少查询时间。

explain SELECT SQL_NO_CACHE  happy_id, sum(sales) as all_sales
FROM `happy_d_sales`
WHERE `happy_d_sales`.`tag_id` IN (xxxx,xxxx,xxxx)
AND `happy_d_sales`.`status` = 1
GROUP BY happy_id ORDER BY all_sales desc
LIMIT 100 OFFSET 0\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: happy_d_sales
         type: range
possible_keys: tag_id_date,idx_of_tag_id_status_happy_id
          key: idx_of_tag_id_status_happy_id
      key_len: 8
          ref: NULL
         rows: 94380
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.06 sec)

平均查询时间： 100 rows in set (0.43 sec)

九、根本没有索引

explain select t_s_id from guang_happy_outs group by t_s_id;
+----+-------------+-----------------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | guang_happy_outs | ALL  | NULL          | NULL | NULL    | NULL | 69008793 | Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+---------------------------------+
1 row in set (0.00 sec)

平均查询时间：3 sec

CREATE TABLE `guang_happy_outs` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `t_s_id` int(11) NOT NULL DEFAULT '0' ,
  `happy_id` int(11) NOT NULL DEFAULT '0' ,
  `count` int(11) NOT NULL DEFAULT '0' ,
  `de_id` int(11) NOT NULL DEFAULT '0' ,
  `ad_count` int(11) NOT NULL DEFAULT '0' ,
  `st_date` date NOT NULL DEFAULT '1970-01-01' ,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `amount` int(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`),
  KEY `idx_guang_dlout_dlid` (`happy_id`),
  KEY `idx_guang_dlout_cat` (`created_at`),
  KEY `idx_guang_dlout_stdate` (`st_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

优化建议：

1、添加必要的索引

mysql> alter table guang_happy_outs add index `idx_of_t_s_id` (`t_s_id`, `id`);
Query OK, 0 rows affected (7 min 41.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

explain select t_s_id from guang_happy_outs group by t_s_id\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: guang_happy_outs
         type: range
possible_keys: NULL
          key: idx_of_t_s_id
      key_len: 4
          ref: NULL
         rows: 201
        Extra: Using index for group-by
1 row in set (0.01 sec)

ERROR:
No query specified


平均查询时间：
    4818 rows in set (0.03 sec)

不过针对大表而言，对于一些常用的查询，可以单独建立小表，在关联的小表上进行查询。

十、EXPLAIN 返回type 为 ALL，全表扫描


EXPLAIN SELECT `r_records`.*
FROM `r_records`
WHERE (create_time >= '2015-08-13'
       AND create_time < '2015-08-14'
       AND device_id !="0")\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r_records
         type: ALL  <-- 看这里
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10753575 <- 看这里
        Extra: Using where
1 row in set (0.01 sec)

ERROR:
No query specified

优化建议：

1、添加关于 create_time 和 device_id 的联合索引。

2、最好的方式就是单独单独建立统计表，针对每天的日志情况做统计。避免在大表上进行范围查询。

alter table r_records add index `idx_of_create_time` (`create_time`, `device_id`, `id`);
Query OK, 0 rows affected (4 min 14.59 sec)


EXPLAIN SELECT `r_records`.*
FROM `r_records`
WHERE (create_time >= '2015-08-13'
       AND create_time < '2015-08-14'
       AND device_id !="0")\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r_records
         type: range
possible_keys: idx_of_create_time <-- 看这里
          key: idx_of_create_time
      key_len: 777  <-- 看这里
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

另一个例子

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)

平均查询时间：13 sec

优化建议：

1、添加关于 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)

ERROR:
No query specified

平均查询时间：0.01 sec

十一、针对大表的 count(id)

mysql> select SQL_NO_CACHE count(id) from guang_happy_outs;
+-----------+
| count(id) |
+-----------+
|  69008543 |
+-----------+
1 row in set (31.14 sec)

千万表上的 count(id) 操作

无解。

如有同学知道怎么解决，请回复，3Q

在 select count (*) from table_name 为什么没有使用主键索引? 有详细的讨论。

mysql> select SQL_NO_CACHE count(id) from g_d_outs\G;
*************************** 1. row ***************************
count(id): 69008543
1 row in set (10.76 sec)

ERROR: 
No query specified

如果你想得到近似的统计值，使用下面的这个方式查找 Rows 值，这种方式是非常快的。

mysql> show table status where name = 'g_d_outs'\G;
*************************** 1. row ***************************
           Name: g_d_outs
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 69008796
 Avg_row_length: 71
    Data_length: 4965007360
Max_data_length: 0
   Index_length: 3560964096
      Data_free: 5242880
 Auto_increment: 138022949
    Create_time: 2015-08-14 18:46:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: ??????????
1 row in set (0.01 sec)

总结：

1、索引优化，最傻的办法，给查询语句添加覆盖索引。但不是最好的方式。

2、将大表拆成小的汇总表。

3、重在实践，MySQL优化器在很多情况下不能给出，最快的实现方式。

4、避免在大表上的group by，order by，offset 操作，除非你知道如何优化的前提下。

5、SQL WHERE查询条件，尽量按照目前添加的索引顺序来。

最后编辑于：2017.11.27 03:24:33

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,324评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,303评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,192评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,555评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,569评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,566评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,927评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,583评论 0赞 257
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,827评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,590评论 2赞 320
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,669评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,365评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,941评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,928评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,159评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,880评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,399评论 2赞 342

MySQL 慢查询的特征表现及优化方式

一、慢查询的体现

二、返回列数太多

三、分页条件，过大的offset

四、驱动表，临时表，排序

五、不合适的group by 分组条件

六、不合适的order by

七、模糊查询

八、检索条件，没有按照索引列查询

九、根本没有索引

十、EXPLAIN 返回type 为 ALL，全表扫描

十一、针对大表的 count(id)

推荐阅读更多精彩内容