MySQL（七）：前缀索引及PostgreSQL中类似功能实现

大家好，我是欧阳方超，公众号同名。

1 概述

在数据库中创建索引时，可以在列名后面加上括号，指定要索引的字符长度，这在MySQL中尤为常见。

CREATE INDEX index_name ON table_name (column_name(length));

这对于 VARCHAR、TEXT 和 BLOB 类型的列尤其重要，因为这些列可能会占用较大的存储空间（索引文件较大），column_name[length]中的length用于指定索引的前缀长度。

2 前缀索引的概念

2.1 什么是前缀索引？

前缀索引是指在字符串列上只索引每个值的前几个字符，而不是整个字符串。这样可以减少索引的大小，提高查询性能，特别是在处理大文本字段时。例如，对于一个varchar类型的列，假设列名为username，如果创建索引时适应username(10)，这意味着只对username列的前10个字符创建索引。

2.2 使用前缀索引的原因

节省空间：索引占用的磁盘空间更小，因为只存储部分字符。
提高性能：在某些情况下，前缀索引可以加快查询速度，尤其是在索引缓存有限的情况下。
适用场景：适用于长字符串字段，如URL、电子邮件地址等。

2.3 不指定前缀长度的情况

默认行为
如果不指定length，数据库将对整个列创建索引。
对于较短的字符串列，这通常是合适的，因为整个列的索引不会显著增加索引大小。
影响
空间占用：索引可能会占用更多的磁盘空间。
性能：在某些情况下，特别是长字符串上，可能会导致索引性能下降。

2.4 选择前缀长度的策略

分析数据分布：选择足够长的前缀，以确保索引的选择性（即索引能有效区分不同的行）。
考虑查询模式：如果查询只涉及字符串的前几个字符，前缀索引可能非常有效。

3 PostgreSQL“前缀索引”

PostgreSQL中没有像MySQL那样直接的“前缀索引”概念，但可以通过变通的方法实现类似的功能，

3.1 使用表达式索引

PostgreSQL支持表达式索引，可能在字符串的前缀上创建索引。例如，可以创建一个索引来仅索引字符串的前几个字符：

CREATE INDEX ON table_name (substring(column_name FROM 1 FOR n));

这样，查询时需要使用相同的表达式来利用这个索引：

SELECT * FROM table_name WHERE substring(column_name FROM 1 FOR n) = 'prefix';

PostgreSQL 本身并没有一个明确的前缀索引类型，但是你可以通过以下几种方法来实现类似的功能，使用 LEFT 或 SUBSTRING 函数在索引中只包含字段的前缀部分。这种方式可以帮助优化字符串的前缀查询，尤其是当只关心字段的前几个字符时。

4 总结

阐述了 MySQL 中创建索引时指定列字符长度（length）的语法及作用，如节省空间、提高性能等，还提及不指定 length 的情况。同时，指出 PostgreSQL 虽无直接 “前缀索引” 概念，但可通过表达式索引等变通方法，利用函数对字符串前缀创建索引来实现类似功能。
我是欧阳方超，把事情做好了自然就有兴趣了，如果你喜欢我的文章，欢迎点赞、转发、评论加关注。我们下次见。