mysql - 如何优化 MySQL JOIN 查询

Question

我有一个想要优化的 MySQL 查询:

SELECT r.WarehouseLocation,sum(sir.qty) 
FROM repairableissue as r 
INNER JOIN SIR ON r.sirno=sir.sirno 
    AND r.region=sir.region 
    AND r.ItemName=sir.Itemdesc 
    AND r.SerialNo=sir.Serialno
WHERE r.status='Pending' 
GROUP BY r.warehouseLocation

如何优化该查询？我阅读了有关优化的内容，发现索引可能有所帮助，但仍然无法达到所需的性能。

应该使用哪个索引，应该删除哪个索引？

下面是查询的解释:

可修复问题

CREATE TABLE `repairableissue` (
 `Vendor` varchar(40) NOT NULL,
 `ItemName` varchar(200) NOT NULL,
 `SerialNo` varchar(50) NOT NULL,
 `person` varchar(200) NOT NULL,
 `siteid` varchar(10) NOT NULL,
 `invuser` varchar(50) NOT NULL,
 `region` varchar(50) NOT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `Dated` date NOT NULL,
 `Sirno` varchar(50) NOT NULL,
 `status` varchar(30) NOT NULL DEFAULT 'Pending',
 `trackthrough` varchar(30) NOT NULL,
 `reason` varchar(100) NOT NULL,
 `ckh` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `WarehouseType` varchar(20) NOT NULL,
 `WarehouseLocation` varchar(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `I1` (`status`),
 KEY `ind2` (`ItemName`),
 KEY `ind3` (`region`),
 KEY `ind5` (`SerialNo`),
 KEY `ind4` (`Sirno`)
) ENGINE=MyISAM AUTO_INCREMENT=63029 DEFAULT CHARSET=latin1

先生

CREATE TABLE `sir` (
 `SirNo` varchar(50) NOT NULL,
 `SiteId` varchar(80) NOT NULL,
 `Vendor` varchar(70) NOT NULL,
 `Type` varchar(15) NOT NULL,
 `ItemDesc` varchar(200) NOT NULL,
 `ItemCode` varchar(25) NOT NULL,
 `SerialNo` varchar(50) NOT NULL,
 `Unit` varchar(15) NOT NULL,
 `AssetCode` varchar(50) NOT NULL,
 `Qty` decimal(11,0) NOT NULL,
 `Region` varchar(15) NOT NULL,
 `Status` varchar(20) NOT NULL DEFAULT 'Installed',
 `FaultInfo` varchar(100) NOT NULL DEFAULT 'date()',
 `chk` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `Phase` varchar(15) NOT NULL,
 `Category` varchar(200) NOT NULL,
 `Issue_Vendor` varchar(30) NOT NULL,
 `AssetName` varchar(150) NOT NULL,
 `Ownership` varchar(20) NOT NULL,
 `Dated` date NOT NULL,
 `PersonName` varchar(150) NOT NULL,
 `Remarks` varchar(300) NOT NULL,
 `po` varchar(100) NOT NULL,
 `invuser` varchar(50) NOT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `grnno` varchar(30) NOT NULL,
 `WarehouseType` varchar(20) NOT NULL,
 `WarehouseLocation` varchar(20) NOT NULL,
 `mainpartserial` varchar(200) NOT NULL,
 PRIMARY KEY (`Vendor`,`Type`,`ItemCode`,`ItemDesc`,`SerialNo`,`Ownership`,`SirNo`,`Region`,`WarehouseType`,`WarehouseLocation`,`po`,`Qty`,`id`),
 KEY `id` (`id`),
 KEY `ind4` (`ItemDesc`),
 KEY `ind6` (`SerialNo`),
 KEY `ind7` (`SerialNo`)
) ENGINE=MyISAM AUTO_INCREMENT=228007 DEFAULT CHARSET=latin1

Answer 1

一个multi-column按 r.status + r.warehouseLocation 的顺序索引。

sir.sirno + sir.region + sir.Itemdesc + sir.Serialno 上的一个多列索引code>，按照基数从大到小的顺序，最后添加 sir.qty。

这假设字段足够小以适合(组合)到索引中。

尽管如此，连接查找还是不可避免的。与 r.status='Pending' 匹配的记录数量将决定此查询的速度。