索引与优化

一、索引基础

1.1 索引概念

1.1.1 什么是索引

索引：数据库中用于提高查询速度的数据结构。

东巴文理解：

索引 = 书籍目录
书籍目录 → 快速定位内容
数据库索引 → 快速定位数据

1.1.2 索引的优缺点

优点：

• 提高查询速度
• 加速排序和分组
• 唯一索引保证数据唯一性

缺点：

• 占用磁盘空间
• 降低写入速度
• 需要维护成本

1.2 索引类型

1.2.1 B+Tree索引

特点：

• 最常用的索引类型
• 支持范围查询
• 支持排序
• 适合精确查找

结构示意：

        [根节点]
       /    |    \
   [中间节点] [中间节点] [中间节点]
   /   |   \
[叶子节点] [叶子节点] [叶子节点]
   ↓         ↓         ↓
 [数据]    [数据]    [数据]

示例：

-- 创建B+Tree索引
CREATE INDEX idx_name ON employees(name);

-- 查看索引
SHOW INDEX FROM employees;

1.2.2 Hash索引

特点：

• 基于哈希表
• 只支持精确查找
• 不支持范围查询
• 不支持排序

示例：

-- Memory引擎支持Hash索引
CREATE TABLE test_hash (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name USING HASH (name)
) ENGINE=MEMORY;

1.2.3 全文索引

特点：

• 用于全文搜索
• 支持中文分词（ngram）
• 适合文本检索

示例：

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

-- 使用全文索引
SELECT * FROM articles
WHERE MATCH(content) AGAINST('数据库');

1.2.4 空间索引

特点：

• 用于地理空间数据
• 支持GIS数据类型
• 适合地图应用

示例：

-- 创建空间索引
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    point POINT,
    SPATIAL INDEX idx_point (point)
);

-- 插入空间数据
INSERT INTO locations VALUES
(1, '北京', POINT(116.4074, 39.9042)),
(2, '上海', POINT(121.4737, 31.2304));

二、索引设计

2.1 索引分类

2.1.1 主键索引

特点：

• 自动创建
• 唯一且非空
• 一个表只能有一个

示例：

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

-- 或
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

2.1.2 唯一索引

特点：

• 值必须唯一
• 允许NULL值
• 一个表可以有多个

示例：

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

-- 或
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

2.1.3 普通索引

特点：

• 最基本的索引
• 无特殊限制
• 允许重复值

示例：

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 或
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

2.1.4 组合索引

特点：

• 多个列组成
• 遵循最左前缀原则
• 提高多列查询性能

示例：

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

-- 使用组合索引
SELECT * FROM users WHERE name = '张三';  -- 使用索引
SELECT * FROM users WHERE name = '张三' AND age = 25;  -- 使用索引
SELECT * FROM users WHERE age = 25;  -- 不使用索引

2.2 索引选择原则

2.2.1 适合创建索引的情况

✅ WHERE子句频繁使用的列 ✅ JOIN连接的列 ✅ ORDER BY排序的列 ✅ GROUP BY分组的列 ✅ 区分度高的列

示例：

-- 区分度高的列适合创建索引
SELECT 
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;

-- name_selectivity: 0.95（高区分度，适合索引）
-- gender_selectivity: 0.5（低区分度，不适合索引）

2.2.2 不适合创建索引的情况

❌ 数据量小的表 ❌ 频繁更新的列 ❌ 区分度低的列 ❌ 很少使用的列

示例：

-- 不适合创建索引的列
CREATE TABLE logs (
    id INT PRIMARY KEY,
    level VARCHAR(10),  -- 区分度低（INFO/WARN/ERROR）
    message TEXT,       -- 长文本
    created_at TIMESTAMP
);

-- level区分度低，不适合单独索引
-- message是长文本，不适合索引

2.3 最左前缀原则

2.3.1 原则说明

组合索引：按照定义顺序，从左到右依次匹配。

示例：

-- 创建组合索引
CREATE INDEX idx_a_b_c ON test(a, b, c);

-- 使用索引的情况
WHERE a = 1                    -- ✅ 使用索引
WHERE a = 1 AND b = 2          -- ✅ 使用索引
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 使用索引

-- 不使用索引的情况
WHERE b = 2                    -- ❌ 不使用索引
WHERE c = 3                    -- ❌ 不使用索引
WHERE b = 2 AND c = 3          -- ❌ 不使用索引

