mysql - 当我们在 mysql 中有大量行时，索引将不起作用

Question

当我使用小日期范围时，索引工作正常。查询是

EXPLAIN select sum(principalBalance) as pos, count(id) as TotalCases, sum(amountPaid) as paid, count(amountPaid) as paidCount, sum(amountPdc) as Pdc,sum(amountPtp),count(amountPtp) from caseDetails USE index (updatedAt_caseDetails) where updatedAt between '2016/06/01 00:00:00' AND '2016/07/26 23:59:00'

查询结果:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  caseDetails     range   updatedAt_caseDetails   updatedAt_caseDetails   6       607554  100 Using index condition; Using MRR

在此结果中，您可以看到 key=updatedAt_caseDetails 索引正在运行

但是当我们增加日期范围索引时，索引将不起作用:

EXPLAIN select sum(principalBalance) as pos, count(id) as TotalCases, sum(amountPaid) as paid, count(amountPaid) as paidCount, sum(amountPdc) as Pdc,sum(amountPtp),count(amountPtp) from caseDetails USE index (updatedAt_caseDetails) where updatedAt between '2016/06/01 00:00:00' AND '2016/07/30 23:59:00'

查询结果:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  caseDetails     ALL updatedAt_caseDetails               2898377 22.94   Using where

Answer 1

我应该使用 force index() 而不是使用 index(): 使用力指数它工作正常。

结果查询将是:

EXPLAIN 选择 sum(principalBalance) 作为 pos，count(id) 作为 TotalCases，sum(amountPaid) 作为 paid，count(amountPaid) 作为 paidCount，sum(amountPdc) 作为 Pdc，sum(amountPtp)，count(amountPtp)来自 caseDetails force index (updatedAt_caseDetails) where updatedAt between '2016/06/01 00:00:00' AND '2016/07/26 23:59:00'

结果将是: