MySQL 数据库索引详细教程
一、索引基础概念
1. 什么是索引
索引是数据库中用于提高查询性能的数据结构，类似于书籍的目录。它可以帮助数据库引擎快速定位到表中的特定数据，而不需要扫描整个表。
2. 索引的优点
大大加快数据的检索速度通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性加速表与表之间的连接在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
3. 索引的缺点
创建和维护索引需要耗费时间索引需要占用物理空间当对表中的数据进行增删改时，索引也要动态维护，降低了数据的维护速度
二、MySQL索引类型
1. B-Tree索引（默认索引类型）
-- 创建B-Tree索引
CREATE INDEX idx_name ON table_name(column_name);
-- 多列索引
CREATE INDEX idx_name ON table_name(col1, col2, col3);
2. 唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(column_name);
3. 主键索引
-- 创建表时指定主键
CREATE TABLE table_name (
id INT NOT NULL,
PRIMARY KEY (id)
);
-- 修改表添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
4. 全文索引（FULLTEXT）
-- 创建全文索引
CREATE FULLTEXT INDEX idx_name ON table_name(column_name);
-- 使用全文索引查询
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_term');
5. 空间索引（SPATIAL）
CREATE SPATIAL INDEX idx_name ON table_name(column_name);
6. 哈希索引（MEMORY引擎支持）
CREATE INDEX idx_name USING HASH ON table_name(column_name);
三、索引的创建与管理
1. 创建索引
-- 基本语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column_name [(length)] [ASC|DESC], ...);
-- 示例
CREATE INDEX idx_customer_name ON customers(last_name, first_name);
2. 查看索引
-- 查看表的索引
SHOW INDEX FROM table_name;
-- 或使用
SHOW KEYS FROM table_name;
3. 删除索引
DROP INDEX index_name ON table_name;
4. 修改索引
MySQL不支持直接修改索引，需要先删除再重建。
四、索引设计原则
1. 选择合适的列建立索引
经常作为查询条件的列（WHERE子句）经常用于表连接的列经常需要排序的列（ORDER BY子句）经常需要分组统计的列（GROUP BY子句）
2. 避免过度索引
索引并非越多越好，每个额外的索引都会占用存储空间并降低写操作性能一般建议单表索引不超过5-6个
3. 考虑索引的选择性
选择性高的列更适合建立索引，选择性计算：
选择性 = 不重复的索引值数量 / 表中记录总数
4. 复合索引设计原则
最左前缀原则：索引(a,b,c)可以用于查询条件a、a,b或a,b,c，但不能用于b,c将选择性高的列放在前面将经常用于查询条件的列放在前面将需要排序的列放在后面
五、索引优化技巧
1. 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';
2. 覆盖索引
当查询的列都包含在索引中时，MySQL可以直接从索引获取数据而不需要回表。
-- 假设有索引(last_name, first_name)
SELECT first_name FROM customers WHERE last_name = 'Smith';
3. 索引条件下推(ICP)
MySQL 5.6+支持，将WHERE条件推到存储引擎层进行过滤。
4. 避免索引失效的场景
使用不等于操作（!= 或 <>）使用函数操作索引列（如WHERE YEAR(date_column) = 2023）使用OR连接条件（除非所有OR条件都有索引）使用LIKE以通配符开头（如’%name’）类型转换（如字符串列与数字比较）
六、高级索引策略
1. 前缀索引
对于长字符串列，可以只索引前几个字符。
CREATE INDEX idx_name ON table_name(column_name(10));
2. 使用索引进行排序
-- 如果索引是(a,b), 以下查询可以使用索引排序
SELECT * FROM table_name ORDER BY a, b;
-- 以下查询不能使用索引排序
SELECT * FROM table_name ORDER BY b;
3. 冗余和重复索引
避免创建功能相同的索引：
-- 已有索引(a,b), 再创建索引(a)就是冗余的
-- 已有主键id, 再创建唯一索引id就是重复的
4. 索引合并
MySQL有时会使用多个索引并将结果合并：
-- 假设有索引a和b
SELECT * FROM table_name WHERE a = 1 OR b = 2;
七、实践案例
案例1：电商平台用户表
CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (user_id),
UNIQUE KEY idx_username (username),
UNIQUE KEY idx_email (email),
INDEX idx_phone (phone),
INDEX idx_created (created_at)
);
案例2：订单查询优化
-- 原始查询
SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped' ORDER BY order_date DESC;
-- 优化方案
CREATE INDEX idx_customer_status_date ON orders(customer_id, status, order_date DESC);
八、常见问题解答
Q1: 为什么我的索引没有生效？
可能原因：
查询条件不符合最左前缀原则使用了导致索引失效的操作符或函数表数据量太小，优化器认为全表扫描更快索引列的数据分布不均匀
Q2: 如何知道应该为哪些列创建索引？
使用慢查询日志找出执行慢的查询使用EXPLAIN分析查询执行计划关注频繁出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的列
Q3: 索引对INSERT、UPDATE、DELETE操作有什么影响？
INSERT：需要同时更新索引，降低性能UPDATE：如果更新了索引列，需要更新索引DELETE：需要从索引中删除相应条目
九、总结
合理设计和使用索引是MySQL性能优化的关键。需要根据实际查询模式设计索引，定期审查和优化索引策略，避免过度索引和索引不足两个极端。通过EXPLAIN分析查询执行计划，结合业务特点不断调整索引策略，才能获得最佳的数据库性能。