时间戳

首页 > 解决方案 > 优化二级索引和日期范围查询

mysql - 优化二级索引和日期范围查询

问题描述

我正在运行一个聚合查询，它花费的时间比预期的要长得多。查询来自没有连接的单个表。where 子句包括日期范围、in子句和日期列。表中只有大约 5k 行，查询时间为 13s。

查询是：

select `site_id`, created_year_month_idx as time_column, count(*) as total 
from `patients` 
where `created_year_month_idx` between 20080101 and 20090101 and 
   `site_id` in (1,2,3) and 
   `patients`.`deleted_at` is null 
group by `created_year_month_idx`, `site_id`

当我解释查询时，它似乎在进行全表扫描：

| id  | select_type | table    | partitions | type  | possible_keys                                 | key                                   | key_len | ref | rows | filtered | Extra                                        |
| --- | ----------- | -------- | ---------- | ----- | --------------------------------------------- | ------------------------------------- | ------- | --- | ---- | -------- | -------------------------------------------- |
| 1   | SIMPLE      | patients |            | range | site_id,patients_created_year_month_idx_index | patients_created_year_month_idx_index | 4       |     | 1    | 100      | Using where; Using temporary; Using filesort |

表创建语句是：

CREATE TABLE `sites` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10),
 PRIMARY KEY (`id`)
);

CREATE TABLE `patients` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `site_id` int(10) unsigned NOT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `deleted_at` timestamp NULL DEFAULT NULL,
 `created_year_month_idx` date GENERATED ALWAYS AS (date_format(`created_at`,'%Y-%m-01')) VIRTUAL,
 PRIMARY KEY (`id`),
 KEY `site_id` (`site_id`),
 KEY `patients_created_year_month_idx_index` (`created_year_month_idx`),
 CONSTRAINT `patients_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`)
);

我在https://www.db-fiddle.com/f/4zbjFpMYXEGSviprQcaTm3/0创建了一个 DB Fiddle

（顺便说一句，如果你能告诉我如何在 SO 上格式化降价表，我会解决上述问题）

标签： mysqlsqlquery-optimization

解决方案

我投票给

INDEX(`deleted_at`, `created_year_month_idx`, `site_id`)

但主要是因为它是“覆盖”。 deleted_at是第一个，因为它本质上是一个相等测试 ( IS NULL)。

你知道你有一年加一天吗？ BETWEEN 20080101 AND 20090101

你真的想要大约 1K 行的输出吗？

推荐阅读