企业官网建设流程全解析

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 = 30

3.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 '%优化'。当数据量很大时，这种开销是无法接受的。

实战心得：遇到必须使用后缀模糊查询的场景（例如搜索商品后缀编号），可以考虑以下方案：

1. 反向存储并建立索引：新增一个字段reverse_title，存储title的反转字符串，并为其建立索引。查询时，用WHERE reverse_title LIKE '化优%'。这本质上是将后缀匹配转换成了前缀匹配。
2. 使用全文索引：对于文本内容的模糊搜索，LIKE '%keyword%'是性能杀手。MySQL提供了全文索引（FULLTEXT INDEX，仅适用于InnoDB和MyISAM的CHAR、VARCHAR、TEXT列），专门为这种场景优化。使用MATCH(column) AGAINST('keyword')进行查询，效率远高于LIKE。
3. 引入搜索引擎：对于复杂的搜索需求（分词、同义词、权重排序等），应使用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）。但在很多情况下，优化器会直接选择全表扫描。为什么呢？

1. 成本估算：优化器需要估算两种路径的成本：

   • 路径A（全表扫描）：成本 = 读取全表所有数据页的IO成本。
   • 路径B（Index Merge）：成本 = 通过idx_user_id查找user_id=1001的IO成本 + 通过idx_ip_address查找ip_address='192.168.1.1'的IO成本 + 将两个结果集去重合并的CPU成本。 如果user_id=1001的记录非常多，或者ip_address='192.168.1.1'的记录非常多，或者两者都多，那么Index Merge的合并去重成本可能会很高，导致优化器认为全表扫描更划算。

2. 索引选择性差：如果user_id或ip_address字段的区分度很低（例如，action字段只有‘login’，‘logout’几个值），那么基于该索引查出来的数据量会非常大，回表成本激增，优化器也会倾向于全表扫描。

5.2 如何优化 OR 查询？

1. 使用 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_id=1001且ip_address='192.168.1.1'的记录会被两个子查询同时查到）。使用UNION ALL比UNION效率高，因为它不去重。如果确定两个结果集没有交集，或者允许有少量重复，可以直接用UNION ALL。这样，每个子查询都可以高效地使用各自的索引。

2. 评估 Index Merge：可以通过EXPLAIN查看优化器是否选择了Index Merge。如果选择了，观察type列是否为index_merge，以及Extra列是否出现Using union(...)。在MySQL 5.6及以后版本，Index Merge优化是默认开启的，但它并不总是最优选择。有时通过optimizer_switch会话变量临时关闭它，迫使优化器选择UNION改写后的路径，可能性能更好。

3. 考虑复合索引：如果OR两边的字段经常同时被查询，且逻辑上相关，可以考虑建立一个包含这两个字段的复合索引。但这对OR查询本身帮助有限，因为复合索引对于WHERE user_id = A OR ip_address = B这样的条件，依然可能不如UNION高效。复合索引更擅长优化AND条件。

个人踩坑记录：曾经有一个实时统计接口，用了WHERE status='success' 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 有效与无效的查询场景

我们通过一个表格来直观展示：

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, ...)），所以无法快速定位b=20的位置，b=20和c=30这两个条件只能在回表后（或索引扫描后）进行过滤。

如何设计复合索引？一个实用的口诀是：等值查询列在前，范围查询列在后，选择性高的列在前。针对上面的查询，如果b和c是等值查询，a是范围查询，更好的索引顺序可能是idx_b_c_a (b, c, a)。这样就能利用b=20 AND c=30进行精确的等值查找，然后再从结果中过滤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 优化器如何做选择？

优化器通过以下步骤估算成本：

1. 全表扫描成本：主要是IO成本，即读取所有数据页所需的代价。
2. 索引扫描成本：
   • 索引查找成本：从索引树根节点查找到叶子节点中第一条符合条件的记录所需的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;

在这个场景下，status=2的记录占了90%。虽然status字段有索引，但优化器通过统计信息（可以通过SHOW INDEX FROM orders查看Cardinality）知道，通过索引查找到所有status=2的记录后，需要回表读取几乎整个表的数据。这会产生大量的随机IO，成本远高于直接顺序扫描整个表（全表扫描是顺序IO，效率更高）。因此，优化器明智地选择了全表扫描。

怎么办？

