大表上的Mysql查询速度慢

Question

尝试在超过 300 万行的 mysql 表上运行以下查询。它非常慢，以至于它几乎挂起，直到脚本超时。以下是查询和该查询的解释，有什么建议吗？

SELECT SQL_CALC_FOUND_ROWS
listing_track.listingid,
listing_track.commid,
listing.listingname,
listing_package.packagename,
listing.active,
community.commname,
SUM( listing_track.impression ) AS listing_impressions,
SUM( listing_track.view ) AS listing_views,
SUM( listing_track.phone ) AS listing_phones,
SUM( listing_track.forward ) AS listing_forward,
SUM( listing_track.coupon ) AS listing_coupons,
SUM( listing_track.email ) AS listing_emails
FROM listing_track
INNER JOIN listing ON listing_track.listingid = listing.id
INNER JOIN community ON listing_track.commid = community.id
INNER JOIN listing_package ON listing.packageid = listing_package.id
WHERE listing_track.commid =2
GROUP BY listing_track.commid, listing_track.listingid, listing_track.trackip
LIMIT 0 , 25

解释如下:

Answer 1

这里的问题是在查询结束时应用 LIMIT，在所有昂贵的表扫描完成之后。成本不是来自返回大量行，而是来自扫描大量行。

像这样加速查询的最简单方法是使用 covering index .这将允许您扫描所需的行，但每行需要更少的 I/O 字节(因为您只扫描每行数据的一部分，而不是整行)。此外，如果索引的排序方式与查询的排序方式相同，则可以避免排序成本并且可以扫描更少的行。

您的索引应在下方包含这些列。前三列的顺序必须与您的 GROUP BY 的顺序相同——这使您的 GROUP BY 和 WHERE 的执行成本大大降低。第二行允许索引“覆盖”查询，这意味着 MySQL 将能够仅从索引中满足查询的整个 listing_track 部分:

CREATE INDEX ix_listing_track_covering ON listing_track (
    commid, listingid, trackip, 
    listing_impression, listing_view, listing_phone, listing_forward, listing_coupon, listing_email);

有了这个索引，您应该能够运行完全相同的查询，但性能会大大提高。