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

关于 mysql 索引讨论

  •  
  •   brader · 2020-03-16 15:19:18 +08:00 · 4060 次点击
    这是一个创建于 1711 天前的主题,其中的信息可能已经有所发展或是发生改变。

    SELECT SUM(number) FROM transfer WHERE username = 888888 AND type = 818 LIMIT 1;

    transfer表数据量较大,类似语句经常使用,但是username字段和type字段 也经常会出现单独使用的情况。

    那么关于索引建立: 是否应该放弃复合索引?转而给usernametype单独建立索引? number作为统计字段,应该给它建立索引吗?,是否利大于弊?(number建立索引考虑:能减少回表操作,上述语句能触发索引覆盖,这点是个人见解,不知道是否理解的正确)

    有大神指点下吗?

    24 条回复    2020-03-17 13:21:45 +08:00
    Jooooooooo
        1
    Jooooooooo  
       2020-03-16 15:25:27 +08:00   ❤️ 1
    username 单独索引可以理解, type 这种字段从语义上讲单独建索引有区分度吗(如果有的话也可以建)

    等于是建两个索引, username 单独一个, (type, username) 联合一个. 覆盖两个单独查询和联合查询的场景.

    至于 sum 这种操作, 实时性要求不是特别高的话还是离线异步跑吧, 直接用 mysql 做这种实时操作是不是太浪费资源了.

    (把 number 建索引可以减少回表的思路我不太确定特定的 mysql 版本和引擎会不会有帮助, 而且按照你这个查询条件要建怎么样的一个索引呢? username type number 的联合索引?
    brader
        2
    brader  
    OP
       2020-03-16 15:32:34 +08:00
    @Jooooooooo 数据库的话,我用的是最新版的 MariDB,
    type 字段的话,我觉得你说的对,我也觉得 type 的辨识度不高,这个字段类型,总共就只有 4 种。
    用 sum 统计是业务有实时需求,没办法。
    liprais
        3
    liprais  
       2020-03-16 15:34:33 +08:00
    如果你没有范围查询的话建个联合索引就行了
    sansanhehe
        4
    sansanhehe  
       2020-03-16 16:15:24 +08:00
    由于左前缀原则,建议两个索引:username 和 type 联合索引,type 的单独索引(区别度不高的话可以不建)。
    如果是 innodb 存储引擎的话,number 字段可以建个索引( username+type+number 或者 username+number )。
    实际都试一下,explain 看看哪个效率高
    liuzhedash
        5
    liuzhedash  
       2020-03-16 16:49:04 +08:00
    @sansanhehe #4
    感觉 number 字段建索引应该不会对 sum 有任何效果。索引可以降低为了满足 where 条件需要检查的行数,但是不能加速 sum 这种聚合函数。
    bbao
        6
    bbao  
       2020-03-16 17:06:02 +08:00
    这个表,一个( username,type )就够用了;
    1,type 字段大多重复内容,独立建立索引没什么用;
    2,username 单独查和组合 type 查,都走索引;
    brader
        7
    brader  
    OP
       2020-03-16 17:17:34 +08:00
    @liuzhedash 不是这样的哦,如果 number 没有索引,我觉得:通过 username 和 type 索引检索出来的数据,只包含了主键信息,这时候需要回表查询 number 的值,然后进行聚合统计。
    brader
        8
    brader  
    OP
       2020-03-16 17:18:34 +08:00
    @liuzhedash 如果 number 有索引的话,就不需要回表了,会直接进行索引覆盖
    brader
        9
    brader  
    OP
       2020-03-16 17:19:12 +08:00
    @bbao 嗯,我刚才试了一下,username+type 的复合索引,效果非常好
    brader
        10
    brader  
    OP
       2020-03-16 17:20:52 +08:00
    @sansanhehe 请问下,如果要实现 number 触发索引覆盖的话,单独给 number 建立索引是不是无效的?必须要建立复合索引( username+type+number 或者 username+number )?
    joyeu
        11
    joyeu  
       2020-03-16 18:28:23 +08:00
    number 建索引没用吧? username 和 type 索引筛选过后不就几条数据了么?然后根据主键读取每条记录。难道索引存在需要的字段就不需要读取整条记录了?记得二级索引的机制没有这种,待确认。
    brader
        12
    brader  
    OP
       2020-03-16 18:43:10 +08:00
    @joyeu 我刚用 EXPLAIN 测试了一下,单独给 number 建立索引是没有用的,还是需要回表,如果在复合索引里加上,是有效果的,username+type+number,这时候 Extra 给出的信息是 Using index,说明进行了索引覆盖。
    但是我试到的查询时间的差别微乎其微,我猜想是:username+type 索引从大量数据中筛选出的数据量已经很小了,然后回表操作查询具体数据,花不了多少时间。
    虽然差别小,但这确实是更优的选择,因为你不保证你以后会不会出现:username+type 筛选后,数据量仍然很多的情况
    brader
        13
    brader  
    OP
       2020-03-16 18:45:53 +08:00
    @joyeu 另外想说的是,复合索引加上 number 字段,又会增加索引维护的成本,至于是维护成本高了,还是节省的查询时间多,就需要自己根据业务去具体考量了,所以说这个没有唯一的标准,适合自己的才是最好的
    joyeu
        14
    joyeu  
       2020-03-16 18:58:02 +08:00
    @brader 你怎么知道复合索引加上 number 就没有回表,Using index 只表示筛选阶段查主键时候用索引吧?
    brader
        15
    brader  
    OP
       2020-03-16 19:01:53 +08:00
    @joyeu 不是的,两个情况我都用 EXPLAIN 测试过了,只有加上 number 的时候,会出现 Using index 提示
    joyeu
        16
    joyeu  
       2020-03-16 19:07:32 +08:00
    @brader 哦哦,你说的应该是对的,查了下这个叫索引覆盖。
    index90
        17
    index90  
       2020-03-16 19:07:43 +08:00
    explain 一下就知道啦
    brader
        18
    brader  
    OP
       2020-03-16 19:16:47 +08:00
    @joyeu 就我自己的业务情况而言,我刚才做了查询时间测试,( username+type+number 和 username+number )的查询时间平均为( 0.11s 和 0.034s )
    brader
        19
    brader  
    OP
       2020-03-16 19:22:03 +08:00
    @joyeu 抱歉,结果搞反了,是( 0.034s 和 0.11s )
    brader
        20
    brader  
    OP
       2020-03-16 19:23:14 +08:00
    @joyeu 索引是 ( username+type+number 和 username+type )
    scriptB0y
        21
    scriptB0y  
       2020-03-16 20:18:16 +08:00
    在没 Explain 的情况下,我尝试分析下:

    - username, type, number 索引,针对第一条 SELECT 语句,这样应该光在索引里面就得到结果了;
    - type 单独建索引,虽然用 type 查的话区别不大了,filter 会消耗大头的时间, 但是有跟没有还是应该有区别的;
    - username 不需要建索引,使用 username 可以走第一条索引;

    最靠谱的办法是看 Explain 分析一下,第二靠谱的办法是压测一下。不同的数据库的优化器可能有不同的选择。

    分享下 https://use-the-index-luke.com/sql/table-of-contents 看完这个对索引基本就明白了。

    嫌长可以看下我的笔记:

    1. https://www.kawabangga.com/posts/3893
    2. https://www.kawabangga.com/posts/3915
    qyvlik
        22
    qyvlik  
       2020-03-16 21:43:45 +08:00
    可以看看 索引覆盖 和 索引下推 这两个特性,mysql5.6 或以上版本有。MariDB 就不太清楚。

    可以做个简单的统计,然后按照查询频次,选择建立合适的索引。例如:
    1. 查询条件单独使用 username 的次数
    2. 查询条件单独使用 type 的次数
    3. 其他条件

    此外可以考虑加个时间戳,这样就可以按照时间段,分段存储统计过的数据,减少不必要的查询。
    sansanhehe
        23
    sansanhehe  
       2020-03-17 09:25:53 +08:00
    @brader mysql 查询时有多个索引只选择最优的一个,所以联合索引生效的情况下,单独的 number 索引应该是不生效的
    Aresxue
        24
    Aresxue  
       2020-03-17 13:21:45 +08:00
    6 楼正解, 只建立一个(username,type)的联合索引即可
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5763 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 01:52 · PVG 09:52 · LAX 17:52 · JFK 20:52
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.