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

关于面试时面试官提出的一些疑问,求解答

  •  1
     
  •   MrGoooo · 119 天前 · 4932 次点击
    这是一个创建于 119 天前的主题,其中的信息可能已经有所发展或是发生改变。
    问题 1:mysql 给表中的 a,b,c 三个列建立联合索引,select * from t where a=1 and b > 2 order by c,面试官问会不会走这个联合索引,我说:会,因为满足了最左前缀匹配。面试说让我下去好好了解了解。

    问题 2:面试官问 redis 缓存和数据库一致性问题,我说:增删改的时候先删 redis 缓存再操作数据库。面试官问如果 redis 删除失败了怎么办?我说:redis 操作失败了就抛出异常,不会再对数据库操作。面试官问:如果需要 redis 操作失败的情况下也需要对数据库增删改成功,并且避免读出脏数据,改怎么做?我没想出来。
    第 1 条附言  ·  118 天前
    感谢大家的回答,我这边基本上得到了答案。

    问题 1:像这种联合索引碰到范围查询后的字段就不会走索引了, 也就是 a 和 b 会走索引,而 c 不会走索引,因为在 B+树中,会根据条件 a=1 和 b>2 找到相应的叶子节点,查询出来的结果如果为(1,3,1),(1,5,0),那么根据 order by c 就要重新排序为,(1,5,0),(1,3,1),所以 c 是不会用到索引的。但是索引还是会走这个联合索引。

    问题 2:可以用消息队列延迟双删。先删缓存,再操作数据库,数据库操作完成事务提交后,再去向消息队列发一个延迟消息(延迟时间在业务允许的范围内),这个延迟消息用来删除缓存。
    48 条回复    2021-08-05 12:12:09 +08:00
    CEBBCAT
        1
    CEBBCAT   119 天前 via Android
    我也不懂,第一个我没读过引擎源码,不好多说

    但第二个这题目的背景我想可以转换为 Redis 不可达,在这种情况下还不想读到脏数据,你鲨了我吧
    linyuyizhizou
        2
    linyuyizhizou   119 天前
    第一题我也好奇。有大佬做出来 踢我一下。
    levelworm
        3
    levelworm   119 天前
    没用过 MySQL,看了下文档:
    https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

    >MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

    感觉和索引中列的顺序有关,比如下面的例子给了:
    如果对 last_name,first_name 建立联合索引,但是仅仅查询 first_name,就不会用到这个索引。感觉说的和你一样啊,只要是最左前缀匹配就可以了,除非他建立索引的顺序不是 a, b, c 。
    mainjzb
        4
    mainjzb   119 天前
    范围和排序不能同时索引
    shalk
        5
    shalk   119 天前
    如果表就只有 a,b,c 所以索引也是 a,b,c 会走索引,而且索引覆盖
    lxy42
        6
    lxy42   119 天前 via Android
    例如这组数据就不可以用到索引:(1,3,1), (1,3,3), (1,3,5), (1,4,2), (1,4,5), (1,4,5)
    pengtdyd
        7
    pengtdyd   118 天前
    面试造火箭,这种问题在实际开发中有用?
    MOONLIGHTT
        8
    MOONLIGHTT   118 天前   ❤️ 2
    第一题是因为 order,如果是 order by a, b 的话就能走索引了
    MOONLIGHTT
        9
    MOONLIGHTT   118 天前
    第二题一般是先更新数据库再删除缓存
    sagaxu
        10
    sagaxu   118 天前
    联合索引,查询匹配到第一个非=结束,有非=时排序就用不到索引了。即使全部是=,若区分度不够高,仍然不会走索引。所以走不走索引,是个玄学,要看具体数据分布和规模以及查询条件。
    ZeawinL
        11
    ZeawinL   118 天前 via Android
    来学习下 MySQL Innodb 索引是怎么存储数据的
    https://someexp.com/post/mysql-learning/
    yinusxxxx
        12
    yinusxxxx   118 天前
    数据量小的话因为回表有成本可能不走索引,直接读更快。数据倾斜,如果区分度不高也可能不走索引比如 a 只有 2 个值,b 只有 3 个值之类的,不走索引,如果 where 条件里查出来过滤程度不高也不会走索引,因为回表成本高,DBMS 会通过采样和直方图 histogram 的方式判断数据分布情况,这里会有一个问题,采样数据直方图不是同步更新的,导致可以走索引反而不走索引或者选错索引的情况。还有一些情况暂时没想到,如果数据量够大,区分度够高会走索引。另外有的数据库支持 index skip scan,即使查询条件不符合最左索引也可以部分使用索引,走不走索引跟 order by 没什么关系,只是如果索引的顺序跟 order by 是一样的可以减少排序。order by 是在所有数据都 select 之后的操作了。
    yinusxxxx
        13
    yinusxxxx   118 天前
    更正一下,order by 不用排序也算是用上索引了,在你的例子里 order by 是用不上索引的
    yidinghe
        14
    yidinghe   118 天前 via Android
    问题出在 c 字段排序。想象索引是一棵树,通过 a 和 b 条件可以直接在树中确定一个子树范围,但是在这个范围内索引仍然是按照 a,b,c 的顺序组织的,只有按照这个顺序排序才能用到索引,直接 c 字段排序会导致需要获取子树中的所有记录,整个重新排序。
    love2020
        15
    love2020   118 天前
    这世界哪有啥一致,都是妥协罢了。
    wugq
        16
    wugq   118 天前
    为什么我觉得问题 1 可以用到索引。a 如果是范围查找只能用到 (A);这里 a 是等值,b 是范围,应该可以用到(A, B)啊。 因为 b 是范围查找,所以 c 没办法用到,需要找到数据后再对 c 排序。
    wugq
        17
    wugq   118 天前
    还是说数据少的时候避免回表会直接走聚簇索引?
    chenshun00
        18
    chenshun00   118 天前
    先反问一波是强一致性还是最终一致性,如果是强一致性,那就解决不了 (引入缓存的都无法解决强一致性,有可以解决的方式,希望大佬下边说一说)。最终一致性可以按照先 MySQL 在 Redis 搞。
    1018ji
        19
    1018ji   118 天前
    我也觉得会走索引,这个 order by 可能是 filesort
    chenshun00
        20
    chenshun00   118 天前
    ```mysql
    CREATE TABLE `test_table` (
    `action` varchar(64) NOT NULL,
    `projectId` int(11) NOT NULL COMMENT 'yapi 项目 ID',
    `projectName` varchar(32) NOT NULL COMMENT '项目名字',
    `catId` int(11) NOT NULL COMMENT 'API 分类 ID',
    `catName` varchar(64) NOT NULL COMMENT 'API 分类名字',
    `apiDesc` varchar(512) NOT NULL COMMENT 'API 分类描述',
    `title` varchar(64) NOT NULL COMMENT 'API 标题',
    `path` varchar(64) NOT NULL COMMENT 'http 请求路径',
    `method` varchar(12) NOT NULL COMMENT 'api 请求方法',
    `upTime` datetime NOT NULL DEFAULT '2020-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    `version` varchar(32) NOT NULL COMMENT '版本',
    `status` varchar(16) NOT NULL COMMENT 'api 状态(上线 /下线)',
    `fullinfo` text NOT NULL COMMENT 'API fullinfo 信息',
    `visibility` tinyint(4) NOT NULL DEFAULT '0',
    `session` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`action`),
    KEY `test_table_projectId_catId_path_index` (`projectId`,`catId`,`path`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ```

    desc select * from test_table where projectId = '11111' and catId >2 order by path;


    key: test_table_projectId_catId_path_index
    extra: Using index condition; Using filesort
    jorneyr
        21
    jorneyr   118 天前
    @MOONLIGHTT 数据库更新成功,缓存删除失败更危险
    CodeCodeStudy
        22
    CodeCodeStudy   118 天前
    第一个问题的 a=1 and b > 2 用到索引了,符合最左前缀匹配,用到了联合索引里的 a,b,跟后面的 order by c 没有关系
    CodeCodeStudy
        23
    CodeCodeStudy   118 天前
    这面试官也是个半吊子
    EmptyDX
        24
    EmptyDX   118 天前
    @lxy42 试过,可以命中索引。联合索引顺序是(a,b,c)

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE test1 ref idx_union idx_union 4 const 6 Using where; Using index; Using filesort
    @lxy42
    fkname
        25
    fkname   118 天前
    亲测第一题是走索引的。第二题一般是先更新数据库再删除缓存,先删除缓存的话可能还没更新数据库其他线程又把老数据读到缓存中去了,而后删除缓存如果失败了可以放到延迟队列重试。还有一点就是缓存一般有过期时间来兜底。
    dongtingyue
        26
    dongtingyue   118 天前
    explain 测试下不就知道了么。。。刚测试一个 type 是 ref,所以面试官硬要说没用那也没办法。my5.7,MyISAM 、InnoDB
    goodboy95
        27
    goodboy95   118 天前
    第二题如果 web 服只有一两台机器的话可以考虑在服务器上标记这个脏数据,不过大集群就不知道咋办了
    whoosy
        28
    whoosy   118 天前
    第一个只能说有机会用到索引,因为是 select * 了,因为要涉及到回表拿其他列的数据,你数据量少的时候或者索引区分度不好的时候, mysql 认为走全表扫可能速度更快,所以不会走索引
    whoosy
        29
    whoosy   118 天前
    第二个用延迟双删就行
    atalia
        30
    atalia   118 天前
    第一题不太懂意思,因为对 a,b,c 建立联合索引相当于有(a),(a,b),(a,b,c)三个索引,所以这个最起码可以用(a,b)这个索引,但是不会用( a,b,c )这个索引
    第二题缓存层和持久层操作顺序都是先持久层,再缓存层把。至于面试官的要求估计只能保证单进程级别的,跨进程除非用通信去通知所有进程标记。
    hq136234303
        31
    hq136234303   118 天前
    第二题。可以是直接走队列 强一致性的话。 直接添加队列来顺序执行。
    eric96
        32
    eric96   118 天前
    面试官可能也是半吊子,他理解的用索引是用了整个索引来做查询和排序。
    实际使用,是用了这个联合索引来查找的,然后由于 b>2,导致无法用这个索引来做排序,需要用到 file sort
    hq136234303
        33
    hq136234303   118 天前
    @whoosy 如果第二次删除失败呢?
    admol
        34
    admol   118 天前   ❤️ 4
    第一题:测试是走了部分索引的
    事物隔离级别:RR,引擎:innoDB ; MYSQL 版本:8.0.22

    创建表:
    CREATE TABLE `test` (
    `id` int NOT NULL AUTO_INCREMENT,
    `a` int DEFAULT NULL,
    `b` int DEFAULT NULL,
    `c` int DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `ids_a_b_c` (`a`,`b`,`c`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


    插入测试数据:
    INSERT INTO `my`.`test`(`id`, `a`, `b`, `c`) VALUES (1, 1, 1, 1);
    INSERT INTO `my`.`test`(`id`, `a`, `b`, `c`) VALUES (2, 2, 2, 2);
    INSERT INTO `my`.`test`(`id`, `a`, `b`, `c`) VALUES (5, 2, 3, 3);
    INSERT INTO `my`.`test`(`id`, `a`, `b`, `c`) VALUES (6, 2, 3, 4);
    INSERT INTO `my`.`test`(`id`, `a`, `b`, `c`) VALUES (3, 3, 3, 3);
    INSERT INTO `my`.`test`(`id`, `a`, `b`, `c`) VALUES (4, 4, 4, 4);


    执行计划:
    explain select * from test WHERE a = 2 and b > 2 ORDER BY c;

    mysql> explain select * from test WHERE a = 2 and b > 2 ORDER BY c;
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
    | 1 | SIMPLE | test | NULL | range | ids_a_b_c | ids_a_b_c | 10 | NULL | 2 | 100.00 | Using where; Using index; Using filesort |
    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
    1 row in set (0.02 sec)


    结果:
    type 为 range
    key 为 ids_a_b_c (建的联合索引)
    Extra 为 Using where; Using index; Using filesort
    Using index 说明相应的 select 操作中使用了覆盖索引
    Using filesort 说明排序确实没走索引



    第二题:
    延迟双删,保证数据的最终一致性
    “redis 操作失败的情况下也需要对数据库增删改成功,并且避免读出脏数据” 这种情况下,可以用 Canal 订阅 binlog 完成数据同步,保证数据的最终一致性。
    WillLiao
        35
    WillLiao   118 天前
    我觉得
    问题 1,楼主说满足最左匹配会使用索引没有问题,a 等值,b 范围,范围后的操作不会使用索引,但总体来看还是用到了组合索引
    问题 2,就是在瞎扯,这种情况就应该说做不到,没有必要保持强一致性,要保持强一致性你还引入 redis 干嘛?
    Macolor21
        36
    Macolor21   118 天前
    @admol 实践是检验真理的唯一标准 [:doge:]
    offswitch
        37
    offswitch   118 天前
    第一题,取决于你的*是什么,如果*就是 a,b,c 那么走覆盖索引,如果*不是,有可能不走索引,这个面试官大概率想问你 order by 对索引的影响,但是你这个即使有 order by 又如何?只要跟定义索引的时候同方向,即使一个索引增一个减,也会用到索引。
    https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
    第二题,操作失败也要保证增删成功,避免读出脏数据,那么你就要考虑 redis 与服务节点之间网络分区恢复的问题,考虑单节点、多节点数据的问题,要在节点标记数据为脏数据,网络恢复后,根据节点脏数据过滤,读 db.
    allAboutDbmss
        38
    allAboutDbmss   118 天前
    在 psql 里面尝试了一下:

    ```
    jigao=# create table A (a int, b int, c int);
    CREATE TABLE
    jigao=# insert into A values (1, 2, 3);
    INSERT 0 1
    jigao=# insert into A values (1, 3, 4);
    INSERT 0 1
    jigao=# insert into A values (2, 5, 6);
    INSERT 0 1
    jigao=# select * from A;
    a | b | c
    ---+---+---
    1 | 2 | 3
    1 | 3 | 4
    2 | 5 | 6
    (3 rows)

    jigao=# select * from A where a=1 and b > 2 order by c;
    a | b | c
    ---+---+---
    1 | 3 | 4
    (1 row)

    jigao=# explain select * from A where a=1 and b > 2 order by c;
    QUERY PLAN
    ---------------------------------------------------------
    Sort (cost=40.62..40.63 rows=3 width=12)
    Sort Key: c
    -> Seq Scan on a (cost=0.00..40.60 rows=3 width=12)
    Filter: ((b > 2) AND (a = 1))
    (4 rows)

    jigao=# create index id on A (a, b, c);
    CREATE INDEX
    jigao=# select * from A where a=1 and b > 2 order by c;
    a | b | c
    ---+---+---
    1 | 3 | 4
    (1 row)

    jigao=# explain select * from A where a=1 and b > 2 order by c;
    QUERY PLAN
    --------------------------------------------------------
    Sort (cost=1.05..1.06 rows=1 width=12)
    Sort Key: c
    -> Seq Scan on a (cost=0.00..1.04 rows=1 width=12)
    Filter: ((b > 2) AND (a = 1))
    (4 rows)

    jigao=# drop index id;
    DROP INDEX
    jigao=# explain select * from A where a=1 and b > 2 order by c;
    QUERY PLAN
    --------------------------------------------------------
    Sort (cost=1.05..1.06 rows=1 width=12)
    Sort Key: c
    -> Seq Scan on a (cost=0.00..1.04 rows=1 width=12)
    Filter: ((b > 2) AND (a = 1))
    (4 rows)


    ```

    观察现象是: 没用使用到 index
    但是这个主要是 cost-based 这些不一定是可以这样面试情况下有一个确定答案 我觉得就是问一个思路而已
    fkdog
        39
    fkdog   118 天前
    缓存如果要强一致的话, 可以考虑二 /三阶段提交.
    然而事实上大部分系统并不需要强一致性, 对于这类对一致性要求并不高的系统, 可以通过消息队列等手段达到最终一致性效果.
    weizhen199
        40
    weizhen199   118 天前
    我试了下,差不多 2e row,10 col 的表。在 Oracle 上是 Index Range Scan + Sort Order 。
    小表就 table scan 了
    cmai
        41
    cmai   118 天前
    @yidinghe
    1. 排序是在我拿到结果集后在内存 /磁盘中做的排序,会先走我的筛选条件筛选结果集
    2.筛选条件按照最左前缀的来的,是可以用到索引的, 用不到索引的情况楼上楼下讲的很多了,就不举例了
    yangyaofei
        42
    yangyaofei   118 天前
    我觉得,要求事务性数据库和 redis 一致性本身就是不对的, 就像非要自行车跑高速一样,在怎么优化也有出问题的地方,因为他就不是设计干这个的.

    就像你说的解决方案, 万一消息队列也异常了呢?

    应该是在认为这两个库必然会出现不一致的前提下去设计业务逻辑,从使用场景上避免因为不一致导致的问题,或者让他不是问题就行了吧. 然后一致性做到尽可能做到的很好的情况,不影响上层业务逻辑就可以了.
    BBCCBB
        43
    BBCCBB   118 天前
    2. 这个一致性只要不能保证 redis 操作和数据库操作有事务, 就一定会有不一致的可能! 所以基本无解. 只能尽力去降低不一致的概率.
    NoDocCat
        44
    NoDocCat   118 天前
    计算机科学只存在两个问题:1. 变量命名 2. 缓存失效
    blackboom
        45
    blackboom   118 天前
    2. 通过先删某个再存某个,然后出现脏数据或者脏读无解,最终都要通过消息队列,或者订阅 binlog 完成缓存刷新。
    Actrace
        46
    Actrace   118 天前
    问这种其实一点用都没有。
    如果是我,我一般会去问如何分析 SQL 中的性能问题。
    waitingChou
        47
    waitingChou   117 天前
    第一个问题面试官问法有问题,他应该继续追问一下,那具体用到了索引的哪些字段? 三个字段都用上索引了吗? 笼统地回答用上了理论上没错的。

    第二个问题是老生常谈的问题了,只是减少缓存不一致的的概率和影响,不可能保持完全一致。可用的办法很多,但都不能完美解决,延迟双删你入消息队列就失败的情况怎么办。binlog 也有这个问题,你也不能完全保证他运作,更何况 binlog 到缓存 key 的映射也是个问题。 你在这行数据上建了多少缓存,相当于又做了个小型系统出来。
    我觉得一般控制下缓存时间不太长,比如不超过五分钟。然后采用 先操作数据库再删缓存的方案就足够。
    morty0
        48
    morty0   117 天前
    2. 一定会存在不一致的情况, 如果要求完美的一致性就不应该用缓存
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2338 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 15:32 · PVG 23:32 · LAX 07:32 · JFK 10:32
    ♥ Do have faith in what you're doing.