对于大表查询时间范围的一点疑问

Question

我有一张 430W 数据的大表，这表 有create_time 的字段查询语句 如下：select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:39:25' and pl.point_id = 250;
就是时间区域为7分钟
费时 5.31S 
如果改成select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:38:25' and pl.point_id = 250;就是时间区域为6分钟。
费时 0.03S。create_time point_id 都已经建立索引请问 是什么原因 造成 这么大的差距？
最好能解释一下原理

Answer 1

贴出你的explain select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:38:25' and pl.point_id = 250; explain select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:39:25' and pl.point_id = 250;show index from tbl_zb_point_log 以供分析。
另外做对比的时候，建议同一语句至少执行三次以上来对比。

Answer 2

（没有任何根据的）IMHO，第二句SQL的结果集是第一句结果集的子集，而且，相关索引已经加载过，只要筛选其子集即可。

Answer 3

楼上的解释 我都执行过了 我不光是多次执行 select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:38:25' and pl.point_id = 250;
也有更改时间区域，包区域没有和第一个时间段重复。
测试结果都是我所的一样，6分钟以下 非常快，6分钟以上 很慢。show index from tbl_zb_point_log
    -> ;
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_zb_point_log |          0 | PRIMARY     |            1 | id          | A         |     3803895 | NULL     | NULL   |      | BTREE      |         |
| tbl_zb_point_log |          1 | point_id    |            1 | point_id    | A         |        1344 | NULL     | NULL   |      | BTREE      |         |
| tbl_zb_point_log |          1 | node_log_id |            1 | node_log_id | A         |      760779 | NULL     | NULL   |      | BTREE      |         |
| tbl_zb_point_log |          1 | create_time |            1 | create_time | A         |      271706 | NULL     | NULL   |      | BTREE      |         |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.
create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:38:25'
 and pl.point_id = 250;
+---------+-------+---------------------+
| id      | data  | create_time         |
+---------+-------+---------------------+
| 3363210 | 73776 | 2011-09-08 01:32:30 |
| 3363222 | 73776 | 2011-09-08 01:32:30 |
| 3363234 | 73776 | 2011-09-08 01:32:31 |
| 3363924 | 73804 | 2011-09-08 01:32:58 |
| 3363936 | 73804 | 2011-09-08 01:32:58 |
| 3363948 | 73804 | 2011-09-08 01:32:59 |
| 3364632 | 74028 | 2011-09-08 01:33:26 |
| 3364644 | 74028 | 2011-09-08 01:33:27 |
| 3365346 | 74056 | 2011-09-08 01:33:54 |
| 3365358 | 74056 | 2011-09-08 01:33:54 |
| 3365370 | 74056 | 2011-09-08 01:33:55 |
| 3366060 | 74280 | 2011-09-08 01:34:22 |
| 3366072 | 74280 | 2011-09-08 01:34:23 |
| 3366084 | 74280 | 2011-09-08 01:34:23 |
| 3366768 | 74308 | 2011-09-08 01:34:50 |
| 3366780 | 74308 | 2011-09-08 01:34:51 |
| 3367482 | 74532 | 2011-09-08 01:35:18 |
| 3367494 | 74532 | 2011-09-08 01:35:18 |
| 3367506 | 74532 | 2011-09-08 01:35:19 |
| 3368196 | 74560 | 2011-09-08 01:35:46 |
| 3368208 | 74560 | 2011-09-08 01:35:46 |
| 3368220 | 74560 | 2011-09-08 01:35:47 |
| 3368904 | 74784 | 2011-09-08 01:36:15 |
| 3368916 | 74784 | 2011-09-08 01:36:15 |
| 3369618 | 74812 | 2011-09-08 01:36:42 |
| 3369630 | 74812 | 2011-09-08 01:36:42 |
| 3369642 | 74812 | 2011-09-08 01:36:43 |
| 3370332 | 75036 | 2011-09-08 01:37:10 |
| 3370344 | 75036 | 2011-09-08 01:37:10 |
| 3370356 | 75036 | 2011-09-08 01:37:11 |
| 3371040 | 75064 | 2011-09-08 01:37:38 |
| 3371052 | 75064 | 2011-09-08 01:37:39 |
| 3371754 | 75288 | 2011-09-08 01:38:06 |
| 3371766 | 75288 | 2011-09-08 01:38:06 |
| 3371778 | 75288 | 2011-09-08 01:38:07 |
+---------+-------+---------------------+
35 rows in set (0.03 sec)
mysql> select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.
create_time >= '2011-09-08 01:50:25' and pl.create_time <= '2011-09-08 01:57:25'
 and pl.point_id = 250;
