索引原理与设计

简介

索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。理解索引的底层原理（如 B+ 树结构）、掌握聚簇索引与非聚簇索引的区别、学会设计高效的复合索引和覆盖索引，是每个开发人员和 DBA 必备的核心技能。

特点

• 1.B+ 树原理 — 理解索引的底层存储结构
• 2.聚簇与非聚簇 — 区分两种核心索引类型的适用场景
• 3.复合索引设计 — 掌握联合索引的创建和使用策略
• 4.索引设计原则 — 遵循索引设计的最佳实践

B+ 树索引原理

B+ 树结构

-- B+ 树是数据库索引最常用的数据结构
-- 特点：
--   1. 非叶子节点只存储键值，不存储数据（相比 B 树能存更多键值）
--   2. 叶子节点存储所有键值和数据（或指向数据的指针）
--   3. 叶子节点通过双向链表连接，支持高效范围查询
--   4. 树的高度通常为 3-4 层，即可表示千万级数据

-- B+ 树高度与容量的关系（以 InnoDB 为例）
-- | 阶数     | 单页键数 | 2层容量    | 3层容量      | 4层容量         |
-- |---------|---------|-----------|-------------|----------------|
-- | 非叶子节点 | ~1170   | ~1170     | ~136万       | ~16亿           |
-- | 叶子节点  | ~16条记录 | ~16条      | ~1.8万条     | ~2184万条       |

-- 说明：假设非叶子节点每个页约存 1170 个键（16KB 页 / 约 14B 每键）
--       叶子节点每页约存 16 条记录（假设每条记录约 1KB）

-- 创建 B+ 树索引
CREATE INDEX idx_user_email ON users(email);

-- 查看索引信息
-- MySQL
SHOW INDEX FROM users;

-- PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- SQL Server
EXEC sp_helpindex 'users';

B+ 树查找过程

-- 假设索引列为 id，查找 id = 500 的记录
-- 查找过程（假设 3 层 B+ 树）：
--
-- 第 1 次磁盘 I/O：读取根节点，确定 500 在某个子节点范围内
-- 第 2 次磁盘 I/O：读取中间层节点，进一步缩小范围
-- 第 3 次磁盘 I/O：读取叶子节点，找到 id = 500 的记录
--
-- 总共只需 3 次磁盘 I/O，无论表有多少数据

-- 等值查询（精确匹配）
SELECT * FROM users WHERE id = 500;

-- 范围查询（利用叶子节点链表）
SELECT * FROM users WHERE id BETWEEN 100 AND 500;
-- 找到 id=100 后，沿链表顺序扫描到 id=500

-- 排序查询（索引本身有序）
SELECT * FROM users ORDER BY id LIMIT 10;
-- 直接从索引最左端读取，无需额外排序

聚簇索引与非聚簇索引

聚簇索引（Clustered Index）

-- 聚簇索引：数据行按索引键的顺序物理存储
-- 一张表只能有一个聚簇索引（InnoDB 中即主键）

-- InnoDB 主键就是聚簇索引
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 聚簇索引
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    status VARCHAR(20),
    created_at DATETIME,
    INDEX idx_order_no (order_no),         -- 非聚簇索引
    INDEX idx_user_id (user_id)            -- 非聚簇索引
);

-- 聚簇索引的叶子节点直接存储完整的行数据
-- 通过主键查询可以直接获取数据，无需回表
SELECT * FROM orders WHERE id = 100000;
-- 直接通过聚簇索引定位，效率最高

-- SQL Server 显式创建聚簇索引
CREATE CLUSTERED INDEX idx_orders_id ON orders(id);

-- 聚簇索引选择原则：
-- 1. 选择有序递增的列（避免页分裂）
-- 2. 选择经常用于范围查询的列
-- 3. 选择不易频繁更新的列
-- 4. 自增 ID 通常是最好的聚簇索引选择

非聚簇索引（Non-Clustered Index）

-- 非聚簇索引：叶子节点存储索引键值 + 主键值（或行指针）
-- 查询非索引列时需要"回表"操作

