sql - 奇怪的 mysql 查询执行时间行为

Question

你好我对几乎相同的 SQL 查询有奇怪的执行时间行为

q1:

SELECT `t1`.`id`, `t1`.`key`, `t1`.`module`, `t2`.`value` 
FROM `translates` AS `t1` 
LEFT JOIN `translates_i18n` AS `t2` ON (`t2`.`id` = `t1`.`id` AND `t2`.`culture` = 'en') 
WHERE `t1`.`module` IN ('GLOBAL','AJAX','FORMS', .... about 15 items) LIMIT 9000;

0.10秒

q2:

SELECT `t1`.`id`, `t1`.`key`, `t2`.`value` 
FROM `translates` AS `t1` 
LEFT JOIN `translates_i18n` AS `t2` ON (`t2`.`id` = `t1`.`id` AND `t2`.`culture` = 'en') 
WHERE `t1`.`module` IN ('GLOBAL','AJAX','FORMS', .... about 15 items) LIMIT 9000;

0.000...秒

表定义:

CREATE TABLE IF NOT EXISTS `translates` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `module` varchar(255) NOT NULL,
  `system` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key` (`key`,`module`),
  KEY `module` (`module`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `translates_i18n` (
  `id` int(11) unsigned NOT NULL,
  `culture` varchar(2) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY (`id`,`culture`),
  KEY `culture` (`culture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `translates_i18n`
  ADD CONSTRAINT `translates_i18n_ibfk_1` FOREIGN KEY (`id`) REFERENCES `translates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

q1 和 q2 之间的差异在 t1.module 列中，也在 where 语句中

我只是看不出问题出在哪里，请任何人指出我

更新

q1:

mysql> EXPLAIN SELECT SQL_NO_CACHE  `t1`.`id` ,  `t1`.`key` ,  `t1`.`module` ,  `t2`.`value` 
    -> FROM  `translates` AS  `t1` 
    -> LEFT JOIN  `translates_i18n` AS  `t2` ON (  `t2`.`id` =  `t1`.`id` 
    -> AND  `t2`.`culture` =  'en' ) 
    -> WHERE  `t1`.`module` 
    -> IN (
    -> 'GLOBAL',  'AJAX',  'FORMS',  'ROOTMENU',  'LANGSWITCHER',  'AUTHORIZATION',  'MENU',  'MINIFY',  'SIMPLESHOP'
    -> )
    -> LIMIT 100500
    -> ;
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref                 | rows | Extra                    |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
|  1 | SIMPLE      | t1    | index  | module          | key     | 1534    | NULL                |  627 | Using where; Using index |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,culture | PRIMARY | 12      | theloom.t1.id,const |    1 |                          |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
2 rows in set (0.00 sec)

q2:

mysql> EXPLAIN SELECT SQL_NO_CACHE  `t1`.`id` ,  `t1`.`key` ,  `t2`.`value` 
    -> FROM  `translates` AS  `t1` 
    -> LEFT JOIN  `translates_i18n` AS  `t2` ON (  `t2`.`id` =  `t1`.`id` 
    -> AND  `t2`.`culture` =  'en' ) 
    -> WHERE  `t1`.`module` 
            -> IN (
    -> 'GLOBAL',  'AJAX',  'FORMS',  'ROOTMENU',  'LANGSWITCHER',  'AUTHORIZATION',  'MENU',  'MINIFY',  'SIMPLESHOP'
    -> )
    -> LIMIT 100500;
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref                 | rows | Extra                    |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
|  1 | SIMPLE      | t1    | index  | module          | key     | 1534    | NULL                |  627 | Using where; Using index |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,culture | PRIMARY | 12      | theloom.t1.id,const |    1 |                          |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
2 rows in set (0.00 sec)

Answer 1

看看这个，我认为原因是由于 the INNODB Buffer Pool 。第一个查询以干净的缓冲区开始，因此在处理查询之前，需要将索引读入内存。然后，当您运行第二个查询时，它已经在内存中，因此运行速度要快得多。

尝试在每个查询之间添加 FLUSH TALBES 命令。

您还可以尝试使用 Benchmark()函数来测试这个。

另一个可能导致差异的因素是要传输的数据的大小。我看到附加列被声明为 VARCHAR(255)。也许该列有大量数据，9000 行确实会显着增加网络开销......

至少要调查一些事情......