精华 Mongodb索引实战
发布于 9 年前 作者 DoubleSpout 15551 次浏览 最后一次编辑是 8 年前 来自 分享

最近碰到这样的一个需求,一张酒店政策优惠表,我们要根据用户入住和离开的时间,计算一家酒店的最低价政策前10位,数据库表字段如下:

'hid':88,     酒店id
'date':20150530,  入住日期整形(不要纠结unix时间戳)
'enable':1,  政策是否启用
'price':100,  政策价格
'name':'abc',  政策名称
'position':'china',  酒店位置
'writeTime':datetime.datetime.now(),        写入时间

我们的查询语句也相对固定,都是这样的:

db.getCollection('hotels').find({"hid":88, "date":{"$gte":20150501, "$lte":20150510}, "enable":1}).sort({"price":1}).limit(10)

其中条件分为3个: 1、酒店 id :“hid”:88 2、date在某个区间里 3、enable启用为1,表示启用 排序条件是一个: 1、price正序排序

现在我往数据库插入10万条测试数据,插入脚本如下:

# -*- coding: utf-8 -*-
import pymongo
import json
import datetime,time
import sys
import copy
import sys, os
from multiprocessing import Process, Value, Array
from hashlib import md5
from random import choice, randint

def getTimestampFromDatetime(d=None):
	if d is None:
		d = datetime.datetime.now()
	return time.mktime(d.timetuple())

def md5Hash(str):
	m = md5()
	m.update(str)
	return m.hexdigest().upper()

def task():
	#10分之一的概率无法使用
	enableList = [1,1,1,1,1,1,1,1,1,0]
	dateList = []
	for i in range(31):
		dateInt = 20150501
		dateList.append(dateInt+i)

	mongoUri = 'mongodb://10.14.40.62:27017/hotel'
	all_data = {
		'hid':0,
		'date':0,
		'enable':0,
		'price':0,
		'name':'abc',
		'position':'china',
		'writeTime':datetime.datetime.now(),
	}
	tableName = 'hotels'
	client = pymongo.MongoClient(mongoUri, max_pool_size=100)
	db = client.hotel

	listData = []
	for i in range(100000):
		all_data['price'] = randint(100, 10000)
		all_data['enable'] = choice(enableList)
		all_data['date'] = choice(dateList)
		all_data['hid'] = randint(1, 100)
		listData.append(copy.copy(all_data))

	db[tableName].insert(listData)


if __name__ == '__main__':
	start = getTimestampFromDatetime()
	task()
	end = getTimestampFromDatetime()
	print('time: {0}s'.format(end-start))

一、不建任何索引查询: 我们执行如下语句,查看语句执行情况:

db.getCollection('hotels').find({"hid":88, "date":{"$gte":20150501, "$lte":20150510}, "enable":1}).sort({"price":1}).limit(10).explain()

我们看到结果:

"n" : 10,
"nscannedObjects" : 100000,
"nscanned" : 100000,
...
"scanAndOrder" : true,
...
"millis" : 200,

其中 n 表示最终返回的结果,nscannedObjects表示我们扫描了多少数据,scanAndOrder表示我们进行了扫描并排序的操作,这是非常消耗cpu和内存的。

从结果来看,我们对10万条数据进行了全表扫描,最终得出10条结果出来。显然这个方案我们不能接受,时间我们花费了200毫秒,这个速度如果上线应用,肯定是不行的。

二、对hid加上索引 我们很容易就想到,对hid加上索引,这样我们第一个结果hid的搜索就可以快速将酒店的索引返回缩小,于是我们创建酒店 hid 的索引,然后同样执行上述语句。 索引如下:

{
	"hid" : 1
}

结果如下:

"n" : 10,
"nscannedObjects" : 1024,
"nscanned" : 1024,
...
"scanAndOrder" : true,
...
"millis" : 58ms,

对比上述的结果,我们把200ms的查询通过hid索引一下优化到了58ms,从扫描全表10万条数据,修改为只扫描了1024条数据,同时我们的响应时间也下降到了58ms,我们是否可以再优化一下呢?

三、建立hid和date的联合索引 我们发现查询还有第二个参数,date作为时间范围的,所以我们建立一个联合索引,hid:1, date:1这是否可以更加快一些?索引如下:

{
	"hid" : 1,
	"date" : 1
}

结果如下:

"n" : 10,
"nscannedObjects" : 326,
"nscanned" : 326,
...
"scanAndOrder" : true,
...
"millis" : 6ms,

经过再次优化,这个查询一下就变成6ms返回,只扫描了326行数据了。但是我们只需要返回10条数据,扫描了300多行数据,是否可以再进行一次优化?

四、建立hid、date、enable的联合索引 我们发现查询条件还有第三个参数 enable,由于enable大约有10分之一的数据是我们不要的,就是未启用的政策,所以我们把enable字段也加到索引中,索引如下:

{
	"hid" : 1,
	"date" : 1,
	"enable" : 1
}

执行结果如下:

"n" : 10,
"nscannedObjects" : 291,
"nscanned" : 300,
...
"scanAndOrder" : true,
...
"millis" : 5ms,

这里nscanned和nscannedObjects不同,nscanned:300表示从数据库索引条目中搜索了300条数据,nscannedObjects表示在这300条中,出最终的10条记录,扫描了这300条中的291条。

根据上面的结果,我们通过索引又进一步优化了这个查询,但是还不满足,我是否可以再增加sort排序的索引来优化呢?

五、建立hid,date,enable,price联合索引 我们把排序的索引也加到联合索引中,看看还能否再进一步优化这个查询了,建立索引如下:

{
	"hid" : 1,
	"date" : 1,
	"enable" : 1,
	"price" : 1
}

同样的执行语句结果如下:

"n" : 10,
"nscannedObjects" : 291,
"nscanned" : 300,
...
"scanAndOrder" : true,
...
"millis" : 5ms,

我们发现,无论是 nscannedObjects 还是 nscanned,以及查询时间都没有任何帮助了,和之前一样了,似乎我们的优化已经完成了。

六、建立逆索引试试 因为我们的查询条件有一个date作为区间查询的,而最终我们要得到的是根据price排序的结果,所以我们这样建立索引,看看是否对我们的查询有所帮助:

{
	"hid" : 1,
	"price" : 1,
	"date" : 1,
	"enable" : 1
}

执行结果如下:

"n" : 10,
"nscannedObjects" : 10,
"nscanned" : 37,
...
"scanAndOrder" : false,
...
"millis" : 0ms,

看到结果令人满意,我们把成功的把一个原来200ms的查询优化到0ms了,我们从索引查找到37条记录保存在内存里,同时我们只扫描了其中的10条记录就把结果返回了。同时 scanAndOrder 这个字段也成为了false,表示我们没有做在内存里的扫描和排序操作,将会降低cpu和内存的消耗,我们的优化已经完成了。

不过需要指出一点,如果从写入性能来讲,可以考虑把 “enable” : 1 从索引中拿走,毕竟这个索引并不能很好的帮助我们大量减少筛选的数据。

总结一下: 对于这种查询条件有 $in, $gte 等的区间操作的,并且带有sort排序的查询,合理的索引的建立,如果有条件优化到 scanAndOrder 结果为false,将大大的提升我们的数据库性能和响应时间。

22 回复

学习了!

scanAndOrder 参数

mongod 3 以后版本 explain 参数就完全不一样了。 期待新版本的分析

大牛以后多点来

@DoubleSpout 如果 enable只有1和0两个值,确实没必要索引

可以試試dex 基本上自動化了上面的過程

@jiangzhuo 搜了搜,是这个:https://github.com/mongolab/dex

2014年5月开始就不更新了

学习了,摆事实说明索引的用处啊

@alsotang 大神过奖了,只是雕虫小技罢了~,mongodb官方博客就有这方面的优化建议

@chita 额,公司还不敢用3.0,相对2.6稳定些

@TigerSoaring 是的,主要为了说明命中率,真实情况没必要加这个索引的,不过扫描的数据条数会有一点点增加~

@DavidCai1993 本来索引就是干这个的呀~

@jiangzhuo 看了下,是用来分析mongodb慢日志用的~mtools也有类似的功能,我们用那个,也是python的

学习 看来得多研究研究索引了

@DoubleSpout ’date’:20150530, 入住日期整形(不要纠结unix时间戳) "不要纠结unix时间戳"这个是神马意思呢? 与存成日期类型在检索效率上有什么区别?

@TigerSoaring 因为酒店入住的时间只需要精确到天,无需时分秒,所以这样存理论上性能和空间都应该有优势

@DoubleSpout mongodb 3.0 现在应该还算稳定吧,数据量超大的集群可能不太清楚,升级也是困难

弱弱的提一句,对于检索的确索引会有很大助力,但是插入的话就会相应的减缓,而且对于一个doc 7个字段4个都加索引的话应该也是极限了。(没有泼冷水的意思)

@haozxuan 是这样的,这个文章只是举一个例子,真实情况下字段肯定不止7个,另外实际情况下enable索引我是去掉的,所以组合索引是3个。最后这个表读写比相差巨大,所以这样才会去这样优化~,插入的性能影响是可以接受

@DoubleSpout 恩恩,优化方法还是学习到了 赞一个

难道不是根据需求的查询条件直接完成最优的索引建立吗?需要这么费劲?当然,lz如果仅仅是测试并普及知识就另当别论了。

回到顶部