我没有其他有关如何优化此查询的想法，它在使用商品XML表超过200万行的情况下，运行时间约为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
最佳答案
EXPLAIN表明它需要扫描大约82K行的gx。显然有很多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％。
关于mysql - MySQL查询在大表上很慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45856221/