你这份总结整体是对的我按“为什么会失效”来解释一遍。核心记住一句话索引能不能用本质看 MySQL 能不能直接拿索引树里的值去定位数据。如果你把索引列“加工”了或者查询条件不符合索引树的排序规则MySQL 就很难走索引。1. 对索引字段使用函数比如select*fromt_userwheresubstring(name,1,1)张;假设name有索引。索引树中存的是原始的name值比如张三 李四 王五但是你的查询条件不是name张三而是substring(name,1,1)张这意味着 MySQL 需要对每一行的name执行函数然后判断结果是不是张。也就是说它不能直接在索引树中查找只能把数据一条条拿出来算substring(张三, 1, 1) substring(李四, 1, 1) substring(王五, 1, 1)所以索引容易失效。类似的还有wheredate(create_time)2026-05-27wherelower(username)tomwhereyear(create_time)2026更推荐写法是wherecreate_time2026-05-27 00:00:00andcreate_time2026-05-28 00:00:00这样create_time没有被函数包裹索引可以正常使用。2. 对索引字段使用表达式计算比如select*fromt_userwhereage119;假设age有索引。虽然这个条件等价于whereage18;但是 MySQL 看到的是age119它需要先对每一行的age做计算18 1 20 1 30 1再判断是否等于 19。所以不容易直接走age索引。更推荐写法select*fromt_userwhereage18;再比如whereid*210应该改成whereid5总结一下where索引列某个值通常能走索引。where索引列计算某个值容易导致索引失效。3. 隐式类型转换导致索引失效你举的例子很好select*fromt_userwherephone1300000001;假设phone是varchar类型并且有索引。但是你查询的时候写的是数字没有加引号。MySQL 在比较字符串和数字时会把字符串转成数字再比较。所以它可能相当于执行select*fromt_userwhereCAST(phoneASsignedint)1300000001;也就是说索引字段phone被函数转换了。一旦对索引字段做了转换就容易导致索引失效。正确写法应该是select*fromt_userwherephone1300000001;这里有一个面试重点字段是什么类型查询条件就尽量用什么类型。如果字段是varchar条件值就加引号如果字段是int条件值就不要加引号。4. Like 以通配符开头假设name有索引。可以走索引的情况select*fromt_userwherenamelike张%;因为它表示查找所有以“张”开头的数据。索引树中的字符串是按顺序排列的比如张三 张伟 张敏 李四 王五MySQL 可以定位到“张”开头的位置然后向后扫描。但是如果写成select*fromt_userwherenamelike%三;或者select*fromt_userwherenamelike%三%;这就不行了。因为%三表示前面是什么不知道只要最后是“三”就行。比如张三 王三 李三 赵三MySQL 无法根据索引树的前缀顺序快速定位只能一个个判断所以索引容易失效。总结likeabc%-- 可以利用索引like%abc-- 一般不能利用索引like%abc%-- 一般不能利用索引5. 联合索引与最左前缀法则假设有联合索引index(a,b,c)它不是给a、b、c各自建了三个独立索引而是建立了一棵联合索引树。这棵树的排序规则是先按 a 排序 a 相同再按 b 排序 b 相同再按 c 排序类似这样a b c 1 1 1 1 1 2 1 2 1 1 2 3 2 1 1 2 2 1 3 1 1所以联合索引(a, b, c)的核心规则是必须先有 a才能进一步利用 b必须先有 a、b才能进一步利用 c。这些可以用到联合索引wherea1;可以用因为从最左边的a开始了。wherea1andb2;可以用符合a - b的顺序。wherea1andb2andc3;可以用完整符合a - b - c。whereb2anda1;也可以用。因为 SQL 里条件顺序不重要优化器会帮你调整。它本质还是wherea1andb2;所以能用。wherec3andb2anda1;也可以用本质还是同时有a、b、c。wherea1andc3;这个要注意。它可以用到联合索引中的a但是通常用不到b因为中间缺了b。也就是说不是整个联合索引完全失效而是a 可以用 c 一般不能用于精准定位所以更准确地说wherea1andc3;索引不会完全失效但只能利用到 a 这一部分。这些不符合最左前缀法则whereb2;不行因为缺少最左边的a。wherec3;不行因为缺少a和b。whereb2andc3;也不行因为还是缺少a。原因是联合索引树首先按照a排序。如果你不提供aMySQL 就无法确定从索引树的哪个范围开始找。举个简单类比通讯录按照姓氏 - 名字 - 年龄排序。如果你知道姓氏是“张”就很容易找到所有张姓的人。如果你知道“姓张名字叫三”就更容易找。但如果你只知道“名字叫三”不知道姓氏那就很难根据这个排序快速定位。因为名字叫“三”的人可能分散在不同姓氏下面。联合索引也是一样。6. OR 条件导致索引失效比如select*fromt_userwhereid1orage18;假设id 有索引 age 没有索引那么问题来了id1这部分可以走索引。但是age18这部分不能走索引。因为是OR表示只要满足其中一个条件就可以。MySQL 必须找出id 1 的数据 或者 age 18 的数据但age没有索引它还是要全表扫描去找age 18的数据。既然都要全表扫描了MySQL 可能就干脆不走id的索引了直接全表扫描。所以总结whereid1orage18;如果id有索引age没有索引容易导致索引失效。如果id和age都有索引MySQL 有可能使用索引合并但不一定每次都会用最终还是要看优化器判断成本。最后帮你整理成面试版索引失效常见情况有对索引列使用函数wheredate(create_time)2026-05-27对索引列进行表达式计算whereage118发生隐式类型转换phone 是varcharwherephone1300000001应该写成wherephone1300000001like 以%开头wherenamelike%张wherenamelike%张%联合索引不符合最左前缀法则联合索引(a, b, c)必须从a开始使用。whereb2wherec3whereb2andc3都不符合。OR 两边有一边没有索引whereid1orage18如果age没索引可能导致全表扫描。你可以这样理解索引失效索引就像一本按规则排序的字典。只要查询条件破坏了这个排序规则或者让 MySQL 不能直接按索引值查找就可能导致索引失效。
【Mysql】索引失效的原因
发布时间：2026/5/30 8:13:19
你这份总结整体是对的我按“为什么会失效”来解释一遍。核心记住一句话索引能不能用本质看 MySQL 能不能直接拿索引树里的值去定位数据。如果你把索引列“加工”了或者查询条件不符合索引树的排序规则MySQL 就很难走索引。1. 对索引字段使用函数比如select*fromt_userwheresubstring(name,1,1)张;假设name有索引。索引树中存的是原始的name值比如张三 李四 王五但是你的查询条件不是name张三而是substring(name,1,1)张这意味着 MySQL 需要对每一行的name执行函数然后判断结果是不是张。也就是说它不能直接在索引树中查找只能把数据一条条拿出来算substring(张三, 1, 1) substring(李四, 1, 1) substring(王五, 1, 1)所以索引容易失效。类似的还有wheredate(create_time)2026-05-27wherelower(username)tomwhereyear(create_time)2026更推荐写法是wherecreate_time2026-05-27 00:00:00andcreate_time2026-05-28 00:00:00这样create_time没有被函数包裹索引可以正常使用。2. 对索引字段使用表达式计算比如select*fromt_userwhereage119;假设age有索引。虽然这个条件等价于whereage18;但是 MySQL 看到的是age119它需要先对每一行的age做计算18 1 20 1 30 1再判断是否等于 19。所以不容易直接走age索引。更推荐写法select*fromt_userwhereage18;再比如whereid*210应该改成whereid5总结一下where索引列某个值通常能走索引。where索引列计算某个值容易导致索引失效。3. 隐式类型转换导致索引失效你举的例子很好select*fromt_userwherephone1300000001;假设phone是varchar类型并且有索引。但是你查询的时候写的是数字没有加引号。MySQL 在比较字符串和数字时会把字符串转成数字再比较。所以它可能相当于执行select*fromt_userwhereCAST(phoneASsignedint)1300000001;也就是说索引字段phone被函数转换了。一旦对索引字段做了转换就容易导致索引失效。正确写法应该是select*fromt_userwherephone1300000001;这里有一个面试重点字段是什么类型查询条件就尽量用什么类型。如果字段是varchar条件值就加引号如果字段是int条件值就不要加引号。4. Like 以通配符开头假设name有索引。可以走索引的情况select*fromt_userwherenamelike张%;因为它表示查找所有以“张”开头的数据。索引树中的字符串是按顺序排列的比如张三 张伟 张敏 李四 王五MySQL 可以定位到“张”开头的位置然后向后扫描。但是如果写成select*fromt_userwherenamelike%三;或者select*fromt_userwherenamelike%三%;这就不行了。因为%三表示前面是什么不知道只要最后是“三”就行。比如张三 王三 李三 赵三MySQL 无法根据索引树的前缀顺序快速定位只能一个个判断所以索引容易失效。总结likeabc%-- 可以利用索引like%abc-- 一般不能利用索引like%abc%-- 一般不能利用索引5. 联合索引与最左前缀法则假设有联合索引index(a,b,c)它不是给a、b、c各自建了三个独立索引而是建立了一棵联合索引树。这棵树的排序规则是先按 a 排序 a 相同再按 b 排序 b 相同再按 c 排序类似这样a b c 1 1 1 1 1 2 1 2 1 1 2 3 2 1 1 2 2 1 3 1 1所以联合索引(a, b, c)的核心规则是必须先有 a才能进一步利用 b必须先有 a、b才能进一步利用 c。这些可以用到联合索引wherea1;可以用因为从最左边的a开始了。wherea1andb2;可以用符合a - b的顺序。wherea1andb2andc3;可以用完整符合a - b - c。whereb2anda1;也可以用。因为 SQL 里条件顺序不重要优化器会帮你调整。它本质还是wherea1andb2;所以能用。wherec3andb2anda1;也可以用本质还是同时有a、b、c。wherea1andc3;这个要注意。它可以用到联合索引中的a但是通常用不到b因为中间缺了b。也就是说不是整个联合索引完全失效而是a 可以用 c 一般不能用于精准定位所以更准确地说wherea1andc3;索引不会完全失效但只能利用到 a 这一部分。这些不符合最左前缀法则whereb2;不行因为缺少最左边的a。wherec3;不行因为缺少a和b。whereb2andc3;也不行因为还是缺少a。原因是联合索引树首先按照a排序。如果你不提供aMySQL 就无法确定从索引树的哪个范围开始找。举个简单类比通讯录按照姓氏 - 名字 - 年龄排序。如果你知道姓氏是“张”就很容易找到所有张姓的人。如果你知道“姓张名字叫三”就更容易找。但如果你只知道“名字叫三”不知道姓氏那就很难根据这个排序快速定位。因为名字叫“三”的人可能分散在不同姓氏下面。联合索引也是一样。6. OR 条件导致索引失效比如select*fromt_userwhereid1orage18;假设id 有索引 age 没有索引那么问题来了id1这部分可以走索引。但是age18这部分不能走索引。因为是OR表示只要满足其中一个条件就可以。MySQL 必须找出id 1 的数据 或者 age 18 的数据但age没有索引它还是要全表扫描去找age 18的数据。既然都要全表扫描了MySQL 可能就干脆不走id的索引了直接全表扫描。所以总结whereid1orage18;如果id有索引age没有索引容易导致索引失效。如果id和age都有索引MySQL 有可能使用索引合并但不一定每次都会用最终还是要看优化器判断成本。最后帮你整理成面试版索引失效常见情况有对索引列使用函数wheredate(create_time)2026-05-27对索引列进行表达式计算whereage118发生隐式类型转换phone 是varcharwherephone1300000001应该写成wherephone1300000001like 以%开头wherenamelike%张wherenamelike%张%联合索引不符合最左前缀法则联合索引(a, b, c)必须从a开始使用。whereb2wherec3whereb2andc3都不符合。OR 两边有一边没有索引whereid1orage18如果age没索引可能导致全表扫描。你可以这样理解索引失效索引就像一本按规则排序的字典。只要查询条件破坏了这个排序规则或者让 MySQL 不能直接按索引值查找就可能导致索引失效。
