RDS性能优化之分页优化

今天来聊一下RDS性能优化之分页优化

1、为什么要对查询结果进行分页

当需要从数据库查询的表超过上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。

2、分页查询使用场景

当要显示的数据，当一页显示不全，有很多的数据时，就需要分页提交sql请求

3、分页查询的分类

分页有逻辑分页和物理分页，但数据量较大时，一般使用物理分页。

逻辑分页和物理分页的区别是：

 

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

 

 

为什么逻辑分页占用较大的内存空间，比如我有一张表，向该表中插入300万条数据后，再转储到桌面，这是多么庞大的数据，占用的内存多么可怕，为什么我们再选用数据库。这也是我们使用云服务器时，设定mysql的存储空间的大小。
我们一般不推荐使用逻辑分页，而使用物理分页。在使用物理分页的时候，就要考虑到limit的用法。

4、limit的用法

 

解释limit

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

X表示第一个返回记录行的偏移量，Y表示返回记录行的最大数目

如果X为0的话，即 limit 0, Y，相当于limit Y、

 

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 ；

你会发现，方法2的执行效率远比方法1的执行效率高，几乎是方法1的九分之一的时间。
为什么方法1的效率低，而方法二的效率高呢？

分析一、
因为在方法1中，我们使用的单纯的limit。原因mysql会读取表中的前

X+Y条数据，X越大，性能就越差。而方法2用上索引加where和limit，优化后的翻页写法，先查询翻页中需要的X条数据的主键id，在根据主

键id 回表查询所需要的Y条数据，

分析二、
用explain来分析
  limit语句的执行效率未必很高，因为会进行全表扫描，这就是为什么方法1扫描的的行数是400万行的原因。方法2的扫描行数是47945行，这也是为什么方法2执行效率高的原因。我们尽量避免全表扫描查询，尤其是数据非常庞大，这张表仅有400万条数据，方法1和方法就有这么大差距，可想而知上千万条的数据呢。
能用索引的尽量使用索引，type至少达到range级别，这不是我说的，这是阿里巴巴开发手册的5.2.8中要求的

我不用索引查询到的结果和返回的时间和方法1的时间差不多：

SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT  20000

受影响的行: 0 时间: 0.196s

这也就是我们为什么尽量使用索引的原因。mysql索引方法一般有BTREE索引和HASH索引，hash索引的效率比BTREE索引的效率高，但我们经常使用BTREE索引，而不是hash索引。因为最重要的一点就是：Hash索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
如果是范围查询，我们为什么用BTREE索引的原因。BTREE索引就是二叉树索引，学过数据结构的应该都清楚，这里就不赘述了。

limit物理分页

我们都知道limit一般有两个参数，X和Y，X表示跳过X个数据，读取Y个数据，我们就此来查询数据
如果是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;

如果是用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;

    }