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

一个 Mysql 索引问题

  •  
  •   dackh · 25 天前 · 1569 次点击

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

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

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

    这个 SQL,grade 是加了索引的

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

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

    第 1 条附言  ·  24 天前

    我实际的 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 条附言  ·  24 天前

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

    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 条附言  ·  24 天前

    想明白了,蛋疼的地方在

    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
        1
    yumenawei   25 天前 via Android
    帮顶
        2
    sujin190   25 天前
    感觉理解是不是有偏差,这里应该不是查出所有主键通过主键再去查询数据,正确的意思应该是索引不包含实际数据,只有主键信息,通过主键才能在真实的数据文件中读取出数据,这么设计出于提高性能目的,比如改表结构的时候,数据文件修改了,但是主键不变,此时无需重建索引,其他的比如可变类型修改超出原是长度需要挪动数据位置的时候也无需重建索引
        3
    arrow8899   25 天前
    查出所有的 id,再去回表查询 name 的值,由于 id 唯一索引查询时间是 const,所以批量查和单独查时间差不多;
    如果你不需要 name 的值得话,就不需要回表了,grade 索引已经包含了 grade 和 id 的值,直接就可以返回。
        4
    masironen   25 天前 via Android
    我觉得应该是查询出所有的主键值,然后再一次性根据主键去查需要的值,因为如果查到一个主键就去查相应的数据的话,io 次数是要多很多的,在查询速度上快不了,反而可能要比全表查询慢。

    以上是我个人的理解,不一定对。
        5
    dackh   24 天前
    @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

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

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

    但对应到主键索引表,可能就不在同一个分页,要多次查找分页
        17
    woyixinyiyi   24 天前
    @dackh 楼主 贴出你的 explain 里面的信息

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

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

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

    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)
        19
    CRVV   24 天前
    select * from T where k between 3 and 5
    用 k 的索引找到 id
    再用 id 来查整条记录

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

    ```
    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
    ```
        24
    mineqiqi   24 天前
    7000w 单表数据还不分表。。。再怎么优化也不会很快
        25
    noahsophie   24 天前
    学习了~
        26
    optional   24 天前
    把分页从 offset 分页改成主键分页,可以从 100ms->10ms
        27
    dackh   24 天前
    @optional 可以具体说说吗
        28
    jojojo   22 天前 via Android
    延迟关联
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3346 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 545ms · UTC 10:24 · PVG 18:24 · LAX 03:24 · JFK 06:24
    ♥ Do have faith in what you're doing.