MySQL order by limit 走错索引(range->indexscan)

生产库遇到过好几例本文要讨论的案例,而且比较棘手。简而言之,有类似这样的查询 SELECT * FROM t1 where t1.f2>1 and t2.f2<100 order by t1.id,id是主键,条件里面有个range查询,就会造成优化器是选择主键,还是选择filesort问题,有些特殊情况就会选错索引,比如为了回避内存排序,选择了主键扫描,导致原本走范围过滤再sort 500ms勉强可以结束的查询,5分钟不出结果。

下面具体来这个案例。

1. 背景

阿里云RDS,5.6.16-log。
表 d_ec_someextend.t_tbl_test_time_08:

1
2
3
4
5
6
7
8
CREATE TABLE `t_tbl_test_time_08` (
`f_some_id` int(11) unsigned DEFAULT '0',
`f_qiye_id` int(11) DEFAULT '0',
`f_type` tinyint(3) DEFAULT '0' COMMENT '有效联系类型 1: QQ联系,2:拨打电话,3:发送邮件,4:发送短信,5:添加跟进记录,6:拜访客户,7:EC联系,8:更新客户阶段',
`f_contact_time` timestamp NULL DEFAULT '1970-01-01 16:00:01',
UNIQUE KEY `some_qiye_type` (`f_some_id`,`f_qiye_id`,`f_type`),
KEY `f_contact_time` (`f_contact_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表索引信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show table status like "t_tbl_test_time_08";
+-----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+--------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Block_format |
+-----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+--------------+
| t_tbl_test_time_08 | InnoDB | 10 | Compact | 19264318 | 45 | 882900992 | 0 | 2176843776 | 752877568 | NULL | 2017-10-25 20:27:08 | NULL | NULL | utf8mb4_general_ci | NULL | | | Original |
+-----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+--------------+
1 row in set
mysql> show index from t_tbl_test_time_08;
+--------------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_tbl_test_time_08 | 0 | some_qiye_type | 1 | f_some_id | A | 19264318 | NULL | NULL | YES | BTREE | | |
| t_tbl_test_time_08 | 0 | some_qiye_type | 2 | f_qiye_id | A | 19264318 | NULL | NULL | YES | BTREE | | |
| t_tbl_test_time_08 | 0 | some_qiye_type | 3 | f_type | A | 19264318 | NULL | NULL | YES | BTREE | | |
| t_tbl_test_time_08 | 1 | f_contact_time | 1 | f_contact_time | A | 9632159 | NULL | NULL | YES | BTREE | | |
+--------------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set

问题查询:

1
2
3
4
select f_some_id from d_ec_some1.t_tbl_test_time_08
where f_qiye_id=5077665 and f_type=9
and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53'
order by f_some_id limit 300

该表的其它查询:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT `c`.`f_some_id`, max(f_contact_time) AS `time` FROM `d_ec_some`.`t_some_relation` AS `r`
LEFT JOIN `d_ec_someextend`.`t_tbl_test_time_10` AS `c` ON c.f_some_id=r.f_some_id AND c.f_type in(1,2,3,4,5,6,7)
WHERE (r.f_qiye_id='4047065' and r.f_user_id='4047064' and c.f_some_id is not null)
GROUP BY `f_some_id` ORDER BY `time` desc LIMIT 20
-- group-by, order-by
select f_some_id, f_type, f_contact_time from d_ec_some1.t_tbl_test_time_08
where f_qiye_id = 1181333
and f_type > 0 and f_type < 11
and f_some_id > 263047293 and f_some_id < 780306437
order by f_some_id
-- 分页

2. explain

问题查询执行计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> explain extended select f_some_id from d_ec_some1.t_tbl_test_time_08
where f_qiye_id=5077665 and f_type=9
and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53'
order by f_some_id limit 300;
+----+-------------+-----------------------+-------+----------------+---------------+---------+------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+-------+----------------+---------------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t_tbl_test_time_08 | index | f_contact_time | some_qiye_type | 12 | NULL | 16032 | 2248.49 | Using where |
+----+-------------+-----------------------+-------+----------------+---------------+---------+------+-------+----------+-------------+
1 row in set
-- 指定一个索引
mysql> explain extended select f_some_id from d_ec_some1.t_tbl_test_time_08 use index(f_contact_time)
where f_qiye_id=5077665 and f_type=9
and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53'
order by f_some_id limit 300;
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------------------------------------+
| 1 | SIMPLE | t_tbl_test_time_08 | range | f_contact_time | f_contact_time | 5 | NULL | 360478 | 100 | Using index condition; Using where; Using MRR; Using filesort |
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------------------------------------+
1 row in set

解释:
第一个explain结果里面,type=index,表示 full index scan。注意这里看到的 index 不代表“查询用到索引了”。全索引扫描比全表扫描并不好到拿去,甚至更慢(因为随机IO)。是否用到正确的索引要看key那一列:
some_qiye_type 索引定义是 (f_some_id,f_qiye_id,f_type),从key_len=12看得出这三列都用上了(12=4+5+3)。但实际这个执行计划需要200多秒。

第二个explain,在sql里面指定了 use index(f_contact_time),依据是where条件里面f_contact_time的范围固定4s,猜想数据量不会很大,过滤效果会比较好。
解释器结果,type=range,表示是个范围查找(“范围”涵盖的种类不止</>),使用的索引是 f_contact_time(f_contact_time)。Extra列:

  • Using index condition: 用到了索引下推特性。Using where是回表拿数据。关于ICP见文后参考。
  • Using MRR: 用到了 Multi-Range Read 优化特性。mysql在通过二级索引范围查找的时候,得到的记录在物理上是无序的,为了减少去获取数据的随机IO,它会在内存缓冲区里面先根据rowid快速排序,然后顺序IO去拉取数据。(这个缓冲区大小参数由 read_rnd_buffer_size 控制)
  • Using filesort: 需要内存排序。对应 order by f_some_id

rows扫描虽然36w行,比前面的 16032 要多,但这个执行计划实际运行只需要0.7s,要快将近300倍。

但MySQL优化器默认选择了第一个更慢的执行计划,它的理由是走 some_qiye_type 索引不需要内存排序,候选的 f_contact_time 被淘汰。mysql是基于cost的,所以在想是不是有什么参数可以改变optimizer的行为,让它filesort。
(这里提一下,这类查询该表上非常多,绝大部分都走的是 f_contact_time,偶尔会有几条走some_qiye_type。这种执行计划不稳定的查询,实际带来的风险是会很高的,可能会拖垮db)

这里我们祭出优化sql的两大法宝:profiling和optimizer_trace,来尝试找出是什么因素。

  • profiling:可以定位出sql从接受到返回结果,时间都耗在哪里
  • optimizer_trace: 跟踪优化器的成本评估过程,可以情况的看到它如何从多个候选索引里,做出选择

3. profiling

先来看两种查询计划 profiling 的结果。

profiling 默认

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> set profiling=1;
mysql> select f_some_id from d_ec_some1.t_tbl_test_time_08 where f_qiye_id=5077665 and f_type=9
and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53' order by f_some_id limit 300;
mysql> show profile block io,cpu for query 1;
+----------------------+------------+------------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+------------+------------+------------+--------------+---------------+
| starting | 0.000121 | 0 | 0 | 0 | 0 |
| checking permissions | 3.2E-5 | 0 | 0 | 0 | 0 |
| Opening tables | 3.7E-5 | 0 | 0 | 0 | 0 |
| init | 4.2E-5 | 0 | 0 | 0 | 0 |
| System lock | 2.9E-5 | 0 | 0 | 0 | 0 |
| optimizing | 3.3E-5 | 0 | 0 | 0 | 0 |
| statistics | 0.005796 | 0 | 0.000999 | 448 | 0 |
| preparing | 4.3E-5 | 0 | 0 | 0 | 0 |
| Sorting result | 2.8E-5 | 0 | 0 | 0 | 0 |
| executing | 2.7E-5 | 0 | 0 | 0 | 0 |
| Sending data | 172.824522 | 189.040262 | 2.441629 | 1504928 | 6896 |
| end | 8.3E-5 | 0 | 0 | 0 | 0 |
| query end | 3E-5 | 0 | 0 | 0 | 0 |
| closing tables | 3.3E-5 | 0 | 0 | 0 | 0 |
| freeing items | 7E-5 | 0 | 0 | 0 | 0 |
| logging slow query | 3.1E-5 | 0 | 0 | 0 | 0 |
| Opening tables | 3.4E-5 | 0 | 0 | 0 | 0 |
| System lock | 7E-5 | 0 | 0 | 0 | 8 |
| cleaning up | 9.5E-5 | 0 | 0 | 0 | 0 |
+----------------------+------------+------------+------------+--------------+---------------+
19 rows in set
mysql> show status like "Handler%";
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 9430930 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+---------+
18 rows in set

profiling use index:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> set profiling=1;
mysql> select .... use index(f_contact_time)
mysql> show profile block io,cpu for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.00013 | 0 | 0 | 0 | 0 |
| checking permissions | 3.4E-5 | 0 | 0 | 0 | 0 |
| Opening tables | 4.5E-5 | 0 | 0 | 0 | 0 |
| init | 4.6E-5 | 0 | 0 | 0 | 0 |
| System lock | 3E-5 | 0 | 0 | 0 | 0 |
| optimizing | 3.9E-5 | 0 | 0 | 0 | 0 |
| statistics | 0.000109 | 0 | 0 | 0 | 0 |
| preparing | 5.5E-5 | 0 | 0 | 0 | 0 |
| Sorting result | 3.1E-5 | 0 | 0 | 0 | 0 |
| executing | 3.2E-5 | 0 | 0 | 0 | 0 |
| Sending data | 3.4E-5 | 0 | 0 | 0 | 0 |
| Creating sort index | 1.703224 | 1.718739 | 0.012999 | 0 | 0 |
| end | 8.4E-5 | 0 | 0 | 0 | 0 |
| query end | 3.2E-5 | 0 | 0 | 0 | 0 |
| closing tables | 3.9E-5 | 0 | 0 | 0 | 0 |
| freeing items | 7.3E-5 | 0 | 0 | 0 | 0 |
| logging slow query | 3.2E-5 | 0 | 0 | 0 | 0 |
| Opening tables | 5E-5 | 0 | 0 | 0 | 0 |
| System lock | 6.9E-5 | 0 | 0 | 0 | 8 |
| cleaning up | 4.1E-5 | 0 | 0 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set
set profiling=0;
mysql> show status like "Handler%";
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 188156 |
| Handler_read_last | 0 |
| Handler_read_next | 188155 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 188155 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+--------+
18 rows in set

第一个profiling
看到第一个profiling里面 Sending data 时间最长,第二个 Creating sort index 最久

1
2
3
4
5
Sending data:
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
Creating sort index
The thread is processing a SELECT that is resolved using an internal temporary table

Sending data 很具有误导性,它不仅表示发送数据到客户端,还包括“收集”数据,即mysql根据索引条件检索完数据后,得到一堆rowid,再根据rowid回表拿数据,有可能还要对数据过滤、排序,所以抛开网络因素,sending data时间长表明有大量的读磁盘操作,是非常笼统的一个状态。
下面的status里面

  • Handler_read_first: 索引里面第一条记录被读取的次数,为1,说明做了一次索引全扫描。与前面 explain 结果 type=index 是一致的。
  • Handler_read_key:根据索引定位到一行记录的次数。这个值越高,说明使用到了高效的索引。
  • Handler_read_next
    Number of requests to read the next row in key order, incremented if you are querying an index column
    with a range constraint or if you are doing an index scan.

    即根据索引key的顺序,依次去获取行数据的次数。
    这个值在这里非常的大,表明Server读取从头读取索引 (f_some_id,f_qiye_id,f_type) key的 9430930 行数据之后,找到了300个满足条件的记录,并且已是排好顺序。在这里值会随着limit的增大而增大。

第二个profiling

  • Handler_read_rnd
    The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries
    that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins
    that don’t use keys properly.

    随机读取行数据的次数,可以认为是有一堆没有顺序的主键,要依次去读取数据的次数(随机IO)。一般在有内存排序的时候,后者join查询的副表关联字段上没有好的索引,这个值都会比较高。

经过计算,条件满足 f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53' 的记录有188155 个,因此根据索引依次读取到的行数 Handler_read_key=188155。

这里有两个疑问个人没有解开:

  1. 既然有MRR,Handler_read_rnd为什么还这么高呢,不应该是是顺序IO了?
    这里不太肯定,两种可能:一是MRR不影响这个计数;第二种可能是,我们这个表上没有主键,唯一索引也不满足所有列都NOT NULL定义,也就是这个表的主键实际是innodb内部维持增长的rowid,使用MRR之后,rowid有序但并不连续,读取行的随机性没有得到大的改善。
  2. Using index condition 在这里有点费解,因为似乎没有where条件可以下推,f_contact_time索引只有 f_contact_time 这一列。

上面的分析过程只能知道实际执行过程是怎样的,直接从explain也能看出结果,这里是顺便理解一下 Hander_read_xxx, MRR, ICP 的含义,大部分sql优化都不用不到profiling和status。

4. optimizer_trace

再看 optimizer_trace 跟踪的结果:(因为trace的信息太长,放到github gist上了)

optimizer_trace包含三个步骤,下面是本次trace简化的结构:

  1. json_preparation
  2. json_optimization
    • condition_processing
      • equality_propagation
      • constant_propagation
      • trivial_condition_removal
    • table_dependencies
    • ref_optimizer_key_uses
    • rows_estimation
      • range_analysis
      • table_scan
      • potential_range_indices
      • chosen_range_access_summary
    • considered_execution_plans
      • best_access_path
    • attaching_conditions_to_tables
      • attached_conditions_computation
      • rechecking_index_usage
    • clause_processing
    • refine_plan
      • pushed_index_condition
      • access_type
    • reconsidering_access_paths_for_index_ordering
      • plan_changed
      • access_type, index
  3. json_execution

可以看到上面default与use index两次的trace,前大半部分都是一样的,候选索引都是 access_type=range, index=f_contact_time,就在 reconsidering_access_paths_for_index_ordering 的地方,“一票否决”,从而选择了 access_type=index_scan, index=some_qiye_type

搜索 reconsidering_access_paths_for_index_ordering 可以在mysql或percona的官网上,找到四五个相关的 bug report :

#70245#78997percona#1362212#74602

网上提及比较多的是 #70245,调整参数 eq_range_index_dive_limit 可解决这个问题。我们也遇到过这个案例,见 MySQL 5.6 查询优化器新特性的“BUG” 。但是本文的例子满足range的个数达到18w(explain估算是36w),也试着加大这个参数从2到800000,都没作用。

#74602 这个说的是low_limit导致的,刚好在 optimizer_trace 里面的 rechecking_index_usage:recheck_reason:low_limit
测试神奇的发发现,上面的查询limit有个分解值,会选择不同的索引:
https://gist.github.com/seanlook/64990b956bf986eaeece5b26055f2f18 limit 8168后默认选择filesort的trace)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> explain select f_some_id from d_ec_some1.t_tbl_test_time_08
where f_qiye_id=5077665 and f_type=9
and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53'
order by f_some_id limit 8167;
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+-------------+
| 1 | SIMPLE | t_tbl_test_time_08 | index | f_contact_time | some_qiye_type | 12 | NULL | 414115 | Using where |
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+-------------+
1 row in set
mysql> explain select f_some_id from d_ec_some1.t_tbl_test_time_08
where f_qiye_id=5077665 and f_type=9
and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53'
order by f_some_id limit 8168;
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+---------------------------------------------------------------+
| 1 | SIMPLE | t_tbl_test_time_08 | range | f_contact_time | f_contact_time | 5 | NULL | 360478 | Using index condition; Using where; Using MRR; Using filesort |
+----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+---------------------------------------------------------------+
1 row in set

具体为啥这样,bug里面没说,只是提到5.7已修复,5.6应该不会修复。

5. 解决办法

其实做sql优化,执行计划不稳定这种,是不容易搞定的,因为并不是这个sql所有都会慢,而是不同值的特征,走不同的索引,会偶发性的慢。优化索引的时候,反而容易把原本较快的查询的索引改掉,造成更大的灾难。

本文的sql便是如此。从业务那边了解到,f_contact_time范围是固定4s,99%的情况,这个索引效率很高,但是正好有一大批数据(18万) f_contact_time=’2017-10-17 14:23:51’,导致优化器做出了自己以为更优的决定。

那么解决这个问题,就是要去掉干扰因素,或者提供更优的选项给它。

  1. 去掉干扰因素
    干扰它的索引是 some_qiye_type,是个唯一索引,因为恰好f_some_id开头,索引优化器想当然的用它来避免排序。
    去掉这个干扰因素就是调换 f_qiye_id,f_some_id的顺序。调换顺序还有个好处,有f_qiye_id等值条件,可以用在索引检索上。
    但是它的负面作用有两个:

    • 原本这个表上有 f_some_id 的等值、join ref以及分页查询,调换索引这两个字段顺序后,全都变成慢查询
    • f_qiye_id作为第一列,满足条件的值可能会有上百万,对这个查询的改观不大
  2. 提供更优的索引
    添加索引 (f_qiye_id,f_contact_time) 看起来不错。这样一来,该类查询都会走这个索引

另外这个表上只有一个唯一索引,而且该唯一索引有字段允许null,所以没有主键。
加一个自增主键 f_id bigint unsigned not null
修改f_some_id字段为 f_some_id bigint unsigned NOT NULL
修改f_qiye_id字段为 f_qiye_id int unsigned NOT NULL
修改字段f_type字段为 tinyint NOT NULL
总之一句话:所有(作为索引的)字段,都定义为NOT NULL,f_some_id全部定义为bigint。最终表结构:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t_tbl_test_time_16` (
`f_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`f_some_id` bigint(20) unsigned NOT NULL,
`f_qiye_id` int(11) unsigned NOT NULL DEFAULT '0',
`f_type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '有效联系类型 1: QQ联系,2:拨打电话,3:发送邮件,4:发送短信,5:添加跟进记录,6:拜访客户,7:EC联系,8:更新客户阶段',
`f_contact_time` timestamp NOT NULL DEFAULT '1970-01-01 16:00:01',
PRIMARY KEY(f_id),
UNIQUE KEY `some_qiye_type` (`f_some_id`,`f_qiye_id`,`f_type`) USING BTREE,
KEY `idx_qiye_time` (`f_qiye_id`,`f_contact_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

或者唯一索引变成联合主键(关于自增主键与联合主键的选择,参考 http://seanlook.com/2016/05/13/mysql-innodb-primary_key/

1
2
3
4
5
6
7
8
9
CREATE TABLE `t_tbl_test_time_16` (
`f_some_id` bigint(20) unsigned NOT NULL,
`f_qiye_id` int(11) unsigned NOT NULL DEFAULT '0',
`f_type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '有效联系类型 1: QQ联系,2:拨打电话,3:发送邮件,4:发送短信,5:添加跟进记录,6:拜访客户,7:EC联系,8:更新客户阶段',
`f_contact_time` timestamp NOT NULL DEFAULT '1970-01-01 16:00:01',
PRIMARY KEY(`f_qiye_id`,`f_some_id`,`f_type`),
KEY `idx_qiye_time` (`f_qiye_id`,`f_contact_time`),
KEY `idx_some`(`f_some_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

原文连接地址:http://seanlook.com/2017/10/26/mysql-bad-plan-order_by-limit/


支持可请我喝杯茶 ^_- (上限10个铜板)