来自 数据库 2020-01-18 15:49 的文章
当前位置: 网上澳门金莎娱乐 > 数据库 > 正文

SQL网上澳门金莎娱乐 双亲节点查找所有子节点的实现方法

SELECT p1.id, p1.name, p1.parentId as parentId, p2.parentId as parent2_id, p3.parentId as parent3_id, p4.parentId as parent4_id, p5.parentId as parent5_id,p6.parentId as parent6_idFROMcategory p1LEFT JOIN category p2 on p2.id = p1.parentId LEFT JOIN category p3 on p3.id = p2.parentId LEFT JOIN category p4 on p4.id = p3.parentId LEFT JOIN category p5 on p5.id = p4.parentId LEFT JOIN category p6 on p6.id = p5.parentIdWHERE 1 IN (p1.parentId, p2.parentId, p3.parentId, p4.parentId, p5.parentId, p6.parentId)

http://www.bkjia.com/Mysql/450449.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/450449.htmlTechArticlemysql数据库中性能优化一直是一个大常乐谈的问题,下面我来给大家介绍避免临时表来给你的SQL语句性能提升100倍方法。 【问题现象】 线上...

很好的总结贴:

mysql数据库中性能优化一直是一个大常乐谈的问题,下面我来给大家介绍避免临时表来给你的SQL语句性能提升100倍方法。 【问题现象】    线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。    SQL语句如下:    SELECT DISTINCT g.*,  cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ; 【问题分析】    使用explain查看执行计划,结果如下:    网上澳门金莎娱乐 1    这条sql语句的问题其实还是比较明显的:    查询了大量数据(包括数据条数、以及g.* ),然后使用临时表order by,但最终又只返回了20条数据。    DBA观察到的IO高,是因为sql语句生成了一个巨大的临时表,内存放不下,于是全部拷贝到磁盘,导致IO飙升。 【优化方案】    优化的总体思路是拆分sql,将排序操作和查询所有信息的操作分开。    第一条语句:查询符合条件的数据,只需要查询g.id即可    SELECT DISTINCT g.id FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;    第二条语句:查询符合条件的详细数据,将第一条sql的结果使用in操作拼接到第二条的sql    SELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ; 【实测效果】    在SATA机器上测试,优化前大约需要50s,优化后第一条0.3s,第二条0.1s,优化后执行速度是原来的100倍以上,IO从100%降到不到1%    在SSD机器上测试,优化前大约需要7s,优化后第一条0.3s,第二条0.1s,优化后执行速度是原来的10倍以上,IO从100%降到不到1%    可以看出,优化前磁盘io是性能瓶颈,SSD的速度要比SATA明显要快,优化后磁盘不再是瓶颈,SSD和SATA性能没有差别。 网上澳门金莎娱乐,【理论分析】    MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。

CREATE TABLE category ( id LONG, parentId LONG, name String(20) )INSERT INTO category VALUES ( 1, NULL, 'Root' )INSERT INTO category VALUES ( 2, 1, 'Branch1' )INSERT INTO category VALUES ( 3, 1, 'Branch2' )INSERT INTO category VALUES ( 4, 3, 'SubBranch1' )INSERT INTO category VALUES ( 5, 2, 'SubBranch2' )
  • 临时表存储
  • 使用临时表的场景
  • 直接使用磁盘临时表的场景
  • 临时表相关配置
  • 表的设计原则
  • 如何判断使用了临时表?

这样就很通用啦~无论你 SQLite 还是 MySQL。

在另一边厢,大家都爱用的 MySQL 却无视 with 语句,官网博客上明确说明是压根不支持,十分不方便,明明可以很简单事情为什么不能用呢?——而且 MySQL 也好像没有计划在将来的新版本中添加 with 的 cte 功能。于是大家想出了很多办法。其实不就是一个递归程序么——应该不难——写函数或者存储过程总该行吧?没错,的确如此,——写递归不是问题,问题是用 SQL 写就是个问题——还是那句话,“隔行如隔山”,虽然有点夸张的说法,但我想既懂数据库又懂各种数据库方言写法的人应该不是很多吧~,——不细究了,反正就是代码帖来贴去呗~

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4FROM category AS t1LEFT JOIN category AS t2 ON t2.parentId = t1.idLEFT JOIN category AS t3 ON t3.parentId = t2.idLEFT JOIN category AS t4 ON t4.parentId = t3.idWHERE t1.id= 1

递归法

使用 with 语句递归,通俗易懂的例子,我第一个成功的例子就是从这里 copy 的,另外还可以查层数 level 和反向的父节点:_query

至此,我们的目的可以说已经达到了,而且还不错,因为这是不限层数的。——其实,一般情况下,层数超过三层就很多,很复杂了,一般用户如无特殊需求,也用不上这么多层。于是,在给定层数的约束下,可以写标准的 SQL 来完成该任务——尽管有点写死的感觉~~

WITH w1( id, parentId, name) AS (SELECT category.id, category.parentId, category.nameFROM category WHERE id = 1UNION ALL SELECT category.id, category.parentId, category.nameFROM category JOIN w1 ON category.parentId= w1.id) 

SQlite with 语句用法中文翻译

SELECT p1.id, p1.name, p1.parentId as parentId, p2.parentId as parent2_id, p3.parentId as parent3_id FROM category p1 LEFT JOIN category p2 on p2.parentId = p1.id LEFT JOIN category p3 on p3.parentId = p2.id WHERE 9 IN (p1.id, p2.id, p3.id) ORDER BY 1, 2, 3; 

其中,parent id 表示父节点, name 是节点名称。

怎么保存树状结构的数据呢?在 SQL 中常用的是双亲节点法。创建表如下

经查询,最好的方法是 SQL 递归 CTE 的方法。所谓 CTE 是 Common Table Expressison 公用表表达式的意思。网友评价说:“CTE 是一种十分优雅的存在。CTE 所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归 CTE 可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。”——其实我对 SQL 不太熟悉,大家谷歌下其意思即可。

ORDER BY 1, 2, 3, 4, 5, 6, 7; 这个总算像点样子了,结果是这样子的。

以上这篇SQL 双亲节点查找所有子节点的实现方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持脚本之家。

相应地给出查找所有父节点的方法

本文由网上澳门金莎娱乐发布于数据库,转载请注明出处:SQL网上澳门金莎娱乐 双亲节点查找所有子节点的实现方法

关键词: