straight_join最佳化sql語句
在表關聯查詢中，mysql最佳化器往往會選擇結果集小的表，但是往往也會有例外：
SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task` ebt, `edm$business_task_list` ebtl, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebt.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
執行時間需要三十秒
explain下，以ebt作為驅動表：
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| 1 | SIMPLE | ebt | ref | PRIMARY,index_business_task_rid,index_business_task_bt | index_business_task_bt | 5 | const | 3341380 | Using where |
| 1 | SIMPLE | bu | eq_ref | PRIMARY,idx_buyer$user_country_id | PRIMARY | 4 | cfec1.ebt.relative_id | 1 | Using where |
| 1 | SIMPLE | ebtl | ref | index_business_tl_tid,index_business_tl_bt | index_business_tl_tid | 5 | cfec1.ebt.task_id | 1 | Using where |
| 1 | SIMPLE | ecs | ref | fk_strategy_bt | fk_strategy_bt | 4 | cfec1.bu.country_id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+------------
檢視ebt的結果集（ebt.business_type = 23）
mysql> select count(*) from `edm$business_task` ebt where ebt.business_type =23 ;
+----------+
| count(*) |
+----------+
| 1749616 |
+----------+
1 row in set (0.87 sec)
ebt表竟然有100多萬行記錄，然後想辦法縮小結果集
我們關注查詢這樣一些表示式：ebt.business_type = ebtl.business_type AND ebt.business_type = 23然後ebtl.exec_email = 0，TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
是否可以將ebtl作為關聯表，ebt.business_type = 23改成ebtl.business_type=23來減少結果集呢？
看如下語句，結果集為22w的樣子
mysql> SELECT count(*) FROM `edm$business_task_list` ebtl WHERE ebtl.business_type=23 AND ebtl.exec_email = 0 AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
+----------+
| count(*) |
+----------+
| 222669 |
+----------+
看來理論上還是可行了
改寫sql語句如下：
mysql> SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task` ebt, `edm$business_task_list` ebtl, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebtl.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
Empty set (30.73 sec)
結果還是30秒
檢視下執行計劃：
mysql> explain SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task` ebt, `edm$business_task_list` ebtl, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebtl.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| 1 | SIMPLE | ebt | ref | PRIMARY,index_business_task_rid,index_business_task_bt | index_business_task_bt | 5 | const | 3341380 | Using where |
| 1 | SIMPLE | bu | eq_ref | PRIMARY,idx_buyer$user_country_id | PRIMARY | 4 | cfec1.ebt.relative_id | 1 | Using where |
| 1 | SIMPLE | ebtl | ref | index_business_tl_tid,index_business_tl_bt | index_business_tl_tid | 5 | cfec1.ebt.task_id | 1 | Using where |
| 1 | SIMPLE | ecs | ref | fk_strategy_bt | fk_strategy_bt | 4 | cfec1.bu.country_id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
還是用原來的ebt作為驅動表（由此可看出MySQL最佳化器沒有自動最佳化）
那麼我的強制使用ebtl作為驅動表，改寫sql如下，發現執行時間只有5s：
mysql> SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task_list` ebtl straight_join `edm$business_task` ebt, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebtl.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
Empty set (4.99 sec)
檢視執行計劃，以ebtl作為了驅動表：
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
| 1 | SIMPLE | ebtl | ref | index_business_tl_tid,index_business_tl_bt | index_business_tl_bt | 10 | const,const | 3312034 | Using where |
| 1 | SIMPLE | ebt | eq_ref | PRIMARY,index_business_task_rid,index_business_task_bt | PRIMARY | 4 | cfec1.ebtl.task_id | 1 | Using where |
| 1 | SIMPLE | bu | eq_ref | PRIMARY,idx_buyer$user_country_id | PRIMARY | 4 | cfec1.ebt.relative_id | 1 | Using where |
| 1 | SIMPLE | ecs | ref | fk_strategy_bt | fk_strategy_bt | 4 | cfec1.bu.country_id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
至此該條語句的最佳化完成。
SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task` ebt, `edm$business_task_list` ebtl, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebt.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
執行時間需要三十秒
explain下，以ebt作為驅動表：
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| 1 | SIMPLE | ebt | ref | PRIMARY,index_business_task_rid,index_business_task_bt | index_business_task_bt | 5 | const | 3341380 | Using where |
| 1 | SIMPLE | bu | eq_ref | PRIMARY,idx_buyer$user_country_id | PRIMARY | 4 | cfec1.ebt.relative_id | 1 | Using where |
| 1 | SIMPLE | ebtl | ref | index_business_tl_tid,index_business_tl_bt | index_business_tl_tid | 5 | cfec1.ebt.task_id | 1 | Using where |
| 1 | SIMPLE | ecs | ref | fk_strategy_bt | fk_strategy_bt | 4 | cfec1.bu.country_id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+------------
檢視ebt的結果集（ebt.business_type = 23）
mysql> select count(*) from `edm$business_task` ebt where ebt.business_type =23 ;
+----------+
| count(*) |
+----------+
| 1749616 |
+----------+
1 row in set (0.87 sec)
ebt表竟然有100多萬行記錄，然後想辦法縮小結果集
我們關注查詢這樣一些表示式：ebt.business_type = ebtl.business_type AND ebt.business_type = 23然後ebtl.exec_email = 0，TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
是否可以將ebtl作為關聯表，ebt.business_type = 23改成ebtl.business_type=23來減少結果集呢？
看如下語句，結果集為22w的樣子
mysql> SELECT count(*) FROM `edm$business_task_list` ebtl WHERE ebtl.business_type=23 AND ebtl.exec_email = 0 AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
+----------+
| count(*) |
+----------+
| 222669 |
+----------+
看來理論上還是可行了
改寫sql語句如下：
mysql> SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task` ebt, `edm$business_task_list` ebtl, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebtl.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
Empty set (30.73 sec)
結果還是30秒
檢視下執行計劃：
mysql> explain SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task` ebt, `edm$business_task_list` ebtl, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebtl.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| 1 | SIMPLE | ebt | ref | PRIMARY,index_business_task_rid,index_business_task_bt | index_business_task_bt | 5 | const | 3341380 | Using where |
| 1 | SIMPLE | bu | eq_ref | PRIMARY,idx_buyer$user_country_id | PRIMARY | 4 | cfec1.ebt.relative_id | 1 | Using where |
| 1 | SIMPLE | ebtl | ref | index_business_tl_tid,index_business_tl_bt | index_business_tl_tid | 5 | cfec1.ebt.task_id | 1 | Using where |
| 1 | SIMPLE | ecs | ref | fk_strategy_bt | fk_strategy_bt | 4 | cfec1.bu.country_id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
還是用原來的ebt作為驅動表（由此可看出MySQL最佳化器沒有自動最佳化）
那麼我的強制使用ebtl作為驅動表，改寫sql如下，發現執行時間只有5s：
mysql> SELECT ebt.relative_type, ebt.relative_Id, bu.buyer_photo, ebt.`business_type`, bu.email, bu.first_name, bu.last_name FROM `edm$business_task_list` ebtl straight_join `edm$business_task` ebt, `buyer$user` bu, `edm$country_strategy` ecs WHERE ebt.task_id = ebtl.task_id AND ebt.business_type = ebtl.business_type AND bu.buyer_id = ebt.relative_id AND ecs.country_id = bu.country_id AND ebtl.business_type = 23 AND ebtl.exec_email = 0 AND ecs.delivery_time_point = '1:00' AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
Empty set (4.99 sec)
檢視執行計劃，以ebtl作為了驅動表：
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
| 1 | SIMPLE | ebtl | ref | index_business_tl_tid,index_business_tl_bt | index_business_tl_bt | 10 | const,const | 3312034 | Using where |
| 1 | SIMPLE | ebt | eq_ref | PRIMARY,index_business_task_rid,index_business_task_bt | PRIMARY | 4 | cfec1.ebtl.task_id | 1 | Using where |
| 1 | SIMPLE | bu | eq_ref | PRIMARY,idx_buyer$user_country_id | PRIMARY | 4 | cfec1.ebt.relative_id | 1 | Using where |
| 1 | SIMPLE | ecs | ref | fk_strategy_bt | fk_strategy_bt | 4 | cfec1.bu.country_id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
至此該條語句的最佳化完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30221425/viewspace-1799834/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 最佳化sql語句SQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- sql語句的最佳化SQL
- SQL Profiles與語句最佳化SQL
- SQL語句運算子最佳化SQL
- 效能最佳化之SQL語句最佳化SQL
- SQL語句最佳化技術分析SQL
- ORACLE常用SQL最佳化hint語句OracleSQL
- 資料庫最佳化技巧 - SQL語句最佳化資料庫SQL
- 對sql語句的最佳化問題SQL
- Oracle SQL語句最佳化技術分析OracleSQL
- MySQL的SQL語句最佳化一例MySql
- Effective MySQL之SQL語句最佳化 小結MySql
- SQL最佳化案例-單表分頁語句的最佳化（八）SQL
- SQL語句SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- sql語句大全SQL
- 共享SQL語句SQL
- SQL語句整理SQL
- SQL基本語句SQL
- 精妙Sql語句SQL
- SQL語句集合SQL
- oracle sql語句OracleSQL
- sql語句 求救！SQL
- SQL精妙語句SQL
- SQL語句收集SQL
- 常用SQL語句SQL
- sql常用語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- [轉]透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- 第45期：一條 SQL 語句最佳化的基本思路SQL
- 【SQL】9 SQL INSERT INTO 語句SQL
- 1.3. SQL 語句SQL
- Sql語句小整理SQL
- SQL語句優化SQL優化
- SQL SELECT 語句SQL