近日介入了一個(gè)互聯(lián)網(wǎng)產(chǎn)品項(xiàng)目,接觸了不少mysql數(shù)據(jù)庫架構(gòu)方面的工作,發(fā)現(xiàn)mysql存在不少“坑”(當(dāng)然也可能是我太年輕).下面就和大家展開港一港.

1. 沒有over語句

這個(gè)看起來是個(gè)小問題,可在實(shí)際應(yīng)用場(chǎng)景中確實(shí)帶來的不便.over語句主要和rank(),row_number()等一起配合使用.假如我有一個(gè)名為midterm_score的表存放一所學(xué)校某年級(jí)所有學(xué)生的期中考試成績(jī),有班級(jí)編號(hào)(class_code),學(xué)生名稱(student_name)和總分(score)共3個(gè)字段.如果我現(xiàn)在想對(duì)每個(gè)班級(jí)學(xué)生的總分進(jìn)行排名,我只必要執(zhí)行如下的sql語句：

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

這行sql代碼清晰明了,簡(jiǎn)單實(shí)用.然而,mysql并沒有over語句,那么同樣的功效要怎么實(shí)現(xiàn)呢?代碼如下：

SET @count=0;SET @mid='';SELECT a.*,b.rank FROM midterm AS a INNER JOIN (

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

2. 結(jié)合索引的最左匹配原則

索引其實(shí)就是對(duì)選定的一個(gè)或多個(gè)字段保留排序的結(jié)果,可以大大加快以這幾列作條件的查詢的速度.還是以上面這個(gè)表做例子,現(xiàn)在多加一個(gè)字段 subject_name 代表科目名稱,表的樣式如下：

如果我們對(duì)class_code,student_code和subject_name做索引,就能很快查詢出任何一個(gè)班級(jí),任何一個(gè)學(xué)生任何一門課的成就了.于是我們歡快地給這個(gè)表建了個(gè)三個(gè)字段組成的聯(lián)合索引,然后回憶起每個(gè)班的1號(hào)是種子選手,我們想看看他們的數(shù)學(xué)成就,寫了如下的sql:

ALTER TABLE midterm ADD KEY (class_code, student_code, subject_name);EXPLAIN SELECT * FROM midterm WHERE student_code = '1' AND subject_name = 'math';

然后發(fā)現(xiàn),我們建立的索引根本沒有施展作用.

這是怎么一回事兒呢,本來mysql中建立聯(lián)合索引,并不是對(duì)其字段的所有子集也建立了索引,而是遵從了最左匹配原則.這個(gè)例子里我們只相當(dāng)于建立了class_code的單獨(dú)索引,class_code和student_code 建立的聯(lián)合索引和由所有字段組成的聯(lián)合索引.因?yàn)?生成索引時(shí),是先對(duì)class_code排序,再對(duì)student_code排序,最后再對(duì)subject_name排序.如果單獨(dú)看第二第三列,結(jié)果就是無序的,查詢時(shí)自然不能提速了.假若你需要在這三個(gè)字段的任意組合都能實(shí)現(xiàn)索引,那么就要一共建立(class_code, student_code, subject_name),(student_code, subject_name),(subject_name)一共三個(gè)normal key.如果你對(duì)一張表的多個(gè)字段要建立索引,那么就需要需要添加n多個(gè)的normal key,十分麻煩.人家postgresql支持的聯(lián)合索引的子集就比mysql不知高到哪里去了.

3. 分區(qū)鍵必需是獨(dú)立鍵

分區(qū)是mysql里一個(gè)看上去挺實(shí)用的功能,能避免讓你手動(dòng)分表,加快體量很大的數(shù)據(jù)表的查詢速度.分區(qū)實(shí)質(zhì)是依照設(shè)定的分區(qū)鍵排序,然后劃分區(qū)域把一張表水平切分存儲(chǔ)在不同的物理區(qū)域,這樣查詢時(shí)只要查找那些鍵所在區(qū)域的分表就行,避免了大規(guī)模的全表掃描,而且表看上去并沒有被拆分.可是這個(gè)東西只是看起來很美,mysql里有個(gè)現(xiàn)值,所有用于分區(qū)的鍵(字段的組合)都必須包含于所有的獨(dú)立建(unique key)中,沒錯(cuò),是所有的獨(dú)立鍵里.那么問題來了,主鍵肯定是獨(dú)立鍵,那么分區(qū)鍵就必須是主鍵的真子集.然而目前大部分?jǐn)?shù)據(jù)表都不會(huì)把有實(shí)質(zhì)意義的業(yè)務(wù)字段作為主鍵,這就使得分區(qū)的業(yè)務(wù)意義大大降低了.上面的例子中,主鍵是自增長的id,可以視作記錄插入的時(shí)間順序,如果依照id分區(qū),在以class_code之類有實(shí)際意義的字段為條件做查詢時(shí),分區(qū)就派不上用場(chǎng)了.而如果對(duì)score之類不在主鍵中的字段做分區(qū),結(jié)果如下：

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.

作者：錢亦欣

起源：知乎