1
bay10350154 2020-09-22 16:55:50 +08:00
UNION ALL
|
2
RickyC OP |
3
limboMu 2020-09-22 18:58:45 +08:00
分情况,如果 A=1 or B=2 的数据在整个数据集的占比比较小,可以把,两个字段合并成一个字段,加索引优化。如果 数据集占比比较大的可以考虑引入缓存来计数,不过这样要考虑缓存和数据库计数的一致性。
|
4
limboMu 2020-09-22 19:02:48 +08:00
接上,第一种情况如果 A B 字段数据占比比较理想的话,直接拆开用 UNION ALL 分别走索引查询也可以
|
5
cqxxxxxxx 2020-09-22 19:31:57 +08:00 via Android
我记得建立 a b 的组合索引对 count 查询即使用了 or 也会生效吧
|
6
icql 2020-09-22 20:02:29 +08:00
@RickyC 一楼的意思是 UNION ALL 后再把两个 count 再加一下吧。。。。你可以 sql 包一层 sum 一下或者代码里边加一下
SELECT count(*) FROM TABLE WHERE A=1 UNION ALL SELECT count(*) FROM TABLE WHERE B=2 |
8
RickyC OP |
10
RickyC OP 群里一位大哥给了个答案
先查 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 的 |
11
liprais 2020-09-22 23:35:11 +08:00
用 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 |
12
wangritian 2020-09-23 00:01:58 +08:00
A+B 的索引当然对 or B 不起作用了,需要单独对 B 加索引
|
13
liprais 2020-09-23 00:40:45 +08:00
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" |
15
taogen 2020-09-23 13:28:01 +08:00
@RickyC 加索引 index (A, B) 后 OR 的查询管用。不信你贴一下 explain SELECT count(*) FROM TABLE WHERE A=1 OR B=2
|
16
zhangysh1995 2020-09-23 17:24:25 +08:00
@liprais EXPLAIN 是近似结果,可能很离谱
|
17
RickyC OP 命令: 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 |
18
liprais 2020-09-23 17:32:13 +08:00
@zhangysh1995 看执行计划当然是看有没有命中索引啊?你觉得我贴执行计划是看啥?
|