MySQL反連線的優化總結

今天同事有一個環境發現一條語句執行時間很長，感到非常奇怪。剛好有些時間，就抽空琢磨了下這個問題。
總體來看這個環境還是相對比較繁忙的，執行緒大概是200多個。
# mysqladmin pro|less|wc -l
235
帶著好奇檢視慢日誌，馬上定位到這個語句，已做了脫敏處理。
# Time: 161013  9:51:45
# User@Host: root[root] @ localhost []
# Thread_id: 24630498  Schema: test Last_errno: 1160  Killed: 0
# Query_time: 61213.561106  Lock_time: 0.000082  Rows_sent: 7551  Rows_examined: 201945890920  Rows_affected: 0  Rows_read: 7551
# Bytes_sent: 0  Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 2F8E5A82
SET timestamp=1476323505;
select account from t_fund_info
where money >=300 and account not in
(select distinct(login_account) from t_user_login_record where login_time >='2016-06-01')
into outfile '/tmp/data.txt';
從慢日誌來看，執行時間達61213s,這個是相當驚人了，也就意味著這個語句跑了一整天。
這引起了我的好奇和興趣，這個問題有得搞頭了。
表t_fund_info資料量近200萬，存在一個主鍵在id列，唯一性索引在account上。
CREATE TABLE `t_fund_info`
。。。
PRIMARY KEY (`id`),
  UNIQUE KEY `account` (`account`)
) ENGINE=InnoDB AUTO_INCREMENT=1998416 DEFAULT CHARSET=utf8
表t_user_login_record資料量2千多萬，存在主鍵列id
CREATE TABLE `t_user_login_record`
。。。
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22676193 DEFAULT CHARSET=utf8
從語句可以看出，是在做一個批量的大查詢，是希望把查詢結果生成一個文字檔案來，但是過濾條件很有限。目前根據查詢來看肯定是全表掃描。
先簡單看了下過濾條件，從t_fund_info這個表中，根據一個過濾條件能過濾掉絕大多數的資料，得到1萬多資料，還是比較理想的。
> select count(*)from t_fund_info where money >=300;
+----------+
| count(*) |
+----------+
|    13528 |
+----------+
1 row in set (0.99 sec)
那問題的瓶頸看來是在後面的子查詢了。
把下面的語句放入一個SQL指令碼query.sql
select distinct(login_account) from t_user_login_record where login_time >='2016-06-01';
匯出資料，大概耗時1分鐘。
time mysql test < query.sql > query_rt.log
real    0m59.149s
user    0m0.394s
sys     0m0.046s
過濾後的資料有50多萬，相對還是比較理想的過濾情況。
# less query_rt.log|wc -l
548652
我們來解析一下這個語句，看看裡面的Not in的條件是怎麼解析的。
explain extended select account from t_fund_info
where money >=300 and account not in
 (select distinct(login_account) from t_user_login_record where login_time >='2016-06-01')；
show warnings;
結果如下：
Message: select `test`.`t_fund_info`.`account` AS `account` from `test`.`t_fund_info` where ((`test`.`t_fund_info`.`money` >= 300) and (not((`test`.`t_fund_info`.`account`,(select distinct 1 from `test`.`t_user_login_record` where ((`test`.`t_user_login_record`.`login_time` >= '2016-06-01') and (((`test`.`t_fund_info`.`account`) = `test`.`t_user_login_record`.`login_account`) or isnull(`test`.`t_user_login_record`.`login_account`))) having (`test`.`t_user_login_record`.`login_account`))))))
可以看到整個解析的過程非常複雜，原本簡單的一個語句，經過解析，竟然變得如此複雜。

因為MySQL裡面的優化改進空間相比Oracle還是少很多，我決定循序漸進來嘗試優化。因為這個環境還是很重要的，所以我在從庫端使用mysqldump匯出資料，匯入到另外一個測試環境，放開手腳來測試了。
首先對於not in的部分，是否是因為生成臨時表的消耗代價太高導致，所以我決定建立一個臨時表來快取子查詢的資料。
> create table test_tab as select distinct(login_account) login_account from t_user_login_record where login_time >='2016-06-01';
Query OK, 548650 rows affected (1 min 3.78 sec)
Records: 548650  Duplicates: 0  Warnings: 0
這樣檢視這個臨時表就很輕鬆了，不到1秒就出結果。
> select count(*)from test_tab;
+----------+
| count(*) |
+----------+
|   548650 |
+----------+
1 row in set (0.38 sec)
然後再次檢視使用臨時表後的查詢是否解析會有改善。
explain extended select account from t_fund_info
where money >=300 and account not in(select login_account from test_tab);
show warnings;
發現還是一樣，可見臨時表的改進效果不大。
| Note  | 1003 | select `test`.`t_fund_info`.`account` AS `account` from `test`.`t_fund_info` where ((`test`.`t_fund_info`.`money` >= 300) and (not((`test`.`t_fund_info`.`account`,(select 1 from `test`.`test_tab` where (((`test`.`t_fund_info`.`account`) = `test`.`test_tab`.`login_account`) or isnull(`test`.`test_tab`.`login_account`)) having (`test`.`test_tab`.`login_account`)))))) |
是否是因為子查詢中的資料量太大導致整個反連線的查詢過程中回表太慢，那我縮小一下子查詢的資料條數。
select account from t_fund_info
where money >=300 and  not exists (select login_account from test_tab where login_account=t_fund_info.account limit 1,10);
這種方式依舊很卡，持續了近半個小時還是沒有反應，所以果斷放棄。
是不是t_fund_info的過濾查詢導致了效能問題，我們也建立一個臨時表
> create table test_tab1 as select account from t_fund_info
    -> where money >=300;
