Ruby China

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

ibugs · 2015年08月20日 · 最后由 iamzhuchunbo 回复于 2019年09月18日 · 8755 次阅读

我们将超过指定时间的 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 查询，InnoDB 存储引擎中，记录集默认就是按照 id 升序排列的，MyISAM 不能保证。

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 查询条件，尽量按照目前添加的索引顺序来。

19 个赞
无引用文章
prajnamas #1 2015年08月20日

我现在手头上有一个 SQL 语句，十分想求怎么优化……

SELECT  `artwork_transactions`.`id`, 
        `artwork_transactions`.`artwork_id`, 
        `artwork_transactions`.`transaction_date`, 
        `artwork_transactions`.`trans_type`, 
        `artwork_transactions`.`display_price`, 
        `artwork_transactions`.`icon_img_url` 
FROM `artwork_transactions` 
INNER JOIN `artworks` 
ON `artwork_transactions`.`artwork_id` = `artworks`.`id` 
WHERE `artworks`.`artist_id` = 28896  
ORDER BY `artwork_transactions`.`transaction_date` ASC 
LIMIT 25 
OFFSET 0

现在我都不知道应该怎么去加索引……

ibugs #2 2015年08月20日

@prajnamas 

SELECT `artwork_transactions`.`id`,
       `artwork_transactions`.`artwork_id`,
       `artwork_transactions`.`transaction_date`,
       `artwork_transactions`.`trans_type`,
       `artwork_transactions`.`display_price`,
       `artwork_transactions`.`icon_img_url`
FROM `artwork_transactions`
INNER JOIN `artworks` ON `artwork_transactions`.`artwork_id` = `artworks`.`id`
WHERE `artworks`.`artist_id` = 28896
ORDER BY `artwork_transactions`.`transaction_date` ASC LIMIT 25
OFFSET 0

方便表结构描述一下么？

解决方式大概有如下： 1、指定正确的驱动表 artwork_transactions 或 artworks。如果要在 artwork_transactions 这个表上面排序，就需要指定 artwork_transactions 为驱动表。所以需要将 INNER JOIN 改成 STRAIGHT_JOIN。 2、建立artwork_transactions.artwork_id，artwork_transactions.trans_type,artwork_transactions.display_price, artwork_transactions.icon_img_url, artwork_transactions.id,覆盖索引 3、在 artworks 上建立 artworks.artist_id 索引。

根据如上方式优化之后，SQL 查询肯定会很快！

prajnamas #3 2015年08月20日

#2 楼 @ibugs 我发了封邮件到您 github 上面写的邮件，多谢！

foghost #4 2015年08月21日

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

没记错的话，虽然大多数情况下看起来是这样的，但实际上 MySQL 是不保证这点的（按主键升序列排序） http://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

ibugs #5 2015年08月21日

@foghost

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

Combining TOP with ORDER BY adds determinism to the set of rows returned. Without the ORDER BY, the set of rows returned depends on the query plan and may even vary from execution to execution. Always use ORDER BY if you expect a well-defined and consistent order in your result set. Never rely on how your database may store the rows on disk (e.g. via a clustered index) to guarantee a certain ordering of data in your queries.

Never rely on how your database may store the rows on disk (e.g. via a clustered index) to guarantee a certain ordering of data in your queries.

http://dba.stackexchange.com/questions/5774/why-is-ssms-inserting-new-rows-at-the-top-of-a-table-not-the-bottom/5775#5775 如果能有具体的理论依据就好了。看来我得认真研究下这个问题。

3Q for @foghost

ibugs #6 2015年08月21日

@foghost 









