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
silenceeeee
V2EX  ›  MySQL

请教一个 MySQL 查询问题

  •  
  •   silenceeeee · 2017-08-29 09:53:48 +08:00 · 4508 次点击
    这是一个创建于 2630 天前的主题,其中的信息可能已经有所发展或是发生改变。

    当一个查询中包含较多的”碰撞性很高的列”的时候,如何优化呢?

    比如有如下 SQL:

    SELECT * FROM tab WHERE status=1 AND is_onsale=1 AND type=1 AND is_recommand

    类似这样的查询,假设这里的查询条件的可能值都只有少数几个, 如 status 的值只能为 1,2,3。is_onsale 的值只能为 1 或 -1。

    27 条回复    2017-08-30 18:25:19 +08:00
    geeglo
        1
    geeglo  
       2017-08-29 10:45:03 +08:00
    你好像拼错单词了
    silenceeeee
        2
    silenceeeee  
    OP
       2017-08-29 11:03:32 +08:00
    @geeglo ...
    zjm947373
        3
    zjm947373  
       2017-08-29 11:05:12 +08:00
    …… where status in (1,2,3) and is_onsale in (1,-1)……
    silenceeeee
        4
    silenceeeee  
    OP
       2017-08-29 11:55:10 +08:00
    @zjm947373 可能我没有描述清楚,我的意思是 status 的值可能是 1 或者 2 或者 3,但是查询的时候是查的一个明确的条件,比如 status=1,或者 status=2
    cye3s
        5
    cye3s  
       2017-08-29 12:05:34 +08:00 via Android
    mysql 有 bitmap 索引么?
    zjm947373
        6
    zjm947373  
       2017-08-29 12:10:22 +08:00
    @silenceeeee 这个不就是 in 的作用么。。。至于查询条件不是应该由你的程序来生成的么,要查 1 或 2 生成的自然就是 in (1,2)了。(或者我真没看懂你到底是在问什么)
    silenceeeee
        7
    silenceeeee  
    OP
       2017-08-29 13:44:39 +08:00
    @zjm947373
    查询条件:WHERE status=xxx (查询的时候没有使用 OR 和 IN 的需求)我称之为“但是查询的时候是查的一个明确的条件 比如 status=1,或者 status=2 ”
    status 的值可能是 1 或者 2 或者 3:
    status 的值可以(且只能)是 1, 2, 3 的其中一项。
    finull
        8
    finull  
       2017-08-29 14:08:39 +08:00
    @cye3s mysql 没有 bitmap index
    @silenceeeee 这个只能对这些列索引了
    fcka
        9
    fcka  
       2017-08-29 14:20:03 +08:00 via Android
    这个表有多少万行?
    silenceeeee
        10
    silenceeeee  
    OP
       2017-08-29 14:42:12 +08:00
    @fcka 倒不是真的遇到这个问题了,就是来讨论讨论。
    @finull 好吧。
    kkeiko
        11
    kkeiko  
       2017-08-29 14:57:51 +08:00
    mysql 的组成中有一部分叫‘查询优化器’,类似编译优化,就是你输入的语句其实不一定是最终执行的语句,mysql 自己会进行优化。你的这个问题就直接该怎么写就怎么写呗,where ... and ... and ... 只要每列都建索引了,这么写就是最优方案了。
    floraX
        12
    floraX  
       2017-08-29 15:00:28 +08:00
    @kkeiko 只要每列都建索引了,这么写就是最优方案了。
    不是的
    and and and,最终 用到的,仅仅是一个索引,如果可以,弄联合索引,效率高很多。。
    kkeiko
        13
    kkeiko  
       2017-08-29 15:04:21 +08:00
    @floraX 可能是我表达问题,我的意思是说该查的列都建上索引就行了,当然具体你 where 一个条件可以建单列索引,多个条件可以建联合索引。核心观点是得有索引
    badttt
        14
    badttt  
       2017-08-29 18:37:06 +08:00
    按照问题中的查询,只要建了索引就是最大优化,建议了解下 MySQL 中的索引最左前缀匹配
    340244120
        15
    340244120  
       2017-08-29 19:30:39 +08:00 via Android
    一个字段建好索引后,接着又在另一个联合索引中作为最左的字段。或者这两步顺序反过来。这两种情况下字段会建立两次索引吗
    wintercoder
        16
    wintercoder  
       2017-08-29 21:08:00 +08:00
    借楼问,类似的,上面的 status 如果为 1 的值很多,为 2 的很少,为 3 的一般多,limit 20 去查 status=1 的话很快,查 2 很慢,除了索引 还有其他方案么,加索引的话跟已有一起拼成复合的还是单独建好
    wintercoder
        17
    wintercoder  
       2017-08-29 22:02:36 +08:00
    @wintercoder #16 额我这种情况区分度不高,建索引也没意义,还是会走全表- -
    zhx1991
        18
    zhx1991  
       2017-08-29 22:43:51 +08:00
    这种区分度不高的没什么办法, 怎么捣鼓都是全表扫描

    只能从别的角度下手, 比如事先算好数扔在缓存里
    akira
        19
    akira  
       2017-08-30 00:09:33 +08:00
    没有太好的办法优化的。
    最好是另外有一个字段可以上索引 并大幅减少数据。 例如时间维度?
    msg7086
        20
    msg7086  
       2017-08-30 00:58:56 +08:00
    recommand

    重新发号施令。
    stelpen
        21
    stelpen  
       2017-08-30 07:50:26 +08:00 via Android
    @finull 如果这几个值分布均匀索引也没啥效果,如果分布不均匀,查询的值是分布较少的那一部分,索引才有明显的效果
    stelpen
        22
    stelpen  
       2017-08-30 07:51:09 +08:00 via Android
    @wintercoder 说反了吧
    viakiba
        23
    viakiba  
       2017-08-30 08:47:36 +08:00
    explain 一下
    caijihui11
        24
    caijihui11  
       2017-08-30 08:58:54 +08:00
    不用调整了,这样就是最好的 sql 了 。
    DRcoding
        25
    DRcoding  
       2017-08-30 09:06:28 +08:00
    少年郎,你还是买本权威指南看看吧,你这单表查询优化无非是对查询项加索引。对于你说的“碰撞性很高的列”,你没有发现如果你第二次重复执行一条相同的查询语句,一般情况下速度会快很多嘛。
    silenceeeee
        26
    silenceeeee  
    OP
       2017-08-30 17:44:01 +08:00
    @340244120 会的 这两个索引的最左前缀是不一样的。
    340244120
        27
    340244120  
       2017-08-30 18:25:19 +08:00 via Android
    @silenceeeee 谢谢 不过这也略不智能啊
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2728 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 07:38 · PVG 15:38 · LAX 23:38 · JFK 02:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.