面试官索引不会用还敢说精通MySQL这7个坑你踩过几个索引不是万能药用错比不用更可怕从原理到实战带你避开MySQL索引的七个天坑。引言索引越多越好大错特错“这个查询太慢了给所有WHERE字段都加上索引吧”“不就是加个索引嘛分分钟搞定性能肯定飞起”这样的对话在无数开发团队中反复上演。然而索引真的是越多越好吗现实往往啪啪打脸加了索引后查询没变快插入反而变慢了明明建了索引执行计划却显示全表扫描数据量才几万索引文件比数据文件还大如果你也遇到过这些问题或者对索引的理解仅停留在“给查询字段加索引”的层面那么这篇文章正是为你准备的。今天我将带你从MySQL InnoDB的存储原理出发彻底搞懂索引的工作机制并用7个真实案例帮你避开索引使用的那些坑。全程高能建议收藏一、InnoDB数据存储原理不懂这个谈何索引1.1 页MySQL的最小存储单位InnoDB不会像我们想象的那样一行一行地读取磁盘数据。为了减少磁盘I/O它把数据划分为若干页每个页默认16KB。数据页之间组成双向链表页内记录按主键顺序组成单向链表。每个数据页内部有一个页目录它把记录分成多个组槽每组若干条记录。这样按主键搜索时可以先通过二分法定位到所在的槽然后在组内顺序查找大大加快了页内查找速度。下面是一个数据页的结构示意图渲染错误:Mermaid 渲染失败: Parse error on line 2: ... TD subgraph 数据页(16KB) direc ----------------------^ Expecting SEMI, NEWLINE, SPACE, EOF, GRAPH, DIR, subgraph, SQS, end, AMP, COLON, START_LINK, STYLE, LINKSTYLE, CLASSDEF, CLASS, CLICK, DOWN, UP, NUM, NODE_STRING, BRKT, MINUS, MULT, UNICODE_TEXT, got PS假设我们要查找PK15的记录流程如下二分页目录找到记录所在槽从槽指向的记录开始顺序向下查找3次找到目标这种结构使得页内查找非常高效但页之间呢如果表有成千上万个页如何快速定位记录所在的页这就轮到B树登场了。1.2 B树索引的基石InnoDB使用B树作为索引结构。B树是一棵“倒着长的树”所有真实数据都存在叶子节点上层节点只存放索引键和指向子节点的指针。数据页非叶子节点非叶子节点索引键:10指针→页10索引键:20指针→页20索引键:...索引键:5指针→页5索引键:15指针→页15索引键:25指针→页25PK1, data...PK4, data...PK7, data...PK12, data...PK15, data...PK18, data...聚簇索引InnoDB会自动使用主键作为聚簇索引的键。叶子节点保存整行数据。由于数据只有一份所以聚簇索引只能有一个。二级索引我们手动创建的索引非主键索引都是二级索引。它的叶子节点不保存数据只保存主键值。通过二级索引找到主键后还需要再到聚簇索引中查找完整数据这个过程叫回表。渲染错误:Mermaid 渲染失败: Parse error on line 2: ...TD subgraph 二级索引(name) N1[na ----------------------^ Expecting SEMI, NEWLINE, SPACE, EOF, GRAPH, DIR, subgraph, SQS, end, AMP, COLON, START_LINK, STYLE, LINKSTYLE, CLASSDEF, CLASS, CLICK, DOWN, UP, NUM, NODE_STRING, BRKT, MINUS, MULT, UNICODE_TEXT, got PS理解了B树和回表我们就能明白索引不是免费的午餐每一次查询都可能付出代价。二、索引的三大代价为什么不能乱加索引很多开发者以为索引就是“查询加速器”多多益善。实际上创建索引需要付出维护代价、空间代价、回表代价。2.1 维护代价写入变慢每张表如果有N个索引那么插入一条数据时不仅要更新聚簇索引还要更新N个二级索引的B树。这会导致写入速度直线下降。做个实验创建一个10万行的表person字段有id(主键)、name、score、create_time。无索引插入10万条记录耗时约140秒。创建两个索引(name,score)联合索引 create_time单独索引插入耗时增加到154秒。而且B树为了保持有序当页满时会发生页分裂删除数据时可能发生页合并这些都会带来额外的I/O开销甚至可能产生死锁。2.2 空间代价索引可能比数据还大二级索引虽然不保存完整数据但要保存索引列的值和主键占用的磁盘空间不容小觑。还是上面的person表两个索引占用的空间如下SELECTDATA_LENGTH,INDEX_LENGTHFROMinformation_schema.TABLESWHERETABLE_NAMEperson;结果数据大小DATA_LENGTH 4.7MB索引大小INDEX_LENGTH 8.4MB索引比数据还大如果你的表有几十个索引磁盘空间很快就会告急。2.3 回表代价额外的查询使用二级索引查询时如果SELECT的字段不在索引中就需要回表。回表意味着至少多一次磁盘I/O甚至更多因为可能跨多个数据页。我们通过EXPLAIN对比一下-- 需要回表EXPLAINSELECT*FROMpersonWHEREnamename1;-- Extra: (空)-- 索引覆盖查询字段都在索引中EXPLAINSELECTname,scoreFROMpersonWHEREnamename1;-- Extra: Using index索引覆盖避免了回表性能大幅提升。这也是为什么推荐只SELECT必要的字段并尽量利用联合索引覆盖查询。2.4 最佳实践不要过早建索引等业务稳定、数据量上万、查询变慢后再考虑。索引字段要轻量能用int就别用varchar能用前缀索引就别用全字段。用索引覆盖SELECT只取索引中包含的字段避免回表。三、索引失效的四大场景明明有索引为啥不走你辛辛苦苦建了索引MySQL却不领情执行计划显示全表扫描。这通常是因为你的查询写法导致索引失效。3.1 左模糊匹配EXPLAINSELECT*FROMpersonWHEREnameLIKE%name123%;B树索引是根据索引值排序的只能从左到右匹配。以通配符%开头无法确定前缀索引自然失效。对策尽量使用前缀匹配name LIKE name123%。如果必须后缀搜索可以考虑把数据反转存储或者使用全文搜索引擎如Elasticsearch。3.2 对索引列使用函数EXPLAINSELECT*FROMpersonWHERELENGTH(name)7;索引保存的是原始值经过函数计算后的值无法匹配。对策如果经常需要函数查询可以考虑创建函数索引MySQL 5.7支持虚拟列并对其创建索引或者提前计算好存入单独字段。3.3 联合索引未使用最左列假设有联合索引(name, score)-- 无法使用联合索引EXPLAINSELECT*FROMpersonWHEREscore90;-- 可以使用联合索引EXPLAINSELECT*FROMpersonWHEREnamename1ANDscore90;联合索引按照第一列排序第一列相同时才按第二列排。因此如果查询条件中没有第一列就无法利用索引。对策根据查询频率和选择性合理设计联合索引的列顺序。经常作为查询条件的列放在左边。3.4 类型隐式转换虽然原文没提但这也是常见的索引失效场景。-- 假设name是varchar类型但传入整数EXPLAINSELECT*FROMpersonWHEREname123;MySQL会将name隐式转换为整数进行比较导致索引失效相当于对索引列使用了函数。对策确保查询条件类型与字段类型一致避免隐式转换。索引失效场景左模糊匹配函数操作联合索引缺左列隐式类型转换解决方案前缀匹配/全文索引方案函数索引/预计算方案调整索引列顺序方案类型匹配四、MySQL基于成本决定是否走索引有时候即使查询能用到索引MySQL也可能选择全表扫描。为什么因为MySQL认为全表扫描比用索引更快。MySQL会基于成本来选择执行计划。成本主要包括I/O成本从磁盘加载数据页的成本默认读取一个页成本1.0CPU成本检测记录是否满足条件、排序等操作的成本默认检测一条记录成本0.24.1 全表扫描成本计算全表扫描的成本 ≈ 聚簇索引占用的页数 × 1.0 总记录数 × 0.2通过SHOW TABLE STATUS可以查看统计信息rows行数估算Data_length聚簇索引占用字节数除以16KB得页数对于person表rows≈100086Data_length≈4.7MB ≈ 289页所以全表扫描成本 ≈ 289×1.0 100086×0.2 ≈ 20306。4.2 索引扫描成本计算使用二级索引扫描时成本包括扫描二级索引页的I/O成本处理索引记录的CPU成本回表的I/O成本如果需要4.3 optimizer trace看透MySQL的选择MySQL提供了optimizer trace功能可以查看每个执行计划的成本明细。SEToptimizer_traceenabledon;SELECT*FROMpersonWHEREnamename84059ANDcreate_time2020-01-24 05:00:00;SELECT*FROMinformation_schema.OPTIMIZER_TRACE;SEToptimizer_traceenabledoff;输出的JSON中会列出每个可能索引的成本和最终选择的原因。例如当create_time条件较宽松时用create_time索引扫描成本可能高于全表扫描MySQL就会放弃索引。{index:create_time,ranges:[0x5e2a79d0 create_time],rows:23758,cost:28511,chosen:false,cause:cost}当调小时间范围后扫描行数减少成本降低MySQL就会选择索引。这个功能非常强大可以帮助我们理解为什么索引没被选中进而优化查询。4.4 人工干预force index如果统计信息不准或者你确定用索引更快可以使用FORCE INDEX强制走索引。SELECT*FROMpersonFORCEINDEX(name_score)WHEREnamename84059ANDcreate_time2020-01-24 05:00:00;但谨慎使用因为随着数据分布变化强制索引可能带来反效果。五、索引使用最佳实践总结按需创建索引不要提前优化等业务明确、数据量大再考虑。优先考虑联合索引多个条件查询时联合索引比多个单列索引更高效且可能实现索引覆盖。关注索引列顺序将最常用、区分度最高的列放在联合索引最左边。避免索引失效注意左模糊、函数操作、隐式转换等坑。监控索引使用情况用EXPLAIN和optimizer trace分析查询及时调整。考虑索引维护成本写入频繁的表索引不宜过多。适时清理无用索引定期检查删除重复或长期不用的索引。六、互动与思考你在工作中还遇到过哪些索引的“骚操作”或“奇葩坑”欢迎在评论区分享你的经历我们一起避坑
【后端开发踩坑记】7、面试官：索引不会用还敢说精通MySQL？这7个坑你踩过几个？
面试官索引不会用还敢说精通MySQL这7个坑你踩过几个索引不是万能药用错比不用更可怕从原理到实战带你避开MySQL索引的七个天坑。引言索引越多越好大错特错“这个查询太慢了给所有WHERE字段都加上索引吧”“不就是加个索引嘛分分钟搞定性能肯定飞起”这样的对话在无数开发团队中反复上演。然而索引真的是越多越好吗现实往往啪啪打脸加了索引后查询没变快插入反而变慢了明明建了索引执行计划却显示全表扫描数据量才几万索引文件比数据文件还大如果你也遇到过这些问题或者对索引的理解仅停留在“给查询字段加索引”的层面那么这篇文章正是为你准备的。今天我将带你从MySQL InnoDB的存储原理出发彻底搞懂索引的工作机制并用7个真实案例帮你避开索引使用的那些坑。全程高能建议收藏一、InnoDB数据存储原理不懂这个谈何索引1.1 页MySQL的最小存储单位InnoDB不会像我们想象的那样一行一行地读取磁盘数据。为了减少磁盘I/O它把数据划分为若干页每个页默认16KB。数据页之间组成双向链表页内记录按主键顺序组成单向链表。每个数据页内部有一个页目录它把记录分成多个组槽每组若干条记录。这样按主键搜索时可以先通过二分法定位到所在的槽然后在组内顺序查找大大加快了页内查找速度。下面是一个数据页的结构示意图渲染错误:Mermaid 渲染失败: Parse error on line 2: ... TD subgraph 数据页(16KB) direc ----------------------^ Expecting SEMI, NEWLINE, SPACE, EOF, GRAPH, DIR, subgraph, SQS, end, AMP, COLON, START_LINK, STYLE, LINKSTYLE, CLASSDEF, CLASS, CLICK, DOWN, UP, NUM, NODE_STRING, BRKT, MINUS, MULT, UNICODE_TEXT, got PS假设我们要查找PK15的记录流程如下二分页目录找到记录所在槽从槽指向的记录开始顺序向下查找3次找到目标这种结构使得页内查找非常高效但页之间呢如果表有成千上万个页如何快速定位记录所在的页这就轮到B树登场了。1.2 B树索引的基石InnoDB使用B树作为索引结构。B树是一棵“倒着长的树”所有真实数据都存在叶子节点上层节点只存放索引键和指向子节点的指针。数据页非叶子节点非叶子节点索引键:10指针→页10索引键:20指针→页20索引键:...索引键:5指针→页5索引键:15指针→页15索引键:25指针→页25PK1, data...PK4, data...PK7, data...PK12, data...PK15, data...PK18, data...聚簇索引InnoDB会自动使用主键作为聚簇索引的键。叶子节点保存整行数据。由于数据只有一份所以聚簇索引只能有一个。二级索引我们手动创建的索引非主键索引都是二级索引。它的叶子节点不保存数据只保存主键值。通过二级索引找到主键后还需要再到聚簇索引中查找完整数据这个过程叫回表。渲染错误:Mermaid 渲染失败: Parse error on line 2: ...TD subgraph 二级索引(name) N1[na ----------------------^ Expecting SEMI, NEWLINE, SPACE, EOF, GRAPH, DIR, subgraph, SQS, end, AMP, COLON, START_LINK, STYLE, LINKSTYLE, CLASSDEF, CLASS, CLICK, DOWN, UP, NUM, NODE_STRING, BRKT, MINUS, MULT, UNICODE_TEXT, got PS理解了B树和回表我们就能明白索引不是免费的午餐每一次查询都可能付出代价。二、索引的三大代价为什么不能乱加索引很多开发者以为索引就是“查询加速器”多多益善。实际上创建索引需要付出维护代价、空间代价、回表代价。2.1 维护代价写入变慢每张表如果有N个索引那么插入一条数据时不仅要更新聚簇索引还要更新N个二级索引的B树。这会导致写入速度直线下降。做个实验创建一个10万行的表person字段有id(主键)、name、score、create_time。无索引插入10万条记录耗时约140秒。创建两个索引(name,score)联合索引 create_time单独索引插入耗时增加到154秒。而且B树为了保持有序当页满时会发生页分裂删除数据时可能发生页合并这些都会带来额外的I/O开销甚至可能产生死锁。2.2 空间代价索引可能比数据还大二级索引虽然不保存完整数据但要保存索引列的值和主键占用的磁盘空间不容小觑。还是上面的person表两个索引占用的空间如下SELECTDATA_LENGTH,INDEX_LENGTHFROMinformation_schema.TABLESWHERETABLE_NAMEperson;结果数据大小DATA_LENGTH 4.7MB索引大小INDEX_LENGTH 8.4MB索引比数据还大如果你的表有几十个索引磁盘空间很快就会告急。2.3 回表代价额外的查询使用二级索引查询时如果SELECT的字段不在索引中就需要回表。回表意味着至少多一次磁盘I/O甚至更多因为可能跨多个数据页。我们通过EXPLAIN对比一下-- 需要回表EXPLAINSELECT*FROMpersonWHEREnamename1;-- Extra: (空)-- 索引覆盖查询字段都在索引中EXPLAINSELECTname,scoreFROMpersonWHEREnamename1;-- Extra: Using index索引覆盖避免了回表性能大幅提升。这也是为什么推荐只SELECT必要的字段并尽量利用联合索引覆盖查询。2.4 最佳实践不要过早建索引等业务稳定、数据量上万、查询变慢后再考虑。索引字段要轻量能用int就别用varchar能用前缀索引就别用全字段。用索引覆盖SELECT只取索引中包含的字段避免回表。三、索引失效的四大场景明明有索引为啥不走你辛辛苦苦建了索引MySQL却不领情执行计划显示全表扫描。这通常是因为你的查询写法导致索引失效。3.1 左模糊匹配EXPLAINSELECT*FROMpersonWHEREnameLIKE%name123%;B树索引是根据索引值排序的只能从左到右匹配。以通配符%开头无法确定前缀索引自然失效。对策尽量使用前缀匹配name LIKE name123%。如果必须后缀搜索可以考虑把数据反转存储或者使用全文搜索引擎如Elasticsearch。3.2 对索引列使用函数EXPLAINSELECT*FROMpersonWHERELENGTH(name)7;索引保存的是原始值经过函数计算后的值无法匹配。对策如果经常需要函数查询可以考虑创建函数索引MySQL 5.7支持虚拟列并对其创建索引或者提前计算好存入单独字段。3.3 联合索引未使用最左列假设有联合索引(name, score)-- 无法使用联合索引EXPLAINSELECT*FROMpersonWHEREscore90;-- 可以使用联合索引EXPLAINSELECT*FROMpersonWHEREnamename1ANDscore90;联合索引按照第一列排序第一列相同时才按第二列排。因此如果查询条件中没有第一列就无法利用索引。对策根据查询频率和选择性合理设计联合索引的列顺序。经常作为查询条件的列放在左边。3.4 类型隐式转换虽然原文没提但这也是常见的索引失效场景。-- 假设name是varchar类型但传入整数EXPLAINSELECT*FROMpersonWHEREname123;MySQL会将name隐式转换为整数进行比较导致索引失效相当于对索引列使用了函数。对策确保查询条件类型与字段类型一致避免隐式转换。索引失效场景左模糊匹配函数操作联合索引缺左列隐式类型转换解决方案前缀匹配/全文索引方案函数索引/预计算方案调整索引列顺序方案类型匹配四、MySQL基于成本决定是否走索引有时候即使查询能用到索引MySQL也可能选择全表扫描。为什么因为MySQL认为全表扫描比用索引更快。MySQL会基于成本来选择执行计划。成本主要包括I/O成本从磁盘加载数据页的成本默认读取一个页成本1.0CPU成本检测记录是否满足条件、排序等操作的成本默认检测一条记录成本0.24.1 全表扫描成本计算全表扫描的成本 ≈ 聚簇索引占用的页数 × 1.0 总记录数 × 0.2通过SHOW TABLE STATUS可以查看统计信息rows行数估算Data_length聚簇索引占用字节数除以16KB得页数对于person表rows≈100086Data_length≈4.7MB ≈ 289页所以全表扫描成本 ≈ 289×1.0 100086×0.2 ≈ 20306。4.2 索引扫描成本计算使用二级索引扫描时成本包括扫描二级索引页的I/O成本处理索引记录的CPU成本回表的I/O成本如果需要4.3 optimizer trace看透MySQL的选择MySQL提供了optimizer trace功能可以查看每个执行计划的成本明细。SEToptimizer_traceenabledon;SELECT*FROMpersonWHEREnamename84059ANDcreate_time2020-01-24 05:00:00;SELECT*FROMinformation_schema.OPTIMIZER_TRACE;SEToptimizer_traceenabledoff;输出的JSON中会列出每个可能索引的成本和最终选择的原因。例如当create_time条件较宽松时用create_time索引扫描成本可能高于全表扫描MySQL就会放弃索引。{index:create_time,ranges:[0x5e2a79d0 create_time],rows:23758,cost:28511,chosen:false,cause:cost}当调小时间范围后扫描行数减少成本降低MySQL就会选择索引。这个功能非常强大可以帮助我们理解为什么索引没被选中进而优化查询。4.4 人工干预force index如果统计信息不准或者你确定用索引更快可以使用FORCE INDEX强制走索引。SELECT*FROMpersonFORCEINDEX(name_score)WHEREnamename84059ANDcreate_time2020-01-24 05:00:00;但谨慎使用因为随着数据分布变化强制索引可能带来反效果。五、索引使用最佳实践总结按需创建索引不要提前优化等业务明确、数据量大再考虑。优先考虑联合索引多个条件查询时联合索引比多个单列索引更高效且可能实现索引覆盖。关注索引列顺序将最常用、区分度最高的列放在联合索引最左边。避免索引失效注意左模糊、函数操作、隐式转换等坑。监控索引使用情况用EXPLAIN和optimizer trace分析查询及时调整。考虑索引维护成本写入频繁的表索引不宜过多。适时清理无用索引定期检查删除重复或长期不用的索引。六、互动与思考你在工作中还遇到过哪些索引的“骚操作”或“奇葩坑”欢迎在评论区分享你的经历我们一起避坑
