【原創】MYSQL 的那些「坑」

01-29

作者錢亦欣

鍵人近日參與了一個互聯網產品項目，接觸了不少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)n

這行sql代碼清晰明了，簡單實用。然而，mysql並沒有over語句，那麼同樣的功能要怎麼實現呢？代碼如下：

SET @count=0;nSET @mid=;nSELECT a.*,b.rank FROM midterm AS a nINNER JOIN (n SELECT class_code, score, CASE WHEN @mid = class_code THEN @count:=@count+1 ELSE @count:=1 END AS rank, @mid:=class_code AS MID n FROM midterm n ORDER BY class_code, score DESCn) AS b ON b.class_code=a.class_code AND b.score=a.scoren; n

不知看你能不能看懂，反正我看不懂。。。

2. 聯合索引的最左匹配原則

索引其實就是對選定的一個或多個欄位保存排序的結果，可以大大加快以這幾列作條件的查詢的速度。還是以上面這個表做例子，現在多加一個欄位 subject_name 代表科目名稱，表的樣式如下：

如果我們對class_code，student_code和subject_name做索引，就能很快查詢出任何一個班級，任何一個學生任何一門課的成績了。於是我們歡快地給這個表建了個三個欄位組成的聯合索引，然後回憶起每個班的1號是種子選手，我們想看看他們的數學成績，寫了如下的sql:

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

然後發現，我們建立的索引根本沒有發揮作用。

這是怎麼一回事兒呢，原來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)nPARTITIONS 2n;nn[Err] 1659 - Field score is of a not allowed type for this type of partitioningn

真是欲哭無淚。。。

4. 沒有IGNORE子句

ignore 子句是mysql對標準sql語句的一個拓展，常用在數據表的去重上。比如我們的midterm表由於錄入不當心或是跑了錯誤的select代碼使得記錄重複了，這個表中class_code，student_code和subject_name三個欄位可以構成一個唯一鍵，要保留不重複的記錄，只需要執行

ALTER IGNORE TABLE mideter ADD UNIQUE (class_code, student_code, subject_name);n

這個調整會插入一個獨立鍵，只保留有重複記錄的第一條記錄。是不是很棒棒？然而ignore子句在5.6版本就不被推薦使用，5.7就直接把它移除了。官方給出的理由是無法正確定義第一條記錄，而且這個操作在有外鍵的情況下會影響其他表。然而，很多時候重複記錄都是一毛一樣的，物理外鍵現在也不怎麼應用了，都用的邏輯外鏈。現在要實現上面的去重，就要create一個結構一樣，但包含唯一鍵的表midterm_copy，然後把midterm表的數據複製過去(insert 還是支持ignore的)，然後刪除midterm並把midterm_copy重命名為midterm。流程複雜不少。

CREATE TABLE `midterm_copy` (n `id` int(11) NOT NULL AUTO_INCREMENT,n `class_code` varchar(255) DEFAULT NULL,n `student_code` varchar(255) DEFAULT NULL,n `subject_code` varchar(255) CHARACTER SET utf8mb4 NOT NULL,n `score` float DEFAULT NULL,n PRIMARY KEY (`id`),n UNIQUE KEY (`class_code`, `student_code`, `subject_name`)n ) ENGINE=InnoDB;nn INSERT IGNORE　INTO midterm_copy SELECT * FROM mideterm;n DROP TABLE midterm;n ALTER TABLE midterm_copy RENAME midterm;n

結語

吐槽了mysql的4個"坑"，當然我也知道這些其實都是設計上的一些考慮，然而在使用上這三點確實帶來了很多不便。希望在這方面有研究的前輩同仁可以一起討論如何應對這些問題。歡迎添加我的微信：ishii-masato