mysql - 奇怪的MySQL查询计划: why is this query using temporary & filesort?如何优化？

Question

我有一个问题:

SELECT *
FROM amp_ads,amp_c,amp_c_countries    
WHERE 
(amp_c.zone = '24' OR amp_c.zone = '25') AND 
amp_ads.ad_complete = '1' AND 
amp_ads.ad_type = '17' AND 
amp_ads.accept = '1' AND 
amp_ads.en_w = '1' AND 
amp_c.en_u = '1' AND 
amp_c.en_w = '1' AND 
(amp_c.i_nu>'0' OR amp_c.c_nu>'0' OR amp_c.d_valid_by>'1299341823' OR amp_c.unlimit='1') AND 
(amp_c.i_d_max='0' OR amp_c.i_d_nu>'0') AND 
(amp_c.c_d_max='0' OR  amp_c.c_d_nu>'0') AND 
amp_c.t1<'1299341823' AND 
amp_c.t2>'1299341823' AND 
amp_c.d7 = '1'  AND 
(amp_c.some_countr = '0' OR (amp_c_countries.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries.c))  AND 
amp_c.n = amp_ads.n AND 
amp_ads.def = 0       
ORDER BY amp_c.price_c desc LIMIT 1 

(其实不是SELECT *，不过我简化了SELECT子句，让它不那么乱。)

上述查询的 EXPLAIN 的输出是:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: amp_c
         type: ref
possible_keys: work,n_index,zone_price
          key: zone_price
      key_len: 4
          ref: const
         rows: 79
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: amp_ads
         type: eq_ref
possible_keys: n,work
          key: n
      key_len: 4
          ref: advertis_admpro.amp_c.n
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: amp_c_countries
         type: index
possible_keys: work
          key: work
      key_len: 12
          ref: NULL
         rows: 4083
        Extra: Using where; Using index; Using join buffer

1) 为什么第一个表 Using temporary 和 Using filesort？ EXPLAIN 表明它正在使用索引 zone_price，它由 2 列组成:(zone, price_c)。所以使用索引根据zone值选择行后，得到的所有行都是price_c的顺序。由于查询是 ORDER BY price_c，因此根本不需要 Using temporary 和 Using filesort。我错过了什么？

2) 对于第 3 个表，它应该使用索引 work。但是 ref 是 NULL。这意味着什么？ work 由 (ad,c,country) 列组成。因此，当使用 WHERE 子句从 amp_c_countries 中选择行时 (amp_c_countries.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries .c)，不应该只是简单的索引查找吗？ EXPLAIN 中的rows 值为 4083，根据 SHOW TABLE STATUS，amp_c_countries 有 4113 行。这是否意味着 MySQL 正在执行完整索引扫描而不是查找？

3) 关于如何解决上述 2 个问题的任何想法？ amp_ads 包含 TEXT 列，因此正在创建大量磁盘临时表:

| Created_tmp_disk_tables               | 906952      |
| Created_tmp_files                     | 11          |
| Created_tmp_tables                    | 912227      |

show processlist 也显示很多进程处于Copying to tmp table状态。

谢谢。感谢您的帮助。

编辑:

SHOW CREATE TABLE 的输出:

mysql> SHOW CREATE TABLE `advertis_admpro`.`amp_c`\G
*************************** 1. row ***************************
       Table: amp_c
