Thursday, June 9, 2011

The Force.com Multitenant Architecture

最近为了解决一技术问题,使用了Force.com Multitenant Architecture,在此详细说明下Force.com 的架构,具体的白皮书可以从下面的URL获取:(http://wiki.developerforce.com/index.php/Multi_Tenant_Architecture)。
   
Force.com Multitenant 架构的最大亮点在于,将数据索引化,一个存储数据的表,一个用于索引数据的表,再加上一系列存储元数据的表;每次的业务数据的查询首先查找元数据表,然后再改写成具体的查询语句到具体的数据表和索引中进行联合查询,这样SQL语句的执行计划固定,它一定是一个数据表加上一个索引表的join操作,非常适合于OLTP系统,具体的表ER图如下:























上面的说明比较抽象,下面举个具体事例来说明相关技术。假设我们需要根据用户的firstname和lastname来获取某个用户的详细信息,通过Force.com Multitenant 架构,我们如何去做呢?

首先,我们定义元数据信息使用的表。
create table mt_tables
(
 tabid        int(9) not null,
 orgid        int(9) not null,
 tabname      varchar(30) not null,
 primary key(tabid)
) ENGINE=InnoDB  CHARSET=utf8;

create table mt_fields
(
 fieldid      int(9)  not null,
 orgid        int(9)  not null,
 tabid        int(9)  not null,
 fieldname    varchar(128) not null,
 fieldtype    varchar(128) not null,
 fieldnum     smallint     not null,
 indextype    char(1)      not null, -- u:unique or primary index,i-index,n-no index
 primary key(fieldid)
) ENGINE=InnoDB  CHARSET=utf8;
create index ind_to_mt_fields
on mt_fields
(tabid,orgid);
create unique index ind_uname_mt_tables
on mt_tables
(tabname,orgid);

其次,我们定义存储具体数据需要的表、建立查询索引需要的表和建立数据关系表。

create table mt_data
(
 guid         varchar(36)  not null, -- yyyymmdd+0000000001-9999999999
 orgid        int(9)       not null,
 tabid        int(9)       not null,
 name         varchar(128) not null,
 IsDeleted    char(1)      not null,
 value0       varchar(1000),
 ...
 value20      varchar(1000),
 primary key(guid,orgid)
) ENGINE=InnoDB  CHARSET=utf8;

create table mt_clobs
(
 guid         varchar(36)  not null, -- yyyymmdd+0000000001-9999999999,refer to mt_data.guid
 orgid        int(9)       not null,
 tabid        int(9)       not null,
 value0       text,
 ...
 value10      text,
 primary key(guid,orgid)
)ENGINE=InnoDB  CHARSET=utf8;

create table mt_indexes
(
   guid         varchar(36)  not null, -- yyyymmdd+0000000001-9999999999,refer to mt_data.guid
   orgid        int(9)       not null,
   tabid        int(9)       not null,
   fieldnum     smallint     not null,
   stringvalue  varchar(250),
   numvalue     varchar(40),
   datevalue    varchar(40),
   primary key(guid,orgid,tabid,fieldnum)
)ENGINE=InnoDB  CHARSET=utf8;
create index  ind_string_mt_indexes
on mt_indexes
(stringvalue,tabid,fieldnum);
create index  ind_date_mt_indexes
on mt_indexes
(datevalue,tabid,fieldnum);
create index  ind_num_mt_indexes
on mt_indexes
(numvalue,tabid,fieldnum);

create table  mt_unique_indexes
(
   guid         varchar(36)  not null, -- yyyymmdd+0000000001-9999999999,refer to mt_data.guid
   orgid        int(9)       not null,
   tabid        int(9)       not null,
   fieldnum     smallint     not null,
   stringvalue  varchar(224),
   numvalue     varchar(40),
   datevalue    varchar(40),
   primary key(guid,orgid,tabid,fieldnum)
)ENGINE=InnoDB  CHARSET=utf8;

create unique index uk_string_mt_unique_indexes
on mt_unique_indexes
(stringvalue,tabid,fieldnum);

create unique index uk_date_mt_unique_indexes
on mt_unique_indexes
(datevalue,tabid,fieldnum);

create unique index ind_num_mt_unique_indexes
on mt_unique_indexes
(numvalue,tabid,fieldnum);

create table  mt_relationships
(
   relationid   varchar(36)  not null, -- yyyymmdd+0000000001-9999999999 
   guid         varchar(36)  not null, -- yyyymmdd+0000000001-9999999999,refer to mt_data.guid
   orgid        int(9)       not null,
   tabid        int(9)       not null,
   targetTabid  int(9)       not null,
   primary key(relationid,orgid,tabid,targetTabid)
)ENGINE=InnoDB  CHARSET=utf8;

第三步,我们开始建立元数据与存储数据的关系,插入的数据为用户表数据,此时的用户表是一个逻辑上的表,存在于mt_tables中。

insert into mt_tables(tabid,orgid,tabname,createtime,modifytime)
values(1,1,'USER',sysdate(),sysdate());
commit;
set @tablid=1;
insert into mt_fields(fieldid,orgid,tabid,fieldname,fieldtype,fieldnum,indextype,createtime,modifytime)
values
(1,1,@tablid,'USERID','VARCHAR',0,'u',sysdate(),sysdate());

insert into mt_fields(fieldid,orgid,tabid,fieldname,fieldtype,fieldnum,indextype,createtime,modifytime)
values
(2,1,@tablid,'USERNAME','VARCHAR',1,'i',sysdate(),sysdate());
....
insert into mt_fields(fieldid,orgid,tabid,fieldname,fieldtype,fieldnum,indextype,createtime,modifytime)
values
(5,1,@tablid,'FIRST_NAME','VARCHAR',4,'n',sysdate(),sysdate());
...
insert into mt_fields(fieldid,orgid,tabid,fieldname,fieldtype,fieldnum,indextype,createtime,modifytime)
values
(7,1,@tablid,'LAST_NAME','VARCHAR',6,'n',sysdate(),sysdate());
commit;

第四步,在应用程序创建用户数据的时候根据元数据表信息,建立mt_data,mt_clobs,mt_indexes等相关表的关系数据。


最后,我们看下如何根据firstname和lastname获取用户的信息,SQL语句如下:

SELECT b.value0,value1,value2,value3,value4,value5,value6
           FROM mt_data b,
                (SELECT guid, orgid
                   FROM mt_indexes a
                  WHERE a.stringvalue = 'philip'
                    AND a.tabid = 1
                    AND a.fieldnum = 4
     ) c
          WHERE c.guid = b.guid
            AND c.orgid = b.orgid
            AND value6 = 'zhong'

整套架构的优点:

类似与oracle的数据字典+索引的机制,实现了一套通用的数据查询体制,与类似的行存储列方式的通用解决方案,数据的冗余程度很小,数据量也不会成倍增加,加上上层orgid的sharding,单个schema存储的数据量将不会很大,也能满足web应用的可扩展性,适用于MYSQL数据库,SQL执行计划非常固定。

缺点:
所有的SQL查询将是固定模式的join查询,但如果本身应用涉及的表join过多,那么查询的速度将下降,可以通过表的冗余措施降低表的jion复杂度。此外唯一性数据的查询速度有所降低。

下面是针对该架构在mysql5.1.55上进行的一些性能测试数据:
Case 1:
      MYSQL------ mt_data records= 9000415 ,
                              mt_index records =9000415,
Case 2:
      MYSQL------ mt_data records= 5000415,
                              mt_index records =5000415
Case 3:
      MYSQL------ mt_data records= 1000305,
                              mt_index1 records =1000305
Case 4:
      MYSQL------ mt_data records= 36630,
                              mt_index1 records =36630


Case name
MYSQL force.com SQL query
   Case 1
55 ms
Case 2
40 ms
Case 3
42 ms
  Case 4
38 ms

39 comments:

  1. 太长了,老大能不能在开始的时候用简单的语言描述一下,先不要贴代码?

    ReplyDelete
  2. 我把代码部分简化些,主要实现的还是类似于oracle的元数据的设计方式

    ReplyDelete
  3. 我有一点不明白,这个relation Index怎么实现双向查找呢?

    ReplyDelete
  4. @wang.lang,不太理解你的双向查找的含义,如果是指关联查询的话,可以通过mt_relationships关系表实现,也可以将关系冗余到数据中,获取完数据再筛选

    ReplyDelete
  5. 接着你这个例子,假如有另一个虚表App(APPID, OwnerID, Description),其中一个 OwnerID 是fk,引用USER中的USERID。
    这样的关系,两个表的metadata是什么呢?App插入(1, 1, "TEST)这样一个数据,Relations Table的数据是什么?

    ReplyDelete
  6. 不知道我这个表达你是否明白,
    假设虚表USER中有这样的记录(orgID:1; tabID:1; userid:1; FirstName: Hello; LastName "World"...)

    ReplyDelete
  7. 双向查找指,怎么根据RelationShips表中存的信息找到某个GUID数据中FK对应的另一个数据,然后就是反过来,这些数据怎么知道有谁引用他呢?

    ReplyDelete
  8. 按照上面的两个表,我来具体说明下:
    首先,插入mt_tables两条记录标志两个表tabid=1 and tabid=2:
    insert into mt_tables(tabid,orgid,tabname,createtime,modifytime)
    values(1,1,'USER',sysdate(),sysdate());

    insert into mt_tables(tabid,orgid,tabname,createtime,modifytime)
    values(2,1,'App',sysdate(),sysdate());

    其次,将表USER和APP的对应关系插入到表mt_fields,建立mt_data字段与USER和APP的字段对应关系。
    insert into mt_fields(fieldid,orgid,tabid,fieldname,fieldtype,fieldnum,indextype,createtime,modifytime)
    values
    (10,1,2,'APPID','VARCHAR',0,'i',sysdate(),sysdate());
    insert into mt_fields(fieldid,orgid,tabid,fieldname,fieldtype,fieldnum,indextype,createtime,modifytime)
    values
    (11,1,2,'OwnerID','VARCHAR',1,'i',sysdate(),sysdate());
    ....

    第三,插入数据到mt_data, mt_index,mt_relationships。
    比如:
    App插入(1, 1, "TEST);
    USER插入(userid:1; FirstName: Hello; LastName: World);
    a)从mt_tables和mt_fields获取表对应的mt_data的字段对应关系。App.APPID对应mt_data.value0,App.OwnerID对应mt_data.value2;USER.FIRST_NAME的mt_data.value4,USER.LAST_NAME对应mt_data.value6。
    b)插入App和USER的数据到mt_data中,比如:
    app的数据:
    guid=36位的唯一uuid
    orgid=1
    tableid=2
    value0=1
    value1=1
    根据需要索引的字段插入到mt_index表中
    user的数据:
    guid=36位的唯一uuid
    orgid=1
    tableid=1
    value0...
    value4='Hello'
    value6='World'
    根据需要索引的字段插入到mt_index表中
    c)插入数据到关系表中mt_relationships。
    app的数据:
    relationid=pk(建立关系的relationid相同)
    guid=步骤b中产生的app的guid
    orgid=1
    tabid=2
    targetTabid=1(user的表id)

    user的数据:
    relationid=pk(建立关系的relationid相同)
    guid=步骤b中产生的user的guid
    orgid=1
    tabid=1
    targetTabid=2(user的表id)

    最后,查询app的ownerid=1的user的firtname SQL:
    a)找到app和user对应的相同relationid。
    select relationid,guid,orgid,tabid
    from mt_relationships b,
    (SELECT guid, orgid
    FROM mt_indexes a
    WHERE a.stringvalue ='1'
    AND a.tabid = 2
    AND a.fieldnum = 1)c
    WHERE c.guid = b.guid
    AND c.orgid = b.orgid;
    b)根据步骤a获取的guid去获取user的信息。
    SELECT b.value4,value5,value6
    FROM mt_data b
    where b.guid=#步骤a获取的user的guid#
    and b.orgid=1

    如果查询user对应的app信息,也是同样的操作。

    ReplyDelete
  9. 我试验了一下
    如果要找appid=1的数据对应的username
    通过a得到appid=1的relationship
    在b中用这个guid去data表中查找,还是得到appid=1的数据
    而不会得到对应的username

    ReplyDelete
  10. sql上表示缺少一个步骤。
    a)找到app和user对应的相同relationid。
    select relationid,orgid,targetTabidTabid
    from mt_relationships b,
    (SELECT guid, orgid
    FROM mt_indexes a
    WHERE a.stringvalue ='1'
    AND a.tabid = 2
    AND a.fieldnum = 1)c
    WHERE c.guid = b.guid
    AND c.orgid = b.orgid;
    b)在根据relationid加上orgid从relationship找到guid,targetTabid,Tabid,有多条,可以根据你的需求剔除多出的数据。

    c)再根据步骤b获取的guid去获取user的信息。
    SELECT b.value4,value5,value6
    FROM mt_data b
    where b.guid=#步骤b获取的user的guid#
    and b.orgid=1

    ReplyDelete
  11. 相当感谢你耐心的回答!
    我发现这里a步骤的作用就是利用索引找出相关条件对应guid,然后用guid找relationship吧。这里b,c两步骤用的guid只是从索引找到的guid,本质上没用到relationship的关系的。

    ReplyDelete
  12. 没有relationship,两个不同的guid就不能建立关系,在relationship表中两个有关系的表,有两条记录,relationid相同,当guid和tableid以及targetTabid是不同,不知道这样说你能否理解?

    ReplyDelete
  13. 我明白你的意思了,是每一个有依赖关系的数据在relationship表中都有两个记录。用relationid来找对应的GUID.应该没错吧?
    这样在data table中没有存放任何relation的信息。

    不过这个与force.com white paper上的设计不太一样了,它是会为child-parent关系中的child obj增加一个relation type的field.
    你看那个paper上relationships pivot table这一节,有提到,不过比较少。google "The Developer's Guide to the Force.com Platform filetype:pdf" 这个手册的3rd chapter也有提到使用relation filed。

    ReplyDelete
  14. 不是这样的,你看下“The Developer's Guide to the Force.com Platform filetype:pdf”,有一个提示:
    Tip: You cannot create a master-detail relationship from a child object that contains
    data, since master-detail relationship fields are required; however, you can convert a
    lookup relationship to a master-detail relationship. If you want to add a master-detail
    relationship to an object with data, you can create a lookup relationship to the master,
    add a value for the relationship for all child records, and then convert that relationship
    to a master-detail relationship.
    在“Force.com_Multitenancy_WP”中关于Relationships Pivot Table也有详细定义,The Relationships index table has two underlying database unique composite indexes(OrgID+GUID, and OrgID+ObjID+RelationID+TargetObjID) that allow for efficient object traversals in either direction, as necessary。
    如果建立你描述那样的关系,就太复杂了。

    ReplyDelete
  15. 在我理解当中"The Developer's Guide to the Force.com Platform"这段话说明,在child obj中,对master-detail relationship而言,这个relation field的是非空的,而lookup relationship是允许为空的。

    不过插两个数据至relationship表,且relationID相同,可以很好解释OrgID+ObjID+RelationID+TargetObjID可以作为一个index使用。

    ReplyDelete
  16. 其实你从两个唯一索引上也能看出这样的架构的精妙之处(unique indexes(OrgID+GUID), and (OrgID+ObjID+RelationID+TargetObjID)),否则也不能称之为通用架构了。

    ReplyDelete
  17. 恩,非常巧妙的一个双向检索的办法,很让我大开眼界。
    非常感谢你耐心的帮助。

    ReplyDelete
  18. 我有一点不大明白,在步骤a
    a)找到app和user对应的相同relationid。
    select relationid,orgid,targetTabidTabid
    from mt_relationships b,
    (SELECT guid, orgid
    FROM mt_indexes a
    WHERE a.stringvalue ='1'
    AND a.tabid = 2
    AND a.fieldnum = 1)c
    WHERE c.guid = b.guid
    AND c.orgid = b.orgid;

    这个有可能返回多条relationshipid,因为owner_id=1的app可能有多条。

    因此,通过相同的relationshipid从relation表反向拿到的user guid是否也有可能有多个呢?

    在我们这个情况下,确实是一个,因为我们默认app只有一个relationship,那就是app和user,但是如果app与其他table有多个relation呢,比如app有另外一个fk,比如category_id,那么反向找到的GUID或许就得到的事category的内容,而不是user.

    ReplyDelete
  19. @JamsonBao,
    1)原始一条记录的关系数据,在此架构中,用两条记录表示,相同的relationshipid。
    2)如果用很多条关系数据,就是步骤1)的多次循环。(可以一条SQL出来所有数据)
    3)这样出来的guid就是多条,再从data表中获取数据,反向和顺向的逻辑相同。
    关于多个表的关系,逻辑如下:
    以三个表为例:
    1)原始一条记录的关系数据,在此架构中,用三条记录表示,相同的relationshipid,由于是三个表,因此tableid和targetTabid均不同。
    因此依然可以根据不同的tableid、relationshipid加上orgid然后能够找到不同关系的guid,在data表中也有tableid,因此能够区分。

    ReplyDelete
  20. 我觉得为这个relationship表加一个TargetGUID的话可以加快查询的效率~

    ReplyDelete
  21. @Wang, Han,如果加TargetGUID,三个表的jion或者三个以上的表的关系就必须要多加很多字段,这样就不适合common的原则了。如果你的应用中所有的查询一定只有两个表jion的话,你可以这样做

    ReplyDelete
  22. 多表join查询本质还是两表的吧。
    可以插入多条relation数据啊。怎么说是加很多字段呢?

    ReplyDelete
  23. @Wang, Han,如果你还是多条记录的话,就没有必要再加TargetGUID了。因为你首先必须获得relationshipid,你无法提前知道TargetGUID,一旦你知道了relationshipid,才能获取具体的guid,而此时一个relationshipid下的所有关系的guid都回获取。

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. 你的意思应该是一个relationshipid会对应一个GUID与其他很多GUID之间的关系。明白了~之前我举的例子里头只有两个表让我有些误解。

    还有个问题,就是应该要在child obj中有这指向parent obj的filed。不然只凭relationships表是不知道从属关系的。

    ReplyDelete
  26. (OrgID+GUID)可以作为唯一索引么,如果一个GUID有两个或多个关系怎么办
    例如APP除了有owner_id还有catagories_id,那么他就有两个关系了。

    ReplyDelete
  27. @Wang, Han,不要加“child obj中有这指向parent obj的filed”,因为在relationship表中有targetTabid,
    这个就表述了child和parent的关系。

    ReplyDelete
  28. 不是的,因为会插入多条relation元组,对于relationships table而言,这些relation元组没有child和parent之分。

    ReplyDelete
  29. @JamsonBao,需要建立两个关系去表示一个表的两个字段和另一个表的关系,app层可以并发处理每个关系的数据

    ReplyDelete
  30. @Wang, Han,不太明白为啥要区分child和parent?我们建立了关系,就知道了join的表是谁,不就ok?

    ReplyDelete
  31. 原因是这样的,就那这个User和App的例子吧
    对于App, AppOwner是它的一个不可缺少的属性
    对于User, 有哪些App只有在查找的时候才会关心。
    这就是为什么要区别child和parent的关系了。

    ReplyDelete
  32. @Wang, Han,对于user有那些app,不需要区别child和parent的关系,任然可以获取数据的呀,任然不明白你需要区别child和parent的目的是什么,是什么样的数据查询不出来,能举例说明?

    ReplyDelete
  33. 并不是查不出来.
    对于App而言,AppOwner是它的属性。就是这个可以认为是强制地去查找relationships表。
    对User找他的App是按需的,非强制。
    当然把这个查找的需求放到具体的业务逻辑层也可以实现。只是认为这中关系固化在数据表结构上会更好。

    ReplyDelete
  34. @Wang, Han,不太建议你这样做,我们已经定义了mt_table和mt_fields去存储表以及表的字段属性,而mt_data中存储了所有表的所有字段的数据,无非是以value0...value500来表示不同表不同字段.在mt_index中建立索引加快速度,在mt_relationships建立关系,找到关系的主键,再去data中捞取需要的数据,无需在mt_relationships在保存父子的关系。

    ReplyDelete
  35. 我的意思是,比如存在如下两个relationship
    (relationshipid :1,GUID:app在data表的id,tableid:app_table的id,target_tableid:user_table的id,orgid:org_id)

    (relationshipid :2,GUID:app在data表的id,tableid:app_table的id,target_tableid:category_table的id,orgid:org_id)

    那么此时guid+orgid还能作为唯一索引吗?

    ReplyDelete
  36. 这么说吧,对于插入的relationships表的元组,它的依据也应该是一个定义好了的child-parent关系。
    如果没在child obj中定义指向parent obj的field,这些relationships表插入的数据targetObjID的依据是什么呢?由业务逻辑来控制?

    ReplyDelete
  37. @JamsonBao,针对你描述的情况,这个唯一索引必须换成普通的索引了。

    ReplyDelete
  38. @Wang, Han,我明白你的意思了,如果你想存储类似树结构的父子关系,必须知道父节点的GUID信息,你需要加个字段。

    ReplyDelete
  39. 恩,好的,很高兴与你这样的讨论~Thanks~

    ReplyDelete