MySQL sql 無法自動謂詞推入

標籤：mysql 謂詞推入

mysql 版本 5.6.16：

最佳化前：

[email protected] jd_credit 03:41:23>SELECT aaa.POP_VENDER_ACC_ID as accId, sum(aaa.aa1) as TC

    ->   FROM (SELECT a.POP_VENDER_ACC_ID,

    ->                a.POP_VENDER_NAME,

    ->                a.license_no,

    ->                (CASE

    ->                  WHEN a.POP_VENDER_NAME = b.POP_VENDER_NAME OR

    ->                       a.license_no = b.license_no THEN

    ->                   b.totalPRINCIPAL

    ->                  ELSE

    ->                   0

    ->                END) aa1

    ->           FROM a_01_pop_info a

    ->           JOIN (SELECT t.POP_VENDER_NAME,

    ->                       t.license_no,

    ->                       t.POP_VENDER_ACC_ID,

    ->                       SUM(t1.REST_PRINCIPAL) AS totalPRINCIPAL

    ->                  FROM a_01_pop_info t, A_01_POP_LOAN_INFO t1

    ->                 WHERE t.POP_VENDER_ACC_ID = t1.POP_VENDER_ACC_ID

    ->                   AND t1. STATUS = ‘1‘

    ->                 GROUP BY t.POP_VENDER_ACC_ID,

    ->                          t.POP_VENDER_NAME,

    ->                          t.license_no) b ON 1 = 1) aaa

    ->  where aaa.POP_VENDER_ACC_ID = ‘40367‘

    ->  GROUP BY aaa.POP_VENDER_ACC_ID;

+-------+----------+

| accId | TC       |

+-------+----------+

| 40367 | 34817.30 |

+-------+----------+

1 row in set (9.64 sec)

最佳化後：

[email protected] jd_credit 03:41:16>SELECT aaa.POP_VENDER_ACC_ID as accId, sum(aaa.aa1) as TC

    ->   FROM (SELECT a.POP_VENDER_ACC_ID,

    ->                a.POP_VENDER_NAME,

    ->                a.license_no,

    ->                (CASE

    ->                  WHEN a.POP_VENDER_NAME = b.POP_VENDER_NAME OR

    ->                       a.license_no = b.license_no THEN

    ->                   b.totalPRINCIPAL

    ->                  ELSE

    ->                   0

    ->                END) aa1

    ->           FROM a_01_pop_info a

    ->           JOIN (SELECT t.POP_VENDER_NAME,

    ->                       t.license_no,

    ->                       t.POP_VENDER_ACC_ID,

    ->                       SUM(t1.REST_PRINCIPAL) AS totalPRINCIPAL

    ->                  FROM a_01_pop_info t, A_01_POP_LOAN_INFO t1

    ->                 WHERE t.POP_VENDER_ACC_ID = t1.POP_VENDER_ACC_ID

    ->                   AND t1. STATUS = ‘1‘

    ->                 GROUP BY t.POP_VENDER_ACC_ID,

    ->                          t.POP_VENDER_NAME,

    ->                          t.license_no) b ON 1 = 1

    ->          where a.POP_VENDER_ACC_ID = ‘40367‘) aaa

    ->  GROUP BY aaa.POP_VENDER_ACC_ID

    -> ;

+-------+----------+

| accId | TC       |

+-------+----------+

| 40367 | 34817.30 |

+-------+----------+

1 row in set (0.00 sec)

.從目前的版本5.6.16 MySQL 並沒有自動做謂詞推入，Oracle 8i開始這方面自動就謂詞推入，減少最佳化的工作量，希望MySQL 能儘早實現謂詞推入功能。

 

本文出自 “My DBA life” 部落格，請務必保留此出處http://huanghualiang.blog.51cto.com/6782683/1557138

MySQL sql 無法自動謂詞推入