【原創】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 代表科目名稱,表的樣式如下(多餘的欄位後面會用到):
如果我們對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
真是欲哭無淚。。。
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
相關文章
- Python裡的那些坑Python
- iOS開發的那些坑iOS
- Go語言的那些坑Go
- Laravel裡的那些坑 - OptionalLaravel
- 移動端的那些坑
- 再見,BLE的那些坑!
- jcenter上傳的那些坑
- 繼承那些坑繼承
- Fragment中呼叫startActivityForResult的那些坑Fragment
- springcloud-config-client的那些坑SpringGCCloudclient
- React Native 打包apk的那些坑React NativeAPK
- 那些你需要注意的坑
- 【原創】MySQL 返回更新值(RETURNING)MySql
- Android加固和簽名的那些坑(防掉坑技巧)Android
- Java 中,Arrays 轉 List 的那些坑Java
- HTTP 規範中的那些暗坑HTTP
- Fragment中的那些坑——Android進階FragmentAndroid
- Python:那些年我們遇到的坑Python
- uniapp之那些年踩過的坑APP
- MYSQL中的那些鎖MySql
- 【原創】MySQL 模擬條件索引MySql索引
- 創業者楊採購在網際網路行業走的那些坑!ILF創業行業
- [原創]Swoole和Swoft的那些事(Task投遞/定時任務篇)
- 那些前端工作中遇到的坑(01)前端
- 解決input 中placeholder的那些神坑
- 那些年走過下劃線的坑
- 那些jdk中坑你沒商量的方法JDK
- 異常處理遇到過的那些坑
- 使用vue匯出excel遇到的那些坑VueExcel
- 避坑攻略:細數買雲伺服器的那些坑,如何避免?伺服器
- 程式設計師技術入股的那些坑程式設計師
- 微信小程式開發遇到的那些坑微信小程式
- Xcode10更新的那些坑(持續更新)XCode
- 當年用httpclient時踩過的那些坑HTTPclient
- PageHelper 分頁外掛使用中的那些“坑”
- IT人,那些年,一起踩過的坑
- 那些年走過ArrayList迴圈remove的坑REM
- 微軟外服札記④——Spark中的那些坑...微軟Spark
- 【原創】Mysql中事務ACID實現原理MySql