MySQL 单表百万分页查询优化

优化方案

• 百万级数据分页查询上可以使用小表驱动大表的方式,尽可能的减少mysql查询缓冲池的压力
• 使用子查询,先查询到满足limit条件的第一个主键(适用于自增主键),然后再以此为基础往后查询需要的size数据
• 代码展示:

    <select id="selectListPage" resultType="cn.fateverse.admin.entity.Config">
        <include refid="selectConfigVo"/>
        <where>
            # 找到第一个id
            config_id >= (select config_id from sys_config
            <where>
                <if test="query.configName != null  and query.configName != ''"> and config_name like concat('%', #{query.configName}, '%')</if>
                <if test="query.configKey != null  and query.configKey != ''"> and config_key like concat('%', #{query.configKey}, '%')</if>
                <if test="query.configType != null "> and config_type = #{query.configType}</if>
            </where> limit #{start},1)
            <if test="query.configName != null  and query.configName != ''"> and config_name like concat('%', #{query.configName}, '%')</if>
            <if test="query.configKey != null  and query.configKey != ''"> and config_key like concat('%', #{query.configKey}, '%')</if>
            <if test="query.configType != null "> and config_type = #{query.configType}</if>
             limit #{size}
        </where>
    </select>

测试数据

• 数据量: 7195220
• 每页数据量: 10
• 查询页数: 718522

测试结果

• 优化

  page query time :2141

• 未优化

  query time :3310