mysql - 即使有索引，对具有 6M+ 记录的表的查询速度也很慢

Question

我最近将旧记录从一个令人不安的设计表迁移到一个新表，我需要根据给定的时间范围过滤记录 - 假设我需要从 2017 年 1 月 1 日到 2017 年 12 月 31 日的所有记录。表显然有 2014 年至今约 6M+ 条记录。

一个非常基本的查询大约需要 20~ 秒。我还在 WHERE 子句中引用的列上添加了索引，但 MySQL 似乎因此没有使用它。我需要表中存在的所有列(我可以创建复合索引，但我只需要几列)。

以下是我的查询:

SELECT
    pt.* -- no index utilized at all @ 24.232s
    --  pt.out_date, pt.pt_id, pt.pi_id, pt.p_id, pt.out_status | makes use of cmpst_ndx_1 @ 9.380s
FROM
    process_trail pt
WHERE
    DATE(pt.out_date) BETWEEN DATE('2017-01-01') AND DATE('2017-12-31');

这是我的表格的定义:

Field           Type            Null        Key     Default     Extra
===============================================================================
pt_id           int(11)         NO          PRI                 auto_increment
pi_id           int(11)         NO          MUL     
p_id            int(11)         NO          
in_date         datetime        NO          MUL     
in_by_id        int(11)         NO          
out_date        datetime        NO          MUL     
out_by_id       int(11)         NO          
out_status      varchar(255)    NO          
out_remarks     text            NO          
tech_remarks    text            NO      

接下来是我制作的索引:

Name        Fields      Index Type  Index Method    Collation   Cardinality
============================================================================
in_date     in_date     NORMAL      BTREE           A           3094370
out_date    out_date    NORMAL      BTREE           A           3001272
pi_id       pi_id       NORMAL      BTREE           A           302772  

EXPLAIN 语法向我展示了这个:

id  select_type table   partitions  type    possible_keys   key     key_len     ref     rows        filtered    Extra
==============================================================================================================================
1   SIMPLE      pt      (NULL)      ALL     (NULL)          (NULL)  (NULL)      (NULL)  3246351     100         Using where

Answer 1

我的解决方案是

WHERE
    pt.out_date BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 23:59:59'

因为日期功能可能会变慢