MySQL索引优化指南：B-Tree、哈希索引与性能提升
MySQL索引与优化：从原理到实践
一、索引类型详解
1. B-Tree索引（最常用）
B-Tree（平衡树）索引是MySQL中最常见的索引类型，InnoDB引擎实际使用的是B+Tree结构。
特点：
- 所有叶子节点在同一层
- 非叶子节点存储键值和指针
- 叶子节点包含所有索引列数据和指向数据的指针
适用场景：
- 全值匹配（=）
- 范围查询（>, <, BETWEEN）
- 前缀匹配（LIKE 'abc%'）
实践建议：
-- 创建B-Tree索引
CREATE INDEX idx_name ON users(last_name, first_name);
-- 适合的查询
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM users WHERE last_name BETWEEN 'A' AND 'M';2. 哈希索引
基于哈希表实现，只有Memory引擎显式支持，InnoDB有自适应哈希索引功能。
特点：
- 精确匹配极快（O(1)复杂度）
- 不支持范围查询
- 不支持排序
- 存在哈希冲突
实践建议：
-- Memory引擎创建哈希索引
CREATE TABLE hash_table (
id INT,
name VARCHAR(100),
INDEX USING HASH (id)
) ENGINE=MEMORY;3. 全文索引
用于文本内容的搜索，支持自然语言和布尔搜索模式。
-- 创建全文索引
CREATE FULLTEXT INDEX ft_idx ON articles(content);
-- 使用全文搜索
SELECT * FROM articles
WHERE MATCH(content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);实践建议：
- 适用于大文本字段搜索
- 5.6+版本InnoDB支持全文索引
- 注意最小词长配置（ft_min_word_len）
4. 空间索引（R-Tree）
用于地理空间数据，遵循OpenGIS标准。
CREATE TABLE spatial_table (
id INT PRIMARY KEY,
location GEOMETRY NOT NULL,
SPATIAL INDEX(location)
);
-- 空间查询
SELECT * FROM spatial_table
WHERE ST_Contains(location, POINT(10,20));5. 组合索引（最左前缀原则）
多个列组合创建的索引，遵循最左前缀匹配原则。
CREATE INDEX idx_name_age ON employees(last_name, first_name, age);匹配情况分析：
- ✅
WHERE last_name='Smith' - ✅
WHERE last_name='Smith' AND first_name='John' - ✅
WHERE last_name='Smith' AND first_name='John' AND age=30 - ❌
WHERE first_name='John'(不匹配最左前缀) - ❌
WHERE age=30(不匹配最左前缀)
二、索引优化策略
1. EXPLAIN执行计划分析
EXPLAIN SELECT * FROM users WHERE last_name = 'Smith'\G关键字段解读：
- type：从最好到最差依次为 system > const > eq_ref > ref > range > index > ALL
- key：实际使用的索引
- rows：预估需要检查的行数
- Extra：额外信息（Using index/Using filesort/Using temporary等）
2. 索引选择性
选择性 = 不重复的索引值数量 / 表记录总数
高选择性列更适合建索引：
-- 计算选择性
SELECT
COUNT(DISTINCT last_name)/COUNT(*) AS selectivity
FROM users;实践建议：
- 选择性>0.1的列适合建索引
- 性别这种低选择性列不适合单独建索引
3. 覆盖索引
索引包含查询所需的所有字段，无需回表。
-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(user_id, order_date, amount);
-- 使用覆盖索引的查询
SELECT user_id, order_date, amount FROM orders
WHERE user_id = 100 AND order_date > '2023-01-01';4. 索引下推（ICP，5.6+）
存储引擎层过滤数据，减少回表次数。
-- 假设有索引 (a, b)
SELECT * FROM table WHERE a = 'xxx' AND b LIKE '%yyy%';优化效果：
- 5.6前：先通过a='xxx'定位记录，再回表检查b条件
- 5.6+：在索引层同时检查a和b条件，减少回表
三、查询优化实战
1. 慢查询日志分析
配置：
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1分析工具：
mysqldumpslow -s t /var/log/mysql/mysql-slow.log
pt-query-digest /var/log/mysql/mysql-slow.log2. 查询缓存（注意：8.0已移除）
-- 查看查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';
-- 查询缓存命中率
SHOW STATUS LIKE 'Qcache%';实践建议：
- 写频繁的系统建议关闭查询缓存
- 5.7版本可设置query_cache_type=DEMAND按需使用
3. 优化器提示（HINT）
-- 强制使用某个索引
SELECT * FROM users FORCE INDEX(idx_last_name) WHERE last_name = 'Smith';
-- 忽略索引
SELECT * FROM users IGNORE INDEX(idx_age) WHERE age > 30;
-- 关闭ICP
SELECT * FROM users WHERE last_name = 'Smith' AND first_name LIKE 'J%'
OPTION (MAX_EXECUTION_TIME=1000, NO_ICP);四、综合优化案例
问题查询：
SELECT u.user_id, u.user_name, o.order_count
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.register_time > '2023-01-01'
ORDER BY o.order_count DESC
LIMIT 100;优化步骤：
检查索引：
-- 确保users表有register_time索引 CREATE INDEX idx_register ON users(register_time); -- 确保orders表有user_id和order_count的复合索引 CREATE INDEX idx_user_orders ON orders(user_id, order_count);改写查询：
SELECT u.user_id, u.user_name, o.order_count FROM ( SELECT user_id FROM users WHERE register_time > '2023-01-01' LIMIT 100 ) AS u_ids JOIN users u ON u_ids.user_id = u.user_id JOIN orders o ON u.user_id = o.user_id ORDER BY o.order_count DESC LIMIT 100;使用STRAIGHT_JOIN优化连接顺序：
SELECT STRAIGHT_JOIN u.user_id, u.user_name, o.order_count FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.register_time > '2023-01-01' ORDER BY o.order_count DESC LIMIT 100;
五、最佳实践总结
索引设计原则：
- 为WHERE、JOIN、ORDER BY子句中的列建索引
- 遵循最左前缀原则设计组合索引
- 避免过度索引，每个索引都有维护成本
查询编写建议：
- 避免SELECT *，只查询需要的列
- 小心使用LIKE '%前缀'模糊查询
- 注意IN和OR可能导致索引失效
- 避免在索引列上使用函数
监控与调优：
- 定期分析慢查询日志
- 使用Performance Schema监控性能
- 关注索引统计信息的准确性
通过合理使用索引和优化查询，可以显著提升MySQL数据库性能。建议在实际应用中结合EXPLAIN分析工具，持续监控和优化数据库性能。