MySQL查询不同,性能也不同

是下面的查询有什么区别? id是主键

1, select id from tbl_xxx WHERE 2708 <= id <= 1562755185; 2, select id from tbl_xxx WHERE id> = 2708 and id <= 1562755185; 3, select id from tbl_xxx WHERE id between 2708 and 1562755185;

我发现,性能不同,但我不知道为什么?

explain select id from tblData WHERE id >= 2708 and id <= 1562755185 ORDER BY id asc LIMIT 652000, 1000\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tblData type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 30461948 Extra: Using where; Using index explain select id from tblData WHERE 2708 <= id <= 1562755185 ORDER BY id asc LIMIT 652000, 1000\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tblData type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 653000 Extra: Using where; Using index

--------------解决方案-------------

查询23是等效所以应该执行相同的。 查询1是完全不同的东西; 查询1等效于此:

select id from tbl_xxx WHERE (2708 <= id) <= 1562755185;

自从2708 <= id是一个布尔表达式,MySQL中是零和一以布尔,查询总是比较1 <= 15627551850 <= 1562755185 ,从而退化到这样的:

select id from tbl_xxx;

假设id是从来没有空,当然。

因此,查询有很大的不同,而且也没有理由指望他​​们来执行相同的。

例如,我有一个表周围踢如下:

mysql> select * from stars;
+----+-------+
| id | stars |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 2 |
| 9 | 1 |
+----+-------+

between做到这一点:

mysql> select * from stars where id between 3 and 5;
+----+-------+
| id | stars |
+----+-------+
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+----+-------+

但是,你的a <= id <= b做到这一点:

mysql> select * from stars where 3 <= id <= 5;
+----+-------+
| id | stars |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 2 |
| 9 | 1 |
+----+-------+

分类:MySQL的 时间:2015-03-15 人气:0
本文关键词: MySQL和SQL
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.398 (s). 10 q(s)