mysql - 用于日期范围查询的聚簇索引与非聚簇索引

Question

我在 mySql 中有这个查询:

SELECT field1
FROM table1
WHERE tdate > '2018-6-01' OR (tdate BETWEEN '2015-4-07' AND 2016-6-07);

tdate 上的索引或 (tdate, field1) 上的覆盖索引，什么会提高查询性能？

Answer 1

您可以使用 included columns 创建索引:

Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

CREATE INDEX my_idx_name ON tab_name(tdate) INCLUDE (field1)