SELECT SUM(number) FROM transfer
WHERE username
= 888888 AND type
= 818 LIMIT 1;
transfer
表数据量较大,类似语句经常使用,但是username
字段和type
字段 也经常会出现单独使用的情况。
那么关于索引建立:
是否应该放弃复合索引?转而给username
和type
单独建立索引?
number
作为统计字段,应该给它建立索引吗?,是否利大于弊?(number
建立索引考虑:能减少回表操作,上述语句能触发索引覆盖,这点是个人见解,不知道是否理解的正确)
有大神指点下吗?
1
Jooooooooo 2020-03-16 15:25:27 +08:00 1
username 单独索引可以理解, type 这种字段从语义上讲单独建索引有区分度吗(如果有的话也可以建)
等于是建两个索引, username 单独一个, (type, username) 联合一个. 覆盖两个单独查询和联合查询的场景. 至于 sum 这种操作, 实时性要求不是特别高的话还是离线异步跑吧, 直接用 mysql 做这种实时操作是不是太浪费资源了. (把 number 建索引可以减少回表的思路我不太确定特定的 mysql 版本和引擎会不会有帮助, 而且按照你这个查询条件要建怎么样的一个索引呢? username type number 的联合索引? |
2
brader OP @Jooooooooo 数据库的话,我用的是最新版的 MariDB,
type 字段的话,我觉得你说的对,我也觉得 type 的辨识度不高,这个字段类型,总共就只有 4 种。 用 sum 统计是业务有实时需求,没办法。 |
3
liprais 2020-03-16 15:34:33 +08:00
如果你没有范围查询的话建个联合索引就行了
|
4
sansanhehe 2020-03-16 16:15:24 +08:00
由于左前缀原则,建议两个索引:username 和 type 联合索引,type 的单独索引(区别度不高的话可以不建)。
如果是 innodb 存储引擎的话,number 字段可以建个索引( username+type+number 或者 username+number )。 实际都试一下,explain 看看哪个效率高 |
5
liuzhedash 2020-03-16 16:49:04 +08:00
@sansanhehe #4
感觉 number 字段建索引应该不会对 sum 有任何效果。索引可以降低为了满足 where 条件需要检查的行数,但是不能加速 sum 这种聚合函数。 |
6
bbao 2020-03-16 17:06:02 +08:00
这个表,一个( username,type )就够用了;
1,type 字段大多重复内容,独立建立索引没什么用; 2,username 单独查和组合 type 查,都走索引; |
7
brader OP @liuzhedash 不是这样的哦,如果 number 没有索引,我觉得:通过 username 和 type 索引检索出来的数据,只包含了主键信息,这时候需要回表查询 number 的值,然后进行聚合统计。
|
8
brader OP @liuzhedash 如果 number 有索引的话,就不需要回表了,会直接进行索引覆盖
|
10
brader OP @sansanhehe 请问下,如果要实现 number 触发索引覆盖的话,单独给 number 建立索引是不是无效的?必须要建立复合索引( username+type+number 或者 username+number )?
|
11
joyeu 2020-03-16 18:28:23 +08:00
number 建索引没用吧? username 和 type 索引筛选过后不就几条数据了么?然后根据主键读取每条记录。难道索引存在需要的字段就不需要读取整条记录了?记得二级索引的机制没有这种,待确认。
|
12
brader OP @joyeu 我刚用 EXPLAIN 测试了一下,单独给 number 建立索引是没有用的,还是需要回表,如果在复合索引里加上,是有效果的,username+type+number,这时候 Extra 给出的信息是 Using index,说明进行了索引覆盖。
但是我试到的查询时间的差别微乎其微,我猜想是:username+type 索引从大量数据中筛选出的数据量已经很小了,然后回表操作查询具体数据,花不了多少时间。 虽然差别小,但这确实是更优的选择,因为你不保证你以后会不会出现:username+type 筛选后,数据量仍然很多的情况 |
13
brader OP @joyeu 另外想说的是,复合索引加上 number 字段,又会增加索引维护的成本,至于是维护成本高了,还是节省的查询时间多,就需要自己根据业务去具体考量了,所以说这个没有唯一的标准,适合自己的才是最好的
|
15
brader OP @joyeu 不是的,两个情况我都用 EXPLAIN 测试过了,只有加上 number 的时候,会出现 Using index 提示
|
17
index90 2020-03-16 19:07:43 +08:00
explain 一下就知道啦
|
18
brader OP @joyeu 就我自己的业务情况而言,我刚才做了查询时间测试,( username+type+number 和 username+number )的查询时间平均为( 0.11s 和 0.034s )
|
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 |
22
qyvlik 2020-03-16 21:43:45 +08:00
可以看看 索引覆盖 和 索引下推 这两个特性,mysql5.6 或以上版本有。MariDB 就不太清楚。
可以做个简单的统计,然后按照查询频次,选择建立合适的索引。例如: 1. 查询条件单独使用 username 的次数 2. 查询条件单独使用 type 的次数 3. 其他条件 此外可以考虑加个时间戳,这样就可以按照时间段,分段存储统计过的数据,减少不必要的查询。 |
23
sansanhehe 2020-03-17 09:25:53 +08:00
@brader mysql 查询时有多个索引只选择最优的一个,所以联合索引生效的情况下,单独的 number 索引应该是不生效的
|
24
Aresxue 2020-03-17 13:21:45 +08:00
6 楼正解, 只建立一个(username,type)的联合索引即可
|