Ilren 小记

索引的本质：数据库的加速引擎

索引如同书籍的目录，通过预排序的数据结构（如B+树）减少磁盘扫描量。核心价值体现在：

减少数据扫描范围（从全表扫描到索引区间扫描）
避免排序操作（利用索引天然有序性）
实现覆盖查询（直接从索引获取数据）

一、按数据结构分类的索引类型

1. B+树索引（最常用）

结构：多级平衡树，叶子节点形成有序链表
优势：范围查询、排序、高并发读写
SQL示例：

-- 创建B+树索引
CREATE INDEX idx_employee_age ON employees(age);

-- 范围查询优化
SELECT * FROM employees WHERE age BETWEEN 25 AND 35; -- 利用索引快速定位区间

-- 排序优化
SELECT * FROM employees ORDER BY hire_date; -- 若hire_date有索引，避免filesort

2. 哈希索引

结构：基于哈希表的键值存储
优势：O(1)复杂度等值查询
劣势：不支持范围查询
SQL示例：

-- MySQL创建哈希索引（MEMORY引擎）
CREATE TABLE session_data (
    session_id CHAR(32) PRIMARY KEY,
    data BLOB
) ENGINE=MEMORY;

CREATE INDEX hash_idx USING HASH ON session_data(session_id);

-- 等值查询
SELECT data FROM session_data WHERE session_id = 'e99a18c428cb38d5f260853678922e03';

3. 全文索引

结构：倒排索引（单词->文档映射）
适用场景：文本内容搜索
SQL示例：

-- MySQL创建全文索引
ALTER TABLE articles ADD FULLTEXT ft_idx_content (content);

-- 全文搜索
SELECT title FROM articles 
WHERE MATCH(content) AGAINST('+database -Oracle' IN BOOLEAN MODE);

4. R-Tree空间索引

结构：多维空间数据索引
适用场景：地理坐标查询
SQL示例：

-- PostGIS示例
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

CREATE INDEX gidx_locations_geom ON locations USING GIST(geom);

-- 附近10公里内的地点
SELECT name FROM locations 
WHERE ST_DWithin(
    geom, 
    ST_SetSRID(ST_MakePoint(-73.97, 40.77), 4326),
    10000
);

二、按逻辑功能分类的索引类型

1. 主键索引（PRIMARY KEY）

特性：唯一标识行，InnoDB的聚簇索引
SQL示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 主键索引
    email VARCHAR(255) UNIQUE
);

2. 唯一索引（UNIQUE）

特性：强制列值唯一性
SQL示例：

ALTER TABLE products ADD UNIQUE uniq_product_code (product_code);

-- 唯一约束检查
INSERT INTO products (product_code) VALUES ('A123'); -- 第二次插入报错

3. 复合索引（最左前缀原则）

特性：多列联合索引，遵循最左匹配
SQL示例：

CREATE INDEX idx_name_phone ON customers(last_name, first_name, phone);

-- 有效使用索引的查询
SELECT * FROM customers 
WHERE last_name = 'Smith' 
  AND first_name = 'John'; -- 使用索引前两列

-- 部分使用索引
SELECT * FROM customers 
WHERE last_name = 'Smith' 
  AND phone = '555-1234'; -- 只使用last_name列

-- 索引失效的查询
SELECT * FROM customers 
WHERE first_name = 'John' 
  AND phone = '555-1234'; -- 未使用最左列

4. 覆盖索引（Covering Index）

特性：索引包含查询所需全部字段
SQL示例：

-- 创建覆盖索引
CREATE INDEX idx_cover ON orders(order_date, total_amount, customer_id);

-- 避免回表查询
SELECT order_date, total_amount 
FROM orders
WHERE customer_id = 1007 
  AND order_date > '2023-01-01'; -- 直接从索引获取数据

三、索引如何提升查询效率：工作原理图解

场景1：全表扫描 vs 索引扫描

-- 无索引（全表扫描）
SELECT * FROM employees WHERE department = 'Engineering'; -- 扫描所有数据页

-- 有索引（索引范围扫描）
CREATE INDEX idx_dept ON employees(department);
SELECT * FROM employees WHERE department = 'Engineering'; -- 仅扫描Engineering相关节点

场景2：避免排序操作

-- 无索引（额外排序）
SELECT * FROM orders ORDER BY order_date DESC; -- Using filesort

-- 有索引（利用有序性）
CREATE INDEX idx_order_date ON orders(order_date);
SELECT * FROM orders ORDER BY order_date DESC; -- Using index

场景3：减少磁盘I/O

二级索引查询流程：

在idx_department找到Engineering对应的主键列表
通过主键回表查询聚簇索引获取完整数据
覆盖索引优化：若索引包含所有需返回字段，跳过步骤2

四、索引优化实战建议

1. 索引创建原则

-- 选择高区分度列
CREATE INDEX idx_email ON users(email); -- email比gender更适合建索引

-- 短字段使用前缀索引
CREATE INDEX idx_name_prefix ON products(name(10)); -- 取前10字符

-- 常查询条件优先
CREATE INDEX idx_active_user ON users(last_login) WHERE is_active = 1;

2. 避免索引失效场景

-- 函数操作导致失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 索引失效

-- 改为范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- LIKE通配符前置失效
SELECT * FROM products WHERE name LIKE '%phone'; -- 无法使用索引

-- 全文索引替代
ALTER TABLE products ADD FULLTEXT ft_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone*' IN BOOLEAN MODE);

3. 执行计划分析（EXPLAIN）

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 305 
  AND status = 'shipped';

-- 关键指标解读
-- type: ref (索引查找)
-- key: idx_customer_status
-- rows: 50 (扫描行数)
-- Extra: Using index condition

总结：索引使用最佳实践

精准定位原则：WHERE/JOIN/ORDER BY/GROUP BY涉及的列优先建索引
复合索引策略：高频查询创建复合索引，遵循最左前缀原则
覆盖索引优先：查询字段尽量包含在索引中

定期索引维护：

ANALYZE TABLE orders; -- 更新统计信息
OPTIMIZE TABLE orders; -- 重建索引减少碎片

避免过度索引：每个额外索引增加写操作成本

重要提醒：索引不是越多越好！监控慢查询日志，使用EXPLAIN验证索引效果，才是性能优化的正确姿势。

数据库索引：提升查询性能的关键技术