单表数据量超几千万,查询越来越慢,需要优化,在索引等优化后仍不能满足条件,需要在mysql底层原理进行优化。
优化前sql:举例
SELECT
CAT_AUC_ID,
ITEM_ID,
SESSION_ID,
AUCTION_ID,
LOT_NO,
LOT_NAME
FROM
app_catalogue_auction
WHERE
CAT_AUC_STATE = 1
AND ITEM_SOLD_PRICE_DISPLAY = 1
AND CAT_AUC_VIDEO = 1
LIMIT 120000,
10;
优化后:
SELECT
aca.CAT_AUC_ID,
aca.ITEM_ID,
aca.SESSION_ID,
aca.AUCTION_ID,
aca.LOT_NO,
aca.LOT_NAME
FROM
app_catalogue_auction aca
RIGHT JOIN ( SELECT CAT_AUC_ID FROM app_catalogue_auction WHERE CAT_AUC_STATE = 1 AND ITEM_SOLD_PRICE_DISPLAY = 1 AND CAT_AUC_VIDEO = 1 LIMIT 120000, 10 ) temp ON aca.CAT_AUC_ID = temp.CAT_AUC_ID;
其实就是将原来的查询作为一个子查询为前导表,查出limit10条的主键索引,然后直接取索引的data.因为b+tree叶子节点存储的是所有节点的data,因此聚簇索引上可以查询到所有字段值,因此这样即可性能得到较大提升