PostgreSQL 聊一下索引和排序规则

最新推荐文章于 2026-06-17 11:25:20 发布

原创最新推荐文章于 2026-06-17 11:25:20 发布 · 331 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#postgresql

PostgreSQL数据库专栏收录该内容

31 篇文章

订阅专栏

索引和排序规则

1. 先用一句话说明

索引决定数据库能不能快速找到数据；排序规则决定数据库怎样比较和排序字符串。

在实际开发中，很多查询慢、排序结果奇怪、明明建了索引却没有用上，都可能和这两个概念有关。

2. 什么是索引？

可以把数据库表想象成一本很厚的书。

如果没有目录，你要找某个章节，只能从第一页开始一页一页翻。
如果有目录，你可以先看目录，直接跳到对应页码。

数据库里的索引就类似书的目录。

例如有一张用户表：

CREATE TABLE user_info (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20),
    age INT
);

如果经常通过手机号查询用户：

SELECT * FROM user_info WHERE phone = '13800000000';

如果 phone 没有索引，数据库可能要从第一行扫到最后一行。
如果给 phone 建了索引：

CREATE INDEX idx_user_phone ON user_info(phone);

数据库就可以先去索引里查手机号，再定位到对应的数据行。

3. 为什么索引能加速查询？

索引通常不是简单地把字段复制一份，而是按照某种数据结构组织起来。

在 MySQL InnoDB 中，常见索引结构是 B+Tree。

你不需要记住 B+Tree 的所有细节，只要理解三点：

索引是有序的
比如手机号索引会按照手机号从小到大排列。
查找时可以快速缩小范围
类似查字典，不需要从第一页开始找。
适合等值查询和范围查询
例如：

SELECT * FROM user_info WHERE phone = '13800000000';

SELECT * FROM user_info WHERE age BETWEEN 20 AND 30;

如果 phone 或 age 上有合适索引，查询通常会更快。

4. 常见索引类型

4.1 主键索引

主键索引用于唯一标识一行数据。

id BIGINT PRIMARY KEY

特点：

不能重复
不能为 NULL
一张表只能有一个主键
InnoDB 中，数据通常按照主键索引组织存储

4.2 唯一索引

唯一索引用来保证某个字段不能重复。

CREATE UNIQUE INDEX uk_user_phone ON user_info(phone);

适合：手机号、邮箱、身份证号、业务唯一编码等。

4.3 普通索引

普通索引只负责加速查询，不保证唯一性。

CREATE INDEX idx_user_age ON user_info(age);

4.4 联合索引

联合索引是多个字段组成的索引。

CREATE INDEX idx_user_name_age ON user_info(name, age);

它适合这样的查询：

SELECT * FROM user_info WHERE name = 'Tom' AND age = 20;

也可能适合：

SELECT * FROM user_info WHERE name = 'Tom';

但通常不适合只按第二个字段查询：

SELECT * FROM user_info WHERE age = 20;

这是因为联合索引遵循常说的最左前缀原则。

可以把 (name, age) 理解成一本先按 name 排序，再在相同 name 内按 age 排序的目录。
如果你不提供 name，只提供 age，数据库很难直接利用这个目录快速定位。

4.5 前缀索引

对于很长的字符串字段，可以只索引前面一部分字符。

CREATE INDEX idx_user_name_prefix ON user_info(name(10));

优点：索引更小。
缺点：区分度可能变差。

例如很多人的名字前 10 个字符都一样，那么这个索引效果就不好。

4.6 全文索引

全文索引用于关键词搜索。

CREATE FULLTEXT INDEX idx_article_content ON article(content);

适合文章内容、商品描述等文本搜索场景。
但如果是复杂搜索业务，通常会考虑 Elasticsearch、OpenSearch 等专门的搜索引擎。

5. 索引不是越多越好

索引可以加速查询，但也有成本。

5.1 占用磁盘空间

每个索引都需要额外存储。

5.2 影响写入速度

当执行新增、修改、删除时，数据库不仅要改表数据，还要维护索引。

例如：

UPDATE user_info SET phone = '13900000000' WHERE id = 1;

如果 phone 有索引，数据库还要更新 phone 对应的索引结构。

5.3 索引太多会增加优化器选择成本

数据库执行 SQL 前，会选择一个执行计划。
索引太多时，优化器也要判断用哪个索引更合适。

所以建索引要有目的：

经常作为查询条件的字段
经常用于排序的字段
经常用于分组的字段
区分度高的字段

6. 什么是排序规则？

排序规则通常叫 Collation。

它决定字符串之间如何比较和排序。

例如下面几个字符串：

'a'
'A'
'á'
'b'

不同排序规则下，数据库可能认为：

'a' 和 'A' 相等
'a' 和 'A' 不相等
'á' 和 'a' 接近
'á' 应该排在某个特殊位置

所以排序规则会影响：

= 比较
LIKE 匹配
ORDER BY 排序
GROUP BY 分组
唯一索引判断是否重复
字符串索引是否能正常使用

7. 字符集和排序规则的关系

