V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
RickyC
V2EX  ›  MySQL

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

  •  
  •   RickyC · 2020-09-22 16:41:01 +08:00 · 2072 次点击
    这是一个创建于 1508 天前的主题,其中的信息可能已经有所发展或是发生改变。
    SELECT count(*) FROM TABLE WHERE A=1 OR B=2

    这样的
    我需要得到总数

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

    单表千万条数据的
    18 条回复    2020-09-23 17:32:13 +08:00
    bay10350154
        1
    bay10350154  
       2020-09-22 16:55:50 +08:00
    UNION ALL
    RickyC
        2
    RickyC  
    OP
       2020-09-22 17:06:23 +08:00
    @bay10350154
    由于是千万级的表, 即使使用索引 SELECT 出单一条件的所有数据, 也需要很久

    UNION ALL 只能用于数据, 而不能用于 count(*)吧?
    limboMu
        3
    limboMu  
       2020-09-22 18:58:45 +08:00
    分情况,如果 A=1 or B=2 的数据在整个数据集的占比比较小,可以把,两个字段合并成一个字段,加索引优化。如果 数据集占比比较大的可以考虑引入缓存来计数,不过这样要考虑缓存和数据库计数的一致性。
    limboMu
        4
    limboMu  
       2020-09-22 19:02:48 +08:00
    接上,第一种情况如果 A B 字段数据占比比较理想的话,直接拆开用 UNION ALL 分别走索引查询也可以
    cqxxxxxxx
        5
    cqxxxxxxx  
       2020-09-22 19:31:57 +08:00 via Android
    我记得建立 a b 的组合索引对 count 查询即使用了 or 也会生效吧
    icql
        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
    RickyC
        7
    RickyC  
    OP
       2020-09-22 21:17:18 +08:00
    @icql A=1 和 B=2 里有重复的呀, 直接相加得到的是不正确的结果
    RickyC
        8
    RickyC  
    OP
       2020-09-22 21:18:09 +08:00
    @cqxxxxxxx
    目前看来 where a=1 or a=2 这样的走索引
    而 where a=2 or b=2 这样的不走索引, 即使是 count(*)
    RickyC
        9
    RickyC  
    OP
       2020-09-22 21:18:56 +08:00
    @cqxxxxxxx 组合索引也不好使
    RickyC
        10
    RickyC  
    OP
       2020-09-22 23:03:02 +08:00
    群里一位大哥给了个答案

    先查 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
        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
    wangritian
        12
    wangritian  
       2020-09-23 00:01:58 +08:00
    A+B 的索引当然对 or B 不起作用了,需要单独对 B 加索引
    liprais
        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"
    RickyC
        14
    RickyC  
    OP
       2020-09-23 00:53:43 +08:00
    @liprais explain 的 rows 并不是统计个数吧? 和 count(*)结果相差太大
    taogen
        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
    zhangysh1995
        16
    zhangysh1995  
       2020-09-23 17:24:25 +08:00
    @liprais EXPLAIN 是近似结果,可能很离谱
    RickyC
        17
    RickyC  
    OP
       2020-09-23 17:31:18 +08:00
    命令: 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
        18
    liprais  
       2020-09-23 17:32:13 +08:00
    @zhangysh1995 看执行计划当然是看有没有命中索引啊?你觉得我贴执行计划是看啥?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1683 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 16:50 · PVG 00:50 · LAX 08:50 · JFK 11:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.