印刻数据库

外观

PostgreSQL 索引失效场景 ​

索引失效概述 ​

索引失效是指PostgreSQL查询优化器在执行查询时，选择不使用已创建的索引，而是选择全表扫描或其他低效的执行计划。索引失效会导致查询性能急剧下降，影响系统的整体性能。了解索引失效的常见场景和原因，对于优化查询性能至关重要。

索引失效的常见场景 ​

1. 索引列上使用函数 ​

当在WHERE子句中对索引列使用函数时，查询优化器无法使用该索引，因为索引存储的是原始列值，而不是函数计算后的值。

示例 ​

sql
-- 索引失效：在索引列上使用LOWER函数
SELECT * FROM users WHERE LOWER(email) = 'john.doe@example.com';

-- 索引有效：不使用函数，直接比较
SELECT * FROM users WHERE email = 'John.Doe@example.com';

解决方案 ​

  • 使用表达式索引：创建基于函数结果的表达式索引
  • 避免在索引列上使用函数：将函数应用到查询值上，而不是索引列上
sql
-- 解决方案1：创建表达式索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 解决方案2：将函数应用到查询值上
SELECT * FROM users WHERE email = LOWER('John.Doe@example.com');

2. 索引列上使用运算符 ​

当在WHERE子句中对索引列使用某些运算符时，查询优化器可能无法使用该索引。

示例 ​

sql
-- 索引失效：使用不等运算符!=或<>
SELECT * FROM users WHERE status != 'active';

-- 索引失效：使用LIKE通配符开头的模糊查询
SELECT * FROM users WHERE email LIKE '%example.com';

-- 索引失效：使用NOT IN
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

解决方案 ​

  • 避免使用不等运算符：如果可能，使用等值查询或范围查询替代
  • 优化LIKE查询：将通配符放在末尾，或使用全文搜索
  • 使用NOT EXISTS替代NOT IN：NOT EXISTS的性能通常优于NOT IN
sql
-- 解决方案1：优化LIKE查询，将通配符放在末尾
SELECT * FROM users WHERE email LIKE 'john%';

-- 解决方案2：使用NOT EXISTS替代NOT IN
SELECT * FROM users u 
WHERE NOT EXISTS (SELECT 1 FROM blocked_users b WHERE b.user_id = u.id);

3. 索引列参与计算 ​

当索引列参与数学计算或字符串连接等操作时，查询优化器无法使用该索引。

示例 ​

sql
-- 索引失效：索引列参与数学计算
SELECT * FROM products WHERE price * 0.9 < 100;

-- 索引失效：索引列参与字符串连接
SELECT * FROM users WHERE first_name || ' ' || last_name = 'John Doe';

解决方案 ​

  • 避免在索引列上进行计算：将计算移到查询值上
  • 创建表达式索引：如果无法避免计算，创建基于计算结果的表达式索引
sql
-- 解决方案1：将计算移到查询值上
SELECT * FROM products WHERE price < 100 / 0.9;

-- 解决方案2：创建表达式索引
CREATE INDEX idx_products_discounted_price ON products(price * 0.9);

4. 数据类型不匹配 ​

当查询条件中的数据类型与索引列的数据类型不匹配时，PostgreSQL会进行隐式类型转换，导致索引失效。

示例 ​

sql
-- 索引失效：数据类型不匹配（索引列是整数，查询值是字符串）
SELECT * FROM users WHERE id = '123';

-- 索引失效：数据类型不匹配（索引列是日期，查询值是字符串）
SELECT * FROM orders WHERE created_at = '2023-01-01';

解决方案 ​

  • 确保数据类型匹配：使用与索引列相同的数据类型
  • 显式类型转换：如果无法避免类型转换，使用显式类型转换
sql
-- 解决方案1：使用正确的数据类型
SELECT * FROM users WHERE id = 123;

-- 解决方案2：显式类型转换
SELECT * FROM orders WHERE created_at = CAST('2023-01-01' AS DATE);

5. 复合索引的最左前缀原则 ​

复合索引遵循最左前缀原则，只有当查询条件包含索引的最左前缀列时，才能使用该索引。

示例 ​

sql
-- 创建复合索引
CREATE INDEX idx_orders_customer_id_created_at ON orders(customer_id, created_at);

