MySQL Inner Join加ORDER BY查询性能过慢，求优化方案

数据规模与表结构 #

现有两张数据表：

• products表：约6,276,445条数据
• product_images表：约22,888,685条数据

products表结构 #

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `feed_id` bigint(20) unsigned NOT NULL,
  `feed_item_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_group_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `categorytext` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `categorytext_hash` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `manufacturer` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `price_vat` float(12,2) DEFAULT NULL,
  `price_vat_old` float(12,2) DEFAULT NULL,
  `vat` tinyint(4) DEFAULT NULL,
  `discount_percentage` tinyint(4) NOT NULL DEFAULT 0,
  `image_source_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_filename` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gtin` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ean` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
  `isbn` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
  `upc` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mpn` varchar(70) COLLATE utf8mb4_unicode_ci NOT NULL,
  `missing_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `project_1` tinyint(4) NOT NULL DEFAULT 0,
  `project_2` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id_UNIQUE` (`unique_id`),
  KEY `feed_id_updated_at` (`feed_id`,`updated_at`),
  KEY `feed_id` (`feed_id`),
  KEY `feed_id_missing_at` (`feed_id`,`missing_at`),
  KEY `feed_id_categorytext_hash_id` (`feed_id`,`categorytext_hash`,`id`),
  KEY `categorytext_hash` (`categorytext_hash`),
  KEY `missing_at_deleted_at_categorytext_hash_feed_id_id` (`missing_at`,`deleted_at`,`categorytext_hash`,`feed_id`,`id`),
  KEY `project_1_id` (`project_1`,`id`),
  KEY `project_2_id` (`project_2`,`id`),
  KEY `project_1` (`project_1`),
  KEY `project_2` (`project_2`),
  CONSTRAINT `fk_products_feeds` FOREIGN KEY (`feed_id`) REFERENCES `feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=122268834 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

product_images表结构 #

CREATE TABLE `product_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_unique_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `unique_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `feed_id` bigint(20) unsigned NOT NULL,
  `image_source_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_filename` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id_UNIQUE` (`unique_id`),
  KEY `feed_id_updated_at` (`feed_id`,`updated_at`),
  KEY `product_unique_id` (`product_unique_id`),
  KEY `product_unique_id_id` (`product_unique_id`,`id`),
  CONSTRAINT `fk_product_images_products1` FOREIGN KEY (`product_unique_id`) REFERENCES `products` (`unique_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=333584333 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

无ORDER BY的查询情况 #

SELECT IMG.id
    FROM products PR
    INNER JOIN product_images IMG on IMG.product_unique_id = PR.unique_id
    WHERE 
        PR.project_1 = 1
        -- ORDER BY IMG.id
    LIMIT 1000 OFFSET 0

执行耗时：0.016秒

EXPLAIN结果：

id|select_type|table|type|possible_keys                          |key              |key_len|ref                          |rows  |Extra      |
--+-----------+-----+----+---------------------------------------+-----------------+-------+-----------------------------+------+-----------+
 1|SIMPLE     |PR   |ref |unique_id_UNIQUE,project_1_id,project_1|project_1        |1      |const                        |286960|           |
 1|SIMPLE     |IMG  |ref |product_unique_id,product_unique_id_id |product_unique_id|130    |products-storage.PR.unique_id|2     |Using index|

带ORDER BY的查询情况 #

SELECT IMG.id
    FROM products PR
    INNER JOIN product_images IMG on IMG.product_unique_id = PR.unique_id
    WHERE 
        PR.project_1 = 1
    ORDER BY IMG.id
    LIMIT 1000 OFFSET 0

执行耗时：17.922秒

EXPLAIN结果：

id|select_type|table|type|possible_keys                          |key              |key_len|ref                          |rows  |Extra                          |
--+-----------+-----+----+---------------------------------------+-----------------+-------+-----------------------------+------+-------------------------------+
 1|SIMPLE     |PR   |ref |unique_id_UNIQUE,project_1_id,project_1|project_1        |1      |const                        |286960|Using temporary; Using filesort|
 1|SIMPLE     |IMG  |ref |product_unique_id,product_unique_id_id |product_unique_id|130    |products-storage.PR.unique_id|2     |Using index                    |

问题说明 #

带ORDER BY的查询速度过慢，分页功能依赖该排序逻辑，需求是筛选出指定项目（project_1=1）的所有图片并通过API发送至另一服务器。当前使用MariaDB 10.8.2版本，服务器搭载SSD，内存为8GB。

优化方案 #

1. 调整查询逻辑，从product_images表发起查询 #

原查询从products表出发，需先取出所有project_1=1的记录（约28万条），关联图片后得到约57万条数据再排序，触发Using temporary和Using filesort导致性能骤降。

改为从product_images表按主键id（天然有序）遍历，关联products表验证project_1=1，凑够1000条即停止，避免全量排序：

SELECT IMG.id
FROM product_images IMG
INNER JOIN products PR ON IMG.product_unique_id = PR.unique_id
WHERE PR.project_1 = 1
ORDER BY IMG.id
LIMIT 1000 OFFSET 0

2. 新增针对性联合索引 #

给products表创建(project_1, unique_id)联合索引，覆盖WHERE条件和关联字段，避免回表查询：

CREATE INDEX idx_project1_uniqueid ON products (project_1, unique_id);

该索引可让数据库快速筛选出project_1=1的记录，同时直接获取关联所需的unique_id，无需访问主表数据。

3. 调整数据库内存配置 #

服务器内存为8GB，建议将InnoDB缓冲池大小设置为4GB左右（约占物理内存的50%），让更多索引和热数据缓存到内存，减少磁盘IO：

# 在my.cnf或my.ini中修改
innodb_buffer_pool_size = 4G

修改后重启MariaDB生效。

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