很多人会把字符集和排序规则混在一起。

它们不是一回事。

7.1 字符集

字符集决定数据库能存哪些字符。

例如 MySQL 中常见字符集：

utf8mb4

utf8mb4 可以存中文、英文、emoji 等字符。

7.2 排序规则

排序规则决定这些字符如何比较和排序。

例如 MySQL 中常见排序规则：

utf8mb4_general_ci
utf8mb4_unicode_ci
utf8mb4_bin
utf8mb4_0900_ai_ci

其中常见后缀含义：

后缀	含义	示例
`ci`	case insensitive，大小写不敏感	`a` 和 `A` 可能被认为相等
`cs`	case sensitive，大小写敏感	`a` 和 `A` 不相等
`ai`	accent insensitive，重音不敏感	`a` 和 `á` 可能被认为相等
`as`	accent sensitive，重音敏感	`a` 和 `á` 不相等
`bin`	按二进制值比较	比较最严格

8. 排序规则如何影响查询结果？

假设有一张表：

CREATE TABLE account (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

插入数据：

INSERT INTO account(id, username) VALUES
(1, 'tom'),
(2, 'Tom'),
(3, 'TOM');

如果排序规则是大小写不敏感的 utf8mb4_general_ci，下面这个查询可能会查出三行：

SELECT * FROM account WHERE username = 'tom';

因为在这种排序规则下，tom、Tom、TOM 可能被认为是相等的。

如果使用 utf8mb4_bin 这类大小写敏感的规则，可能只查出 tom 这一行。

9. 排序规则如何影响唯一索引？

这是一个非常常见的坑。

假设 username 上有唯一索引：

CREATE UNIQUE INDEX uk_account_username ON account(username);

如果表使用的是大小写不敏感排序规则，那么：

INSERT INTO account(id, username) VALUES (1, 'tom');
INSERT INTO account(id, username) VALUES (2, 'Tom');

第二条可能会失败，因为数据库认为 tom 和 Tom 是同一个值。

这对用户名、邮箱等字段非常重要。

如果业务要求：

Tom 和 tom 算同一个用户名：可以使用大小写不敏感排序规则
Tom 和 tom 必须算不同用户名：需要使用大小写敏感或二进制排序规则

10. 排序规则如何影响 ORDER BY？

看下面的排序：

SELECT username FROM account ORDER BY username;

不同排序规则下，排序结果可能不同。

例如大小写不敏感排序规则可能更接近自然语言排序；
二进制排序规则会更接近字符编码值排序。

所以如果你发现本地、测试、生产环境排序结果不一致，要检查：

数据库默认字符集
数据库默认排序规则
表的排序规则
字段的排序规则
SQL 中是否显式指定了 COLLATE

11. 排序规则如何影响索引命中？

这是索引和排序规则之间最容易被忽略的关系。

11.1 字段排序规则和查询排序规则不一致

假设字段 username 的排序规则是：

utf8mb4_general_ci

但查询时强行指定另一个排序规则：

SELECT *
FROM account
WHERE username COLLATE utf8mb4_bin = 'tom';

这种写法可能导致数据库不能很好地使用原来的索引，因为比较规则变了。

简单理解：

索引是按照字段原本的排序规则建好的目录。
如果查询时临时换了一套比较规则，原目录可能就不完全适用了。

11.2 对索引字段使用函数

例如：

SELECT * FROM account WHERE LOWER(username) = 'tom';

即使 username 上有索引，这种写法也可能导致索引失效。

原因是索引存的是原始 username 的值，不是 LOWER(username) 计算后的值。

更推荐的做法是：

选择合适的排序规则，让比较天然支持大小写不敏感；或
增加一个规范化字段，例如 username_lower；或
使用数据库支持的函数索引。

11.3 隐式字符集或排序规则转换

如果两个字段的字符集或排序规则不同，比较时数据库可能要做隐式转换。

例如：

SELECT *
FROM table_a a
JOIN table_b b ON a.name = b.name;

如果 a.name 和 b.name 的排序规则不同，数据库可能需要先转换其中一个字段再比较。
这不仅可能影响性能，还可能让索引无法充分使用。

12. 如何判断索引有没有用上？

在 MySQL 中，可以使用 EXPLAIN 查看执行计划。

EXPLAIN SELECT * FROM user_info WHERE phone = '13800000000';

重点关注几个字段：

字段	含义	简单理解
`type`	访问类型	越接近 `const`、`ref`、`range` 通常越好
`key`	实际使用的索引	如果是 `NULL`，通常表示没用上索引
`rows`	预估扫描行数	越少越好
`Extra`	附加信息	关注 `Using filesort`、`Using temporary` 等

例如：

key: idx_user_phone
rows: 1

通常说明查询比较理想。

如果看到：

key: NULL
rows: 1000000

就要警惕是否发生了全表扫描。

13. 常见导致索引失效的写法

13.1 在索引字段上使用函数

-- 不推荐
SELECT * FROM user_info WHERE DATE(create_time) = '2026-06-16';

更推荐：

SELECT *
FROM user_info
WHERE create_time >= '2026-06-16 00:00:00'
  AND create_time <  '2026-06-17 00:00:00';

13.2 对字段做计算

-- 不推荐
SELECT * FROM product WHERE price * 100 > 10000;

更推荐：

SELECT * FROM product WHERE price > 100;

13.3 联合索引没有从最左列开始使用

有索引：

CREATE INDEX idx_order_user_status ON orders(user_id, status);

13.4 LIKE 以通配符开头

-- 不推荐
SELECT * FROM user_info WHERE name LIKE '%Tom';

更容易使用索引：

SELECT * FROM user_info WHERE name LIKE 'Tom%';

13.5 字段类型不一致

如果 phone 是字符串类型：

phone VARCHAR(20)

不推荐：

SELECT * FROM user_info WHERE phone = 13800000000;

更推荐：

SELECT * FROM user_info WHERE phone = '13800000000';

类型不一致可能导致隐式转换，影响索引使用。

14. 实践建议

14.1 建索引前先看查询场景

不要看到字段就建索引。
应该先看：

哪些 SQL 最常执行
哪些 SQL 最慢
查询条件是什么
是否经常排序或分组
数据量有多大

14.2 优先给高区分度字段建索引

区分度高，意思是字段值重复少。

例如：

身份证号：区分度高
手机号：区分度高
性别：区分度低
状态：通常区分度低

像 gender、status 这种字段，单独建索引不一定有明显效果。
但它们可以作为联合索引的一部分，例如：

CREATE INDEX idx_order_user_status_time
ON orders(user_id, status, create_time);

14.3 联合索引要考虑字段顺序

一般可以参考：

等值查询字段放前面
区分度高的字段放前面
排序字段尽量和索引顺序一致
范围查询字段后面的索引列可能无法充分利用

例如：

WHERE user_id = ? AND status = ? AND create_time BETWEEN ? AND ?
ORDER BY create_time DESC

可以考虑：

CREATE INDEX idx_order_user_status_time
ON orders(user_id, status, create_time);

14.4 字符串字段要提前统一字符集和排序规则

建议在建库、建表阶段就统一规则。

例如 MySQL 中常见选择：

CREATE DATABASE demo
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

如果使用 MySQL 5.7，可能没有 utf8mb4_0900_ai_ci，可以根据版本选择合适规则，例如：

utf8mb4_unicode_ci

重点是：

不同环境保持一致
表之间保持一致
关联字段保持一致
业务上明确是否大小写敏感

14.5 用户名、邮箱等字段要特别注意大小写

例如邮箱：

Test@example.com
test@example.com

业务上通常会认为它们是同一个邮箱。
这时可以：

存储前统一转小写
使用大小写不敏感排序规则
在规范化字段上建唯一索引

例如：

email_original VARCHAR(255),
email_normalized VARCHAR(255),
UNIQUE KEY uk_email_normalized(email_normalized)

这样既能保留用户输入的原始形式，又能避免重复注册。

14.6 不要随意在 SQL 中临时指定 COLLATE

例如：

SELECT * FROM account
WHERE username COLLATE utf8mb4_bin = 'Tom';

这种写法不是不能用，但要谨慎。
因为它可能改变比较规则，进而影响索引使用和查询结果。

如果某个字段长期需要大小写敏感比较，更推荐从字段设计上解决。

15. 一个简单的排查流程

当你遇到“查询慢”时，可以按下面顺序排查：

用 EXPLAIN 看是否使用索引
看 WHERE 条件是否命中索引字段
看是否对索引字段使用了函数或计算
看字段类型是否和查询参数类型一致
看联合索引是否符合最左前缀原则
看是否有隐式字符集或排序规则转换
看排序字段是否可以利用索引
看数据量和字段区分度是否适合当前索引

当你遇到“字符串比较或排序结果不符合预期”时，可以排查：

数据库默认字符集和排序规则
表级字符集和排序规则
字段级字符集和排序规则
SQL 中是否显式使用了 COLLATE
应用程序是否对字符串做了大小写转换
测试环境和生产环境配置是否一致

16. 总结

索引和排序规则看起来是两个概念，但它们在字符串查询中关系很密切。

可以这样记：

索引像目录，帮助数据库快速定位数据
排序规则像比较规则，决定字符串怎样判断相等、大小和顺序
字符串索引是按照某种排序规则建立的
查询时如果改变比较规则，可能影响索引使用
唯一索引在字符串字段上是否认为两个值重复，也受排序规则影响

实际项目中，建议做到：

建库建表时统一 utf8mb4 字符集
根据业务选择合适排序规则
用户名、邮箱、编码类字段提前明确是否大小写敏感
慢查询用 EXPLAIN 分析，不凭感觉判断
避免在索引字段上使用函数、计算和不必要的 COLLATE
联合索引设计要结合真实 SQL，而不是只看字段名

一句话收尾：

索引用得好，查询快；排序规则选得对，结果准。两者一起设计，数据库才能既快又稳定。