-- 索引有效：包含最左前缀列customer_id
SELECT * FROM orders WHERE customer_id = 1;

-- 索引有效：包含最左前缀列customer_id和第二列created_at
SELECT * FROM orders WHERE customer_id = 1 AND created_at > '2023-01-01';

-- 索引失效：不包含最左前缀列customer_id
SELECT * FROM orders WHERE created_at > '2023-01-01';

解决方案 ​

  • 遵循最左前缀原则：确保查询条件包含复合索引的最左前缀列
  • 调整复合索引的列顺序：将常用的查询列放在复合索引的前面
  • 创建单独的索引：对于不包含最左前缀列的查询，创建单独的索引
sql
-- 解决方案1：遵循最左前缀原则
SELECT * FROM orders WHERE customer_id = 1 AND created_at > '2023-01-01';

-- 解决方案2：创建单独的索引
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. 范围查询后面的列无法使用索引 ​

在复合索引中，如果前面的列使用了范围查询，那么后面的列无法使用索引。

示例 ​

sql
-- 创建复合索引
CREATE INDEX idx_orders_customer_id_created_at_status ON orders(customer_id, created_at, status);

-- 索引有效：customer_id是等值查询，created_at是范围查询，status无法使用索引
SELECT * FROM orders WHERE customer_id = 1 AND created_at > '2023-01-01' AND status = 'active';

-- 实际使用的索引部分：customer_id, created_at
-- status列无法使用索引，因为前面的created_at使用了范围查询

解决方案 ​

  • 将范围查询列放在复合索引的末尾：确保范围查询列后面没有需要使用索引的列
  • 调整查询条件：如果可能，将范围查询转换为等值查询
  • 创建合适的复合索引：根据查询模式创建合适的复合索引
sql
-- 解决方案1：将范围查询列放在末尾
CREATE INDEX idx_orders_customer_id_status_created_at ON orders(customer_id, status, created_at);

-- 解决方案2：调整查询条件，使用等值查询
SELECT * FROM orders WHERE customer_id = 1 AND status = 'active' AND created_at > '2023-01-01';

7. 统计信息不准确 ​

PostgreSQL的查询优化器依赖于准确的统计信息来生成最优的查询计划。如果统计信息不准确，查询优化器可能会选择不使用索引。

示例 ​

sql
-- 索引存在但不被使用，因为统计信息不准确
SELECT * FROM users WHERE email = 'john.doe@example.com';

解决方案 ​

  • 更新统计信息：手动执行ANALYZE操作，更新表的统计信息
  • 调整统计信息收集参数：增加统计信息收集的样本量
  • 使用ALTER TABLE设置统计信息参数：为特定表或列设置合适的统计信息参数
sql
-- 解决方案1：更新统计信息
ANALYZE users;

-- 解决方案2：增加统计信息收集的样本量
ALTER TABLE users ALTER COLUMN email SET STATISTICS 1000;

-- 解决方案3：调整autovacuum参数，提高统计信息更新频率
ALTER TABLE users SET (autovacuum_analyze_scale_factor = 0.05);

8. 索引选择性低 ​

索引的选择性是指不重复的索引值占总记录数的比例。如果索引的选择性很低，查询优化器可能会选择不使用该索引，因为全表扫描的成本可能更低。

示例 ​

sql
-- 索引选择性低：gender列只有几个不同的值
CREATE INDEX idx_users_gender ON users(gender);

-- 索引失效：选择性低，查询优化器选择全表扫描
SELECT * FROM users WHERE gender = 'male';

解决方案 ​

  • 避免为低选择性列创建单独的索引：低选择性列不适合单独创建索引
  • 创建复合索引：将低选择性列与高选择性列组合，创建复合索引
  • 使用部分索引：如果只需要查询低选择性列的特定值，使用部分索引
sql
-- 解决方案1：创建复合索引
CREATE INDEX idx_users_gender_created_at ON users(gender, created_at);

-- 解决方案2：使用部分索引
CREATE INDEX idx_users_gender_active ON users(gender) WHERE status = 'active';

9. 查询返回大部分数据 ​

当查询需要返回表中大部分数据时（通常超过20-30%），查询优化器可能会选择全表扫描，因为全表扫描的成本可能比索引扫描更低。

示例 ​

sql
-- 索引存在但不被使用，因为查询返回大部分数据
SELECT * FROM users WHERE created_at > '2020-01-01';

解决方案 ​

  • 优化查询：减少返回的数据量，使用LIMIT、WHERE等条件过滤
  • 使用分区表：将大表分割为多个小表，减少单表数据量
  • 使用BRIN索引：对于超大表的范围查询，BRIN索引可能比B-tree索引更有效
sql
-- 解决方案1：减少返回的数据量
SELECT * FROM users WHERE created_at > '2023-01-01' AND status = 'active' LIMIT 100;

-- 解决方案2：使用分区表
CREATE TABLE users_partitioned (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL
)
PARTITION BY RANGE (created_at);

-- 解决方案3：使用BRIN索引
CREATE INDEX idx_users_created_at_brin ON users USING BRIN(created_at);

10. 索引膨胀 ​

索引膨胀是指索引占用的物理空间远大于实际存储数据所需的空间。索引膨胀会导致索引扫描的成本增加，查询优化器可能会选择不使用膨胀的索引。

示例 ​

sql
-- 索引存在但不被使用，因为索引膨胀严重
SELECT * FROM orders WHERE customer_id = 1;

解决方案 ​

  • 重建索引：使用REINDEX命令重建膨胀的索引
  • 使用CONCURRENTLY选项：在生产环境中，使用CONCURRENTLY选项避免阻塞业务
  • 定期监控索引膨胀：使用pgstattuple或pg_bloat_check工具定期检测索引膨胀情况
sql
-- 解决方案1：重建索引
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- 解决方案2：监控索引膨胀
SELECT 
  round((stat_size - real_size) * 100.0 / stat_size, 2) AS bloat_ratio
FROM pgstatindex('idx_orders_customer_id');

11. 隐式类型转换 ​

当查询条件中的数据类型与索引列的数据类型不匹配时，PostgreSQL会进行隐式类型转换，导致索引失效。

示例 ​

sql
-- 索引列id是整数类型
CREATE INDEX idx_users_id ON users(id);

-- 索引失效：查询值是字符串，PostgreSQL进行隐式类型转换
SELECT * FROM users WHERE id = '123';

解决方案 ​

  • 确保数据类型匹配：使用与索引列相同的数据类型
  • 显式类型转换：如果无法避免类型转换，使用显式类型转换
sql
-- 解决方案1：使用正确的数据类型
SELECT * FROM users WHERE id = 123;

-- 解决方案2：显式类型转换
SELECT * FROM users WHERE id = CAST('123' AS INTEGER);

12. NULL值查询 ​

当在WHERE子句中使用IS NULL或IS NOT NULL查询索引列时，查询优化器可能会选择不使用该索引，特别是当索引列包含大量NULL值时。

示例 ​

sql
-- 索引存在但不被使用，因为email列包含大量NULL值
CREATE INDEX idx_users_email ON users(email);

-- 索引失效：查询NULL值
SELECT * FROM users WHERE email IS NULL;

解决方案 ​

  • 避免在索引列中存储大量NULL值：设计表结构时，尽量避免在索引列中存储大量NULL值
  • 使用部分索引：如果只需要查询非NULL值，使用部分索引
  • 创建包含NULL值的索引：对于确实需要查询NULL值的场景，创建包含NULL值的索引
sql
-- 解决方案1：使用部分索引
CREATE INDEX idx_users_email_not_null ON users(email) WHERE email IS NOT NULL;

-- 解决方案2：创建包含NULL值的索引
CREATE INDEX idx_users_email_including_null ON users(email);

索引失效的诊断方法 ​

1. 使用EXPLAIN分析查询计划 ​

EXPLAIN命令可以显示查询的执行计划，帮助我们判断是否使用了索引。

sql
-- 使用EXPLAIN分析查询计划
EXPLAIN SELECT * FROM users WHERE email = 'john.doe@example.com';

-- 使用EXPLAIN ANALYZE显示实际执行情况
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john.doe@example.com';

2. 查看索引使用情况 ​

通过pg_stat_user_indexes视图可以查看索引的使用情况，包括索引扫描次数、全表扫描次数等。

sql
-- 查看索引使用情况
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS index_scans,
  seq_scan AS seq_scans,
  idx_scan * 100.0 / NULLIF((idx_scan + seq_scan), 0) AS index_usage_ratio
FROM pg_stat_user_indexes 
JOIN pg_stat_user_tables USING (schemaname, relname)
ORDER BY index_usage_ratio ASC;

3. 使用pg_hint_plan强制使用索引 ​

pg_hint_plan是一个PostgreSQL扩展，可以强制查询优化器使用指定的索引，帮助我们测试索引的效果。

安装pg_hint_plan扩展 ​

sql
-- 安装pg_hint_plan扩展
CREATE EXTENSION pg_hint_plan;

使用pg_hint_plan强制使用索引 ​

sql
-- 强制使用指定的索引
/*+ IndexScan(users idx_users_email) */
SELECT * FROM users WHERE email = 'john.doe@example.com';

避免索引失效的最佳实践 ​

1. 遵循索引设计原则 ​

  • 只为必要的列创建索引
  • 考虑索引的选择性
  • 合理选择索引类型
  • 合理设计复合索引
  • 避免过度索引

2. 优化查询写法 ​

  • 避免在索引列上使用函数
  • 避免在索引列上使用运算符
  • 避免在索引列上进行计算
  • 确保数据类型匹配
  • 遵循复合索引的最左前缀原则
  • 将范围查询列放在复合索引的末尾

3. 维护索引和统计信息 ​

  • 定期更新统计信息
  • 定期重建膨胀的索引
  • 定期清理未使用的索引
  • 监控索引使用情况

4. 监控和诊断 ​

  • 使用EXPLAIN分析查询计划
  • 查看索引使用情况
  • 使用pg_hint_plan测试索引效果
  • 定期进行性能测试

索引失效案例分析 ​

案例1：电商系统商品查询 ​

问题现象：商品查询接口响应缓慢，查询优化器没有使用已创建的索引。

SQL语句：

sql
SELECT * FROM products WHERE LOWER(name) LIKE '%iphone%' AND price < 1000;

分析：

  1. 在索引列name上使用了LOWER函数，导致索引失效
  2. LIKE查询使用了通配符开头，导致索引失效
  3. 复合索引的顺序不合理

解决方案：

  1. 创建基于LOWER(name)的表达式索引
  2. 使用全文搜索替代LIKE通配符开头的查询
  3. 优化复合索引的列顺序

实施过程：

sql
-- 1. 创建表达式索引
CREATE INDEX idx_products_name_lower ON products(LOWER(name));

-- 2. 使用全文搜索
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN(name gin_trgm_ops);

-- 3. 优化复合索引
CREATE INDEX idx_products_price_name ON products(price, name);

-- 优化后的查询
SELECT * FROM products WHERE LOWER(name) LIKE '%iphone%' AND price < 1000;

案例2：社交媒体用户查询 ​

问题现象：用户查询接口响应缓慢，查询优化器没有使用已创建的复合索引。

SQL语句：

sql
SELECT * FROM users WHERE created_at > '2023-01-01' AND status = 'active' AND gender = 'male';

索引：

sql
CREATE INDEX idx_users_created_at_status_gender ON users(created_at, status, gender);

分析：

  1. created_at列使用了范围查询，导致后面的status和gender列无法使用索引
  2. 复合索引的列顺序不合理，范围查询列应该放在末尾

解决方案：

  1. 调整复合索引的列顺序，将范围查询列放在末尾
  2. 重新创建复合索引

实施过程：

sql
-- 1. 删除原索引
DROP INDEX idx_users_created_at_status_gender;

-- 2. 创建新的复合索引，将范围查询列放在末尾
CREATE INDEX idx_users_status_gender_created_at ON users(status, gender, created_at);

-- 3. 优化后的查询
SELECT * FROM users WHERE status = 'active' AND gender = 'male' AND created_at > '2023-01-01';

总结 ​

索引失效是PostgreSQL查询性能优化中的常见问题，了解索引失效的常见场景和原因，对于优化查询性能至关重要。通过遵循索引设计原则、优化查询写法、维护索引和统计信息、定期监控和诊断，可以有效避免索引失效问题，提高查询性能。

在实际生产环境中，DBA和开发人员应该密切合作，共同优化查询性能。开发人员应该了解索引失效的常见场景，编写高效的SQL查询；DBA应该定期监控索引使用情况，维护索引和统计信息，确保索引的高效运行。通过共同努力，可以确保PostgreSQL数据库的查询性能始终保持在最佳状态。