mysql的limit用法、逻辑分页和物理分页

蔚落 2022-05-15 00:23 325阅读 0赞

***参考[https://www.cnblogs.com/tonghun/p/7122801.html][https_www.cnblogs.com_tonghun_p_7122801.html]***

--------------------

## 物理分页为什么用limit ##

在讲解limit之间，我们先说说分页的事情。  
分页有逻辑分页和物理分页，就像删除有逻辑删除和物理删除。逻辑删除就是改变数据库的状态，物理删除就是直接删除数据库的记录，而逻辑删除只是改变该数据库的状态。例如  
![这里写图片描述][70]  
同理，逻辑分页和物理分页是有区别的

<table> 
 <thead> 
  <tr> 
   <th>物理分页</th> 
   <th align="center">逻辑分页</th> 
   <th align="right">Cool</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>物理分页依赖的是某一物理实体，这个物理实体就是数据库，比如MySQL数据库提供了limit关键字，程序员只需要编写带有limit关键字的SQL语句，数据库返回的就是分页结果。</td> 
   <td align="center">逻辑分页依赖的是程序员编写的代码。数据库返回的不是分页结果，而是全部数据，然后再由程序员通过代码获取分页数据，常用的操作是一次性从数据库中查询出全部数据并存储到List集合中，因为List集合有序，再根据索引获取指定范围的数据。</td> 
   <td align="right">概念</td> 
  </tr> 
  <tr> 
   <td>每次都要访问数据库，对数据库造成的负担大</td> 
   <td align="center">只需要访问一次数据库</td> 
   <td align="right">数据库负担</td> 
  </tr> 
  <tr> 
   <td>每次只读取一部分数据，占用的内存空间较小</td> 
   <td align="center">一次性将数据读取到内存，占用较大的内存空间。如果使用java开发，Java本身引用的框架就占用了很多内存，这无疑加重了服务器的负担。</td> 
   <td align="right">服务器负担</td> 
  </tr> 
  <tr> 
   <td>每次需要数据时都访问数据库，能够获取数据库的最新状态，实时性强</td> 
   <td align="center">因为一次性读入到内存，数据发生了改变，数据库逇最新状态无法实时反映到操作中</td> 
   <td align="right">实时性</td> 
  </tr> 
  <tr> 
   <td>数据库量大、更新频繁的场合</td> 
   <td align="center">数据量较小、数据稳定的场合</td> 
   <td align="right">服务器负担</td> 
  </tr> 
 </tbody> 
</table>

为什么逻辑分页占用较大的内存空间，比如我有一张表，表的信息是：

-- ----------------------------
    -- Table structure for vote_record_memory
    -- ----------------------------
    DROP TABLE IF EXISTS `vote_record_memory`;
    CREATE TABLE `vote_record_memory` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` varchar(20) NOT NULL, `vote_id` int(11) NOT NULL, `group_id` int(11) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `index_id` (`user_id`) USING HASH ) ENGINE=MEMORY AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8;

向该表中插入300万条数据后，再转储到桌面，查看转储后的SQL文件的属性：  
![这里写图片描述][70 1]  
这是多么庞大的数据，占用的内存多么可怕，为什么我们再选用数据库。这也是我们使用云服务器时，设定mysql的存储空间的大小。  
我们一般不推荐使用逻辑分页，而使用物理分页。在使用物理分页的时候，就要考虑到limit的用法。

--------------------

## 解释limit ##

limit X，Y ，跳过前X条数据，读取Y条数据

*  X表示第一个返回记录行的偏移量，Y表示返回记录行的最大数目
 *  如果X为0的话，即 limit 0, Y，相当于limit Y、

--------------------

通过业务分析limit

*  我有一张工资表，只显示最新的***前两条记录***，同时进行员工姓名和工资提成备注查询

SELECT cue.real_name empName, zs.push_money AS pushMoney, zs.push_money_note AS pushMoneyNote, zs.create_datetime AS createTime FROM zq_salary zs //主表 LEFT JOIN core_user_ext cue ON cue.id = zs.user_id //从表 on之后是从表的条件 WHERE zs.is_deleted = 0 AND ( cue.real_name LIKE '%李%' OR zs.push_money_note LIKE '%测%' ) ORDER BY zs.create_datetime DESC LIMIT 2;
    
    就相当于
    ORDER BY
        zs.create_datetime DESC
    LIMIT 0,2;

![这里写图片描述][70 2]

## limit的效率问题 ##

*  我有一个需求，就是从vote\_record\_memory表中查出3600000到3800000的数据，此时在id上加个索引，索引的类型是Normal，索引的方法是BTREE，分别用两种方法查询

-- 方法1
    SELECT * FROM vote_record_memory vrm LIMIT 3600000,20000 ;
    
    -- 方法2
    SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT 20000

![这里写图片描述][70 3]  
你会发现，方法2的执行效率远比方法1的执行效率高，几乎是方法1的九分之一的时间。  
为什么方法1的效率低，而方法二的效率高呢？

*  分析一、  
    因为在方法1中，我们使用的单纯的limit。limit随着行偏移量的增大，当大到一定程度后，会出现效率下降。而方法2用上索引加where和limit，性能基本稳定，受偏移量和行数的影响不大。
 *  分析二、  
    我们用explain来分析  
    ![这里写图片描述][70 4]  
    ![这里写图片描述][70 5]  
    可见，limit语句的执行效率未必很高，因为会进行全表扫描，这就是为什么方法1扫描的的行数是400万行的原因。方法2的扫描行数是47945行，这也是为什么方法2执行效率高的原因。我们尽量避免全表扫描查询，尤其是数据非常庞大，这张表仅有400万条数据，方法1和方法就有这么大差距，可想而知上千万条的数据呢。  
    能用索引的尽量使用索引，type至少达到range级别***，这不是我说的，这是阿里巴巴开发手册的5.2.8中要求的***  
    ![这里写图片描述][70 6]  
    我不用索引查询到的结果和返回的时间和方法1的时间差不多：

> SELECT \* FROM vote\_record\_memory vrm WHERE vrm.id >= 3600000 LIMIT  
> 20000 受影响的行: 0 时间: 0.196s  
> ![这里写图片描述][70 7]  
> 这也就是我们为什么尽量使用索引的原因。mysql索引方法一般有BTREE索引和HASH索引，hash索引的效率比BTREE索引的效率高，但我们经常使用BTREE索引，而不是hash索引。因为最重要的一点就是：Hash索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。  
> ![这里写图片描述][70 8]  
> 如果是范围查询，我们为什么用BTREE索引的原因。BTREE索引就是二叉树索引，学过数据结构的应该都清楚，这里就不赘述了。

## limit物理分页 ##

我们都知道limit一般有两个参数，X和Y，X表示跳过X个数据，读取Y个数据，我们就此来查询数据  
![这里写图片描述][70 9]

<table> 
 <thead> 
  <tr> 
   <th>页数</th> 
   <th align="center">每页显示的行数</th> 
   <th align="right">limit语句</th> 
   <th align="right">计算方式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>第一页</td> 
   <td align="center">20</td> 
   <td align="right">limit 0,20</td> 
   <td align="right">limit 0*20,20</td> 
  </tr> 
  <tr> 
   <td>第二页</td> 
   <td align="center">20</td> 
   <td align="right">limit 20,20</td> 
   <td align="right">limit 1*20,20</td> 
  </tr> 
  <tr> 
   <td>第三页</td> 
   <td align="center">20</td> 
   <td align="right">limit 40,20</td> 
   <td align="right">limit 2*20,20</td> 
  </tr> 
  <tr> 
   <td>第四页</td> 
   <td align="center">20</td> 
   <td align="right">limit 60,20</td> 
   <td align="right">limit 3*20,20</td> 
  </tr> 
 </tbody> 
</table>

如果是SQL语句来进行分页的话，我们可以看到的是：

-- 首页
    SELECT * from vote_record_memory LIMIT 0,20;
    
    -- 第二页
    SELECT * from vote_record_memory LIMIT 20,20;
    
    -- 第三页
    SELECT * from vote_record_memory LIMIT 40,20;
    
    -- 第四页
    SELECT * from vote_record_memory LIMIT 60,20;
    
    -- n页
    SELECT * from vote_record_memory LIMIT (n-1)*20,20;

![这里写图片描述][70 10]  
因而，如果是用java的话，我们就可以写一个方法，有两个参数，一个是页数，一个每页显示的行数

/** * @description 简单的模拟分页雏形 * @author zby * @param currentPage 当前页 * @param lines 每页显示的多少条 * @return 数据的集合 */
        public List<Object> listObjects(int currentPage, int lines) {
            String sql = "SELECT * from vote_record_memory LIMIT " + (currentPage - 1) * lines + "," + lines;
            return null;
        }

[https_www.cnblogs.com_tonghun_p_7122801.html]: https://www.cnblogs.com/tonghun/p/7122801.html
[70]: /images/20220515/2cac32ac14db418f8e268cf640813207.png
[70 1]: /images/20220515/7b346d5e1e8b4954b4be9472294fe864.png
[70 2]: /images/20220515/94b4dfbde23e49adb0d66a5c833ad8a3.png
[70 3]: /images/20220515/36452c090e9042b3af7573f22bb1e49f.png
[70 4]: /images/20220515/e5f9d42600a5493fbf0b1bc512c18cd8.png
[70 5]: /images/20220515/5420244d3f524a3eb2399a90f1601aae.png
[70 6]: /images/20220515/08fd2be59b2446a0ad8514f7216ceaa1.png
[70 7]: /images/20220515/e99043adf70c493f93612398f1ccaa8f.png
[70 8]: /images/20220515/cf582ccaf33c4e029ec8c1b3a3d50bb4.png
[70 9]: /images/20220515/4cb6ab3440424f3b8facf338fc3228ef.png
[70 10]: /images/20220515/dd61086757a0422890690ebf84d8b5fb.png