mysql中bit_count和bit_or使用

在看参考手册的时候看到个案例
3.6.8 Calculating Visits Per Day

create table t1(year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
insert into t1 values(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
select * from t1;

QQ图片20180726203424.png

首先要说明以下几个函数
bit_count:
就是计算二进制里有多少个1
10的二进制是1010
bit_count(10) = 2
100的二进制是1100100
bit_count(123) = 3

bit_or:
按位或，就是如果两边一个位置上存在1，那这个位置就为1
拿上面10和100举例
10 | 100 转换为二进制就是 1010|1100100
1010
1100100

---

1101110

1<<day:
向左位移，向左位移几位，然后用0填补
比如day=2
1<<2 = 100
day=4
1<<4 = 10000

回到主题，现在要计算出图中每天的访问量，可以看到day列中是存在重复记录的，所以要去除，一般思路应该会写出以下SQL：

mysql> select year,month,count(0) from (select year,month,count(day) from t1 group by year,month,day) as tmp group by year,month;

这个的确可以得到正确值，但是官方参考文档里使用了更巧妙的算法

select year,month,bit_count(bit_or(1<<day)) from t1 group by year,month;

为了演示，现在将day设定为1，2，2，3好了，那就等于
bit_or(1<<1, 1<<2, 1<<2, 1<<3)
10 | 100 | 100 | 1000 => 110 | 100 | 1000 => 110 | 1000 => 1110
当110 | 100的时候，相同的值就不会发生变化，所以最后bit_count的结果就为3

插入2w条数据后，看下执行计划，第一种SQL执行两遍全表扫描

为了明确看到查询性能，我们启用profiling并关闭query cache：

SET profiling = 1;
SET query_cache_type = 0;
SET GLOBAL query_cache_size = 0;

image.png image.png image.png