奇怪的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值select行之后，所有结果行都按照price_c的顺序排列。 由于查询是ORDER BY price_c，所以根本不需要Using temporary和Using filesort 。 我错过了什么？

• 很多插入的MySQL优化很lessselect环境
• 如果我要为DBA开发一个软件来帮助他们进行查询优化，我可以使用哪些function？
• SQL Express 08 R2在Windows数据中心服务器08上运行优化
• MySQL的InnoDB数据库“挂起”
• 最大限度地减less压力

2）对于第三张表，应该是使用索引work 。 但是ref是NULL 。 那是什么意思？ work由专栏(ad,c,country) 。 因此，当使用WHERE子句从amp_c_countries中select行(amp_c_countries.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries.c) ，不应该只是简单的索引查找吗？ EXPLAIN的rows值是4083， amp_c_countries根据SHOW TABLE STATUS有4113行。 这是否意味着MySQL正在做一个完整的索引扫描，而不是查找？

3）如何解决上述两个问题的任何想法？ amp_ads包含TEXT列，所以正在创build大量磁盘临时表：

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