-- 部分使用索引
WHERE a = 1 AND c = 3          -- ✅ 只使用a列索引

2.3.2 范围查询

范围查询后的列不使用索引：

-- 创建组合索引
CREATE INDEX idx_a_b_c ON test(a, b, c);

-- 范围查询
WHERE a = 1 AND b > 2 AND c = 3  -- ✅ 使用a和b，不使用c

三、索引操作

3.1 创建索引

3.1.1 CREATE INDEX

语法格式：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名(列名[(长度)] [ASC|DESC]);

示例：

-- 创建普通索引
CREATE INDEX idx_name ON employees(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);

-- 创建组合索引
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON employees(name(10));

3.1.2 ALTER TABLE

语法格式：

ALTER TABLE 表名 ADD [索引类型] INDEX 索引名(列名);

示例：

-- 添加主键
ALTER TABLE employees ADD PRIMARY KEY (id);

-- 添加唯一索引
ALTER TABLE employees ADD UNIQUE INDEX idx_email (email);

-- 添加普通索引
ALTER TABLE employees ADD INDEX idx_name (name);

3.2 删除索引

3.2.1 DROP INDEX

语法格式：

DROP INDEX 索引名 ON 表名;

示例：

-- 删除索引
DROP INDEX idx_name ON employees;

3.2.2 ALTER TABLE

语法格式：

ALTER TABLE 表名 DROP INDEX 索引名;

示例：

-- 删除索引
ALTER TABLE employees DROP INDEX idx_name;

-- 删除主键
ALTER TABLE employees DROP PRIMARY KEY;

3.3 查看索引

3.3.1 SHOW INDEX

语法格式：

SHOW INDEX FROM 表名;

示例：

-- 查看索引
SHOW INDEX FROM employees;

输出说明：

四、索引优化

4.1 避免索引失效

4.1.1 不要在索引列上使用函数

错误示例：

-- 索引失效
SELECT * FROM employees WHERE YEAR(created_at) = 2024;

-- 优化后
SELECT * FROM employees 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

4.1.2 不要在索引列上使用运算

错误示例：

-- 索引失效
SELECT * FROM employees WHERE salary * 12 > 100000;

-- 优化后
SELECT * FROM employees WHERE salary > 100000 / 12;

4.1.3 避免隐式类型转换

错误示例：

-- 字符串列，传入数字，索引失效
SELECT * FROM employees WHERE phone = 13800138000;

-- 优化后
SELECT * FROM employees WHERE phone = '13800138000';

4.1.4 避免使用NOT和!=

错误示例：

-- 索引可能失效
SELECT * FROM employees WHERE status != 0;

-- 优化后
SELECT * FROM employees WHERE status IN (1, 2, 3);

4.1.5 避免使用OR

错误示例：

-- 索引可能失效
SELECT * FROM employees WHERE name = '张三' OR age = 25;

-- 优化后
SELECT * FROM employees WHERE name = '张三'
UNION
SELECT * FROM employees WHERE age = 25;

4.1.6 LIKE查询优化

错误示例：

-- 索引失效（前缀模糊）
SELECT * FROM employees WHERE name LIKE '%张%';

-- 使用索引（后缀模糊）
SELECT * FROM employees WHERE name LIKE '张%';

4.2 使用覆盖索引

4.2.1 覆盖索引概念

覆盖索引：查询所需的所有列都在索引中，不需要回表。

示例：

-- 创建组合索引
CREATE INDEX idx_name_age ON employees(name, age);

-- 使用覆盖索引
SELECT name, age FROM employees WHERE name = '张三';

-- 查看执行计划
EXPLAIN SELECT name, age FROM employees WHERE name = '张三';

Extra列显示Using index：表示使用覆盖索引。

4.3 索引下推

4.3.1 索引下推概念

索引下推（ICP）：在索引遍历过程中，对索引中包含的字段进行判断，减少回表次数。

示例：

-- 创建组合索引
CREATE INDEX idx_name_age ON employees(name, age);

-- 查询
SELECT * FROM employees WHERE name LIKE '张%' AND age = 25;

无ICP：

• 存储引擎：根据name筛选，返回所有name LIKE '张%'的行
• 服务器：根据age筛选

有ICP：

• 存储引擎：根据name和age筛选，只返回满足条件的行
• 减少回表次数

查看ICP：

