MySQL性能优化与索引之：`MySQL`索引失效的`10`种常见场景：从`like`到`or`的深层原因。

MySQL索引失效的10种常见场景：从like到or的深层原因

大家好，今天我们来聊聊MySQL索引失效这个老生常谈但又至关重要的话题。索引是提升数据库查询性能的关键，但如果使用不当，索引不仅不会加速查询，反而会增加数据库的负担。了解索引失效的常见场景，能帮助我们编写更高效的SQL语句，避免不必要的性能损失。

我们今天会深入探讨10种导致MySQL索引失效的常见场景，并分析其背后的原因，同时给出相应的优化建议。

1. Like语句的左模糊匹配

这是最常见也是最容易理解的索引失效场景之一。当LIKE语句以%开头时，索引会失效。

原因：

MySQL的B-Tree索引是按照键值的从左到右进行排序的。当使用左模糊匹配时，例如LIKE '%keyword'，数据库无法利用索引的有序性来定位符合条件的记录，因为无法确定以keyword结尾的值在索引中的位置。数据库必须扫描整个索引树，甚至整个表，才能找到所有匹配的记录。

示例：

假设我们有一个名为users的表，其中包含name字段，并在name字段上创建了索引。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

CREATE INDEX idx_name ON users(name);

-- 索引失效
SELECT * FROM users WHERE name LIKE '%keyword';

-- 索引有效
SELECT * FROM users WHERE name LIKE 'keyword%';

优化建议：

• 尽量避免左模糊匹配： 如果可能，将%放在字符串的右侧。
• 使用全文索引： 对于需要进行复杂模糊匹配的场景，可以考虑使用MySQL的全文索引，它更适合处理文本搜索。
• 倒排索引： 如果必须使用左模糊匹配，可以考虑使用倒排索引，但实现和维护成本较高。

2. 使用OR条件

当WHERE子句中使用OR条件，并且OR连接的多个条件中，至少有一个条件没有使用索引，那么整个查询可能会导致索引失效。

原因：

MySQL优化器在评估查询成本时，如果发现OR条件中的一个分支无法使用索引，它可能会认为全表扫描的成本更低，从而放弃使用索引。

示例：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    category_id INT
);

CREATE INDEX idx_product_name ON products(product_name);

-- 索引失效，假设category_id没有索引
SELECT * FROM products WHERE product_name = 'product1' OR category_id = 10;

-- 索引可能有效（如果category_id有索引）
CREATE INDEX idx_category_id ON products(category_id);
SELECT * FROM products WHERE product_name = 'product1' OR category_id = 10;

优化建议：

• 使用UNION ALL代替OR： 将OR条件拆分成多个SELECT语句，然后使用UNION ALL合并结果。这样可以确保每个SELECT语句都能独立地使用索引。
• 确保所有OR条件都使用索引： 如果可能，为OR条件中的每个字段都创建索引。
• 使用IN代替OR（针对相同字段）： 如果OR条件针对的是同一个字段，可以考虑使用IN操作符。

-- 使用UNION ALL
SELECT * FROM products WHERE product_name = 'product1'
UNION ALL
SELECT * FROM products WHERE category_id = 10;

-- 使用IN
SELECT * FROM products WHERE category_id IN (10, 20, 30);

3. 数据类型不匹配

如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL可能会进行隐式类型转换，导致索引失效。

原因：

MySQL需要先将查询条件转换为索引列的数据类型，然后才能进行比较。这个转换过程会增加额外的开销，并且可能导致索引无法被正确使用。

示例：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATETIME
);

CREATE INDEX idx_user_id ON orders(user_id);

-- 索引失效，user_id是INT，但查询条件是字符串
SELECT * FROM orders WHERE user_id = '123';

-- 索引有效
SELECT * FROM orders WHERE user_id = 123;

-- order_date 字段上创建了索引
CREATE INDEX idx_order_date ON orders(order_date);
-- 索引失效，order_date是DATETIME，但查询条件是字符串
SELECT * FROM orders WHERE order_date = '2023-10-26';

-- 索引有效
SELECT * FROM orders WHERE order_date = STR_TO_DATE('2023-10-26', '%Y-%m-%d');

优化建议：

