日常总结：大数量级表多层JOIN连接查询效率慢问题的解决方案 |
您所在的位置：网站首页 › join多表查询 › 日常总结：大数量级表多层JOIN连接查询效率慢问题的解决方案 |
|
1、订单信息表
假设有一张 TB 级别的大表(订单信息表) oeder_info，表字段如下： prov_id(省ID)city_id(市ID)area_id(区ID)town_id(街道ID)date(下单日期)order_type(订单类型：tc同城/not_tc非同城)order_money(订单金额：元)0010010100101010010101012021-12-06tc1000010010100101020010102032021-12-06tc1000020020200202020020202022021-12-07not_tc5000030030300303030030303032021-12-08tc800………………… 2、订单行政区域维度表另一张 TB 级别的大表(订单行政区域维度表) dim_order_area ，表字段如下： prov_id(省ID)prov_name(省名称)city_id(市ID)city_name(市名称)area_id(区ID)area_name(区名称)town_id(街道ID)town_name(街道名称)001河南省00101洛阳市0010101涧西区001010101西苑街道001河南省00101洛阳市0010102洛龙区001010203开元大道001…………………002浙江省00202杭州市0020202西湖区002020202蒋村街道002…………………003上海00303上海市0030303杨浦区003030303五角场街道003……………………………………… 3、两表的拼接需求现在我们需要将两张表进行关联，让 oeder_info 外连接 dim_order_area 得到包含省/市/区ID，以及省/市/区名称的完整的订单明细表，要求明细表中包含各个街道维度的订单总数、订单总金额、非同城订单总数、按照订单日期（天维度）分组，且只筛选 2021 年的订单。 prov_id(省ID)prov_name(省名称)city_id(市ID)city_name(市名称)area_id(区ID)area_name(区名称)town_id(街道ID)town_name(街道名称)date(下单日期)not_tc_order_count(街道维度的非同城上门订单数量)街total_order_money(道维度的总交易金额)001河南省00101洛阳市0010102洛龙区001010203开元大道2021-12-06989800我使用如下SQL语句去查询（未优化调整）： SELECT order_tab.prov_id AS prov_id, -- 省ID area_tab.prov_name AS prov_name, -- 省名称 order_tab.city_id AS city_id, -- 市ID area_tab.name AS city_name, -- 市名称 order_tab.area_id AS area_id, -- 区ID area_tab.area_name AS area_name, -- 区名称 order_tab.town_id AS town_id, -- 街道ID area_tab.town_name AS town_name, -- 街道名称 order_tab.date AS sign_date, -- 订单日期 COUNT(*) AS town_total, -- 统计总订单数量 -- 统计非同城订单总数 SUM(CASE WHEN order_tab.order_type = 'not_tc' THEN 1 ELSE 0 END) AS not_tc_order_count, SUM(order_tab.order_money) AS total_order_money -- 统计街道维度的订单总金额 FROM order_info AS order_tab -- 订单信息表 LEFT JOIN -- 左外连接订单行政区域维度表 dim_order_area AS area_tab ON order_tab.prov_id = area_tab.prov_id AND order_tab.city_id = area_tab.city_id AND order_tab.area_id = area_tab.area_id AND order_tab.town_id = area_tab.town_id GROUP BY -- 按照如下字段进行分组 order_tab.city_id , order_tab.prov_id , order_tab.area_id , order_tab.town_id , order_tab.date ,area_tab.prov_name ,area_tab.city_name ,area_tab.county_name ,area_tab.town_name HAVING YEAR(sign_date) = 2021; -- 只筛选2021年的订单以上SQL虽然可以解决上述查询需求，但是会遇到一个问题，如下所示： ... LEFT JOIN -- 左外连接订单行政区域维度表 dim_order_area AS area_tab ON -- order_tab.prov_id = area_tab.prov_id AND order_tab.city_id = area_tab.city_id AND order_tab.area_id = area_tab.area_id AND order_tab.town_id = area_tab.town_id ...这里通过一连串的AND拼接判断条件（并集），得到的结果可能会有误差，假如某个订单信息表中的town_id是空，其他省市区ID是正常的，那么他就无法被该LEFT JOIN拼接的条件匹配到，会被直接舍弃。 改进方案： 将LEFT JOIN并集拼接判断条件方式改成单个逐次拼接： LEFT JOIN -- 左外连接订单行政区域维度表(拼接省份名称，*并通过DISTINCT去重复*) (SELECT prov_name,prov_id FROM dim_order_area) AS area_tab1 ON order_tab.prov_id = area_tab1.prov_id LEFT JOIN -- 左外连接订单行政区域维度表(拼接市名称，*并通过DISTINCT去重复*) (SELECT city_id,city_name FROM dim_order_area) AS area_tab2 ON order_tab.city_id = area_tab2.city_id LEFT JOIN -- 左外连接订单行政区域维度表(拼接区名称，*并通过DISTINCT去重复*) (SELECT county_id,county_name FROM dim_order_area) AS area_tab3 ON order_tab.area_id = area_tab3.county_id LEFT JOIN -- 左外连接订单行政区域维度表(拼接街道名称，*并通过DISTINCT去重复*) (SELECT town_id,town_name FROM dim_order_area) AS area_tab4 ON order_tab.town_id = area_tab4.town_id但是也会遇到一个问题：查询时间过长！ 原因：多次LEFT JOIN，每次子查询都要将两张表拼接之后生成一个中间表，然后连续4次做笛卡尔积拼接。解决方案：DISTINCT 关键字去重：去掉每次子查询中重复的数据（eg: 第一次只筛选出省份去重复，第二次只筛选出城市去重复…），这样就可以很大程度上降低中间表生成的成本，加快查询时间。调整优化过后的SQL语句： SELECT order_tab.prov_id AS prov_id, -- 省ID area_tab1.prov_name AS prov_name, -- 省名称 order_tab.city_id AS city_id, -- 市ID area_tab2.name AS city_name, -- 市名称 order_tab.area_id AS area_id, -- 区ID area_tab3.area_name AS area_name, -- 区名称 order_tab.town_id AS town_id, -- 街道ID area_tab4.town_name AS town_name, -- 街道名称 order_tab.date AS sign_date, -- 订单日期 COUNT(*) AS town_total, -- 统计总订单数量 -- 统计非同城订单总数 SUM(CASE WHEN order_tab.order_type = 'not_tc' THEN 1 ELSE 0 END) AS not_tc_order_count, SUM(order_tab.order_money) AS order_money -- 统计街道维度的订单总金额 FROM order_info AS order_tab -- 订单信息表 LEFT JOIN -- 左外连接订单行政区域维度表(拼接省份名称，*并通过DISTINCT去重复*) (SELECT DISTINCT prov_name,prov_id FROM dim_order_area) AS area_tab1 ON order_tab.prov_id = area_tab1.prov_id LEFT JOIN -- 左外连接订单行政区域维度表(拼接市名称，*并通过DISTINCT去重复*) (SELECT DISTINCT city_id,city_name FROM dim_order_area) AS area_tab2 ON order_tab.city_id = area_tab2.city_id LEFT JOIN -- 左外连接订单行政区域维度表(拼接区名称，*并通过DISTINCT去重复*) (SELECT DISTINCT county_id,county_name FROM dim_order_area) AS area_tab3 ON order_tab.area_id = area_tab3.county_id LEFT JOIN -- 左外连接订单行政区域维度表(拼接街道名称，*并通过DISTINCT去重复*) (SELECT DISTINCT town_id,town_name FROM dim_order_area) AS area_tab4 ON order_tab.town_id = area_tab4.town_id GROUP BY -- 按照如下字段进行分组 order_tab.city_id , order_tab.prov_id , order_tab.area_id , order_tab.town_id , order_tab.date ,area_tab1.prov_name ,area_tab2.city_name ,area_tab3.county_name ,area_tab4.town_name HAVING YEAR(sign_date) = 2021; -- 只筛选2021年的订单 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |