Java数据库索引优化

索引大幅提升查询性能，但使用不当会降低写入性能，需合理设计。

索引原理

B+树结构

┌─────────────────────────────────────┐
│            B+树索引                  │
├─────────────────────────────────────┤
│                                     │
│         [根节点]                     │
│         10 | 20 | 30                │
│          /    \    \                │
│    [叶子节点]  [叶子节点]  [叶子节点] │
│    1-10→数据   11-20→数据  21-30→数据│
│                                     │
│ 特点：                               │
│ - 叶子节点存储数据或指针              │
│ - 叶子节点有序链接                    │
│ - 查找O(logN)                        │
│ - 范围查询高效                        │
│                                     │
└─────────────────────────────────────┘

聚簇索引与非聚簇索引

聚簇索引（InnoDB主键）：
┌──────────┐
│ 主键值   │ → 直接指向数据行
└──────────┘

非聚簇索引（普通索引）：
┌──────────┐
│ 素引列值 │ → 指向主键值 → 再指向数据行（回表）
└──────────┘

索引类型

MySQL索引类型

创建索引

-- 主键索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 唯一索引
CREATE UNIQUE INDEX idx_name ON users(name);

-- 普通索引
CREATE INDEX idx_age ON users(age);

-- 组合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

素引使用原则

最左前缀原则

-- 组合索引：(name, age, city)

-- 有效使用：
SELECT * FROM users WHERE name = '张三';             -- 用到name
SELECT * FROM users WHERE name = '张三' AND age = 25; -- 用到name, age
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京'; -- 全用

-- 无效使用：
SELECT * FROM users WHERE age = 25;           -- 不用索引（跳过name）
SELECT * FROM users WHERE city = '北京';       -- 不用索引（跳过name, age）

-- 范围查询后失效：
SELECT * FROM users WHERE name = '张三' AND age > 25 AND city = '北京';
-- 只用到name, age，city不生效

素引选择原则

区分度判断

-- 区分度计算
SELECT COUNT(DISTINCT name) / COUNT(*) FROM users;

-- 区分度 > 0.1：适合建索引
-- 区分度 < 0.1：不适合建索引

-- 示例：性别区分度低
SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users;
-- 结果约0.5（只有男/女），不适合索引

素引优化技巧

覆盖索引

-- 普通查询：回表
SELECT * FROM users WHERE name = '张三';  -- 查name索引→回表取全部字段

-- 覆盖索引：不回表
SELECT id, name FROM users WHERE name = '张三';
-- name索引包含id（聚簇索引主键），不需要回表

-- 创建覆盖索引
CREATE INDEX idx_name_id ON users(name, id);

素引下推

-- MySQL 5.6+ 素引下推（ICP）
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;

-- 无ICP：素引只过滤name，age在存储层过滤
-- 有ICP：素引同时过滤name和age，减少回表次数

避免索引失效

-- 1. 不在索引列上做运算
SELECT * FROM users WHERE YEAR(create_time) = 2024;  -- 素引失效！
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';  -- 有效

-- 2. 避免类型转换
SELECT * FROM users WHERE name = 123;  -- name是字符串，类型转换，素引失效！
SELECT * FROM users WHERE name = '123';  -- 有效

-- 3. 避免LIKE左模糊
SELECT * FROM users WHERE name LIKE '%张';  -- 素引失效！
SELECT * FROM users WHERE name LIKE '张%';  -- 有效

-- 4. 避免OR连接不同列
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 素引可能失效
SELECT * FROM users WHERE name = '张三' UNION SELECT * FROM users WHERE age = 25;  -- 改用UNION

-- 5. 避免!=和NOT IN
SELECT * FROM users WHERE age != 25;  -- 素引可能失效
SELECT * FROM users WHERE age < 25 OR age > 25;  -- 改用范围查询

-- 6. 避免 NULL判断
SELECT * FROM users WHERE name IS NULL;  -- 素引可能失效
-- 设计时字段尽量NOT NULL

索引维护

查看索引使用情况

-- 查看表索引
SHOW INDEX FROM users;

-- 查看索引是否被使用
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- EXPLAIN结果
-- type: ALL（全表扫描，无索引）
-- type: ref（使用索引）
-- key: 显示使用的索引名

查看未使用索引

-- MySQL统计索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 找出从未使用的索引，考虑删除

删除冗余索引

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 示例：
-- 有索引(name, age)
-- 又建索引(name)
-- name索引是冗余的，可删除

DROP INDEX idx_name ON users;

组合索引设计

选择顺序

-- 组合索引列顺序原则：
-- 1. 区分度高的列在前
-- 2. 常单独查询的列在前
-- 3. 范围查询的列在后

-- 示例：区分度
name: 区分度0.8（高）
age: 区分度0.1（低）
city: 区分度0.3（中）

-- 建议：(name, city, age)
CREATE INDEX idx_name_city_age ON users(name, city, age);

一张表索引数量

-- 建议：
-- 单表索引数量 <= 5个
-- 组合索引列数 <= 3列

-- 过多索引影响：
-- 1. 写入性能下降（每次更新多个索引）
-- 2. 索引选择困难
-- 3. 占用存储空间

素引性能对比

注意事项

素引不是越多越好，影响写入性能

组合索引遵循最左前缀原则

区分度低的字段不建议索引

避免索引失效场景（运算、类型转换、左模糊）

定期检查未使用索引，考虑删除

大表索引优化收益大，小表可能不需要

要点总结

1. B+树索引O(logN)查找，范围查询高效
2. 聚簇索引数据与索引一起，普通索引需回表
3. 组合索引遵循最左前缀原则
4. 覆盖索引避免回表，性能更优
5. 避免索引失效：不做运算、不类型转换、不左模糊
6. 单表索引<=5，组合索引<=3列