百万级数据量sql优化

 

sql分页优化：

1.SELECT * FROM mt_data_subtitle_site_task_download where id > #{id} limit 10;

2.SELECT * FROM mt_data_subtitle_site_task_download  where id >=  (SELECT id FROM mt_data_subtitle_site_task_download LIMIT #{count }, 1)

总结：最重要还是有效的利用索引去进行分页查询

mybatis：

<select id="selectByQueryWithPage" resultMap="ResultMapWithBLOBs">
    SELECT * FROM TABLE
    where id <![CDATA[>=]]>
    (
        SELECT id FROM TABLE
        where id is not null
        <if test="status != ''">
            and status = #{status}
        </if>
        order by id  asc
        LIMIT ${offset},1
    )
    <if test="status != ''">
        and status = #{status}
    </if>
    order by id asc
    limit 10
</select>