V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
dackh
V2EX  ›  程序员

一个 Mysql 索引问题

  •  
  •   dackh ·
    dackh · 2019-09-19 17:55:49 +08:00 · 2865 次点击
    这是一个创建于 1876 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我们都知道 mysql 二级索引的存的是主键值,所以我们一般通过二级索引查询数据时,需要通过主键值再去查一次。

    但是现在有一个问题是:例如 sql 是这样的

    SELECT id,name,grade from student where grade = 1;
    

    这个 SQL,grade 是加了索引的

    但是根据 grade 索引查询的主键,再去通过聚集索引查的时候是如何查的?

    1、查询出所有的主键值,再一次性查出 2、一个主键就去查一次,如果是这种那就需要查询很多次

    第 1 条附言  ·  2019-09-19 20:04:53 +08:00

    我实际的 sql 是这样的

    select id from xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500;
    
    select id,user_idfrom xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500;
    

    这个表数据量 7000w+,第一条 sql 查询可能 100ms+,第二条 sql 得将近 1min

    我想这是为什么

    第 2 条附言  ·  2019-09-19 20:59:23 +08:00

    使用二级索引查询执行过程

    select * from T where k between 3 and 5
    
    1. 在 k 索引树上找到 k=3 的记录,取得 ID = 300;

    2. 再到 ID 索引树查到 ID=300 对应的 R3;

    3. 在 k 索引树取下一个值 k=5,取得 ID=500;

    4. 再回到 ID 索引树查到 ID=500 对应的 R4;

    5. 在 k 索引树取下一个值 k=6,不满足条件,循环结束。

    这是我看到的解释,如果二级索引执行过程是这样的,那么慢就可以理解了,但是Mysql这么蠢吗,要一条一条记录去回表?

    第 3 条附言  ·  2019-09-20 11:06:59 +08:00

    想明白了,蛋疼的地方在

    Limit 300000,500
    

    limit 这个蛋疼的东西是查询出最终的结果再去除前offset的数据,所以这条sql相当于需要找300500条数据。

    • 第一条SQL走索引,因为只查主键,所以一次查询就出来了
    • 第二条SQL因为需要回表,回表需要通过主键去匹配300500条数据,这就造成查询需要1min的情况。

    limit这种大列表数据最好的方法是分两条SQL查询。例如:

    SELECT id FROM xxx.xxx WHERE type = 12 AND statue in (11,12,13) limit 300000,500;
    

    然后再通过查询出来的主键去in,这样就只需要匹配500条数据,而不是300500条数据了。

    SELECT * FROM xxx.xxx WHERE id in (`第一条SQL查询的结果`);
    
    28 条回复    2019-09-22 17:28:50 +08:00
    yumenawei
        1
    yumenawei  
       2019-09-19 18:00:06 +08:00 via Android
    帮顶
    sujin190
        2
    sujin190  
       2019-09-19 18:07:39 +08:00
    感觉理解是不是有偏差,这里应该不是查出所有主键通过主键再去查询数据,正确的意思应该是索引不包含实际数据,只有主键信息,通过主键才能在真实的数据文件中读取出数据,这么设计出于提高性能目的,比如改表结构的时候,数据文件修改了,但是主键不变,此时无需重建索引,其他的比如可变类型修改超出原是长度需要挪动数据位置的时候也无需重建索引
    arrow8899
        3
    arrow8899  
       2019-09-19 18:14:20 +08:00
    查出所有的 id,再去回表查询 name 的值,由于 id 唯一索引查询时间是 const,所以批量查和单独查时间差不多;
    如果你不需要 name 的值得话,就不需要回表了,grade 索引已经包含了 grade 和 id 的值,直接就可以返回。
    masironen
        4
    masironen  
       2019-09-19 18:16:08 +08:00 via Android
    我觉得应该是查询出所有的主键值,然后再一次性根据主键去查需要的值,因为如果查到一个主键就去查相应的数据的话,io 次数是要多很多的,在查询速度上快不了,反而可能要比全表查询慢。

    以上是我个人的理解,不一定对。
    dackh
        5
    dackh  
    OP
       2019-09-19 20:03:54 +08:00
    @arrow8899
    我实际的 sql 是这样的
    ```
    select id from xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500;
    ```
    ```
    select id,user_idfrom xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500;
    ```

    这个表数据量 7000w+,第一条 sql 查询可能 100ms+,第二条 sql 得将近 1min

    我想这是为什么
    mccreefei
        6
    mccreefei  
       2019-09-19 20:18:16 +08:00
    @dackh #5 我想知道你实际 sql 中二级索引是怎么建的
    NizumaEiji
        7
    NizumaEiji  
       2019-09-19 20:19:44 +08:00
    覆盖索引?
    dackh
        8
    dackh  
    OP
       2019-09-19 20:41:36 +08:00
    @NizumaEiji 第一条因为 id 是 primary key 不需要回表,所以很快,第二条 user_id,需要多回表查一次,但是回表一次为什么这么慢
    dackh
        9
    dackh  
    OP
       2019-09-19 20:42:33 +08:00
    @mccreefei type 跟 status 都有单列索引,问题的关键在雨多查一个 user_id 为什么回表查一次慢这么多
    mccreefei
        10
    mccreefei  
       2019-09-19 20:53:24 +08:00
    @dackh #9 type 和 status 两张二级索引表根据索引值查询很快,是因为要查询的索引值是连续的,查询到的数据也是分布在连续的页上(连续的页之间有指针可快速访问)。但是得到的主键值却不连续,基于你的数据量有 7000w 之多,那么回表查询的数据页跨度也会很大,因此两者查询速度相差大。
    NizumaEiji
        11
    NizumaEiji  
       2019-09-19 20:56:04 +08:00
    @dackh #8
    你分别 explain 一下看看吧
    aliipay
        12
    aliipay  
       2019-09-19 20:57:59 +08:00 via Android
    没看太明白,我猜问题原因是 offset 太大
    dackh
        13
    dackh  
    OP
       2019-09-19 20:58:04 +08:00
    @mccreefei 按这么说,主键也是有索引的,那根据主键索引回表一次这么慢?我加了一条附言,你看下
    dackh
        14
    dackh  
    OP
       2019-09-19 20:59:52 +08:00
    @NizumaEiji explain 出来的结果一模一样的
    dackh
        15
    dackh  
    OP
       2019-09-19 21:02:45 +08:00
    @mccreefei 或者你的意思是回表查询的时候不会走主键索引
    DonaldY
        16
    DonaldY  
       2019-09-19 21:24:17 +08:00
    `select * from T where k between 3 and 5`

    在 k 索引表下,3 5 是连续的,可能在同一个分页。

    但对应到主键索引表,可能就不在同一个分页,要多次查找分页
    woyixinyiyi
        17
    woyixinyiyi  
       2019-09-19 22:43:57 +08:00
    @dackh 楼主 贴出你的 explain 里面的信息

    第一条 sql 查询可能 100ms+,这个是因为二级索引会保护聚簇索引的值,这个时候是不需要回表的。
    第二条 sql 得将近 1min,这个是因为查询的字段的值 user_id,不仅仅只有聚簇索引,包括其他的字段,同时这个字段也不是覆盖索引的字段,所以这个过程肯定是需要根据聚簇索引回表来查询具体的行记录的。

    mysql 毕竟只是一个程序,没那么智能,只能尽可能覆盖各种场景遇到的一些问题。

    比如你遇到的问题
    根据二级索引查询可以获取一个聚簇索引集合(主键集合一般为 id ),
    楼主可以用关键字 搜索下 read_rnd_buffer 和 MRR。
    如果开启 MRR,大概思路是,根据你二级索引获取到的聚簇索引进行排序,针对排完的聚簇索引去回表查询,
    因为聚簇索引是在数据页中是有序的,这样让读取数据是顺序读,理论上是可以减少磁盘数据页的访问。
    akira
        18
    akira  
       2019-09-20 00:02:27 +08:00
    那这样写呢 ,这样就能保证他是一次过去查了吧

    select id , user_id from xxxx
    where
    id in (select id from xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500)
    CRVV
        19
    CRVV  
       2019-09-20 00:21:19 +08:00
    select * from T where k between 3 and 5
    用 k 的索引找到 id
    再用 id 来查整条记录

    我觉得这么查没有问题,请问不蠢的查询方式是什么?
    aliipay
        20
    aliipay  
       2019-09-20 00:31:24 +08:00
    @akira subquery 不支持 limit,可以改成 select id, user_id from xx join ( select id from xx limit 300000,50) yy on xx.id = yy.id
    chibupang
        21
    chibupang  
       2019-09-20 01:35:47 +08:00 via Android
    索引都没有命中吧?最左匹配原则了解一下
    Soar360
        22
    Soar360  
       2019-09-20 01:53:33 +08:00 via iPhone
    覆盖索引?
    GTim
        23
    GTim  
       2019-09-20 09:07:31 +08:00
    因为,使不使用索引是看天吃饭的.....

    ```
    If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan
    ```
    mineqiqi
        24
    mineqiqi  
       2019-09-20 09:30:55 +08:00
    7000w 单表数据还不分表。。。再怎么优化也不会很快
    noahsophie
        25
    noahsophie  
       2019-09-20 11:46:33 +08:00
    学习了~
    optional
        26
    optional  
       2019-09-20 12:23:56 +08:00
    把分页从 offset 分页改成主键分页,可以从 100ms->10ms
    dackh
        27
    dackh  
    OP
       2019-09-20 14:05:54 +08:00
    @optional 可以具体说说吗
    jojojo
        28
    jojojo  
       2019-09-22 17:28:50 +08:00 via Android
    延迟关联
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4403 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 01:03 · PVG 09:03 · LAX 17:03 · JFK 20:03
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.