【MySQL全面教学】MySQL索引原理与优化Day8（2026年）

写在前面

欢迎来到MySQL系列教学第8天！经过前几天的学习，我们已经掌握了MySQL的基础操作和查询技巧。今天，我们将深入MySQL最核心的优化手段——索引。索引是数据库性能优化的基石，理解索引原理对于每一位开发者都至关重要。

无论你是刚入门的初学者，还是准备面试的求职者，这篇文章都将帮助你全面掌握MySQL索引的核心知识。

一、为什么需要索引

1.1 全表扫描的性能灾难

想象一下，你有一本1000页的书，没有目录，要找到某一章的内容，你只能一页一页地翻。这就是全表扫描——数据库需要逐行检查每一行数据来找到匹配的记录。

-- 假设users表有100万条数据，没有索引SELECT*FROMusersWHEREphone='13800138000';

在没有索引的情况下，MySQL需要扫描全部100万条记录，时间复杂度为O(n)。

1.2 索引扫描的性能飞跃

如果给phone字段加上索引，查询过程就变成了：

1. 在索引结构中快速定位到目标值
2. 根据索引中的指针找到对应的数据行

时间复杂度降为O(log n)，对于100万条数据，只需要约20次磁盘IO即可定位。

1.3 性能对比实验

-- 创建测试表CREATETABLEtest_index(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50),phoneVARCHAR(20),emailVARCHAR(100),created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 插入100万条测试数据DELIMITER$$CREATEPROCEDUREinsert_test_data()BEGINDECLAREiINTDEFAULT1;WHILEi<=1000000DOINSERTINTOtest_index(name,phone,email)VALUES(CONCAT('user',i),CONCAT('138',LPAD(i,8,'0')),CONCAT('user',i,'@example.com'));SETi=i+1;ENDWHILE;END$$DELIMITER;CALLinsert_test_data();-- 无索引查询（约1-2秒）SELECT*FROMtest_indexWHEREphone='13800123456';-- 创建索引CREATEINDEXidx_phoneONtest_index(phone);-- 有索引查询（约0.001秒）SELECT*FROMtest_indexWHEREphone='13800123456';

结论：随着数据量增长，索引带来的性能提升呈指数级增长。

二、索引的数据结构

2.1 为什么不用哈希表？

哈希表查询时间复杂度为O(1)，看似很完美，但为什么不作为索引的数据结构呢？

哈希索引的缺点：

1. 不支持范围查询：WHERE age > 18无法使用哈希索引
2. 不支持排序：ORDER BY无法利用哈希索引
3. 不支持最左前缀匹配：组合索引无法部分匹配
4. 哈希冲突：需要处理冲突，增加复杂度

-- 只有Memory引擎支持哈希索引CREATETABLEhash_test(idINTPRIMARYKEY,nameVARCHAR(50))ENGINE=MEMORY;-- InnoDB的自适应哈希索引是内部优化，不可控

2.2 为什么不用二叉搜索树？

二叉搜索树（BST）查询时间复杂度为O(log n)，但存在致命问题：

1. 树高度过高：1000万条数据，树高约24层，需要24次磁盘IO
2. 可能退化成链表：极端情况下时间复杂度变为O(n)

2.3 为什么用B+树？

B树的特点：

• 多路平衡搜索树，每个节点可以有多个子节点
• 所有叶子节点在同一层，保证查询效率稳定

B+树的优化（InnoDB使用）：

1. 数据只存储在叶子节点：非叶子节点只存储键值，可以存储更多索引项
2. 叶子节点形成有序链表：便于范围查询和排序
3. 更低的树高度：假设每个节点存储1000个键，3层B+树可存储10亿条记录

B+树结构示意（简化）： [10 | 20 | 30] / | \ [5|8] [15|18] [25|28] / | | \ | \ 1-5 6-8 11-15 16-18 21-25 26-28 叶子节点通过指针连接：1-5 → 6-8 → 11-15 → ...

2.4 B树 vs B+树对比

三、索引类型详解

3.1 主键索引（Primary Key）

-- 创建表时指定主键CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL);-- 或单独添加ALTERTABLEusersADDPRIMARYKEY(id);

特点：

• 唯一标识每行记录
• 自动创建，不能为NULL
• InnoDB中，主键索引就是聚簇索引，数据存储在叶子节点

3.2 唯一索引（Unique Index）

