索引失效场景

Postgresql数组和JSON数据类型字段索引创建方式

数组—GIN索引

1.创建GIN索引

CREATE INDEX "logs_index" ON "public"."tb_security_event" USING gin (
  "logs" COLLATE "pg_catalog"."default" "pg_catalog"."array_ops"
);

2.添加索引执行结果

是否使用索引	索引名称	是否索引覆盖	是否回表	执行时间
是	logs_index	否	是	2.747ms

3.未添加索引执行结果

---

JSONB内部字段B-Tree索引 --操作符 ->>

1.创建索引

CREATE INDEX "notification_status_index" ON "public"."tb_security_event" USING btree (
  (notification ->> 'status'::text) COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

2.添加索引执行结果

是否使用索引	索引名称	是否索引覆盖	是否回表	执行时间
是	notification_status_index	否	是	0.154ms

3.未添加索引执行结果

---

JSONB内部字段B-Tree索引 --操作符 #>>

1.创建索引

CREATE INDEX "notification_status#_index" ON "public"."tb_security_event" USING btree (
  (notification #>> '{status}'::text[]) COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);	

2.添加索引执行结果

是否使用索引	索引名称	是否索引覆盖	是否回表	执行时间
是	notification_status#_index	否	是	0.071ms

3.未添加索引执行结果

---

JSONB字段GIN索引 --操作符 @>

1.创建索引

CREATE INDEX "notification_index" ON "public"."tb_security_event" USING gin (
  "notification" "pg_catalog"."jsonb_ops"
);

2.添加索引执行结果

是否使用索引	索引名称	是否索引覆盖	是否回表	执行时间
是	notification_index	否	是	0.608ms

3.未添加索引执行结果

---

JSONB内部字段GIN索引 --操作符 ?

1.创建索引

CREATE INDEX "notificationStatus_index" ON "public"."tb_security_event" USING gin (
  (notification -> 'status'::text) "pg_catalog"."jsonb_ops"
);

2.添加索引执行结果

是否使用索引	索引名称	是否索引覆盖	是否回表	执行时间
是	notificationStatus_index	否	是	0.046ms

3.未添加索引执行结果

数组 和 JSONB索引创建建议

1.数组索引创建 使用GIN索引

2.JSONB索引结合目前项目代码对JSON的处理以及操作符之间查询比较，索引创建使用 JSONB内部字段B-Tree索引 --操作符 ->>

• JSONB #>>比较的为text[]

• JSONB @>比较的是文本包含

• JSONB ? 比较的是键值

  ---

索引失效场景

1、任何计算、函数、类型转换 2、!=、<> 3、IS NULL或者IS NOT NULL。类似导致索引失效的还有NOT IN，NOT LIKE等，但是NOT EXISTS不会导致索引失效。 4、模糊查询通配符在开头 5、索引字段在表中占比较高 6、多字段btree索引查询条件不包含第一列 7、在WHERE中使用OR时，有一个列没有索引，那么其它列的索引将不起作用 8、多字段索引查询条件使用OR 在索引列上进行大于大于这类的比较后，这个列的索引是有效的，但是其后的索引将失效

操作符说明: