摘要：在互联网软件开发领域，MySQL 作为一款广泛使用的关系型数据库，其性能优化至关重要。而索引，作为提升 MySQL 查询性能的关键手段，一旦失效，会导致查询效率大幅下降，影响整个系统的性能。今天，就来深入探讨一下 MySQL 索引什么时候会失效，帮助各位开发人
在互联网软件开发领域，MySQL 作为一款广泛使用的关系型数据库，其性能优化至关重要。而索引，作为提升 MySQL 查询性能的关键手段，一旦失效，会导致查询效率大幅下降，影响整个系统的性能。今天，就来深入探讨一下 MySQL 索引什么时候会失效，帮助各位开发人员更好地优化数据库。
MySQL 在比较不同数据类型的值时，可能会尝试进行隐式转换。如果这种转换导致了复杂度增加或无法直接利用索引，则会导致索引失效。比如，当我们有一个表users，其中id字段是INT类型，我们却使用字符串类型的值去查询：
SELECT * FROM users WHERE id = '1';这里 MySQL 会将字符串'1'隐式转换为整数 1 去进行比较，这个转换过程可能会使索引无法正常使用。正确的做法是确保查询条件中的值与列的数据类型相匹配：
SELECT * FROM users WHERE id = 1;如果在应用层面上，也要确保传入数据库的参数类型正确，避免出现这种数据类型不匹配的情况
隐式转换是指 MySQL 自动将一个数据类型转换为另一个数据类型。这种转换可能改变原始的查询模式，导致索引失效。假设我们有一个phone_number字段为字符串类型，在查询时却使用了整数：
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,phone_number VARCHAR(20));INSERT INTO users (phone_number) VALUES ('13800138000');-- 可能导致索引失效的查询SELECT * FROM users WHERE phone_number = 13800138000;解决方案就是尽量保持查询条件简单明了，避免不必要的数学运算或其他可能导致隐式转换的操作。编写 SQL 语句时，务必确保数据类型一致性。
对索引列应用函数或复杂的表达式会阻止 MySQL 使用该索引，因为它需要计算每一行的结果，从而失去了索引的优势。例如：
-- 假设name字段有索引SELECT * FROM users WHERE UPPER(name) = 'JOHN';这里对name字段使用了UPPER函数，MySQL 无法直接利用索引。在 MySQL 8.0 引入了表达式索引（也称为函数索引），可以在某些情况下帮助缓解这个问题，但如果是较老版本的 MySQL，重构查询以避免使用函数可能是唯一的选择。比如我们可以将上述查询改为：
SELECT * FROM users WHERE name = 'john' OR name = 'John' OR name = 'JOHN';或者如果业务允许，创建表达式索引（MySQL 8.0+）：
CREATE INDEX idx_name_upper ON users (UPPER(name));然后查询：
SELECT * FROM users WHERE UPPER(name) = 'JOHN';这样就能利用到表达式索引了。
在复合索引中，一旦出现了范围条件，MySQL 就不能再使用后续的索引部分，因为这些部分不再能够有效地缩小搜索范围。例如，我们有一个复合索引(age, salary)，当进行如下查询时：
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,age INT,salary DECIMAL(10, 2));CREATE INDEX idx_age_salary ON employees (age, salary);-- 范围查询后salary索引部分失效SELECT * FROM employees WHERE age > 30 AND salary > 5000;在这个查询中，age字段进行了范围查询，salary字段的索引部分就无法被使用了。对于频繁使用的范围查询，应该单独为涉及的列创建索引。同时，在设计复合索引时要考虑到查询模式，尽量让等值条件先于范围条件出现。比如可以将复合索引改为(salary, age)，如果查询经常是salary等值查询加上age范围查询的话。
当LIKE模式以通配符开头时，MySQL 不能使用索引来加速查询，因为它需要扫描所有可能的前缀。例如：
CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT,product_name VARCHAR(100));CREATE INDEX idx_product_name ON products (product_name);-- 索引失效的查询SELECT * FROM products WHERE product_name LIKE '%apple%';但是，如果通配符出现在模式的末尾，则索引仍然可以被使用：
-- 可以使用索引的查询SELECT * FROM products WHERE product_name LIKE 'apple%';如果确实需要以通配符开头的模糊查询功能，可以考虑使用全文索引或者其他专门的搜索引擎。对于前缀匹配，可以通过范围查询实现索引的有效利用。比如可以将LIKE '%apple%'的查询，通过一些算法转化为范围查询来利用索引。
使用OR连接的不同列上的条件可能导致 MySQL 无法有效利用索引，特别是当OR条件跨越多个不同的列时。例如：
CREATE TABLE customers (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50),last_name VARCHAR(50));CREATE INDEX idx_first_name ON customers (first_name);CREATE INDEX idx_last_name ON customers (last_name);-- 索引可能失效的查询SELECT * FROM customers WHERE first_name = 'John' OR last_name = 'Doe';在这种情况下，MySQL 可能无法有效地利用索引。对于这种情况，我们可以评估是否可以通过创建组合索引或者重构查询逻辑来提高性能。比如可以创建一个复合索引(first_name, last_name)，或者将查询重构为UNION的形式：
SELECT * FROM customers WHERE first_name = 'John'UNIONSELECT * FROM customers WHERE last_name = 'Doe';在 MySQL 5.6 及以后版本支持索引合并策略，可以在某些情况下提高性能，我们可以通过检查EXPLAIN输出，看看是否启用了索引合并。
对于非常小的表或者返回大部分行的查询，全表扫描可能比使用索引更快，因为索引访问涉及到额外的 I/O 操作。MySQL 优化器会权衡利弊，决定最合适的执行计划。比如有一个小表test，只有几行数据，即使有索引，MySQL 可能也会选择全表扫描：
CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT,data VARCHAR(50));CREATE INDEX idx_data ON test (data);-- 对于小表，可能选择全表扫描SELECT * FROM test;又比如一个大表orders，如果查询返回大量行，例如：
CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT,order_date DATE,amount DECIMAL(10, 2));CREATE INDEX idx_order_date ON orders (order_date);-- 返回大量行，优化器可能倾向于全表扫描SELECT * FROM orders WHERE order_date > '2025-01-01';我们要理解 MySQL 优化器的行为，不要盲目依赖索引。有时候，对于特定的小表或高覆盖率查询，全表扫描是最佳选择。定期分析查询性能，确保优化器做出正确的决策。
选择性低意味着索引列包含大量的重复值，使得索引的效果大打折扣。在这种情况下，MySQL 可能会认为全表扫描更加高效。比如有一个gender列，只有male和female两个值，其选择性很低：
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,gender ENUM('male', 'female'));CREATE INDEX idx_gender ON users (gender);-- 性别列选择性低，索引效果不佳SELECT * FROM users WHERE gender ='male';对于这种情况，我们要尽量避免在低选择性的列上创建独立的索引，除非它们与其他高选择性的列一起组成复合索引。我们可以通过ANALYZE TABLE命令获取统计信息，评估索引的选择性。
当查询中所选的列不在索引中时，MySQL 必须回表获取完整行信息，这增加了额外的 I/O 成本，降低了索引的效率。例如，有一个覆盖索引idx_id_name包含id和name列：
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),email VARCHAR(100));CREATE INDEX idx_id_name ON users (id, name);-- 覆盖索引不足的查询SELECT email FROM users WHERE id = 1;在这个查询中，需要的email列不在索引中，MySQL 需要回表获取数据。我们可以创建覆盖索引，即包括查询中所有需要的列。比如对于上述查询，可以创建索引idx_id_name_email：
CREATE INDEX idx_id_name_email ON users (id, name, email);这样就可以在索引中直接获取所需数据，而无需回表。但要注意，覆盖索引虽然提高了读取速度，但可能会影响写入性能，因此需要平衡考虑。
MySQL 优化器依赖于表的统计信息来决定查询计划。如果这些统计数据过时或不准确，优化器可能会做出错误的决策。例如，当表数据发生大量变化后，统计信息没有及时更新：
CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT,product_name VARCHAR(100),price DECIMAL(10, 2));CREATE INDEX idx_product_name_price ON products (product_name, price);-- 大量数据更新后未更新统计信息UPDATE products SET price = price * 1.1;-- 优化器可能因统计信息不准确做出错误决策SELECT * FROM products WHERE product_name LIKE 'product%' AND price > 50;我们可以定期运行ANALYZE TABLE或OPTIMIZE TABLE命令来保持统计信息的准确性。这对于大型表尤其重要。在 MySQL 8.0 及以上版本，可以启用直方图统计信息来更好地反映数据分布。
使用 EXPLAIN 分析执行计划：通过EXPLAIN关键字，我们可以查看 MySQL 执行查询的详细计划。例如：
EXPLAIN SELECT * FROM users WHERE age > 30;重点关注type列，如果是ALL，表示全表扫描，索引可能失效；index或range则表示使用了索引。key列显示实际使用的索引名称，Extra列中Using where; Using index表示覆盖索引。
开启慢查询日志：开启慢查询日志可以记录执行时间较长的查询，我们可以从中找出可能存在索引失效问题的查询语句。在 MySQL 配置文件中，设置slow_query_log = ON，并指定slow_query_log_file来记录日志文件路径。然后通过分析日志文件，找到那些执行时间长的查询，进一步检查是否存在索引失效情况。
在 MySQL 数据库开发中，索引失效是一个常见但又必须重视的问题。了解各种导致索引失效的场景，如数据类型不匹配、隐式转换、函数使用、范围查询、LIKE和OR条件等，能够帮助我们在编写 SQL 语句和设计索引时，避免这些陷阱，从而提高数据库的查询性能。同时，掌握EXPLAIN分析执行计划和开启慢查询日志等排查工具的使用，能让我们及时发现和解决索引失效问题，确保数据库高效稳定运行。希望各位开发人员在今后的工作中，能够灵活运用这些知识，打造出高性能的数据库应用。
来源：从程序员到架构师一点号