• 确保查询条件的数据类型与索引列的数据类型一致： 在编写SQL语句时，仔细检查数据类型，避免隐式类型转换。
• 使用正确的类型转换函数： 如果必须进行类型转换，使用MySQL提供的类型转换函数，例如CAST()或CONVERT()，确保转换后的数据类型与索引列的数据类型一致。

4. 索引列上使用函数或表达式

如果在WHERE子句中对索引列使用了函数或表达式，MySQL将无法使用索引。

原因：

MySQL索引存储的是原始的列值。当对索引列使用函数或表达式时，数据库无法直接使用索引来定位符合条件的记录，而是需要计算每个索引值，才能进行比较。

示例：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    hire_date DATE
);

CREATE INDEX idx_hire_date ON employees(hire_date);

-- 索引失效
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- 索引失效
SELECT * FROM employees WHERE hire_date + INTERVAL 1 DAY = '2023-10-27';

优化建议：

• 避免在索引列上使用函数或表达式： 尽可能将函数或表达式应用于查询条件的值，而不是索引列。
• 创建基于函数或表达式的索引： 在MySQL 5.7及更高版本中，可以创建基于函数或表达式的索引，但需要谨慎使用，因为会增加索引维护的成本。
• 预先计算结果： 如果函数或表达式的结果是固定的，可以预先计算结果，并将其存储在一个新的列中，然后对该列创建索引。

-- 优化后的SQL
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

5. 联合索引不满足最左前缀原则

对于联合索引，MySQL会按照索引定义时的顺序从左到右依次匹配。如果查询条件没有包含联合索引的最左侧列，或者跳过了中间的列，那么索引将失效。

原因：

MySQL的B-Tree索引是按照索引定义时的顺序进行排序的。如果查询条件没有包含最左侧的列，或者跳过了中间的列，数据库无法利用索引的有序性来定位符合条件的记录。

示例：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATETIME,
    product_id INT
);

CREATE INDEX idx_user_order_date ON orders(user_id, order_date);

-- 索引有效
SELECT * FROM orders WHERE user_id = 123;

-- 索引有效
SELECT * FROM orders WHERE user_id = 123 AND order_date = '2023-10-26';

-- 索引失效
SELECT * FROM orders WHERE order_date = '2023-10-26';

-- 索引失效
SELECT * FROM orders WHERE  order_date = '2023-10-26' AND product_id = 456;

优化建议：

• 确保查询条件包含联合索引的最左侧列： 在编写SQL语句时，仔细检查查询条件，确保包含了联合索引的最左侧列。
• 按照索引定义时的顺序提供查询条件： 如果查询条件包含了联合索引的多个列，确保按照索引定义时的顺序提供查询条件。
• 调整索引列的顺序： 如果某些列的查询频率较高，可以将它们放在联合索引的最左侧。

6. 使用NOT IN或<>

通常情况下，NOT IN和<>操作符会导致索引失效。

原因：

NOT IN和<>操作符需要扫描整个索引范围，才能找到不符合条件的记录。MySQL优化器可能会认为全表扫描的成本更低，从而放弃使用索引。

示例：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    category_id INT
);

CREATE INDEX idx_category_id ON products(category_id);

-- 索引失效
SELECT * FROM products WHERE category_id NOT IN (10, 20, 30);

-- 索引失效
SELECT * FROM products WHERE category_id <> 10;

优化建议：

• 使用BETWEEN或IN代替NOT IN和<>： 如果可能，使用BETWEEN或IN操作符代替NOT IN和<>。
• 将NOT IN转换为NOT EXISTS： 可以尝试将NOT IN子查询转换为NOT EXISTS子查询。
• 考虑全表扫描： 在某些情况下，全表扫描可能比使用索引更快。

-- 使用NOT EXISTS
SELECT * FROM products WHERE NOT EXISTS (SELECT 1 FROM categories WHERE categories.id = products.category_id AND categories.id IN (10, 20, 30));

7. 索引列的区分度不高

如果索引列的区分度不高，例如性别字段（只有男女两种取值），那么索引的效果会很差，甚至可能导致索引失效。

原因：

当索引列的区分度不高时，索引的选择性很差。这意味着索引无法有效地过滤数据，数据库需要扫描大量的索引条目，才能找到符合条件的记录。MySQL优化器可能会认为全表扫描的成本更低，从而放弃使用索引。