+---------+-------+---------------------+
| id      | data  | create_time         |
+---------+-------+---------------------+
| 3390978 | 78396 | 2011-09-08 01:50:42 |
| 3390990 | 78396 | 2011-09-08 01:50:42 |
| 3391002 | 78396 | 2011-09-08 01:50:43 |
| 3391692 | 78620 | 2011-09-08 01:51:10 |
| 3391704 | 78620 | 2011-09-08 01:51:10 |
| 3391716 | 78620 | 2011-09-08 01:51:11 |
| 3392400 | 78648 | 2011-09-08 01:51:38 |
| 3392412 | 78648 | 2011-09-08 01:51:39 |
| 3393114 | 78872 | 2011-09-08 01:52:06 |
| 3393126 | 78872 | 2011-09-08 01:52:07 |
| 3393138 | 78872 | 2011-09-08 01:52:07 |
| 3393828 | 78900 | 2011-09-08 01:52:34 |
| 3393840 | 78900 | 2011-09-08 01:52:35 |
| 3393852 | 78900 | 2011-09-08 01:52:35 |
| 3394536 | 79124 | 2011-09-08 01:53:02 |
| 3394548 | 79124 | 2011-09-08 01:53:03 |
| 3395250 | 79152 | 2011-09-08 01:53:30 |
| 3395262 | 79152 | 2011-09-08 01:53:30 |
| 3395274 | 79152 | 2011-09-08 01:53:31 |
| 3395964 | 79180 | 2011-09-08 01:53:58 |
| 3395976 | 79180 | 2011-09-08 01:53:58 |
| 3395988 | 79180 | 2011-09-08 01:53:59 |
| 3396672 | 79404 | 2011-09-08 01:54:27 |
| 3396684 | 79404 | 2011-09-08 01:54:27 |
| 3397386 | 79432 | 2011-09-08 01:54:54 |
| 3397398 | 79432 | 2011-09-08 01:54:54 |
| 3397410 | 79432 | 2011-09-08 01:54:55 |
| 3398100 | 79656 | 2011-09-08 01:55:22 |
| 3398112 | 79656 | 2011-09-08 01:55:22 |
| 3398124 | 79656 | 2011-09-08 01:55:23 |
| 3398808 | 79684 | 2011-09-08 01:55:50 |
| 3398820 | 79684 | 2011-09-08 01:55:51 |
| 3399522 | 79908 | 2011-09-08 01:56:18 |
| 3399534 | 79908 | 2011-09-08 01:56:19 |
| 3399546 | 79908 | 2011-09-08 01:56:19 |
| 3400236 | 79936 | 2011-09-08 01:56:46 |
| 3400248 | 79936 | 2011-09-08 01:56:46 |
| 3400260 | 79936 | 2011-09-08 01:56:47 |
| 3400944 | 80160 | 2011-09-08 01:57:14 |
| 3400956 | 80160 | 2011-09-08 01:57:15 |
+---------+-------+---------------------+
40 rows in set (5.08 sec)第二遍查询 和上一个语句一模一样mysql> select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.
create_time >= '2011-09-08 01:50:25' and pl.create_time <= '2011-09-08 01:57:25'
 and pl.point_id = 250;
