MySQL查询在大表上很慢

I dont have other ideas on how to optimize this query, runs about 2,5 sec with goodsXML table over 2 million rows. It looks like order by is slowing a lot, but i cant remove it. Also, it depends a lot on how many items selected here gx.categoryID IN(892), because later another tables joins this items set. I cant make joins after this option, because joined tables perform in where clauses.

我没有关于如何优化此查询的其他想法，使用goodsXML表超过200万行运行大约2.5秒。它看起来像是减速很多，但我不能删除它。此外，它在很大程度上取决于此处选择了多少项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

Explain is following:

说明如下：

+----+-------------+-------+--------+--------------------------------+-----------------+----------------+------------------------+--------------------+--------------------------------+
| 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                  |                                |
+----+-------------+-------+--------+--------------------------------+-----------------+----------------+------------------------+--------------------+--------------------------------+

Show create table for goodsXML:

显示goodsXML的create table：

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

1 个解决方案

#1

The EXPLAIN shows that it needs to scan about 82K rows of gx. There are apparently about that many rows with categoryID = 892, correct? Most of the rest is straightforward JOINs.

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

• Don't use MyISAM, use InnoDB.
• 不要使用MyISAM，请使用InnoDB。
• INT(6) -- the (6) means nothing. Perhaps you meant MEDIUMINT UNSIGNED? INT is 4 bytes; MEDIUMINT is 3.
• INT（6） - （6）没有任何意义。也许你的意思是MEDIUMINT UNSIGNED？ INT是4个字节; MEDIUMINT是3。
• Are you 'always' searching by category? If so, make use of InnoDB's "clustered" PK by switching to PRIMARY KEY (categoryID, id), INDEX(id) and chuck the two existing indexes starting with categoryID.
• 你总是'按类别搜索？如果是这样，请通过切换到PRIMARY KEY（categoryID，id），INDEX（id）并使用categoryID来查看两个现有索引来利用InnoDB的“聚簇”PK。
• Don't use CHAR unless the column is truly fixed length; use VARCHAR.
• 除非列是真正固定的长度，否则不要使用CHAR;使用VARCHAR。
• Don't use FLOAT(m,n), it can lead to subtle rounding errors. For Money, use DECIMAL(m,n); for Scientific values, use FLOAT.
• 不要使用FLOAT（m，n），它可能导致细微的舍入错误。对于Money，使用DECIMAL（m，n）;对于科学价值观，请使用FLOAT。
• ORs defeat optimizations. See if you can redesign the schema to avoid some of them.
• ORs失败优化。看看你是否可以重新设计架构以避免其中的一些。
• What is LIKE '%^598^%'? Do you have a list of numbers in that column?
• LIKE'％^ 598 ^％'是什么？你有那个列中的数字列表吗？
• After switching to InnoDB, decrease key_buffer_size to only 30M and increase innodb_buffer_pool_size to 70% of available RAM`.
• 切换到InnoDB后，将key_buffer_size减少到仅30M，并将innodb_buffer_pool_size增加到可用RAM的70％。