近日介入了一個互聯網產品項目,接觸了不少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.

作者：錢亦欣

起源：知乎