PgSQL如何优雅的加模糊查询索引

「这是我参与2022首次更文挑战的第6天，活动详情查看：2022首次更文挑战」。

今天我以pgsql模糊搜索加索引优化作为切入点，介绍一下pgsql索引优化的一些知识点。

模糊搜索实例

模糊查询sql语句

EXPLAIN ANALYSE SELECT * from tbl_school_info WHERE name like '%清华%'

不加索引的执行效果：

添加索引

1. 引入索引扩展包

CREATE EXTENSION pg_bigm;

2. 创建索引

CREATE INDEX school_name_like_index ON tbl_school_info USING gin (name PUBLIC.gin_bigm_ops);

添加索引之后的执行效果：

优化效果

执行速度提高了22倍

总结

由此可见合理添加索引的重要性。

延伸

网络上介绍添加索引的文章很多，我在这里就不搬砖了。

我分享一下索引不生效，不命中的场景供大家参考，掘友们千万要避坑哦~

会索引失效的情况：

1. 任何计算、函数、类型转换

2. !=

3. NOT，相当于使用函数

4. 模糊查询通配符在开头

5. 索引字段在表中占比较高

6. 多字段btree索引查询条件不包含第一列

7. 多字段索引查询条件使用OR（有时也会走索引扫描，但查询效率不高）

8. 当order by 中的字段出现在where条件中时，才会利用索引而不排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作，这个结论不仅对order by有效，对其他需要排序的操作也有效，如group by 、union 、distinct等。这种情景下若要走索引需单独建立一个order by 条件的索引，并删除带order by条件的联合索引，不仅postgresql如此，mysql也会这样。