cq网络网站,电脑pc端网站建设是什么意思,做网站优化排名,2022年小规模企业所得税怎么征收多列索引 专栏内容#xff1a; postgresql使用入门基础手写数据库toadb并发编程 个人主页#xff1a;我的主页 管理社区#xff1a;开源数据库 座右铭#xff1a;天行健#xff0c;君子以自强不息#xff1b;地势坤#xff0c;君子以厚德载物. 文章目录 多列索引概述 …多列索引 专栏内容 postgresql使用入门基础手写数据库toadb并发编程 个人主页我的主页 管理社区开源数据库 座右铭天行健君子以自强不息地势坤君子以厚德载物. 文章目录 多列索引概述 多列索引创建 创建语法 创建说明 案例分析 创建数据 创建索引 带首列查询 不带首列查询 总结 结尾 概述 在实际业务的查询中往往会带有多个过滤条件涉及多个列而索引也能够以多列数据构成。
在postgresql 中一个索引可以由最多32个列来构建如果业务中有多列查询情况可以对此进行优化。
当然多列索引在应用时复杂度会提升应用不当反尔性能下降。
本文就来分享一下多列索引的使用同时通过案例来剖析它的原理揭示它的优势与不足避免遇坑。
多列索引创建 下面分享多列索引的创建语法在postgresql中的限制说明。 创建语法
多列索引的创建SQL语法如下
CREATE INDEX index_name
ON table_name(column1, column2, ...);基本SQL形式与普通索引相同只是在选择表的列时可以指定为多列这里最多为32个列。 创建说明
支持的索引类型
当然也可以使用using子句指定索引类型不指定时默认为btree类型。
在postgresql中多列索引可以使用的索引类型有btree, brin, gin和gist其它类型不支持。
列的顺序
创建索引时指定列的顺序是有讲究的不然会事得其反。
在where条件中使用频繁程度高的列要放在前面依次排序。
比如 column1,column2,column3这样的顺序建索引时那么对于
... where column1 a;
... where column1 a and column2 b;
... where column1 a and column2 b and column3 c;
这三种情况都可以用到该索引其中column1的使用频率最高column2次之最后是column3。
列的数量
虽然多列索引可以支持最大32列通过实践证明最多不要超过两到三列。 案例分析 通过对btree类型的多列索引进行分析看看多列索引的运行机制。 创建数据
为了演示方便我们创建如下数据表。
CREATE TABLE test1 (major int,minor int,last int,name varchar);往表中插入10万条测试数据。
postgres INSERT INTO test1(major,minor,last,name)
select id, (random() * 100000)::int, (random() * 100000 100)::int, name || id::int FROM generate_series(1, 100000) as id;INSERT 0 100000创建索引
在major, minor, last 三列数据上创建一个索引操作如下
postgres create index idx_test1 ON test1 (major ,minor, last);
CREATE INDEX
postgres \d test1Table senlleng.test1Column | Type | Collation | Nullable | Default
---------------------------------------------------------major | integer | | |minor | integer | | |last | integer | | |name | character varying | | |
Indexes:idx_test1 btree (major, minor, last)创建索引idx_test1它是在三列上创建的默认btree索引查看表定义可以看到索引已经创建。
下面我们来看看如何使用此索引。 带首列查询
这里先来看看带有索引首列major带的条件查询它可以分为以下情况
条件带有major, minor, last三列
postgres explain select * from test1 where major 1005 and minor 5000 and last 8000;QUERY PLAN
------------------------------------------------------------------------Index Scan using idx_test1 on test1 (cost0.42..8.44 rows1 width21)Index Cond: ((major 1005) AND (minor 5000) AND (last 8000))
(2 rows)
可以看到使用了刚才创建的索引 idx_test1那么我们将条件中各列的顺序进行调换再来看看。
postgres explain select * from test1 where last 8000 and minor 5000 and major 1005 ;QUERY PLAN
------------------------------------------------------------------------Index Scan using idx_test1 on test1 (cost0.42..8.44 rows1 width21)Index Cond: ((major 1005) AND (minor 5000) AND (last 8000))
(2 rows)同样也使用了索引同时很惊奇的发现 Index Cond: ((major 1005) AND (minor 5000) AND (last 8000)) 索引条件居然与我们where子句中的相反。
这一变动其实由查询优化器来做的它为什么这么做呢 哎看下面的案例分析就明白了。
带major另外两列之一
如果减少其中一列还会用到索引路径吗 下面我们来看看还有第一列major但是其它两列任选一列时会是什么情况发生呢
postgres explain select * from test1 where last 8000 and major 1005 ;QUERY PLAN
------------------------------------------------------------------------Index Scan using idx_test1 on test1 (cost0.42..8.44 rows1 width21)Index Cond: ((major 1005) AND (last 8000))
(2 rows)postgres explain select * from test1 where minor 5000 and major 1005 ;QUERY PLAN
------------------------------------------------------------------------Index Scan using idx_test1 on test1 (cost0.42..8.44 rows1 width21)Index Cond: ((major 1005) AND (minor 5000))
(2 rows)可以看到索引仍然使用到了。
当然单独带有第一列时也是同样可以使用索引的。 不带首列查询
如果不带第一列major时又会是什么情况呢
postgres explain select * from test1 where last 8000 and minor 5000 ;QUERY PLAN
------------------------------------------------------------Seq Scan on test1 (cost0.00..2137.00 rows7814 width21)Filter: ((last 8000) AND (minor 5000))
(2 rows)postgres explain select * from test1 where minor 5000 ;QUERY PLAN
-------------------------------------------------------------Seq Scan on test1 (cost0.00..1887.00 rows95076 width21)Filter: (minor 5000)
(2 rows)postgres explain select * from test1 where last 8000 ;QUERY PLAN
------------------------------------------------------------Seq Scan on test1 (cost0.00..1887.00 rows8218 width21)Filter: (last 8000)
(2 rows)
带有第二列第三列或者它们两者独立作为条件执行计划中都没有使用到索引。
总结 在多列上创建索引时必须把使用最频繁的列放在索引列的最前面 通过案例分析可以看到只有在查询条件中带有第一列时查询计划中才会用到索引即使将条件中各列的顺序打乱优化器也会按索引中的列的顺序进行查找路径。 在使用多列索引时避免出现案例中索引失效的场景。
结尾 非常感谢大家的支持在浏览的同时别忘了留下您宝贵的评论如果觉得值得鼓励请点赞收藏我会更加努力 作者邮箱studysenllang.onaliyun.com 如有错误或者疏漏欢迎指出互相学习。
注未经同意不得转载
