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

我也不懂，第一个我没读过引擎源码，不好多说

但第二个这题目的背景我想可以转换为 Redis 不可达，在这种情况下还不想读到脏数据，你鲨了我吧

第一题我也好奇。有大佬做出来 踢我一下。

没用过 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 。

范围和排序不能同时索引

如果表就只有 a,b,c 所以索引也是 a,b,c 会走索引，而且索引覆盖

例如这组数据就不可以用到索引：(1,3,1), (1,3,3), (1,3,5), (1,4,2), (1,4,5), (1,4,5)

面试造火箭，这种问题在实际开发中有用？

第一题是因为 order，如果是 order by a, b 的话就能走索引了

第二题一般是先更新数据库再删除缓存

联合索引，查询匹配到第一个非=结束，有非=时排序就用不到索引了。即使全部是=，若区分度不够高，仍然不会走索引。所以走不走索引，是个玄学，要看具体数据分布和规模以及查询条件。

来学习下 MySQL Innodb 索引是怎么存储数据的
https://someexp.com/post/mysql-learning/

数据量小的话因为回表有成本可能不走索引，直接读更快。数据倾斜，如果区分度不高也可能不走索引比如 a 只有 2 个值，b 只有 3 个值之类的，不走索引，如果 where 条件里查出来过滤程度不高也不会走索引，因为回表成本高，DBMS 会通过采样和直方图 histogram 的方式判断数据分布情况，这里会有一个问题，采样数据直方图不是同步更新的，导致可以走索引反而不走索引或者选错索引的情况。还有一些情况暂时没想到，如果数据量够大，区分度够高会走索引。另外有的数据库支持 index skip scan，即使查询条件不符合最左索引也可以部分使用索引，走不走索引跟 order by 没什么关系，只是如果索引的顺序跟 order by 是一样的可以减少排序。order by 是在所有数据都 select 之后的操作了。

更正一下，order by 不用排序也算是用上索引了，在你的例子里 order by 是用不上索引的

问题出在 c 字段排序。想象索引是一棵树，通过 a 和 b 条件可以直接在树中确定一个子树范围，但是在这个范围内索引仍然是按照 a，b，c 的顺序组织的，只有按照这个顺序排序才能用到索引，直接 c 字段排序会导致需要获取子树中的所有记录，整个重新排序。

这世界哪有啥一致，都是妥协罢了。

为什么我觉得问题 1 可以用到索引。a 如果是范围查找只能用到 (A)；这里 a 是等值，b 是范围，应该可以用到(A, B)啊。 因为 b 是范围查找，所以 c 没办法用到，需要找到数据后再对 c 排序。

还是说数据少的时候避免回表会直接走聚簇索引？

先反问一波是强一致性还是最终一致性，如果是强一致性，那就解决不了 (引入缓存的都无法解决强一致性，有可以解决的方式，希望大佬下边说一说)。最终一致性可以按照先 MySQL 在 Redis 搞。

我也觉得会走索引，这个 order by 可能是 filesort

