@zhangysh1995 #13

你说“WHERE 和 ON 在 JOIN 情况下没有什么区别”，你是说 inner join 吗？如果是的话，你说得没错，但是我想更加明确地说出，where 和 on 在 outer join 的表现是有区别的。

在“Database System Concepts 6th edition - Chapter 4 Intermediate SQL - 4.1 Join Expressions - 4.1.1 Join Conditions”中，说了“However, there are two good reasons for introducing the on condition. First, we shall see shortly that for a kind of join called an outer join, on conditions do behave in a manner different from where conditions. Second, an SQL query is often more readable by humans if the join condition is specified in the on clause and the rest of the conditions appear in the where clause.”。之后在“4.1.2 Outer Joins”中，它说“As we noted earlier, on and where behave differently for outer join. The reason for this is that outer join adds null-padded tuples only for those tuples that do not contribute to the result of the corresponding inner join. The on condition is part of the outer join specification, but a where clause is not.”。

我这里用例子演示一下 on 和 where 的区别吧。
create table t1(c1 int);
create table t2(c2 int);
insert into t1 values(1),(2),(3);
insert into t2 values(1);

select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; 的结果为
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
| 2 | NULL |
| 3 | NULL |
+------+------+

select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; 的结果为
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+

@xgfan 我现在是使用 String 替代枚举作为替代应急措施，但是我更希望 Jackson 自身能够实现"保存 Map 中 key 的类型信息"，因为不能改用其他的序列化 /反序列化机制。

@hanssx #8 https://stackoverflow.com/a/47451071/5232255，这个回答或许对你有帮助。我暂时没有找到更好的方法。

@hanssx #5 谁先谁后“只能”通过最后的执行计划确定（“只能”加上双引号是因为有些还是可以直接看出来的）。如果 select 先执行，那么`select column1 from t order by column2`怎么能够成功执行呢？说 order by 先执行，`select column1 from t order by column1`，如果 t 有很多列，那么先执行 select，然后再执行 order by，肯定性能会更好。

@hanssx #4 第一条和第三条回复加起来就能够解释为什么两者不一样了，你的理解是对的。

@JasonLaw #1 第一条回复加上 https://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first/14104090 应该能够解答你的疑问。

@JasonLaw #1 不好意思，看错了

请教一：order by 1+1 不等价于 order by 2 的原因？
这是因为你的表只有一列，所以会报错，你可以使用`order by 1`试试。

请教二：order by (select 0)不等价于 order by 0 的原因？
同样的道理，`order by ordinal`中的 ordinal 要大于 0，小于等于表的列数

详细请看 https://stackoverflow.com/questions/11353688/what-is-this-order-by-1

注意：官方文档有这么一句话
Use of column positions is deprecated because the syntax has been removed from the SQL standard.

@izgnod #3 好的，期待你的回复。

@izgnod #1 我做了以下实验，事实并不是你所说的那样，并不是“插入前获取辅助索引的插入意向锁，插入后获取主键的记录锁”。而且问题中的两个 schedules 也能证明你所说的是不正确的。

create table t2(id int primary key, value int, index ix_t2_value(value));
insert into t2 values (5,10),(10,5);

-- session 1
start transaction;
select * from t2 where id = 3 for share;

-- session 2
start transaction;
insert into t2 values (2, 8); -- 等待获取 PRIMARY 索引上的插入意向锁(-∞ , 5)

select * from performance_schema.data_locks 的输出为：

+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+
| INNODB | 140311361761480:1063:140311280045776 | 2072 | 50 | 14 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
| INNODB | 140311361761480:2:4:2:140311280042864 | 2072 | 50 | 14 | test | t2 | NULL | NULL | PRIMARY | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5 |
| INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
| INNODB | 140311361760632:2:4:2:140311280036640 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | PRIMARY | 140311280036640 | RECORD | S,GAP | GRANTED | 5 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+



---



create table t2(id int primary key, value int, index ix_t2_value(value));
insert into t2 values (5,10),(10,5);

-- session 1
start transaction;
select * from t2 where value = 3 for share;

