MySQL 在大表上查询速度很慢

Question

对于如何优化此查询，我没有其他想法，对于超过 200 万行的 GoodsXML 表，运行大约 2.5 秒。看起来 order by 速度慢了很多，但我无法删除它。此外，这在很大程度上取决于此处选择的项目数 gx.categoryID IN(892)，因为稍后另一个表会加入此项目集。我无法在此选项后进行连接，因为连接表在 where 子句中执行。

SELECT MD5(CONCAT(gx.id,598)) citySort,gx.dateCreated lastModifiedSince,IF(DATE(gx.dateModified)>=(IF((EXTRACT(HOUR FROM NOW()) BETWEEN 0 AND 6),DATE(NOW() -INTERVAL 6 HOUR),DATE(NOW()))) OR DATE(gx.dateModified)>=DATE(NOW()),1,0) isActual,
        gx.id,p.producerName,gx.categoryID,gx.name,CONCAT('::',gxi.imageName) images,IF(CONCAT('::',gxi.imageName)!='',1,0) imExist,gx.price,gx.oldPrice,gx.oldPricePt,gx.sourceUrl,IF(s.offerPostingType='XML',IF(s.alternateName!='',s.alternateName,s.name),CONCAT(u.lastName,' ',u.name)) shopName,s.logoName,
        s.id shopID,s.active shopActive,s.offerPostingType,c.titleAdd,'Москва' cityName,
        IF((s.cityID='598' AND s.deliveryByCity=1) OR (sa.cityID='598' AND sa.deliveryByCity=1) OR (s.deliveryByMRCities LIKE '%^598^%' AND s.deliveryByMR=1),1,0) deliveryInYourCity,
        IF(s.deliveryByCityAll=1 OR (s.cityID='598' AND s.deliveryByCity=1) OR (sa.cityID='598' AND sa.deliveryByCity=1) OR (s.deliveryByMRCities LIKE '%^598^%' AND s.deliveryByMR=1),1,0) deliveryByCity,
        IF(s.deliveryByMail=1,1,0) deliveryByMail,
        IF(s.deliveryBySelfAll=1 OR (s.cityID='598' AND s.deliveryBySelf=1) OR (sa.cityID='598' AND sa.deliveryBySelf=1),1,0) deliveryBySelf
        FROM goodsXML gx
        JOIN category c ON c.id=gx.categoryID
        LEFT JOIN producer p ON p.id=gx.producerID
        JOIN shop s ON s.id=gx.shopID
        LEFT JOIN shopAddress sa ON sa.shopID=s.id
        LEFT JOIN users u ON u.id=s.userID
        LEFT JOIN goodsXMLImages gxi ON gxi.goodsXMLID=gx.id AND gxi.isMain = 1

        WHERE 1=1 AND (s.cityID='598' OR s.deliveryByCityAll=1 OR s.deliveryBySelfAll=1 OR s.deliveryByMail=1 OR sa.cityID='598' OR (s.deliveryByMR=1 AND s.deliveryByMRCities LIKE '%^598^%')) AND (s.isPaying=0 OR u.balance>0) AND gx.categoryID IN(892)
        GROUP BY gx.id

        ORDER BY isActual DESC,imExist DESC,gx.PPC DESC,gx.payPrior ASC,citySort DESC
        LIMIT 0,40

解释如下:

+----+-------------+-------+--------+--------------------------------+-----------------+----------------+------------------------+--------------------+--------------------------------+
| ID | SELECT_TYPE | TABLE |  TYPE  |         POSSIBLE_KEYS          |       KEY       |    KEY_LEN     |          REF           |        ROWS        |             EXTRA              |
+----+-------------+-------+--------+--------------------------------+-----------------+----------------+------------------------+--------------------+--------------------------------+
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | c     | const  | PRIMARY                        | PRIMARY         | 4              | const                  | 1                  | Using temporary; Using filesor |
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | gx    | ref    | ixGroupNameCategoryIDShopIDPro | ixCategoryID... | ixCategoryIDid | 4                      | const              | 82005                          |
|    |             |       |        | ducerID                        |                 |                |                        |                    |                                |
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | s     | eq_ref | PRIMARY                        | deliveryByMR    | PRIMARY        | 4                      | vsesrazu.gx.shopID | 1                              |
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | sa    | ref    | shopKey                        | shopKey         | 5              | vsesrazu.s.id          | 2                  | Using where                    |
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | u     | eq_ref | PRIMARY                        | PRIMARY         | 4              | vsesrazu.s.userID      | 1                  | Using where                    |
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | p     | eq_ref | PRIMARY                        | PRIMARY         | 4              | vsesrazu.gx.producerID | 1                  |                                |
|    |             |       |        |                                |                 |                |                        |                    |                                |
| 1  | SIMPLE      | gxi   | ref    | over                           | over            | 4              | vsesrazu.gx.id         | 1                  |                                |
+----+-------------+-------+--------+--------------------------------+-----------------+----------------+------------------------+--------------------+--------------------------------+

