Distinct下推(v1.21-2021-11-30)

Distinct下推是指

select distinct .....
或者
select ... group by ....语法下推
而非
count distinct函数,当然count distinct在一些情况也会被优化为select distinct

SELECT DISTINCT *  from db1.distinct_sharding//distinct_sharding是分片表
生成sq
SELECT `distinct_sharding`.`id`, `distinct_sharding`.`user_id`, `distinct_sharding`.`traveldate`, `distinct_sharding`.`fee`, `distinct_sharding`.`days`, `distinct_sharding`.`blob` 
FROM db1_1.sharding_0 AS `distinct_sharding` 
GROUP BY `distinct_sharding`.`id`, `distinct_sharding`.`user_id`, `distinct_sharding`.`traveldate`, `distinct_sharding`.`fee`, `distinct_sharding`.`days`, `distinct_sharding`.`blob`

SELECT DISTINCT id  from db1.distinct_sharding//id是分片键,不是主键
生成sql
SELECT `distinct_sharding`.`id` 
FROM db1_0.sharding_0 AS `distinct_sharding` GROUP BY `distinct_sharding`.`id`

SELECT DISTINCT traveldate  from db1.distinct_sharding//traveldate不是分片键
生成sql
SELECT `distinct_sharding`.`traveldate` 
FROM db1_1.sharding_0 AS `distinct_sharding` 
GROUP BY `distinct_sharding`.`traveldate`
MycatHashAggregate(group=[{0}])   MycatView(distribution=[[db1.distinct_sharding]])

SELECT DISTINCT user_id  from db1.distinct_sharding
//user_id带有唯一索引,但不是分片键也不是主键
生成sql
SELECT `distinct_sharding`.`user_id` FROM db1_1.sharding_0 AS `distinct_sharding`
//MycatView(distribution=[[db1.distinct_sharding]])

SELECT user_id,id  from db1.distinct_sharding group by user_id,id
//user带唯一索引,id是分片键
生成sql
SELECT `distinct_sharding`.`user_id`, `distinct_sharding`.`id` 
FROM db1_1.sharding_0 AS `distinct_sharding` 
GROUP BY `distinct_sharding`.`user_id`, `distinct_sharding`.`id`
//MycatView(distribution=[[db1.distinct_sharding]])

SELECT count(*)  from db1.distinct_sharding group by user_id //user带唯一索引
生成sql
SELECT `distinct_sharding`.`user_id`, COUNT(*) AS `count(*)` 
FROM db1_1.sharding_0 AS `distinct_sharding` 
GROUP BY `distinct_sharding`.`user_id` 
MycatProject(count(*)=[$1])   
MycatHashAggregate(group=[{0}], count(*)=[$SUM0($1)])     
MycatView(distribution=[[db1.distinct_sharding]])

mycat2会对配置中的建表语句进行分析然后对执行计划优化,对于主键和带有唯一索引的字段会视为唯一的键.注意分片键可能并不是唯一的键.