-- 创建非聚簇索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 通过非聚簇索引查询
SELECT * FROM orders WHERE user_id = 100;
-- 步骤：
-- 1. 在 idx_user_id 索引树中找到 user_id=100 的记录
-- 2. 获取对应的主键 id 值
-- 3. 回到聚簇索引树中通过 id 查找完整行数据

-- SQL Server 非聚簇索引
CREATE NONCLUSTERED INDEX idx_orders_status ON orders(status);

-- 回表代价：每条记录需要额外的随机 I/O
-- 如果结果集很大，回表代价可能超过全表扫描
-- 这时优化器可能选择不使用索引（即索引失效场景）

复合索引设计

复合索引与最左前缀原则

-- 复合索引：在多个列上创建的索引
CREATE INDEX idx_user_status_created
ON orders(user_id, status, created_at);

-- 最左前缀原则：索引 (a, b, c) 可以匹配以下查询
-- | 查询条件                     | 是否使用索引 | 说明           |
-- |-----------------------------|-------------|----------------|
-- | WHERE a = 1                 | 是          | 使用 a 列       |
-- | WHERE a = 1 AND b = 2       | 是          | 使用 a, b 列    |
-- | WHERE a = 1 AND b = 2 AND c = 3 | 是     | 使用全部三列     |
-- | WHERE b = 2                 | 否          | 跳过最左列 a    |
-- | WHERE c = 3                 | 否          | 跳过最左列 a, b |
-- | WHERE a = 1 AND c = 3       | 部分         | 只使用 a 列     |
-- | WHERE a > 1 AND b = 2       | 部分         | 范围查询后的列无法使用 |
-- | WHERE a = 1 ORDER BY b      | 是          | a 等值 + b 排序  |
-- | WHERE a = 1 ORDER BY c      | 部分         | a 等值，但跳过 b 排序 |

-- 有效使用复合索引
SELECT * FROM orders
WHERE user_id = 100 AND status = 'paid'
ORDER BY created_at DESC;

-- 复合索引列顺序设计原则
-- 1. 等值查询的列放在前面
-- 2. 范围查询的列放在后面
-- 3. 排序的列尽量利用索引顺序
-- 4. 高选择性（区分度高）的列放在前面

-- 不好的设计：范围查询在前
CREATE INDEX idx_bad ON orders(created_at, user_id, status);
-- 查询 WHERE created_at > '2026-01-01' AND user_id = 100
-- 只能用到 created_at 列

-- 好的设计：等值查询在前
CREATE INDEX idx_good ON orders(user_id, status, created_at);
-- 查询 WHERE user_id = 100 AND status = 'paid' AND created_at > '2026-01-01'
-- 三列都能用到

复合索引优化案例

-- 案例：电商订单查询优化
-- 常见查询模式
-- Q1: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- Q2: WHERE user_id = ? AND created_at BETWEEN ? AND ?
-- Q3: WHERE status = ? AND created_at > ? ORDER BY created_at

-- 创建一个覆盖多个查询的复合索引
CREATE INDEX idx_orders_composite
ON orders(user_id, status, created_at);

-- Q1 可以完全利用索引
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
-- type: ref, key: idx_orders_composite, Extra: NULL

-- Q2 可以利用索引的前两列+范围
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND created_at BETWEEN '2026-01-01' AND '2026-04-01';
-- 注意：这里跳过了 status，只有 user_id 能用到索引

-- 为 Q2 创建单独的索引
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- 为 Q3 创建单独的索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

覆盖索引

覆盖索引设计

-- 覆盖索引：索引包含了查询所需的所有列，无需回表
-- 通过 EXPLAIN 的 Extra 列显示 "Using index" 表示使用了覆盖索引

-- 示例表
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT,
    sales_count INT,
    INDEX idx_category_price (category, price)
);

-- 非覆盖索引查询（需要回表）
SELECT * FROM products WHERE category = '电子';
-- Extra: Using index condition（需要回表获取 name, stock, sales_count）