Query OK, 13528 rows affected (1.38 sec)
Records: 13528  Duplicates: 0  Warnings: 0
再次查詢效果依舊很不理想。
select account from test_tab1
where  not exists (select login_account from test_tab where login_account=test_tab1.account limit 1,10);
持續了20多分鐘還是沒有反應，所以還是果斷放棄。
這個時候能想到就是索引了，我們在臨時表test_tab上建立索引。
> create index ind_tmp_login_account on test_tab(login_account);
Query OK, 0 rows affected (4.31 sec)
Records: 0  Duplicates: 0  Warnings: 0
在臨時表test_tab1上也建立索引。
> create index ind_tmp_account on test_tab1(account);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
再次檢視效能就變得很好了，執行時間0.15秒，簡直不敢相信。
explain select account from test_tab1
where  not exists (select login_account from test_tab where login_account=test_tab1.account );
11364 rows in set (0.15 sec)

執行計劃如下：

可見通過這種拆分，不斷的猜測和排除，已經找到了一些思路。
我們開始抓住問題的本質。
首先刪除test_tab1上的索引，看看執行效果如何。
> alter table test_tab1 drop index ind_tmp_account;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
語句如下，執行時間0.15秒
select account from test_tab1
     where  not exists (select login_account from test_tab where login_account=test_tab1.account );    
+--------------------------------+
11364 rows in set (0.15 sec)
是否not in的方式會有很大的差別呢，持續0.18秒，有差別，但差別不大。
select account from test_tab1
     where account not in (select login_account from test_tab  );
+--------------------------------+
11364 rows in set (0.18 sec)
我們逐步恢復原來的查詢，去除臨時表test_tab1,整個查詢持續了1.12秒。
select account from t_fund_info
where money >=300 and account not in(select login_account from test_tab);
+--------------------------------+
11364 rows in set (1.12 sec)
使用explain extended解析的內容如下：
Message: select `test`.`t_fund_info`.`account` AS `account` from `test`.`t_fund_info` where ((`test`.`t_fund_info`.`money` >= 300) and (not((`test`.`t_fund_info`.`account`,(((`test`.`t_fund_info`.`account`) in test_tab on ind_tmp_login_account checking NULL having (`test`.`test_tab`.`login_account`)))))))
這個時候，問題已經基本定位了。在反連線的查詢中，在這個問題場景中，需要對子查詢的表新增一個索引基於login_account，可以和外層的查詢欄位對映，提高查詢效率。
當然在一個資料量龐大，業務相對繁忙的系統中，新增一個臨時需求的索引可能不是一個很好的方案。不過我們還是在測試環境體驗一下。
> create index ind_tmp_account1 on t_user_login_record(login_account);
Query OK, 0 rows affected (4 min 45.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
新增索引的過程持續了近4分鐘，在這個時候我們使用最開始的查詢語句，效能如何呢。
select account from t_fund_info where money >=300 and account not in  (select distinct(login_account) from t_user_login_record where);
+--------------------------------+
11364 rows in set (2.52 sec)
只要2.52秒就可以完成之前20多個小時查詢結果，效能簡直就是天壤之別。
不過話說回來，跑批查詢可以在從庫上執行，從庫上建立一個這樣的索引，用完再刪掉也是不錯的選擇，要麼就是建立一個臨時表，在臨時表上建立索引，臨時表的意義就在於此，不是為了做查詢結果快取而是建立索引來提高資料過濾效率。
在此有個問題就是臨時表只有一個欄位，建立索引的意義在哪裡呢。
我畫一個圖來解釋一下。

首先這個查詢的資料是以t_fund_info的過濾條件為準，從200萬資料中過濾得到1萬條資料，然後兩個欄位通過account=login_account的條件關聯，而不是先關聯子查詢的過濾條件 login_time,過濾完之後account的值之後再過濾login_time，最後根據not in的邏輯來取捨資料，整個資料集就會大大減少。如此一來，子查詢的表千萬行，效能的差別就不會是指數級的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2126302/，如需轉載，請註明出處，否則將追究法律責任。