mongoDB、redis、Mysql的count、limit性能比较与分析

xian123

　　现在的业务需求是希望根据一具体给定的排行值查询出该值在按照大小排序后的排行系统中的排名。例如，下表存储了4条排行榜记录。

biz	type	key	value
成绩排行	语文	张三	90
成绩排行	语文	李四	80
成绩排行	语文	王二	70
成绩排行	语文	麻子	60

　　简单地说，若需要获取50分在“成绩排行”+“语文”中的排名，则SQL语句为：select count(*) from test where value>50，则可计算到50分的排名位置为5。
　　由此，排行系统的主业务场景大都是count(*)和limit的offset。
　　但是对实时排行榜系统进行了压力测试，发现在大数据量下mongoDB的count和skip的查询性能很慢，成为了该排行系统性能的一个瓶颈点。之后在寻找优化方案过程中接触并测试了些关于mongoDB、mysql、redis的count、limit性能。
1 mongoDB、mysql、redis的count和limit总体比较
1.1 环境说明
　　CPU:4核
　　内存：4G
1.2 压测比较结果
　　分别向MongoDB、Mysql和Redis中插入500W数据，数据结构为string bizType, string subkey, int value，其中value是1～500W的自增int型数据。Mongo和Mysql存储中，分别为[bizType, subkey]和value字段建立索引。
　　场景1：模拟排行系统中为了获取value值为200000的排名的场景，在MongoDB和Mysql存储中对应SQL语句为select count(*) from test where value < 200000；在Redis存储中对应的操作为zRank可以获取排名位置。
　　场景2：模拟排行系统中为了分页来获取列表的场景，在MongoDB和Mysql存储中对应SQL语句为select * from test limit 100000, 1000，这里offset=100000，count=1000；在Redis存储中的操作为zRangeByScore来获取列表。
　　注：select * from test limit 100000, 1000这种offset很大的情况一般不太建议直接使用，这里是为了测试比较说明的作用。
　　对上述两种场景分别在MongoDB、Mysql、Redis中进行了压力测试，结果如下表所示。
　　
　　其中表格中N表示表或集合中元素个数，M表示返回的元素个数。
　　从上述表格中可以看出，Redis存储比较适合根据值大小获取排名的场景。
　　下面则对MongoDB和Mysql使用explain执行计划命令查看上述count和limit场景下的具体执行过程以及分析此场景下为什么性能不够。
2 MongoDB的性能分析
　　现采用explain命令来查看执行计划，也就是通过explain来获得mongo如何执行select语句的信息，其返回的主要字段说明:

• cursor: 返回游标类型(BasicCursor 或 BtreeCursor)
  
• nscanned: 被扫描的文档数量
  
• n: 返回的文档数量
  
• millis: 耗时(毫秒)
  
• indexBounds: 所使用的索引
  

2.1 count性能
　　现在MongoDB表中有500W数据，数据结构为string bizType, string subkey, int value，其中value是1～500W的自增int型数据，且在字段[bizType, subkey]和value上建立索引。
　　现需要查找出小于200000值的数据个数，则mongoDB命令为db.rank.find({‘value’:{‘$lt’:200000}})。执行该命令，并使用explain查看执行过程，如下图所示。

从该图中indexBounds可以看出，该命令使用了索引字段value。另外返回值中nscanned=199999和n=199999可以看出，mongo预执行该find命令，预期扫描索引199999次，以及实际返回的记录条数为199999。由此看出，此种情况下value的大小位置决定了该命令的执行条数。
2.2 limit性能
　　MongoDB表中仍然是上述2.1中的500W数据。现应用系统为了实现分页，需要取出第10W条数据之后的1000条数据，则mongoDB命令表示为db.rank.find().limit(1000).skip(100000)。执行该命令，并使用explain查看执行过程，如下图所示。

从该图中indexBounds可以看出，该命令未使用任何索引，故该操作是基于表扫描。返回值nscanned=101000和n=101000说明mongo执行该find命令，预期扫描表记录101000次，以及实际返回的记录条数为101000。由此看出，此种情况下limit的offset+count的值决定了该命令的扫描次数。
当数据量很小时，这样做没什么问题，但是当数据量很大时，skip操作会逐条遍历offset前面的记录，这样会变得很慢，因此应该严格避免特别大的skip操作。
3. Mysql的性能分析
　　在Mysql命令执行中，同样可以采用explain命令来查看执行计划，来获取mysql如何执行select语句的信息。EXPLAIN返回的列字段解释如下。
　　l  table：显示这一行的数据是关于哪张表的。l  type：这是重要的一列，显示使用了何种联接类型。联接类型有const、range、
index和ALL等。const表示表最多有一个匹配行，const表很快。range表示只检索给定范围的行，使用一个索引来选择行。ALL表示全表进行扫描，效率最差。l  possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
　　l  key：实际使用的索引。如果为NULL，则没有使用索引。
　　l  key_len：使用的索引的长度。
　　l  ref：显示索引的哪一列被使用了。
　　l  rows：显示MySQL认为它执行查询时必须检查的行数。
3.1 MyISAM存储引擎
　　现在Mysql表中有500W数据，表结构与数据结构如下图所示，其中value是1～500W的自增int型数据，且在字段[biz_type, subkey]和value上建立索引。

3.1.1 count性能
　　MySQL的count操作性能主要分不带where条件的count(*)和带where条件的count 。
1）不带where条件的count(*)

2）带where条件的count(*)命令
现需要从表中查找出value小于200000值的数据个数，则Mysql命令为select count(*) from test1 where value < 200000。执行该命令，并使用explain查看执行过程，如下图所示。

从表中key=value_index可以看出该命令使用了value_index索引, 以及从type=range可以看出执行时在索引中检索了给定范围的行，扫描索引的行数为200126，与SQL语句中的value值相关。
　　由上可知，MyISAM引擎时，COUNT命令，如果没有WHERE限制的话，MySQL直接返回保存有总的行数；而在有WHERE限制的情况下，且如果建立了索引，则需要对Mysql的全索引或者索引的一个范围扫描，这取决于扫描的范围。
3.1.2 limit性能
　　limit操作性能也分不带where条件的limit和带where条件的limit。
　　1） 不带where条件的limit语句性能
　　
从该图中可以看出，type=ALL表示进行完整的表扫描，扫描记录rows为4999999条。这样性能是相当慢的。
　　2） 带where < 100000这样条件的limit语句性能
　　
key=value_index表示使用了该索引，type=range表示在该索引表中检索给定范围的行，扫描条数为200126，与value值相关。
3.2 InnoDB性能
　　对InnoDB引擎数据库同样执行了MyISAM的以上操作，除了不带where条件的count(*)的执行过程有差别，其他两者执行一致。
    不带where条件的count(*)的执行结果如下图所示。

执行这条命令引擎对全索引进行了遍历，使用了primary主索引，扫描索引条数为5000235。
4   总结几点
　　1)  每个数据库存储都有自身的特性和各自适合的应用场景，比如本文讨论的Redis很适合排行计算的场景，所以设计一个系统时需要综合考虑每个存储的优缺点，尽量扬长避短。
　　2)  合理创建索引，一个合理的索引能够为查询性能带来质的提升。
　　对于limit的offset很大带来的查询性能差的问题，无论是MongoDB还是Mysql，几乎每个数据库都有这样的问题。所以应该尽量避免直接写很大的limit offset，比如可以考虑采用子查询先查出offset对应的那条记录，然后再使用limit 0，count。这样可以改善查询性能。