440-MySQL(limit分页)

- 日理万妓 2022-09-05 15:44 9阅读 0赞

## 分页查询limit语法 ##

1、因为商品非常多，总是会分页显示，这样显示的话方便大家查看。  
2、我在翻页的时候，查询的性能是均衡的。

**limit语法**

select id,nickname,name,age,sex from user limit 10; 
    select id,nickname,name,age,sex from user limit 2000,10;

**对整张表的数据的查看，然后显示前3个。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**对整张表的数据的查看，然后偏移1个，然后显示前3个。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][f073afba84bd408baf5c615f29da4914.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
**都是先执行select，然后再根据limit的要求去做(显示前几行，或者先偏移一下，再显示前几行。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]

## limit对于SQL的查询，仅仅只是限制了查询的数据量的显示条数吗？ ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
**我们看到，id是主键，我们还给name给了唯一键，主键会自动创建主键索引，unique会自动创建唯一键索引。age和sex没有创建索引。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**当我在这张表去找name=zhangsan的时候，MySQL是把这张表全扫了一遍，还是说只扫了一行就找到了？**  
**关系型数据库是二维表，就是看扫的数据量来看效率。**

**我们加入explain关键字（查看SQL语句的执行计划，罗列出执行SQL的过程和信息，但是不是非常准确，MySQL的优化不会统计，性能指标只是大致）**  
![在这里插入图片描述][9bad321e12c849bfa7b8ce1113646a77.png]  
**我们看到，rows是1，扫了1行，我试过了，扫哪条信息都是扫1行！！！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]

**因为name字段加了索引，所以不管记录在哪一行，都是扫1行。花费的时间，效率是一样的。**

**我们知道，age和sex没有索引，所以我们来查看看age和sex**  
因为age没有索引，计算机不知道后面会不会出现要显示的age值，**所以肯定要全部扫1遍。** **数据量越大，效率越低**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]  
**如果不使用索引的话，还有什么办法可以提高查询的效率？**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
**如果加了limit，MySQL会有优化，它知道我们只需要1行，它从上往下扫表，扫到这个age记录，而且知道我们只需要1行，它就停止了，直接返回了，这就效率高了。**  
**但是explain却显示5行，因为explain看不到MySQL对limit的优化。**  
![在这里插入图片描述][3256d771ee964b8b8e45fac4a1634477.png]

## 我们加入大量数据，测试一下limit优化 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]  
**用下面的代码，可以迅速给表添加数据**

delimiter $//分割符，以$结束，代表1个SQL
    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,'@linzeyu.com'),i+1);
    //0@linzeyu.com , 1@linzeyu.com ,2@linzeyu.com
    //password就是 i+1
    SET i=i+1;//每循环1次，i=i+1
    END WHILE;//while的结束
    END$//存储的过程的结构 BEGIN到END
    delimiter ;//还原;为SQL的结束
    call add_t_user(2000000);//增加数据量

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]  
![在这里插入图片描述][266dd71541024f5ab963c0d1aec6d976.png]  
![在这里插入图片描述][b68369662c28414d9bc7aafef7e44544.png]  
![在这里插入图片描述][746ac407b05d4de49ff627ac7e7c3fde.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]  
**现在数量就很多了，我们来看看效率**

**email没有索引，MySQL是把整张表全扫了1遍。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]  
![在这里插入图片描述][9262bf33c7a246e7bca09c11606654be.png]  
**我们加上limit看看**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]  
**看耗时。0.44秒。0秒。效率提升了！**  
![在这里插入图片描述][ead3796a31134cde8bc4f4bfdce03b21.png]  
![在这里插入图片描述][1a96727568b24867a5c5412c12359fab.png]  
**加上limit是0.20秒，不加是0.44秒**  
![在这里插入图片描述][4297890a554540b4b9fe2aac1b237d07.png]  
**刚才的limit是0秒，是扫描到第一行就结束了。这个是扫描1000001行。**

**如果是这个email有索引，那么效率就已经优化了，效率都是一样的。**  
如果是这个password有重复的，除非加索引，否则只能整张表扫。

## limit分页的正确应用 ##