Create Table: CREATE TABLE `amp_c` (
  `n` int(10) unsigned NOT NULL DEFAULT '0',
  `camp` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `zone` int(11) NOT NULL DEFAULT '0',
  `javascript` tinyint(1) NOT NULL DEFAULT '0',
  `banner_target` varchar(50) NOT NULL DEFAULT '',
  `accept` tinyint(1) NOT NULL DEFAULT '0',
  `en_u` tinyint(1) NOT NULL DEFAULT '0',
  `en_w` tinyint(1) NOT NULL DEFAULT '0',
  `i_got` int(10) unsigned NOT NULL DEFAULT '0',
  `c_got` int(10) unsigned NOT NULL DEFAULT '0',
  `r` double(4,2) unsigned NOT NULL DEFAULT '0.00',
  `price_i` double(10,6) unsigned NOT NULL,
  `price_c` double(10,3) unsigned NOT NULL,
  `i_nu` int(11) NOT NULL DEFAULT '0',
  `c_nu` int(11) NOT NULL DEFAULT '0',
  `unlimit` tinyint(1) NOT NULL DEFAULT '0',
  `d_total` int(10) unsigned NOT NULL DEFAULT '0',
  `d_valid_by` int(10) unsigned NOT NULL DEFAULT '0',
  `t1` int(10) unsigned NOT NULL DEFAULT '0',
  `t2` int(10) unsigned NOT NULL DEFAULT '0',
  `d1` tinyint(1) NOT NULL DEFAULT '0',
  `d2` tinyint(1) NOT NULL DEFAULT '0',
  `d3` tinyint(1) NOT NULL DEFAULT '0',
  `d4` tinyint(1) NOT NULL DEFAULT '0',
  `d5` tinyint(1) NOT NULL DEFAULT '0',
  `d6` tinyint(1) NOT NULL DEFAULT '0',
  `d7` tinyint(1) NOT NULL DEFAULT '0',
  `tz1` tinyint(1) NOT NULL DEFAULT '0',
  `tz2` tinyint(1) NOT NULL DEFAULT '0',
  `tz3` tinyint(1) NOT NULL DEFAULT '0',
  `tz4` tinyint(1) NOT NULL DEFAULT '0',
  `tz5` tinyint(1) NOT NULL DEFAULT '0',
  `some_countr` tinyint(1) NOT NULL DEFAULT '0',
  `i_d_max` int(10) unsigned NOT NULL DEFAULT '0',
  `c_d_max` int(10) unsigned NOT NULL DEFAULT '0',
  `i_d_nu` int(10) unsigned NOT NULL DEFAULT '0',
  `c_d_nu` int(10) unsigned NOT NULL DEFAULT '0',
  `last` int(10) unsigned NOT NULL DEFAULT '0',
  `user` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(15) NOT NULL DEFAULT '',
  `emailed` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `work` (`en_u`,`en_w`,`i_nu`,`c_nu`,`d_valid_by`,`unlimit`,`i_d_max`,`c_d_max`,`i_d_nu`,`c_d_nu`,`t1`,`t2`,`n`),
  KEY `n_index` (`n`,`camp`),
  KEY `zone_price` (`zone`,`price_c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `advertis_admpro`.`amp_ads`\G
*************************** 1. row ***************************
       Table: amp_ads
Create Table: CREATE TABLE `amp_ads` (
  `n` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `ad_type` int(10) unsigned NOT NULL DEFAULT '0',
  `accept` tinyint(1) NOT NULL DEFAULT '0',
  `en_w` tinyint(1) NOT NULL DEFAULT '0',
  `weight` tinyint(1) NOT NULL DEFAULT '0',
  `w` smallint(5) unsigned NOT NULL DEFAULT '0',
  `h` smallint(5) unsigned NOT NULL DEFAULT '0',
  `norepeat` int(10) unsigned NOT NULL DEFAULT '0',
  `campaigns` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `zones` text NOT NULL,
  `keywords` text NOT NULL,
  `banner` varchar(255) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alt` varchar(255) NOT NULL DEFAULT '',
  `raw` text NOT NULL,
  `kind` varchar(40) NOT NULL DEFAULT '',
  `javascript` tinyint(1) NOT NULL DEFAULT '0',
  `ad_complete` tinyint(1) NOT NULL DEFAULT '0',
  `url1` text NOT NULL,
  `url2` text NOT NULL,
  `url3` text NOT NULL,
  `text1` text NOT NULL,
  `text2` text NOT NULL,
  `text3` text NOT NULL,
  `text4` text NOT NULL,
  `text5` text NOT NULL,
  `text6` text NOT NULL,
  `text7` text NOT NULL,
  `text8` text NOT NULL,
  `text9` text NOT NULL,
  `text10` text NOT NULL,
  `picture1` varchar(255) NOT NULL DEFAULT '',
  `picture2` varchar(255) NOT NULL DEFAULT '',
  `picture3` varchar(255) NOT NULL DEFAULT '',
  `picture4` varchar(255) NOT NULL DEFAULT '',
  `picture5` varchar(255) NOT NULL DEFAULT '',
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `user` int(11) NOT NULL DEFAULT '0',
  `username` varchar(15) NOT NULL DEFAULT '',
  `preview` text NOT NULL,
  `def` tinyint(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `n` (`n`),
  KEY `work` (`ad_type`,`accept`,`en_w`,`norepeat`,`ad_complete`,`def`)
) ENGINE=InnoDB AUTO_INCREMENT=1532 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `advertis_admpro`.`amp_c_countries`\G
*************************** 1. row ***************************
       Table: amp_c_countries
Create Table: CREATE TABLE `amp_c_countries` (
  `ad` int(10) unsigned NOT NULL DEFAULT '0',
  `c` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `country` varchar(5) NOT NULL DEFAULT '',
  KEY `work` (`ad`,`c`,`country`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Answer 1

为了避免需要排序，按照索引的第二部分排序时，第一部分必须保持不变。

在您的情况下，第一部分的条件是 amp_c.zone = '24' OR amp_c.zone = '25'，这可能不够好。

尝试将条件更改为仅 amp_c.zone = '24'，看看是否会改变解释(显然您不会获得所需的所有结果，但这样做是为了验证我的猜测) ...

如果它有效并且解释没有再次显示 using filesort，您有 2 个选择:

1. 按索引的所有部分排序:ORDER BY amp_c.zone, amp_c.price_c。
2. 在区域列上只有一个条件，并与第二个条件的另一个类似查询合并，例如:

---

(SELECT  ...  WHERE zone = 24 ... ORDER BY price_c)
UNION
(SELECT  ...  WHERE zone = 25 ... ORDER BY price_c)
ORDER BY price_c