首页 > 代码库 > MySQL sql 无法自动谓词推入

MySQL sql 无法自动谓词推入

mysql 版本 5.6.16:

优化前:

xxx@5.6.16-log 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)

优化后:

xxx@5.6.16-log 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 无法自动谓词推入