1. 索引失效的常见场景从一次慢查询说起上周排查一个线上服务性能问题时遇到一个典型的索引失效案例。一个看似简单的用户订单查询在数据量增长到百万级后响应时间从几十毫秒飙升到十几秒。SQL语句看起来没什么问题WHERE子句的字段也建了索引但EXPLAIN一跑赫然显示type: ALL也就是全表扫描。这让我不得不停下手中的活重新审视那些让MySQL“放弃”索引的隐蔽陷阱。很多开发者包括一些有经验的同行都容易陷入一个误区只要给查询条件中的字段加上索引数据库就一定会用。实际上MySQL的查询优化器Optimizer是一个非常复杂的成本计算模型它基于统计信息、数据分布、查询写法等多种因素来估算不同执行路径的代价最终选择它认为成本最低的那一个。所谓“不走索引”很多时候是优化器经过计算后认为全表扫描反而比走索引回表再过滤更划算。理解这些场景不仅能帮助我们写出更高效的SQL更能让我们在数据库设计阶段就规避掉潜在的性能瓶颈。今天我们就来系统性地拆解一下MySQL在哪些情况下会选择“绕开”你精心创建的索引。这不仅仅是面试八股文更是每个后端和DBA必须掌握的实战经验。2. 数据类型不匹配与隐式类型转换这是索引失效最常见、也最容易被忽视的原因之一。当查询条件中字段的数据类型与传入值的数据类型不一致时MySQL会尝试进行隐式类型转换Implicit Type Conversion。一旦发生类型转换优化器通常就无法再使用该字段上的索引了。2.1 字符串与数字的“暧昧”关系假设我们有一张用户表users其中phone字段是VARCHAR(20)类型并且在这个字段上建立了索引。-- 表结构 CREATE TABLE users ( id INT PRIMARY KEY, phone VARCHAR(20), INDEX idx_phone (phone) ); -- 失效的查询传入数字 SELECT * FROM users WHERE phone 13800138000;在这个查询中phone字段是字符串类型但传入的条件13800138000是一个数字。为了进行比较MySQL必须将phone字段的每一行值都转换为数字或者将传入的数字13800138000转换为字符串。实际上在涉及数值和字符串比较时MySQL会倾向于将字符串转换为数值。这意味着对于表中的每一行MySQL都要执行一次CAST(phone AS UNSIGNED)的操作然后再与13800138000比较。由于索引是按照phone的原始字符串值排序的经过函数转换后的值已经破坏了索引的有序性因此优化器无法使用索引进行快速查找只能选择全表扫描。正确的写法应该是传入字符串-- 有效的查询传入字符串 SELECT * FROM users WHERE phone 13800138000;2.2 日期时间类型的陷阱日期时间类型也有类似的坑。假设有一个订单表create_time字段是DATETIME类型并建立了索引。CREATE TABLE orders ( id INT PRIMARY KEY, create_time DATETIME, INDEX idx_create_time (create_time) ); -- 失效的查询使用字符串日期范围查询但格式不匹配或函数操作 SELECT * FROM orders WHERE DATE(create_time) 2023-10-01;这里使用了DATE()函数来提取create_time的日期部分。一旦对索引字段使用了函数索引就失效了。因为索引存储的是2023-10-01 14:30:00这样的完整值而不是2023-10-01。优化器无法利用索引树的有序结构来快速定位所有日期为2023-10-01的行。正确的做法是使用范围查询-- 有效的查询利用索引的有序性进行范围扫描 SELECT * FROM orders WHERE create_time 2023-10-01 00:00:00 AND create_time 2023-10-02 00:00:00;注意隐式转换的规则比较复杂取决于MySQL的版本和SQL模式。一个基本原则是让传入值的类型与字段定义的类型严格一致。在编写Prepared Statement或使用ORM框架时要特别注意参数绑定时的类型。3. 索引列参与计算或使用函数延续上面的思路只要索引列不是以“裸奔”的形式出现在查询条件中而是被函数包裹或参与了运算那么索引大概率会失效。因为索引中存储的是列的原始值而不是计算后的值。3.1 算术运算-- 假设age字段是INT且有索引 CREATE TABLE employees ( id INT PRIMARY KEY, age INT, INDEX idx_age (age) ); -- 索引失效 SELECT * FROM employees WHERE age 1 30; -- 索引失效 SELECT * FROM employees WHERE age * 2 60;在这两个查询中为了判断条件是否成立MySQL需要先为每一行计算age 1或age * 2的值。这个计算过程发生在读取行数据之后或者在无法使用索引的情况下读取行数据之前索引无法提供基于计算结果的有序查找。正确的做法是将计算移到等式的另一边-- 索引有效 SELECT * FROM employees WHERE age 29; -- 因为 age 1 30 等价于 age 29 SELECT * FROM employees WHERE age 30; -- 因为 age * 2 60 等价于 age 303.2 字符串函数除了DATE()常见的LEFT()、SUBSTRING()、CONCAT()、UPPER()、LOWER()等函数也会导致索引失效。-- 假设name字段有索引 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), INDEX idx_name (name) ); -- 索引失效 SELECT * FROM products WHERE LEFT(name, 3) ABC; SELECT * FROM products WHERE UPPER(name) IPHONE;对于这种前缀匹配查询如果业务允许更好的方式是使用前缀索引配合LIKE-- 如果业务上就是查前三个字符可以建立前缀索引 CREATE INDEX idx_name_prefix ON products (name(3)); -- 然后使用LIKE此时前缀索引可能生效取决于优化器选择 SELECT * FROM products WHERE name LIKE ABC%;关于UPPER()这类大小写转换需求更根本的解决方法是存储时就统一大小写或者使用COLLATE设置不区分大小写的校对规则从而避免在查询时使用函数。3.3 为什么优化器“算不过来”你可能会想优化器难道不能聪明一点把age 1 30重写为age 29吗对于这种简单的线性运算理论上是可以的但MySQL的优化器目前还不会对所有表达式进行这种等价重写。更重要的是对于复杂的函数或自定义函数优化器根本无法推导其逆运算。因此最安全的做法就是确保索引列单独出现在条件的一侧。4. 前导模糊查询 LIKE ‘%xxx’模糊查询LIKE是索引失效的重灾区其是否使用索引完全取决于通配符%的位置。-- 假设title字段有索引 CREATE TABLE articles ( id INT PRIMARY KEY, title VARCHAR(200), INDEX idx_title (title) ); -- 情况一前缀匹配索引可能有效Range Scan SELECT * FROM articles WHERE title LIKE MySQL%; -- 情况二后缀匹配索引失效Full Table Scan SELECT * FROM articles WHERE title LIKE %优化; -- 情况三前后均匹配索引失效Full Table Scan SELECT * FROM articles WHERE title LIKE %索引%;原理分析B树索引是一种有序的数据结构它按照索引字段的值进行排序。当进行LIKE MySQL%查询时优化器知道索引中所有以MySQL开头的值都是连续存储的。它可以在索引树中快速定位到第一个以MySQL开头的条目然后沿着叶子节点的链表向后扫描直到遇到第一个不以MySQL开头的条目为止。这是一个非常高效的范围扫描Range Scan。然而对于LIKE %优化这意味着要查找所有以优化结尾的字符串。索引的有序性是基于整个字符串的而不是基于后缀。性能优化和查询优化在索引中可能相隔甚远。优化器没有办法利用索引的有序性来快速定位这些行它只能扫描全部索引条目如果选择覆盖索引扫描或者全表数据对每一行的title值计算LIKE %优化。当数据量很大时这种开销是无法接受的。实战心得遇到必须使用后缀模糊查询的场景例如搜索商品后缀编号可以考虑以下方案反向存储并建立索引新增一个字段reverse_title存储title的反转字符串并为其建立索引。查询时用WHERE reverse_title LIKE 化优%。这本质上是将后缀匹配转换成了前缀匹配。使用全文索引对于文本内容的模糊搜索LIKE %keyword%是性能杀手。MySQL提供了全文索引FULLTEXT INDEX仅适用于InnoDB和MyISAM的CHAR、VARCHAR、TEXT列专门为这种场景优化。使用MATCH(column) AGAINST(keyword)进行查询效率远高于LIKE。引入搜索引擎对于复杂的搜索需求分词、同义词、权重排序等应使用Elasticsearch、Solr等专业搜索引擎将数据库从繁重的搜索任务中解放出来。5. OR 连接条件与索引选择性使用OR连接多个条件时索引的使用情况会变得复杂并非简单地“有一个条件能用索引就行”。5.1 OR 导致的全表扫描CREATE TABLE user_logs ( id INT PRIMARY KEY, user_id INT, ip_address VARCHAR(45), action VARCHAR(50), INDEX idx_user_id (user_id), INDEX idx_ip_address (ip_address) ); -- 假设这个查询会导致全表扫描 SELECT * FROM user_logs WHERE user_id 1001 OR ip_address 192.168.1.1;在这个查询中user_id和ip_address分别都有单列索引。你可能会期望优化器分别使用两个索引进行查找然后将结果合并Index Merge。但在很多情况下优化器会直接选择全表扫描。为什么呢成本估算优化器需要估算两种路径的成本路径A全表扫描成本 读取全表所有数据页的IO成本。路径BIndex Merge成本 通过idx_user_id查找user_id1001的IO成本 通过idx_ip_address查找ip_address192.168.1.1的IO成本 将两个结果集去重合并的CPU成本。 如果user_id1001的记录非常多或者ip_address192.168.1.1的记录非常多或者两者都多那么Index Merge的合并去重成本可能会很高导致优化器认为全表扫描更划算。索引选择性差如果user_id或ip_address字段的区分度很低例如action字段只有‘login’‘logout’几个值那么基于该索引查出来的数据量会非常大回表成本激增优化器也会倾向于全表扫描。5.2 如何优化 OR 查询使用 UNION ALL 改写这是最有效、最稳定的优化手段。将OR拆分成多个查询的UNION。SELECT * FROM user_logs WHERE user_id 1001 UNION ALL SELECT * FROM user_logs WHERE ip_address 192.168.1.1 AND user_id ! 1001;注意第二个查询加上了AND user_id ! 1001这是为了排除在第一个查询中已经找到的重复行如果user_id1001且ip_address192.168.1.1的记录会被两个子查询同时查到。使用UNION ALL比UNION效率高因为它不去重。如果确定两个结果集没有交集或者允许有少量重复可以直接用UNION ALL。这样每个子查询都可以高效地使用各自的索引。评估 Index Merge可以通过EXPLAIN查看优化器是否选择了Index Merge。如果选择了观察type列是否为index_merge以及Extra列是否出现Using union(...)。在MySQL 5.6及以后版本Index Merge优化是默认开启的但它并不总是最优选择。有时通过optimizer_switch会话变量临时关闭它迫使优化器选择UNION改写后的路径可能性能更好。考虑复合索引如果OR两边的字段经常同时被查询且逻辑上相关可以考虑建立一个包含这两个字段的复合索引。但这对OR查询本身帮助有限因为复合索引对于WHERE user_id A OR ip_address B这样的条件依然可能不如UNION高效。复合索引更擅长优化AND条件。个人踩坑记录曾经有一个实时统计接口用了WHERE statussuccess OR error_code IS NULL。status字段有索引但error_code没有。上线初期数据量小没问题数据量上来后接口超时。用EXPLAIN一看全表扫描。原因是OR右边条件无索引导致整个OR条件无法使用任何索引。最终用UNION改写并给error_code加了索引性能提升百倍。记住一个原则OR两边的条件最好都要有索引否则极易导致全表扫描。6. 复合索引的最左前缀匹配原则这是理解复合索引联合索引如何工作的核心。复合索引idx_a_b_c (a, b, c)其索引项是按照a、b、c的顺序排序的先按a排序a相同再按b排序b相同再按c排序。6.1 有效与无效的查询场景我们通过一个表格来直观展示查询条件是否使用索引使用部分说明WHERE a 1是a完美匹配最左列WHERE a 1 AND b 2是a, b匹配前两列WHERE a 1 AND b 2 AND c 3是a, b, c匹配所有列WHERE b 2否-违反最左前缀。索引树首先按a组织不知道b2的记录分布在哪里。WHERE b 2 AND c 3否-违反最左前缀。缺少最左列a。WHERE a 1 AND c 3是部分a匹配到最左列a但无法利用c列进行索引过滤跳跃了b。找到所有a1的记录后需要回表或过滤c3。WHERE a 1是a范围查询可利用a列进行索引范围扫描。WHERE a 1 AND b 2是a, b匹配a的等值查询和b的范围查询。WHERE a 1 AND b 2是部分a范围查询a之后b在索引中不再是全局有序的仅在a相同的局部范围内有序因此b2无法作为索引过滤条件只能作为回表后的过滤条件。6.2 范围查询导致的后缀列失效最后一行是特别容易出错的地方。对于idx_a_b_c (a, b, c)-- 这个查询只能用到索引的 (a) 列进行范围扫描b和c无法用于索引过滤。 SELECT * FROM table WHERE a 10 AND b 20 AND c 30;执行过程是利用索引找到第一个a 10的记录然后向后扫描所有a 10的索引条目。由于a是范围查询在a 10这个范围内b的值并不是有序的例如(11,1, ...),(11,5, ...),(12,1, ...)所以无法快速定位b20的位置b20和c30这两个条件只能在回表后或索引扫描后进行过滤。如何设计复合索引一个实用的口诀是等值查询列在前范围查询列在后选择性高的列在前。针对上面的查询如果b和c是等值查询a是范围查询更好的索引顺序可能是idx_b_c_a (b, c, a)。这样就能利用b20 AND c30进行精确的等值查找然后再从结果中过滤a 10。7. 索引选择性太差与优化器成本估算即使查询写法完全正确字段也建立了索引MySQL也可能不走索引。核心原因在于优化器认为走索引的成本高于全表扫描的成本。7.1 什么是索引选择性索引选择性Selectivity是指不重复的索引值基数Cardinality与表总记录数#T的比值选择性 基数 / #T。 选择性越高索引的价值越大。唯一索引的选择性是1这是最好的情况。假设一张users表有100万行数据gender字段‘M‘ ’F‘的基数约为2选择性为 2/1,000,000 0.000002。非常差。user_id字段唯一的基数为1,000,000选择性为 1。非常好。7.2 优化器如何做选择优化器通过以下步骤估算成本全表扫描成本主要是IO成本即读取所有数据页所需的代价。索引扫描成本索引查找成本从索引树根节点查找到叶子节点中第一条符合条件的记录所需的IO和CPU成本。回表成本根据索引中的主键ID回表随机IO读取完整数据行的成本。这取决于预估的需要回表的记录数。过滤成本对回表后的数据应用其他查询条件进行过滤的CPU成本。当优化器估算出需要回表的记录数占全表比例非常大时例如超过20%-30%这个阈值受innodb_stats_sample_pages等参数影响随机IO的成本会变得非常高可能超过顺序读取全表的成本。此时优化器就会选择全表扫描。7.3 一个典型的例子查询状态为“进行中”的订单CREATE TABLE orders ( id INT PRIMARY KEY, status TINYINT COMMENT 1:待支付 2:进行中 3:已完成 4:已取消, INDEX idx_status (status) ); -- 表中 90% 的订单状态都是 2进行中 SELECT * FROM orders WHERE status 2;在这个场景下status2的记录占了90%。虽然status字段有索引但优化器通过统计信息可以通过SHOW INDEX FROM orders查看Cardinality知道通过索引查找到所有status2的记录后需要回表读取几乎整个表的数据。这会产生大量的随机IO成本远高于直接顺序扫描整个表全表扫描是顺序IO效率更高。因此优化器明智地选择了全表扫描。怎么办接受优化器的选择在这种情况下全表扫描确实是更优的执行计划。强制使用索引FORCE INDEX反而会降低性能。使用覆盖索引如果查询只需要返回id和status字段可以创建一个包含这两个字段的覆盖索引(status, id)。这样查询只需要扫描索引无需回表成本大大降低优化器就会选择走索引。SELECT id, status FROM orders WHERE status 2; -- 覆盖索引 (status, id) 生效优化数据分布从业务上思考为什么“进行中”状态这么多是否可以引入更细粒度的状态如“待发货”、“已发货”或者将历史完成订单归档到另一张表来改善当前表的数据分布提高索引选择性。8. 其他导致索引失效的边角情况除了上述主要场景还有一些细节需要注意。8.1 使用 NOT、!、 运算符SELECT * FROM table WHERE column ! value; SELECT * FROM table WHERE column NOT IN (1,2,3); SELECT * FROM table WHERE column IS NOT NULL; -- 如果column是索引列且允许NULL此查询可能走索引也可能不走取决于数据分布对于!或NOT IN优化器通常认为需要检查大部分数据行因此倾向于全表扫描。IS NOT NULL类似如果表中该字段为NULL的记录很少走索引可能划算如果大部分都是NULL全表扫描更划算。8.2 使用 IN 与 NOT IN 的差异IN查询通常是可以用到索引的尤其是当IN列表中的值很多时优化器可能会将其视为多个等值查询的OR并可能采用Index Range Scan。 而NOT IN则很难使用索引原因同上。8.3 索引列使用 IS NULL 查询对于允许为NULL的索引列查询WHERE column IS NULL是可以使用索引的如果NULL值很少优化器可能选择索引。但查询WHERE column IS NOT NULL则不一定同样取决于数据分布。8.4 查询条件中使用了“OR”连接了非索引列如前所述如果OR的一边涉及没有索引的列优化器通常会对整个条件放弃使用索引。8.5 表数据量过小当表中数据量非常少比如只有几页的时候全表扫描的IO成本可能低于走索引再回表的随机IO成本。优化器会直接选择全表扫描。这是合理的不要为此担心。9. 诊断工具EXPLAIN 详解理论说了这么多实战中如何判断索引是否生效答案就是EXPLAIN命令。它展示了MySQL优化器为SQL语句选择的执行计划。9.1 关键字段解读执行EXPLAIN SELECT ...重点关注以下几列type访问类型从好到坏大致是systemconsteq_refrefrangeindexALLconst通过主键或唯一索引一次就找到一行。ref使用非唯一索引进行等值查找。range使用索引进行范围查找BETWEEN,,,IN,LIKE prefix%。index全索引扫描遍历整个索引树通常比ALL快因为索引文件通常比数据文件小。ALL全表扫描。我们的目标就是避免出现ALL。possible_keys查询可能用到的索引。key查询实际用到的索引。如果为NULL说明没用到索引。key_len使用的索引的长度字节数。可以用来判断复合索引使用了哪几部分。rowsMySQL预估需要扫描的行数。一个重要的参考值。Extra额外信息包含很多重要细节Using index使用了覆盖索引查询的列都在索引中无需回表。性能最佳信号之一。Using where在存储引擎检索行后MySQL服务器层进行了额外的过滤。如果type是ALL且Using where说明是全表扫描后再过滤性能差。Using index condition索引条件下推ICP5.6后引入的优化将WHERE条件中索引列的过滤部分下推到存储引擎层执行减少回表次数。Using filesort需要额外的排序操作可能意味着ORDER BY的字段没有用上索引。Using temporary需要创建临时表来处理查询常见于GROUP BY和ORDER BY子句的列不同。9.2 一个完整的诊断案例假设我们有慢查询SELECT * FROM orders WHERE user_id 100 AND amount 500 ORDER BY create_time DESC LIMIT 10;我们怀疑索引没用好。首先用EXPLAIN查看EXPLAIN SELECT * FROM orders WHERE user_id 100 AND amount 500 ORDER BY create_time DESC LIMIT 10;假设输出如下idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEordersALLidx_user_idNULLNULL1000000Using where; Using filesort分析type: ALL最坏的情况全表扫描。key: NULL没有使用任何索引。rows: 1000000预估扫描100万行。Extra: Using where; Using filesort服务器层对扫描出的所有行进行amount 500的过滤并且还在内存或磁盘上对结果进行了排序因为ORDER BY create_time。结论当前索引idx_user_id (user_id)无法支撑这个查询。虽然user_id是等值查询但amount是范围查询create_time需要排序。优化器认为通过idx_user_id找到所有user_id100的记录后还需要回表过滤amount并排序成本可能很高于是选择了全表扫描。优化方案创建一个复合索引(user_id, amount, create_time)。user_id用于等值过滤。amount用于范围过滤。虽然范围查询amount 500之后的列create_time索引失效但我们可以利用LIMIT 10和排序。create_time用于避免filesort。注意由于amount是范围查询create_time在索引中无法用于直接过滤但索引本身是按照(user_id, amount, create_time)排序的。对于user_id100且amount500的所有记录它们在索引中是按照create_time局部有序的在user_id和amount相同的分组内。优化器可以利用这个顺序先通过索引找到符合user_id100 AND amount500的第一条记录然后沿着索引顺序扫描直到找到10条满足条件的记录为止。这通常比全表扫描后再排序快得多。创建索引后再次EXPLAIN可能会看到type: rangekey: idx_user_amount_timeExtra: Using index condition。Using filesort消失。这就是一个成功的优化。10. 总结与最佳实践索引是一把双刃剑用得好可以极大提升性能用不好或设计不当反而会成为负担。回顾一下要让MySQL心甘情愿地使用你的索引需要避开以下陷阱保持类型一致确保WHERE条件中的值与列定义类型相同避免隐式转换。让索引列“独立”不要对索引列使用函数或进行运算。谨慎使用LIKE前缀匹配LIKE abc%才能有效利用索引后缀和全模糊匹配应考虑其他方案反向索引、全文索引、搜索引擎。小心OR操作符确保OR两边的条件都有索引否则考虑用UNION ALL改写。理解复合索引的最左前缀设计索引时将等值查询和高选择性的列放在左边。范围查询列放最后在复合索引中范围查询,,BETWEEN,LIKE后面的列无法用于索引过滤。关注索引选择性不要为选择性极差的列创建单列索引如性别、状态除非结合其他列创建复合索引或用于覆盖查询。善用覆盖索引如果查询只需要返回索引包含的列尽量使用覆盖索引避免回表。使用EXPLAIN验证任何性能相关的SQL调整都必须用EXPLAIN查看执行计划不要凭感觉。理解优化器的成本模型不走索引不一定是错误可能是优化器基于统计信息做出的更优选择。强制使用索引USE INDEX/FORCE INDEX要非常谨慎最好在业务高低峰期分别测试验证。最后索引优化是一个持续的过程需要结合具体的业务查询模式、数据量和增长趋势来综合考虑。没有一劳永逸的银弹只有对原理的深刻理解和对业务的持续关注才能打造出高效稳定的数据库系统。