本文是我最近了解到的sql某些方法()组合实现一些功能的总结以后还会更新:
rand与rand(n)实现提取随机行及order by原理的探讨。
Bit_and,bit_or,bit_count等位操作方法实现统计功能。
rand与rand(n)提取
使用order by rand(n)配合limit n可以提起相应的n个随机样本。
如:select * from student grade=’2’ order by rand() limit 5
随机提取5条2年级学生数据。
起初对 order by 一个 随机值的方式有一些疑惑。要想理解这条语句组合首先需要对order by 的实现原理有一定的了解(早就想知道order by在使用索引等一系列问题上有这么多的特殊之处的原因了) 于是我找到了这里
对于这句话我有一定的怀疑。
注意:MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了。
查询资料后发现了一个合并索引的概念,这里由于并不是本文的重点就先不说了。(不过这里有一个想法如果是这样,那么我使用join的方式来执行,需要有索引但由于where导致的不能使用索引的order by的sql语句,是否能够有效的提高sql效率呢?留待以后来验证)
由上的文章了解到order by 有 indexsort 与filesort两种方式实现。
Indexsort方式即通过索引实现的排序。而索引都是有序的了(建立时给出特别option的除外)。无需再进行排序。
Tips: SQL 语句执行顺序: SELECT FROM --1 WHERE --2 GROUP BY --3 HAVING --4 ORDER BY --5)
Filesort即通过排序算法实现:
具体方式有
读取所有需要排序的数据
每行数据
算法1(original):存储排序key和行指针
算法2(modified):存储排序key和select中的字段
每次排序sort_buffer_size能容纳的行数,排序结果写入IO_CACHE对象(不妨称为IO1),本次排序结果的位置信息写入另一个IO_CACHE对象(不妨称为IO2);
iO_CACHE超过64k时写入临时文件
当order by有limit n时,只需要把前n条排序结果写入IO_CACHE;
排序KEY长度<=20且排序KEY数量在一千和十万之间时使用radixsort,否则使用quicksort
Merge buffer
读取排序结果(算法2直接从临时文件读取结果;算法1从临时文件读取行指针,再从表中读取数据)
摘自
https://blog.csdn.net/softn/article/details/51988943
也即最外部使用了外部排序,没次外部排序使用桶排序或者快速排序。取决于数据量的大小,Sort_buffer_size也会影响排序的性能。
有上面的内容可以了解到order by rand()实际上把是将n条记录或者n条select记录的查询查询字段加上一个rand值进行排序。返回排序结果进行输出截取。
百度order by rand()会跳出一篇被大量转载的:sql优化-order by rand的文章中提到改组合会使sql的效率急剧下降!。
对此我也做了一下测试
explain select * from t_financial_bak order by cashAndBank limit 5
用时1.085s
explain select * from t_financial_bak order by rand() limit 5
用时14.5s
两个都没用到索引,都使用了filesort。但使用order by rand()的语句使用了temporary而且用时高达14.5s。至于那篇文章中提到的会多次在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。
我起初看得有点蒙不过由于这个例子我明白了:
select * from t_financial_bak where id>=(SELECT FLOOR( MAX(id) * RAND()) FROM t_financial_bak ) limit 5
用时2.728s
2. select * from t_financial_bak as t1 join (select rand()* (SELECT MAX(id) FROM t_financial_bak)AS id)as t2 limit 5
用时0.014s
都是作者提出的对order by rand()的改进方式,两者一个采用子查询的方式,一个采用join的方式。子查询的方式用时比join方式多的多。
对上面order by rand()效率的问题我们应该有了结论。
Order by rand()的效率问题不是order by与rand()之间有什么特别的不契合的地方而是,由于每一条数据都要调用rand语句,造成了大量的rand函数调用时间,甚至需要给sql开辟临时表空间才能满足调用rand函数对空间的需求。从而导致了sql效率的低下。
而用上文中的子查询以及join方式来代替也是一个权益之计,因为rand()* MAX(id)是有一定大小的,而较小的数字不一定能够比这个数字更大,仅仅是在大于rand()*MAX(id)的数据中截取5个对小的(对于已经有序的数据来说)。5条数据明显是相邻的。而order by rand()的结果每条数据都有很好的随机性。
我的解决办法:
如果可以:可以在每条数据行中加入一个随机数,使用join一个随机数的方式来去随机。不过需要牺牲一定的数据空间,当然如果可以还是在程序中实现更加方便。
Bit_and,bit_or,bit_count统计功能的实现
为操作我们在学习c语言基础的时候就接触过,但是位操作意义不明显,不直观(虽然规则很简单)
文中对“1<
此处针对1<
最后经过bit_or按位或后变成10000..(20个0)100,然后用bit_count统计2进制1的个数为2,非常巧妙。
说的不明不白。其实很简单就是如同c中的位操作一样,表示把1左移day位,这样实现了不同值占用不同的位。
文中使用了三种方式来实现去重统计。
多重查询方法
select year, month, count(0)
from (select year, month, day,count(0) from t1 group by year, month, day) as tmp
group by year, month;
位操作方法
select year,month,bit_count(bit_or(1<
以及distinct方法
select year,month,count(distinct day) from t1 group by year,month;
下面我们来比较一下三者的效率
select qygm,hydm ,count(qygm) from (select qygm,hydm from t_financial_bak group by hydm,qygm ) as tmp group by hydm
(内部sql起到去除hydm与qygm都重复的内容)
其中qygm与hydm都是有索引的,用时2.035s
可以看到内部与外部的sql语句都没有能用上索引,而且都使用了temporary与filesort
效率低下。
select qygm,hydm,bit_count(bit_or(1<
用时2.293s
Explain的结果是使用了名为hydm_sy的这个索引,而且也没有使用到temporary与filesort
但是依然使用了相当不短的时间
select qygm,hydm,count(distinct(qygm)) from t_financial_bak group by hydm
用时2.024s跟前两者的用时在同一个数量级。
而且也使用到了索引hydm_sy,说明distinct在索引的使用上并没有说明特殊,没有被distinct的列依然可以使用索引。
那么我们看一下三者的时间都花在哪里?
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 8.6E-5 |
| checking permissions | 7E-6 |
| Opening tables | 0.000135 |
| init | 1.6E-5 |
| System lock | 9E-6 |
| optimizing | 3E-6 |
| optimizing | 4E-6 |
| statistics | 1.5E-5 |
| preparing | 1.6E-5 |
| Creating tmp table | 6.2E-5 |
| Sorting result | 6E-6 |
| statistics | 7E-6 |
| preparing | 7E-6 |
| Creating tmp table | 5.9E-5 |
| Sorting result | 4E-6 |
| executing | 1.1E-5 |
| Sending data | 1.1E-5 |
| executing | 2E-6 |
| Sending data | 2.086242 |
| Creating sort index | 0.005237 |
| removing tmp table | 1.8E-5 |
| Creating sort index | 0.002354 |
| Creating sort index | 0.002965 |
| end | 4E-6 |
| removing tmp table | 8E-6 |
| end | 3E-6 |
| query end | 9E-6 |
| closing tables | 2E-6 |
| removing tmp table | 1E-5 |
| closing tables | 1.3E-5 |
| freeing items | 5.2E-5 |
| cleaning up | 1.3E-5 |
+----------------------+----------+
结果显示三者的时间都花在了sanding data上,与是我修改数据返回加了limit。
结果distinct与bit_count用时都缩短到了0.002s
而内外查询的sql用时依然是2s。
而用时较短的两者都所花的时间也大多用在了sanding data上。
当我把有索引的hydm换成一个没有索引的列时,三者再度回到了2s这个数量级。说明内外查询的方式与其他两种方式的差距确实是hydm索引造成的。
而distinct与bit方法依然不分上下。
于是我换了一个更大的表,结果distinct用时66s,而bit方法用时125s,有趣的是当我去除distinct这个关键字是用时121s。
看上去貌似distinct获胜了。当时不要忘了我们的sql语句一直是在进行着order by。当我们只要去重统计个数,而不要分组时会怎么样呢?
在统计字段有索引和没有索引是Bit方式用时都是16s,而distinct方式在有索引时用时12s,没有时用时21s。两者95%以上的时间花在sanding data上。
可见bit方式的io比distinct少,这就说明distinct应该做了一些更多的io操作。但是没有filesort的过程可以了解distinct是不排序的。使用order by时distinct用时更少说明,order by中也许包含了distinct中的某些操作,从而使的去重的过程并没有花费更多的时间,反而应为节省的io时间而优化了sql效率。
当然这里使用bit方式去除重复与distinct比较只是想探究一下distinct做了哪些事。Bit方式对于少量数据可以使用,但是mysql整数类型是有边界的,而使用位作为不同值的标识,其上限仅仅是64!!