```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

@MOONLIGHTT 数据库更新成功，缓存删除失败更危险

第一个问题的 a=1 and b > 2 用到索引了，符合最左前缀匹配，用到了联合索引里的 a,b，跟后面的 order by c 没有关系

这面试官也是个半吊子

@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

亲测第一题是走索引的。第二题一般是先更新数据库再删除缓存，先删除缓存的话可能还没更新数据库其他线程又把老数据读到缓存中去了，而后删除缓存如果失败了可以放到延迟队列重试。还有一点就是缓存一般有过期时间来兜底。

explain 测试下不就知道了么。。。刚测试一个 type 是 ref，所以面试官硬要说没用那也没办法。my5.7，MyISAM 、InnoDB

第二题如果 web 服只有一两台机器的话可以考虑在服务器上标记这个脏数据，不过大集群就不知道咋办了

第一个只能说有机会用到索引，因为是 select * 了，因为要涉及到回表拿其他列的数据，你数据量少的时候或者索引区分度不好的时候, mysql 认为走全表扫可能速度更快，所以不会走索引

第二个用延迟双删就行

第一题不太懂意思，因为对 a,b,c 建立联合索引相当于有(a)，(a,b)，(a,b,c)三个索引，所以这个最起码可以用(a,b)这个索引,但是不会用（ a,b,c ）这个索引
第二题缓存层和持久层操作顺序都是先持久层，再缓存层把。至于面试官的要求估计只能保证单进程级别的，跨进程除非用通信去通知所有进程标记。

第二题。可以是直接走队列 强一致性的话。 直接添加队列来顺序执行。

面试官可能也是半吊子，他理解的用索引是用了整个索引来做查询和排序。
实际使用，是用了这个联合索引来查找的，然后由于 b>2，导致无法用这个索引来做排序，需要用到 file sort

@whoosy 如果第二次删除失败呢？

第一题：测试是走了部分索引的
事物隔离级别：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 完成数据同步，保证数据的最终一致性。

我觉得
问题 1，楼主说满足最左匹配会使用索引没有问题，a 等值，b 范围，范围后的操作不会使用索引，但总体来看还是用到了组合索引
问题 2，就是在瞎扯，这种情况就应该说做不到，没有必要保持强一致性，要保持强一致性你还引入 redis 干嘛？

@admol 实践是检验真理的唯一标准 [:doge:]

第一题，取决于你的*是什么，如果*就是 a,b,c 那么走覆盖索引，如果*不是，有可能不走索引，这个面试官大概率想问你 order by 对索引的影响，但是你这个即使有 order by 又如何？只要跟定义索引的时候同方向，即使一个索引增一个减，也会用到索引。
https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
第二题，操作失败也要保证增删成功，避免读出脏数据，那么你就要考虑 redis 与服务节点之间网络分区恢复的问题，考虑单节点、多节点数据的问题，要在节点标记数据为脏数据，网络恢复后，根据节点脏数据过滤，读 db.

在 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 这些不一定是可以这样面试情况下有一个确定答案 我觉得就是问一个思路而已

缓存如果要强一致的话, 可以考虑二 /三阶段提交.
然而事实上大部分系统并不需要强一致性, 对于这类对一致性要求并不高的系统, 可以通过消息队列等手段达到最终一致性效果.

我试了下，差不多 2e row，10 col 的表。在 Oracle 上是 Index Range Scan + Sort Order 。
小表就 table scan 了

@yidinghe
1. 排序是在我拿到结果集后在内存 /磁盘中做的排序，会先走我的筛选条件筛选结果集
2.筛选条件按照最左前缀的来的，是可以用到索引的， 用不到索引的情况楼上楼下讲的很多了，就不举例了

我觉得,要求事务性数据库和 redis 一致性本身就是不对的, 就像非要自行车跑高速一样,在怎么优化也有出问题的地方,因为他就不是设计干这个的.

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

应该是在认为这两个库必然会出现不一致的前提下去设计业务逻辑,从使用场景上避免因为不一致导致的问题,或者让他不是问题就行了吧. 然后一致性做到尽可能做到的很好的情况,不影响上层业务逻辑就可以了.

2. 这个一致性只要不能保证 redis 操作和数据库操作有事务, 就一定会有不一致的可能! 所以基本无解. 只能尽力去降低不一致的概率.

计算机科学只存在两个问题：1. 变量命名 2. 缓存失效

2. 通过先删某个再存某个，然后出现脏数据或者脏读无解，最终都要通过消息队列，或者订阅 binlog 完成缓存刷新。

问这种其实一点用都没有。
如果是我，我一般会去问如何分析 SQL 中的性能问题。

第一个问题面试官问法有问题，他应该继续追问一下，那具体用到了索引的哪些字段？ 三个字段都用上索引了吗？ 笼统地回答用上了理论上没错的。

第二个问题是老生常谈的问题了，只是减少缓存不一致的的概率和影响，不可能保持完全一致。可用的办法很多，但都不能完美解决，延迟双删你入消息队列就失败的情况怎么办。binlog 也有这个问题，你也不能完全保证他运作，更何况 binlog 到缓存 key 的映射也是个问题。 你在这行数据上建了多少缓存，相当于又做了个小型系统出来。
我觉得一般控制下缓存时间不太长，比如不超过五分钟。然后采用 先操作数据库再删缓存的方案就足够。

2. 一定会存在不一致的情况, 如果要求完美的一致性就不应该用缓存