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 (

不知看你能不克不及看懂,反正我看不懂...

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

真是欲哭无泪...

End.

作者：钱亦欣

起源：知乎