奇怪的MySQL查询计划：为什么这个查询使用临时＆filesort？如何优化它？

问题描述：

我有一个查询：奇怪的MySQL查询计划：为什么这个查询使用临时＆filesort？如何优化它？

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.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries.c)从amp_c_countries中选择行时，它不应该只是一个简单的索引查找吗？ EXPLAIN中的rows值为4083,amp_c_countries根据SHOW TABLE STATUS具有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状态。

谢谢。感谢你的帮助。

EDIT：

的 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)

对结果进行排序将需要文件排序。但这并不意味着会使用实际的磁盘文件。 – 2011-03-05 19:22:36

订单结果并不总是需要一个文件夹，特别是当行已经在索引中排序时，就像这里的情况一样。这里也使用磁盘表格，正如我发布的Created_tmp_disk_tables值所示。 – Continuation 2011-03-05 19:31:54

预计将执行filesort操作。它需要满足ORDER BY。请注意，优化器（zone_price）选择的索引没有将price_c作为前导列，并且查询可能会返回具有不同值\ zone的行。所以，返回的行按照'price_c \'的顺序（升序或降序）是不正确的。 – spencer7593 2011-03-05 22:21:33

答

为了防止需要根据所述索引的第二部分排序时排序，，第一部分必须保持恒定。

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

尝试改变的条件只有amp_c.zone = '24'，看看有没有什么改变解释（显然，你不会得到你所需要的结果，但这样做是为了验证我的猜测）...

如果它的工作原理和解释再次使用文件排序没有显示，你有两个选择：

排序索引的所有部分：ORDER BY amp_c.zone, amp_c.price_c。

对区栏只有一个条件，并联合与第二个条件另一个类似的查询，像：

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

我试着用'amp_c.zone ='24''。不幸的是，'EXPLAIN'仍然显示'Using temporary;使用filesort'。有什么建议么？谢谢。 – Continuation 2011-03-05 22:27:22

@Continuation - 刚刚发现我们这个专题的文件：http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html。还有一个问题，有可能是相关的，虽然我不知道你如何改变你的查询来解决它：“你要加入很多表，并在ORDER BY中的列并不是全部来自所使用的第一个非恒定表检索行（这是EXPLAIN不具有一个const联接类型输出的第一个表。）” – Galz 2011-03-05 22:33:50

@Continuation - 也可以尝试到区域添加到ORDER BY子句（之前的价格）。这又是不完全是你在找什么，但可以让我们更接近...... – Galz 2011-03-05 22:39:15

奇怪的MySQL查询计划：为什么这个查询使用临时＆filesort？如何优化它？

相关推荐