写在前面欢迎来到MySQL系列教学第8天经过前几天的学习我们已经掌握了MySQL的基础操作和查询技巧。今天我们将深入MySQL最核心的优化手段——索引。索引是数据库性能优化的基石理解索引原理对于每一位开发者都至关重要。无论你是刚入门的初学者还是准备面试的求职者这篇文章都将帮助你全面掌握MySQL索引的核心知识。文章目录写在前面一、为什么需要索引1.1 全表扫描的性能灾难1.2 索引扫描的性能飞跃1.3 性能对比实验二、索引的数据结构2.1 为什么不用哈希表2.2 为什么不用二叉搜索树2.3 为什么用B树2.4 B树 vs B树对比三、索引类型详解3.1 主键索引Primary Key3.2 唯一索引Unique Index3.3 普通索引Normal Index3.4 组合索引Composite Index3.5 全文索引Fulltext Index3.6 索引类型总结表四、索引的创建与删除4.1 CREATE INDEX语法4.2 ALTER TABLE语法4.3 删除索引4.4 查看索引五、组合索引的最左前缀原则重点5.1 什么是最左前缀原则5.2 最左前缀的底层原理5.3 组合索引设计技巧5.4 索引下推Index Condition Pushdown六、索引失效场景6.1 OR条件导致索引失效6.2 LIKE以通配符开头6.3 对索引列进行函数操作6.4 隐式类型转换6.5 其他索引失效场景七、EXPLAIN分析执行计划7.1 EXPLAIN基本使用7.2 关键字段解读type字段访问类型从好到差key字段rows字段Extra字段7.3 EXPLAIN示例分析八、实战为电商系统表设计索引8.1 用户表(users)8.2 商品表(products)8.3 订单表(orders)8.4 订单详情表(order_items)九、踩坑提醒9.1 索引不是越多越好9.2 写操作会变慢9.3 避免冗余索引9.4 长字段索引使用前缀十、面试高频考点Q1B树和B树的区别Q2聚簇索引和非聚簇索引的区别Q3什么是覆盖索引Q4为什么主键推荐使用自增IDQ5索引失效的常见场景十一、总结下一步预告参考资料互动话题一、为什么需要索引1.1 全表扫描的性能灾难想象一下你有一本1000页的书没有目录要找到某一章的内容你只能一页一页地翻。这就是全表扫描——数据库需要逐行检查每一行数据来找到匹配的记录。-- 假设users表有100万条数据没有索引SELECT*FROMusersWHEREphone13800138000;在没有索引的情况下MySQL需要扫描全部100万条记录时间复杂度为O(n)。1.2 索引扫描的性能飞跃如果给phone字段加上索引查询过程就变成了在索引结构中快速定位到目标值根据索引中的指针找到对应的数据行时间复杂度降为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;WHILEi1000000DOINSERTINTOtest_index(name,phone,email)VALUES(CONCAT(user,i),CONCAT(138,LPAD(i,8,0)),CONCAT(user,i,example.com));SETii1;ENDWHILE;END$$DELIMITER;CALLinsert_test_data();-- 无索引查询约1-2秒SELECT*FROMtest_indexWHEREphone13800123456;-- 创建索引CREATEINDEXidx_phoneONtest_index(phone);-- 有索引查询约0.001秒SELECT*FROMtest_indexWHEREphone13800123456;数据量无索引查询时间有索引查询时间性能提升1万条5ms0.5ms10倍10万条50ms0.8ms60倍100万条800ms1ms800倍1000万条10s2ms5000倍结论随着数据量增长索引带来的性能提升呈指数级增长。二、索引的数据结构2.1 为什么不用哈希表哈希表查询时间复杂度为O(1)看似很完美但为什么不作为索引的数据结构呢哈希索引的缺点不支持范围查询WHERE age 18无法使用哈希索引不支持排序ORDER BY无法利用哈希索引不支持最左前缀匹配组合索引无法部分匹配哈希冲突需要处理冲突增加复杂度-- 只有Memory引擎支持哈希索引CREATETABLEhash_test(idINTPRIMARYKEY,nameVARCHAR(50))ENGINEMEMORY;-- InnoDB的自适应哈希索引是内部优化不可控2.2 为什么不用二叉搜索树二叉搜索树BST查询时间复杂度为O(log n)但存在致命问题树高度过高1000万条数据树高约24层需要24次磁盘IO可能退化成链表极端情况下时间复杂度变为O(n)2.3 为什么用B树B树的特点多路平衡搜索树每个节点可以有多个子节点所有叶子节点在同一层保证查询效率稳定B树的优化InnoDB使用数据只存储在叶子节点非叶子节点只存储键值可以存储更多索引项叶子节点形成有序链表便于范围查询和排序更低的树高度假设每个节点存储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树对比特性B树B树数据存储位置所有节点仅叶子节点叶子节点链接无有形成有序链表范围查询效率需要中序遍历直接遍历叶子节点树高度较高更低查询稳定性不稳定可能在非叶子节点找到稳定必须到叶子节点适用场景文件系统数据库索引三、索引类型详解3.1 主键索引Primary Key-- 创建表时指定主键CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL);-- 或单独添加ALTERTABLEusersADDPRIMARYKEY(id);特点唯一标识每行记录自动创建不能为NULLInnoDB中主键索引就是聚簇索引数据存储在叶子节点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 IndexCREATEINDEXidx_name_age_cityONusers(name,age,city);最左前缀原则查询条件必须从索引的最左列开始可以只使用前缀部分但不能跳过中间列-- 可以使用索引SELECT*FROMusersWHEREname张三;SELECT*FROMusersWHEREname张三ANDage20;SELECT*FROMusersWHEREname张三ANDage20ANDcity北京;-- 不能使用索引缺少最左列nameSELECT*FROMusersWHEREage20;SELECT*FROMusersWHEREcity北京;-- 部分使用索引只用到了nameSELECT*FROMusersWHEREname张三ANDcity北京;3.5 全文索引Fulltext Index-- 创建全文索引CREATEFULLTEXTINDEXidx_contentONarticles(content);-- 使用全文索引SELECT*FROMarticlesWHEREMATCH(content)AGAINST(MySQL索引INNATURALLANGUAGEMODE);注意仅支持CHAR、VARCHAR、TEXT类型只有MyISAM和InnoDB5.6支持中文分词需要额外配置3.6 索引类型总结表索引类型是否允许重复是否允许NULL使用场景主键索引否否表的主键唯一索引否是一个邮箱、手机号等普通索引是是频繁查询的字段组合索引是是多条件查询全文索引是是文本搜索四、索引的创建与删除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);-- 有效使用全部索引列WHEREa1ANDb2ANDc3WHEREa1ANDb2WHEREa1-- 无效缺少最左列aWHEREb2ANDc3WHEREc3-- 部分有效只使用aWHEREa1ANDc35.2 最左前缀的底层原理B树按照索引列的顺序排序存储先按第一列排序第一列相同按第二列排序以此类推因此缺少最左列就无法在B树中定位。5.3 组合索引设计技巧原则将区分度高的、查询频繁的列放在前面-- 不好的设计性别区分度低CREATEINDEXidx_gender_ageONusers(gender,age);-- 好的设计年龄区分度高CREATEINDEXidx_age_genderONusers(age,gender);-- 更好的设计考虑查询频率-- 如果经常按年龄查询偶尔按性别过滤CREATEINDEXidx_ageONusers(age);5.4 索引下推Index Condition PushdownMySQL 5.6引入的优化可以在索引遍历过程中过滤不满足条件的记录。-- 有索引(name, age)SELECT*FROMusersWHEREnameLIKE张%ANDage20;-- 没有索引下推先找到所有name以张开头的记录再回表过滤age-- 有索引下推在索引中就过滤掉age!20的记录减少回表次数六、索引失效场景6.1 OR条件导致索引失效-- 假设有索引idx_ageSELECT*FROMusersWHEREage20ORname张三;-- name没有索引会导致全表扫描-- 解决方案给name也加上索引或改写为UNIONSELECT*FROMusersWHEREage20UNIONALLSELECT*FROMusersWHEREname张三;6.2 LIKE以通配符开头-- 索引失效SELECT*FROMusersWHEREnameLIKE%三%;SELECT*FROMusersWHEREnameLIKE%张;-- 索引有效SELECT*FROMusersWHEREnameLIKE张%;6.3 对索引列进行函数操作-- 索引失效SELECT*FROMusersWHEREYEAR(created_at)2024;SELECT*FROMusersWHEREUPPER(name)ZHANGSAN;-- 改写为范围查询SELECT*FROMusersWHEREcreated_at2024-01-01ANDcreated_at2025-01-01;6.4 隐式类型转换-- 假设phone是VARCHAR类型-- 索引失效数字与字符串比较发生类型转换SELECT*FROMusersWHEREphone13800138000;-- 索引有效SELECT*FROMusersWHEREphone13800138000;6.5 其他索引失效场景-- 1. 使用!或SELECT*FROMusersWHEREage!20;-- 2. 使用IS NOT NULLIS NULL可以用索引SELECT*FROMusersWHEREnameISNOTNULL;-- 3. 索引列参与计算SELECT*FROMusersWHEREage121;-- 4. 字符串不加引号SELECT*FROMusersWHEREname123;-- name是VARCHAR-- 5. 全表扫描比索引更快时数据量小或查询大部分数据SELECT*FROMusersWHEREage0;-- 几乎所有数据都满足七、EXPLAIN分析执行计划7.1 EXPLAIN基本使用EXPLAINSELECT*FROMusersWHEREid1;7.2 关键字段解读type字段访问类型从好到差type值说明效率system系统表只有一行最高const主键或唯一索引最多一行高eq_ref连接查询使用主键或唯一索引高ref非唯一索引较高range索引范围扫描中等index全索引扫描较低ALL全表扫描最低key字段实际使用的索引名称。NULL表示没有使用索引。rows字段估计需要扫描的行数。越小越好。Extra字段Extra值说明Using index覆盖索引不需要回表Using where使用WHERE过滤Using temporary使用临时表需要优化Using filesort需要额外排序需要优化Using index condition使用索引下推7.3 EXPLAIN示例分析-- 示例1好的执行计划EXPLAINSELECT*FROMusersWHEREid1;-- 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);-- 商品名称搜索用全文索引或Elasticsearch8.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索引-- 更新索引列时代价更大UPDATEusersSETemailnewexample.comWHEREid1;-- 需要删除旧索引项插入新索引项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;-- 选择区分度接近总区分度的最短前缀十、面试高频考点Q1B树和B树的区别答B树数据只存储在叶子节点B树数据存储在所有节点B树叶子节点形成有序链表便于范围查询B树非叶子节点可以存储更多键树高更低B树查询性能更稳定必须到叶子节点Q2聚簇索引和非聚簇索引的区别答聚簇索引数据行和索引存储在一起叶子节点就是数据页。InnoDB的主键索引就是聚簇索引。非聚簇索引索引和数据分开存储叶子节点存储的是主键值二级索引。查询二级索引时先找到主键值再回表查询完整数据覆盖索引除外。Q3什么是覆盖索引答查询的所有列都在索引中不需要回表查询数据行。-- 有索引(name, age)SELECTname,ageFROMusersWHEREname张三;-- 覆盖索引Extra显示Using indexQ4为什么主键推荐使用自增ID答自增ID是顺序插入减少页分裂占用空间小INT/BIGINT vs UUID字符串查询性能更好整数比较比字符串快避免UUID随机插入导致的页分裂和碎片Q5索引失效的常见场景答违反最左前缀原则使用LIKE %xxx%对索引列使用函数隐式类型转换使用!、、IS NOT NULLOR条件中有列没有索引十一、总结今天我们深入学习了MySQL索引的核心知识索引原理B树数据结构为什么比哈希、二叉树更适合数据库索引类型主键、唯一、普通、组合、全文索引的适用场景最左前缀原则组合索引设计的核心原则索引失效避免常见的索引失效场景EXPLAIN分析SQL执行计划的利器实战设计电商系统的索引设计案例核心要点索引能大幅提升查询性能但会降低写性能理解B树原理才能设计出高效的索引最左前缀原则是组合索引的灵魂学会用EXPLAIN分析SQL性能下一步预告Day9我们将学习MySQL的事务与ACID特性这是保证数据一致性的核心机制。我们将深入探讨事务隔离级别、MVCC机制以及如何处理并发问题。敬请期待参考资料MySQL官方文档 - 索引优化MySQL索引背后的数据结构及算法原理互动话题你在实际项目中遇到过哪些索引相关的性能问题是如何解决的对于索引越多越好这个观点你有什么看法你们团队是如何做SQL审核和索引优化的欢迎在评论区分享你的经验和见解如果觉得本文有帮助别忘了点赞收藏哦~
