一、mysql索引
MySQL索引跟pgsql索引类似,都是为了加速查询而创建的。MySQL索引有主键索引、唯一索引、普通索引、全文索引、空间索引,其中最常用的是B-Tree索引,而MyISAM存储引擎还有Hash索引。
二、pgsql索引行超出btree长度
一般情况下,我们创建索引时指定列和索引类型即可。但当列长度过长时,如果直接创建索引,则在查询时会出现“行行超过btree最大长度”的错误。这时我们就需要使用函数对列进行截断处理。例如:
CREATE INDEX idx_temp ON table1 (substring(col1, 1, 10));
三、pgsql索引的内存
索引被存储在内存中,索引越小,查询效率越高。当我们使用“EXPLAIN ANALYZE”来分析查询计划时,可以看到索引占用的内存大小。如果内存不足,可以通过调整“shared_buffers”参数来加大索引占用的内存空间。例如:
shared_buffers = 1024MB
四、pgsql索引创建
我们可以使用CREATE INDEX命令来创建索引。参数包括表名、列名和索引类型等。例如:
CREATE INDEX idx_name ON table_name (column_name);
五、pgsql索引类型
pgsql支持多种索引类型,包括B-Tree、哈希表、GiST和SP-GiST等。B-Tree是pgsq最常用的索引类型,哈希表可以在精确匹配时效率更高,GiST可以在多维空间中使用,而SP-GiST则是空间分割的基础。下面是创建不同类型索引的示例:
--创建B-Tree索引 CREATE INDEX idx_name ON table_name USING btree (column_name); --创建哈希表索引 CREATE INDEX idx_name ON table_name USING hash (column_name); --创建GiST索引 CREATE INDEX idx_name ON table_name USING gist (column_name); --创建SP-GiST索引 CREATE INDEX idx_name ON table_name USING spgist (column_name);
六、pgsql索引原理
B-Tree索引是在数据结构中利用了平衡树的思想。它的原理是将数据按照索引列的值进行排序,然后将排序后的数据按照一定的规则划分成多个块(也称之为节点),并生成多层树状结构,每个节点都包含了自己范围内的元素。这整个过程就是使用B-Tree算法生成索引的过程。简单来说,就是把数据存到一棵树里,然后根据每个节点的取值范围,在树的各个层级中查找。
七、pgsql索引结构
B-Tree索引结构由根节点、中间节点和叶子节点三部分组成。每个节点都存储一定数量的键和指针。中间节点以及根节点都存储键和指针,用于连接下一层节点。叶子节点存储实际数据和行指针。下面是一个B-Tree索引结构的图示:
+------+---+---+---+---+ | | | | | | | K | P | P | P | P | | | | | | | +------+---+---+---+---+ / | | \ / \ | | \ / | | | / \ | | \ / | \ / / \ \ / \ L K P K P K K P K R (其中,K代表索引键,P代表指针)
八、pgsql索引优化
优化索引可以在查询效率上有所提升。例如,在查询字段少但需要返回的数据比较多时,我们可以使用索引包含需要返回的数据。如果涉及到多表查询时,将复杂查询拆解成多个简单查询,使用索引连接返回的数据。如果使用索引查询以后还需要对数据进行排序、分组等操作,那么我们可以考虑新建索引,或者在现有的索引基础上新增字段。
九、pgsql索引分区
当数据量过大时,我们可以使用索引分区来对数据进行划分,这样不仅可以加快查询速度,还可以在维护数据时提高效率。例如:
CREATE TABLE table_name ( id SERIAL PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now(), data TEXT NOT NULL ) PARTITION BY RANGE(created_at); --创建分区表 CREATE TABLE table_name_2019_01 PARTITION OF table_name FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'); --创建索引 CREATE INDEX ON table_name (created_at);
十、pgsql索引含null选取
当我们在查询含有null值的列时,通常会使用“is null”或“is not null”来进行筛选,而一般情况下,数据库并不会将null值存储在B-Tree索引中。如果将含有null值的列添加到B-Tree索引中,大量的null值会大大影响查询效率。因此,我们在添加索引时,应该确定索引不含有null值。例如:
CREATE INDEX idx_name ON table_name (column_name) WHERE column_name IS NOT NULL;