MySQL LIMIT 优化问题

Question

我有以下 MySQL 查询，我希望使 LIMIT 更快，因为它运行得非常慢。 SQL_CALC_FOUND_ROWS 大约等于 114000 行。

SELECT SQL_CALC_FOUND_ROWS PStD.ProductID FROM ProductStoreDef PStD
    JOIN ProductSummary PS ON PStD.ProductID = PS.ProductID 
    JOIN MasterVendor MV ON MV.VendorID = PStD.MasterVendorID 
WHERE 
    PStD.SKUStatus = 'A' AND 
    MV.isActive = 1 AND 
    PStD.MasterCategoryID = 66 AND 
    PStD.CustomerPrice > 0
ORDER BY PStD.VendorName, PS.VendorPartNumber 
LIMIT 100000,50

下面是EXPLAIN的结果

+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+-----------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                   | key             | key_len | ref                     | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+-----------------+---------+-------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | MV    | ALL    | PRIMARY,isActive,VendorID                                                                       | NULL            | NULL    | NULL                    | 2126 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | PStD  | ref    | PRIMARY,MasterVendorID,MasterCategoryID,SKUStatus,CustomerPrice,MasterVendCatID,ProdStoreStatus | MasterVendCatID | 8       | ecomm.MV.VendorID,const |   94 | Using where                                  | 
|  1 | SIMPLE      | PS    | eq_ref | PRIMARY                                                                                         | PRIMARY         | 4       | ecomm.PStD.ProductID    |    1 |                                              | 
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+-----------------+---------+-------------------------+------+----------------------------------------------+

如有任何建议，我们将不胜感激。

更新:通过创建一个单独的表来解决这个问题，该表预先计算排序顺序，使网站运行速度提高大约 500 到 1000 倍。

Answer 1

看来问题出在排序上。在这种情况下，您可以尝试创建这些索引，但我不能保证任何事情:

ALTER TABLE `ProductStoreDef` ADD INDEX `ProductStoreDef_CIndex` (
    `ProductID` ASC, `MasterVendorID` ASC, `MasterCategoryID` ASC, 
    `SKUStatus` ASC, `CustomerPrice` ASC, `VendorName` ASC
);    
ALTER TABLE `ProductSummary` ADD INDEX `ProductSummary_CIndex` (
    `ProductID` ASC, `VendorPartNumber` ASC
);    
ALTER TABLE `MasterVendor` ADD INDEX `MasterVendor_CIndex` (
    `VendorID` ASC, `isActive` ASC
);