mysql - 请教我如何优化这个 MySQL 查询

Question

我有两个表，main_part(3k 条记录)和part_details(25k 条记录)

我尝试了以下索引，但 explain 总是返回 25k 条记录的全表扫描，而不是大约 2k 条匹配记录和 Using where;使用临时的；使用文件排序

ALTER TABLE `main_part` ADD INDEX `main_part_index_1` (`unit`);
ALTER TABLE `part_details` ADD INDEX `part_details_index_1` (`approved`, `display`, `country`, `id`, `price`);

这是我的查询:

SELECT a.part_id, b.my_title, 
       b.price, a.type, 
       a.unit, a.certification, 
       b.my_image, 
       b.price/a.unit AS priceW 
FROM main_part AS a
INNER JOIN part_details AS b ON a.part_id=b.id
WHERE b.approved = 'Yes' 
AND b.display = 'On' 
AND b.country = 'US' 
AND a.unit >= 300 
ORDER BY b.price ASC LIMIT 50

我知道的一件事是 a.part_id 不是 main_part 表中的主键。这会是罪魁祸首吗？

创建表 SQL:

CREATE TABLE `main_part` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `part_id` mediumint(9) NOT NULL DEFAULT '0',
  `type` varchar(50) NOT NULL DEFAULT '',
  `unit` varchar(50) NOT NULL DEFAULT '',
  `certification` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `part_details` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `asn` varchar(50) NOT NULL DEFAULT '',
  `country` varchar(5) NOT NULL DEFAULT '',
  `my_title` varchar(200) NOT NULL DEFAULT '',
  `display` varchar(5) NOT NULL DEFAULT 'On',
  `approved` varchar(5) NOT NULL DEFAULT 'No',
  `price` decimal(7,3) NOT NULL DEFAULT '0.000',
  `my_image` varchar(250) NOT NULL DEFAULT '',
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `countryasn` (`country`,`asn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

对于您的查询，更重要的索引是 JOIN 条件，并且您已经知道 a.part_id 不是主键，因此没有默认值索引，您的第一次尝试应该是:

ALTER TABLE `main_part` ADD INDEX `main_part_index_1` (`part_id`,`unit`);

因为我们首先对 JOIN 条件感兴趣，所以您还应该将第二个索引更改为

ALTER TABLE `part_details` ADD INDEX `part_details_index_1` 
            (`id`, `approved`, `display`, `country`, `price`);

索引中的顺序很重要

另一个提示是您从基本查询开始:

SELECT *
FROM main_part AS a
INNER JOIN part_details AS b ON a.part_id=b.id

为 part_id 和 id 添加索引检查解释计划，然后开始添加条件并在需要时更新索引。