Py学习  »  DATABASE

MYSQL 的那些“坑”

中国统计网 • 6 年前 • 681 次点击  


近日参与了一个互联网产品项目,接触了不少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

真是欲哭无泪。。。


End.

作者:钱亦欣

来源:https://zhuanlan.zhihu.com/p/28645383


今天看啥 - 高品质阅读平台
本文地址:http://www.jintiankansha.me/t/4bkwRBUwz2
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/3138
 
681 次点击