+---------+-------+---------------------+
| id      | data  | create_time         |
+---------+-------+---------------------+
| 3390978 | 78396 | 2011-09-08 01:50:42 |
| 3390990 | 78396 | 2011-09-08 01:50:42 |
| 3391002 | 78396 | 2011-09-08 01:50:43 |
| 3391692 | 78620 | 2011-09-08 01:51:10 |
| 3391704 | 78620 | 2011-09-08 01:51:10 |
| 3391716 | 78620 | 2011-09-08 01:51:11 |
| 3392400 | 78648 | 2011-09-08 01:51:38 |
| 3392412 | 78648 | 2011-09-08 01:51:39 |
| 3393114 | 78872 | 2011-09-08 01:52:06 |
| 3393126 | 78872 | 2011-09-08 01:52:07 |
| 3393138 | 78872 | 2011-09-08 01:52:07 |
| 3393828 | 78900 | 2011-09-08 01:52:34 |
| 3393840 | 78900 | 2011-09-08 01:52:35 |
| 3393852 | 78900 | 2011-09-08 01:52:35 |
| 3394536 | 79124 | 2011-09-08 01:53:02 |
| 3394548 | 79124 | 2011-09-08 01:53:03 |
| 3395250 | 79152 | 2011-09-08 01:53:30 |
| 3395262 | 79152 | 2011-09-08 01:53:30 |
| 3395274 | 79152 | 2011-09-08 01:53:31 |
| 3395964 | 79180 | 2011-09-08 01:53:58 |
| 3395976 | 79180 | 2011-09-08 01:53:58 |
| 3395988 | 79180 | 2011-09-08 01:53:59 |
| 3396672 | 79404 | 2011-09-08 01:54:27 |
| 3396684 | 79404 | 2011-09-08 01:54:27 |
| 3397386 | 79432 | 2011-09-08 01:54:54 |
| 3397398 | 79432 | 2011-09-08 01:54:54 |
| 3397410 | 79432 | 2011-09-08 01:54:55 |
| 3398100 | 79656 | 2011-09-08 01:55:22 |
| 3398112 | 79656 | 2011-09-08 01:55:22 |
| 3398124 | 79656 | 2011-09-08 01:55:23 |
| 3398808 | 79684 | 2011-09-08 01:55:50 |
| 3398820 | 79684 | 2011-09-08 01:55:51 |
| 3399522 | 79908 | 2011-09-08 01:56:18 |
| 3399534 | 79908 | 2011-09-08 01:56:19 |
| 3399546 | 79908 | 2011-09-08 01:56:19 |
| 3400236 | 79936 | 2011-09-08 01:56:46 |
| 3400248 | 79936 | 2011-09-08 01:56:46 |
| 3400260 | 79936 | 2011-09-08 01:56:47 |
| 3400944 | 80160 | 2011-09-08 01:57:14 |
| 3400956 | 80160 | 2011-09-08 01:57:15 |
+---------+-------+---------------------+
40 rows in set (5.72 sec)所以没有所谓的缓冲 机制!

Answer 4

我对第2楼解释一下，我先执行时间区域大的，在执行小的。我们看下效果mysql> select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.
create_time >= '2011-09-08 02:04:25' and pl.create_time <= '2011-09-08 02:10:25'
 and pl.point_id = 250;
+---------+--------+---------------------+
| id      | data   | create_time         |
+---------+--------+---------------------+
| 3411996 | 132144 | 2011-09-08 02:04:30 |
| 3412008 | 132144 | 2011-09-08 02:04:30 |
| 3412020 | 132144 | 2011-09-08 02:04:31 |
| 3412710 | 132172 | 2011-09-08 02:04:58 |
| 3412722 | 132172 | 2011-09-08 02:04:58 |
| 3412734 | 132172 | 2011-09-08 02:04:59 |
| 3413418 | 132396 | 2011-09-08 02:05:26 |
| 3413430 | 132396 | 2011-09-08 02:05:27 |
| 3414132 | 132424 | 2011-09-08 02:05:54 |
| 3414144 | 132424 | 2011-09-08 02:05:54 |
| 3414156 | 132424 | 2011-09-08 02:05:55 |
| 3414846 | 132648 | 2011-09-08 02:06:22 |
| 3414858 | 132648 | 2011-09-08 02:06:22 |
| 3414870 | 132648 | 2011-09-08 02:06:23 |
| 3415554 | 132676 | 2011-09-08 02:06:50 |
| 3415566 | 132676 | 2011-09-08 02:06:51 |
| 3416268 | 132900 | 2011-09-08 02:07:18 |
| 3416280 | 132900 | 2011-09-08 02:07:18 |
| 3416292 | 132900 | 2011-09-08 02:07:19 |
| 3416982 | 132928 | 2011-09-08 02:07:46 |
| 3416994 | 132928 | 2011-09-08 02:07:47 |
| 3417006 | 132928 | 2011-09-08 02:07:47 |
| 3417690 | 133152 | 2011-09-08 02:08:14 |
| 3417702 | 133152 | 2011-09-08 02:08:15 |
| 3418404 | 133180 | 2011-09-08 02:08:42 |
| 3418416 | 133180 | 2011-09-08 02:08:42 |
| 3418428 | 133180 | 2011-09-08 02:08:43 |
| 3419118 | 133404 | 2011-09-08 02:09:10 |
| 3419130 | 133404 | 2011-09-08 02:09:11 |
| 3419142 | 133404 | 2011-09-08 02:09:11 |
| 3419826 | 133432 | 2011-09-08 02:09:38 |
| 3419838 | 133432 | 2011-09-08 02:09:39 |
| 3420540 | 133656 | 2011-09-08 02:10:06 |
| 3420552 | 133656 | 2011-09-08 02:10:06 |
| 3420564 | 133656 | 2011-09-08 02:10:07 |
+---------+--------+---------------------+
35 rows in set (0.17 sec)
区域大的mysql> select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.
create_time >= '2011-09-08 02:04:25' and pl.create_time <= '2011-09-08 02:11:25'
 and pl.point_id = 250;