-- 创建唯一索引CREATEUNIQUEINDEXidx_emailONusers(email);-- 或在建表时指定CREATETABLEusers(idINTPRIMARYKEY,emailVARCHAR(100)UNIQUE);

特点：

• 保证列值的唯一性
• 允许NULL值（但只能有一个NULL）
• 查询性能与普通索引相同

3.3 普通索引（Normal Index）

-- 创建普通索引CREATEINDEXidx_usernameONusers(username);-- 多列索引CREATEINDEXidx_name_ageONusers(name,age);

3.4 组合索引（Composite Index）

CREATEINDEXidx_name_age_cityONusers(name,age,city);

最左前缀原则：

• 查询条件必须从索引的最左列开始
• 可以只使用前缀部分，但不能跳过中间列

-- 可以使用索引SELECT*FROMusersWHEREname='张三';SELECT*FROMusersWHEREname='张三'ANDage=20;SELECT*FROMusersWHEREname='张三'ANDage=20ANDcity='北京';-- 不能使用索引（缺少最左列name）SELECT*FROMusersWHEREage=20;SELECT*FROMusersWHEREcity='北京';-- 部分使用索引（只用到了name）SELECT*FROMusersWHEREname='张三'ANDcity='北京';

3.5 全文索引（Fulltext Index）

-- 创建全文索引CREATEFULLTEXTINDEXidx_contentONarticles(content);-- 使用全文索引SELECT*FROMarticlesWHEREMATCH(content)AGAINST('MySQL索引'INNATURALLANGUAGEMODE);

注意：

• 仅支持CHAR、VARCHAR、TEXT类型
• 只有MyISAM和InnoDB（5.6+）支持
• 中文分词需要额外配置

3.6 索引类型总结表

四、索引的创建与删除

4.1 CREATE INDEX语法

-- 基本语法CREATE[UNIQUE|FULLTEXT]INDEXindex_nameONtable_name(column1[ASC|DESC],column2[ASC|DESC],...);-- 示例CREATEINDEXidx_nameONemployees(name);CREATEUNIQUEINDEXidx_emailONemployees(email);CREATEINDEXidx_name_ageONemployees(nameASC,ageDESC);

4.2 ALTER TABLE语法

-- 添加索引ALTERTABLEtable_nameADDINDEXindex_name(column_list);ALTERTABLEtable_nameADDUNIQUEindex_name(column_list);ALTERTABLEtable_nameADDPRIMARYKEY(column_list);ALTERTABLEtable_nameADDFULLTEXT index_name(column_list);-- 示例ALTERTABLEemployeesADDINDEXidx_department(department_id);ALTERTABLEemployeesADDPRIMARYKEY(id);

4.3 删除索引

-- 删除普通/唯一/全文索引DROPINDEXindex_nameONtable_name;-- 或ALTERTABLEtable_nameDROPINDEXindex_name;-- 删除主键索引ALTERTABLEtable_nameDROPPRIMARYKEY;

4.4 查看索引

-- 查看表的索引信息SHOWINDEXFROMtable_name;-- 或SHOWKEYSFROMtable_name;

五、组合索引的最左前缀原则（重点！）

5.1 什么是最左前缀原则

对于组合索引(a, b, c)，查询条件必须从最左边的列开始，才能使用索引。

-- 创建组合索引CREATEINDEXidx_abcONtable_name(a,b,c);-- 有效（使用全部索引列）WHEREa=1ANDb=2ANDc=3WHEREa=1ANDb=2WHEREa=1-- 无效（缺少最左列a）WHEREb=2ANDc=3WHEREc=3-- 部分有效（只使用a）WHEREa=1ANDc=3

5.2 最左前缀的底层原理

B+树按照索引列的顺序排序存储：

1. 先按第一列排序
2. 第一列相同，按第二列排序
3. 以此类推

因此，缺少最左列就无法在B+树中定位。

5.3 组合索引设计技巧

原则：将区分度高的、查询频繁的列放在前面

-- 不好的设计（性别区分度低）CREATEINDEXidx_gender_ageONusers(gender,age);-- 好的设计（年龄区分度高）CREATEINDEXidx_age_genderONusers(age,gender);-- 更好的设计（考虑查询频率）-- 如果经常按年龄查询，偶尔按性别过滤CREATEINDEXidx_ageONusers(age);

5.4 索引下推（Index Condition Pushdown）

MySQL 5.6+引入的优化，可以在索引遍历过程中过滤不满足条件的记录。