EXPLAIN SELECT * FROM employees WHERE name LIKE '张%' AND age = 25;

Extra列显示Using index condition：表示使用索引下推。

五、查询优化

5.1 EXPLAIN分析

5.1.1 基本用法

语法格式：

EXPLAIN SELECT语句;

示例：

EXPLAIN SELECT * FROM employees WHERE name = '张三';

5.1.2 输出字段说明

id：查询标识符

select_type：查询类型

type：访问类型（性能从好到差）

key：使用的索引

rows：预估扫描行数

Extra：额外信息

5.1.3 优化示例

示例1：全表扫描优化

-- 查看执行计划
EXPLAIN SELECT * FROM employees WHERE name = '张三';

-- type: ALL（全表扫描）
-- 创建索引
CREATE INDEX idx_name ON employees(name);

-- 再次查看
EXPLAIN SELECT * FROM employees WHERE name = '张三';
-- type: ref（索引查找）

示例2：文件排序优化

-- 查看执行计划
EXPLAIN SELECT * FROM employees ORDER BY salary;

-- Extra: Using filesort（文件排序）
-- 创建索引
CREATE INDEX idx_salary ON employees(salary);

-- 再次查看
EXPLAIN SELECT * FROM employees ORDER BY salary;
-- Extra: Using index（使用索引）

5.2 慢查询日志

5.2.1 开启慢查询日志

查看配置：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

开启慢查询日志：

-- 临时开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录

-- 永久开启（修改配置文件）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

5.2.2 分析慢查询日志

使用mysqldumpslow：

# 查看慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 参数说明
-s t：按查询时间排序
-t 10：显示前10条

使用pt-query-digest：

# 安装Percona Toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

5.3 优化策略

5.3.1 查询优化

只查询需要的列：

-- 不推荐
SELECT * FROM employees;

-- 推荐
SELECT id, name FROM employees;

使用LIMIT：

-- 限制返回行数
SELECT * FROM employees LIMIT 100;

**避免SELECT ***：

-- 不推荐
SELECT * FROM employees WHERE name = '张三';

-- 推荐
SELECT id, name, salary FROM employees WHERE name = '张三';

5.3.2 表结构优化

选择合适的数据类型：

-- 不推荐
CREATE TABLE users (
    id INT,
    name VARCHAR(255),
    age INT,
    gender VARCHAR(10)
);

-- 推荐
CREATE TABLE users (
    id INT UNSIGNED,
    name VARCHAR(50),
    age TINYINT UNSIGNED,
    gender ENUM('男', '女')
);

避免NULL值：

-- 不推荐
CREATE TABLE users (
    id INT,
    name VARCHAR(50)
);

-- 推荐
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL DEFAULT ''
);

5.3.3 索引优化

定期分析和优化表：

-- 分析表
ANALYZE TABLE employees;

-- 优化表
OPTIMIZE TABLE employees;

查看索引使用情况：

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'database_name'
ORDER BY TABLE_NAME, INDEX_NAME;

六、实战案例

6.1 优化慢查询

问题查询：

SELECT * FROM orders 
WHERE YEAR(created_at) = 2024 
AND MONTH(created_at) = 1
AND status = 1;

分析：

EXPLAIN SELECT * FROM orders 
WHERE YEAR(created_at) = 2024 
AND MONTH(created_at) = 1
AND status = 1;

优化方案：

-- 1. 创建组合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 2. 修改查询条件
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
AND created_at < '2024-02-01'
AND status = 1;

6.2 优化分页查询

问题查询：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

优化方案：

-- 方案1：使用覆盖索引
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) tmp ON o.id = tmp.id;

-- 方案2：使用游标
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

6.3 优化JOIN查询

问题查询：

SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

优化方案：

-- 1. 创建索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

-- 2. 使用小表驱动大表
SELECT * FROM (
    SELECT * FROM orders WHERE status = 1
) o
INNER JOIN users u ON o.user_id = u.id;

七、本章小结

7.1 核心要点

✅ 索引提高查询速度，但降低写入速度 ✅ 选择合适的索引类型和列 ✅ 遵循最左前缀原则 ✅ 避免索引失效的情况 ✅ 使用EXPLAIN分析查询

7.2 验证清单

完成本章学习后，请确认您能够：

• 理解各种索引类型的特点
• 选择合适的列创建索引
• 避免索引失效的情况
• 使用EXPLAIN分析查询
• 优化慢查询