关于 mysql 索引讨论

2020-03-16 15:19:18 +08:00
brader  brader

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

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

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

有大神指点下吗?

4134 次点击
所在节点   MySQL  MySQL
24 条回复
Jooooooooo
Jooooooooo
2020-03-16 15:25:27 +08:00
username 单独索引可以理解, type 这种字段从语义上讲单独建索引有区分度吗(如果有的话也可以建)

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

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

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

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/653270

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX