mysql、关键及优化: does my key seem to be useless?

Question

这是我完成的sql命令，我不明白为什么没有 key (〜1.45秒)和有 key (〜1.15秒)之间的差异这么小:

mysql> show create table devis;
| devis | CREATE TABLE `devis` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  ...blabla...
  `date_v_creation` datetime default NULL,
  `date_v_fin` datetime default NULL,
  ...blabla...
  `etat` tinyint(4) default NULL,
  `etat_date` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `date_v_creation` (`date_v_creation`),
  KEY `date_v_fin` (`date_v_fin`),
  ...blabla...
) ENGINE=InnoDB AUTO_INCREMENT=3714317 DEFAULT CHARSET=utf8 |

mysql> select date_v_creation from devis
where etat=3
and date_v_fin is null
and TO_DAYS(NOW()) - TO_DAYS(date_v_creation) > 54;

+---------------------+
| date_v_creation     |
+---------------------+
| 2011-10-30 21:44:54 | 
| ...blabla...        | 
| 2011-10-30 21:48:05 | 
+---------------------+
216 rows in set (1.45 sec)

mysql> alter table devis add key( date_v_fin, etat);

mysql> select date_v_creation from devis
where etat=3
and date_v_fin is null
and TO_DAYS(NOW()) - TO_DAYS(date_v_creation) > 54;

+---------------------+
| date_v_creation     |
+---------------------+
| 2011-10-30 21:44:54 | 
| ...blabla...        | 
| 2011-10-30 21:48:05 | 
+---------------------+
216 rows in set (1.13 sec)

如果我做了很多次，它总是在(1.05秒)和(1.15秒)之间。

有很多次我添加了按键来缩短响应时间，当它不起作用时，时间没有改变，而当它起作用时，时间会更改为最小为 10 的因子。

我是不是做错了什么，错过了什么，还是收获这么少是正常的？

非常感谢!

更新

这是解决我的问题的查询，但我仍然不明白为什么它解决了它。

select date_v_creation
from devis
where etat=3
  and date_v_creation < NOW() - INTERVAL 54 DAY
  and date_v_fin is null;   

这是我的“错误”查询的解释:

mysql> EXPLAIN select date_v_creation from devis where etat=3 and date_v_fin is null and TO_DAYS(NOW()) - TO_DAYS(date_v_creation) > 54;
+----+-------------+-------+------+-------------------------+------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys           | key        | key_len | ref   | rows  | Extra       |
+----+-------------+-------+------+-------------------------+------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | devis | ref  | date_v_fin,date_v_fin_2 | date_v_fin | 9       | const | 15913 | Using where |
+----+-------------+-------+------+-------------------------+------------+---------+-------+-------+-------------+
1 row in set (0.34 sec)

以下是“良好”的优化查询的说明:

mysql> explain select date_v_creation from devis where etat=3   and date_v_creation < NOW() - INTERVAL 54 DAY   and date_v_fin is null;
+----+-------------+-------+-------+-----------------------------------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys                           | key             | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+-----------------------------------------+-----------------+---------+------+------+-------------+
|  1 | SIMPLE      | devis | range | date_v_fin,date_v_creation,date_v_fin_2 | date_v_creation | 9       | NULL | 1458 | Using where |
+----+-------------+-------+-------+-----------------------------------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Answer 1

您基本上有两个问题:

1. 查询没有有用的键:这里有用的是包含字段索引的键，包括方程，然后是比较。也就是说，您需要 (etat, date_v_creation) 上的 key

2. 当前无法使用 date_v_creation 上的索引，因为比较是基于字段值的函数而不是值本身。这就是为什么 date_v_creation 上的键没有帮助的原因。因此，您应该按如下方式重写查询:

   select date_v_creation
   from devis
   where etat=3
     and date_v_creation < NOW() - INTERVAL 54 DAY
     and date_v_fin is null;