十万级数据量的CI分页类优化,比原生分页类快一百倍!!

avinmo

首先，得说明一下，在以下两种情况下差距不大
第一情况：当数据量不大的时候，例如几千条。
第二种情况：当你从第一页点到第二页，或者只在当前页的附近页跳转的时候。

在当数据量达到十万左右，或者七八万条的时候，效率对比就非常明显了，特别是当你从第一页直接跳转到最后一页时。

下面给出个对比测试的数据吧。
数据库表的记录条数九万多(接近十万条)
用ci原生的分页类，查询当由第一页，直接点击跳转到最后一页的时候。测试得出的运行时间：15.7396 S
用改进后的分页类，测试结果：0.0466 S

在这种条件下，对比是不是非常明显? 效率甚至超出一百倍。

当然，我可以自己写一个分页类，能达到此效率。之所以使用继承CI分页，在CI分页的基础上改进。是因为习惯了使用CI的分页类。在这里。除了分页类本身经过继承外，还有就是使用方法上有细微区别。

好吧，现在就开始吧。
通常我们查询列表，在没有特殊条件下查询，一般会使用order_by('id','desc');

以下贴出使用代码。
注意，此代码仅在 $config['use_page_numbers']=FALSE时可用。CI里默认是FALSE，所以只要你不设置为TRUE就好。

现在就按ID的顺序来查询某表的所有记录，并分页。

代码如下 ：

$this->load->library('pagination');
$config['base_url']=site_url('manage/member/showlist');

//记录总行数

//每页显示的记录数
$config['per_page'] = '12';
$config['first_link'] = '第一页';
$config['last_link'] = '最后一页';
$config['prev_link'] = '上一页';
$config['next_link'] = '下一页';
// 表示第 4段 URL 为当前页数，如 index.php/控制器/方法/页数，如果表示当前页的 URL 段不是第 4 段，请修改成需要的数值。
$config['uri_segment'] = 4;
$config['num_links'] = 8;
$config['order_by'] = 'asc';//这个是新加上去的参数，非CI原生参数(按顺序查询)


//查询条件开始
$bcs_db->start_cache();
$bcs_db->select('id,forbbs_username,forbbs_email,reg_datetime,forbbs_userclass,email_activation_date,vemail');
$bcs_db->from('bcs_user');
$bcs_db->order_by('id','asc');

$bcs_db->stop_cache();
//查询条件结束

$config['total_rows']=$bcs_db->count_all_results();

if(empty($segment4))//当第一页时，因为是顺序查询，所以$segment设置为零
{
$segment4 = 0;
}

$bcs_db->where('bcs_user.id >',$segment4); //关键是这里，mysql里采用select * from table where id>10000 limit N的写法会比直接写limit(n,m)的写法效率高。
$bcs_db->limit($config['per_page'] );
$this->pagination->initialize($config); //初始化分页类

$query = $bcs_db->get('')->result();




//再给出倒序排列的写法。如下：

$this->load->library('pagination');
$config['base_url']=site_url('manage/member/showlist');

//每页显示的记录数
$config['per_page'] = '12';
$config['first_link'] = '第一页';
$config['last_link'] = '最后一页';
$config['prev_link'] = '上一页';
$config['next_link'] = '下一页';
// 表示第 4段 URL 为当前页数，如 index.php/控制器/方法/页数，如果表示当前页的 URL 段不是第 4 段，请修改成需要的数值。
$config['uri_segment'] = 4;
$config['num_links'] = 8;
$config['order_by'] = 'desc';


//查询条件开始
$bcs_db->start_cache();
$bcs_db->select('id,forbbs_username,forbbs_email,reg_datetime,forbbs_userclass,email_activation_date,vemail');
$bcs_db->from('bcs_user');
$bcs_db->order_by('id','desc');


$bcs_db->stop_cache();
//查询条件结束

$config['total_rows']=$bcs_db->count_all_results();

