百万级数据分页查询优化

一、模拟百万级数据量，采用爆炸式增长方式

insert into mmw_product( product_name, product_price,product_stock,prodct_barcode,product_img,class_id,status,create_time,update_time )
select product_name, product_price, product_stock, prodct_barcode,product_img,class_id,status,create_time,update_time from mmw_product

百万数据

二、普通分页

1.普通分页查询sql语句（配合mybatis）

 <select id="listPage" parameterType="java.util.Map" resultMap="beanMap">
        SELECT * from <include refid="table" />  limit 100000,20;
 </select>

2.在dao（持久化层）计算查询时间

    System.out.println("--------begin---------");
    long beginTime = System.currentTimeMillis();  //开始查询—毫秒
    List<Object> list = getSqlSession().selectList(getStatement(SQL_LIST_PAGE)); //操作数据库
    long endTime = System.currentTimeMillis();  //结束查询—毫秒
    System.out.println("-------end-----------");
    long result=endTime-beginTime;
    System.out.println("查询耗时："+result);

3.使用postman发起查询：

接口分页查询方法

4.观察控制台输出结果，耗时18毫秒

18毫秒

5.使用explain分析语句，MySQL这次扫描的行数是20w+。

explain SELECT * from mmw_product  limit 100000,20;

explain语句分析结果图

总结：一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT 100000,20这样的查询，这时MySQL需要查询100020条记录然后只返回最后20条，前面100000条记录都被抛弃，这种分页MySQL需要从头开始一直往后计算，这样大大影响效率，代价非常高；

三、优化分页

1.使用主键索引来优化数据分页（配合mybatis）

<select id="listPage" parameterType="java.util.Map" resultMap="beanMap">
        select * from <include refid="table" /> where id>(select id from <include refid="table" /> where id>=100000 limit 1) limit 20
 </select>

2.观察控制台输出结果，耗时12毫秒

12毫秒

3.使用explain分析语句，MySQL这次扫描的行数是10w+.

explain select * from mmw_product where id>(select id from mmw_product where id>=100000 limit 1) limit 20; 

Paste_Image.png

总结：在数据量比较大的时候，我们尽量使用索引来优化sql语句。该例子优化方法如果id不是主键索引，查询效率比第一种还要低点。当然查询效率不仅仅是跟我们的sql语句、java代码有关，跟应用的架构以及硬件有关，所以说架构很重要。

四、SQL逻辑查询语句执行顺序

(7) SELECT 
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>