3.3 索引优化实战：让你的查询速度提升10倍的秘密武器

3.3 索引优化实战：让你的查询速度提升10倍的秘密武器

📚 学习目标

通过本节学习，你将掌握：

• ✅ MySQL索引的工作原理和数据结构（B-Tree、Hash、全文索引等）
• ✅ 索引设计原则和最佳实践
• ✅ 复合索引、覆盖索引、前缀索引等高级索引技术
• ✅ 索引优化实战案例和性能提升技巧
• ✅ 索引监控和维护方法

🎯 学习收获

学完本节后，你将能够：

1. 性能提升：通过索引优化让查询速度提升10倍甚至更多
2. 索引设计：设计高效的索引策略
3. 问题诊断：快速识别索引问题并优化
4. 最佳实践：掌握索引优化的最佳实践

💡 实际场景引入

场景一：查询性能急剧下降

问题描述：某订单系统的订单查询功能，随着数据量增长到1000万条，查询时间从原来的0.1秒增加到10秒，严重影响用户体验。

你的任务：如何通过索引优化将查询时间降低到0.1秒以内？

场景二：索引过多导致写入性能下降

问题描述：某系统为了优化查询性能，创建了大量索引。虽然查询性能提升了，但写入性能急剧下降，插入一条记录需要几秒钟。

你的任务：如何平衡索引数量和写入性能？

索引是数据库性能优化中最重要也是最复杂的主题之一。一个设计良好的索引可以将查询性能从几秒提升到几毫秒，而一个糟糕的索引设计则可能导致系统性能急剧下降。本节将深入探讨MySQL索引的工作原理、不同类型索引的特点和使用场景，并通过大量实战案例展示如何设计和优化索引，让你的查询速度提升10倍甚至更多。

索引基础原理

索引是什么？

索引是数据库中一种特殊的数据结构，类似于书籍的目录，可以帮助数据库快速定位到所需的数据行，而无需扫描整个表。

索引类型详解

1. B-Tree索引

-- B-Tree索引是最常见的索引类型CREATEINDEXidx_user_emailONusers(email);-- 查看索引信息SHOWINDEXFROMusers;-- B-Tree索引适用于：-- 1. 精确匹配查询SELECT*FROMusersWHEREemail='john@example.com';-- 2. 范围查询SELECT*FROMusersWHEREageBETWEEN20AND30;-- 3. 前缀匹配SELECT*FROMusersWHEREemailLIKE'john%';-- 4. 排序操作SELECT*FROMusersORDERBYemail;

2. 哈希索引

-- 哈希索引只支持精确匹配CREATETABLEuser_sessions(session_idVARCHAR(64),user_idINT,created_atTIMESTAMP,INDEXidx_session_idUSINGHASH(session_id));-- 哈希索引适用于：-- 1. 精确匹配查询（非常快）SELECT*FROMuser_sessionsWHEREsession_id='abc123def456';-- 不适用于：-- 1. 范围查询-- 2. 排序操作-- 3. 前缀匹配

3. 全文索引

-- 全文索引用于文本搜索CREATETABLEarticles(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(255),contentTEXT,FULLTEXTINDEXidx_content(title,content));-- 全文搜索查询SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST('MySQL performance optimization'INNATURALLANGUAGEMODE);-- 布尔模式搜索SELECT*FROMarticlesWHEREMATCH(title,content)AGAINST('+MySQL +optimization -slow'INBOOLEANMODE);

索引设计原则

1. 选择合适的列作为索引

-- 分析查询模式来决定索引列-- 查看最频繁的查询SELECTDIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT/1000000000ASavg_time_secFROMperformance_schema.events_statements_summary_by_digestWHEREDIGEST_TEXTLIKE'SELECT%'ORDERBYCOUNT_STARDESCLIMIT10;-- 根据查询条件选择索引列-- 查询：SELECT * FROM orders WHERE user_id = 123 AND status = 'completed'-- 建议创建复合索引CREATEINDEXidx_user_statusONorders(user_id,status);

2. 索引列顺序的重要性

-- 复合索引中列的顺序非常重要-- 创建索引CREATEINDEXidx_user_date_statusONorders(user_id,order_date,status);-- 以下查询可以使用索引SELECT*FROMordersWHEREuser_id=123;SELECT*FROMordersWHEREuser_id=123ANDorder_date>'2023-01-01';SELECT*FROMordersWHEREuser_id=123ANDorder_date>'2023-01-01'ANDstatus='completed';-- 以下查询无法有效使用索引SELECT*FROMordersWHEREstatus='completed';SELECT*FROMordersWHEREorder_date>'2023-01-01';SELECT*FROMordersWHEREorder_date>'2023-01-01'ANDstatus='completed';

3. 索引选择性分析

-- 索引选择性 = 不同值的数量 / 总行数-- 选择性越高，索引效果越好（接近1为最佳）-- 分析列的选择性SELECTCOUNT(DISTINCTuser_id)ASdistinct_users,COUNT(*)AStotal_records,COUNT(DISTINCTuser_id)/COUNT(*)ASselectivityFROMorders;-- 对于高选择性列创建索引效果更好-- 例如：user_id选择性接近1，status选择性可能只有0.1

EXPLAIN分析索引使用

执行计划详解

-- 使用EXPLAIN分析查询执行计划EXPLAINSELECT*FROMusersWHEREemail='john@example.com';-- EXPLAIN输出字段说明：-- id: 查询序列号-- select_type: 查询类型-- table: 表名-- partitions: 匹配的分区-- type: 连接类型（重要！）-- possible_keys: 可能使用的索引-- key: 实际使用的索引-- key_len: 使用的索引长度-- ref: 索引比较的列或常数-- rows: 扫描的行数-- filtered: 按表条件过滤的行百分比-- Extra: 附加信息-- 连接类型(type)重要性排序（从好到差）：-- system/const/eq_ref/ref/ref_or_null/index_merge/unique_subquery/index_subquery/range/index/ALL

常见执行计划分析

-- 1. const（最佳）EXPLAINSELECT*FROMusersWHEREuser_id=123;-- 通过主键或唯一索引精确匹配一行-- 2. ref（良好）EXPLAINSELECT*FROMusersWHEREemail='john@example.com';-- 使用非唯一索引进行匹配-- 3. range（可接受）EXPLAINSELECT*FROMusersWHEREageBETWEEN20AND30;-- 使用索引进行范围扫描-- 4. index（一般）EXPLAINSELECTCOUNT(*)FROMusers;-- 扫描整个索引树-- 5. ALL（最差）EXPLAINSELECT*FROMusersWHEREfirst_name='John';-- 全表扫描，需要优化

优化前后的对比

-- 优化前：全表扫描EXPLAINSELECTu.name,o.order_date,o.total_amountFROMusers uJOINorders oONu.user_id=o.user_idWHEREYEAR(o.order_date)=2023;-- 输出显示type=ALL，rows=大量数据，Extra包含"Using where"-- 优化后：使用索引EXPLAINSELECTu.name,o.order_date,o.total_amountFROMusers uJOINorders oONu.user_id=o.user_idWHEREo.order_date>='2023-01-01'ANDo.order_date<'2024-01-01'