如何使MySQL在datetime列上使用功能索引? [英] How to make MySQL use functional index on a datetime column?
问题描述
假设我正在使用包含约1M行的表 data
运行MySQL 8.我想在 date
范围(使用日期索引)上过滤 datetime
列.
Say I'm running MySQL 8 with a table data
containing about 1M rows. And I want to filter a datetime
column on date
a range (using a date index).
CREATE TABLE `data` (
`rowId` int NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
`created` DATETIME NOT NULL, -- <-- datetime column for a date index
`created_date` DATE AS (cast(`created` as date)) NOT NULL, -- <-- generated date column
PRIMARY KEY (`rowId`),
INDEX (`created`), -- <-- datetime index w/ cardinality ~ 1M
INDEX (`created_date`) -- <-- date index w/ cardinality of 1250
INDEX `created_cast` ((cast(`created` as date))) -- <-- functional "date" index w/ cardinality of 1250
-- ^ I WANT TO USE THIS INDEX, BUT DON'T KNOW HOW
) ENGINE=InnoDB;
然后让我们仅过滤2018年以后的行,比如说:
Then let's filter rows only from 2018, let's say:
SELECT COUNT(*) FROM data
WHERE created >= CAST('2018-01-01' AS DATE) AND created < CAST('2019-01-01' AS DATE);
-- Query time: 0.16 s
-- EXPLAIN shows: key: created, Using where; Using index
-- Uses the whole datetime index w/ cardinality of ~ 1M
SELECT COUNT(*) FROM data
WHERE created_date BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-31' AS DATE);
-- Query time: 0.09 s
-- EXPLAIN shows: key: created_date, Using where; Using index
-- Uses the date index w/ cardinality of 1250
SELECT COUNT(*) FROM data
WHERE CAST(created AS DATE) BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-31' AS DATE);
-- Query time: 0.35 s, that's slow!
-- EXPLAIN shows: key: NULL, Using where
-- Doesn't use any index at all!
是否可以使用此功能索引?
以上查询使用 created_date
(生成列的日期)索引.
Is there a way to use this functional index?
The queries above use either created
(datetime) or created_date
(date from generated column) indexes.
是否可以直接使用功能索引 created_cast
?我什至尝试使用 USE INDEX
或 FORCE INDEX
,但没有运气.
Is there a way to use functional index created_cast
directly? I've even tried with USE INDEX
or FORCE INDEX
, but no luck.
谢谢大家:)
推荐答案
我将重写您的查询并删除 CAST
并使用他生成的列,因为其中没有任何转换,但它需要空间.
I would rewrite your queries and remove the CAST
and use the he generated column, as you have no conversion in it, but it needs space.
CREATE TABLE data (created datetime,
`created_date` DATE AS (cast(`created` as date)) NOT NULL, INDEX testin (`created`), -- <-- datetime index w/ cardinality ~ 1M
INDEX (`created_date`))
✓
INSERt INTO data (created) VALUEs('2018-02-01'),('2018-02-01'),('2018-02-01'),('2018-02-01')
✓
SELECT COUNT(*) FROM data
WHERE YEAR(`created`) = 2018;
| COUNT(*) |
| -------: |
| 4 |
SELECT COUNT(*) FROM data FORCE INDEX (testin)
WHERE DATE(created) BETWEEN '2018-01-01' AND '2018-12-31';
| COUNT(*) |
| -------: |
| 4 |
EXPLAIN SELECT COUNT(*) FROM data FORCE INDEX (testin)
WHERE DATE(created) BETWEEN '2018-01-01' AND '2018-12-31'
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
-: | :---------- | :---- | :--------- | :---- | :------------ | :----- | :------ | :--- | ---: | -------: | :-----------------------
1 | SIMPLE | data | null | index | null | testin | 6 | null | 4 | 100.00 | Using where; Using index
SELECT COUNT(*) FROM data
WHERE created BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:49:59';
| COUNT(*) |
| -------: |
| 4 |
EXPLAIN SELECT COUNT(*) FROM data
WHERE created BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:49:59';
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
-: | :---------- | :---- | :--------- | :---- | :------------ | :----- | :------ | :--- | ---: | -------: | :-----------------------
1 | SIMPLE | data | null | index | testin | testin | 6 | null | 4 | 100.00 | Using where; Using index
db<>小提琴此处
这篇关于如何使MySQL在datetime列上使用功能索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!