mysql - 由于索引无效而避免文件排序..？

Question

我正在运行一个通过日期范围搜索连接多个表的查询，并试图弄清楚如何进一步优化它。

SELECT ACC.name AS account_name, CAMP.account_id AS account_id,CAMP.name AS campaign_name,CAMP.id AS campaign_id,ADG.id AS adgroup_id,ADG.name AS adgroup_name,KW.text AS keyword_name,
SUM(SPENT.billed_clicks) AS billed_clicks,KW.id AS keyword_id,KW.status_id AS status_id FROM account ACC, campaign CAMP,adgroup ADG,adgroup_keyword KW INNER JOIN keyword_spent SPENT
ON KW.id = SPENT.keyword_id WHERE     summary_date >= '2012-03-01' AND summary_date <= '2012-03-04' AND KW.adgroup_id = ADG.id AND ADG.campaign_id = CAMP.id AND CAMP.account_id = ACC.id
GROUP BY keyword_id

对此的 EXPLAIN 产生以下结果 -

+----+-------------+-------+--------+----------------------------+--------------+---------+---------------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys              | key          | key_len | ref                             | rows   | Extra                                        |
+----+-------------+-------+--------+----------------------------+--------------+---------+---------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | SPENT | range  | summary_date               | summary_date | 3       | NULL                            | 752191 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | KW    | eq_ref | PRIMARY,FK1948D0E6ED3A5544 | PRIMARY      | 8       | clicksummarydb.SPENT.keyword_id |      1 |                                              | 
|  1 | SIMPLE      | ADG   | eq_ref | PRIMARY,FKBBC2083C29112FD0 | PRIMARY      | 8       | advertisedb.KW.adgroup_id       |      1 |                                              | 
|  1 | SIMPLE      | CAMP  | eq_ref | PRIMARY,FKF7A90110246F33C4 | PRIMARY      | 8       | advertisedb.ADG.campaign_id     |      1 |                                              | 
|  1 | SIMPLE      | ACC   | eq_ref | PRIMARY                    | PRIMARY      | 8       | advertisedb.CAMP.account_id     |      1 |                                              | 
+----+-------------+-------+--------+----------------------------+--------------+---------+---------------------------------+--------+----------------------------------------------+

keyword_spent 表包含超过 150 万行，这里是在其上显示创建表

 | keyword_spent | CREATE TABLE `keyword_spent` (
   `id` bigint(20) NOT NULL auto_increment,
   `summary_date` date NOT NULL,
   `adgroup_id` bigint(20) NOT NULL,
   `keyword_id` bigint(20) NOT NULL,
   `billed_clicks` int(11) default NULL,
   `un_billed_clicks` int(11) default NULL,
   `spent` decimal(20,5) default NULL,
   `last_click_recno` bigint(20) default NULL,
   `campaign_id` bigint(20) NOT NULL,
   `account_id` bigint(20) NOT NULL,
   `total_convs` bigint(20) unsigned default '0',
    PRIMARY KEY  (`id`),
   UNIQUE KEY `keyword_spent_uniq` (`summary_date`,`adgroup_id`,`keyword_id`),
   KEY `idx_account_id` (`account_id`),
   KEY `idx_kw_id` (`keyword_id`),
   KEY `adgroup_id` (`adgroup_id`),
   KEY `campaign_id` (`campaign_id`),
   KEY `summary_date` (`summary_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 

我不明白为什么在该日期范围内的记录不超过 100,000 条时要扫描近 750,000 行。

此外，为什么它要进行文件排序而不是使用索引。 ？

Answer 1

尝试对连接谓词中引用的所有列建立索引:

CREATE INDEX keyword_spent_IX2 ON keyword_spent (keyword_id, summary_date)

-或-

CREATE INDEX keyword_spent_IX3 ON keyword_spent (summary_date, keyword_id)

- 或者- 您甚至可以创建一个包含查询中引用的所有列的覆盖索引:

CREATE INDEX keyword_spent_IX4 ON keyword_spent (keyword_id, summary_date,
    billed_clicks, un_billed_clicks, spent, total_convs)

---

文件排序操作可能是由于 GROUP BY。

我更喜欢使用 JOIN ... ON 语法，而不是老式的逗号和在 WHERE 子句中混合连接谓词。

  FROM account ACC
  JOIN campaign CAMP ON CAMP.account_id = ACC.id
  JOIN adgroup ADG ON ADG.campaign_id = CAMP.id
  JOIN adgroup_keyword KW ON KW.adgroup_id = ADG.id
  JOIN keyword_spent SPENT ON SPENT.keyword_id = KW.id
 WHERE SPENT.summary_date >= '2012-03-01'
   AND SPENT.summary_date <= '2012-03-04'
 GROUP BY SPENT.id

您仅按 SELECT 列表中非聚合的子集进行分组。大多数其他 RDBMS 都会对此抛出异常； MySQL 更加自由。