mysql - 为什么 WHERE 子句中具有不同外键的相同 SQL 查询会获得不同的性能？

Question

为什么这两个查询的唯一区别是campaign_id(另一个表的外键)获得不同的性能和不同的EXPLAIN结果？

查询 1 - 平均时间:0.21s

SELECT tx_time, campaign_id, tx_amount, tx_status FROM tx WHERE
 campaign_id=6963 ORDER BY tx_time DESC LIMIT 2500;

查询 2 - 平均时间:0.29s

 SELECT tx_time, campaign_id, tx_amount, tx_status FROM tx WHERE
 campaign_id=6946 ORDER BY tx_time DESC LIMIT 2500;

查询 1 与查询 2 说明:

 id  select_type   table   partitions  type    possible_keys     key             key_len   ref   rows    filtered  Extra
 1   SIMPLE        tx      NULL        index   tx_campaign_id    tx_time         4         NULL  85591   2.92      Using where
 1   SIMPLE        tx      NULL        ref     tx_campaign_id    tx_campaign_id  4         const 106312  100       Using index condition; Using filesort

更新:添加 (tx_id,tx_time,campaign_id) 和 (tx_id,tx_time) 索引并运行 ANALYZE 后，查询 1 已缩短至 0.15 秒，但查询 2 已减慢至 13 秒。更新说明:

 id  select_type   table   partitions  type    possible_keys     key             key_len   ref   rows    filtered  Extra
 1   SIMPLE        tx      NULL        index   tx_campaign_id    tx_time         4         NULL  75450   3.31      Using where
 1   SIMPLE        tx      NULL        ref     tx_campaign_id    tx_campaign_id  4         const 117400  100.00    Using index condition; Using filesort

表发送:

CREATE TABLE tx (
tx_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
tx_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
campaign_id int(10) unsigned NOT NULL,
tx_amount decimal(12,5) unsigned NOT NULL,
tx_geo varchar(2) NOT NULL,
tx_langauge varchar(511) NOT NULL,
tx_ua varchar(511) NOT NULL,
tx_ip varchar(45) NOT NULL,
tx_status tinyint(255) DEFAULT NULL,
PRIMARY KEY (tx_id),
KEY tx_campaign_id (campaign_id),
KEY tx_time (tx_time) USING BTREE,
KEY tx_amount (tx_amount) USING BTREE,
KEY tx_time_campaign_id (tx_id,tx_time,campaign_id) USING BTREE,
KEY tx_id_time (tx_id,tx_time) USING BTREE,
CONSTRAINT campaign_idcampaign_id FOREIGN KEY (campaign_id) REFERENCES campaign (campaign_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10855433 DEFAULT CHARSET=utf8

Answer 1

您需要 INDEX(campaign_id, tx_time) 以及按该顺序排列的列。

一般情况下，将=列放在前面，即campaign_id。在本例中，它会处理整个 WHERE 子句，因此您可以继续处理 ORDER BY。然后添加ORDER BY中的所有列，即tx_time。

成功构建处理这些问题的索引后，处理就可以在 LIMIT 行处停止并避免“文件排序”。

Index Cookbook