mysql gin索引_gin索引优化实例1

GIN(Generalized Inverted Index, 通用倒排索引) 是一个存储对(key, posting list)集合的索引结构，其中key是一个键值，而posting list 是一组出现过key的位置。如(‘hello', '14:2 23:4')中，表示hello在14:2和23:4这两个位置出现过，在PG中这些位置实际上就是元组的tid(行号，包括数据块ID(32bit),以及item point(16 bit) )。

在表中的每一个属性，在建立索引时，都可能会被解析为多个键值，所以同一个元组的tid可能会出现在多个key的posting list中。

通过这种索引结构可以快速的查找到包含指定关键字的元组，因此GIN索引特别适用于多值类型的元素搜索，比如支持全文搜索，数组中元素的搜索，而PG的GIN索引模块最初也是为了支持全文搜索而开发的。

说到这里，你可能会觉得GIN的结构有点像b+tree，包括KEY和对应的值(posting list)。别急，请继续往下看。

receivers跟reads字段都是数组,使用any函数来查找是个性能差的事情并且加了gin索引CBO也不会用上,使用了2295.317 ms

explain(analyze,verbose,buffers,costs,timing)SELECT"msgId",

"sender",

"contentType",

"content",

"expiresIn",

"timestamp",

"sessionType",

"sessionId",

"isRescission",

"isOncePush",CAST ( "extensions" AS VARCHAR),NULL AS"receivers",NULL AS"reads"FROM"表"WHERE

NOT"isRescission"AND 7189526 = ANY( "receivers" )AND NOT 7189526 = ANY("reads");

QUERYPLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Seq Scan on public."表" (cost=0.00..140829.79 rows=79282 width=206) (actual time=191.069..2295.282 rows=1 loops=1)

Output: "msgId", sender, "contentType", content, "expiresIn", "timestamp", "sessionType", "sessionId", "isRescission", "isOncePush", (extensions)::character varying, NULL::unknown, NULL::unknown

Filter: ((NOT "表"."isRescission") AND (7189526 = ANY ("表".receivers)) AND (7189526 <> ALL("表".reads)))

Rows Removedby Filter: 1776067Buffers: shared hit=79270Planning time:0.092ms

Execution time:2295.317 ms

对表添加gin索引并改写语句达到性能优化目的

CREATE INDEX idxrgin1 on "OnceMessages_test" USING GIN ("receivers");

CREATE INDEX idxrgin2 on "OnceMessages_test" USING GIN ("reads");

改写成array函数 , @>包含, 并强制类型转换::bigint

explain(analyze,verbose,buffers,costs,timing)SELECT"msgId",

"sender",

"contentType",

"content",

"expiresIn",

"timestamp",

"sessionType",

"sessionId",

"isRescission",

"isOncePush",CAST ( "extensions" AS VARCHAR),NULL AS"receivers",NULL AS "reads" FROM "public"."表_test" where "receivers" @> ARRAY[7189526::bigint] and NOT"isRescission"and "reads" @>ARRAY[7189526::bigint] = 'f'Bitmap Heap Scanon public."表_test" (cost=18.98..1484.00 rows=375 width=207) (actual time=0.110..0.110 rows=1 loops=1)

Output: "msgId", sender, "contentType", content, "expiresIn", "timestamp", "sessionType", "sessionId", "isRescission", "isOncePush", (extensions)::character varying, NULL::unknown, NULL::unknown

Recheck Cond: ("表_test".receivers @> '{7189526}'::bigint[])

Filter: ((NOT "表_test"."isRescission") AND (NOT ("表_test".reads @> '{7189526}'::bigint[])))

Rows Removedby Filter: 2Heap Blocks: exact=3Buffers: shared hit=1 read=6I/O Timings: read=0.032

-> Bitmap Index Scan on idxrgin1 (cost=0.00..18.89 rows=385 width=0) (actual time=0.057..0.057 rows=3 loops=1)Index Cond: ("表_test".receivers @> '{7189526}'::bigint[])

Buffers: shared hit=1 read=3I/O Timings: read=0.016Planning time:0.299ms

Execution time:0.149 ms

改写之后性能达到 0.149ms , , 函数不能乱用,随便用上性能会不会更慢也不知道,要多看手册, 中国普遍程序员都是这种想用就用的模式,跟国外完全不一样的先理解再使用

否则研发思维只会越来越落后!

CREATE INDEX idxrgin1 on "OnceMessages_test" USING GIN ("receivers");

CREATE INDEX idxrgin2 on "OnceMessages_test" USING GIN ("reads");