令人崩溃的选择：MySql

淡淡的烟草味﹌ 2022-06-13 20:16 245阅读 0赞

最近想了解PostgreSQL，查阅资料时发现最长用来与之比较的是Mysql，于是看到了这篇文章：

[https://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/][https_blog.ionelmc.ro_2014_12_28_terrible-choices-mysql]

看了一点觉得总结的不错，想用拙劣的英语水平尝试翻译一下，细数我们走过的Mysql的坑。

文中有很多Django的术语，我没用过这个框架，所以不太了解，有些看不懂的直接百度翻译了，可能翻译的很不专业，请见谅。

译文：

我用Mysql已经有较长一段时间了，这个数据库有太多需要遵从的"神奇"的规则。本文从使用Django框架+MySQL5.5的视角，向你展示我工作中遇到的糟糕的事情，这是一种可怕的经历。同时我也在使用PostgreSQL。

也欢迎你们在评论中分享你们自己的经历。

**【默认配置】**

MySQL在很大程度上支持ANSI SQL 99标准，但是它的默认配置根本不符合这个标准。

如果你用过任意一个其他的数据库，就会知道这是一个多么令人费解的事情。

**SQL模式**

为了不向用户报出错误，MySQL的默认配置会转换数据，或者静悄悄的对数据做一些处理。很难说这到底是不是一个正确的决定。

默认配置下允许的事情：

*  存储非法的日期例如：'0000-00-00' 或者 '2010-01-00'
 *  后台默默的处理错误数据，例如：

① 如果你指定的存储引擎是不可用的，它会帮你转成默认的存储引擎，默默地，没有任何提示的。。。

② 插入非法的数据。过长的字符串自动截取为字段允许的最大长度。过大的Integer值也被转为字段允许的最大值。如果这个字段允许NULL值的话，其他的非法值会被转成NULL。而这一切都是自动执行的。

ORM框架的默认配置不会将你从这种痛苦中解救出来。在使用Django的时候，你需要在配置文件中添加如下内容才行：

![Center][]

There's an open Django [ticket to set this the default][].

也可以在MySQL中进行配置，但是我劝你不要这样做。因为如果你在部署应用时忘记修改服务器上的/etc/mysql/my.cnf，你的应用就开始胡作非为了，产生很多被损坏的数据。如果这种事情真的发生了，当你意识到有啥地方不对劲的时候，大批坏数据已经产生了。

**排序规则和字符编码**

我一直以来都手动指定字符集为utf-8，因为你永远不知道默认的字符集是啥(貌似是latin1)。但是，请注意，字符集会影响VARCHAR列的最大长度，想知道你为什么经常看到这个吗：

![Center 1][]

是的，在utf-8字符集下，VARCHAR的最大长度被缩小为255个字符。此外，如果你想存储类似于![Center 2][]的表情符号的话，就会陷入困境，不幸的是你必须更换字符集为：utf8mb4，这意味着更小的字段长度：191。

（译者插曲。。不知道CSDN用的是不是mysql。。。之前那个愤怒的表情我直接从原网站粘贴过来的，这么长的文章，我写的途中也时不时保存草稿，但是发布之后，正好到表情这里就没有了，草稿也没了，我真的是崩溃，写了那么多都没了）

不过这还不算很糟糕，至少当你指定错误的字符集时会得到错误提示，相比之下排序规则就更令人迷醉了，排序规则是用来比较字符集中的字符的。

看看下面的内容，注意到 \_ci 后缀了没？

![Center 3][]

MySQL所有的默认排序规则都是大小写不敏感的，这意味着啥呢？你的所有查询和索引都是不区分大小写的，我得说，这个数据库对我们的痛点太不敏感了。

如果你使用大小写不敏感的排序规则，所有非文本的查询结果都会很奇怪。当你使用方言或处理一些特殊情况时，get\_or\_create的结果就不如你所愿。

解决方案是使用utf\_bin排序规则（除非你需要特别方言处理或是案例折叠），像这样配置：

![Center 4][]

不幸的是我一开始并不知道这些，所以必须修复现有数据和安装脚本。当你创建数据库时，请确保手动指定了字符编码和排序规则：

![Center 5][]

为了修复现有数据，我们使用south进行数据迁移：

![Center 6][]

新的Django1.7这样迁移：

![Center 7][]

注意，数据迁移会花费很长时间（不知道为啥MySQL似乎拷贝了所有的表），会改变所有字段的字符集和排序规则。

**事务隔离级别**

你以为正确配置了字符集和排序规则就万事大吉了吗？并不。MySQL默认的事务隔离级别是REPEATABLE READ，这说明，在一个事务中，你查到了一些数据，过一会你再进行相同的查询，返回的还是这些数据，即使这些数据再其他的事务中被修改并提交了。换句话说，REPEATABLE READ使你的get\_or\_create在事务中坏掉了，它使得事务中的代码“看不见”事务之外创建的对象。

可以像这样配置隔离级别为READ COMMITED：

![Center 8][]

**DDL语句**

MySQL中的DDL语句不仅慢，而且会锁表(意味着停机)，它们会忽略事务。当你在事务中使用ALTER语句时，万能的InnoDB引擎不会像你一样理智。因此，MySQL的数据迁移必须非常小心，你还得使用特定外部工具以防停机。

下面的提示非常让人懊恼，即使是在开发中遇到：

![Center 9][]

如果这和1.7版本中的迁移系统一样（看起来是的，但你只能得到简单的回溯），并且应用中有一些容易运行失败的定制SQL，那你最好进行小范围的数据迁移，并且在迁移成功后进行压缩。

**【稳定性】**

在遇到带JOIN或GROUP BY的语句时，MySQL的查询优化总是倾向于使用临时表，这和Django很类似，至于Why and How是一个很大的话题，也许后面我们会说到......（译者：并没有）

现在，我们来说一个悲伤的故事。

我有一个这样的model类：

![Center 10][]

由于某些原因我必须使用这个model，我们要探讨的问题不是这个model为什么这样设计或是为什么我要继续使用它。现在，看看它有什么问题吧。

你知道的，在允许NULL值的列上建立索引是非常糟糕的，因为索引不包含NULL值。数据库不会允许你插入两次"foobar"，但是允许你插入很多NULL值。举个栗子，下面这种行为是允许的：

![Center 11][]

只要你创造条件，get\_or\_create就会插入两条一毛一样的数据，因为数据库没有say no。

这在MySQL中非常的常见，一般遇到这种情况，你会重新设计model，MySQL不应该被指责，对不？但是，唉，你看到了，我正在使用ORM框架，而且我很喜欢外键的便捷性，不要指责我的软弱因为我就像个沙漠中口渴的傻瓜。

以下选项我都无法选择：

1.保留约束，将索引中的parent设置为计算列，但是MySQL没有计算列这个东东。

2.使用条件约束，但是MySQL没有条件约束这个东东。

3.创建视图，在视图上添加索引，但是MySQL不允许在视图上添加索引。

再来一个重击，这些功能PostgreSQL都有。

尝试在客户端寻求解决方案是无果的。应用的高并发只能数据库层被处理，我坚持了下来并想出一个办法：触发器

在各种疯狂的尝试中，我最终选择了这种：

![Center 12][]

这种方式并没有解决问题，只是停止了INSERT，get\_or\_create必须处理一种新的异常类型：DatabaseError。因此model需要一个自定义管理器：

![Center 13][]

如果你仔细观察这个触发器就知道它并不能真正做到唯一索引的限制，没有原子性，我已经知道它在压力测试中不会起作用。

于是我回到书桌前，又想出了一个新点子：创建一个带有唯一索引的影子表，以及一些在原表被修改前更新影子表的触发器。

我删除了原表上的唯一索引，并创建了这个：

![Center 14][]

以及这些用来同步影子表数据的触发器：

![Center 15][]

现在你知道了，在MySQL中要解决这个问题是多么棘手。但在支持条件索引的PostgreSQL中一切是这么简单：

![Center 16][]

**【后记...】**

如果我打从一开始就知道这些，也许我的编程之路会变成“简单模式”。这条路我走的越远，我就越来越无法停止思考“如果我一开始用的就是PostgreSQL”。当你对每一个新手程序猿解释MySQL的这些大坑时，“我做了多么糟糕的一个决定”在你脑中挥之不去。某种程度上这是技术负债吧，为了正确使用MySQL，你必须付出这些认知成本。

[https_blog.ionelmc.ro_2014_12_28_terrible-choices-mysql]: https://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/
[Center]: /images/20220614/35fdfd9a850246c2b5a85fc85eb91cf7.png
[ticket to set this the default]: https://code.djangoproject.com/ticket/15940
[Center 1]: /images/20220614/6a432a7685e849168b226176e46295be.png
[Center 2]: /images/20220614/ed9ba41b02e648b0b176fb6d2c48a52e.png
[Center 3]: /images/20220614/bf32cc44e42e404a96a31fd8838e672d.png
[Center 4]: /images/20220614/c5dd4f61fa6042eb9ed12d4458f4ba19.png
[Center 5]: /images/20220614/0116bcf73c604422ba272f58f008d754.png
[Center 6]: /images/20220614/6077ac7588fa43a4b6bebee5f1f8aba2.png
[Center 7]: /images/20220614/480dbd286d2f4e4280d2ec908ed4c12e.png
[Center 8]: /images/20220614/195f873927e5400c8b966fde5aab1144.png
[Center 9]: /images/20220614/ecb2ee0963d84e4387ef251ead7a031c.png
[Center 10]: /images/20220614/1af9d800e629497196faa03b46600bb5.png
[Center 11]: /images/20220614/34760ccd6dde429eb9d2b94ac66dfbaf.png
[Center 12]: /images/20220614/a2efe2f7db484610b277eb22e3128d7f.png
[Center 13]: /images/20220614/81bd24285173440593dfd898aafaf8e4.png
[Center 14]: /images/20220614/0d1486e9045547fa8644b150cca43205.png
[Center 15]: /images/20220614/c7bc38d7290246d48a5c37c1278e89c8.png
[Center 16]: /images/20220614/195c3e80f0a84feca7f9dddefdefb01e.png