虚谷数据库的联合索引在查询中的使用规律

最新推荐文章于 2024-09-25 11:06:23 发布

Spyro丶

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量368

点赞数 5

分类专栏：虚谷数据库文章标签：数据库

本文链接：https://blog.csdn.net/qq_44528523/article/details/141557896

版权

虚谷数据库专栏收录该内容

10 篇文章 1 订阅

订阅专栏

索引可以为数据定位带来高性能，高效率，但并非表中创建的索引越多越好，因为利用索引提高查询效率是以额外占用存储空间为代价的，而且为了维护索引的有效性，当对表中数据进行操作时，数据库需要对索引进行动态维护。
索引设计可参考以下原则：
• 在常用的数据定位列上创建索引（即 WHERE 子句中出现的列）。
• 在表的主键、外键上创建索引（默认创建的有索引）。
• 在经常用于表与表之间连接的字段上创建索引。
• 查询几乎没有涉及到的列不纳入索引列。
• 针对重复度过高的列不纳入索引列或不创建索引。
• 当该表写的性能比查询的性能要求高时，应少建或者不建索引。

例表添加主键后，自动创建了多列唯一值约束和联合索引：(“grid_labels”,“published_at”,“forecast_at”)

alter table AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST 
add constraint "WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST_PK" 
primary key(grid_labels,published_at,forecast_at);

下面分别看下不同where条件下的执行计划，三个字段的索引序对应：
grid_labels：1
published_at：2
forecast_at：3

一、单列过滤

1、EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where grid_labels='2-56'; --走索引

在这里插入图片描述

2、EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where published_at='2024-08-07 00:00:00';--不走索引

在这里插入图片描述

3、EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where forecast_at='2024-08-12 15:00:00';--不走索引

在这里插入图片描述

二、两个字段过滤

(1,2)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where grid_labels='2-56'
and published_at='2024-08-07 00:00:00';--走索引

(1,3)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where grid_labels='2-56'
and forecast_at='2024-08-12 15:00:00';--走索引

(2,1)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where published_at='2024-08-07 00:00:00'
and grid_labels='2-56';--走索引

(2,3)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where published_at='2024-08-07 00:00:00'
and forecast_at='2024-08-12 15:00:00';--不走

(3,1)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where forecast_at='2024-08-12 15:00:00'
and grid_labels='2-56';--走索引

(3,2)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where forecast_at='2024-08-12 15:00:00'
and published_at='2024-08-07 00:00:00';--不走

三、三个字段过滤

(1,2,3)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where grid_labels='2-56'
and published_at='2024-08-07 00:00:00'
and forecast_at='2024-08-12 15:00:00';--走索引

(1,3,2)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where grid_labels='2-56'
and forecast_at='2024-08-12 15:00:00'
and published_at='2024-08-07 00:00:00';--走索引

(2,1,3)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where published_at='2024-08-07 00:00:00'
and grid_labels='2-56'
and forecast_at='2024-08-12 15:00:00';--走索引

(2,3,1)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where published_at='2024-08-07 00:00:00'
and forecast_at='2024-08-12 15:00:00'
and grid_labels='2-56';--走索引

(3,1,2)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where forecast_at='2024-08-12 15:00:00'
and grid_labels='2-56'
and published_at='2024-08-07 00:00:00';--走索引

(3,2,1)
EXPLAIN VERBOSE
select * from AECC_PLAT.WRF_192H_POSTD03_FORECAST_WEATHER_NEWEST
where forecast_at='2024-08-12 15:00:00'
and published_at='2024-08-07 00:00:00'
and grid_labels='2-56';--走索引