作者 钱亦欣

键人近日参与了一个互联网产品项目,接触了不少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 代表科目名称,表的样式如下(多余的字段后面会用到):

enter image description here

如果我们对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';

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

enter image description here

这是怎么一回事儿呢,原来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