显示为goodsXML创建表:

CREATE TABLE goodsXML (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 localID char(255) NOT NULL,
 groupID char(255) DEFAULT NULL,
 dateCreated datetime NOT NULL,
 dateModified datetime NOT NULL,
 dateModifiedPrice datetime NOT NULL,
 name char(255) DEFAULT NULL,
 nameHash char(32) NOT NULL,
 groupName char(255) DEFAULT NULL,
 newGroupName char(255) NOT NULL,
 url char(255) NOT NULL,
 sourceUrl char(255) NOT NULL,
 categoryID int(6) unsigned NOT NULL,
 producerID int(6) DEFAULT NULL,
 authorID int(6) DEFAULT NULL,
 shopID int(6) NOT NULL,
 XMLUrlOrder tinyint(2) NOT NULL,
 price float(12,2) NOT NULL,
 oldPrice float(12,2) NOT NULL,
 oldPricePt smallint(3) NOT NULL,
 description text,
 descriptionHash char(32) NOT NULL,
 descriptionForGroup text NOT NULL,
 imExist tinyint(1) NOT NULL DEFAULT '0',
 imagesForGroup tinyint(1) NOT NULL DEFAULT '0',
 videoHighlight text NOT NULL,
 videoSiteUrl char(255) NOT NULL,
 videoChannelUrl char(255) NOT NULL,
 plusesMinuses text NOT NULL,
 toIndex tinyint(1) NOT NULL DEFAULT '0',
 isRST tinyint(1) NOT NULL DEFAULT '0',
 isReplica tinyint(1) NOT NULL DEFAULT '0',
 status tinyint(1) NOT NULL DEFAULT '0',
 comment char(255) NOT NULL,
 daysLeft tinyint(2) NOT NULL,
 PPC float(5,2) DEFAULT '0.00',
 payPrior tinyint(1) NOT NULL DEFAULT '4',
 PRIMARY KEY (id),
 UNIQUE KEY ixGroupNameCategoryIDShopIDProducerID (shopID,localID),
 KEY ixGroupNameCategoryID (groupName,categoryID),
 KEY ixStatusShopID (status,shopID),
 KEY ixCategoryID (categoryID),
 KEY authorID (authorID),
 KEY ixDateModified (dateModified,imExist),
 KEY daysLeft (daysLeft),
 KEY sourceUrl (sourceUrl),
 KEY ixCategoryIDid (categoryID,id)
) ENGINE=MyISAM AUTO_INCREMENT=4218880 DEFAULT CHARSET=utf8

Answer 1

EXPLAIN显示它需要扫描 gx 的大约 82K 行。显然有很多行 categoryID = 892 ， 正确的？其余大部分都很简单 JOINs .

• 不要使用 MyISAM，使用 InnoDB。
• INT(6) --(6)没有任何意义。也许你的意思是 MEDIUMINT UNSIGNED ？ INT是 4 个字节； MEDIUMINT是 3。
• 您是否“总是”通过 category 进行搜索？如果是这样，请切换到 PRIMARY KEY (categoryID, id), INDEX(id) 来利用 InnoDB 的“集群”PK并删除以 categoryID 开头的两个现有索引.
• 请勿使用CHAR除非列确实是固定长度的；使用VARCHAR .
• 请勿使用FLOAT(m,n) ，它可能会导致细微的舍入误差。如需金钱，请使用 DECIMAL(m,n) ;对于科学值，请使用 FLOAT .
• ORs击败优化。看看是否可以重新设计架构来避免其中的一些问题。
• 什么是LIKE '%^598^%' ？您有该栏中的数字列表吗？
• 切换到InnoDB后，减少key_buffer_size到只有30M并增加innodb_buffer_pool_size到可用 RAM 的 70%`。