作者：钱亦欣
键人近日参与了一个互联网产品项目
接触了不少MySQL数据库架构方面的工作
发现MySQL存在不少“坑”
(当然也可能是我太年轻……)
下面就和大家展开港一港
· 1 ·
没有OVER语句
这个看起来是个小问题，
可在实际应用场景中确实带来的不便。
over语句主要和rank()，row_number()等一起配合使用。
假如我有一个名为midterm_score的表
存放一所学校某年级所有学生的期中考试成绩，
有班级编号(class_code)，学生名称(student_name)和总分(score)共3个字段。
如果我现在想对每个班级学生的总分进行排名，
我只需要执行如下的SQL语句：
这行SQL代码清晰明了，简单实用。
然而，MySQL并没有over语句，
那么同样的功能要怎么实现呢？
代码如下：
不知道你能不能看懂，反正我看不懂。。。
· 2 ·
联合索引的最左匹配原则
索引其实就是对选定的一个或多个字段保存排序的结果，可以大大加快以这几列作条件的查询的速度。
还是以上面这个表做例子，
现在多加一个字段 subject_name 代表科目名称，
表的样式如下：
如果我们对class_code，
student_code和subject_name做索引，
就能很快查询出任何一个班级，
任何一个学生任何一门课的成绩了。
于是我们欢快地给这个表建了个三个字段组成的联合索引，
然后回忆起每个班的1号是种子选手，
我们想看看他们的数学成绩，
写了如下的SQL:
然后发现，
我们建立的索引根本没有发挥作用。
这是怎么一回事儿呢？
原来MySQL中建立联合索引，
并不是对其字段的所有子集也建立了索引，
而是遵从了最左匹配原则。
这个例子里我们只相当于建立了
class_code的单独索引，
class_code和student_code 建立的联合索引
和由所有字段组成的联合索引。
因为，生成索引时，是先对class_code排序，
再对student_code排序，
最后再对subject_name排序。
如果单独看第二第三列，
结果就是无序的，
查询时自然不能提速了。
假若你需要在这三个字段的任意组合都能实现索引，
那么就要一共建立
(class_code, student_code, subject_name),
(student_code, subject_name),
(subject_name)
三个normal key。
如果你对一张表的多个字段要建立索引，
那么就需要需要添加n多个的normal key，十分麻烦。
人家postgreSQL支持的联合索引的子集就比MySQL不知高到哪里去了。
· 3 ·
分区键必须是唯一键
分区是MySQL里一个看上去挺实用的功能，
能避免让你手动分表，
加快体量很大的数据表的查询速度。
分区实质是按照设定的分区键排序，
然后划分区域把一张表水平切分存储在不同的物理区域，
这样查询时只要查找那些键所在区域的分表就行，
避免了大规模的全表扫描，
而且表看上去并没有被拆分。
可是这个东西只是看起来很美，
MySQL里有个现值，
所有用于分区的键(字段的组合)都必须包含于所有的独立建(unique key)中，
没错，
是所有的独立键里。
那么问题来了，
主键肯定是独立键，
那么分区键就必须是主键的真子集。
然而目前大部分数据表都不会把有实质意义的业务字段作为主键，
这就使得分区的业务意义大大降低了。
上面的例子中，
主键是自增长的id，
可以视作记录插入的时间顺序，
如果按照id分区，
在以class_code之类有实际意义的字段为条件做查询时，分区就派不上用场了。
而如果对score之类不在主键中的字段做分区，结果如下：
。。。
· 4 ·
没有IGNORE子句
IGNORE子句是MySQL对标准SQL语句的一个拓展，
常用在数据表的去重上。
比如我们的midterm表由于录入不当心或是跑了错误的select代码使得记录重复了，
这个表中class_code，student_code和subject_name三个字段可以构成一个唯一键，
要保留不重复的记录，
只需要执行
这个调整会插入一个独立键，
只保留有重复记录的第一条记录。
是不是很棒棒？
然而ignore子句在5.6版本就不被推荐使用，
5.7就直接把它移除了。
官方给出的理由是无法正确定义第一条记录，
而且这个操作在有外键的情况下会影响其他表。
然而，很多时候重复记录都是一毛一样的，
物理外键现在也不怎么应用了，都用的逻辑外链。
现在要实现上面的去重，
就要create一个结构一样，
但包含唯一键的表midterm_copy，
然后把midterm表的数据复制过去
(insert 还是支持ignore的)，
然后删除midterm
并把midterm_copy重命名为midterm。
流程复杂不少。
吐槽了mysql的4个"坑"，
当然我也知道这些其实都是设计上的一些考虑，
然而在使用上这三点确实带来了很多不便。
希望在这方面有研究的前辈同仁可以一起讨论如何应对这些问题。
