为什么要使用联合索引

mysql中的一些查询规则

在一次查询中，MySQL只能使用一个索引， or 语句会导致可能使用多个。
在真实项目中，SQL语句中的WHERE子句里通常会包含多个查询条件还会有排序、分组等。
若表中索引过多，会影响INSERT及UPDATE性能，简单说就是会影响数据写入性能。因为更新数据的同时，也要同时更新索引。

原因

原因零：在and的查询语句中，一次查询只会走一个索引

以mongo为例子：

# 1. 来看这样一条查询
db.presale_day_platform_snick_goods_question.explain("executionStats").find({"shop_id": "59f452501a6b200895100003", date: {"$gte": 20191012, "$lte": 20191019}, "question_id": ObjectId("59f452501a6b200895100060")})

# 如果分别对shop_id, date, question_id 三个分别建立索引，在查询时只会走一个索引，我这里线上数据胜出的索引是（其他两个索引失效了）
"indexBounds": {
  "question_id": [
    "[ObjectId('59f452501a6b200895100060'), ObjectId('59f452501a6b200895100060')]"
  ]
}

# 如果对shop_id, date, question_id 三列建立联合索引，那么会走这个联合索引
"indexBounds" : {
"shop_id": [
    "[\"59f452501a6b200895100003\", \"59f452501a6b200895100003\"]"
  ],
"date": [
    "[20191019.0, 20191012.0]"
  ],
"question_id": [
    "[ObjectId('59f452501a6b200895100060'), ObjectId('59f452501a6b200895100060')]"
  ]
},


# 来看这样一条带排序的查询
db.mock.explain("executionStats").find({"day": "2019-10-19", "shop_id": ObjectId("59f452501a6b200895100091")}).sort({"num": -1})
# 如果有这样的联合索引，那么会走这个联合索引！
db.mock.createIndex({shop_id:1, day:-1, num: -1}, {background: true})

"indexBounds" : {
        "shop_id" : [
                "[ObjectId('59f452501a6b200895100091'), ObjectId('59f452501a6b200895100091')]"
        ],
        "day" : [
                "[\"2019-10-19\", \"2019-10-19\"]"
        ],
        "num" : [
                "[MaxKey, MinKey]"
        ]
},

# 注意在多个键上排序时（来自mongo文档）
# 1. 您可以指定在索引的所有键或者部分键上排序。但是，排序键的顺序必须和它们在索引中的排列顺序 一致 。例如，索引 { a: 1, b: 1 } 可以支持排序 { a: 1, b: 1 } 但不支持 { b: 1, a: 1 } 排序。

# 2. 此外，sort中指定的所有键的排序顺序(例如递增/递减）必须和索引中的对应键的排序顺序 完全相同, 或者 完全相反 。例如，索引 { a: 1, b: 1 } 可以支持排序 { a: 1, b: 1 } 和排序 { a: -1, b: -1 } ，但 不支持 排序 { a: -1, b: 1 } 。
# 例如
# 现在有两个索引
# db.mock.createIndex({shop_id:1, date:-1, num: -1, category: -1}, {background: true})
# db.mock.createIndex({shop_id:1, date:-1, num: -1}, {background: true})


# 这个会使用第一个索引
db.mock.explain("executionStats").find({"shop_id": ObjectId("59f452501a6b200895100008"), "date": 20191022}).sort({"num": 1, "category": 1})

# 这个会使用第二个索引
db.mock.explain("executionStats").find({"shop_id": ObjectId("59f452501a6b200895100008"), "date": 20191022}).sort({"num": 1, "category": -1})

原因一：一个顶三个

建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！

原因二：单次筛选

索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

原因三：覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

当一条查询语句符合覆盖索引条件时，sql只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O。提高效率。因为这里如果不是覆盖索引的话，还需要其他字段，那么就会拿着索引对应的id，去数据表里走随机I/O（类似于数组中查询其中list[i]），但是即使不用遍历，相对而言，随机I/O依然是一个相对费时的操作。

总结

当索引字段超过三个时，一定要考虑联合索引的可行性

参考

https://segmentfault.com/q/1010000000342176

http://hedengcheng.com/?p=577

http://www.mongoing.com/docs/tutorial/sort-results-with-indexes.html