if(empty($segment4))
{
  $segment4 = $config['total_rows'];//由于是按倒序排列，所以第一页，最后一条记录数。就是总记录数。
}

$bcs_db->where('bcs_user.id <',$segment4); //倒序和顺序。 在查询条件上有所区别，这里需要注意!!!
$bcs_db->limit($config['per_page'] );
$this->pagination->initialize($config); //初始化分页类

$query = $bcs_db->get('')->result();

最后给出经修改后的分页类文件下载，下载后请放到application/library下使用。文件名和类名请勿修改。
原文地址：http://www.dgpower.net/news/shownews/392.html

spt119

分页的情况，个人认为需要具体情况具体处理。
如果是单表查询，楼主的方式是可行的。如果是多表或三表级联查询，楼主的方式，就有待商榷了，而在实际项目中，多表级联查询，要比单表，多很多（至少我接手的项目中是这样）。
所谓的效率，来自两个方面，一个是SQL的写法层面优化，另一方面，就是数据库本身。有很多的开发者，对索引并不关注，对如何正确的设置索引也不很关心。
这样的习惯，在数据量不大时，问题不突出，数据量稍微一大，就很明显。
索引！是所有考虑分页效率时，应该第一个考虑到的，而不是直接去操作SQL语句。

aqxinzhuan

先收藏一下，以后再回头来看

秋天的童话

mark一下，以后再看

avinmo

感谢 CI4群里 小维 提出的BUG。 当数据ID不为连续数的时候(例如个别记录被删除掉)。 这个方法会存在一个问题。
大家给点思路。有没有更好的解决方案?

指尖的殤魂

ID>$ID     或     ID<$ID

像这个

avinmo

楼上的什么意思? 我不太明白。

gauspican

直接用$this->db->query() 的飘过：）

我是我

SQL复制代码

      SELECT id,... FROM TABLE WHERE id >(SELECT id FROM TABLE WHERE ... LIMIT 10000,1)  LIMIT per_page

复制代码

据说这样写效率搞，大家验证。。。。

avinmo

谢谢楼上的。的确解决了我所遇到的问题。 使用你所说的方法。。 不需要修改CI的pagination类的情况下，能有效提高上百倍的速度。

avinmo

下面贴上部分代码供大家参考。

                $segment4 = (int)$this->uri->segment(4);
                if(empty($segment4))
                {
                        $segment4 =1;
                }

                $this->load->library('pagination');
                $config['base_url']=site_url('manage/member/showlist');

                //每页显示的记录数
                $config['per_page'] = '12';
                $config['first_link'] = '第一页';
                $config['last_link'] = '最后一页';
                $config['prev_link'] = '上一页';
                $config['next_link'] = '下一页';
                // 表示第 4段 URL 为当前页数，如 index.php/控制器/方法/页数，如果表示当前页的 URL 段不是第 4 段，请修改成需要的数值。
                $config['uri_segment'] = 4;
                $config['num_links'] = 8;

                $config['use_page_numbers'] = TRUE;//注意此处。
               

                $count_sql =  "select count(*) as record_count from bcs_user";//查出总记录数
                $sqlstr = "select id,forbbs_username,forbbs_email,reg_datetime,forbbs_userclass,email_activation_date,vemail from bcs_user where id<=(select id from bcs_user order by id desc limit ".ceil(($segment4-1) * $config['per_page']).",1) order by id desc limit ".$config['per_page'];//关键：按照六楼"我是我"所说原理写的查询语句。(效率提高上百倍)
               
                $config['total_rows']=$bcs_db->query($count_sql)->row()->record_count;
                $this->pagination->initialize($config); //初始化分页类
                $query = $bcs_db->query($sqlstr)->result();

原文链接：http://www.dgpower.net/news/shownews/394.html

avinmo

貌似好冷清。。呵呵。

tanqimin

不同数据库有不同的分页优化方式。