sql distinct的查询如何提高效率

Question

select COUNT(distinct a.oid) count from member a where  a.tid = '2001' AND a.type = 0  AND a.delete_at is null

执行是2.7s 库里有25w数据

select COUNT(a.id) count from member a where  a.tid = '2001' AND a.type = 0  AND a.delete_at is null

执行是0.7s

Answer 1

SELECT
    count(1 ) 
FROM
    ( SELECT DISTINCT a.oid FROM member a WHERE a.tid = '2001' AND a.type = 0 AND a.`delete_at` IS NULL  ) tmp;

最终解决的还是子查询的慢 还是没有解决

Answer 2

试试

SELECT COUNT(*) `count` FROM (
    SELECT DISTINCT `a`.`oid` FROM `member` `a` WHERE `a`.`tid` = '2001' AND `a`.`type` = 0 AND `a`.`delete_at` IS NULL
) `tmp`

另外记得给 oid 加个索引、给 tid, type 加个联合索引。