-- 覆盖索引查询
SELECT category, price FROM products WHERE category = '电子';
-- Extra: Using index（索引包含了所有查询列）

-- 扩展为覆盖索引
DROP INDEX idx_category_price ON products;
CREATE INDEX idx_covering ON products(category, price, name, sales_count);

-- 现在这个查询也可以使用覆盖索引
SELECT name, price, sales_count
FROM products WHERE category = '电子';
-- Extra: Using index

-- PostgreSQL 的 INCLUDE 语法
CREATE INDEX idx_products_covering
ON products(category, price)
INCLUDE (name, sales_count);

-- SQL Server 的 INCLUDE 语法
CREATE INDEX idx_products_covering
ON products(category, price)
INCLUDE (name, sales_count, stock);

-- 覆盖索引的权衡
-- 优点：消除回表，大幅减少 I/O
-- 缺点：索引列越多，索引越大，维护成本越高
-- 建议只在热点查询上使用覆盖索引

索引设计原则

索引设计最佳实践

-- 1. 选择合适的索引列
-- 优先为以下场景创建索引
-- WHERE 条件中的列
-- JOIN 关联的列
-- ORDER BY / GROUP BY 的列
-- DISTINCT 的列

-- 2. 选择高选择性的列
-- 选择性 = DISTINCT 值数量 / 总行数
-- 选择性越高，索引效果越好

-- 查看列的选择性（MySQL）
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) AS created_selectivity
FROM orders;
-- user_id 选择性高，适合创建索引
-- status 只有几个值，选择性低，适合作为复合索引的辅助列

-- 3. 避免过度索引
-- 查看 MySQL 表的索引使用情况
SELECT
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
ORDER BY count_read DESC;

-- 找出从未使用过的索引（可以删除）
SELECT
    t.table_schema,
    t.table_name,
    s.index_name,
    s.non_unique
FROM information_schema.statistics s
JOIN information_schema.tables t
    ON s.table_schema = t.table_schema
    AND s.table_name = t.table_name
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
    ON p.object_schema = s.table_schema
    AND p.object_name = s.table_name
    AND p.index_name = s.index_name
WHERE s.table_schema = 'mydb'
  AND (p.count_read IS NULL OR p.count_read = 0)
  AND s.index_name != 'PRIMARY';

-- 4. 定期维护索引
-- 重建碎片化索引（MySQL）
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;

-- PostgreSQL 重建索引
REINDEX INDEX idx_orders_user_id;
-- 在线重建（不锁表）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- SQL Server 重建索引
ALTER INDEX idx_orders_user_id ON orders REBUILD;
-- 在线重建
ALTER INDEX idx_orders_user_id ON orders REBUILD WITH (ONLINE = ON);

索引失效的常见场景

-- 1. 在索引列上使用函数或运算
SELECT * FROM orders WHERE YEAR(created_at) = 2026;     -- 索引失效
SELECT * FROM orders WHERE created_at >= '2026-01-01';   -- 索引有效

-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 12345;       -- order_no 是 VARCHAR，索引失效
SELECT * FROM orders WHERE order_no = '12345';     -- 索引有效

-- 3. 使用 NOT / != / <> / NOT IN
SELECT * FROM orders WHERE status != 'cancelled';   -- 索引可能失效
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');  -- 索引有效

-- 4. 前缀通配符 LIKE
SELECT * FROM orders WHERE order_no LIKE '%ABC%';   -- 索引失效
SELECT * FROM orders WHERE order_no LIKE 'ABC%';    -- 索引有效

-- 5. OR 条件中包含非索引列
SELECT * FROM orders WHERE user_id = 100 OR total_amount > 10000;
-- 如果 total_amount 没有索引，整体索引失效
-- 解决方案：为两列分别创建索引，优化器可能使用 Index Merge
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_amount ON orders(total_amount);

-- 6. IS NULL / IS NOT NULL（取决于数据分布）
-- 如果 NULL 值占比很小，IS NOT NULL 可能使用索引
-- 如果 NULL 值占比很大，IS NULL 可能不使用索引

唯一索引与全文索引

唯一索引

