当你和别人都能实现一个某个功能，这时候区分你们能力的不是谁干活多少，而是谁能写出效率更高的代码。比如显示一个订单列表它不仅仅是写一条SELECT SQL那么简单，我们还需要很清楚的知道这条SQL他大概扫描了多少行数据，返回了多少行数据，是否需要创建索引，创建什么样的索引，索引是否生效，等等。

这里以订单列表显示和订单导出为例来谈谈Mysql分页优化。

CREATE TABLE IF NOT EXISTS `order_info` (

  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',

  `order_sn` varchar(60) NOT NULL COMMENT '订单号',

  `user_id` int(11) NOT NULL COMMENT '用户ID',

  `channels_id` int(11) NOT NULL COMMENT '渠道ID',

  ……一些其他字段

  `order_time` datetime NOT NULL COMMENT '下单时间',

  PRIMARY KEY (`order_id`),

  KEY `channels_id` (`channels_id`),

  KEY `order_sn` (`order_sn`),

  KEY `user_id` (`user_id`),

  KEY `order_time` (`order_time`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

select count(1) as num from order_info where channels_id=35;    0.24 sec

select * from order_info where channels_id=35 order by order_id desc limit 0,20;    0.01 sec

select * from order_info where channels_id=35 order by order_id desc limit 1320000,20;    12.55 sec  即便是第二次查询也用了4.27 sec（mysql自身也会有查询缓存机制）

select order_id from order_info where channels_id=35 order by order_id desc limit 1320000,20;    0.40 sec

select * from order_info,(select order_id from order_info where channels_id=35 order by order_id desc limit 1320000,20) order_info_tmp where order_info.order_id = order_info_tmp.order_id; 0.47 sec

select * from order_info,(select order_id from order_info where channels_id=35 order by order_id desc limit 0,20) order_info_tmp where order_info.order_id = order_info_tmp.order_id; 0.00 sec

select * from order_info where channels_id=35 AND order_id <=54388 order by order_id desc limit 20;    0.00 sec

//auth by duxiaokong 2016-08-23

$fp = fopen('php://output', 'a');

$num_limit = 1000;

$order_id = 0;

$order_list = [];

while (true) {

    //执行sql  select * from order_info where $where AND order_id > $order_id order by order_id ASC limit $num_limit; 得到$order_list订单列表

    //这里一定要注意 order_id > $order_id 和 order_id ASC的排序

    if (empty($order_list)) {

        break;

    }

    $line = 0;

    $row_str = '';

    foreach ($order_list as $key => $val) {

        $order_id = $val['order_id']; //这行代码一定要记得赋值不然会造成死循环

        $line++;

        // 获取导出数据

        $row = [

            $val['order_sn'],

            $val['order_time'],

            $val['user_name']

            // ……

        ];

        //$row 过滤 $row中的非法字符

        $row_str .= mb_convert_encoding(implode(',', $row), 'gbk', 'utf-8') . PHP_EOL;

        //每获取20次记录写入一次数据库，减少IO

        if ($line >= 20) {

            fwrite($fp, $row_str);

            $line = 0;

            $row_str = '';

        }

    }

    if (!empty($row_str)) {

        fwrite($fp, $row_str);

        $line = 0;

        $row_str = '';

    }

}

fclose($fp);