近日参与了一个互联网产品项目，接触了不少mysql数据库架构方面的工作，发现mysql存在不少“坑”（当然也可能是我太年轻）。下面就和大家展开港一港。
这个看起来是个小问题，可在实际应用场景中确实带来的不便。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;
不知看你能不能看懂，反正我看不懂。。。
索引其实就是对选定的一个或多个字段保存排序的结果，可以大大加快以这几列作条件的查询的速度。还是以上面这个表做例子，现在多加一个字段 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不知高到哪里去了。
分区是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
