对于不同字段的 or 查询怎样建立所以查询速度快?

[RickyC]

SELECT count(*) FROM TABLE WHERE A=1 OR B=2

这样的
我需要得到总数

建立 A+B 的索引对于这种 OR 的查询好像不管用

单表千万条数据的

[bay10350154]

UNION ALL

[RickyC]

@bay10350154
由于是千万级的表, 即使使用索引 SELECT 出单一条件的所有数据, 也需要很久

UNION ALL 只能用于数据, 而不能用于 count(*)吧?

[limboMu]

分情况，如果 A=1 or B=2 的数据在整个数据集的占比比较小，可以把，两个字段合并成一个字段，加索引优化。如果 数据集占比比较大的可以考虑引入缓存来计数，不过这样要考虑缓存和数据库计数的一致性。

[limboMu]

接上，第一种情况如果 A B 字段数据占比比较理想的话，直接拆开用 UNION ALL 分别走索引查询也可以

[cqxxxxxxx]

我记得建立 a b 的组合索引对 count 查询即使用了 or 也会生效吧

[icql]

@RickyC 一楼的意思是 UNION ALL 后再把两个 count 再加一下吧。。。。你可以 sql 包一层 sum 一下或者代码里边加一下
SELECT count(*) FROM TABLE WHERE A=1
UNION ALL
SELECT count(*) FROM TABLE WHERE B=2

[RickyC]

@icql A=1 和 B=2 里有重复的呀, 直接相加得到的是不正确的结果

[RickyC]

@cqxxxxxxx
目前看来 where a=1 or a=2 这样的走索引
而 where a=2 or b=2 这样的不走索引, 即使是 count(*)

[RickyC]

@cqxxxxxxx 组合索引也不好使

[RickyC]

群里一位大哥给了个答案

先查 count(*) where a=1, 得 x
再查 count(*) where b=2, 得 y
再查 count(*) where a=1 and b=2, 得 z

然后用 x+y-z 就得到 where a=1 or b=2 的个数

需要 3 个索引: 单独 a 的, 单独 b 的, a 和 b 的

[liprais]

用 postgresql 随便找了个表试了试

只需要在 a,b 列上单独建索引就行，这是执行计划:

QUERY PLAN
Aggregate (cost=22119.36..22119.37 rows=1 width=8) (actual time=21.540..21.542 rows=1 loops=1)
-> Bitmap Heap Scan on a (cost=300.17..22081.89 rows=14988 width=0) (actual time=1.539..20.603 rows=15506 loops=1)
Recheck Cond: ((city_name = '北京'::text) OR (city_code = '120000'::text))
Heap Blocks: exact=3805
-> BitmapOr (cost=300.17..300.17 rows=15445 width=0) (actual time=1.153..1.154 rows=0 loops=1)
-> Bitmap Index Scan on index_a (cost=0.00..214.28 rows=11449 width=0) (actual time=0.806..0.806 rows=11534 loops=1)
Index Cond: (city_name = '北京'::text)
-> Bitmap Index Scan on index_b (cost=0.00..78.39 rows=3996 width=0) (actual time=0.346..0.346 rows=3972 loops=1)
Index Cond: (city_code = '120000'::text)
Planning Time: 0.128 ms
Execution Time: 21.566 ms

[wangritian]

A+B 的索引当然对 or B 不起作用了，需要单独对 B 加索引

[liprais]

mysql 8.0 是可以的
explain select * from test.mvcc where a = 1 or b = 2;
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,mvcc,,index_merge,"a,b","a,b","5,5",,24,100,"Using union(a,b); Using where"

[RickyC]

@liprais explain 的 rows 并不是统计个数吧? 和 count(*)结果相差太大

[taogen]

@RickyC 加索引 index (A, B) 后 OR 的查询管用。不信你贴一下 explain SELECT count(*) FROM TABLE WHERE A=1 OR B=2

[zhangysh1995]

@liprais EXPLAIN 是近似结果，可能很离谱

[RickyC]

命令: SELECT count(*) FROM table WHERE `a`=21 OR `b`=4301;

+----+-------------+--------------------+------------+-------------+---------------------+----------------+---------+------+--------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+-------------+---------------------+----------------+---------+------+--------+----------+-----------------------------------------------+
| 1 | SIMPLE | table | NULL | index_merge | index_a,index_b,index_ab | index_ab,index_b | 5,5 | NULL | 639711 | 100.00 | Using sort_union(index_ab,index_b); Using where |
+----+-------------+--------------------+------------+-------------+---------------------+----------------+---------+------+--------+----------+-----------------------------------------------+

----------------分隔符------------------------

命令: SELECT count(*) FROM table WHERE `a`=21 OR `b`=4301;
+----------+
| count(*) |
+----------+
| 690113 |
+----------+

1 row in set (2 min 23.63 sec)

----------------分隔符------------------------

-------------------------
您是说 explain 的 rows 639711 就是总条数吗?
但是和 count 的 690113 数量不同

@taogen

[liprais]

@zhangysh1995 看执行计划当然是看有没有命中索引啊？你觉得我贴执行计划是看啥?