limit的使用

作用是 偏移+限制长度

mysql> select * from user;
+----+-------------+-----+-----+
| id | name        | age | sex |
+----+-------------+-----+-----+
|  4 | zhang san   |  20 | M   |
|  5 | li si       |  21 | W   |
|  6 | zhang xiang |  20 | M   |
|  7 | zhang ai    |  21 | W   |
|  8 | wu liu      |  21 | M   |
+----+-------------+-----+-----+
5 rows in set (0.00 sec)

mysql> select * from user limit 3 offset 1;
+----+-------------+-----+-----+
| id | name        | age | sex |
+----+-------------+-----+-----+
|  5 | li si       |  21 | W   |
|  6 | zhang xiang |  20 | M   |
|  7 | zhang ai    |  21 | W   |
+----+-------------+-----+-----+
3 rows in set (0.00 sec)

mysql> select * from user limit 1,3;
+----+-------------+-----+-----+
| id | name        | age | sex |
+----+-------------+-----+-----+
|  5 | li si       |  21 | W   |
|  6 | zhang xiang |  20 | M   |
|  7 | zhang ai    |  21 | W   |
+----+-------------+-----+-----+
3 rows in set (0.00 sec)

mysql> select * from user limit 3;
+----+-------------+-----+-----+
| id | name        | age | sex |
+----+-------------+-----+-----+
|  4 | zhang san   |  20 | M   |
|  5 | li si       |  21 | W   |
|  6 | zhang xiang |  20 | M   |
+----+-------------+-----+-----+
3 rows in set (0.00 sec)

limit的作用

先创建2百万的测试数据

mysql> create table t_user(
    -> id int primary key not null auto_increment,
    -> email varchar(255) default null,
    -> password varchar(255) default null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $
mysql> Create Procedure add_t_user (IN n INT)
    -> BEGIN
    -> DECLARE i INT;
    -> SET i=0;
    -> WHILE i<n DO
    -> INSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@qq.com'),i+1);
    -> SET i=i+1;
    -> END WHILE;
    -> END$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call add_t_user(2000000);
Query OK, 1 row affected (7 min 8.89 sec)

确实是插入了2百万个，并且遍历一遍需要大约0.4秒。

mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.38 sec)

由于 email 没有索引而 id 有索引（primary key默认的），所以在运行 select 时两种方式有天壤之别。

mysql> select * from t_user where email = '1@qq.com';
+----+----------+----------+
| id | email    | password |
+----+----------+----------+
|  1 | 1@qq.com | 1        |
+----+----------+----------+
1 row in set (0.60 sec)

mysql> select * from t_user where id = '1000000';
+---------+----------------+----------+
| id      | email          | password |
+---------+----------------+----------+
| 1000000 | 1000000@qq.com | 1000000  |
+---------+----------------+----------+
1 row in set (0.00 sec)

可以用 limit 及时终止查询

mysql> select * from t_user where email = '1000000@qq.com' limit 1;
+---------+----------------+----------+
| id      | email          | password |
+---------+----------------+----------+
| 1000000 | 1000000@qq.com | 1000000  |
+---------+----------------+----------+
1 row in set (0.21 sec)

也可以把自己和 id 产生联系。这也是解决偏移量的一个方法（偏移量O(n)）

mysql> select * from t_user where id >= '1000000' limit 1;
+---------+----------------+----------+
| id      | email          | password |
+---------+----------------+----------+
| 1000000 | 1000000@qq.com | 1000000  |
+---------+----------------+----------+
1 row in set (0.00 sec)

mysql> select * from t_user where id >= '1000000' limit 5;
+---------+----------------+----------+
| id      | email          | password |
+---------+----------------+----------+
| 1000000 | 1000000@qq.com | 1000000  |
| 1000001 | 1000001@qq.com | 1000001  |
| 1000002 | 1000002@qq.com | 1000002  |
| 1000003 | 1000003@qq.com | 1000003  |
| 1000004 | 1000004@qq.com | 1000004  |
+---------+----------------+----------+
5 rows in set (0.00 sec)