索引的使用
专栏内容：
- postgresql使用入门基础
- 手写数据库toadb
- 并发编程
个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.
文章目录
概述
在postgresql中内置了丰富的索引类型如btree, hash, gin, gist, sp-gist，还有扩展的bloom等等索引，同时还可以有不同的用途，如主键，外键，唯一性等。
本文分享如何使用不同类型的索引，如创建，查看，删除；同时通过查看执行计划，了解查询状态与索引使用情况，最后通过案例如果分析增加适合自己的索引。
索引
在《postgresql 基础入门》专栏中分享了创建默认类型索引的SQL语法，这里主要介绍创建不同类型索引的语法，同时分享查看索引，删除索引的语法。
创建索引
创建索引的语法如下：
createindex index_name ON tablename using index_type ( column1 );
其中：
- index_name，本次创建的索引名称，长度在64个字符以内；
ON关键字之后是表名tablename，在此表的某一列上创建索引，指定列名为 column1；using关键字指示使用的索引算法类型，可以使用btree,hash等内置的索引类型；
下面我们在
product
表的上创建hash索引。
表的定义如下：
-- 创建产品表 CREATETABLE products (
product_id INTPRIMARYKEY,
product_name VARCHAR(255)NOTNULL,
price DECIMAL(10,2)NOTNULL,
category VARCHAR(255));
可以插入几条数据，也可以使用前面章节《物化视图》提到的方法初始化大量数据来对比。
这里已经有了很多数据，先打开客户端的执行时间统计功能，查询名称wfplpgsbre为的商品。
postgres=# \timing on
Timing ison.
postgres=# select * from products where product_name='wfplpgsbre';
product_id | product_name | price | category
------------+--------------+--------+-----------44| wfplpgsbre |199.66| Category4
(1row)Time: 4.828 ms
然后在商品名称列上创建hash类型的索引。
postgres=# create index idx_pro_name ON products using hash ( product_name );CREATEINDEXTime: 64.740 ms
这里我们对比一下增加索引后的查询效果。
postgres=# select * from products where product_name='wfplpgsbre';
product_id | product_name | price | category
------------+--------------+--------+-----------44| wfplpgsbre |199.66| Category4
(1row)Time: 0.279 ms
可以看到增加索引后，查询明显变快了，效果提升了20倍。
查看索引
在postgresql的命令行客户端上，可以用以下几种方法来查看。
- 使用psql客户端的
\di命令
postgres=>\di
List of relations
Schema | Name | Type | Owner | Table
----------+---------------+-------+----------+----------
senlleng | idx_pro_name | index | senllang | products
senlleng | orders_pkey | index | senllang | orders
senlleng | products_pkey | index | senllang | products
(3 rows)postgres=>\di idx_pro_name
List of relations
Schema | Name | Type | Owner | Table
----------+--------------+-------+----------+----------
senlleng | idx_pro_name | index | senllang | products
(1 row)
\di
命令是display index 缩写，如果不带索引名称，就会查询当前数据库中的所有索引，可以看到有主键索引，还有刚才创建的索引。
如果带索引名称，就会查看该索引的详细信息。
- 另一种是查看表的定义，使用
\d命令
postgres=>\d products
Table "senlleng.products"
Column | Type | Collation | Nullable | Default
--------------+------------------------+-----------+----------+---------
product_id | integer || not null |
product_name | character varying(255)|| not null |
price | numeric(10,2)|| not null |
category | character varying(255)|||
Indexes:
"products_pkey" PRIMARY KEY, btree (product_id)"idx_pro_name"hash(product_name)
Referenced by:
TABLE "orders" CONSTRAINT "orders_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)
在表的详细信息中，会列出表上的索引列表，可以看到products表中，有两个索引，分别是products_pkey和idx_pro_name。
删除索引
删除索引就比较简单，语法如下：
dropindex index_name;
删除上面创建的索引。
postgres=>dropindex idx_pro_name ;DROPINDEXTime: 9.890 ms
查看执行计划
索引经常用于提升SQL的查询性能，性能的优化是一个综合而复杂的事情，这里介绍一个常用的手段，就是查看执行计划。
执行计划是数据库将输入SQL命令解析之后，根据当前数据表的状态，如表中数据行的多少，被查询的列上是否有索引等等，对每一种路径都会统计其耗时，然后选出一个耗时较少的路径来最终执行，这就是执行计划。
当然生成执行计划的过程很复杂，但是我们可以查看执行计划，来评估执行的效率，决定是否需要增加索引来优化，以及使用那种类型索引更优秀。
查看执行计划，使用命令
explain
。
在执行SQL前加
explain
就会打印执行计划，查看一下查询产品的SQL。
postgres=>explainselect*from products where product_name='lrqfankmib';
QUERY PLAN------------------------------------------------------------
Seq Scan on products (cost=0.00..1992.00rows=1 width=31)
Filter: ((product_name)::text='lrqfankmib'::text)(2rows)Time: 0.237 ms
这是索引已经删除的情况下，可以看到执行计划中
Seq Scan on products
，说明在这个张表上执行顺序扫描，也就是从头遍历查找，要把表整个遍历一次。
在数据量少时，顺序扫描还是可以接受的，在大数据分析场景下，那简直是灾难级的。
下面我们通过案例来看看，如何使用索引。
案例分析
顺序查找效率比较低时，我们给常用字段加一个索引。
添加默认索引类型
给产品名增加默认索引类型btree，来看看执行计划和性能的变化情况。
postgres=>createindex btree_proname on products usingbtree(product_name );CREATEINDEXTime: 122.786 ms
看一下执行计划。
postgres=>explainselect*from products where product_name ='lrqfankmib';
QUERY PLAN-------------------------------------------------------------------------------Index Scan using btree_proname on products (cost=0.42..8.44rows=1 width=31)Index Cond: ((product_name)::text='lrqfankmib'::text)(2rows)Time: 0.322 ms
可以看到用到了我们刚才创建的索引
Index Scan using btree_proname on products
。
下面实际再执行一遍，看一下耗时的变化。
postgres=>select*from products where product_name ='lrqfankmib';
product_id | product_name | price | category
------------+--------------+--------+-----------51| lrqfankmib |254.70| Category2
(1row)Time: 0.242 ms
确实快了很多，到1ms以下了。
创建hash索引
按商品名称的特点，一般不会使用比较运算，符合hash算法的特点，我们在商品名上创建hash索引。
postgres=>createindex hash_proname on products usinghash( product_name);CREATEINDEXTime: 83.966 ms
postgres=> \d products
Table"senlleng.products"Column|Type| Collation | Nullable |Default--------------+------------------------+-----------+----------+---------
product_id |integer||notnull|
product_name |charactervarying(255)||notnull|
price |numeric(10,2)||notnull|
category |charactervarying(255)|||
Indexes:
"products_pkey"PRIMARYKEY,btree(product_id)"btree_proname"btree(product_name)"hash_proname"hash(product_name)
Referenced by:
TABLE"orders"CONSTRAINT"orders_product_id_fkey"FOREIGNKEY(product_id)REFERENCES products(product_id)
可以看到创建hash索引之后，商品名上有两个索引，也就是说按索引执行的路径就有两种。
再来看一下执行计划吧。
postgres=>explainselect*from products where product_name ='lrqfankmib';
QUERY PLAN------------------------------------------------------------------------------Index Scan using hash_proname on products (cost=0.00..8.02rows=1 width=31)Index Cond: ((product_name)::text='lrqfankmib'::text)(2rows)Time: 0.286 ms
btree与hash索引比较
可以看到执行计划中使用了刚创建的hash索，也就是说经过postgresql 数据库的计算，还是使用hash索引耗时较短。
下面实际执行一下。
postgres=>select*from products where product_name ='lrqfankmib';
product_id | product_name | price | category
------------+--------------+--------+-----------51| lrqfankmib |254.70| Category2
(1row)Time: 0.157 ms
可以看到，与btree索引差别非常小，在毫秒级，可能多次执行统计会更准确一些。
总结
本文介绍了在postgresql 中使用不同类型索引的方法，通过查看SQL的执行计划，来评估查询的性能，一般采用索引查询。当然不同数据类型，要采用对应的索引类型，会取得较好的效果，最后通过在商品名称上创建btree与hash索引，对应字符匹配hash索引更符合一些。
结尾
非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！
作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。
注：未经同意，不得转载！
版权归原作者 韩楚风 所有， 如有侵权，请联系我们删除。