關於MySQL的一些騷操作——提升正確性，摳點性能

概要

回顧以前寫的項目，發現在規範的時候，還是可以做點騷操作的。
假使以後還有新的項目用到了MySQL，那麼肯定是要實踐一番的。
為了準備，創建測試數據表（建表語句中預設使用utf8mb4以及utf8mb4_unicode_ci，感興趣的讀者可以自行搜索這兩個配置）：

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '編號',
  `name` varchar(30) NOT NULL COMMENT '名稱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入衝突時更新數據

SQL執行插入時，可能因為種種原因插入失敗，比如UNIQUE索引衝突導致插入失敗。比如某個不曉得DBA插入了一條錯誤的學生記錄("3", "小明")，悲劇的是小明的編號是1。常規做法就是判斷當前的資料庫記錄中是否存在小明的記錄，如果有則更新其對應其編號，否則就插入小明的記錄。當然存在更好的做法：

INSERT INTO student(`no`, `name`) VALUES (3, "xiaoming");
INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming"), (2,"xiaohong")
ON DUPLICATE KEY UPDATE `no` = VALUES(`no`);

那就是使用ON DUPLICATE KEY UPDATE，這是mysql獨特的語法（語句後面可以放置多個更新條件，每個條件使用逗號隔開即可）。需要註意，這裡的VALUES(no)是將衝突的no數值更新為用戶插入數據中的no，這樣每條衝突的數據就可以動態的設置新的數值。

忽略批量插入失敗中的錯誤

批量插入比單條數據挨個插入，普遍會提高性能以及減少總的網路開銷。但是，假如批量插入的數據中心存在一個臭蟲，在預設的情況下，這就會導致批量插入失敗（沒有一條數據插入成功）。當然，我們可以選擇忽略，MongoDB都能夠做到的事情，MySQL自然是可以做到。

INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming");
INSERT IGNORE INTO student(`no`, `name`) VALUES (1, "xiaoming"), (2,"xiaohong"),(3, "xiaowang");

只需要在批量插入的語句中，插入IGNORE，那麼某幾條數據的插入失敗就會被忽略掉，正確的數據依然可以插入庫中。但是，我建議這個功能謹慎使用，使用mysql資料庫本身就是看中數據的正確性，沒必要為了批量插入的性能而自動放棄數據的正確性，如果真心覺得這個數據不重要，那麼為什麼不將此數據存入NoSQL中呢，MongoDB就是不錯的選擇。

IGNORE還有些副作用，感興趣的可以自行查詢。

使用JOIN替換子查詢

MySQL的子查詢優化不是太好，它的運行有點反我們的直覺（我們寫的代碼終究會在某些時候和我們的直覺相悖，這大概就是優化產生的根源之一吧）。其中最糟糕的一類是WHERE子句中包含IN的子查詢語句（詳情可見《高性能MySQL》一書的6.5章節,標題名字起得就很nice，為MySQL查詢優化器的局限性）。概括下就是在部分情況下，在部分情況下MySQL可能會在挨個執行外部記錄時執行子查詢，如果外部記錄數量較大，那麼性能就會堪憂。

SELECT * FROM student WHERE no > (SELECT no FROM student WHERE `name`='xiaoming');
SELECT s.* FROM student s JOIN (SELECT no FROM student WHERE `name`='xiaoming') t ON s.no > t.no;

看上述代碼，可以知道使用JOIN還是比較容易替換子迴圈，代碼雖然會稍顯晦澀，但是也許可以避免在併發量大的某個晚上你被叫起來檢討自己的錯誤。MySQL一直在優化子查詢，在部分條件下子查詢可能會比JOIN具有更高的效率，因此在有時間進行驗證的情況下選擇最佳的SQL語句。

JOIN中的WHERE和AND坑

為了更好的說明坑，我這裡需要創建一個新的表，併在原來的學生表中添加欄位:

CREATE TABLE `class` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(10) unsigned NOT NULL COMMENT '編號',
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名稱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `student`
  ADD COLUMN `cls_no` smallint(6) unsigned NOT NULL DEFAULT 0 AFTER `no`;

偽造一些數據，假設有4個班級，4班沒有相對應的學生。使用如下的查詢語句就能發現不同之處：

select c.*, s.`name` from class c left join student s on c.no = s.cls_no and c.no < 4 order by c.no asc;

查詢結果如下圖所示：

需要註意的是，此處我再查詢條件中設置了c.no < 4這一JOIN條件，但是明顯的沒有起到作用，查詢結果中仍然顯示了no=4的結果，這是因為此次查詢使用的JOIN是LEFT JOIN，class作為左表，在匹配條件無法完全滿足的情況下，亦會將左表的所有數據顯示出來，引入了NULL值。
換成使用WHERE呢，參照下句：

select c.*, s.`name` from class c left join student s on c.no = s.cls_no where c.no < 4 order by c.no asc;

查詢結果如下圖所示：

為什麼同樣是使用LEFT JOIN，查詢結果就不同了呢？這是因為可以認為SQL是分成兩部分進行執行的(偽SQL，意思到位)：

(1) select c., s.name from class c left join student s on c.no = s.cls_no as tmn;
(2)select c., s.name from tmp where c.no < 4 order by c.no asc;

需要註意的是，此處首先執行JOIN部分查詢，再對查詢結果執行WHERE。在執行INNER JOIN時，以上問題還可以忽略，但是如果使用的是LEFT JOIN或者RIGHT JOIN，則需要加倍小心查詢條件了。

分頁查詢優化

查詢的優化，最初是在研究MongoDB的分頁查詢時學到的，只能說大多數的資料庫都是差不多的(當然現在存在時序資料庫，分頁查詢那是更加騷氣的)。大多數的分頁查詢都是類似如下的寫法：

SELECT * FROM student WHERE cls_no > 1 LIMIT 1000, 10 ORDER BY id;

這樣的寫法存在性能損耗，資料庫會將所有符合條件的數據查詢出來，挨個數到第1000條記錄，最後選取前10條記錄進行交差。前面的1000條數據，就會顯得很浪費，在LIMIT數值很大的情況下，這個性能損耗就是無法忍受的了（百度就會預設禁止查詢76頁以後的數據）。
因為分頁一般是逐頁翻下去的(如果是跳頁進行查詢，那就只能用上面的查詢語句慢慢查詢搜尋結果了)，那麼每次分頁完都能獲取當前的最大ID，我們可以基於ID確定我們的搜索起始點，基於此點向後查詢10條滿足要求的結果，改動如下(讓前端多傳一個當前頁的最大ID，這個小小的要求當然是可以滿足的)：

SELECT * FROM student WHERE id > 1000 AND cls_no > 1 LIMIT 10 ORDER BY id;

以上是基於當前的ID是連續ID（其中若幹記錄沒有被物理刪除掉），如果是非連續ID，那麼基於ID確定起始查詢點是不恰當的，此時我們就可以使用JOIN：

SELECT s.* FROM student s JOIN (SELECT id FROM student LIMIT 1000, 10) t ON s.id = t.id;

其實，此處我們是id的索引表，從而快速的確定ID，因此查詢簡化成根據索引表查詢的ID確定數據記錄（不過需要註意，此處的索引表是無法添加WHERE子句的），因此這種寫法在實際環境中幾乎是個雞肋。

UPDATE/DELETE改動多個表記錄

工作中，經常需要修改多個表中的關聯記錄。一般的做法是將相關表中的記錄查詢出來，再挨個進行修改。如果修改的邏輯較為複雜，那麼這樣做是沒有問題的，但是若是只是簡單的修改（比如修改boolean變數），那麼可以通過一條SQL語句完成此任務。
SQL中只要提及多個表，那麼大致上就會出現JOIN的身影。我們有個需求，就是將3班的學生轉移到5班（原有的3班更改為5班）,使用JOIN語句的話就可以按照如下方式完成任務。

UPDATE student s JOIN class c ON c.no =3 AND c.no = s.cls_no SET c.no = 5, s.cls_no = 5; 

通過JOIN既可以完成此任務，可以拓展到修改多個表中數據內容，也可以擴展至DELETE語句中。

SELECT COUNT(*)/COUNT(1)/COUNT(列名)掉書袋

此處，就簡單的總結一下：

• SELECT COUNT(*)：是SQL 92中定義的標準統計行數的語法(所以肯定是做了很多優化的)；
• SELECT COUNT(1): 查詢符合條件的行數;
• SLECT COUNT(列名): 查詢符合條件的，且指定的列名所對應值非NULL行數。

對於SELECT COUNT(*)/COUNT(1)，在MySQL的官方文檔中，其實現思路是一樣的，不存在性能差異，那麼自然是推薦更加標準的寫法了。

PS:
如果您覺得我的文章對您有幫助，請關註我的微信公眾號，謝謝!