Day03—SQL优化案例

工作内容，不对外开放

一、Exists可能会让sql巨慢

hpfm_unit中unit_type_code = ‘D’ 表示它是部门数据，unit_type_code = 'C’表示它是公司，unit_type_code = ‘G’ 表示这条数据是集团。
一般的，集团下面是公司，公司下面是部门，部门下面还可能是部门。
我们要从hpfm_unit中查找部门编码或部门名称LIKE '%DEP1-A%'的数据，然后找到这些部门数据后，再全表遍历hpfm_position表，拿到（这些部门）及其（它们的父级）和（父级的父级)数据。也就是说白了，找到一个部门后还要找到该部门的父部门和它所属的公司它所属的集团

方式一：

SELECT
*
FROM
        hpfm_unit u
        JOIN hpfm_unit_tl utl ON utl.unit_id = u.unit_id 
        AND utl.lang = 'zh_CN'
        LEFT JOIN hpfm_unit pu ON pu.unit_id = u.parent_unit_id
        LEFT JOIN hpfm_company hc ON hc.company_id = u.company_id 
WHERE
        1 = 1 
        AND u.tenant_id = 0 
        AND EXISTS (
        SELECT
                1
        FROM
                hpfm_unit ut 
        WHERE
                1 = 1 
                AND ( ut.unit_name LIKE '%DEP1-A%' OR ut.unit_code LIKE '%DEP1-A%' ) 
				AND ut.unit_type_code = 'D' 
                AND ( ut.unit_id = u.unit_id OR ut.level_path LIKE concat( u.level_path, '|%' )) 
        ) 

性能：查询了60s，因为它的执行逻辑就是
①从hpfm_unit中查找查找部门编码或部门名称LIKE '%DEP1-A%'的数据，找到这些部门数据
②全表遍历hpfm_position表，拿到那些部门及其它们的父级和父级的父级数据。

方式二：

select
						DISTINCT
            t3.unit_id,
            t3.unit_code,
            t3.unit_name,
            t3.unit_type_code,
            t3.tenant_id,
            t3.description,
            t3.order_seq,
            t3.parent_unit_id,
            t3.supervisor_flag,
            t3.enabled_flag,
            t3.level_path,
            t3.unit_company_id,
            t3.object_version_number,
            t3.company_id,
            t3.quick_index,
            t3.phoneticize,
            t3.parent_unit_name,
            t3.unit_company_name,
            t3.company_name
from
        (
						SELECT
										t1.level_path
						FROM
									(
										SELECT
														t.level_path,
														t.unit_code,
														tl.unit_name 
										FROM
														hpfm_unit t
														JOIN hpfm_unit_tl tl ON tl.unit_id = t.unit_id 
														AND tl.lang = 'zh_CN' 
										WHERE
														t.unit_type_code = 'D' 
														AND t.tenant_id = 0 
									) t1
						WHERE 
						t1.unit_name LIKE '%DEP1-A%'  
						OR t1.unit_code LIKE '%DEP1-A%'
        ) t2 ,
		(
				select
						u.unit_id,
						u.unit_code,
						ut.unit_name,
						u.unit_type_code,
						u.tenant_id,
						ut.description,
						u.order_seq,
						u.parent_unit_id,
						u.supervisor_flag,
						u.enabled_flag,
						u.level_path,
						u.unit_company_id,
						u.object_version_number,
						u.company_id,
						u.quick_index,
						u.phoneticize,
						put.unit_name parent_unit_name,
						uct.unit_name unit_company_name,
						hc.company_name
				from
						hpfm_unit u
				join hpfm_unit_tl ut on ut.unit_id = u.unit_id and ut.lang = 'zh_CN' 
				left join hpfm_unit pu on pu.unit_id = u.parent_unit_id
				left join hpfm_unit_tl put on pu.unit_id = put.unit_id and put.lang = 'zh_CN' 
				left join hpfm_unit uc on u.unit_company_id = uc.unit_id
				left join hpfm_unit_tl uct on uc.unit_id = uct.unit_id and uct.lang = 'zh_CN' 
				left join hpfm_company hc on hc.company_id = u.company_id
				where
						1 = 1
				and u.tenant_id = 0
		)t3