-- 唯一索引：保证列值的唯一性，同时提供查询加速
-- 适用场景：用户名、邮箱、订单号等唯一标识

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- MySQL 支持在创建表时直接定义
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE INDEX idx_username (username),
    UNIQUE INDEX idx_email (email)
);

-- 复合唯一索引
CREATE UNIQUE INDEX idx_user_product_favorite
ON favorites(user_id, product_id);
-- 确保同一用户不会重复收藏同一商品

-- NULL 值的处理
-- 大多数数据库中，唯一索引允许存在多个 NULL（NULL != NULL）
-- SQL Server 中，唯一索引只允许一个 NULL（除非使用过滤索引）
CREATE UNIQUE INDEX idx_users_phone
ON users(phone)
WHERE phone IS NOT NULL;  -- SQL Server 过滤索引，允许多个 NULL

全文索引

-- MySQL 全文索引（适合文本搜索场景）
CREATE TABLE articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (title, content)  -- 全文索引
) ENGINE=InnoDB;

-- 全文搜索查询
SELECT *, MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- 布尔模式搜索（支持 +必须包含 -排除 *通配）
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+索引 -失效' IN BOOLEAN MODE);

-- PostgreSQL 全文搜索
-- 创建 tsvector 列和 GIN 索引
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

UPDATE articles SET search_vector =
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(content, '')), 'B');

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 全文搜索查询
SELECT title,
       ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('simple', '数据库索引优化') query
WHERE search_vector @@ query
ORDER BY rank DESC;

全文索引适用场景：
- 文章搜索、产品搜索、日志搜索等文本检索
- LIKE '%keyword%' 的替代方案（全文索引可以利用索引，LIKE 不能）
- 需要相关度排序的搜索场景
- 注意：MySQL 全文索引对中文需要配置 ngram 解析器
  CREATE FULLTEXT INDEX ft_content ON articles(title, content) WITH PARSER ngram;

执行计划分析

MySQL EXPLAIN 详解

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 输出关键列解读：
-- | 列名        | 含义                                     |
-- |------------|-----------------------------------------|
-- | id         | 查询序号，子查询会有多个 id                  |
-- | type       | 访问类型（从好到差排列）                    |
-- | key        | 实际使用的索引                            |
-- | rows       | 预估扫描行数                              |
-- | filtered   | 过滤比例（100% 最好）                     |
-- | Extra      | 额外信息（排序、临时表等）                  |

-- type 列（从优到劣）：
-- system > const > eq_ref > ref > range > index > ALL
-- system/const: 主键或唯一索引等值查询，最优
-- eq_ref: JOIN 时主键/唯一索引等值查询
-- ref: 非唯一索引等值查询
-- range: 索引范围扫描（BETWEEN, >, <, IN）
-- index: 全索引扫描（遍历索引树）
-- ALL: 全表扫描，最差

-- Extra 列常见值：
-- Using index: 覆盖索引，无需回表（好）
-- Using where: 存储引擎返回数据后还需要在 Server 层过滤
-- Using temporary: 使用临时表（通常需要优化）
-- Using filesort: 额外排序操作（需要优化）
-- Using index condition: 索引条件下推（ICP）

-- EXPLAIN ANALYZE（MySQL 8.0+，显示实际执行时间）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

PostgreSQL EXPLAIN ANALYZE

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- 带实际执行统计
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_no, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > '2026-01-01';

-- 输出关键信息：
-- Seq Scan: 顺序扫描（全表扫描）
-- Index Scan: 索引扫描
-- Index Only Scan: 仅索引扫描（覆盖索引）
-- Bitmap Heap Scan: 位图堆扫描（多条件组合）
-- Nested Loop: 嵌套循环 JOIN（适合小表驱动大表）
-- Hash Join: 哈希 JOIN（适合等值连接的大表关联）
-- Merge Join: 合并 JOIN（适合已排序的数据）

-- 查看索引使用统计
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;  -- idx_scan 低的是可能未使用的索引

SQL Server 执行计划

