MySQL中那些诡异的特性

先看一个现象

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE `type_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `num` bigint(20) unsigned NOT NULL,
  `str` varchar(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_num` (`num`) USING BTREE,
  KEY `idx_str` (`str`) USING BTREE
)  DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM type_test where str=1;
EXPLAIN SELECT * FROM type_test where num='13';

你认为上述两个查询（输入的类型和实际类型不一致），是否都会命中索引呢？

答案是：

字段类型为number，用string去查，命中索引
字段类型为string，用number去查，不会命中

思维冲突点

在使用高级开发语言过程中

number转string会比较可控
- 比如golang里面的strconv.Itoa，其签名为func Itoa(i int) string，直接返回了string对象
string转number是不可控的，因为字符串可能不是一个合法的int，比如'123a'
- 比如golang里面的strconv.Atoi，其签名为func Atoi(s string) (int, error)，返回了int和error对象，需要上层做额外的类型判断

按照这个惯性思维，SELECT * FROM type_test where str=1 也应该命中索引，在查询前将1转为字符串'1'不就行了？

原因探查

查阅多个资料，最后得出结论：MySQL遇到类型不一致的比较时，只会 string 👉 number

SELECT * FROM type_test where num='13' 发生转换的string 👉 number是查询条件，到达索引层的就是 number vs numbers
SELECT * FROM type_test where str=1 要进行比较，只能保持 number 不变，而去将表记录的string转为number。如果转换的对象是表内索引字段，那么肯定不会命中索引

为什么会有这个设计？

我猜应该是为了满足这种查询场景：（这个语句不会报错，而是会查出 num=13 的所有记录）

1
SELECT * FROM type_test where num='13abc';

目前没有找到可以完全说服我的结论，给一个网上比较常见的解释：

这种在处理类型不匹配的等值比较时，所遵循的「低优先级类型向高优先级类型转换」的原则，核心是效率和一致性：
数值类型的比较运算（=、>、<等）是数据库中最基础、最高效的运算，底层实现简单，速度快。
字符串比较需要按字符编码（如 UTF-8）逐字节对比，效率远低于数值比较，MySQL 优先选择数值类型比较，是为了尽可能提升查询效率。

NULL > 数值类型（int、bigint、float、double 等） > 字符串类型（char、varchar 等） > 日期 / 时间类型（datetime、date 等）

其它主流数据如何处理这种场景的？

TiDB: 和MySQL一样，可以正常执行，但是索引不一定命中
PostgreSQL: 不允许类型不一致，直接报错
Oracle: 能转换的就转且能命中索引，不能转的直接报错
- int类型 + where ‘123’: 正常查询，索引有效
- string类型 + where 123：报错
SQL Server/SQLite: 正常运行且索引都有效

思考

一些主流的中间件所展示的规则，不一定是真理。有的只是其自身为了满足其它功能而做出的妥协，不都能拿来作为架构设计的经验。

再说一个例子因为MySQL(InnoDB)的默认隔离级别是RR（大家都在用），所以我建新库or选用其它数据库，默认隔离级别也应该使用RR。这个思路一定正确吗？

答案是：不一定。
- 很多大厂建议的隔离级别是RC（读已提交），而不是RR（可重复读）阿里内部规范明确要求用RC级别（用的时候把binlog改为row就行）
  1. 有了row格式的保证，不存在主从不一致的情况
  2. 为了实现RR，底层会用间隙锁保护索引范围，但是这在高并发场景锁等待会很严重，死锁概率也很高
  3. RR隔离级别下，为了实现MVCC而维护的undolog，一旦遇到长事务，内存消耗会变大
  4. 通常，业务上的大多数场景，并不需要“同一事务内读到的数据不变”这个保证
    - 你可以在需要的场景，使用乐观锁或悲观锁来实现需求，而不是所有的场景都因为RR而加锁
- 其它的主流数据库，诸如SQL Server、Oracle、PG，默认隔离级别也是RC
为什么因为MySQL的默认隔离级别是RR？
- 在MySQL的旧版本中（5.1之前），主从同步所使用的binlog格式为Statement，记录的原始SQL语句。在RC隔离级别下，并发语句没有间隙锁保证，有可能在从库上重放的顺序不一致，导致主从数据不一致。
- 后续MySQL版本（5.1及以上），binlog默认格式改为了Row，记录的时行数据变更，不管事务提交顺序时啥样的，每一行最终的状态都是一致的，都能保证主从数据一致。但是MySQL为了向前兼容，一直保持默认隔离级别为RR。

这又是一个为了兼容历史特性/问题而做出的妥协式设计，但这显然不是最优解。

用第一性原理去分析这个问题：在做架构设计和技术选型时，需要用奥卡姆剃刀砍掉这种路径依赖，从现状和需求出发才能找到最合适刚刚好的方案

MySQL上类似的不符合直觉的设计还有很多

5.5版本前，如果一个查询有多个索引符合条件，MySQL不会选用最合适的索引，而是选择最早创建的那个

对于ENUM这一数据类型，选用不同的排序语法，结果是不一致的

举例：

1
2
3
4
5
6
7
CREATE TABLE table1(
    id INT,
    spec ENUM('S', 'M', 'L', 'XL')
)
-- 如果让你查最小号，稍不留神就会出错
SELECT spec FROM table1 ORDER BY spec LIMIT 1; -- 结果是：S
SELECT MIN(spec) FROM table1; -- 结果是 L

为什么？主打一个随意，脑筋急转弯级别的
- ORDER BY 语法里：排序根据ENUM的定义顺序来决定大小的
- MIN/MAX 语法里：会把ENUM当作字符串来处理

为什么很多规范里面都不建议使用外键以及触发器吗？这就牵扯到了著名的11472号bug
- 场景再现：给上述table1创建Trigger，用来监听这个表的改动。并且给id创建了外键
  - 当修改是主动发生的，如果table1数据发生修改，你会收到监听器的消息
  - 但如果修改是动发生的，比如外键对应其他表的数据被删除，导致table1某一行id变成null，那触发器是不会触发的
- 这就导致MySQL中的Trigger是否被触发是完全没有保障的
…

所以，八股文、文档、规范给你灌输的一些原理和准则，未必都代表了优秀的设计思想，有些只是想让你精通它们所构造出来的某种特性(甚至BUG[旺柴])

先看一个现象#

思维冲突点#

原因探查#

为什么会有这个设计？#

其它主流数据如何处理这种场景的？#

思考#

先看一个现象

思维冲突点

原因探查

为什么会有这个设计？

其它主流数据如何处理这种场景的？

思考