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}}
复制代码

 

posted on   五官一体即忢  阅读(58)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

导航

统计

点击右上角即可分享
微信分享提示