优化分页查询

我们一般使用分页都是使用limit来完成的，如果数据量小的话还可以，但是当数据量非常大的时候，不建立索引，通过全表查询，将会非常耗时，性能将受到很大的影响。

第一种优化方式
在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容

例：我想对我之前的分页进行优化，没有优化前的sql语句

<select id="queryNewsByPage" resultType="news">        SELECT news_id, news_title, news_content        FROM news        <if test="start != null and size != null">            limit #{start}, #{size}        </if>    </select>

对其进行优化：
1）我首先在news_title上建立了一个索引
2）修改sql语句
修改以后：

<select id="queryNewsByPage" resultType="news">        SELECT a.news_id, a.news_title, a.news_content        FROM news a INNER JOIN        (SELECT news_id FROM news ORDER BY news_title        <if test="start != null and size != null">            limit #{start}, #{size}        </if>        ) b ON a.news_id = b.news_id </select>

感觉自己是为了强行使用索引优化而改的，因为做了测试性能并没有提升。也有可能是自己数据库的数据量太少了，只有100行
第一种索引优化是利用了二级索引的特点，二级索引的叶子结点存放的是自定义索引+主键（这里为news_title+news_id）先通过索引排序分页，在索引上进行排序是很快的，其实根本就不用排了，索引是顺序存储的，然后再利用主键进行表关联
通过explain查看执行情况

优化前：select * from news order by news_title limit 10,5

mysql> explain select * from news order by news_title limit 10,5 \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: news   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 93     filtered: 100.00        Extra: Using filesort1 row in set, 1 warning (0.00 sec)

优化后：select a.* from news a inner join (select news_id from news order by news_title limit 20,5) b using(news_id)、

mysql> explain select a.* from news a inner join (select news_id from news order by news_title limit 10,5) b using(news_id)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 15     filtered: 100.00        Extra: NULL*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: a   partitions: NULL         type: eq_refpossible_keys: PRIMARY          key: PRIMARY      key_len: 4          ref: b.news_id         rows: 1     filtered: 100.00        Extra: NULL*************************** 3. row ***************************           id: 2  select_type: DERIVED        table: news   partitions: NULL         type: indexpossible_keys: NULL          key: news_title      key_len: 767          ref: NULL         rows: 15     filtered: 100.00        Extra: Using index3 rows in set, 1 warning (0.00 sec)mysql> explain select * from news limit 10,5\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: news   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 93     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.00 sec)

优化前通过文件排序，文件排序是非常浪费时间和空间的，并且其是通过全表进行排序的，扫描的数据量非常多
优化后，使用索引排序，并且可以看到虽然子查询表中是全表扫描，但是也做到了扫描尽可能的行

第二种优化方式
把limit查询转化成某个位置的查询
在查询的过程中需要记录上一次查询到的地方
在继续对上面的例子进行优化

  <select id="queryNewsByPageOptimization" resultType="news">        SELECT news_id, news_title, news_content        FROM news WHERE news_id > #{lastPageRecord} ORDER BY news_id limit           #{size} </select>

这种优化方式只适合排序字段不会出现重复值的特定场景，如果排序字段出现大量重复值，会造成分页结果的丢失。
如果第二种方式可以使用的话，则第二种优化的方式比第一种的效率更高，通过explain，第二种type为range，而第一种type为index。range的性能要比index好。

mysql> explain select * from tt where id>5 order by id limit 5\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tt   partitions: NULL         type: rangepossible_keys: PRIMARY          key: PRIMARY      key_len: 4          ref: NULL         rows: 15     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)