第一章：MySQL 索引基础

第一章：MySQL 索引基础

1. 索引是什么？

• ​​定义​​：索引（Index）是数据库中用于快速查找数据的一种数据结构，类似于书籍的目录。
• ​​核心作用​​：通过减少磁盘I/O次数，加速查询速度（尤其是WHERE、JOIN、ORDER BY等操作）。
• ​​代价​​：
  • ​​存储空间​​：索引需要额外的磁盘或内存空间。
  • ​​维护成本​​：增删改操作（INSERT/UPDATE/DELETE）需要同步更新索引，可能降低写性能。

---

2. 为什么需要索引？

没有索引时的问题

• ​​全表扫描​​（Full Table Scan）：查询时必须逐行检查数据，时间复杂度为O(n)，数据量大时性能极差。

索引的优化场景

• 快速定位符合条件的行。
• 加速表连接（JOIN）操作。
• 避免排序操作（如果索引本身有序）。

---

3. MySQL常见索引类型

3.1 按数据结构分类

索引类型	数据结构	支持引擎	适用场景
​​B-Tree​​	平衡多路搜索树	InnoDB、MyISAM	范围查询、精确查询、排序
​​Hash​​	哈希表	Memory/Heap	等值查询（仅精确匹配）
​​Full-Text​​	倒排索引	InnoDB、MyISAM	文本内容的模糊搜索
​​R-Tree​​	空间索引	MyISAM	地理空间数据查询

B-Tree索引的特点

• ​​默认索引类型​​：InnoDB实际使用B+Tree（B-Tree的变种）。
• ​​有序性​​：索引按顺序存储，适合范围查询（如WHERE age BETWEEN 20 AND 30）。

Hash索引的局限性

• ​​仅支持等值查询​​（=、IN()），无法用于范围查询或排序。
• ​​不保证顺序​​：数据存储无序。

---

3.2 按功能逻辑分类

索引类型	描述
​​主键索引​​	唯一且非空，一张表只能有一个（PRIMARY KEY）
​​唯一索引​​	列值唯一，允许NULL（UNIQUE KEY）
​​普通索引​​	无唯一性限制（INDEX或KEY）
​​组合索引​​	多列联合组成的索引

组合索引（复合索引）

• ​​示例​​：INDEX idx_name_age (name, age)
• ​​最左前缀原则​​：查询条件必须包含组合索引的左侧列才能生效。
  例如：
  • ✅ 有效：WHERE name = 'Alice' AND age = 25
  • ✅ 有效：WHERE name = 'Bob'
  • ❌ 无效：WHERE age = 30（未使用最左列name）

---

4. 索引的创建与管理

4.1 创建索引

语法

-- 创建普通索引
CREATE INDEX idx_name ON table_name (column1, column2);-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users (email);-- 修改表结构添加索引
ALTER TABLE orders ADD INDEX idx_order_date (order_date);

创建主键索引

ALTER TABLE employees ADD PRIMARY KEY (id);

4.2 删除索引

DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

---

5. 索引使用注意事项

1. ​​不要过度索引​​：

   • 索引会占用存储空间，并增加写操作开销。
   • 建议只为频繁查询的列或WHERE/JOIN条件中的列创建索引。

2. ​​选择高选择性列​​：

   • 选择性高的列（如唯一值多的列，如用户ID）更适合作为索引。

3. ​​避免重复索引​​：

   • 例如已存在INDEX (a, b)，再创建INDEX (a)是冗余的。

4. ​​使用EXPLAIN分析查询​​：

   EXPLAIN SELECT * FROM users WHERE name = 'Alice';

   • 检查是否命中索引（key字段显示使用的索引）。

---

6. 索引的优缺点总结

优点	缺点
加速查询速度	占用额外存储空间
减少服务器扫描的数据量	增删改操作需维护索引，降低写性能
帮助避免排序和临时表	不合理的索引可能导致查询更慢