概要
回顧以前寫的專案，發現在規范的時候，還是可以做點騷操作的，
假使以后還有新的專案用到了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:
如果您覺得我的文章對您有幫助，請關注我的微信公眾號，謝謝!
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/114670.html
標籤：MySQL
上一篇：mybatis中的分頁插件