分页性能探索-mysql

* FROM LIST_TABLE WHERE id > offset_id LIMIT n; 电梯方式

另外一种数据获取方式在产品上体现成精确的翻页方式，如1,2,3……n，同时在导航上也可以由用户输入直达n页。国内大部分场景采用电梯方式，但电梯方式在技术实现上相对成本较高。

在MySQL中，通常提到的b-tree，在存储引擎实现上，通常都是b+tree。

使用电梯方式时候，当用户指定翻到第n页时候，并没有直接方法寻址到该位置，而是需要从第一楼逐个count，scan到count*page时候，获取数据才真正开始，所以导致效率不高。

传统分页技术(电梯方式)

首先前端需要传给你的分页实体，以及查询条件

  //分页实体 
 struct  FinanceDcPage{
     1 : i32 pageSize,                                     //页容量 
     2 : i32 pageIndex,                                    //当前页索引 
} 

然后你需要返回查询总条数给前端；

   SELECT   COUNT (*)  FROM  my_table  WHERE  x = y  ORDER   BY  id;  

然后再返回指定页面条数给前端：

   SELECT  *  FROM  my_table  WHERE  x = y  ORDER   BY  date_col LIMIT (pageIndex -  1 ) * pageSize, pageSize;  

由上面两条sql语句查询出来的结果需要返回给前端的分页实体，以及单页结果集

  //分页实体 
 struct  FinanceDcPage{
     1 : i32 pageSize,                                     //页容量 
     2 : i32 pageIndex,                                    //当前页索引 
     3 : i32 pageTotal,                                    //总页数 
     4 : i32 totalRecod,                                   //总条数 
} 

传统查询方法，每次请求变化的只有pageIndex值，也就是limit offset,num 的offset

如limit 0,10; limit 10,10; …. limit 10000,10;

上面的变化会导致每次查询所执行的时间会有偏差，offset值越大需要的时间越长，如limit 10000,10 需要读取10010个数据才能得到想要的10条数据。

优化方法

传统方法中我们了解到，影响效率的关键是程序遍历了许多不需要的数据，找到了关键点那么就从这里着手。

如果没有必须使用电梯方式的时候，我们可以使用扶梯的方式，来提高性能。

但是大多数情况，电梯形式更能满足用户的需求，所以我们就需要另找方法来优化电梯形式。

这里有篇文章介绍了对电梯方式的优化，由于我做的项目还没有上升到要对其做这种优化的地步，所以就直接上他的方式吧。

为什么超长列表数据的翻页技术实现复杂 为什么超长列表数据的翻页技术实现复杂（二）

基于传统方式的优化

上面提到的优化方式，要么难以满足用户的需求，要么实现起来过于复杂，所以如果数据量不是特别大的时候，像百来万条数据，其实根本没有必要使用上面的优化方法。

传统方法已经足够用了，只不过传统方法也可能需要优化的地方。例如：

order by优化

   SELECT  *  FROM  pa_dc_flow  ORDER   BY  subject_code  DESC  LIMIT  100000 , 5   

这条语句中使用了ORDER BY关键字，那么对什么进行排序又非常重要了，如果你是对自增id进行排序的话，那么这条语句就不需要优化了，如果是索引甚至非索引的话，那就需要优化了。

首先你要保证它是索引，不然真的会很慢。然后如果他是索引，但是本身不像自增id那样有序的话，那么就要改写成下面的语句。

   SELECT  *  FROM  pa_dc_flow  INNER   JOIN  ( SELECT  id  FROM  pa_dc_flow  ORDER   BY  subject_code  DESC  LIMIT  100000 , 5 )  AS  pa_dc_flow_id  USING (id);  

下面是对两条sql的 EXPLAIN

由图中我们可以看出，第二个sql可以少扫面很多页面。

其实这涉及到order by的优化问题，第一条sql中并没有利用到subject_code索引。如果你改为 select subject_code …则用到了索引。下面是对order by的优化。

order by 后的字段，如果要走索引，须与where 条件里的某字段建立复合索引！！或者说orcer by后的字段如果要走索引排序，它要么与where 条件里的字段建立复合索引【这里建立复合索引的时候，需要注意复合索引的列顺序为（where字段，order by 字段），这样才能满足最左列原则，原因可能是order by字段并能算在where 查询条件中！】，要么它自身要在where 条件里被引用到！

表a subject_code为普通字段,上面建有索引,id是自增主键

   select  *  from  a  order   by  subject_code   //用不上索引
 select  id  from  a  order   by  subject_code  //能用上索引
 select  subject_code  from  a  order   by  subject_code    //能用上索引
 select  *  from  a  where  subject_code = XX  order   by  subject_code   //能用上索引  

意思是说order by 要避免使用文件系统排序，要么把order by的字段出现在select 后，要么使用order by字段出现在where 条件里，要么把order by字段与where 条件字段建立复合索引！

详见order by关键字优化

第二条sql就是巧妙的利用第二种方式利用上了索引。 select id from a order by subject_code，这种方式

count优化

当数据量非常大时，其实可以输出总数的大概数据，利用explain语句，他并没有真正去执行sql，而是进行的估算

2015-5-12 19:27:34

Brave,Happy,Thanksgiving !

$(function () { $(‘pre.prettyprint code‘).each(function () { var lines = $(this).text().split(‘\n‘).length; var $numbering = $(‘ ‘).addClass(‘pre-numbering‘).hide(); $(this).addClass(‘has-numbering‘).parent().append($numbering); for (i = 1; i ‘).text(i)); }; $numbering.fadeIn(1700); }); });

分页性能探索-mysql

标签：分页   mysql   性能优化   

查看更多关于分页性能探索-mysql的详细内容...