把MySql当作列存数据库使用时多条件and查询如何实现?

可能是为了优化查询性能，也可能是做大数据的惯性思维，才会把MySql数据库当分析型数仓去使用吧。

本篇分享的内容是：当我们把MySql当作列存数据库（OLAP分析型数仓）使用时多条件and查询如何实现。

假设某个表有n列，那么把一行记录按列存储后就需要存约n-1行记录。同时，转为列存后每行都需要多出几列标记这一条记录属于哪个“行记录”，以及存储的是“行记录”哪一列的值。

以用户表为例，假设tb_row_user表结构如下。

那么转为列存后创建的表tb_column_user结构如下。

现在我们往表tb_row_user里面插入一条记录。

如果使用列存，则需要往表tb_column_user插入如下3条记录。

当然，我们还需要为列user_id、column_name、column_value分别创建索引。

现在我们需要查询同时满足年龄大于25岁且性别为男的所有用户的id，我们应该怎样写sql？现在条件只有两个，假如条件有5个，10个呢？

下面是笔者首先想到的一种方法：

select user_id,count(id) as cnt 
from tb_column_user where 
(column_name = 'age' and column_value > '25') 
or 
(column_name = 'sex' and column_value = '男')
group by user_id having cnt = 2;

如果把group by去掉，那么sql就是查询条件满足age大于25或者性别是男的用户，即。

select user_id from tb_column_user where 
(column_name = 'age' and column_value > '25') 
or 
(column_name = 'sex' and column_value = '男');

如果一个用户同时满足这两个条件，那么查询结果同个user_id就会有两条记录，否则如果只满足其中一个条件，那么查询结果这个user_id就只会有一条记录。比如：

所以我们按user_id分组统计得到的cnt就是匹配的条件个数，最后通过having就可以筛选出同时满足两个条件（having cnt = 2）的用户的id。

这种方法的sql更便于动态拼接，不过使用这个sql有个前提条件，要求tb_row_user表里面的每一行记录的每一列对应到tb_column_user表都只有一行记录（也可以没有）。

最终的SQL的执行计划如下：

由于使用了OR查询，因此Type为index，即全索引扫描(遍历索引树)，因此性能会随着数据量的增加而越来越慢。

另一种方法，使用join查询，sql如下：

select u1.user_id from tb_column_user as u1 inner join (
 select user_id from tb_column_user where column_name = 'sex' and column_value = '男'
) as u2 
where u1.user_id=u2.user_id and u1.column_name = 'age' and u1.column_value > '25';

这条sql的执行计划如下。

如果再加一个条件sql就会看得很头疼了：

select u1.user_id from tb_column_user as u1 inner join (
    select uu1.user_id from tb_column_user uu1 inner join(
        select user_id from tb_column_user where column_name = 'tag' and column_value = '穷'
    )uu2 
    where uu1.user_id=uu2.user_id and uu1.column_name = 'sex' and uu1.column_value = '男'
) as u2 
where u1.user_id=u2.user_id and u1.column_name = 'age' and u1.column_value > '25';

执行计划如下。

可以看出，使用join查询每个查询都能用到索引，并且外层查询扫描的行数总比内层查询扫描的行数少，经过了层层过滤，最终扫描的行数就是最内层的查询语句所扫描的行数。

总结

方法一：优点是sql清晰易读，缺点是性能差；

方法二：优点是性能相对更好，但缺点是条件越多sql看着越难理解。