Jeskson-微服务分布式

索引原理与应用

# 索引原理与应用

# 概述

索引是数据库中用于快速定位数据的数据结构，类似于书籍的目录。合理使用索引可以大幅提升查询性能，但也会增加存储空间和维护成本。

# 索引的基本原理

# 1. 索引结构

# B+树索引（最常用）

        [10, 20, 30]
       /    |    |    \
   [1,5,8] [12,15] [22,25] [35,40]
     |       |       |       |
   数据页   数据页   数据页   数据页

特点：

  • 叶子节点存储数据或数据指针
  • 非叶子节点只存储键值
  • 叶子节点之间有链表连接
  • 查询时间复杂度：O(log n)

# 哈希索引

-- 适用于等值查询
SELECT * FROM users WHERE id = 123;

-- 不适用于范围查询
SELECT * FROM users WHERE id > 100; -- 无法使用哈希索引

# 2. 索引类型

# 聚簇索引（Clustered Index）

  • 数据行按索引键值物理排序
  • 每个表只能有一个聚簇索引
  • 通常是主键索引
-- MySQL InnoDB中，主键就是聚簇索引
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100)
);

# 非聚簇索引（Non-Clustered Index）

  • 索引结构与数据存储分离
  • 索引叶子节点存储指向数据行的指针
  • 一个表可以有多个非聚簇索引
-- 创建非聚簇索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_name ON users(name);

# 索引的创建与管理

# 1. 创建索引

# 单列索引

-- 基本语法
CREATE INDEX index_name ON table_name(column_name);

-- 示例
CREATE INDEX idx_user_age ON users(age);

# 复合索引

-- 多列索引
CREATE INDEX idx_user_name_age ON users(name, age);

-- 索引列的顺序很重要
-- 可以使用：name, name+age
-- 不能单独使用：age

# 唯一索引

-- 确保列值唯一
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 复合唯一索引
CREATE UNIQUE INDEX idx_user_name_phone ON users(name, phone);

# 部分索引

-- PostgreSQL支持条件索引
CREATE INDEX idx_active_users ON users(name) WHERE status = 'active';

-- MySQL支持前缀索引
CREATE INDEX idx_user_email_prefix ON users(email(10));

# 2. 删除索引

-- 删除索引
DROP INDEX index_name;

-- MySQL语法
DROP INDEX index_name ON table_name;

-- 查看表的索引
SHOW INDEX FROM table_name;

# 索引使用策略

# 1. 何时创建索引

# 适合创建索引的情况

  • 经常出现在WHERE子句中的列
  • 经常用于JOIN的列
  • 经常用于ORDER BY的列
  • 经常用于GROUP BY的列
-- 经常查询的列
SELECT * FROM orders WHERE customer_id = 123;
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 经常排序的列
SELECT * FROM products ORDER BY price;
CREATE INDEX idx_products_price ON products(price);

-- 经常分组的列
SELECT category, COUNT(*) FROM products GROUP BY category;
CREATE INDEX idx_products_category ON products(category);

# 不适合创建索引的情况

  • 很少查询的列
  • 频繁更新的列
  • 数据量很小的表
  • 列的基数很低（重复值很多）

# 2. 复合索引的使用

# 最左前缀原则

-- 创建复合索引
CREATE INDEX idx_user_name_age_city ON users(name, age, city);

-- 可以使用索引的查询
SELECT * FROM users WHERE name = 'John';                    -- ✓
SELECT * FROM users WHERE name = 'John' AND age = 25;       -- ✓
SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'NYC'; -- ✓

-- 无法使用索引的查询
SELECT * FROM users WHERE age = 25;                         -- ✗
SELECT * FROM users WHERE city = 'NYC';                     -- ✗
SELECT * FROM users WHERE age = 25 AND city = 'NYC';        -- ✗

# 索引列顺序优化

-- 将选择性高的列放在前面
-- 假设：name有1000个不同值，age有50个不同值，city有10个不同值
CREATE INDEX idx_optimal ON users(name, age, city);  -- 推荐
CREATE INDEX idx_poor ON users(city, age, name);     -- 不推荐

# 索引性能分析

# 1. 执行计划分析

# MySQL

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 详细执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John';

-- 分析索引使用情况
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 25;

# PostgreSQL

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 详细执行计划（包含实际执行时间）
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'John';

-- 查看索引使用统计
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# 2. 索引效果监控

-- MySQL：查看索引使用情况
SHOW STATUS LIKE 'Handler_read%';

-- 查看未使用的索引
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s ON t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
AND s.INDEX_NAME NOT IN (
    SELECT DISTINCT INDEX_NAME 
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE OBJECT_SCHEMA = 'your_database'
);

# 实战案例

# 案例1：电商订单查询优化

-- 原始查询（慢）
SELECT * FROM orders 
WHERE customer_id = 123 
AND order_date >= '2024-01-01' 
AND status = 'completed'
ORDER BY order_date DESC;

-- 创建优化索引
CREATE INDEX idx_orders_customer_date_status 
ON orders(customer_id, order_date, status);

-- 或者根据查询频率调整顺序
CREATE INDEX idx_orders_customer_status_date 
ON orders(customer_id, status, order_date);

# 案例2：用户搜索功能优化

-- 用户名模糊搜索
SELECT * FROM users WHERE name LIKE 'John%';

-- 创建前缀索引（MySQL）
CREATE INDEX idx_user_name_prefix ON users(name(10));

-- 全文搜索索引
CREATE FULLTEXT INDEX idx_user_name_fulltext ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('John' IN NATURAL LANGUAGE MODE);

# 案例3：分页查询优化

-- 传统分页（大偏移量时很慢）
SELECT * FROM products ORDER BY id LIMIT 10000, 20;

-- 优化方案：使用索引覆盖
SELECT p.* FROM products p
INNER JOIN (
    SELECT id FROM products ORDER BY id LIMIT 10000, 20
) tmp ON p.id = tmp.id;

-- 或使用游标分页
SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 20;

# 索引维护

# 1. 索引碎片整理

-- MySQL：重建索引
ALTER TABLE table_name ENGINE=InnoDB;

-- 或者
OPTIMIZE TABLE table_name;

-- PostgreSQL：重建索引
REINDEX INDEX index_name;
REINDEX TABLE table_name;

# 2. 索引统计信息更新

-- MySQL：更新统计信息
ANALYZE TABLE table_name;

-- PostgreSQL：更新统计信息
ANALYZE table_name;

-- SQL Server：更新统计信息
UPDATE STATISTICS table_name;

# 常见问题与解决方案

# 1. 索引失效问题

-- 函数操作导致索引失效
SELECT * FROM users WHERE UPPER(name) = 'JOHN';  -- ✗ 索引失效
SELECT * FROM users WHERE name = 'John';         -- ✓ 使用索引

-- 类型转换导致索引失效
SELECT * FROM users WHERE id = '123';  -- ✗ 可能导致索引失效
SELECT * FROM users WHERE id = 123;    -- ✓ 使用索引

-- 前导通配符导致索引失效
SELECT * FROM users WHERE name LIKE '%John';   -- ✗ 索引失效
SELECT * FROM users WHERE name LIKE 'John%';   -- ✓ 使用索引

# 2. 索引选择性问题

-- 检查列的选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) as selectivity
FROM table_name;

-- 选择性太低的列不适合单独建索引
-- 可以考虑复合索引
CREATE INDEX idx_status_date ON orders(status, order_date);

# 最佳实践

# 1. 索引设计原则

  • 为经常查询的列创建索引
  • 复合索引遵循最左前缀原则
  • 避免过多的索引
  • 定期监控索引使用情况

# 2. 性能优化建议

  • 使用覆盖索引减少回表操作
  • 合理设置索引长度
  • 避免在小表上创建索引
  • 定期维护索引统计信息

# 3. 监控与维护

  • 定期检查慢查询日志
  • 监控索引使用率
  • 及时删除无用索引
  • 定期进行索引碎片整理

# 总结

索引是数据库性能优化的重要手段，但需要在查询性能和维护成本之间找到平衡。通过合理的索引设计、定期的性能监控和及时的维护，可以显著提升数据库的查询性能。

在实际应用中，建议根据具体的业务场景和查询模式来设计索引策略，并通过持续的监控和优化来确保索引的有效性。

查询优化