POSTGRESQL区域设置对索引和排序的影响

简介: --区域支持是在使用 initdb 创建一个数据库集群的时候自动初始化的,但可在创建数据库时单独指定--区域设置特别影响下面的 SQL 特性* 查询中使用 ORDER BY 或者对文本数据的标准比较操作符进行排序* upper, lower 和 initcap 函数* 模式匹配运算符(LIKE, SIMILAR TO, 以及 POSIX-风格的正则表达式); 区域影响大小写不敏感的匹配和通过字符分类正则表达式的字符分类。
--区域支持是在使用 initdb 创建一个数据库集群的时候自动初始化的,但可在创建数据库时单独指定


--区域设置特别影响下面的 SQL 特性
* 查询中使用 ORDER BY 或者对文本数据的标准比较操作符进行排序
* upper, lower 和 initcap 函数
* 模式匹配运算符(LIKE, SIMILAR TO, 以及 POSIX-风格的正则表达式); 区域影
响大小写不敏感的匹配和通过字符分类正则表达式的字符分类。
* to_char 函数族
* 使用 LIKE 子句的索引能力




--其中有两个参数可以参考
LC_COLLATE 字符串排序顺序
LC_CTYPE 字符分类(什么是字母?是否区分大小写?)
--LC_COLLATE 和 LC_CTYPE 的设置都是在数据库创建时决定的, 不能被改变除非创建新的数据库


PostgreSQL 里使用非 C 或者 POSIX 区域的缺点是性能影响




--查询服务器支持的区域
select * from pg_collation;


--查看当前数据库的区域设置
show lc_ctype;
show lc_collate;




--下面针对不同的区域查看对索引的影响
--创建数据库,指定区域
createdb test_collcate --lc-collate=C --lc-ctype=C -T template0


test_collcate=# show lc_ctype;
show lc_collate; lc_ctype 
----------
 C
(1 row)


test_collcate=# show lc_collate;
 lc_collate 
------------
 C
(1 row)


--创建测试数据
test_collcate=# create table t3 (id int ,user_name varchar(50));
CREATE TABLE
test_collcate=# insert into t3 select m,'username'||m from generate_series(1,1000000) m;
INSERT 0 1000000
test_collcate=# create index idx_t3_user_name on t3(user_name);   
CREATE INDEX


--可以发现其使用了索引
test_collcate=# explain analyze select count(*) from t3 where user_name like 'username123%';
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=192.92..192.93 rows=1 width=0) (actual time=1.458..1.458 rows=1 loops=1)
   ->  Index Only Scan using idx_t3_user_name on t3  (cost=0.42..192.67 rows=100 width=0) (actual time=0.157..1.313 rows=1111 loops=1)
         Index Cond: ((user_name >= 'username123'::text) AND (user_name < 'username124'::text))
         Filter: ((user_name)::text ~~ 'username123%'::text)
         Heap Fetches: 1111
 Total runtime: 28.043 ms
 
 
 
 
 --在另外一个数据库的区域设置如下
 postgres=# show lc_ctype;
 lc_ctype 
----------
 zh_CN
(1 row)


Time: 0.250 ms
postgres=# show lc_collate;
 lc_collate 
------------
 zh_CN
 
 --测试数据如下
 postgres=# \d+ t2;
                            Table "public.t2"
  Column   |  Type   | Modifiers | Storage  | Stats target | Description 
-----------+---------+-----------+----------+--------------+-------------
 id        | integer |           | plain    |              | 
 user_name | text    |           | extended |              | 
Indexes:
    "idx_t2_user_name" btree (user_name)
Has OIDs: no


--可以看到其不能使用索引
postgres=# explain analyze select count(*) from t2 where user_name like 'rudy1111111111111%'; 
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3583.31..3583.32 rows=1 width=0) (actual time=42.738..42.739 rows=1 loops=1)
   ->  Seq Scan on t2  (cost=0.00..3583.26 rows=20 width=0) (actual time=42.734..42.734 rows=0 loops=1)
         Filter: (user_name ~~ 'rudy1111111111111%'::text)
         Rows Removed by Filter: 200101
 Total runtime: 42.805 ms
(5 rows)


--如果数据库不使用标准的"C"区域设置,对于期望使LIKE 或者 POSIX 正则表达式,使用索引,
--需要指定text_pattern_ops,varchar_pattern_ops 和 bpchar_pattern_ops 操作符类分别支持在 text, varchar 和 char 类型上的 B-tree 索引。 


postgres=# create index idx_t3_user_name on t2(user_name varchar_pattern_ops);   
CREATE INDEX
Time: 450.281 ms


--可以使用索引
postgres=# explain analyze select count(*) from t2 where user_name like 'rudy1111111111111%';
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.49..8.50 rows=1 width=0) (actual time=0.183..0.184 rows=1 loops=1)
   ->  Index Only Scan using idx_t3_user_name on t2  (cost=0.42..8.44 rows=20 width=0) (actual time=0.179..0.179 rows=0 loops=1)
         Index Cond: ((user_name ~>=~ 'rudy1111111111111'::text) AND (user_name ~<~ 'rudy1111111111112'::text))
         Filter: (user_name ~~ 'rudy1111111111111%'::text)
         Heap Fetches: 0
 Total runtime: 0.275 ms
 
 
 --注意对于等于操作,如果字段上有索引,其是可以使用索引的

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
391 4
|
存储 监控 关系型数据库
B-tree不是万能药:PostgreSQL索引失效的7种高频场景与破解方案
在PostgreSQL优化实践中,B-tree索引虽承担了80%以上的查询加速任务,但因多种原因可能导致索引失效,引发性能骤降。本文深入剖析7种高频失效场景,包括隐式类型转换、函数包裹列、前导通配符等,并通过实战案例揭示问题本质,提供生产验证的解决方案。同时,总结索引使用决策矩阵与关键原则,助你让索引真正发挥作用。
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
1131 81
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
440 2
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
505 9
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
449 12
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
716 22
 MySQL秘籍之索引与查询优化实战指南

热门文章

最新文章

推荐镜像

更多