Как использовать функциональный индекс MySQL в столбце datetime?

Question

Скажем, я запускаю MySQL 8 с таблицей dataсодержащий около 1 млн строк. И я хочу отфильтровать datetime колонка на date диапазон (с использованием индекса даты).

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
) ENGINE=InnoDB;

Тогда фильтруем строки только с 2018 года, скажем:

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! 

Есть ли хороший способ использовать сгенерированный (функциональный) индекс вместо сгенерированного столбца?

Последнее утверждение не работает даже с USE INDEX или же FORCE INDEX...

Спасибо вам всем:)

Answer 1

Я бы переписал ваши запросы и удалил CASTи используйте созданный им столбец, так как в нем нет преобразования, но ему нужно место.

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;
    

| СЧЁТ (*) |
| -------: |
| 4 |

    SELECT COUNT(*) FROM data FORCE INDEX (testin)
    WHERE DATE(created) BETWEEN '2018-01-01' AND '2018-12-31';

| СЧЁТ (*) |
| -------: |
| 4 |

    EXPLAIN SELECT COUNT(*) FROM data FORCE INDEX (testin) 
    WHERE DATE(created) BETWEEN '2018-01-01' AND '2018-12-31'

id | select_type | стол | перегородки | тип | possible_keys | ключ | key_len | ref  | строки | фильтрованный | Extra                   
-: |:---------- |:---- |:--------- |:---- |:------------ |:----- |:------ |:--- | ---: | -------: |:-----------------------
 1 | ПРОСТО | данные | null        | индекс | null           | тестин | 6 | null | 4 | 100.00 | Используя где; Использование индекса

    SELECT COUNT(*) FROM data 
    WHERE created BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:49:59';

| СЧЁТ (*) |
| -------: |
| 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 | стол | перегородки | тип | possible_keys | ключ | key_len | ref  | строки | фильтрованный | Extra                   
-: |:---------- |:---- |:--------- |:---- |:------------ |:----- |:------ |:--- | ---: | -------: |:-----------------------
 1 | ПРОСТО | данные | null        | индекс | тестин | тестин | 6 | null | 4 | 100.00 | Используя где; Использование индекса

db<> скрипка здесь