-- 有索引(name, age)SELECT*FROMusersWHEREnameLIKE'张%'ANDage=20;-- 没有索引下推：先找到所有name以'张'开头的记录，再回表过滤age-- 有索引下推：在索引中就过滤掉age!=20的记录，减少回表次数

六、索引失效场景

6.1 OR条件导致索引失效

-- 假设有索引idx_ageSELECT*FROMusersWHEREage=20ORname='张三';-- name没有索引，会导致全表扫描-- 解决方案：给name也加上索引，或改写为UNIONSELECT*FROMusersWHEREage=20UNIONALLSELECT*FROMusersWHEREname='张三';

6.2 LIKE以通配符开头

-- 索引失效SELECT*FROMusersWHEREnameLIKE'%三%';SELECT*FROMusersWHEREnameLIKE'%张';-- 索引有效SELECT*FROMusersWHEREnameLIKE'张%';

6.3 对索引列进行函数操作

-- 索引失效SELECT*FROMusersWHEREYEAR(created_at)=2024;SELECT*FROMusersWHEREUPPER(name)='ZHANGSAN';-- 改写为范围查询SELECT*FROMusersWHEREcreated_at>='2024-01-01'ANDcreated_at<'2025-01-01';

6.4 隐式类型转换

-- 假设phone是VARCHAR类型-- 索引失效（数字与字符串比较，发生类型转换）SELECT*FROMusersWHEREphone=13800138000;-- 索引有效SELECT*FROMusersWHEREphone='13800138000';

6.5 其他索引失效场景

-- 1. 使用!=或<>SELECT*FROMusersWHEREage!=20;-- 2. 使用IS NOT NULL（IS NULL可以用索引）SELECT*FROMusersWHEREnameISNOTNULL;-- 3. 索引列参与计算SELECT*FROMusersWHEREage+1=21;-- 4. 字符串不加引号SELECT*FROMusersWHEREname=123;-- name是VARCHAR-- 5. 全表扫描比索引更快时（数据量小或查询大部分数据）SELECT*FROMusersWHEREage>0;-- 几乎所有数据都满足

七、EXPLAIN分析执行计划

7.1 EXPLAIN基本使用

EXPLAINSELECT*FROMusersWHEREid=1;

7.2 关键字段解读

type字段（访问类型，从好到差）

key字段

实际使用的索引名称。NULL表示没有使用索引。

rows字段

估计需要扫描的行数。越小越好。

Extra字段

7.3 EXPLAIN示例分析

-- 示例1：好的执行计划EXPLAINSELECT*FROMusersWHEREid=1;-- type: const, key: PRIMARY, rows: 1, Extra: NULL-- 示例2：索引范围查询EXPLAINSELECT*FROMusersWHEREageBETWEEN20AND30;-- type: range, key: idx_age, rows: 1000, Extra: Using index condition-- 示例3：需要优化EXPLAINSELECT*FROMusersWHEREYEAR(created_at)=2024ORDERBYname;-- type: ALL, key: NULL, rows: 100000, Extra: Using where; Using filesort

八、实战：为电商系统表设计索引

8.1 用户表(users)

CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100)UNIQUE,phoneVARCHAR(20)UNIQUE,statusTINYINTDEFAULT1,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 索引设计CREATEUNIQUEINDEXidx_usernameONusers(username);-- 主键和UNIQUE自动创建索引

8.2 商品表(products)