1. 接受优化器的选择：在这种情况下，全表扫描确实是更优的执行计划。强制使用索引（FORCE INDEX）反而会降低性能。
2. 使用覆盖索引：如果查询只需要返回id和status字段，可以创建一个包含这两个字段的覆盖索引(status, id)。这样，查询只需要扫描索引，无需回表，成本大大降低，优化器就会选择走索引。

   SELECT id, status FROM orders WHERE status = 2; -- 覆盖索引 (status, id) 生效

3. 优化数据分布：从业务上思考，为什么“进行中”状态这么多？是否可以引入更细粒度的状态（如“待发货”、“已发货”），或者将历史完成订单归档到另一张表，来改善当前表的数据分布，提高索引选择性。

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 ...，重点关注以下几列：

1. type：访问类型，从好到坏大致是：

   • system>const>eq_ref>ref>range>index>ALL
   • const：通过主键或唯一索引一次就找到一行。
   • ref：使用非唯一索引进行等值查找。
   • range：使用索引进行范围查找（BETWEEN,>,<,IN,LIKE 'prefix%'）。
   • index：全索引扫描（遍历整个索引树，通常比ALL快因为索引文件通常比数据文件小）。
   • ALL：全表扫描。我们的目标就是避免出现ALL。

2. possible_keys：查询可能用到的索引。

3. key：查询实际用到的索引。如果为NULL，说明没用到索引。

4. key_len：使用的索引的长度（字节数）。可以用来判断复合索引使用了哪几部分。

5. rows：MySQL预估需要扫描的行数。一个重要的参考值。

6. Extra：额外信息，包含很多重要细节：

   • Using index：使用了覆盖索引，查询的列都在索引中，无需回表。性能最佳信号之一。
   • Using where：在存储引擎检索行后，MySQL服务器层进行了额外的过滤。如果type是ALL且Using where，说明是全表扫描后再过滤，性能差。
   • Using index condition：索引条件下推（ICP），5.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;

假设输出如下：

分析：

• 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_id=100的记录后，还需要回表过滤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_id=100且amount>500的所有记录，它们在索引中是按照create_time局部有序的（在user_id和amount相同的分组内）。优化器可以利用这个顺序，先通过索引找到符合user_id=100 AND amount>500的第一条记录，然后沿着索引顺序扫描，直到找到10条满足条件的记录为止。这通常比全表扫描后再排序快得多。

创建索引后再次EXPLAIN，可能会看到：type: range，key: idx_user_amount_time，Extra: Using index condition。Using filesort消失。这就是一个成功的优化。

10. 总结与最佳实践

索引是一把双刃剑，用得好可以极大提升性能，用不好或设计不当反而会成为负担。回顾一下，要让MySQL心甘情愿地使用你的索引，需要避开以下陷阱：

1. 保持类型一致：确保WHERE条件中的值与列定义类型相同，避免隐式转换。
2. 让索引列“独立”：不要对索引列使用函数或进行运算。
3. 谨慎使用LIKE：前缀匹配LIKE 'abc%'才能有效利用索引，后缀和全模糊匹配应考虑其他方案（反向索引、全文索引、搜索引擎）。
4. 小心OR操作符：确保OR两边的条件都有索引，否则考虑用UNION ALL改写。
5. 理解复合索引的最左前缀：设计索引时，将等值查询和高选择性的列放在左边。
6. 范围查询列放最后：在复合索引中，范围查询（>,<,BETWEEN,LIKE）后面的列无法用于索引过滤。
7. 关注索引选择性：不要为选择性极差的列创建单列索引（如性别、状态），除非结合其他列创建复合索引或用于覆盖查询。
8. 善用覆盖索引：如果查询只需要返回索引包含的列，尽量使用覆盖索引，避免回表。
9. 使用EXPLAIN验证：任何性能相关的SQL调整，都必须用EXPLAIN查看执行计划，不要凭感觉。
10. 理解优化器的成本模型：不走索引不一定是错误，可能是优化器基于统计信息做出的更优选择。强制使用索引（USE INDEX/FORCE INDEX）要非常谨慎，最好在业务高低峰期分别测试验证。

最后，索引优化是一个持续的过程，需要结合具体的业务查询模式、数据量和增长趋势来综合考虑。没有一劳永逸的银弹，只有对原理的深刻理解和对业务的持续关注，才能打造出高效稳定的数据库系统。