問題描述
問題語句：
?
|
現象：測試組發現這個語句從來就沒有執行完過，節點放了一晚上也跑不完。
其中，vtmp_log_node_1606，vm_taobao_shop_prop_61559109均為視圖。
解決過程
第一步：執行分析
與postgresql完全類似，隻要看到慢的SQL，第一件事就是explain一下。
?
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 1089938 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1065570 | Using where; Using join buffer |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 244 | ccms_base.a.customerno,const | 1 | Using index |
| 1 | SIMPLE | uni_customer | eq_ref | PRIMARY,index_uni_id | PRIMARY | 194 | ccms_base.p.uni_id | 1 | Using index |
注意到Extra字段有Using temporary; Using filesort，直覺告訴我們看到file字樣都要留意，覺得這裡一定是一個問題點。懷疑是索引沒有用到，無腦流的分析一下表：
?
|
但是結果并不理想。分析完後查詢解析并沒有變化。
看一下線程狀态：
?
|
發現執行這條語句的線程卡在Copying to tmp table。而有注意到之前的分析，表p（uni_customer_plat）有Using temporary。于是uni_customer_plat一定是有問題的，看一下索引：
?
|
結果觸目驚心：
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| uni_customer_plat | PRIMARY | 1 | plat_code | A | 18 | NULL | NULL | BTREE | ||||
| uni_customer_plat | PRIMARY | 2 | customerno | A | 1070885 | NULL | NULL | BTREE |
uni_id列用于連接配接，但是沒有建索引。果斷加上索引并且無腦流分析一下：
?
|
再次對語句執行分析：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | p | index | uni_customer_plat_uni_id | uni_customer_plat_uni_id | 194 | NULL | 1055755 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1065570 | Using where; Using join buffer |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 244 | ccms_base.a.customerno,const | 1 | Using index |
| 1 | SIMPLE | uni_customer | eq_ref | PRIMARY,index_uni_id | PRIMARY | 194 | ccms_base.p.uni_id | 1 | Using index |
不錯，果然用上了index。但是讨厭的file還在！看到filesort，到底是哪裡sort了呢？起先懷疑是那個distinct，可以去掉後依然存在。看來在這個SQL裡是找不到了。那麼，隻能去view裡檢查了（其實一般都要先去view裡面看看）。
?
|
| View | Create View | character_set_client | collation_connection |
| vtmp_log_node_1606 | CREATE ALGORITHM=UNDEFINED DEFINER=`ccms`@`localhost` SQL SECURITY DEFINER VIEW `vtmp_log_node_1606` AS select `vw_node_uni_id_all`.`uni_id` AS `user_id`,`vw_node_uni_id_all`.`control_group_type` AS `control_group_type` from `vw_node_uni_id_all` | utf8 | utf8_general_ci |
額。。。發現居然還套了一個視圖。vw_node_uni_id_all，繼續往下跟
?
|
| View | Create View | character_set_client | collation_connection |
| vw_node_uni_id_all | CREATE ALGORITHM=UNDEFINED DEFINER=`ccms`@`%` SQL SECURITY DEFINER VIEW `vw_node_uni_id_all` AS select `uni_customer`.`uni_id` AS `uni_id`,(1) AS `control_group_type` from `uni_customer` order by `uni_customer`.`uni_id`,(1) | utf8 | utf8_general_ci |
嗯，終于發現了，這裡有一個order by。可是事實上，從業務分析，這裡是不需要order by的，并且這個視圖做的事情也是沒什麼意義的，于是直接廢掉，建立一個視圖vtmp_log_node_1606_n來做測試：
?
|
| 5 | root | localhost | ccms_base | Query | 27 | Sending data | Select a.user_id, a.control_group_type From vtmp_log_node_1606_n a , vm_taobao_shop_prop_61559109 vm_taobao_shop_prop_61559109,uni_customer_plat p where vm_taobao_shop_prop_61559109.grade > 1.0 and a.user_id = p.uni_id and vm_taobao_shop_prop_61559109.customerno=p.customerno | 69 | 1 |
看狀态，直接就開始Sending data了。。。那個讨厭的Copy to tmp table直接消失。不過這個Sending data依然持續了很長時間沒有結束，等不急了，繼續優化。還是看現在的查詢解析：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | uni_customer | index | PRIMARY,index_uni_id | index_uni_id | 194 | NULL | 1076977 | Using index |
| 1 | SIMPLE | p | ref | uni_customer_plat_uni_id | uni_customer_plat_uni_id | 194 | ccms_base.uni_customer.uni_id | 1 | Using index |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 907681 | Using where; Using join buffer |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 244 | ccms_base.a.customerno,const | 1 | Using index |
發現a（vtmp_log_node_1606）有一個907681行的where條件掃描沒有用到任何key。觀察a引用到的表uni_customer，發現索引都是ok的。于是有懷疑到vm_taobao_shop_prop_61559109視圖。看看這個視圖：
| vm_taobao_shop_prop_61559109 | CREATE ALGORITHM=UNDEFINED DEFINER=`ccms`@`%` SQL SECURITY DEFINER VIEW `vm_taobao_shop_prop_61559109` AS select `a`.`customerno` AS `customerno`,`a`.`grade` AS `grade`,`b`.`valide_date` AS `valide_date`,`b`.`expire_date` AS `expire_date`,`a`.`trade_amount` AS `trade_amount`,`a`.`trade_count` AS `trade_count`,(curdate() - cast(`a`.`last_trade_time` as date)) AS `no_buy_days` from (`plt_taobao_crm_member` `a` left join `twb_vip_customer` `b` on(((`a`.`dp_id` = '61559109') and (`a`.`status` = 'normal') and (`b`.`shop_id` = '61559109') and (`a`.`customerno` = `b`.`customerno`)))) | utf8 | utf8_general_ci |
引用到了兩個表，其中plt_taobao_crm_member的customerno字段是要a做連接配接的。于是再看看這個表的情況，發現index是有的（聯合主鍵）
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| plt_taobao_crm_member | PRIMARY | 1 | dp_id | A | 18 | NULL | NULL | BTREE | ||||
| plt_taobao_crm_member | PRIMARY | 2 | customerno | A | 1212018 | NULL | NULL | BTREE |
奇怪。。。做做查詢測試，
?
|
結果：1 row in set (0.51 sec)
一條記錄居然要半秒！！！貌似是索引根本沒起作用。分析一下表，沒用。。。于是繼續無腦流在customerno字段建立一個索引
?
|
再次執行索引查詢，結果：1 row in set (0.00 sec)。感覺又回到了地球上。
現在再跑一下原來的大SQL的查詢解析：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | p | index | uni_customer_plat_uni_id | uni_customer_plat_uni_id | 194 | NULL | 1080412 | Using index; Using temporary |
| 1 | SIMPLE | a | ref | plt_taobao_crm_member_customerno | plt_taobao_crm_member_customerno | 152 | ccms_base.p.customerno | 1 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 244 | ccms_base.a.customerno,const | 1 | Using index |
| 1 | SIMPLE | uni_customer | eq_ref | PRIMARY,index_uni_id | PRIMARY | 194 | ccms_base.p.uni_id | 1 | Using index |
注意，這個是帶了distinct的。實際執行一下，得到結果
71127 rows in set (13.30 sec)
至此，一個不可能完成的SQL在非常正常的時間執行完了。
總結
查詢優化，就是根據explain的結果一步步抽絲剝繭，不斷的嘗試，直到得到期望的執行計劃。
在這個過程裡，最需要的就是耐心，仔細的觀察每一個explain輸出。同時也要對業務有比較好的了解。
當你優化的不耐煩的時候，就在心裡不斷對自己重複：我就是一個SQL優化機器人，我生下來就是為了優化SQL的。
![各種二分查找[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)