CREATETABLEproducts(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(200)NOTNULL,category_idINTNOTNULL,priceDECIMAL(10,2)NOTNULL,stockINTDEFAULT0,statusTINYINTDEFAULT1,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 索引设计CREATEINDEXidx_category_statusONproducts(category_id,status);CREATEINDEXidx_priceONproducts(price);-- 商品名称搜索用全文索引或Elasticsearch

8.3 订单表(orders)

CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(32)UNIQUENOTNULL,user_idBIGINTNOTNULL,statusTINYINTNOTNULL,total_amountDECIMAL(12,2)NOTNULL,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 索引设计CREATEUNIQUEINDEXidx_order_noONorders(order_no);CREATEINDEXidx_user_createdONorders(user_id,created_at);CREATEINDEXidx_status_createdONorders(status,created_at);

8.4 订单详情表(order_items)

CREATETABLEorder_items(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_idBIGINTNOTNULL,product_idBIGINTNOTNULL,quantityINTNOTNULL,priceDECIMAL(10,2)NOTNULL);-- 索引设计CREATEINDEXidx_order_idONorder_items(order_id);CREATEINDEXidx_product_idONorder_items(product_id);

九、踩坑提醒

9.1 索引不是越多越好

每个索引都有代价：

• 占用磁盘空间
• 降低写操作性能（INSERT/UPDATE/DELETE需要维护索引）
• 增加优化器选择成本

建议：

• 单表索引数不超过5个
• 组合索引列数不超过3个
• 删除不使用的索引

9.2 写操作会变慢

-- 插入数据时，需要维护所有索引INSERTINTOusers(name,email,phone)VALUES(...);-- 需要更新：主键索引、name索引、email索引、phone索引-- 更新索引列时，代价更大UPDATEusersSETemail='new@example.com'WHEREid=1;-- 需要删除旧索引项，插入新索引项

9.3 避免冗余索引

-- 冗余：idx_a 是 idx_a_b 的前缀CREATEINDEXidx_aONtable_name(a);CREATEINDEXidx_a_bONtable_name(a,b);-- 包含了idx_a的功能-- 冗余：主键索引和唯一索引重复ALTERTABLEusersADDPRIMARYKEY(id);CREATEUNIQUEINDEXidx_idONusers(id);-- 重复！

9.4 长字段索引使用前缀

-- 对于长字符串，只索引前缀CREATEINDEXidx_emailONusers(email(10));-- 计算合适的前缀长度SELECTCOUNT(DISTINCTemail)astotal,COUNT(DISTINCTLEFT(email,10))asprefix_10,COUNT(DISTINCTLEFT(email,15))asprefix_15FROMusers;-- 选择区分度接近总区分度的最短前缀

十、面试高频考点

Q1：B+树和B树的区别？

答：

1. B+树数据只存储在叶子节点，B树数据存储在所有节点
2. B+树叶子节点形成有序链表，便于范围查询
3. B+树非叶子节点可以存储更多键，树高更低
4. B+树查询性能更稳定（必须到叶子节点）

Q2：聚簇索引和非聚簇索引的区别？

答：

• 聚簇索引：数据行和索引存储在一起，叶子节点就是数据页。InnoDB的主键索引就是聚簇索引。
• 非聚簇索引：索引和数据分开存储，叶子节点存储的是主键值（二级索引）。

查询二级索引时，先找到主键值，再回表查询完整数据（覆盖索引除外）。

Q3：什么是覆盖索引？

答：查询的所有列都在索引中，不需要回表查询数据行。

-- 有索引(name, age)SELECTname,ageFROMusersWHEREname='张三';-- 覆盖索引，Extra显示Using index

Q4：为什么主键推荐使用自增ID？

答：

1. 自增ID是顺序插入，减少页分裂
2. 占用空间小（INT/BIGINT vs UUID字符串）
3. 查询性能更好（整数比较比字符串快）
4. 避免UUID随机插入导致的页分裂和碎片

Q5：索引失效的常见场景？

答：

1. 违反最左前缀原则
2. 使用LIKE '%xxx%'
3. 对索引列使用函数
4. 隐式类型转换
5. 使用!=、<>、IS NOT NULL
6. OR条件中有列没有索引

十一、总结

今天我们深入学习了MySQL索引的核心知识：

1. 索引原理：B+树数据结构，为什么比哈希、二叉树更适合数据库
2. 索引类型：主键、唯一、普通、组合、全文索引的适用场景
3. 最左前缀原则：组合索引设计的核心原则
4. 索引失效：避免常见的索引失效场景
5. EXPLAIN：分析SQL执行计划的利器
6. 实战设计：电商系统的索引设计案例

核心要点：

• 索引能大幅提升查询性能，但会降低写性能
• 理解B+树原理，才能设计出高效的索引
• 最左前缀原则是组合索引的灵魂
• 学会用EXPLAIN分析SQL性能

下一步预告

Day9我们将学习MySQL的事务与ACID特性，这是保证数据一致性的核心机制。我们将深入探讨事务隔离级别、MVCC机制，以及如何处理并发问题。敬请期待！

参考资料

1. MySQL官方文档 - 索引优化
2. MySQL索引背后的数据结构及算法原理

互动话题

1. 你在实际项目中遇到过哪些索引相关的性能问题？是如何解决的？
2. 对于"索引越多越好"这个观点，你有什么看法？
3. 你们团队是如何做SQL审核和索引优化的？

欢迎在评论区分享你的经验和见解！如果觉得本文有帮助，别忘了点赞收藏哦~