索引是数据库中用于提高查询效率的数据结构，类似于书籍的目录。它通过预先排序和存储数据的引用位置，使数据库系统能够快速定位到所需数据，而无需扫描整个表。合理使用索引可以显著提升查询性能，但过多或不当的索引会影响写入操作的效率。
一、索引的基本概念
1. 索引的作用
- 加速查询速度，减少数据扫描范围
- 优化排序和分组操作
- 强制数据唯一性（如主键索引）
2. 索引的代价
- 占用额外存储空间
- 降低写入操作（INSERT、UPDATE、DELETE）的性能，因为索引需要同步更新
- 增加维护成本，索引需要定期优化
3. 索引的工作原理
MySQL索引主要基于B+树数据结构实现，其特点是：
- 所有数据都存储在叶子节点，形成有序链表
- 非叶子节点只存储索引关键字和指针，用于快速定位
- 支持范围查询和顺序访问
二、索引的类型
1. 按功能划分
（1）主键索引（PRIMARY KEY）
特点：
- 唯一标识表中的每条记录，不允许NULL值
- 一个表只能有一个主键索引
- 自动创建，通常与自增字段配合使用
示例：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
username VARCHAR(50)
);
（2）唯一索引（UNIQUE）
特点：
- 确保索引列的值唯一，但允许NULL值（多个NULL值视为不同）
- 一个表可以有多个唯一索引
示例：
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE, -- 唯一索引
phone VARCHAR(20) UNIQUE -- 另一个唯一索引
);
（3）普通索引（INDEX）
特点：
- 最基本的索引类型，没有唯一性限制
- 主要用于加速查询
示例：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
category_id INT,
INDEX idx_category (category_id) -- 普通索引
);
（4）全文索引（FULLTEXT）
特点：
- 用于全文搜索，适用于大文本字段（如TEXT、VARCHAR）
- InnoDB和MyISAM引擎均支持（MySQL 5.6+）
- 支持中文全文索引（需特定配置）
示例：
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX idx_article_content (title, content) -- 全文索引
);
2. 按结构划分
（1）单列索引
只包含单个字段的索引，适用于在单个字段上进行查询的场景。
示例：
CREATE INDEX idx_username ON users(username);
（2）复合索引（多列索引）
包含多个字段的索引，遵循"最左前缀原则"，适用于多字段联合查询。
示例：
-- 对name和price字段创建复合索引 CREATE INDEX idx_name_price ON products(name, price);
最左前缀原则：上述索引可用于WHERE条件包含name，或name+price的查询，但不能单独用于price的查询。
（3）空间索引（SPATIAL）
用于地理空间数据类型（如GEOMETRY）的索引，支持空间位置查询。
示例：
CREATE TABLE locations (
id INT PRIMARY KEY,
place_name VARCHAR(100),
coordinates GEOMETRY,
SPATIAL INDEX idx_coordinates (coordinates)
);
三、索引的创建与删除
1. 创建索引
（1）创建表时定义索引
CREATE TABLE 表名 (
字段1 数据类型,
字段2 数据类型,
...,
PRIMARY KEY (字段),
UNIQUE (字段),
INDEX 索引名 (字段),
FULLTEXT 索引名 (字段)
);
（2）为已有表添加索引
-- 添加普通索引 CREATE INDEX 索引名 ON 表名(字段); -- 添加唯一索引 CREATE UNIQUE INDEX 索引名 ON 表名(字段); -- 添加复合索引 CREATE INDEX 索引名 ON 表名(字段1, 字段2, ...);
2. 删除索引
DROP INDEX 索引名 ON 表名;
3. 查看表中的索引
-- 方法1 SHOW INDEX FROM 表名; -- 方法2 DESCRIBE 表名; -- 可查看主键和唯一索引
四、索引的使用场景
以下情况适合创建索引：
1. 经常出现在WHERE子句中的字段
-- 对经常用于查询条件的字段创建索引 CREATE INDEX idx_status ON orders(status);
2. 经常用于JOIN操作的字段（通常是外键）
-- 对关联查询的外键创建索引 CREATE INDEX idx_dept_id ON employees(dept_id);
3. 经常用于排序（ORDER BY）或分组（GROUP BY）的字段
-- 对排序字段创建索引 CREATE INDEX idx_create_time ON logs(create_time);
4. 唯一性高的字段（如身份证号、邮箱）适合创建唯一索引
以下情况不适合创建索引：
1. 数据量少的表（全表扫描速度可能更快）
2. 频繁更新或删除的字段（索引维护成本高）
3. 重复值多的字段（如性别、状态等，索引效果差）
4. 很少用于查询条件的字段
五、索引失效的情况
即使创建了索引，在某些情况下索引也不会被使用（索引失效）：
1. 使用函数或表达式操作索引字段
-- 索引字段被函数处理，索引失效 SELECT * FROM users WHERE YEAR(birthday) = 1990; -- 优化：改为索引字段本身参与比较 SELECT * FROM users WHERE birthday >= '1990-01-01' AND birthday < '1991-01-01';
2. 使用不等于（!=、<>）、NOT IN、NOT EXISTS等操作符
-- 可能导致索引失效 SELECT * FROM products WHERE price != 100;
3. 使用LIKE以通配符开头（%）
-- 索引失效 SELECT * FROM users WHERE username LIKE '%john'; -- 索引有效（通配符在末尾） SELECT * FROM users WHERE username LIKE 'john%';
4. 复合索引不满足最左前缀原则
-- 复合索引为(name, price) -- 索引失效（未使用最左列name） SELECT * FROM products WHERE price > 100; -- 索引有效（使用了最左列name） SELECT * FROM products WHERE name = '手机' AND price > 100;
5. 隐式类型转换
-- 字段类型为VARCHAR，查询时使用数字，导致类型转换，索引失效 SELECT * FROM users WHERE phone = 13800138000; -- 优化：使用字符串匹配 SELECT * FROM users WHERE phone = '13800138000';
6. 使用OR连接包含非索引字段的条件
-- 字段age有索引，字段address无索引，OR导致索引失效 SELECT * FROM users WHERE age > 30 OR address = '北京';
六、索引的优化与维护
1. 分析索引使用情况
-- 开启慢查询日志，记录未使用索引的慢查询 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录 -- 查看索引使用统计 SHOW STATUS LIKE 'Handler_read%'; -- Handler_read_key: 索引被使用的次数（越高越好） -- Handler_read_rnd_next: 全表扫描的次数（越低越好）
2. 优化索引结构
- 定期删除无用索引（未被使用或重复的索引）
- 将频繁联合查询的字段创建复合索引，而非多个单列索引
- 对于大表，考虑使用前缀索引（只对字段的前N个字符创建索引）
-- 创建前缀索引（对username的前10个字符创建索引） CREATE INDEX idx_username_prefix ON users(username(10));
3. 重建和优化索引
-- 优化表（会重建索引，释放碎片空间） OPTIMIZE TABLE 表名; -- 重建索引（InnoDB引擎） ALTER TABLE 表名 ENGINE = InnoDB;
七、索引的最佳实践
1. 遵循"按需创建"原则，避免过度索引
2. 对于读写比例高的表，可适当多创建索引；对于写操作频繁的表，应减少索引
3. 复合索引的字段顺序应按查询频率和区分度排列（最常用、区分度最高的放前面）
4. 使用EXPLAIN分析查询计划，验证索引是否被有效使用
-- 分析查询是否使用索引 EXPLAIN SELECT * FROM users WHERE username = 'john';
5. 主键尽量使用自增整数，避免使用UUID等无序值（会导致索引分裂）
6. 对于大型表，考虑分区表结合索引使用，提高查询效率