mysql的坑_【原创】MYSQL 的那些“坑”

作者 钱亦欣

键人近日参与了一个互联网产品项目，接触了不少mysql数据库架构方面的工作，发现mysql存在不少“坑”(当然也可能是我太年轻)。下面就和大家展开港一港。

1. 没有over语句

这个看起来是个小问题，可在实际应用场景中确实带来的不便。over语句主要和rank()，row_number()等一起配合使用。加入我有一个名为midterm_score的表存放一所学校某年级所有学生的期中考试成绩，有班级编号(class_code)，学生名称(student_name)和总分(score)共3个字段。如果我现在想对每个班级学生的总分进行排名，我只需要执行如下的sql语句：

SELECT class_code, student_name, score rank() OVER (PARTITION BY class_code ORDER BY score)

这行sql代码清晰明了，简单实用。然而，mysql并没有over语句，那么同样的功能要怎么实现呢？代码如下：

SET @count=0;

SET @mid='';

SELECT a.*,b.rank FROM midterm AS a

INNER JOIN (

SELECT class_code, score, CASE WHEN @mid = class_code THEN @count:=@count+1 ELSE @count:=1 END AS rank, @mid:=class_code AS MID

FROM midterm

ORDER BY class_code, score DESC

) AS b ON b.class_code=a.class_code AND b.score=a.score

;

不知看你能不能看懂，反正我看不懂。。。

2. 联合索引的最左匹配原则

索引其实就是对选定的一个或多个字段保存排序的结果，可以大大加快以这几列作条件的查询的速度。还是以上面这个表做例子，现在多加一个字段 subject_name 代表科目名称，表的样式如下(多余的字段后面会用到)：

如果我们对class_code，student_code和subject_name做索引，就能很快查询出任何一个班级，任何一个学生任何一门课的成绩了。于是我们欢快地给这个表建了个三个字段组成的联合索引，然后回忆起每个班的1号是种子选手，我们想看看他们的数学成绩，写了如下的sql:

ALTER TABLE midterm ADD KEY (class_code, student_code, subject_name);

EXPLAIN SELECT * FROM midterm WHERE student_code = '1' AND subject_name = 'math';

然后发现，我们建立的索引根本没有发挥作用。

这是怎么一回事儿呢，原来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之类不在主键中的字段做分区，结果如下：

ALTER TABLE `midterm` PARTITION BY HASH(score)

PARTITIONS 2

;

[Err] 1659 - Field 'score' is of a not allowed type for this type of partitioning

真是欲哭无泪。。。

4. 没有IGNORE子句

ignore 子句是mysql对标准sql语句的一个拓展，常用在数据表的去重上。比如我们的midterm表由于录入不当心或是跑了错误的select代码使得记录重复了，这个表中class_code，student_code和subject_name三个字段可以构成一个唯一键，要保留不重复的记录，只需要执行

ALTER IGNORE TABLE mideter ADD UNIQUE (class_code, student_code, subject_name);

这个调整会插入一个独立键，只保留有重复记录的第一条记录。是不是很棒棒？然而ignore子句在5.6版本就不被推荐使用，5.7就直接把它移除了。官方给出的理由是无法正确定义第一条记录，而且这个操作在有外键的情况下会影响其他表。然而，很多时候重复记录都是一毛一样的，物理外键现在也不怎么应用了，都用的逻辑外链。现在要实现上面的去重，就要create一个结构一样，但包含唯一键的表midterm_copy，然后把midterm表的数据复制过去(insert 还是支持ignore的)，然后删除midterm并把midterm_copy重命名为midterm。流程复杂不少。

CREATE TABLE `midterm_copy` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`class_code` varchar(255) DEFAULT NULL,

`student_code` varchar(255) DEFAULT NULL,

`subject_code` varchar(255) CHARACTER SET utf8mb4 NOT NULL,

`score` float DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY (`class_code`, `student_code`, `subject_name`)

) ENGINE=InnoDB;

INSERT IGNORE　INTO midterm_copy SELECT * FROM mideterm;

DROP TABLE midterm;

ALTER TABLE midterm_copy RENAME midterm;

结语

吐槽了mysql的4个"坑"，当然我也知道这些其实都是设计上的一些考虑，然而在使用上这三点确实带来了很多不便。希望在这方面有研究的前辈同仁可以一起讨论如何应对这些问题。欢迎添加我的微信：ishii-masato