mariadb使用with子句重写SQL性能提升5倍
2023-09-27 14:28:33 时间
几个月前,我们有个产品的开发反馈了个问题,说有个组织结构的查询很慢,几千行的复杂关联需要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提示)。
相关文章
- SQL Server-聚焦sp_executesql执行动态SQL查询性能真的比exec好?
- Oracle性能分析1:开启SQL跟踪和获取trace文件
- CREATE LOGIN (Transact-SQL)
- asp.net, c#,时间查询大全, sql语句 用于SQLServer(mssql)
- 《Spark 官方文档》Spark SQL, DataFrames 以及 Datasets 编程指南(四)
- 性能测试:自建数据库与RDS性能对比SQL Server案例排查分析
- 1.8w 字的 SQL 优化大全
- ORACLE性能优化之SQL语句优化
- 数据库SQL优化大总结之 百万级数据库优化方案
- 常见 SQL 语句的加锁分析
- Statistics gathering and SQL Tuning Advisor
- call_compile.sql
- SQL Server 查询性能优化——创建索引原则
- SQL where 条件顺序对性能的影响有哪些
- SQL CASE WHEN语句性能优化
- 浅析SQL优化查询性能的最佳实践
- Oracle 性能调优之:使用 V$SQL_PLAN 视图查询内存中的执行计划
- sql之浅谈视图的作用
- (4.20)sql server性能指标、性能计数器
- sql server性能调优
- 五分钟打造自己的sql性能分析工具
- oracle 查看表属主和表空间sql
- Sql Server-增加字段、修改字段、修改类型、修改默认值 ALTER
- SQL把a表字段数据存到b表字段 update,,insert