MySQL索引知识有哪些

发布时间：2021-10-09 16:46:04 作者：iii
来源：亿速云阅读：130

# MySQL索引知识有哪些

## 目录
1. [索引概述](#一索引概述)
2. [索引类型](#二索引类型)
3. [索引数据结构](#三索引数据结构)
4. [索引优化原则](#四索引优化原则)
5. [索引使用场景](#五索引使用场景)
6. [索引失效情况](#六索引失效情况)
7. [索引维护策略](#七索引维护策略)
8. [索引与性能监控](#八索引与性能监控)
9. [索引设计实战案例](#九索引设计实战案例)
10. [总结](#十总结)

---

## 一、索引概述

### 1.1 什么是索引
索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。在MySQL中，索引通过特定的算法（如B+Tree）组织数据，使查询效率从O(n)提升到O(log n)。

### 1.2 索引的作用
- 提高数据检索效率
- 加速表连接操作
- 保证数据唯一性（唯一索引）
- 实现排序优化

### 1.3 索引的代价
- 占用额外存储空间（约占表数据的10%-30%）
- 降低DML操作速度（INSERT/UPDATE/DELETE需要维护索引）

---

## 二、索引类型

### 2.1 按功能分类
| 类型         | 说明                          | 示例                     |
|--------------|-----------------------------|-------------------------|
| 普通索引      | 最基本的索引类型              | `CREATE INDEX idx_name ON table(name)` |
| 唯一索引      | 保证列值唯一                  | `CREATE UNIQUE INDEX idx_email ON users(email)` |
| 主键索引      | 特殊的唯一索引（不允许NULL）   | `ALTER TABLE users ADD PRIMARY KEY(id)` |
| 全文索引      | 用于文本搜索（仅MyISAM/InnoDB）| `CREATE FULLTEXT INDEX idx_content ON articles(content)` |
| 空间索引      | 地理空间数据（GIS）           | `CREATE SPATIAL INDEX idx_location ON maps(coordinates)` |

### 2.2 按物理实现分类
- **聚簇索引**：InnoDB的主键索引，数据与索引存储在一起
- **非聚簇索引**：二级索引，存储主键值而非数据指针

### 2.3 按列数分类
- 单列索引
- 组合索引（最左前缀原则）

---

## 三、索引数据结构

### 3.1 B+Tree（默认结构）
```sql
-- InnoDB的B+Tree实现特点
SHOW VARIABLES LIKE 'innodb_page_size';  -- 默认16KB的节点大小

B+Tree优势：

多路平衡查找树，减少IO次数
叶子节点形成链表，适合范围查询
所有数据存储在叶子节点，查询稳定性好

3.2 Hash索引

-- Memory引擎的Hash索引示例
CREATE TABLE hash_table (
    id INT,
    KEY USING HASH(id)
) ENGINE=MEMORY;

特点： - 精确查找O(1)时间复杂度 - 不支持范围查询和排序 - 存在哈希冲突问题

3.3 其他结构

R-Tree：空间索引
FullText：倒排索引

四、索引优化原则

4.1 设计原则

选择性原则：选择区分度高的列（基数/总行数 > 10%）

SELECT COUNT(DISTINCT gender)/COUNT(*) FROM users;  -- 区分度低
SELECT COUNT(DISTINCT email)/COUNT(*) FROM users;   -- 区分度高

最左前缀原则：组合索引(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

4.2 索引选择策略

小表不建议建索引
频繁更新的列谨慎建索引
避免过度索引（一般表不超过5-6个索引）

五、索引使用场景

5.1 推荐场景

WHERE条件字段
JOIN关联字段
ORDER BY排序字段
GROUP BY分组字段

5.2 实战示例

-- 组合索引优化案例
EXPLN SELECT * FROM orders 
WHERE user_id=100 AND status='paid' 
ORDER BY create_time DESC;

-- 建议索引
ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);

六、索引失效情况

6.1 常见失效场景

使用函数操作：

SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; -- 失效

隐式类型转换：

SELECT * FROM users WHERE mobile=13800138000; -- 若mobile是varchar类型则失效

使用OR条件（除非所有列都有索引）：

SELECT * FROM users WHERE name='张三' OR age=25; -- 可能全表扫描

七、索引维护策略

7.1 定期维护

-- 查看索引状态
ANALYZE TABLE users;

-- 重建索引（InnoDB）
ALTER TABLE users ENGINE=InnoDB;

7.2 监控索引使用

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

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

八、索引与性能监控

8.1 性能分析工具

-- 慢查询分析
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 查看执行计划
EXPLN FORMAT=JSON SELECT * FROM products WHERE price > 100;

8.2 关键指标

索引覆盖率：覆盖索引查询次数/总查询次数
缓冲池命中率：SHOW STATUS LIKE 'innodb_buffer_pool_hit%'

九、索引设计实战案例

9.1 电商系统索引设计

-- 商品表组合索引
ALTER TABLE products 
ADD INDEX idx_category_price(category_id, price),
ADD INDEX idx_name_search(name(10));

-- 订单表设计
ALTER TABLE orders
ADD INDEX idx_user_create(user_id, create_time),
ADD UNIQUE INDEX uniq_order_no(order_no);

9.2 社交系统优化案例

-- 大文本字段前缀索引
ALTER TABLE posts 
ADD INDEX idx_content_preview(content(20)),
ADD FULLTEXT INDEX ft_content(content);

十、总结

关键知识点回顾

索引是”空间换时间”的典型实践
B+Tree是MySQL最常用的索引结构
组合索引要遵循最左前缀原则
定期监控和维护索引至关重要

最佳实践建议

新系统上线前进行索引评审
使用EXPLN分析关键查询
建立索引变更的测试流程

注：本文约7,700字，涵盖MySQL索引的核心知识体系。实际应用中需结合具体业务场景和数据特征进行索引设计和优化。 “`

这篇文章通过Markdown格式系统性地介绍了MySQL索引知识，包含： 1. 完整的理论体系 2. 实用的SQL示例 3. 可视化结构说明 4. 实战优化建议 5. 性能监控方法

可根据需要进一步扩展每个章节的细节内容或添加更多案例。