mysql - 如何优化datetime条件的查询？

Question

我有一张 table cashflow和 4 索引 [is_dp ], [ is_wd ], [ is_dis ], [ time ]。

`is_dp`   tinyint(1) => (0,1)
`is_wd`   tinyint(1) => (0,1)
`is_dis`  tinyint(1) => (0,1)
`time`    datetime

该表有 300 万行。

我的查询是:

1. SELECT * FROM `cashflow` WHERE is_dp = 1 AND time >= '2019-05-01 00:00:00' AND time <= '2019-05-31 23:59:59';

2. SELECT * FROM `cashflow` WHERE is_wd = 1 AND time >= '2019-05-01 00:00:00' AND time <= '2019-05-31 23:59:59';

3. SELECT * FROM `cashflow` WHERE is_dis = 1 AND time >= '2019-05-01 00:00:00' AND time <= '2019-05-31 23:59:59';

索引time不适用于每个键 [is_dp ], [ is_wd ], [ is_dis ].

解释:

| query | possible_keys  |  key   | key_len |  ref  | rows    | Extra                               |
| 1.    | is_dp,time     | time   | 8       | NULL  | 242616  | Using index condition; Using where; |
| 2.    | is_wd,time     | is_wd  | 1       | const | 494546  | Using where;                        |
| 3.    | is_dis,time    | is_dis | 1       | const | 1089870 | Using where;                        |

如何优化？

或者复合键是一个好方法吗？

ADD KEY `dp_time` (`is_dp`,`time`);
ADD KEY `wd_time` (`is_wd`,`time`);
ADD KEY `dis_time` (`is_dis`,`time`);

非常感谢!

Answer 1

对于那些 3 个SELECT，您需要全部 3 个那些复合索引。建议的 4 列索引对于其中任何一个都不是最佳的。

索引从左侧开始使用。使用 INDEX(is_dp, is_wd, is_dis, time)，查询 1 可以使用 is_dp，但在命中 is_wd 时退出索引。其他两个查询根本无法使用索引。

(我刚才说的并不完全正确，但即使使用，它们也效率不高。)

我的Cookbook更多地解释了如何构建最佳索引。

使用索引条件——意味着InnoDB将在引擎内部而不是处理程序中完成大部分或全部过滤。 (好吧，这是一个神秘的解释；该查询不是讨论 ICP(索引条件下推)的好工具。)

使用where - 使用了一些WHERE。比较模糊。

key_len = 1 - 表示仅使用了索引的 1 个字节；大概是 is_wd 等。