导读
本文节选自松华老师《SQL优化专栏》
想阅读更多内容请点击订阅专栏
大家好,我是知数堂SQL 优化班老师 网名:骑龟的兔子
在之前的的系列文章:有趣的SQL(三)行转列,列转行和复制
已经给大家介绍了,行转列,列转行,复制等方法。
在这篇文章中,对其进行更深一层的应用。
需求如下,
有一个表如下图所示
现在有个需求是,我在画面输入
'10004,22744,24007' 要求返回对应的三行数据
当然有很多种解决方案,一类是,在前端或者server端用语言把输入值分开;
还有一种是在数据库端。
本文介绍的是在数据库端的方法。
当然还有 最简单方案如下
这种方案,也可以解决输入值有空格的情况
那这种方案的问题在哪呢?
先看下,这个表中是有索引的如下
运行了,下面的SQL 从执行计划中,可看出,走全表扫描了
也就是说表越大,有可能越慢!
写SQL有几个阶段,第一个阶段是为了熟悉各种语法和实现各种需求阶段
第二个阶段是,写出更符合数据库特点,符合优化思路的SQL
上面的SQL 就是满足了需求,但是不符合优化。
如果写成如下
就可以有能满足需求,又能达到优化效果的SQL。
那现在的问题是,怎么把 '10004','22744','24007' 一行数据的变成3行数据的问题。
这样总算,文章又回到本文开篇,没跑题~~
根据上篇文章,我们想把一行数据变成多行,我们需要复制
这里我用到了MySQL 8.0 开始支持的with语句,
不支持的可以用临时表代替。
其中num是为了复制而弄的中间表,可以理解为,我们中学的几何题中,为了解决问题画的辅助线。
上面的SQL 已经达到复制效果了。但是显然,复制的有点多了,我们只需要3行数据,但复制了5行,显然不行,我们观察下,结果发现我们如果复制的行数是逗号的个数+1 就可以了!
那怎么表达呢,我们用原来的length-去掉逗号后length +1 就可以
从上图所示,可以看出,这样就复制了我们想要的行数。
现在就剩下,怎么截取的问题,我们想要的是第一行截取第一个,第二行第二个,第n行第n个 。。。
MySQL 8.0给我们提供了regexp_substr这样的强大的函数,专门解决这样的问题
当然还有别的解决方案,为了代码的简单,我就用了这个函数
这样我们就解决了,行转列的问题,剩下的是把这部分带到原来的SQL中,
执行计划如下(执行计划稍微有点复杂),初学者比较难懂
下面是运行结果,还是符合需求的
为了给大家一个直观的感觉,我用大表salaries表替换了原来的dept_emp表
然后运行SQL如下 运行了3.8 秒 !
用修改之后代码行数太多,我把数据删了保留时间如下,0.01秒
用倍数的话:
差距相当明显!
本文,通过一个简单案例,给大家讲述了,SQL开发过程中的几个方法和优化思路,我们不仅仅开发过程中想到怎么解决需求,若想进一步,就需要考虑性能问题
谢谢大家~ 欢迎转发
我是知数堂SQL优化班老师 - 郑松华
如有关于SQL优化方面疑问需要交流的,请加入QQ群(579036588),并@骑龟的兔子 就可与我联系
END
![分割线箭头动态 640?wx_fmt=gif](https://img-blog.csdnimg.cn/img_convert/d9ff32e30d4a281828003271c1d5deb4.gif)
扫码加入的MySQL技术Q群
(群号:579036588)