关于 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 ）

[scriptB0y]

在没 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]

可以看看 索引覆盖 和 索引下推 这两个特性，mysql5.6 或以上版本有。MariDB 就不太清楚。

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

此外可以考虑加个时间戳，这样就可以按照时间段，分段存储统计过的数据，减少不必要的查询。

[sansanhehe]

@brader mysql 查询时有多个索引只选择最优的一个，所以联合索引生效的情况下，单独的 number 索引应该是不生效的

[Aresxue]

6 楼正解, 只建立一个(username,type)的联合索引即可