索引浅谈

一、索引是什么？一个生动的比喻

想象一下你要在一本1000页的百科全书中查找"数据库"相关的内容。如果没有目录，你只能从第一页开始逐页翻阅——这就是数据库的全表扫描（Full Table Scan）。

而如果有精确的索引页，你可以：

在索引页中找到"数据库"这个词条
2.查看它标注的页码（比如495页）
3.直接翻到对应页面获取内容

这个索引页就是数据库索引的真实写照。它是一种特殊的数据结构，通过额外的存储空间来换取极致的查询效率。

二、索引的核心工作原理：B+树

达梦数据库默认使用 B+树（B-Plus Tree）结构实现索引，这是一种高效且稳定的数据结构：

多路平衡搜索树：每个节点可以有多个子节点，保持树的高度很低
所有数据存储在叶子节点：非叶子节点只存储键值，叶子节点存储完整数据或指针
叶子节点相互链接：非常适合范围查询和排序操作

即使面对千万级数据，B+树也只需要3-4次磁盘I/O就能定位到目标数据，避免了大量的无效扫描。

三、达梦数据库中索引的常见类型

1. 普通索引（INDEX）
最基本的索引类型，没有任何限制：

CREATE INDEX idx_employee_name ON employees (employee_name);

2. 唯一索引（UNIQUE INDEX）
确保索引列的值唯一，允许有空值：

CREATE UNIQUE INDEX uk_employee_id_card ON employees (id_card);

3. 复合索引（组合索引）
基于多个列创建的索引，注意最左前缀原则：

-- 将最常用于查询的列放在前面
CREATE INDEX idx_dept_salary ON employees (department_id, salary);

4. 其他高级索引

聚簇索引：表数据按索引顺序物理存储，一个表只能有一个
位图索引：适用于低基数列（如性别、状态）
函数索引：基于表达式或函数计算结果创建

四、索引的实践

应该创建索引的场景

WHERE子句中的高频过滤条件

-- 为这个条件创建索引
SELECT * FROM orders WHERE user_id = 1001 AND status = 'completed';

表连接（JOIN）的关联字段

-- 为两个表的关联字段创建索引
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id; -- 应为o.user_id和u.id创建索引

排序和分组字段

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id  -- 为department_id创建索引
ORDER BY department_id;

高选择性字段（重复值少的列，如身份证号、手机号）

不应该创建索引的场景
小表（数据量很少的表）
低选择性字段（重复值多的列，如性别、状态标志）
很少或从不在查询中使用的列
写多读少的表（索引会降低写入性能）

索引失效的常见陷阱

即使创建了索引，错误的查询方式也会导致索引失效：

-- ❌ 在索引列上使用函数或计算
SELECT * FROM employees WHERE UPPER(name) = 'ZHANGSAN';

-- ❌ LIKE通配符开头
SELECT * FROM employees WHERE name LIKE '%张%';

-- ❌ 跳过复合索引的最左列
SELECT * FROM employees WHERE salary > 5000; -- 索引是(department_id, salary)

-- ❌ 使用OR连接非索引列条件
SELECT * FROM employees WHERE department_id = 10 OR hire_date = '2023-01-01';

五、高级应用场景

跨表空间存储索引

达梦允许将索引存储在与表数据不同的表空间中，这可以带来性能和管理上的好处：

-- 将索引存放在专门的表空间
CREATE INDEX idx_orders_date ON orders (order_date) STORAGE (ON index_ts);

-- 将数据和索引分别存储在不同表空间
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    INDEX idx_name (name) STORAGE (ON index_ts)
) STORAGE (ON data_ts);

优势：

将索引和数据文件部署到不同的物理磁盘，减少I/O竞争
单独备份和恢复索引和数据
针对不同的访问模式进行优化

使用EXPLAIN分析索引使用情况

达梦的EXPLAIN命令可以查看查询执行计划，这是优化查询的必备技能：

EXPLAIN
SELECT * FROM employees WHERE department_id = 10 AND salary > 5000;

查看输出结果，如果看到INDEX SCAN或INDEX RANGE SCAN表示索引被正确使用，如果看到TABLE SCAN (FULL)则表示进行了全表扫描。

六、索引的代价与维护

索引不是免费的，它需要付出以下代价：

占用存储空间：索引需要额外的磁盘空间
降低DML操作速度：插入、更新、删除操作需要维护索引结构
维护成本：需要定期监控和优化

建议定期对索引进行重建，尤其是在大量DML操作后：

-- 重建索引，清理碎片
ALTER INDEX idx_employee_name REBUILD;

七、总结与建议

索引是数据库性能优化的第一利器，但需要理性使用：

不是越多越好：每个额外的索引都会带来维护代价
定期审查：删除未使用或重复的索引
监控分析：使用达梦的性能监控工具分析索引使用情况
测试验证：在生产环境变更前，充分测试索引效果

最终建议：从最影响性能的关键查询开始，创建必要的索引，然后通过监控和分析逐步优化。索引优化的目标是用适当的空间代价换取最大的查询性能提升。

作者