MySQL not exists不走索引的实例分析

在MySQL数据库中，NOT EXISTS子句常用于检查子查询中是否存在符合条件的记录。然而，在某些情况下，使用NOT EXISTS可能会导致查询性能下降，尤其是在没有正确使用索引的情况下。本文将通过实例分析，探讨NOT EXISTS不走索引的原因，并提供优化建议。

1. NOT EXISTS的基本用法

NOT EXISTS子句通常用于检查子查询中是否存在符合条件的记录。如果子查询返回空结果集，则NOT EXISTS返回TRUE，否则返回FALSE。以下是一个简单的示例：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
);

在这个查询中，我们想要找出所有没有关联订单详情的订单。

2. NOT EXISTS不走索引的常见原因

尽管NOT EXISTS子句在某些情况下可以高效地使用索引，但在以下情况下，它可能不会走索引：

2.1 子查询中的列未索引

如果子查询中用于连接的列没有索引，MySQL将无法高效地执行子查询，从而导致全表扫描。例如：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
);

如果order_details表中的order_id列没有索引，MySQL将不得不扫描整个order_details表来检查是否存在匹配的记录。

2.2 子查询中的列类型不匹配

如果子查询中用于连接的列类型与外部查询中的列类型不匹配，MySQL可能无法使用索引。例如：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = CAST(o.order_id AS CHAR)
);

在这个例子中，order_id在orders表中是整数类型，而在子查询中被强制转换为字符类型。这种类型不匹配可能导致MySQL无法使用索引。

2.3 子查询中的复杂条件

如果子查询中包含复杂的条件或函数，MySQL可能无法使用索引。例如：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
    AND DATE(od.created_at) = '2023-01-01'
);

在这个例子中，DATE(od.created_at)函数的使用可能导致MySQL无法使用created_at列上的索引。

3. 实例分析

3.1 实例1：子查询中的列未索引

假设我们有以下两个表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

我们想要找出所有没有关联订单详情的订单：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
);

如果order_details表中的order_id列没有索引，MySQL将不得不扫描整个order_details表来检查是否存在匹配的记录。这会导致查询性能下降。

解决方案：

在order_details表的order_id列上创建索引：

CREATE INDEX idx_order_id ON order_details(order_id);

3.2 实例2：子查询中的列类型不匹配

假设我们有以下两个表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id VARCHAR(10),
    product_id INT,
    quantity INT
);

我们想要找出所有没有关联订单详情的订单：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = CAST(o.order_id AS CHAR)
);

在这个例子中，order_id在orders表中是整数类型，而在order_details表中是字符类型。这种类型不匹配可能导致MySQL无法使用索引。

解决方案：

将order_details表中的order_id列类型更改为整数类型，或者在查询中使用相同的类型：

ALTER TABLE order_details MODIFY order_id INT;

或者：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
);

3.3 实例3：子查询中的复杂条件

假设我们有以下两个表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    created_at DATETIME
);

我们想要找出所有在2023年1月1日没有创建订单详情的订单：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
    AND DATE(od.created_at) = '2023-01-01'
);

在这个例子中，DATE(od.created_at)函数的使用可能导致MySQL无法使用created_at列上的索引。

解决方案：

避免在子查询中使用函数，改为使用范围查询：

SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.order_id = o.order_id
    AND od.created_at >= '2023-01-01 00:00:00'
    AND od.created_at < '2023-01-02 00:00:00'
);

4. 总结

在使用NOT EXISTS子句时，确保子查询中的列有适当的索引，并且列类型匹配。避免在子查询中使用复杂的条件或函数，以提高查询性能。通过合理的索引设计和查询优化，可以显著提升NOT EXISTS查询的效率。

5. 参考

• MySQL官方文档
• 高性能MySQL