那些年，我掉过的MYSQL 的那些“坑”

来源：知乎专栏

数据应用学院 授权转载

发现MySQL存在不少“坑”

（当然也可能是我太年轻……）

下面就和大家展开港一港

over语句主要和rank()，row_number()等一起配合使用。

假如我有一个名为midterm_score的表

存放一所学校某年级所有学生的期中考试成绩，

有班级编号（class_code)，学生名称（student_name)和总分（score）共3个字段。

如果我现在想对每个班级学生的总分进行排名，

我只需要执行如下的SQL语句：

那么同样的功能要怎么实现呢？

代码如下：

现在多加一个字段 subject_name 代表科目名称，

表的样式如下：

就能很快查询出任何一个班级，

任何一个学生任何一门课的成绩了。

于是我们欢快地给这个表建了个三个字段组成的联合索引，

然后回忆起每个班的1号是种子选手，

我们想看看他们的数学成绩，

写了如下的SQL:

而是遵从了最左匹配原则。

这个例子里我们只相当于建立了

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不知高到哪里去了。

加快体量很大的数据表的查询速度。

分区实质是按照设定的分区键排序，

然后划分区域把一张表水平切分存储在不同的物理区域，

这样查询时只要查找那些键所在区域的分表就行，

避免了大规模的全表扫描，

而且表看上去并没有被拆分。

可是这个东西只是看起来很美，

MySQL里有个现值，

所有用于分区的键(字段的组合)都必须包含于所有的独立建(unique key)中，

没错，

是所有的独立键里。

那么问题来了，

主键肯定是独立键，

那么分区键就必须是主键的真子集。

然而目前大部分数据表都不会把有实质意义的业务字段作为主键，

这就使得分区的业务意义大大降低了。

上面的例子中，

主键是自增长的id，

可以视作记录插入的时间顺序，

如果按照id分区，

在以class_code之类有实际意义的字段为条件做查询时，分区就派不上用场了。

而如果对score之类不在主键中的字段做分区，结果如下：

比如我们的midterm表由于录入不当心或是跑了错误的select代码使得记录重复了，

这个表中class_code，student_code和subject_name三个字段可以构成一个唯一键，

要保留不重复的记录，

只需要执行

是不是很棒棒？

然而ignore子句在5.6版本就不被推荐使用，

5.7就直接把它移除了。

官方给出的理由是无法正确定义第一条记录，

而且这个操作在有外键的情况下会影响其他表。

然而，很多时候重复记录都是一毛一样的，

物理外键现在也不怎么应用了，都用的逻辑外链。

现在要实现上面的去重，

就要create一个结构一样，

但包含唯一键的表midterm_copy，

然后把midterm表的数据复制过去

(insert 还是支持ignore的)，

然后删除midterm

并把midterm_copy重命名为midterm。

流程复杂不少。

然而在使用上这三点确实带来了很多不便。

希望在这方面有研究的前辈同仁可以一起讨论如何应对这些问题。