**现在有很多页**  
![在这里插入图片描述][6c422a2e3e554012bf84998165177590.png]  
**假如1页只显示20行，当我们用户在选择相应页码，我们怎么去select出来相应的数据呢？**  
**点击第几页。偏移掉前面的页，然后显示选择的页的前20行**  
![在这里插入图片描述][4ae02d40219b44999e1de900f1eedc3f.png]  
![在这里插入图片描述][1446f50dccd24c40af445ab362c283b8.png]  
**但是，我们在这里看到有效率的问题，总不能我们选前面几页就快，选后面的页就慢。**  
**前面的页要一个个过一遍，这样就慢了！**  
**效率低在M的偏移**

**我们要把M的偏移花费的性能过滤掉！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]  
**我们这样做：**  
**利用主键id自动创建索引的原理**  
**我们通过索引把M过滤掉！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 19]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 20]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 21]  
**优化成功！！！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 22]  
**我们真真正正这么去写：**  
![在这里插入图片描述][3921ff2c28e94e61ac140e89b83957c6.png]  
**索引是常量时间，过滤前面的数据。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/e9062894bdf8409783f237f8006dd205.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/2e70b04adce24097a8e56eb9f3d4f752.png
[f073afba84bd408baf5c615f29da4914.png]: /images/20220829/c8c5848c7a3144dd8bd5fe2f0684b677.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/4035c17c21cc4f95b081deb49b3665b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/bdc5b0514223401bac284c711a52a224.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/6c5fa16f4d7744638f07ebe6d18d1b38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/893c6add5b0842f7bc78ecf97fbaf6c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/8a6b43d9b5b7418895298d6e9a97e92d.png
[9bad321e12c849bfa7b8ce1113646a77.png]: /images/20220829/9544e2271cb1478ea24da4a4505f3d88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/1d1ef221fbca48c1a305ae3a19d12503.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/8ceb8793107f4cc1afa12e91f4bcb2b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/5371f4c282a148b8b1df038cf6a8dcdd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/5a1fc156016b479caaaba25866151ba8.png
[3256d771ee964b8b8e45fac4a1634477.png]: /images/20220829/e9903f406c114010858b5e6406748eb4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/ad203f9c2657490d95165b12403b2eb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/e41851aecb0b46acbec733b245122576.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]: /images/20220829/23b61b84d986471481b1e2dd3af2fbd8.png
[266dd71541024f5ab963c0d1aec6d976.png]: /images/20220829/1099eb86a4d84335b687b4d0bb3fe8ef.png
[b68369662c28414d9bc7aafef7e44544.png]: /images/20220829/96285af754c4437ba4c9c35c17185a27.png
[746ac407b05d4de49ff627ac7e7c3fde.png]: /images/20220829/5896eebe5e6f4e288aa15babf622882a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]: /images/20220829/82c12f1eb31a48099aeb954dc666c472.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]: /images/20220829/4654885834d640fca909b9e7f6647085.png
[9262bf33c7a246e7bca09c11606654be.png]: /images/20220829/e01de69b96ce4f55b168115d91d78892.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]: /images/20220829/5c9d014f42a54a83bd2e0158ee1b771a.png
[ead3796a31134cde8bc4f4bfdce03b21.png]: /images/20220829/bd172f6f9a0f4b16bf559c46e0dd1607.png
[1a96727568b24867a5c5412c12359fab.png]: /images/20220829/eb17e8f3439f4b0f9fa834734644aa5f.png
[4297890a554540b4b9fe2aac1b237d07.png]: /images/20220829/677a5d5881c342bd95a3391a4e604256.png
[6c422a2e3e554012bf84998165177590.png]: /images/20220829/1b61ca8d0ba64506a4d4b21a5feb0ec7.png
[4ae02d40219b44999e1de900f1eedc3f.png]: /images/20220829/23b449f0b1c946e192b6f9d71b92515d.png
[1446f50dccd24c40af445ab362c283b8.png]: /images/20220829/5fc6870f32da43669036a62f107c9aad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]: /images/20220829/7d442e32c7724908b86ceeb6d3c00b3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]: /images/20220829/d6d6885875fc4931bee6675ae092edd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 19]: /images/20220829/174ebf0b7fc94a2182660bf8662ffeda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 20]: /images/20220829/736bb1a65c7240feb88fd4811a2d6511.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 21]: /images/20220829/9843a07434a04052816c7d6b93f2afeb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 22]: /images/20220829/f62c0d9a170a4ae8871355d06f282010.png
[3921ff2c28e94e61ac140e89b83957c6.png]: /images/20220829/df59b20f207e4aa98f3b8d4d0377d17e.png