mysql> CREATE TABLE foo (id SERIAL PRIMARY KEY, bar CHAR(10));
Query OK, 0 rows affected (2.60 sec)

 CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bar` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHA

mysql> INSERT INTO foo (bar) VALUES
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  4 | testing |
|  5 | testing |
|  6 | testing |
|  7 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+
10 rows in set (0.00 sec)

mysql> DELETE FROM foo WHERE id BETWEEN 4 AND 7;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from foo;
+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+
6 rows in set (0.00 sec)

mysql> INSERT INTO foo (bar) VALUES
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
| 11 | testing |
| 12 | testing |
| 13 | testing |
| 14 | testing |
| 15 | testing |
| 16 | testing |
| 17 | testing |
| 18 | testing |
| 19 | testing |
| 20 | testing |
+----+---------+
16 rows in set (0.02 sec)

mysql> status;
--------------
/usr/local/bin/mysql  Ver 14.14 Distrib 5.6.16, for osx10.9 (x86_64) using  EditLine wrapper

如果能找一个反证，证明某些情况下不是按照主键 正序排序就完美了。

sun1752709589 #7 2015年08月21日

添加一个 idx_of_p_status_status_c_time (p_status,status,c_time,id) 索引 如果 engine=innodb，二级索引应该是指向主键索引的，二级索引中包含 id 列的话貌似没必要...

foghost #8 2015年08月21日

@ibugs 这个反证还真不好找，我本地用的 MySQL innodb (5.6.21) 看起来也是主键升序

ibugs #9 2015年08月21日

@sun1752709589 innodb 中，建立索引如果没有添加 id，会自动补加上。手动加上 id 的话，就减少了一丢丢数据库消耗的资源。之前，我问过我们的 DBA。所以现在我们添加索引的时，会主动将 id，添加上。

sun1752709589 #11 2015年08月21日

#9 楼 @ibugs http://www.linuxidc.com/Linux/2014-09/107267.htm 这篇帖子从源码层面分析了下，涨姿势了

#10 楼 @geniousli 没事赶紧敲代码吧，哈哈

ibugs #13 2015年08月24日

@prajnamas

显示执行计划

mysql> EXPLAIN SELECT  `artwork_transactions`.`id`, 
    ->         `artwork_transactions`.`artwork_id`, 
    ->         `artwork_transactions`.`transaction_date`, 
    ->         `artwork_transactions`.`trans_type`, 
    ->         `artwork_transactions`.`display_price`, 
    ->         `artwork_transactions`.`icon_img_url` 
    -> FROM `artwork_transactions` 
    -> INNER JOIN `artworks` 
    -> ON `artwork_transactions`.`artwork_id` = `artworks`.`id` 
    -> WHERE `artworks`.`artist_id` = 28896  
    -> ORDER BY `artwork_transactions`.`transaction_date` ASC 
    -> LIMIT 25 
    -> OFFSET 0\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artwork_transactions
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11083  <- 返回条数太多
        Extra: Using filesort  <- 使用排序不好
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: artworks
         type: eq_ref
possible_keys: PRIMARY,_ArtistID
          key: PRIMARY
      key_len: 4
          ref: tao800.artwork_transactions.artwork_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

使用如下方式进行优化

第一步

修改column属性
alter table artwork_transactions modify column transaction_date timestamp;

添加索引
alter table artwork_transactions add index `index_of_transaction_date` (`transaction_date`, `id`);
alter table artwork_transactions add index `idx_of_artwork_id` (`artwork_id`, `id`);

第二步

mysql> EXPLAIN SELECT  `artwork_transactions`.`id`, 
    ->         `artwork_transactions`.`artwork_id`, 
    ->         `artwork_transactions`.`transaction_date`, 
    ->         `artwork_transactions`.`trans_type`, 
    ->         `artwork_transactions`.`display_price`, 
    ->         `artwork_transactions`.`icon_img_url` 
    -> FROM `artwork_transactions` 
    -> STRAIGHT_JOIN `artworks` 
    -> ON `artwork_transactions`.`artwork_id` = `artworks`.`id` 
    -> WHERE `artworks`.`artist_id` = 28896  
    -> ORDER BY `artwork_transactions`.`transaction_date` ASC 
    -> LIMIT 25 
    -> OFFSET 0\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artwork_transactions
         type: index
possible_keys: idx_of_artwork_id
          key: index_of_transaction_date
      key_len: 12
          ref: NULL
         rows: 25  <- 返回行数大大降低
        Extra:     <- 并且未使用排序
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: artworks
         type: eq_ref
possible_keys: PRIMARY,_ArtistID
          key: PRIMARY
      key_len: 4
          ref: artwork_transactions.artwork_id
         rows: 1
        Extra: Using where
ibugs #14 2015年08月25日

@foghost

这里指明 存储引擎为 InnoDB。如果使用 MyISAM 就不会保证，记录是按照 主键 Id 排序的

mysql> CREATE TABLE `foo` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `bar` char(10) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `id` (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO foo (bar) VALUES
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing'),
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> DELETE FROM foo WHERE id BETWEEN 4 AND 7;
Query OK, 4 rows affected (0.01 sec)

mysql> SELECT * FROM foo;
+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+
6 rows in set (0.01 sec)

mysql> INSERT INTO foo (bar) VALUES
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing'),
    ->   ('testing'), ('testing'), ('testing'), ('testing'), ('testing');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM foo;
+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
| 14 | testing |
| 13 | testing |
| 12 | testing |
| 11 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
| 15 | testing |
| 16 | testing |
| 17 | testing |
| 18 | testing |
| 19 | testing |
| 20 | testing |
+----+---------+
16 rows in set (0.01 sec)

http://stackoverflow.com/questions/1949641/mysql-row-order-for-select-from-table-name/1949663?noredirect=1#comment52182013_1949663

foghost #15 2015年08月25日

@ibugs :plus1:
自己也试了下，MyISAM (5.6) 确实是不按主键排序的 5.6 InnoDB 貌似是按主键排序的，但是不知道以后随版本的变化这块会不会有变化，因为到目前为止没有看到官方文档中有提到会保证结果一定按主键排序返回。代码上如果信任这种排序而省略 order by，感觉以后（MySQL）版本升级时会出现麻烦

ibugs #16 2015年08月25日

@foghost Bingo 所以慢查询优化的时候，尽量避免那些偏门的的技巧。数据库升级会带来不小的隐患。

1 个赞
ibugs #17 2015年08月26日

@foghost

我总结了下，在下面这个情况下，id 不是升序排列的情况。

表结构索引如下：

CREATE TABLE `happy_for_ni_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' ,
  `log_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`,`log_type`,`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

三个 column 的索引

mysql> explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408 AND (created_at >= '2015-08-23 00:00:00');
+----+-------------+------------------+------+---------------------------------+------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys                   | key              | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+---------------------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_created_at,idx_account_type | idx_account_type | 4       | const |    4 | Using where |
+----+-------------+------------------+------+---------------------------------+------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)


mysql> SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408;
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| id      | account_id | b_amount | r_amount | amount   | log_type | created_at          | updated_at          |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| 59 |     408 |              0 |             0 |  -210000 |        1 | 2015-08-23 13:18:43 | 2015-08-23 13:18:43 |
| 45 |     408 |              0 |             0 | -1000000 |        2 | 2015-08-23 13:14:44 | 2015-08-23 13:14:44 |
| 43 |     408 |        1000000 |             0 |  1000000 |        4 | 2015-08-23 13:13:46 | 2015-08-23 13:13:46 |
| 55 |     408 |         210000 |             0 |   210000 |        4 | 2015-08-23 13:18:17 | 2015-08-23 13:18:17 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+

结果并不是按照id正序排列的

explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408 order by id desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra                       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where; Using filesort |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)

explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408 order by log_type desc, id desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408 order by log_type asc, id desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra                       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where; Using filesort |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)

explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408 order by log_type desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

数据按照 account_id,log_type,id 升序排序

两个 column 的情况

如果索引是如下方式创建

mysql> alter table happy_for_ni_logs add index `idx_of_account_id` (`account_id`, `id`);

mysql> SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408;
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| id      | account_id | b_amount | r_amount | amount   | log_type | created_at          | updated_at          |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| 43 |     408 |        1000000 |             0 |  1000000 |        4 | 2015-08-23 13:13:46 | 2015-08-23 13:13:46 |
| 45 |     408 |              0 |             0 | -1000000 |        2 | 2015-08-23 13:14:44 | 2015-08-23 13:14:44 |
| 55 |     408 |         210000 |             0 |   210000 |        4 | 2015-08-23 13:18:17 | 2015-08-23 13:18:17 |
| 59 |     408 |              0 |             0 |  -210000 |        1 | 2015-08-23 13:18:43 | 2015-08-23 13:18:43 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408;
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table            | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_of_account_id | idx_of_account_id | 4       | const |    4 |       |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.01 sec)
以上是按照id正序排列的

EXPLAIN SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408 ORDER BY id desc;

+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | happy_for_ni_logs | ref  | idx_of_account_id | idx_of_account_id | 4       | const |    4 | Using where |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (9.51 sec)

以上情况并没有出现 Using filesort 的情况

数据按照account_id,id 升序排列

在没有索引的情况

mysql> explain SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | happy_for_ni_logs | ALL  | NULL          | NULL | NULL    | NULL | 1684032 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE `happy_for_ni_logs`.* FROM `happy_for_ni_logs` WHERE `happy_for_ni_logs`.`account_id` = 408;
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| id      | account_id | balance_amount | refund_amount | amount   | log_type | created_at          | updated_at          |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| 43 |     408 |        1000000 |             0 |  1000000 |        4 | 2015-08-23 13:13:46 | 2015-08-23 13:13:46 |
| 45 |     408 |              0 |             0 | -1000000 |        2 | 2015-08-23 13:14:44 | 2015-08-23 13:14:44 |
| 55 |     408 |         210000 |             0 |   210000 |        4 | 2015-08-23 13:18:17 | 2015-08-23 13:18:17 |
| 59 |     408 |              0 |             0 |  -210000 |        1 | 2015-08-23 13:18:43 | 2015-08-23 13:18:43 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
4 rows in set (2.47 sec)

数据按照主键 id 升序排列。

foghost #18 2015年08月28日

@ibugs Nice~ 忘记 Secondary Index 的 Case 了，看来没有指定 order by 时顺序缺失是无法做保证的 也看到你这篇里的总结了 https://ruby-china.org/topics/27105

个人整理下（MySQL InnoDB (5.6?）:

  • 检索时没有使用 Secondary Index 时：

    1. 按 Clustered index（主键）里的存储顺序去检索，
    2. 得到并返回检索到的数据集： <主键, 实际数据>的数据集（按照Clustered Index里的 主键 顺序返回）

  • 检索时有使用 Secondary Index 时

    1. 按 Secondary Index （副键）的存储顺序去检索
    2. 得到 <副键, 主键>结果集（按 Secondary Index里的 副键（不是字段） 顺序返回）
    3. 按 主键 到 Clustered Index 里检索到 实际数据：<副键，实际数据>的数据集
    4. 返回结果（按 Secondary Index 里的 副键 顺序返回）
1 个赞
ibugs #19 2015年08月28日

@foghost very good! 👍

实践是检验整理的唯一方式。

iamzhuchunbo #20 2019年09月18日

针对第三点，分页的优化，本地实际操作，时间确实快了，但是数据不一致。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号