数据库优化|查询加速：mysql的索引有哪些；mysql中的索引类型详解

数据库优化 | 查询加速：MySQL索引全攻略

场景引入：慢查询的烦恼

"小王啊，这个用户订单页面怎么加载这么慢啊？都等了快5秒了！"产品经理老张皱着眉头走过来。

小王擦了擦额头的汗："我看看...噢，这个查询没有走索引，全表扫描了200万条记录..."

这样的场景在开发中太常见了，数据库查询慢得像蜗牛爬，用户体验直线下降，只要用好MySQL的索引，很多性能问题都能迎刃而解，今天我们就来深入聊聊MySQL中的各种索引类型,让你的查询飞起来！

MySQL索引基础认知

1 什么是索引？

简单说，索引就像是书本的目录，没有目录的书，你要找某个内容只能一页页翻；有了目录，你就能快速定位到具体章节，数据库索引也是同样的道理，它能帮助数据库引擎快速找到数据,而不必扫描整个表。

2 索引的代价

不过天下没有免费的午餐，索引虽然加速查询,但也有代价：

• 占用额外的存储空间
• 降低写入速度（每次INSERT/UPDATE/DELETE都需要更新索引）
• 维护索引需要成本

所以不是索引越多越好,要找到平衡点。

MySQL索引类型详解

1 B-Tree索引（最常用）

特点：

• 默认的索引类型
• 适合全值匹配、范围查询和排序
• 支持最左前缀原则

适用场景：

-- 创建B-Tree索引
CREATE INDEX idx_name ON users(username);
-- 等值查询
SELECT * FROM users WHERE username = '张三';
-- 范围查询
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
-- 排序
SELECT * FROM orders ORDER BY create_time DESC;

注意事项：

• 索引列的顺序很重要，遵循最左前缀原则
• 对于长字符串，考虑前缀索引：CREATE INDEX idx_name ON users(username(10))

2 哈希索引（Memory引擎默认）

特点：

• 基于哈希表实现
• 只能用于等值比较（=, <=>）
• 查询速度极快（O(1)时间复杂度）

适用场景：

-- 创建哈希索引（Memory引擎）
CREATE TABLE cache_table (
    id INT PRIMARY KEY,
    data VARCHAR(100),
    INDEX USING HASH (data)
) ENGINE=MEMORY;
-- 等值查询
SELECT * FROM cache_table WHERE data = '特定值';

限制：

• 不支持范围查询
• 不支持排序
• 不支持部分索引匹配
• 只存在于Memory引擎，InnoDB有自适应哈希索引，但用户不能直接创建

3 全文索引（FULLTEXT）

特点：

• 专门用于文本搜索
• 支持自然语言搜索和布尔搜索
• 适用于大文本字段

适用场景：

-- 创建全文索引
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)
) ENGINE=InnoDB;
-- 自然语言搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库优化');
-- 布尔搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

注意事项：

• 默认最小词长度为4（可通过ft_min_word_len配置）
• 停用词（如"的"、"和"）不会被索引
• 适合大文本，小文本效果不明显

4 空间索引（R-Tree）

特点：

• 用于地理空间数据
• 支持GIS地理信息查询
• 只有MyISAM和InnoDB（MySQL 5.7+）支持

适用场景：

-- 创建空间索引
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL,
    SPATIAL INDEX(position)
) ENGINE=InnoDB;
-- 空间查询
SET @point = ST_GeomFromText('POINT(116.404 39.915)');
SELECT name FROM locations 
WHERE ST_Distance_Sphere(position, @point) < 1000; -- 1公里内的地点

5 组合索引（最强大）

特点：

• 多个列组成的索引
• 遵循最左前缀原则
• 可以覆盖查询，避免回表

适用场景：

-- 创建组合索引
CREATE INDEX idx_name_age_gender ON users(name, age, gender);
-- 有效使用索引的查询
SELECT * FROM users WHERE name = '张三' AND age = 30; -- 使用索引
SELECT * FROM users WHERE name LIKE '张%' ORDER BY age; -- 使用索引
SELECT age, gender FROM users WHERE name = '李四'; -- 覆盖索引，性能最佳
-- 无法使用索引的情况
SELECT * FROM users WHERE age = 25; -- 不满足最左前缀
SELECT * FROM users WHERE name = '王五' ORDER BY gender; -- gender不是索引下一列

设计技巧：

1. 将选择性高的列放在前面
2. 经常用于排序或分组的列放在后面
3. 考虑覆盖索引，减少回表操作

特殊索引类型

1 主键索引（PRIMARY KEY）

• 特殊的唯一索引
• 不允许NULL值
• InnoDB的表存储就基于主键组织

2 唯一索引（UNIQUE KEY）

• 确保列值唯一
• 允许NULL值（但NULL也算作一个唯一值）
• 性能比普通索引略好

3 前缀索引

对长字符串列的前N个字符建立索引：

CREATE INDEX idx_email_prefix ON users(email(10));

4 降序索引（MySQL 8.0+）

允许指定索引的排序方向：

CREATE INDEX idx_age_desc ON users(age DESC);

索引使用最佳实践

1. 不要过度索引：一般表不超过5-6个索引

   

2. 选择区分度高的列：如用户ID比性别更适合建索引

3. 避免对频繁更新的列建索引：会导致写入性能下降

4. 注意索引失效场景：

   • 使用函数操作：WHERE YEAR(create_time) = 2025
   • 隐式类型转换：WHERE user_id = '123'（user_id是INT）
   • 使用或NOT IN
   • 前导通配符：WHERE name LIKE '%张'

5. 定期分析索引使用情况：

   -- 查看索引使用情况
   SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计信息 SHOW INDEX FROM users;

## 五、真实案例：电商系统索引优化
**问题场景**：
一个订单查询接口变慢，SQL如下：
```sql
SELECT * FROM orders 
WHERE user_id = 10086 
AND status = 'PAID'
ORDER BY create_time DESC
LIMIT 10;

优化过程：

1. 先检查表结构和现有索引：

   SHOW CREATE TABLE orders;
   -- 发现只有主键id索引和单独的user_id索引

2. 创建更适合的组合索引：

   CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time DESC);

3. 优化后效果：

• 查询时间从1200ms降到15ms
• 避免了filesort和临时表
• 扫描行数从10万降到10行

索引是MySQL性能优化的利器，但也是一把双刃剑，理解不同索引类型的特点和适用场景，结合实际业务需求合理设计索引，才能让数据库查询既快又稳，最好的索引策略来自于对业务的理解和对数据的分析,而不是盲目添加索引。

下次当你遇到慢查询时，不妨先问问："这个查询能用上合适的索引吗？" 也许答案就在这里。