-- 查看估计执行计划
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders WHERE user_id = 100;
GO
SET SHOWPLAN_TEXT OFF;

-- 查看实际执行计划（SSMS 中按 Ctrl+M）
-- 或使用 STATISTICS IO/TIME
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM orders WHERE user_id = 100;

-- 查看缺失索引建议
SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact,
    migs.user_seeks + migs.user_scans AS total_queries
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;

-- 索引使用统计
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    s.last_user_seek
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECT_NAME(i.object_id) = 'orders'
ORDER BY s.user_seeks + s.user_scans DESC;

索引与锁的交互

-- 索引对锁的影响（MySQL InnoDB）
-- 1. 使用索引的查询：只锁定匹配的行
SELECT * FROM orders WHERE id = 100 FOR UPDATE;
-- 只锁定 id=100 这一行（行锁）

-- 2. 不使用索引的查询：锁定所有行（退化为表锁）
SELECT * FROM orders WHERE non_indexed_col = 'value' FOR UPDATE;
-- 如果 non_indexed_col 没有索引，InnoDB 会锁定全部行

-- 3. 范围查询的间隙锁
SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE;
-- 锁定 id 在 (100, 200] 之间的记录以及间隙

-- 4. 死锁与索引的关系
-- 事务 A: UPDATE orders SET status = 'paid' WHERE user_id = 100;
-- 事务 B: UPDATE orders SET status = 'shipped' WHERE status = 'paid';
-- 如果 status 没有索引，事务 B 会锁全表，可能造成死锁

-- 建议：
-- UPDATE/DELETE 的 WHERE 条件一定要有索引支持
-- 复合索引设计要考虑写操作的查询模式
-- 检查锁等待：SHOW ENGINE INNODB STATUS

索引与锁的最佳实践：
- UPDATE/DELETE 语句的 WHERE 条件列必须有索引
- 没有索引的更新操作会锁全表，严重影响并发
- 范围更新注意间隙锁范围
- 高并发场景下，索引不仅加速查询，还减少锁竞争
- 定期检查 information_schema.innodb_lock_waits 分析锁等待

特殊索引类型

哈希索引

-- MySQL Memory 引擎支持哈希索引
CREATE TABLE session_cache (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id BIGINT,
    data JSON,
    expires_at DATETIME,
    INDEX USING HASH (session_id)
) ENGINE=MEMORY;

-- PostgreSQL 创建哈希索引
CREATE INDEX idx_session_hash ON session_cache USING HASH (session_id);

-- 哈希索引特点：
-- 等值查询 O(1)，极快
-- 不支持范围查询、排序、最左前缀
-- 不存储实际值，无法用于覆盖索引
-- 哈希冲突时使用链表解决

-- InnoDB 的自适应哈希索引（AHI）
-- InnoDB 自动为热点页建立内存中的哈希索引
-- 查看 AHI 状态：SHOW STATUS LIKE 'Innodb_adaptive_hash%';

空间索引

-- MySQL 空间索引（GIS 场景）
CREATE TABLE locations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_position (position)
);

-- 插入空间数据
INSERT INTO locations (name, position)
VALUES ('北京站', ST_GeomFromText('POINT(116.427 39.902)', 4326));

-- 范围查询：查找 5km 内的位置
SELECT name,
       ST_Distance_Sphere(position, ST_GeomFromText('POINT(116.397 39.908)', 4326)) AS distance
FROM locations
WHERE ST_Contains(
    ST_Buffer(ST_GeomFromText('POINT(116.397 39.908)', 4326), 0.05),
    position
)
ORDER BY distance;

部分索引与表达式索引

-- PostgreSQL 部分索引（只索引满足条件的行）
-- 适合数据分布不均匀的场景
CREATE INDEX idx_orders_active
ON orders(created_at)
WHERE status NOT IN ('cancelled', 'deleted');

-- 只查询活跃订单时走这个索引
SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'deleted')
  AND created_at > '2026-01-01';

-- PostgreSQL 表达式索引
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