示例：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    gender ENUM('male', 'female')
);

CREATE INDEX idx_gender ON users(gender);

-- 索引效果可能很差，甚至失效
SELECT * FROM users WHERE gender = 'male';

优化建议：

• 不要在区分度低的列上创建索引： 只有在区分度高的列上创建索引，才能有效地提高查询性能。
• 创建联合索引： 可以将区分度低的列与其他列组合成联合索引，以提高索引的选择性。
• 考虑全表扫描： 在某些情况下，全表扫描可能比使用索引更快。

8. 表数据量太小

当表中的数据量非常小的时候，MySQL优化器可能会认为全表扫描的成本更低，从而放弃使用索引。

原因：

索引需要占用额外的存储空间，并且在插入、更新和删除数据时，需要维护索引。当表中的数据量非常小时，使用索引带来的额外开销可能会超过索引带来的性能提升。

优化建议：

• 不要在小表上创建索引： 只有在数据量足够大的表上创建索引，才能有效地提高查询性能。
• 根据实际情况评估是否需要索引： 在创建索引之前，仔细评估索引带来的性能提升和额外开销，权衡利弊。

9. 字符集不一致

如果表字段的字符集与客户端连接字符集不一致，可能会导致索引失效。

原因:

字符集不一致可能导致查询条件无法正确匹配索引中的值。例如，如果表字段使用utf8mb4字符集，而客户端连接使用latin1字符集，那么查询条件中的字符串可能无法正确转换为utf8mb4，从而导致索引失效。

示例:

-- 假设表t1的col1字段是utf8mb4字符集
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    col1 VARCHAR(255)
);

CREATE INDEX idx_col1 ON t1(col1);

-- 客户端连接字符集是latin1
-- 可能会导致索引失效
SELECT * FROM t1 WHERE col1 = 'test';

优化建议:

• 确保表字段和客户端连接使用相同的字符集： 建议统一使用utf8mb4字符集，以支持更广泛的字符。
• 在连接字符串中指定字符集： 在连接数据库时，使用charset参数指定字符集。例如：mysql -u user -p password --default-character-set=utf8mb4。

10. 优化器选择错误

虽然MySQL优化器通常能够选择最佳的执行计划，但在某些情况下，优化器可能会做出错误的选择，导致索引失效。

原因：

MySQL优化器依赖于统计信息来评估查询成本。如果统计信息不准确，或者查询条件过于复杂，优化器可能会做出错误的选择。

优化建议：

• 更新统计信息： 定期使用ANALYZE TABLE命令更新表的统计信息。
• 使用FORCE INDEX提示： 可以使用FORCE INDEX提示强制MySQL使用指定的索引。但要谨慎使用，因为这可能会阻止优化器选择更合适的执行计划。
• 重写SQL语句： 尝试重写SQL语句，改变查询的执行方式。
• 升级MySQL版本： 新版本的MySQL优化器通常会更加智能，能够做出更好的选择。

-- 强制使用索引
SELECT * FROM products FORCE INDEX (idx_category_id) WHERE category_id = 10;

关于索引失效场景的几点思考

以上就是10种常见的MySQL索引失效场景。了解这些场景以及背后的原因，可以帮助我们更好地设计表结构，编写高效的SQL语句，避免不必要的性能损失。

但是，需要注意的是，索引失效并不是绝对的。MySQL优化器会根据实际情况进行评估，选择最优的执行计划。即使符合上述某些场景，索引也可能被使用。因此，在进行性能优化时，需要结合实际情况进行分析和测试，不能一概而论。

此外，索引并不是越多越好。过多的索引会增加数据库的负担，影响插入、更新和删除数据的性能。因此，需要根据实际需求，选择合适的索引策略。

希望今天的分享对大家有所帮助。

索引失效的常见场景和优化策略

我们讨论了10种常见的MySQL索引失效场景，包括like左模糊匹配、OR条件、数据类型不匹配等，并针对每种场景提供了相应的优化建议。

理解MySQL优化器和统计信息的重要性

MySQL优化器会根据统计信息评估查询成本，并选择最优的执行计划。因此，保持统计信息的准确性至关重要。

性能优化需要结合实际情况进行分析和测试

索引失效并不是绝对的，需要结合实际情况进行分析和测试，才能找到最佳的优化方案。