《關於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，則需要加倍小心查詢條件了。

獲取資料：

最後給大家分享一份學習資料，裡面包括：（BATJ面試資料、高可用、高併發、高效能及分散式、Jvm效能調優、Spring原始碼，MyBatis，Netty，Redis，Kafka，Mysql，Zookeeper，Tomcat，Docker，Dubbo，Nginx等多個知識點的架構資料）和Java進階學習路線圖。

免費領取加q群：478052716  即可！

分頁查詢最佳化

查詢的最佳化，最初是在研究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的官方文件中，其實現思路是一樣的，不存在效能差異，那麼自然是推薦更加標準的寫法了。