mysql大数据量的分页慢优化

例子

这边通过一张40w条记录的表来说明一下优化大数据量表分页慢的思路

表单自带拼接语句

SELECT
    tbPage.* 
FROM
    (
    SELECT
        tbTemp.*,
        row_number() over ( ORDER BY ID )- 1 AS rownum__ 
    FROM
        (
        SELECT
            __T1.ID AS ID,
            __T1.NAME AS NAME,
            __T1.SEX AS SEX,
            a0.label AS SEX__label,
            __T1.BIRTHDAY AS BIRTHDAY,
            __T1.IS_DIFFICULTY AS IS_DIFFICULTY,
            __T1.PHONE AS PHONE,
            __T1.AREA_CODE AS AREA_CODE,
            a1.label AS AREA_CODE__label,
            __T1.THREATEN AS THREATEN,
            __T1.GROUP_NAME AS GROUP_NAME,
            __T1.RESPONSIBLE AS RESPONSIBLE,
            __T1.LEAVE_SIGNAL AS LEAVE_SIGNAL,
            __T1.LEAVE_WAY AS LEAVE_WAY,
            __T1.RESETTLEMENT_SITE AS RESETTLEMENT_SITE,
            __T1.REMARK AS REMARK 
        FROM
            tb_person __T1
            LEFT JOIN ( SELECT text AS label, VALUE AS VALUE FROM ss_sys_dic WHERE TYPE = '10007' ) a0 ON __T1.SEX = a0.
            
        VALUE
            LEFT JOIN ( SELECT AREA_NAME AS label, AREA_CODE AS VALUE FROM ss_sys_area ) a1 ON __T1.AREA_CODE = a1.
        VALUE
            
        ) tbTemp 
    WHERE
        1 = 1 
        AND AREA_CODE LIKE CONCAT( '', '', '%' ) 
    ) tbPage 
WHERE
    rownum__ BETWEEN 391930 
    AND 391939

 

Where条件内放

select
  *
from
  (
    SELECT
            __T1.ID AS ID,
            __T1.NAME AS NAME,
            __T1.SEX AS SEX,
            a0.label AS SEX__label,
            __T1.BIRTHDAY AS BIRTHDAY,
            __T1.IS_DIFFICULTY AS IS_DIFFICULTY,
            __T1.PHONE AS PHONE,
            __T1.AREA_CODE AS AREA_CODE,
            a1.label AS AREA_CODE__label,
            __T1.THREATEN AS THREATEN,
            __T1.GROUP_NAME AS GROUP_NAME,
            __T1.RESPONSIBLE AS RESPONSIBLE,
            __T1.LEAVE_SIGNAL AS LEAVE_SIGNAL,
            __T1.LEAVE_WAY AS LEAVE_WAY,
            __T1.RESETTLEMENT_SITE AS RESETTLEMENT_SITE,
            __T1.REMARK AS REMARK 
        FROM
            (select * from tb_person where
  1 = 1  AND AREA_CODE LIKE CONCAT('', '', '%')   order by ID LIMIT  391920, 10) __T1
            LEFT JOIN ( SELECT text AS label, VALUE AS VALUE FROM ss_sys_dic WHERE TYPE = '10007' ) a0 ON __T1.SEX = a0.
            
        VALUE
            LEFT JOIN (
            SELECT
                AREA_NAME AS label,
                AREA_CODE AS 
            VALUE
                
            FROM
                ss_sys_area ) a1 on __T1.AREA_CODE = a1.value 
  ) tbTemp

利用"连接+索引"方式查询

select
  *
