【原創】MYSQL 的那些“坑”

錢亦欣發表於2017-08-20
原文網址 : http://www.ituring.com.cn/article/472244
MySql

作者 錢亦欣

鍵人近日參與了一個網際網路產品專案，接觸了不少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 代表科目名稱，表的樣式如下(多餘的欄位後面會用到)：

enter image description here

如果我們對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';

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

enter image description here

這是怎麼一回事兒呢，原來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

真是欲哭無淚。。。

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);

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

CREATE TABLE `midterm_copy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_code` varchar(255) DEFAULT NULL,
  `student_code` varchar(255) DEFAULT NULL,
  `subject_code` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`class_code`, `student_code`, `subject_name`)
) ENGINE=InnoDB;

INSERT IGNORE　INTO midterm_copy SELECT * FROM mideterm;
DROP TABLE midterm;
ALTER TABLE midterm_copy RENAME midterm;

結語

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

相關文章