sql优化案例
模拟场景
目标
查询前1000条办公地址属于北京市、上海市的境内上市公司股票信息数据(代码、名称证券类别、上市时间),并标识机构类别及统一社会信用代码
提供条件
证券信息总表PUB205:『
SECCODE_PUB205 股票代码
SECNAME_PUB205 股票名称
F002V_PUB205 证券类别编码
F003V_PUB205 证券类别描述
F007D_PUB205 上市日期
F012V_PUB205 代码属性编码 行情代码code为214001
F013V_PUB205 代码属性描述 行情代码
F014V_PUB205 发行机构ID 关联到PUB203.ORGID_PUB203
ISVALID 有效性
』
机构信息总表PUB203:『
ORGID_PUB203 机构ID
ORGNAME_PUB203 机构名称(中文)
F001C_PUB203 境内上市标识 1-是,0-否
F002V_PUB203 机构类别编码
F003V_PUB203 机构类别
F060V_PUB203 统一社会信用代码
ISVALID 有效性
』
注册地址及办公地址PUB225:『
ORGID_PUB225 机构ID
F002V_PUB225 地区编码 北京:‘110000’,上海:‘268001’
TYPE_PUB225 类型 1-注册地址,2-办公地址
ISVALID 有效性
』
待优化sql
SELECT
*
FROM PUB205 p5 LEFT JOIN PUB203 p3 ON p5.f014v_pub205 = p3.orgid_pub203
WHERE p5.isvalid = 1
AND p3.isvalid = 1
AND p5.f014v_pub205 in (
SELECT p25.ORGID_PUB225
FROM PUB225 p25
WHERE p25.isvalid = 1
AND p25.type_pub225 = 2
AND p25.f002v_pub225 in ('110000','268001')
)
AND p5.f013v_pub225 = '行情代码'
AND p3.f001c_pub203 = '1'
LIMIT 1000
优化后sql
WITH p5 as
(
SELECT
SECCODE_PUB205,
SECNAME_PUB205,
F002V_PUB205,
F003V_PUB205,
F007D_PUB205,
F012V_PUB205,
F013V_PUB205,
F014V_PUB205
FROM PUB205
WHERE ISVALID = 1
AND F012V_PUB205 = '214001'
AND EXISTS (
SELECT 1
FROM PUB225 p25
WHERE p25.isvalid = 1
AND p23.TYPE_PUB225 = 2
AND p25.F002V_PUB225 in ('110000','268001')
AND p25.orgid_pub225 = p5.F014V_PUB205
)
),
p3 as
(
SELECT
ORGID_PUB203,
ORGNAME_PUB203,
F002V_PUB203,
F003V_PUB203,
F060V_PUB203,
F001C_PUB203
FROM PUB203
WHERE ISVALID = 1
)
SELECT p5.*,p3.*
FROM p5 join p3 on p5.F014V_PUB205 = p3.orgid_pub203
WHERE p3.F001C_PUB203 = '1'
LIMIT 1000