+---------+--------+---------------------+
| id      | data   | create_time         |
+---------+--------+---------------------+
| 3411996 | 132144 | 2011-09-08 02:04:30 |
| 3412008 | 132144 | 2011-09-08 02:04:30 |
| 3412020 | 132144 | 2011-09-08 02:04:31 |
| 3412710 | 132172 | 2011-09-08 02:04:58 |
| 3412722 | 132172 | 2011-09-08 02:04:58 |
| 3412734 | 132172 | 2011-09-08 02:04:59 |
| 3413418 | 132396 | 2011-09-08 02:05:26 |
| 3413430 | 132396 | 2011-09-08 02:05:27 |
| 3414132 | 132424 | 2011-09-08 02:05:54 |
| 3414144 | 132424 | 2011-09-08 02:05:54 |
| 3414156 | 132424 | 2011-09-08 02:05:55 |
| 3414846 | 132648 | 2011-09-08 02:06:22 |
| 3414858 | 132648 | 2011-09-08 02:06:22 |
| 3414870 | 132648 | 2011-09-08 02:06:23 |
| 3415554 | 132676 | 2011-09-08 02:06:50 |
| 3415566 | 132676 | 2011-09-08 02:06:51 |
| 3416268 | 132900 | 2011-09-08 02:07:18 |
| 3416280 | 132900 | 2011-09-08 02:07:18 |
| 3416292 | 132900 | 2011-09-08 02:07:19 |
| 3416982 | 132928 | 2011-09-08 02:07:46 |
| 3416994 | 132928 | 2011-09-08 02:07:47 |
| 3417006 | 132928 | 2011-09-08 02:07:47 |
| 3417690 | 133152 | 2011-09-08 02:08:14 |
| 3417702 | 133152 | 2011-09-08 02:08:15 |
| 3418404 | 133180 | 2011-09-08 02:08:42 |
| 3418416 | 133180 | 2011-09-08 02:08:42 |
| 3418428 | 133180 | 2011-09-08 02:08:43 |
| 3419118 | 133404 | 2011-09-08 02:09:10 |
| 3419130 | 133404 | 2011-09-08 02:09:11 |
| 3419142 | 133404 | 2011-09-08 02:09:11 |
| 3419826 | 133432 | 2011-09-08 02:09:38 |
| 3419838 | 133432 | 2011-09-08 02:09:39 |
| 3420540 | 133656 | 2011-09-08 02:10:06 |
| 3420552 | 133656 | 2011-09-08 02:10:06 |
| 3420564 | 133656 | 2011-09-08 02:10:07 |
| 3421254 | 133684 | 2011-09-08 02:10:34 |
| 3421266 | 133684 | 2011-09-08 02:10:34 |
| 3421278 | 133684 | 2011-09-08 02:10:35 |
| 3421962 | 133908 | 2011-09-08 02:11:02 |
| 3421974 | 133908 | 2011-09-08 02:11:03 |
+---------+--------+---------------------+
40 rows in set (6.08 sec)

