手表网站查询,网站建设推广选哪家,网站开发工程师心得总结,营销培训心得MySQL 基础教程 - 第八章#xff1a;索引与性能优化基础 摘要#xff1a;本章将深入探讨 MySQL 数据库性能优化的核心——索引。我们将从底层 B 树数据结构讲起#xff0c;详细介绍索引的分类、创建方法及设计原则#xff08;如最左前缀法则、覆盖索引#xff09;。同时-- 1. 确保 users 表结构与索引完整-- 如果 users 表不存在请参考前面章节创建。这里重点检查索引。-- 尝试添加索引 (如果已存在可能会报错可忽略 Duplicate key name 错误或者使用可视化工具检查)-- 为了演示 8.4.3 节的 ref 类型查询ALTERTABLEusersADDINDEXidx_username(username);-- 2. 确保 orders 表包含 create_time 字段 (用于演示最左前缀)-- 如果报错 Duplicate column name说明字段已存在忽略即可ALTERTABLEordersADDCOLUMNcreate_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间;-- 3. 确保 orders 表包含复合索引 (用于 8.3.1 节)ALTERTABLEordersADDINDEXidx_user_time(user_id,create_time);-- 4. 数据补充 (确保有数据可查)-- 插入 users 测试数据INSERTIGNOREINTOusers(user_id,username,email,age,gender)VALUES(1,alice,aliceexample.com,25,female),(2,bob,bobexample.com,30,male),(3,charlie,charlieexample.com,22,male),(4,david,davidexample.com,35,male),(5,eve,eveexample.com,28,female);-- 插入 orders 测试数据INSERTIGNOREINTOorders(order_id,user_id,total_amount,order_status,create_time)VALUES(1,1,100.00,1,2023-10-01 10:00:00),(2,1,50.00,1,2023-10-02 11:00:00),(3,2,200.00,0,2023-10-01 12:00:00),(4,3,300.00,2,2023-10-03 09:00:00),(5,1,120.00,1,2023-10-05 14:00:00);8.2 索引概述8.2.1 什么是索引索引Index是帮助 MySQL高效获取数据的数据结构。如果不使用索引MySQL 必须从第一行记录开始扫描整个表Full Table Scan直到找到符合要求的记录。表越大查询越慢。使用索引后MySQL 可以像查字典一样通过目录快速定位到数据所在的位置。优点极大提高数据检索效率O(logN) vs O(N)。通过索引列对数据进行排序降低数据排序的成本降低 CPU 消耗。代价空间成本索引本身也是表需要占用磁盘空间。时间成本当对表进行 INSERT、UPDATE、DELETE 操作时MySQL 不仅要保存数据还要维护索引如 B 树的分裂与合并会降低写操作的性能。8.2.2 索引的数据结构B Tree在 MySQL 的 InnoDB 存储引擎中索引的底层实现是B 树多路平衡搜索树。结构特点非叶子节点内部节点只存储键值Key和指针不存储实际数据行。这使得每个节点能容纳更多的键值降低树的高度。叶子节点Leaf Nodes存储了所有的键值和对应的数据或主键 ID。双向链表所有叶子节点通过指针连接成一个双向链表。这使得范围查询Range Scan如BETWEEN,,非常高效只需找到起点然后顺着链表遍历即可。为什么不用二叉树或 Hash二叉树树高度太高导致磁盘 I/O 次数过多索引通常存储在磁盘上每一层访问都可能产生一次 I/O。Hash只适合等值查询不支持范围查询,不支持排序。8.3 索引分类与创建8.3.1 按功能逻辑分类普通索引 (Normal Index)最基本的索引没有任何限制。创建CREATE INDEX index_name ON table(column);或ALTER TABLE table ADD INDEX index_name(column);唯一索引 (Unique Index)索引列的值必须唯一但允许有空值NULL。创建CREATE UNIQUE INDEX index_name ON table(column);作用除了加速查询更主要用于约束数据的唯一性如email字段。主键索引 (Primary Key)一种特殊的唯一索引不允许有空值。聚簇索引 (Clustered Index)在 InnoDB 中主键索引的叶子节点直接存储了整行数据。每个表只能有一个主键。全文索引 (Fulltext Index)用于全文搜索解决LIKE %word%效率低的问题。MySQL 5.7 InnoDB 支持全文索引但对中文支持需要配置 ngram 解析器此处不展开通常建议使用专门的搜索引擎如 ES。组合索引 (Composite Index)在多个字段上创建的索引如KEY idx_user_time (user_id, create_time)。8.3.2 索引管理实战场景假设我们需要经常根据order_status查询订单。-- 1. 查看现有索引SHOWINDEXFROMorders;-- 2. 创建普通索引-- 方式一CREATE INDEXCREATEINDEXidx_statusONorders(order_status);-- 方式二ALTER TABLE (推荐)ALTERTABLEordersADDINDEXidx_total(total_amount);-- 3. 创建唯一索引-- 假设我们有个业务流水号字段 serial_no 需要唯一-- ALTER TABLE orders ADD UNIQUE INDEX idx_serial (serial_no);-- 4. 删除索引DROPINDEXidx_statusONorders;-- 或者ALTERTABLEordersDROPINDEXidx_total;8.4 索引设计原则 (核心)这是面试和实战中最重要的部分。索引不是越多越好设计不当反而拖慢系统。8.4.1 最左前缀法则 (Most Left Prefix)⚠️ 前置检查本节演示需要orders表包含create_time字段和idx_user_time索引。如果你一直跟随教程操作请先执行以下 SQL 补充结构-- 1. 补充 create_time 字段ALTERTABLEordersADDCOLUMNcreate_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间;-- 2. 补充测试数据 (可选为了演示效果更好)UPDATEordersSETcreate_time2023-10-01 10:00:00WHEREorder_id1;-- 3. 创建复合索引 (核心)ALTERTABLEordersADDINDEXidx_user_time(user_id,create_time);对于组合索引例如idx_user_time (user_id, create_time)查询时必须从索引的最左边列开始匹配。有效的情况-- 1. 查询条件包含最左列 (user_id)SELECT*FROMordersWHEREuser_id1;-- 走索引-- 2. 查询条件包含前缀列 (user_id AND create_time)SELECT*FROMordersWHEREuser_id1ANDcreate_time2023-10-01;-- 走索引失效的情况-- 1. 跳过最左列直接查第二个列SELECT*FROMordersWHEREcreate_time2023-10-01;-- ❌ 不走 idx_user_time 索引全表扫描因为不知道 user_idB树无法定位范围查询截断如果组合索引是(a, b, c)查询WHERE a1 AND b2 AND c3a用到了索引。b用到了索引范围查询。c无法使用索引。因为b是范围后面的列在 B 树中不再有序。8.4.2 覆盖索引 (Covering Index)如果一个索引包含覆盖了所有需要查询的字段的值MySQL 就不需要回表Back Query去查找整行数据。回表普通索引二级索引的叶子节点存储的是主键ID。如果查询需要其他字段MySQL 需先查普通索引拿到 ID再去主键索引查整行数据。覆盖索引示例现有索引idx_user_time (user_id, create_time)。-- 情况 A需要回表SELECT*FROMordersWHEREuser_id1;-- 需要查 * (所有字段)索引里只有 user_id 和 create_time需要回表查 total_amount 等。-- 情况 B覆盖索引 (高性能)SELECTuser_id,create_timeFROMordersWHEREuser_id1;-- 只需要查 user_id 和 create_time这些都在索引里直接返回不需要回表。-- EXPLAIN 输出中 Extra 字段会显示 Using index。8.4.3 索引失效的常见场景在索引列上进行运算或函数操作-- ❌ 失效SELECT*FROMusersWHEREYEAR(create_time)2023;-- ✅ 优化改写为范围查询SELECT*FROMusersWHEREcreate_timeBETWEEN2023-01-01AND2023-12-31 23:59:59;字符串不加单引号类型隐式转换如果phone字段是VARCHAR查询WHERE phone 13800000000MySQL 会自动做CAST(phone AS SIGNED)导致全表扫描。模糊查询以 % 开头-- ❌ 失效SELECT*FROMusersWHEREusernameLIKE%ice;-- ✅ 有效 (最左匹配)SELECT*FROMusersWHEREusernameLIKEali%;OR 连接的条件如果WHERE a1 OR b2除非a和b都有索引否则索引会失效。8.5 慢查询分析工具EXPLAINEXPLAIN是查看 SQL 执行计划的神器。它模拟优化器执行 SQL 语句告诉我们 MySQL 是如何处理你的 SQL 的。8.5.1 基本用法EXPLAINSELECT*FROMusersWHEREuser_id1;执行后会返回一张表包含以下关键字段8.5.2 关键字段详解字段含义详细解释id选择标识符select 查询的序列号。id 越大越先执行id 相同则从上往下执行。select_type查询类型SIMPLE(简单查询不含子查询/UNION)、PRIMARY(主查询)、SUBQUERY(子查询) 等。type访问类型(重要)性能从好到坏systemconsteq_refrefrangeindexALLpossible_keys可能用到的索引MySQL 猜测可能使用的索引列表。key实际使用的索引MySQL 实际决定使用的索引。如果为 NULL则没用索引。key_len索引长度使用了索引的多少字节。有助于判断组合索引是否被充分利用。rows扫描行数预计扫描的行数估算值越小越好。Extra额外信息Using index(覆盖索引好);Using where(需回表过滤);Using filesort(需要额外的排序操作坏);Using temporary(使用了临时表极坏)。8.5.3 Type 详解与实战演示我们通过具体的 SQL 看看type的变化。const通过主键或唯一索引命中一条记录。EXPLAINSELECT*FROMusersWHEREuser_id1;-- type: const, key: PRIMARYref非唯一性索引扫描返回匹配某个单独值的所有行。EXPLAINSELECT*FROMusersWHEREusernamealice;-- type: ref, key: idx_username-- 注意如果 username 不是唯一索引则是 ref如果是唯一索引则是 const。range索引范围扫描常见于, , BETWEEN, IN。EXPLAINSELECT*FROMusersWHEREuser_id1;-- type: range, key: PRIMARYindex全索引扫描 (Full Index Scan)。扫描遍历索引树通常比 ALL 快因为索引文件通常比数据文件小。-- 查询 id (主键) 和 username (索引列)不需要回表但需要扫描整个 username 索引树EXPLAINSELECTuser_id,usernameFROMusers;-- type: index, Extra: Using indexALL全表扫描 (Full Table Scan)。最差的情况。-- gender 字段没有索引 (虽然建表时可能有默认假设这里没建或区分度低被优化器忽略)-- 或者查询非索引列EXPLAINSELECT*FROMusersWHEREage25;-- type: ALL (假设 age 字段未建索引)8.5.4 实战案例Filesort 优化假设我们要查询订单按total_amount排序。-- 场景没有索引时的排序EXPLAINSELECT*FROMordersORDERBYtotal_amount;-- type: ALL, Extra: Using filesort-- 说明 MySQL 无法利用索引完成排序必须在内存中或磁盘进行额外的排序操作性能差。优化添加索引。ALTERTABLEordersADDINDEXidx_amount(total_amount);EXPLAINSELECTtotal_amountFROMordersORDERBYtotal_amount;-- type: index, Extra: Using index-- 此时直接读取索引树本身有序无需额外排序。8.6 总结索引是双刃剑能极大提升读性能但会降低写性能并占用空间。设计原则高频查询的字段建索引。区分度高唯一性强的字段适合建索引如 ID、手机号区分度低如性别、状态通常不适合。利用最左前缀和覆盖索引减少回表。分析习惯写完复杂 SQL 后习惯性用EXPLAIN看一下执行计划确保没有出现ALL或Using filesort在数据量大时。下一章我们将进入数据库事务的世界探讨如何保证数据的一致性与隔离性。