-- 查询时使用相同表达式才能命中
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- MySQL 8.0+ 函数索引
CREATE INDEX idx_users_lower_email
ON users((LOWER(email)));

-- 计算列索引（SQL Server / MySQL）
ALTER TABLE orders ADD COLUMN total_amount_dec AS (CAST(total_amount AS DECIMAL(10,2)));
CREATE INDEX idx_orders_total ON orders(total_amount_dec);

索引监控与维护

-- MySQL 索引碎片检查
SELECT
    table_name,
    data_free / 1024 / 1024 AS fragmentation_mb,
    data_length / 1024 / 1024 AS data_mb,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'mydb'
  AND table_type = 'BASE TABLE'
  AND data_free > 0
ORDER BY fragmentation_pct DESC;

-- 碎片率 > 20% 建议优化
-- OPTIMIZE TABLE orders;  -- 锁表重建
-- ALTER TABLE orders ENGINE=InnoDB;  -- 在线重建

-- MySQL 索引统计信息
ANALYZE TABLE orders;  -- 更新统计信息
SHOW INDEX FROM orders;  -- Cardinality 列表示唯一值估计数

-- PostgreSQL 索引膨胀检查
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::text)) AS index_size,
    CASE WHEN indrelid IS NOT NULL THEN
        ROUND(100.0 * pg_relation_size(indexname::text) /
              pg_relation_size(indrelid), 2)
    END AS index_pct
FROM pg_indexes
JOIN pg_class ON pg_class.relname = indexname
LEFT JOIN pg_index ON pg_index.indexrelid = pg_class.oid
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::text) DESC;

-- 索引大小排行（找出过大的索引）
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::text)) AS size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::text) DESC
LIMIT 20;

优点

• 1.大幅提升查询速度 — 通过索引可将查询复杂度从 O(n) 降到 O(log n)
• 2.减少磁盘 I/O — B+ 树结构只需 3-4 次 I/O 即可定位数据
• 3.加速排序和分组 — 索引本身有序，可避免额外的排序操作
• 4.支持唯一性约束 — 唯一索引保证数据完整性

缺点

• 1.占用额外存储 — 每个索引都会占用磁盘空间，复合索引尤为明显
• 2.降低写入性能 — INSERT/UPDATE/DELETE 需要同步维护索引
• 3.索引碎片化 — 频繁更新导致索引碎片，需要定期维护
• 4.设计复杂度高 — 复合索引顺序选择、覆盖索引设计需要深入理解业务

总结

索引是数据库性能优化的基石，理解 B+ 树的底层原理有助于做出正确的索引设计决策。聚簇索引决定了数据的物理存储顺序，复合索引需要遵循最左前缀原则，覆盖索引可以消除回表开销。在实际项目中，需要根据查询模式、数据分布和业务特点来设计索引，并定期监控和维护索引的健康状态。

关键知识点

• 数据库主题一定要同时看数据模型、读写模式和执行代价。
• 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
• 高可用、备份、迁移和治理与查询优化同样重要。
• 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

• 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
• 上线前准备回滚脚本、备份点和校验方案。
• 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
• 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

• 脱离真实数据分布讨论索引或分片。
• 只看单条 SQL，不看整条业务链路的事务和锁。
• 把测试环境结论直接等同于生产环境结论。
• 脱离真实数据分布设计索引。

进阶路线

• 继续向执行计划、存储引擎、复制机制和数据治理层深入。
• 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
• 沉淀成数据库设计规范、SQL 审核规则和变更流程。
• 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

• 当你准备把《索引原理与设计》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
• 适合数据建模、查询优化、事务控制、高可用和迁移治理。
• 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

• 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
• 所有优化结论都结合执行计划、样本数据和监控指标验证。
• 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

• 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
• 检查执行计划是否走错索引、是否发生排序或全表扫描。
• 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

• 如果把《索引原理与设计》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
• 《索引原理与设计》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
• 相比默认实现或替代方案，采用《索引原理与设计》最大的收益和代价分别是什么？

延伸阅读

• 连接池优化
• 数据迁移实战
• 数据库迁移策略
• 数据库设计原则