☰

奇怪的 MySQL 查询计划：为什么此查询使用临时文件和文件排序？如何优化它？

tag-icon • tag-icon mysql sql optimization
奇怪的 MySQL 查询计划：为什么此查询使用临时文件和文件排序？如何优化它？

我有一个疑问：

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) 对于第三张表，它应该使用索引work。但是 ref 是NULL。那是什么意思？work由列组成。因此，当使用子句(ad,c,country)从中选择行时，难道不应该只是简单的索引查找吗？中的值为4083，根据有 4113 行。这是否意味着 MySQL 正在执行完整索引扫描而不是查找？amp_c_countriesWHERE(amp_c_countries.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries.c)rowsEXPLAINamp_c_countriesSHOW TABLE STATUS

3)关于如何解决上述两个问题，您有什么想法吗？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)

答案1

当值在条件中使用时，往往不使用索引OR。(amp_c.zone = '24' OR amp_c.zone = '25')可能更好地写为(amp_c.zone BETWEEN '24' AND '25')`。

当索引出现在 OR 的一侧而不出现在另一侧时，通常不使用索引。使用amp_c.some_countr = '0' OR (...)可能会使索引的使用无效。

优化器在处理简单的等式时效果更好。您可能需要外连接而不是内连接。

相关内容