WHERE
 ( t2.level_path LIKE concat( t3.level_path, '|%' ) OR t2.level_path = t3.level_path ) 

执行仅用0.5s，因为它用到了该表的索引

二、练习

我们要从hpfm_position中查找岗位编码或岗位名称LIKE ‘%POSITION3-2%’ 的数据，然后找到这些岗位数据后，再全表遍历hpfm_position表，拿到（这些岗位）及其（它们的父级）和（父级的父级)数据。也就是说白了，找到一个岗位后还要找到该岗位是父岗位和它父岗位的父岗位。

SELECT
            t3.position_id,
            t3.tenant_id,
            t3.unit_id,
            t3.parent_position_id,
            t3.parent_id,
            t3.position_code,
            t3.description,
            t3.order_seq,
            t3.supervisor_flag,
            t3.enabled_flag,
            t3.level_path,
            t3.object_version_number
FROM
	(
	SELECT
		t1.level_path 
	FROM
		(
		SELECT
			hp.level_path,
			hp.position_code,
			hpt.position_name 
		FROM
			hpfm_position hp
			JOIN hpfm_position_tl hpt ON hpt.position_id = hp.position_id 
			AND hpt.lang = 'zh_CN' 
		WHERE
			hp.tenant_id = 0 
		) t1 
	WHERE
		t1.position_name LIKE '%POSITION3-2%' 
		OR t1.position_code LIKE '%POSITION3-2%' 
	) t2,
	(
	select
	          hhp.position_id,
            hhp.tenant_id,
            hhp.unit_id,
            hhp.unit_company_id,
            hhp.parent_position_id ,
            hhp.parent_position_id  parent_id,
            hhp.position_code,
            hhp.description,
            hhp.order_seq,
            hhp.supervisor_flag,
            hhp.enabled_flag,
            hhp.level_path,
            hhp.object_version_number
        from hpfm_position hhp
		WHERE
			hhp.tenant_id = 0 
	) t3 
WHERE
	( t2.level_path LIKE concat( t3.level_path, '|%' ) OR t3.level_path = t2.level_path )

select
						DISTINCT
            t3.unit_id,
            t3.unit_code,
            t3.unit_type_code,
            t3.tenant_id,
            t3.order_seq,
            t3.parent_unit_id,
            t3.supervisor_flag,
            t3.enabled_flag,
            t3.level_path,
            t3.unit_company_id,
            t3.object_version_number,
            t3.company_id,
            t3.quick_index,
            t3.phoneticize
from
        (
						SELECT
										t1.level_path
						FROM
									(
										SELECT
														t.level_path,
														t.unit_code,
														tl.unit_name 
										FROM
														hpfm_unit t
														JOIN hpfm_unit_tl tl ON tl.unit_id = t.unit_id 
														AND tl.lang = 'zh_CN' 
										WHERE
														t.tenant_id = 0 
									) t1
						WHERE 
						t1.unit_name LIKE '%DEP1-A%'  
						OR t1.unit_code LIKE '%DEP1-A%'
        ) t2 ,
		(
				select
						u.unit_id,
						u.unit_code,
						u.unit_type_code,
						u.tenant_id,
						u.order_seq,
						u.parent_unit_id,
						u.supervisor_flag,
						u.enabled_flag,
						u.level_path,
						u.unit_company_id,
						u.object_version_number,
						u.company_id,
						u.quick_index,
						u.phoneticize
				from
						hpfm_unit u
		)t3
WHERE
 ( t2.level_path LIKE concat( t3.level_path, '|%' ) OR t2.level_path = t3.level_path )