Mysql 差集與 Database Tuning
最近有遇到一個在某些表格會插入失敗的實例，問題是知道該如何解了，但是我們想要知道現在 DB 中的差異，於是乎遇到了一些問題，把這些東西記錄一下。
目前的狀況是兩個 DB 的 record count 一個是KK級的，一個是10K級的
sql 中要找兩個 table 裡面的差集，第一個想法是用
但是看起來整個效能還滿差的，而且還咬住了兩個table 讓別的 query process 無法拿資料，於是乎又在網路上查了一下，看到了另外一個解法：
看起來非常殘念 mysql 會跟我說 EXCEPT 有問題，也許是 mysql 不支援這樣的語法
接下來又查了一會兒，找到了下面的語法
用這種判斷 is null 的方式來找出差集，可是這樣又殘念了，因為效能還是很差，丟進去 5 分鐘都跑不出來答案，後來只好想說，既然會咬住 table，那用 create temporary table 的方式，讓這個 query process 不要咬住 table，底下是我用的語法：
和上面最大的不同是多了兩個 unique index 條件，結果這次命令跑的飛快，大約5秒就有答案了，原來加了index 會有這麼好的效果＠＠
反正我們平常 query 的語法多半是以 cola/colb 為 where, 加上了這個 index 不知道會不會讓 query 的效能變好一點
目前的狀況是兩個 DB 的 record count 一個是KK級的，一個是10K級的
sql 中要找兩個 table 裡面的差集，第一個想法是用
SELECT cola FROM table1 WHERE cola NOT IN (SELECT colb FROM table2);但是看起來整個效能還滿差的，而且還咬住了兩個table 讓別的 query process 無法拿資料，於是乎又在網路上查了一下，看到了另外一個解法：
SELECT cola FROM table1
EXCEPT
SELECT colb FROM table2; 看起來非常殘念 mysql 會跟我說 EXCEPT 有問題，也許是 mysql 不支援這樣的語法
接下來又查了一會兒，找到了下面的語法
SELECT cola FROM table1
LEFT JOIN table2 ON cola=colb
WHERE colb IS NULL用這種判斷 is null 的方式來找出差集，可是這樣又殘念了，因為效能還是很差，丟進去 5 分鐘都跑不出來答案，後來只好想說，既然會咬住 table，那用 create temporary table 的方式，讓這個 query process 不要咬住 table，底下是我用的語法：
CREATE TEMPORARY TABLE table3 SELECT cola FROM table1;
CREATE TEMPORARY TABLE table4 SELECT colb FROM table2;
SELECT * FROM table3 where cola NOT IN (SELECT colb FROM table4 ); 結果這樣子跑了10分鐘都跑不出來，我怕再下去即使跑出來也因為 connection timeout 而看不到結果，浪費了 cpu 資源，而且我還有要把兩個 table 反過來查詢的要跑，於是又找了一下看有沒有更有效能的方式，找了半天，有人說建 index 會快很多，於是我又改了一下我的語法：CREATE TEMPORARY TABLE table3 SELECT cola FROM table1;
CREATE UNIQUE INDEX idx_cola ON table3 (cola);
CREATE TEMPORARY TABLE table4 SELECT colb FROM table2;
CREATE UNIQUE INDEX idx_colb ON table4 (colb);
SELECT * FROM table3 where cola NOT IN (SELECT colb FROM table4 );和上面最大的不同是多了兩個 unique index 條件，結果這次命令跑的飛快，大約5秒就有答案了，原來加了index 會有這麼好的效果＠＠
反正我們平常 query 的語法多半是以 cola/colb 為 where, 加上了這個 index 不知道會不會讓 query 的效能變好一點
繼續閱讀...
