我们一般使用分页都是使用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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 93
filtered: 100.00
Extra: Using filesort
1 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: ALL
possible_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_ref
possible_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: index
possible_keys: NULL
key: news_title
key_len: 767
ref: NULL
rows: 15
filtered: 100.00
Extra: Using index
3 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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 93
filtered: 100.00
Extra: NULL
1 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: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 15
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)