mariadb使用with子句重写SQL性能提升5倍
2024-09-01 09:43:12
几个月前，我们有个产品的开发反馈了个问题，说有个组织结构的查询很慢，几千行的复杂关联需要1秒钟，表示太慢了，原语句如下：
SELECT
org.org_id,
org.dimension,
org.org_code,
org.org_name,
org.parent_id,
org.manage_id,
org.org_cate,
org.org_level,
org.org_order,
org.org_path,
org.position_code,
org.ext_id,
org.remark,
pos.position_name,
parent.org_code AS parent_code,
parent.org_name AS parent_name,
manage.org_code AS manage_code,
manage.org_name AS manage_name,
org. STATUS
FROM
tsys_organization org
LEFT JOIN tsys_position pos ON org.position_code = pos.position_code
LEFT JOIN tsys_organization parent ON org.parent_id = parent.org_id
LEFT JOIN tsys_organization manage ON org.manage_id = manage.org_id
WHERE
org.org_id IN(
SELECT
org.org_id
FROM
tsys_organization org
JOIN tsys_user us ON us.user_id ='admin' UNION
SELECT
ou.org_id
FROM
tsys_org_user ou
WHERE
user_id ='admin'
UNION
SELECT
org.org_id
FROM
tsys_organization org
JOIN pos_user_view po ON po.user_id ='admin'
)
AND org. STATUS = 0
ORDER BY
org.org_order
够复杂的，看了下执行计划，确实有个子句没有物化导致i/o访问次数下不来，于是对该子查询使用with重写如下：
with a as (
SELECT
org2.org_id
FROM
tsys_organization org2
JOIN tsys_user us ON us.user_id ='admin' UNION all
SELECT
ou.org_id
FROM
tsys_org_user ou
WHERE
user_id ='admin'
UNION all
SELECT
org1.org_id
FROM
tsys_organization org1
JOIN pos_user_view po ON po.user_id ='admin'
)
SELECT
org.org_id,
org.dimension,
org.org_code,
org.org_name,
org.parent_id,
org.manage_id,
org.org_cate,
org.org_level,
org.org_order,
org.org_path,
org.position_code,
org.ext_id,
org.remark,
pos.position_name,
parent.org_code AS parent_code,
parent.org_name AS parent_name,
manage.org_code AS manage_code,
manage.org_name AS manage_name,
org. STATUS
FROM
tsys_organization org
LEFT JOIN tsys_position pos ON org.position_code = pos.position_code
LEFT JOIN tsys_organization parent ON org.parent_id = parent.org_id
LEFT JOIN tsys_organization manage ON org.manage_id = manage.org_id
WHERE
org.org_id IN (select org_id from a)
AND org. STATUS = 0
ORDER BY
org.org_order
因为mariadb的with子句会强制物化为临时表，改写后，0.2秒执行完成（oracle 12.2开始也支持强制，在此之前需要加materialized提示）。
最新文章
- JAVA_HOME环境变量失效的解决办法
- Photoshop 融合属性 Unity Shader
- 在创建窗口句柄之前,不能在控件上调用 Invoke 或 BeginInvoke
- Vmware workstation 10 NAT功能总是不正常的原因
- SharpGL学习笔记(十五) 纹理映射
- matplotlib库的常用知识
- java 导入包（误区）
- Eliza Doolittle lyrics Big City
- Delphi 利用TComm组件 Spcomm 实现串行通信
- Android 仿网易新闻v3.5:上下滑动的引导页
- 用vue实现简单实时汇率计算功能
- wpf listview 行变色
- WPF ResourceDictionary 主题资源替换（一）
- Django（五）母版继承、Cookie、视图装饰器等
- 版本控制commit和update过程
- 『TensorFlow』读书笔记_VGGNet
- OpenCV设置保存图像压缩率
- offset[Parent/Width/Height/Top/Left] 、 client[Width/Height/Top/Left] 、 Element.getBoundingClientRect()
- Python_01 执行方式、解释器路径、编码、变量、条件语句
- Android组件化之终极方案