自定义字段MongoDb与Mysql 扩展表性能分析

1.  需求背景
        对于saas应用来说，不同的租户对于表单有不同的定义，为了满足租户的千人千面，自定义表单字段就成为的必不可少的功能。
2. 技术方案
        从传统数据库mysql到文档数据库mongo db,再到搜索引擎ES 以及列式存储数据库,可以有如下方案：

技术方案	优点	缺点
Mysql 预留扩展字段	简单,成本低,查询方便	只能处理有限字段,浪费资源
Mysql EAV模型	增删简单,无需考虑一致性问题，可以无限扩展	关联查询效率低下
Mysql + MongoDb	效率比EAV模型高	要引用ETL工具同步mysql和mongo数据,需要保证数据一致性问题
Mysql + ES	查询效率高，	一致性问题,多表查询不友好,比mongo方案复杂
列式存储clickhouse(startrocks)	列式存储查询效率高，节约资源，扩展性好	组内懂的人少，引用成本太高

        从上面表格列出的所有方案中折中考虑后，我们选择采用 Mysql EAV模型 或者 Mysql + MongoDb的方案,本文就对这两种方案的性能做一个对比从而确定最终采用的方案。

3. MongDb性能测试

    3.1 MongoDb 测试表信息

表名称	crm_demo_ext
测试总数据量	100W
表索引	排序字段
字段名称	字段类型	字段说明	是否扩展字段
_id	ObjectId	mongo自带id	否
id	Int64	表主键id(mysql主键id)	否
demo_name	String	名称	否
demo_desc	String	描述	否
demo_attr	String	扩展属性	否
demo_position	String	职位	否
demo_city	String	城市	否
demo_layout	String	布局	否
demo_create_at	ISODate	创建时间	否
demo_update_at	ISODate	更新时间	否
ext_user_name	String	扩展名称	是
ext_user_code	String	扩展编码	是
ext_six	String	扩展性别	是
ext_mobile	String	扩展手机号	是
ext_email	String	扩展邮箱	是
ext_position	String	扩展职位	是
ext_update_at	ISODate	扩展更新时间	是

      3.2  测试方案

          3.2.1  模糊查询 + 分页 + 不排序

Jemeter测试参数		100线程 连续测试10分钟
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
2957864次	18ms	32ms	37ms	48ms	1ms	239ms	4929.6/s

         3.2.2 模糊查询+分页+排序 + 排序字段无索引

Jemeter测试参数		100线程 连续测试10分钟, mongo server cpu突增100%
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
8327次	7146ms	7610ms	7727ms	7955ms	2135ms	8798ms	13.6/s

        3.2.3  模糊查询+分页+排序 + 排序字段有索引

Jemeter测试参数		100线程 连续测试10分钟
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
1571761次	35ms	57ms	64ms	81ms	2ms	203ms	2619.6/s

     3.3  Mongo db有无排序以及是否建立合适索引新能对比

         从图上可以看出在有排序并且排序字段建立索引时，无排序比有排序效率提升近一倍，吞吐量也高一倍，但是排序字段没有建立索引时，查询效率已经吞吐量显著降低，并且机器cpu直接打满100%，可见mongo db建立合适的索引也非常重要

4. Mysql 性能测试

   4.1 mysql主表信息

表名称(mysql 冗余字段表)	crm_demo
测试总数据量	100W
表索引	排序字段
字段名称	字段类型	字段说明	是否扩展字段
id	Int64	表主键id(mysql主键id)	否
demo_name	String	名称	否
demo_desc	String	描述	否
demo_attr	String	扩展属性	否
demo_position	String	职位	否
demo_city	String	城市	否
demo_layout	String	布局	否
demo_create_at	datetime	创建时间	否
demo_update_at	datetime	更新时间	否
demo_ext1	String	扩展字段1	是
demo_ext2	String	扩展字段2	是
demo_ext3	String	扩展字段3	是
demo_ext4	String	扩展字段3	是

  4.2 mysql冗余字段表测试方案

      4.2.1  模糊查询 + 分页 + 不排序

Jemeter测试参数		100线程 连续测试10分钟
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
1535106次	37ms	52ms	64ms	100ms	16ms	1017ms	2558.2/s

      4.2.2 模糊查询+分页+排序 + 排序字段无索引

Jemeter测试参数		100线程 连续测试10分钟, mongo server cpu突增100%
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
10329次	5832ms	6480ms	6696ms	7120ms	803ms	8895ms	17.1/s

      4.2.3 模糊查询+分页+排序 + 排序字段有索引

Jemeter测试参数		100线程 连续测试10分钟
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
1637125次	35ms	47ms	57ms	95ms	15ms	774ms	2728.6/s

   4.3 Mysql自定义字段表信息

表名称(自定义字段表)	crm_custom_field
测试总数据量	3000492
表索引	tenant_id + owner_id + ext_table 建立索引
字段名称	字段类型	字段说明	是否扩展字段
id	bigint	主键id	否
tenant_id	bigint	租户id	否
owner_id	bigint	所属人	否
ext_table	varchar	扩展表	否
ext_code	varchar	字段code	是
ext_name	varchar	字段名称	是
ext_type	varchar	字段类型	是
ext_length	int	长度	是
object_id	bigint	主表数据主键id	否
ext_code_value	varchar	字段值	是

    4.4 mysql自定义字段表测试方案

      4.4.1 模糊查询 + 分页 + 不排序

Jemeter测试参数		100线程 连续测试10分钟
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
861899次	68ms	90ms	110ms	166ms	33ms	951ms	1433.6/s

      4.4.2 模糊查询+分页+排序 + 排序字段无索引

Jemeter测试参数		100线程 连续测试10分钟, mongo server cpu突增100%
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
11055次	5439ms	6133ms	6358ms	6862ms	1635ms	9760ms	18.3/s

      4.4.3 模糊查询+分页+排序 + 排序字段有索引

Jemeter测试参数		100线程 连续测试10分钟
总样本	平均RT	90%RT	95%RT	99%RT	最小值	最大值	TPS
782842次	75ms	101ms	122ms	177ms	3ms	620ms	1304.6/s

5. Mysql 与 mongo db性能比较

      5.1 模糊查询 + 分页 + 不排序

         上图中最小值，平均值以及最大的意义不是很大，我们从90%，95%，99%来看，查询mongodb时的性能只有查询自定义字段表的1/3，只有仅仅查询主表的1/2。效率提升明显。

        查询mongoDb的吞吐量(tps):4929.6/s 也远远高于查询自定义字段表的1433.6/s和只查询主表的2558.2/s。

      5.2 模糊查询+分页+排序 + 排序字段无索引

         从图形来看，没有建立索引时效率都很低下，不可取

      5.3 模糊查询+分页+排序 + 排序字段有索引

        上图中最小值，平均值以及最大的意义不是很大，我们从90%，95%，99%来看，查询mongodb时的性能与查询字段冗余表表的性能相差不大，但是只有查询自定义表的1/2，效率提升也比较显著

        查询mongoDb的吞吐量(tps):2619.6/s 与查询字段冗余表的2728.6/s相差不大，但是远远高于查询自定义字段表的1304.6/s。

6. 总结

        从上面分析的结果来看，采用Mongo Db的性能时远优于Mysql EAV模型的，并且此文章里面没有涉及到扩展字段的排序问题，多表联合查询问题，考虑的话效率是更加低于Mongo Db方案，因此考虑到未来一年内的话采用mongo db的方案较优，当然 如果不考虑人工成本的话，迁移的列式存储(clickhouse)是更优方案。

       采用mongo db的方案，我们需要考虑mysql数据同步到mongo db，一般有两种方案，第一种是双写，第二种是采用cdc实时同步，不过两种方案都需要考虑到数据的一致性，此篇文章暂不涉及这些方案。