mysql索引失效的十大问题小结

MySQL索引是优化查询性能的重要手段，但是有时候即使建立了索引也可能出现索引失效的情况。下面是MySQL索引失效的十大问题：

1. 查找NULL值

MySQL的B-Tree索引不适用于查找NULL值，如果查询条件是IS NULL或者IS NOT NULL时，MySQL必须扫描全表。可以使用覆盖索引和联合索引来优化这个问题。

2. 使用函数或者表达式进行计算

如果在WHERE或者ORDER BY语句中使用函数或者表达式进行计算，MySQL就无法使用索引，需要将函数或者表达式移动到索引外部，将计算结果存储在一个新的列中，并为这个新列建立索引。

例如，查询订单表中金额大于1000的记录：

SELECT *
FROM orders
WHERE price * quantity > 1000;

可以改写成：

ALTER TABLE orders
ADD COLUMN amount DECIMAL(9,2) AS (price * quantity);

CREATE INDEX idx_amount ON orders (amount);

SELECT *
FROM orders
WHERE amount > 1000;

3. 字符串类型的列使用LIKE操作符

如果在WHERE语句中使用LIKE操作符进行模糊匹配，MySQL只有在字符串前面没有通配符的情况下才能使用索引，否则无法使用索引。

例如，查询订单表中名称以"B%"开头的记录：

SELECT *
FROM orders
WHERE name LIKE 'B%';

可以改写成：

SELECT *
FROM orders
WHERE name >= 'B' AND name < 'C';

4. 不同类型的列进行比较

如果在WHERE语句中对不同类型的列进行比较，MySQL可能无法使用索引，需要对列进行类型转换或者使用覆盖索引。

例如，查询订单表中ID为字符串类型的记录：

SELECT *
FROM orders
WHERE id = '123';

可以改写成：

SELECT *
FROM orders
WHERE id = 123;

5. 使用OR操作符连接WHERE条件

如果在WHERE语句中使用OR操作符连接条件，MySQL只有在所有条件都使用索引的情况下才能使用索引。

例如，查询订单表中ID为1或者名称为"ABC"的记录：

SELECT *
FROM orders
WHERE id = 1 OR name = 'ABC';

可以改写成：

SELECT *
FROM orders
WHERE (id = 1 AND name <> 'ABC') OR (id <> 1 AND name = 'ABC');

6. 索引列上进行函数操作

如果在索引列上使用函数操作，MySQL会放弃使用索引而进行全表扫描。

例如，查询订单表中ID加1的记录：

SELECT *
FROM orders
WHERE id + 1 = 123;

可以改写成：

SELECT *
FROM orders
WHERE id = 122;

7. 表连接中使用了非等值操作符

如果在表连接中使用了非等值操作符，MySQL可能无法使用索引，需要考虑使用覆盖索引或者索引优化。

例如，查询订单表中ID小于产品表中ID的记录：

SELECT *
FROM orders o, products p
WHERE o.id < p.id;

可以改写成：

SELECT *
FROM orders o
WHERE EXISTS (
 SELECT *
 FROM products p
 WHERE o.id < p.id
);

8. 字符串类型的排序

在MySQL中，按照字符串类型排序需要将字符串类型的列全部加载到内存中排序，如果字符串类型的列过大，会导致内存不足而使用磁盘排序，导致排序性能下降。

例如，查询订单表中名称按照字母顺序排序的记录：

SELECT *
FROM orders
ORDER BY name;

可以改写成：

SELECT *
FROM orders
ORDER BY name COLLATE utf8mb4_unicode_ci;

9. 查询结果集中的重复列

如果查询结果集中包含重复列，MySQL会将重复列读取多次，导致性能下降。

例如，查询订单表中品牌和名称相同的记录：

SELECT brand, name
FROM orders
GROUP BY brand, name
HAVING COUNT(*) > 1;

可以改写成：

SELECT o1.brand, o1.name
FROM orders o1, orders o2
WHERE o1.brand = o2.brand AND o1.name = o2.name AND o1.id < o2.id;

10. 使用ORDER BY或者GROUP BY语句的列和WHERE语句的列不一致

如果使用了ORDER BY或者GROUP BY语句的列和WHERE语句的列不一致，MySQL可能无法使用索引，需要对列进行类型转换或者使用覆盖索引。

例如，查询订单表中品牌为"XYZ"的记录，并按照名称排序：

SELECT *
FROM orders
WHERE brand = 'XYZ'
ORDER BY name;

可以改写成：

SELECT *
FROM orders
WHERE brand = 'XYZ'
ORDER BY name COLLATE utf8mb4_unicode_ci;

以上就是MySQL索引失效的十大问题，希望对你有所帮助。