Answer 5

那就请直接执行 
explain select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 01:38:25' and pl.point_id = 250;然后贴出结果。 类似这种别人提示的东西，如果不清楚，建议自己先GOOGLE或者百度一样。否则你也不知道别人会什么时候再看这个贴子给你解释。

Answer 6

mysql> explain select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl w
here pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 0
1:38:25' and pl.point_id = 250;
+----+-------------+-------+-------+----------------------+-------------+-------
--+------+-------+-------------+
| id | select_type | table | type  | possible_keys        | key         | key_le
n | ref  | rows  | Extra       |
+----+-------------+-------+-------+----------------------+-------------+-------
--+------+-------+-------------+
|  1 | SIMPLE      | pl    | range | point_id,create_time | create_time | 4
  | NULL | 18560 | Using where |
+----+-------------+-------+-------+----------------------+-------------+-------
--+------+-------+-------------+
1 row in set (0.08 sec)mysql> explain select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl w
here pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 0
1:39:25' and pl.point_id = 250;
+----+-------------+-------+------+----------------------+----------+---------+-
------+-------+-------------+
| id | select_type | table | type | possible_keys        | key      | key_len |
ref   | rows  | Extra       |
+----+-------------+-------+------+----------------------+----------+---------+-
------+-------+-------------+
|  1 | SIMPLE      | pl    | ref  | point_id,create_time | point_id | 8       |
const | 22644 | Using where |
+----+-------------+-------+------+----------------------+----------+---------+-
------+-------+-------------+
1 row in set (0.00 sec)
两个explain  都已经 出来  

Answer 7

从上面 EXPLAIN 分析的结果 我 看出 索引 可能是造成查询时间不一样的凶手， 于是 我用select pl.id,pl.data,pl.create_time from tbl_zb_point_log as pl FORCE INDEX (create_time) where pl.create_time >= '2011-09-08 01:32:25' and pl.create_time <= '2011-09-08 02:50:25' and pl.point_id = 250;时间 为0.27s说明索引 真的是造成这个问题的 凶手，但是MYSQl 为什么会对两次 查询的使用不一样的索引呢？
仅仅是因为时间区域大了一分钟？希望 楼上的 大大 给我解释一下

Answer 8

MYSQL执行计划会根据查询条件，去自动选择索引，绝大多数情况下，它的选择都是可靠的，但也不排除偶尔的失误。由于2次查询条件不同，导致开销也不同，那么就可能存在不同的执行计划了。

Answer 9

大大 你能帮我解释一下吗？还有我对复合索引 也有点疑问mysql> explain select * from tbl_zb_point_log where create_time >= '2011-08-17 1
0:42:00' and create_time <= '2011-09-25 00:00:00' and point_id = 296;
+----+-------------+------------------+------+---------------+------+---------+-
-----+--------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len |
ref  | rows   | Extra       |
+----+-------------+------------------+------+---------------+------+---------+-
-----+--------+-------------+
|  1 | SIMPLE      | tbl_zb_point_log | ALL  | create_time   | NULL | NULL    |
NULL | 321975 | Using where |
+----+-------------+------------------+------+---------------+------+---------+-
-----+--------+-------------+
1 row in set (0.00 sec)执行上面语句的 Index为 create_time(create_time，point_id)
mysql> explain select * from tbl_zb_point_log where create_time >= '2011-08-17 1
0:42:00' and create_time <= '2011-09-25 00:00:00' and point_id = 296;
+----+-------------+------------------+-------+---------------+-------------+---
------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key         | ke
y_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+-------------+---
------+------+------+-------------+
|  1 | SIMPLE      | tbl_zb_point_log | range | create_time   | create_time | 12
      | NULL | 3402 | Using where |
+----+-------------+------------------+-------+---------------+-------------+---
------+------+------+-------------+
1 row in set (0.13 sec)执行上面语句的 Index为 create_time(point_id，create_time)
我的疑问是 第一次 不能用索引 第二次就可以用索引 复合索引的 顺序 不一样 会到怎么样的情况 w
谢谢 有人帮我解答一下吗  我GOOGLE 找不到的内容