MySQL添加ORDER BY后查询骤慢，求优化方案（300k数据）

问题背景 #

数据库约有30万条记录，基础查询仅需1-2毫秒，但添加ORDER BY子句后执行时间飙升至700毫秒以上。已创建的索引包括：

customers.id, 
marketplaces.id, 
orders.customer_id, 
orders.shipping_method_id, 
orders.payment_method_id, 
orders.marketplace_id, 
order_items.id, 
order_items.order_id, 
shipping_methods.id

查询中的多OR条件用于适配实时搜索功能，但即使移除所有搜索条件，查询速度也无明显改善。原查询语句如下：

SELECT
    `order_items`.`id` AS `order_item_id`,
    `order_items`.`created_at` AS `order_item_created_at`,
    `order_items`.`name` AS `order_item_name`,
    `order_items`.`quantity` AS `order_item_quantity`,
    `order_items`.`price` AS `order_item_price`,
    `customers`.`name` AS `customer_name`,
    `payment_methods`.`name` AS `payment_method_name`,
    `shipping_methods`.`name` AS `shipping_method_name`,
    `marketplaces`.`name` AS `marketplace_name`
FROM
    `order_items`
INNER JOIN `orders` ON `order_items`.`order_id` = `orders`.`id`
INNER JOIN `customers` ON `customers`.`id` = `orders`.`customer_id`
INNER JOIN `payment_methods` ON `payment_methods`.`id` = `orders`.`payment_method_id`
INNER JOIN `shipping_methods` ON `shipping_methods`.`id` = `orders`.`shipping_method_id`
INNER JOIN `marketplaces` ON `marketplaces`.`id` = `orders`.`marketplace_id`
WHERE
    (
        `order_items`.`id` = '' OR `order_items`.`name` LIKE '%%' OR `order_items`.`quantity` = '' OR `customers`.`name` LIKE '%%' OR `payment_methods`.`name` LIKE '%%' OR `shipping_methods`.`name` LIKE '%%' OR `marketplaces`.`name` LIKE '%%'
    )
ORDER BY
    `order_item_id` ASC
LIMIT 100

优化步骤 #

1. 清理无效WHERE条件 #

原查询中的order_items.id = ''、order_items.quantity = ''属于无效条件（id和quantity为数值类型，空字符串无法匹配任何记录）；LIKE '%%'等价于无过滤条件，会强制MySQL进行全表扫描。这些冗余条件会直接导致索引失效，必须先移除。

2. 创建覆盖索引消除排序开销 #

当前ORDER BY依赖order_items.id（即order_item_id），但多表关联后，MySQL需要先将所有符合条件的关联结果取出，再进行内存/磁盘排序（Using filesort）。创建覆盖索引让MySQL直接通过索引获取排序所需字段，避免额外排序：

CREATE INDEX idx_order_items_id_order_id ON order_items(id, order_id);

该索引包含排序用的id和关联orders所需的order_id，MySQL可直接按索引顺序读取数据，无需再做排序操作。

3. 重构多OR搜索条件 #

多OR条件难以利用索引，建议拆分为多个UNION子查询，每个子查询对应一个搜索字段，让每个子查询单独使用对应字段的索引：

SELECT * FROM (
    -- 按order_items.name搜索
    SELECT
        oi.id AS order_item_id,
        oi.created_at AS order_item_created_at,
        oi.name AS order_item_name,
        oi.quantity AS order_item_quantity,
        oi.price AS order_item_price,
        c.name AS customer_name,
        pm.name AS payment_method_name,
        sm.name AS shipping_method_name,
        m.name AS marketplace_name
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    INNER JOIN customers c ON c.id = o.customer_id
    INNER JOIN payment_methods pm ON pm.id = o.payment_method_id
    INNER JOIN shipping_methods sm ON sm.id = o.shipping_method_id
    INNER JOIN marketplaces m ON m.id = o.marketplace_id
    WHERE oi.name LIKE '%搜索关键词%'

    UNION

    -- 按customers.name搜索
    SELECT
        oi.id AS order_item_id,
        oi.created_at AS order_item_created_at,
        oi.name AS order_item_name,
        oi.quantity AS order_item_quantity,
        oi.price AS order_item_price,
        c.name AS customer_name,
        pm.name AS payment_method_name,
        sm.name AS shipping_method_name,
        m.name AS marketplace_name
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    INNER JOIN customers c ON c.id = o.customer_id
    INNER JOIN payment_methods pm ON pm.id = o.payment_method_id
    INNER JOIN shipping_methods sm ON sm.id = o.shipping_method_id
    INNER JOIN marketplaces m ON m.id = o.marketplace_id
    WHERE c.name LIKE '%搜索关键词%'

    -- 其他搜索条件的子查询依次添加
) AS temp
ORDER BY order_item_id ASC
LIMIT 100;

同时给需要模糊搜索的字段创建全文索引（MySQL 5.6+支持），替换低效的LIKE：

-- 给需要搜索的字段创建全文索引
CREATE FULLTEXT INDEX idx_order_items_name ON order_items(name);
CREATE FULLTEXT INDEX idx_customers_name ON customers(name);
CREATE FULLTEXT INDEX idx_payment_methods_name ON payment_methods(name);
-- 其他搜索字段同理

使用MATCH AGAINST替代LIKE，性能大幅提升：

WHERE MATCH(oi.name) AGAINST('搜索关键词' IN BOOLEAN MODE)

4. 用EXPLAIN验证执行计划 #

执行以下语句查看查询计划，确认是否用到索引、是否消除了Using filesort：

EXPLAIN SELECT ... -- 替换为你的优化后查询语句

若Extra列仍显示Using filesort，需进一步调整索引或查询逻辑。

5. 减少不必要的表关联 #

检查所有关联表是否为业务必需，若部分字段可延迟加载或仅在特定条件下关联，可减少关联开销，提升查询速度。

内容的提问来源于stack exchange，提问作者Winkielo