-- session 2
start transaction;
insert into t2 values (8, 2); -- 等待获取 ix_t2_value 索引上的插入意向锁(-∞, (5, 10))

select * from performance_schema.data_locks 的输出为：

+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+
| INNODB | 140311361761480:1063:140311280045776 | 2073 | 50 | 18 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
| INNODB | 140311361761480:2:5:3:140311280042864 | 2073 | 50 | 18 | test | t2 | NULL | NULL | ix_t2_value | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5, 10 |
| INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
| INNODB | 140311361760632:2:5:3:140311280036640 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | ix_t2_value | 140311280036640 | RECORD | S,GAP | GRANTED | 5, 10 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+

@azygote #5 谢谢🙏

最后的代码为：

PolymorphicTypeValidator ptv = BasicPolymorphicTypeValidator.builder()
.allowIfSubType(ImmutableMap.class)
.build();
ObjectMapper objectMapper = JsonMapper.builder()
.addModule(new GuavaModule())
.activateDefaultTyping(ptv, ObjectMapper.DefaultTyping.EVERYTHING)
.build();

Map<Integer, Integer> map1 = ImmutableMap.of(1, 1);

// content 为`["com.google.common.collect.SingletonImmutableBiMap",{"1":1}]`
String content = objectMapper.writeValueAsString(map1);
// 能够成功反序列化
Map<Integer, Integer> map2 = objectMapper.readValue(content, new TypeReference<Map<Integer, Integer>>() {
});

我现在的处理方式是使用 HashMap 作为“中间人”来实现 ImmutableMap 的序列化和反序列化。参考 https://stackoverflow.com/a/34115875/5232255

@xgfan #2 谢谢提示🙏

改变后的代码如下：

PolymorphicTypeValidator ptv = BasicPolymorphicTypeValidator.builder()
.allowIfSubType(ImmutableMap.class)
.build();
ObjectMapper objectMapper = new ObjectMapper()
.activateDefaultTyping(ptv, ObjectMapper.DefaultTyping.EVERYTHING);

// Map<Integer, Integer> map1 = new HashMap<>();
// map1.put(1, 1);
Map<Integer, Integer> map1 = ImmutableMap.of(1, 1);

// content 为`["com.google.common.collect.SingletonImmutableBiMap",{"1":1}]`
String content = objectMapper.writeValueAsString(map1);
// 但是因为 SingletonImmutableBiMap 没有默认的构造器，反序列化报错
// com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Cannot construct instance of `com.google.common.collect.SingletonImmutableBiMap` (no Creators, like default constructor, exist): no default constructor found
at [Source: (String)"["com.google.common.collect.SingletonImmutableBiMap",{"1":1}]"; line: 1, column: 54]
Map<Integer, Integer> map2 = objectMapper.readValue(content, new TypeReference<Map<Integer, Integer>>() {
});

@optional #6
@15hop #10

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data.

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

@flyfanc #1
@mayday526 #7
@chihiro2014 #8
@996635 #11
@Risin9 #12

以下是我做的一个测试。

1. 创建表（注意：id 和 value 的类型是一样的）

CREATE TABLE `t`
(
`id` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;

2. 插入数据（伪代码），id 和 value 的值是一样的

for (int i = 1; i < 500000; i++) {
insert into t values (i, i);
}

3. 使用命令行登录进数据库管理系统，并使用特定的数据库

mysql -u {user} -p{password}
use database;

4. 开启 profiling

SET profiling = 1;

5. 执行以下语句

select * from t;
select id from t;
select value from t;

6. `show PROFILES;`的结果如下：

+----------+------------+---------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------+
| 1 | 0.24099925 | select * from t |
| 2 | 0.15437950 | select id from t |
| 3 | 0.14546525 | select value from t |
+----------+------------+---------------------+

参考资料：
https://dev.mysql.com/doc/refman/5.6/en/show-profile.html
https://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profiling.html

能够清楚地表达一下你的“为啥”是什么吗？反正我是不明白你的问题是什么

@flyfanc 两个都是使用 clustered index 吧

用 SELECT NAME FROM query_test 的结果如何呢？

@louettagfh 我不是纠结细节，我只是想知道事情到底是怎样的。