关于 mysql 索引讨论

[brader]

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

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

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

有大神指点下吗？

[Jooooooooo]

username 单独索引可以理解, type 这种字段从语义上讲单独建索引有区分度吗(如果有的话也可以建)

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

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

(把 number 建索引可以减少回表的思路我不太确定特定的 mysql 版本和引擎会不会有帮助, 而且按照你这个查询条件要建怎么样的一个索引呢? username type number 的联合索引?

[brader]

@Jooooooooo 数据库的话，我用的是最新版的 MariDB，
type 字段的话，我觉得你说的对，我也觉得 type 的辨识度不高，这个字段类型，总共就只有 4 种。
用 sum 统计是业务有实时需求，没办法。

[liprais]

如果你没有范围查询的话建个联合索引就行了

[sansanhehe]

由于左前缀原则，建议两个索引：username 和 type 联合索引，type 的单独索引（区别度不高的话可以不建）。
如果是 innodb 存储引擎的话，number 字段可以建个索引（ username+type+number 或者 username+number ）。
实际都试一下，explain 看看哪个效率高

[liuzhedash]

@sansanhehe #4
感觉 number 字段建索引应该不会对 sum 有任何效果。索引可以降低为了满足 where 条件需要检查的行数，但是不能加速 sum 这种聚合函数。

[bbao]

这个表，一个（ username,type ）就够用了；
1，type 字段大多重复内容，独立建立索引没什么用；
2，username 单独查和组合 type 查，都走索引；

[brader]

@liuzhedash 不是这样的哦，如果 number 没有索引，我觉得：通过 username 和 type 索引检索出来的数据，只包含了主键信息，这时候需要回表查询 number 的值，然后进行聚合统计。

[brader]

@liuzhedash 如果 number 有索引的话，就不需要回表了，会直接进行索引覆盖

[brader]

@bbao 嗯，我刚才试了一下，username+type 的复合索引，效果非常好

[brader]

@sansanhehe 请问下，如果要实现 number 触发索引覆盖的话，单独给 number 建立索引是不是无效的？必须要建立复合索引（ username+type+number 或者 username+number ）？

[joyeu]

number 建索引没用吧？ username 和 type 索引筛选过后不就几条数据了么？然后根据主键读取每条记录。难道索引存在需要的字段就不需要读取整条记录了？记得二级索引的机制没有这种，待确认。

[brader]

@joyeu 我刚用 EXPLAIN 测试了一下，单独给 number 建立索引是没有用的，还是需要回表，如果在复合索引里加上，是有效果的，username+type+number，这时候 Extra 给出的信息是 Using index，说明进行了索引覆盖。
但是我试到的查询时间的差别微乎其微，我猜想是：username+type 索引从大量数据中筛选出的数据量已经很小了，然后回表操作查询具体数据，花不了多少时间。
虽然差别小，但这确实是更优的选择，因为你不保证你以后会不会出现：username+type 筛选后，数据量仍然很多的情况

[brader]

@joyeu 另外想说的是，复合索引加上 number 字段，又会增加索引维护的成本，至于是维护成本高了，还是节省的查询时间多，就需要自己根据业务去具体考量了，所以说这个没有唯一的标准，适合自己的才是最好的

[joyeu]

@brader 你怎么知道复合索引加上 number 就没有回表，Using index 只表示筛选阶段查主键时候用索引吧？

[brader]

@joyeu 不是的，两个情况我都用 EXPLAIN 测试过了，只有加上 number 的时候，会出现 Using index 提示

[joyeu]

@brader 哦哦，你说的应该是对的，查了下这个叫索引覆盖。

[index90]

explain 一下就知道啦

[brader]

@joyeu 就我自己的业务情况而言，我刚才做了查询时间测试，（ username+type+number 和 username+number ）的查询时间平均为（ 0.11s 和 0.034s ）

[brader]

@joyeu 抱歉，结果搞反了，是（ 0.034s 和 0.11s ）

[brader]

@joyeu 索引是 （ username+type+number 和 username+type ）