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