上一篇: 
Mysql 简单优化
Mysql 简单优化
根据status信息对MySQL服务器进行优化一个很有意义的SQL的优化过程
[ 2009/04/02 23:10 | by selboo ]
优化前
This is The SQL Code
SELECT COUNT(DISTINCT v_yjhm)FROM (SELECT v_yjhm
FROM zjjk_t_yssj_o_his a
WHERE n_yjzl > 0
AND d_sjrq BETWEEN to_date('20070301', 'yyyymmdd') AND
to_date('20070401<a name="entrymore"></a>', 'yyyymmdd')
AND v_yjzldm LIKE '40%'
AND NOT EXISTS(SELECT 'a' FROM INST_TRIG_ZJJK_T_YSSJ_O b WHERE a.v_yjtm=b.yjbh)
--and v_yjtm not in (select yjbh from INST_TRIG_ZJJK_T_YSSJ_O)
UNION
SELECT v_yjhm
FROM zjjk_t_yssj_u_his a
WHERE n_yjzl > 0
AND d_sjrq BETWEEN to_date('20070301', 'yyyymmdd') AND
to_date('20070401', 'yyyymmdd')
AND v_yjzldm LIKE '40%'
AND NOT EXISTS(SELECT 'a' FROM INST_TRIG_ZJJK_T_YSSJ_U b WHERE a.v_yjtm=b.yjbh))
Parsed in 0.006 seconds at 134.24 KB/s
--and v_yjtm not in (select yjbh from INST_TRIG_ZJJK_T_YSSJ_U))
说明：
1、zjjk_t_yssj_o_his 、zjjk_t_yssj_u_his 的d_sjrq 上都有一个索引了
2、zjjk_t_yssj_o_his 、zjjk_t_yssj_u_his 的v_yjtm 都为 not null 字段
3、INST_TRIG_ZJJK_T_YSSJ_O、INST_TRIG_ZJJK_T_YSSJ_U 的 yjbh 为PK
优化建议：
1、什么是DISTINCT ? 就是分组排序后取唯一值 ，底层行为 分组排序
2、什么是 UNION 、 UNION ALL ？ UNION ： 对多个结果集取DISTINCT ，生成一个不含重复记录的结果集，返回给前端，UNION ALL ：不对结果集进行去重复操作 底层行为：分组排序
3、什么是 COUNT(*) ？ 累加
4、需要有什么样的索引？ S_sjrq + v_yjzldm ： 理由： 假如全省的数据量在表中全部数为1000万，查询月数据量为200万，1000万中特快占50%， 则 通过 beween 时间(d_sjrq)+ 种类( v_yjzldm )，可过滤出约100万，这是最好的检索方式了。
5、两表都要进行一次 NOT EXISTS 运算，如何做最优？ NOT EXISTS 是不好做的运算，但是我们可以合并两次的NOT EXISTS 运算。让这费资源的活只干一次。
综合以上，我们可以如下优化这个SQL：
1、内部的UNION 也是去重复，外部的DISTINCT 也是去重复，可左右去掉一个，建议内部的改为 UNION ALL ， 这里稍请注意一下，如果V_YJHM 有NULL的情况，可能会引起COUNT值不对实际数的情况。
2、建一个 D_SJRQ+V_YJZLDM 的复合索引
3、将两个子查询先 UNION ALL 联结 ， 另两个用来做 NOT EXISTS 的表也 UNION ALL 联结
4、在3的基础上再做 NOT EXISTS
5、将NOT EXISTS 替换为NOT IN ，同时加提示 HASH_AJ 做半连接HASH运算
6、最后为外层的COUNT(DISTINCT … 获得结果数
优化后
This is The SQL Code
SELECT COUNT(DISTINCT v_yjhm)FROM (SELECT v_yjtm, v_yjhm
FROM zjjk_t_yssj_o_his a
WHERE n_yjzl > 0
AND d_sjrq BETWEEN to_date('20070301', 'yyyymmdd') AND
to_date('20070401', 'yyyymmdd')
AND v_yjzldm LIKE '40%'
UNION ALL
SELECT v_yjtm, v_yjhm
FROM zjjk_t_yssj_u_his a
WHERE n_yjzl > 0
AND d_sjrq BETWEEN to_date('20070301', 'yyyymmdd') AND
to_date('20070401', 'yyyymmdd')
AND v_yjzldm LIKE '40%'
) a
WHERE a.v_yjtm NOT IN
(SELECT /*+ HASH_AJ */
yjbh
FROM (SELECT yjbh
FROM INST_TRIG_ZJJK_T_YSSJ_O
UNION ALL
SELECT yjbh FROM INST_TRIG_ZJJK_T_YSSJ_U))
Parsed in 0.005 seconds at 151.03 KB/s
经过上述改造，原来这个SQL的执行时间如果为2分钟的话，现在应该20秒足够！
最后编辑： selboo 编辑于2012/05/22 15:53
