V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member Sign In
› 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
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 2239 days ago, the information mentioned may be changed or developed.
SELECT SUM(number) FROM transfer WHERE username = 888888 AND type = 818 LIMIT 1;
transfer表数据量较大，类似语句经常使用，但是username字段和type字段 也经常会出现单独使用的情况。
那么关于索引建立：
是否应该放弃复合索引？转而给username和type单独建立索引？
number作为统计字段，应该给它建立索引吗？，是否利大于弊？（number建立索引考虑：能减少回表操作，上述语句能触发索引覆盖，这点是个人见解，不知道是否理解的正确）
有大神指点下吗？
 |
1
Jooooooooo Mar 16, 2020  1
username 单独索引可以理解, type 这种字段从语义上讲单独建索引有区分度吗(如果有的话也可以建)
等于是建两个索引, username 单独一个, (type, username) 联合一个. 覆盖两个单独查询和联合查询的场景. 至于 sum 这种操作, 实时性要求不是特别高的话还是离线异步跑吧, 直接用 mysql 做这种实时操作是不是太浪费资源了. (把 number 建索引可以减少回表的思路我不太确定特定的 mysql 版本和引擎会不会有帮助, 而且按照你这个查询条件要建怎么样的一个索引呢? username type number 的联合索引? |
 |
2
brader OP @Jooooooooo 数据库的话，我用的是最新版的 MariDB，
type 字段的话，我觉得你说的对，我也觉得 type 的辨识度不高，这个字段类型，总共就只有 4 种。 用 sum 统计是业务有实时需求，没办法。 |
 |
3
F281M6Dh8DXpD1g2 Mar 16, 2020
如果你没有范围查询的话建个联合索引就行了
|
 |
4
sansanhehe Mar 16, 2020
由于左前缀原则，建议两个索引：username 和 type 联合索引，type 的单独索引（区别度不高的话可以不建）。
如果是 innodb 存储引擎的话，number 字段可以建个索引（ username+type+number 或者 username+number ）。 实际都试一下，explain 看看哪个效率高 |
 |
5
liuzhedash Mar 16, 2020
@sansanhehe #4
感觉 number 字段建索引应该不会对 sum 有任何效果。索引可以降低为了满足 where 条件需要检查的行数，但是不能加速 sum 这种聚合函数。 |
 |
6
bbao Mar 16, 2020
这个表，一个（ 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 Mar 16, 2020
number 建索引没用吧？ username 和 type 索引筛选过后不就几条数据了么？然后根据主键读取每条记录。难道索引存在需要的字段就不需要读取整条记录了？记得二级索引的机制没有这种，待确认。
|
|
12
brader OP @joyeu 我刚用 EXPLAIN 测试了一下，单独给 number 建立索引是没有用的，还是需要回表，如果在复合索引里加上，是有效果的，username+type+number，这时候 Extra 给出的信息是 Using index，说明进行了索引覆盖。
但是我试到的查询时间的差别微乎其微，我猜想是：username+type 索引从大量数据中筛选出的数据量已经很小了，然后回表操作查询具体数据，花不了多少时间。 虽然差别小，但这确实是更优的选择，因为你不保证你以后会不会出现：username+type 筛选后，数据量仍然很多的情况 |
|
13
brader OP @joyeu 另外想说的是，复合索引加上 number 字段，又会增加索引维护的成本，至于是维护成本高了，还是节省的查询时间多，就需要自己根据业务去具体考量了，所以说这个没有唯一的标准，适合自己的才是最好的
|
 |
17
index90 Mar 16, 2020
explain 一下就知道啦
|
|
18
brader OP @joyeu 就我自己的业务情况而言，我刚才做了查询时间测试，（ username+type+number 和 username+number ）的查询时间平均为（ 0.11s 和 0.034s ）
|
 |
21
scriptB0y Mar 16, 2020
在没 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 Mar 16, 2020
可以看看 索引覆盖 和 索引下推 这两个特性，mysql5.6 或以上版本有。MariDB 就不太清楚。
可以做个简单的统计，然后按照查询频次，选择建立合适的索引。例如： 1. 查询条件单独使用 username 的次数 2. 查询条件单独使用 type 的次数 3. 其他条件 此外可以考虑加个时间戳，这样就可以按照时间段，分段存储统计过的数据，减少不必要的查询。 |
|
23
sansanhehe Mar 17, 2020
@brader mysql 查询时有多个索引只选择最优的一个，所以联合索引生效的情况下，单独的 number 索引应该是不生效的
|
 |
24
Aresxue Mar 17, 2020
6 楼正解, 只建立一个(username,type)的联合索引即可
|
Select Language