《關(guān)于MySQL的一些騷操作》

概要

回顧以前寫的項目，發(fā)現(xiàn)在規(guī)范的時候，還是可以做點騷操作的。

假使以后還有新的項目用到了MySQL，那么肯定是要實踐一番的。

為了準備，創(chuàng)建測試數(shù)據(jù)表（建表語句中默認使用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;

插入沖突時更新數(shù)據(jù)

SQL執(zhí)行插入時，可能因為種種原因插入失敗，比如UNIQUE索引沖突導(dǎo)致插入失敗。比如某個不曉得DBA插入了一條錯誤的學(xué)生記錄("3", "小明")，悲劇的是小明的編號是1。常規(guī)做法就是判斷當前的數(shù)據(jù)庫記錄中是否存在小明的記錄，如果有則更新其對應(yīng)其編號，否則就插入小明的記錄。當然存在更好的做法：

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數(shù)值更新為用戶插入數(shù)據(jù)中的no，這樣每條沖突的數(shù)據(jù)就可以動態(tài)的設(shè)置新的數(shù)值。

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

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

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

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

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

使用JOIN替換子查詢

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

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還是比較容易替換子循環(huán)，代碼雖然會稍顯晦澀，但是也許可以避免在并發(fā)量大的某個晚上你被叫起來檢討自己的錯誤。MySQL一直在優(yōu)化子查詢，在部分條件下子查詢可能會比JOIN具有更高的效率，因此在有時間進行驗證的情況下選擇最佳的SQL語句。

JOIN中的WHERE和AND坑

為了更好的說明坑，我這里需要創(chuàng)建一個新的表，并在原來的學(xué)生表中添加字段:

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

偽造一些數(shù)據(jù)，假設(shè)有4個班級，4班沒有相對應(yīng)的學(xué)生。使用如下的查詢語句就能發(fā)現(xiàn)不同之處：

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;

查詢結(jié)果如下圖所示：

需要注意的是，此處我再查詢條件中設(shè)置了 c.no < 4 這一JOIN條件，但是明顯的沒有起到作用，查詢結(jié)果中仍然顯示了no=4的結(jié)果，這是因為此次查詢使用的JOIN是LEFT JOIN，class作為左表，在匹配條件無法完全滿足的情況下，亦會將左表的所有數(shù)據(jù)顯示出來，引入了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;

查詢結(jié)果如下圖所示：

為什么同樣是使用LEFT JOIN，查詢結(jié)果就不同了呢？這是因為可以認為SQL是分成兩部分進行執(zhí)行的(偽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;

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

獲取資料：

最后給大家分享一份學(xué)習(xí)資料，里面包括：（BATJ面試資料、高可用、高并發(fā)、高性能及分布式、Jvm性能調(diào)優(yōu)、Spring源碼，MyBatis，Netty，Redis，Kafka，Mysql，Zookeeper，Tomcat，Docker，Dubbo，Nginx等多個知識點的架構(gòu)資料）和Java進階學(xué)習(xí)路線圖。

免費領(lǐng)取加q群：478052716  即可！

分頁查詢優(yōu)化

查詢的優(yōu)化，最初是在研究MongoDB的分頁查詢時學(xué)到的，只能說大多數(shù)的數(shù)據(jù)庫都是差不多的(當然現(xiàn)在存在時序數(shù)據(jù)庫，分頁查詢那是更加騷氣的)。大多數(shù)的分頁查詢都是類似如下的寫法：

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

這樣的寫法存在性能損耗，數(shù)據(jù)庫會將所有符合條件的數(shù)據(jù)查詢出來，挨個數(shù)到第1000條記錄，最后選取前10條記錄進行交差。前面的1000條數(shù)據(jù)，就會顯得很浪費，在LIMIT數(shù)值很大的情況下，這個性能損耗就是無法忍受的了（百度就會默認禁止查詢76頁以后的數(shù)據(jù)）。

因為分頁一般是逐頁翻下去的(如果是跳頁進行查詢，那就只能用上面的查詢語句慢慢查詢搜尋結(jié)果了)，那么每次分頁完都能獲取當前的最大ID，我們可以基于ID確定我們的搜索起始點，基于此點向后查詢10條滿足要求的結(jié)果，改動如下(讓前端多傳一個當前頁的最大ID，這個小小的要求當然是可以滿足的)：

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

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

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

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

UPDATE/DELETE改動多個表記錄

工作中，經(jīng)常需要修改多個表中的關(guān)聯(lián)記錄。一般的做法是將相關(guān)表中的記錄查詢出來，再挨個進行修改。如果修改的邏輯較為復(fù)雜，那么這樣做是沒有問題的，但是若是只是簡單的修改（比如修改boolean變量），那么可以通過一條SQL語句完成此任務(wù)。

SQL中只要提及多個表，那么大致上就會出現(xiàn)JOIN的身影。我們有個需求，就是將3班的學(xué)生轉(zhuǎn)移到5班（原有的3班更改為5班）,使用JOIN語句的話就可以按照如下方式完成任務(wù)。

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既可以完成此任務(wù)，可以拓展到修改多個表中數(shù)據(jù)內(nèi)容，也可以擴展至DELETE語句中。

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

此處，就簡單的總結(jié)一下：

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

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