数据库环境：SQL SERVER 2005
今天看到一条SQL，返回10条数据，执行了50多S。刚好有空，就对它进行了优化，优化后1S出结果。
先看下原始SQL
SELECT t1.line_no , MAX(sat100.confrim_date) confrim_date , sam63_lq.company_name , sat04.c_code , ctlm23.corr_name , MAX(sat04.l_date_d) l_date_d , SUM(sat05.qty_d_order) qty_d_order , sat100.tran_no_rowFROM sat100 INNER JOIN sat101 ON sat100.com_id = sat101.com_id AND sat100.tran_no = sat101.tran_no AND sat100.tran_row = sat101.tran_row LEFT JOIN sat05 ON sat101.com_id = sat05.com_id AND sat101.p_g_order_no = sat05.p_g_order_no AND sat101.p_g_order_line = sat05.p_g_order_line LEFT JOIN sat04 ON sat04.com_id = sat05.com_id AND sat04.p_g_order_no = sat05.p_g_order_no LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id AND sat05.item_no = ctlm22.item_no LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id AND sat100.company_id = sam63_lq.company_id LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id AND sat05.cx_item_no = sam60_lq.cx_item_no LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no AND sat100.company_id = sam65_lq.company_id LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id AND sat05.sa_unit = ctlm17.unit LEFT JOIN salm02 ON sat04.com_id = salm02.com_id AND sat04.c_code = salm02.client_id LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id AND sat04.c_code = ctlm23.corr_id LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY t2.tran_no_row ) line_no , t2.tran_no_row FROM ( SELECT DISTINCT sat100.tran_no_row FROM sat100 INNER JOIN sat101 ON sat100.com_id = sat101.com_id AND sat100.tran_no = sat101.tran_no AND sat100.tran_row = sat101.tran_row LEFT JOIN sat05 ON sat101.com_id = sat05.com_id AND sat101.p_g_order_no = sat05.p_g_order_no AND sat101.p_g_order_line = sat05.p_g_order_line LEFT JOIN sat04 ON sat04.com_id = sat05.com_id AND sat04.p_g_order_no = sat05.p_g_order_no LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id AND sat05.item_no = ctlm22.item_no LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id AND sat100.company_id = sam63_lq.company_id LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id AND sat05.cx_item_no = sam60_lq.cx_item_no LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id AND sat05.sa_unit = ctlm17.unit LEFT JOIN salm02 ON sat04.com_id = salm02.com_id AND sat04.c_code = salm02.client_id LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id AND sat04.c_code = ctlm23.corr_id LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id WHERE salm02.dept_id LIKE '%' AND sat100.company_id = '107' AND sat100.corr_id LIKE 'A010131%' AND sat04.l_date_d >= '2015/06/01 00:00:00' AND sat04.l_date_d <= '2015/06/30 23:59:59' ) t2 ) t1 ON sat100.tran_no_row = t1.tran_no_rowWHERE salm02.dept_id LIKE '%' AND sat100.company_id = '107' AND sat100.corr_id LIKE 'A010131%' AND sat04.l_date_d >= '2015/06/01 00:00:00' AND sat04.l_date_d <= '2015/06/30 23:59:59'GROUP BY t1.line_no , sam63_lq.company_name , sat04.c_code , ctlm23.corr_name , sat100.tran_no_rowORDER BY t1.line_no , sat100.tran_no_row
View Code
下面，我来说下我的优化思路：
1.检查SQL的写法是否有问题
先看下子查询部分，发现和外部访问的表及过滤的条件都差不多，用BeyondCompare工具检查外部查询和子查询的差别，相对于外部查询，
子查询少访问了一个表sam65_lq，即少了这部分内容“LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no AND sat100.company_id
= sam65_lq.company_id”，恰巧字段veh_no和字段company_id是sam65_lq的联合主键，因此，这部分并没有影响查询的数据。
再看下子查询，它要实现的功能就是根据不重复的tran_no_row生成一个序号，因此，可以用dense_rank()替代子查询实现相同的功能。同时，
sat04有过滤条件，因而可以将left join sat04改成inner join sat04。
改写后的SQL如下：
SELECT line_no , MAX(confrim_date) confrim_date , company_name , c_code , corr_name , MAX(l_date_d) l_date_d , SUM(qty_d_order) qty_d_order , tran_no_rowFROM ( SELECT DENSE_RANK() OVER ( ORDER BY sat100.tran_no_row ) AS line_no , sat100.confrim_date , sam63_lq.company_name , sat04.c_code , ctlm23.corr_name , sat04.l_date_d , sat05.qty_d_order , sat100.tran_no_row FROM sat100 INNER JOIN sat101 ON sat100.com_id = sat101.com_id AND sat100.tran_no = sat101.tran_no AND sat100.tran_row = sat101.tran_row LEFT JOIN sat05 ON sat101.com_id = sat05.com_id AND sat101.p_g_order_no = sat05.p_g_order_no AND sat101.p_g_order_line = sat05.p_g_order_line INNER JOIN sat04 ON sat04.com_id = sat05.com_id AND sat04.p_g_order_no = sat05.p_g_order_no LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id AND sat05.item_no = ctlm22.item_no LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id AND sat100.company_id = sam63_lq.company_id LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id AND sat05.cx_item_no = sam60_lq.cx_item_no LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no AND sat100.company_id = sam65_lq.company_id LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id AND sat05.sa_unit = ctlm17.unit LEFT JOIN salm02 ON sat04.com_id = salm02.com_id AND sat04.c_code = salm02.client_id LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id AND sat04.c_code = ctlm23.corr_id LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id WHERE salm02.dept_id IS NOT NULL AND sat100.company_id = '107' AND sat100.corr_id LIKE 'A010131%' AND sat04.l_date_d >= '2015/06/01 00:00:00' AND sat04.l_date_d <= '2015/06/30 23:59:59' ) tGROUP BY line_no , company_name , c_code , corr_name , tran_no_rowORDER BY line_no , tran_no_row
View Code
执行后，检查数据，发现和原始SQL的数据一致，说明改写的语法没有问题。但是，执行的时间没多大改观。
2.检查是否有缺失索引
看了下执行计划，发现在表sat100和表sat04上都走了索引扫描，在sat100表上没有针对company_id和corr_id建和组合索引，sat04表上也没有
针对l_date_d字段建的索引，而恰巧这些列的选择性都和高，适合建索引。
建了索引后，再执行SQL，发现最快的还是要10S。不行，还得继续优化。
3.检查执行计划，看执行计划是否合理
sat05走了索引查找，它的查询开销达到88%。sat05表作为嵌套循环的内部表，外部表有5W多行记录，sat05表总共有50多W条记录，
且在sat05表上没有任何过滤条件。因此，可以判定，这里不应该走嵌套循环，应该使用哈希连接。所以，直接强制走哈希即可。
改写优化后的完整SQL如下：
SELECT line_no , MAX(confrim_date) confrim_date , company_name , c_code , corr_name , MAX(l_date_d) l_date_d , SUM(qty_d_order) qty_d_order , tran_no_rowFROM ( SELECT DENSE_RANK() OVER ( ORDER BY sat100.tran_no_row ) AS line_no , sat100.confrim_date , sam63_lq.company_name , sat04.c_code , ctlm23.corr_name , sat04.l_date_d , sat05.qty_d_order , sat100.tran_no_row FROM sat100 INNER JOIN sat101 ON sat100.com_id = sat101.com_id AND sat100.tran_no = sat101.tran_no AND sat100.tran_row = sat101.tran_row LEFT HASH JOIN sat05 ON sat101.com_id = sat05.com_id AND sat101.p_g_order_no = sat05.p_g_order_no AND sat101.p_g_order_line = sat05.p_g_order_line INNER JOIN sat04 ON sat04.com_id = sat05.com_id AND sat04.p_g_order_no = sat05.p_g_order_no LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id AND sat05.item_no = ctlm22.item_no LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id AND sat100.company_id = sam63_lq.company_id LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id AND sat05.cx_item_no = sam60_lq.cx_item_no LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no AND sat100.company_id = sam65_lq.company_id LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id AND sat05.sa_unit = ctlm17.unit LEFT JOIN salm02 ON sat04.com_id = salm02.com_id AND sat04.c_code = salm02.client_id LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id AND sat04.c_code = ctlm23.corr_id LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id WHERE salm02.dept_id IS NOT NULL AND sat100.company_id = '107' AND sat100.corr_id LIKE 'A010131%' AND sat04.l_date_d >= '2015/06/01 00:00:00' AND sat04.l_date_d <= '2015/06/30 23:59:59' ) tGROUP BY line_no , company_name , c_code , corr_name , tran_no_rowORDER BY line_no , tran_no_row
View Code
原标题：dense_rank()+hash提示改写优化SQL
关键词：sql
我知道了 