from
  (
    SELECT
            __T1.ID AS ID,
            __T1.NAME AS NAME,
            __T1.SEX AS SEX,
            a0.label AS SEX__label,
            __T1.BIRTHDAY AS BIRTHDAY,
            __T1.IS_DIFFICULTY AS IS_DIFFICULTY,
            __T1.PHONE AS PHONE,
            __T1.AREA_CODE AS AREA_CODE,
            a1.label AS AREA_CODE__label,
            __T1.THREATEN AS THREATEN,
            __T1.GROUP_NAME AS GROUP_NAME,
            __T1.RESPONSIBLE AS RESPONSIBLE,
            __T1.LEAVE_SIGNAL AS LEAVE_SIGNAL,
            __T1.LEAVE_WAY AS LEAVE_WAY,
            __T1.RESETTLEMENT_SITE AS RESETTLEMENT_SITE,
            __T1.REMARK AS REMARK 
        FROM
            (select b.* from (
SELECT id from tb_person where
  1 = 1  AND AREA_CODE LIKE CONCAT('', '', '%')   order by ID LIMIT  391920, 10
) a left join tb_person b on a.id = b.id) __T1
            LEFT JOIN ( SELECT text AS label, VALUE AS VALUE FROM ss_sys_dic WHERE TYPE = '10007' ) a0 ON __T1.SEX = a0.
            
        VALUE
            LEFT JOIN (
            SELECT
                AREA_NAME AS label,
                AREA_CODE AS 
            VALUE
                
            FROM
                ss_sys_area ) a1 on __T1.AREA_CODE = a1.value 
  ) tbTemp

 

优化思路

• 搜索的条件需要加上索引，并且不要做外键label查询这种需求，一定要把查询条件内放到大表的where条件中去
• 需要使用 带分页sql 功能来重写列表查询语句
• Mysql的limit语句的查询时间与起始记录的位置成正比即分页的页码越大，查询效率越低，利用表的覆盖索引来加速分页查询：我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。（一句话概括 主分页语句的列只包含主键就好了）
  在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。
• 使用连接+索引方式进行查询

如 :

select b.* from (
SELECT id from tb_person where
  1 = 1  AND AREA_CODE LIKE CONCAT('', '', '%')   order by ID LIMIT  391920, 10
) a left join tb_person b on a.id = b.id

 

 就比

select * from tb_person where
  1 = 1  AND AREA_CODE LIKE CONCAT('', '', '%')   order by ID LIMIT  391920, 10

快很多

优化写法

select
  *
from
  (
    SELECT
            __T1.ID AS ID,
            __T1.NAME AS NAME,
            __T1.SEX AS SEX,
            a0.label AS SEX__label,
            __T1.BIRTHDAY AS BIRTHDAY,
            __T1.IS_DIFFICULTY AS IS_DIFFICULTY,
            __T1.PHONE AS PHONE,
            __T1.AREA_CODE AS AREA_CODE,
            a1.label AS AREA_CODE__label,
            __T1.THREATEN AS THREATEN,
            __T1.GROUP_NAME AS GROUP_NAME,
            __T1.RESPONSIBLE AS RESPONSIBLE,
            __T1.LEAVE_SIGNAL AS LEAVE_SIGNAL,
            __T1.LEAVE_WAY AS LEAVE_WAY,
            __T1.RESETTLEMENT_SITE AS RESETTLEMENT_SITE,
            __T1.REMARK AS REMARK 
        FROM
            (select b.* from (
SELECT id from tb_person where
  1 = 1 {{__where}} {{__order}} LIMIT  #{__page_begin}, #{__page_size}
) a left join tb_person b on a.id = b.id ) __T1
            LEFT JOIN ( SELECT text AS label, VALUE AS VALUE FROM ss_sys_dic WHERE TYPE = '10007' ) a0 ON __T1.SEX = a0.
            
        VALUE
            LEFT JOIN (
            SELECT
                AREA_NAME AS label,
                AREA_CODE AS 
            VALUE
                
            FROM
                ss_sys_area ) a1 on __T1.AREA_CODE = a1.value 
  ) tbTemp
[分割符]
select
  count(*)
from
  tb_person
where
  1 = 1 {{__where}}