正文
MYSQL SQL语句技巧初探（一）
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
MYSQL SQL语句技巧初探（一）本文是我最近了解到的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在使用索引等一系列问题上有这么多的特殊之处的原因了） 于是我找到了这里
https://blog.csdn.net/shine0181/article/details/17807345
对于这句话我有一定的怀疑。
注意：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的效率急剧下降！。
https://blog.csdn.net/mico_cmm/article/details/53446902
对此我也做了一下测试
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统计功能的实现
https://blog.csdn.net/koli6678/article/details/79841153
为操作我们在学习c语言基础的时候就接触过，但是位操作意义不明显，不直观（虽然规则很简单）
文中对“1<<day” 的解释让我很迷惑。
此处针对1<<day进行解释：这里的计数采取的是位图法，每天有访问就用1表示，02天表示100，23表示1000..(23个0）。
最后经过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<<day)) as days from t1 group by year,month;
以及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<<qygm)) from t_financial_bak group by hydm
用时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！！
本文是我最近了解到的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在使用索引等一系列问题上有这么多的特殊之处的原因了） 于是我找到了这里
https://blog.csdn.net/shine0181/article/details/17807345
对于这句话我有一定的怀疑。
注意：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的效率急剧下降！。
https://blog.csdn.net/mico_cmm/article/details/53446902
对此我也做了一下测试
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统计功能的实现
https://blog.csdn.net/koli6678/article/details/79841153
为操作我们在学习c语言基础的时候就接触过，但是位操作意义不明显，不直观（虽然规则很简单）
文中对“1<<day” 的解释让我很迷惑。
此处针对1<<day进行解释：这里的计数采取的是位图法，每天有访问就用1表示，02天表示100，23表示1000..(23个0）。
最后经过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<<day)) as days from t1 group by year,month;
以及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<<qygm)) from t_financial_bak group by hydm
用时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！！
