我正在尝试连接两个表,products
和products_markets
。虽然products
表只有不到一百万条记录,但product_markets
表中的记录接近2000万条。由于数据已更改,因此模式创建表可能会出现一两个拼写错误:
CREATE TABLE `products_markets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`country_code_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`product_id`,`country_code_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21052102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`manufacturer_id` int(10) unsigned NOT NULL,
`department_id` int(10) unsigned NOT NULL,
`code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`popularity` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`value` bigint(20) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `products_code_unique` (`code`),
KEY `products_department_id_foreign` (`department_id`),
KEY `products_manufacturer_id_foreign` (`manufacturer_id`),
CONSTRAINT `products_department_id_foreign`
FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
CONSTRAINT `products_manufacturer_id_foreign`
FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=731563 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
我正在尝试返回某个特定国家最受欢迎的50个产品记录,但运行时间约为50秒左右,这似乎比预期的要长。
我已经尝试了几个不同的查询,但都没有成功:
select `products_markets`.`product_id`
from products_markets
left join
( SELECT products.id, products.popularity
from products
) p ON p.id = products_markets.product_id
where products_markets.country_code_id = 121
order by `popularity` desc, `p`.`id` asc
limit 50
and
select `products`.*
from `products`
where products.id in (
SELECT product_id
from products_markets
where products_markets.country_code_id = 121
)
group by `products`.`name`, `products`.`manufacturer_id`
order by `popularity` desc, `products`.`id` asc
limit 50
这个查询的解释是:
id select_type table type possible_keys key key_len refs rows extra
1 PRIMARY products ALL PRIMARY NULL NULL NULL 623848 Using temporary; Using filesort
1 PRIMARY products_markets ref unique_index unique_index 4 main.products.id 14 Using where; Using index; FirstMatch(products)
我正在考虑的一个选项是将products_markets根据每个国家分成单独的表,以减少查询时间。尝试在服务器上添加更多内存但效果不佳。是否有人能够找出数据库设计或查询中存在的明显问题?
还有哪些其他选项可以将当前大约50秒的查询时间缩短为一小部分?