数据分片 - 全局二级索引(1.20-2021-7-25) - 《Mycat2权威指南》

约束

为了解决分库分表中间件在路由分片规则难以映射多个分片维度的问题,Mycat2使用分片表数据冗余方案。使用不同与原分片表的分片算法，建立原分片表的列子集甚至全集的分片表，这类分片表在Mycat2中称为索引表.

约束

建议原分片表使用id作为主键而且是数字类型，不建议使用日期类型
一个原分片表可以建立多个索引表,是一对多关系.
原分片表必须有主键,主键用于建立索引表与主表的行关系.
索引表也必须有主键,原分片表的分片键,
当使用索引表后,建议在插入语句中,需要显式带有原分片表和索引表的分片的值.
当使用索引表后,不建议对分片表进行频繁更新操作,因为会触发索引表更新.在带有条件的update或者delete语句下,一次更新不能超过1000行.而不带条件下的delete语句则没有这个限制.
Mycat仅保证在更新.插入语句中自动使用XA事务(在XA事务模式下),保证数据一致性.
在使用分片表途中建立全局二级索引,Mycat2仅仅是更改配置,而不负责数据同步,因为暂时缺乏schema变更的数据同步组件.
在使用全局二级索引后,能有效减少全表扫描,对于减少连接使用,减少计算节点与存储节点的数据传输有帮助.

自动提交下如果使用XA事务模式,涉及多个节点,自动开启XA事务,如果遇上插入失败,则隐式自动尝试回滚XA事务,建议显式启动事务进行数据插入,修改,且插入值不要涉及SQL函数调用避免运行时报错
如果仅涉及一个mysql为主节点,那么可以使用proxy事务

自动提交下如果使用Proxy(本地)事务模式,涉及多个节点,如果遇上提交失败,则无法回滚已经成功插入数据的连接.需要业务系统显式编写删除数据的代码
所以建议使用XA事务使用,只有一个mysql集群或者只有一个mysql则使用proxy事务

全局二级索引表暂时不支持alter,请自行修正mycat配置文件,更改字段

具体的执行语句可以通过explain语句查看

建立全局二级索引有三种办法

1.在建表语句中带有全局二级索引信息

CREATE TABLE IF NOT EXISTS db1.`travelrecord` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10, 0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`),
    GLOBAL INDEX `g_i_user_id`(`user_id`) COVERING (`fee`, id) DBPARTITION BY mod_hash(`user_id`) TBPARTITION BY mod_hash(`user_id`) DBPARTITIONS 2 TBPARTITIONS 2
) ENGINE = InnoDB CHARSET = utf8
DBPARTITION BY mod_hash(id) DBPARTITIONS 2
TBPARTITION BY mod_hash(id) TBPARTITIONS 2

该全局二级索引的语法是

GLOBAL INDEX `g_i_user_id`(`user_id`) COVERING (`fee`, id) DBPARTITION BY mod_hash(`user_id`) TBPARTITION BY mod_hash(`user_id`) DBPARTITIONS 2 TBPARTITIONS 2

g_i_user_id是索引名字,它不能与mysql中的索引名重复
其后的user_id是索引列(字段)名字,在这里它就是分片字段,必须与分片字段相同
COVERING后面的是覆盖列,冗余的信息,用于减少扫描原分片表或者优化为不扫描原分片表,

索引列与覆盖列必须包含原分片表的主键,原分片字段,否则无法进行数据插入,无法正确路由.

DBPARTITION BY mod_hash(`user_id`) TBPARTITION BY mod_hash(`user_id`) DBPARTITIONS 2 TBPARTITIONS 2

此处配置与分片表的建表语句相同,不再重复.

2.在已有分片表中添加全局二级索引

//建立分片表
CREATE TABLE db1.`travelrecord` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` varchar(100) DEFAULT NULL,
  `traveldate` date DEFAULT NULL,
  `fee` decimal(10,0) DEFAULT NULL,
  `days` int DEFAULT NULL,
  `blob` longblob,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 dbpartition by mod_hash(id) tbpartition by mod_hash(id) tbpartitions 2 dbpartitions 2;
//建立全局二级索引
 CREATE UNIQUE GLOBAL INDEX `g_i_user_id` ON `db1`.`travelrecord`(`user_id`) 
    COVERING(`fee`,id) 
    dbpartition by mod_hash(`user_id`) tbpartition by mod_hash(`user_id`) dbpartitions 2 tbpartitions 2

3.在配置文件中自定义索引表

{
    "customTables":{},
    "globalTables":{},
    "normalTables":{},
    "schemaName":"db1",
    "shardingTables":{
        "travelrecord":{
            "createTableSQL":"CREATE TABLE IF NOT EXISTS db1.`travelrecord` (\n\t`id` bigint NOT NULL AUTO_INCREMENT,\n\t`user_id` varchar(100) DEFAULT NULL,\n\t`traveldate` date DEFAULT NULL,\n\t`fee` decimal(10, 0) DEFAULT NULL,\n\t`days` int DEFAULT NULL,\n\t`blob` longblob,\n\tPRIMARY KEY (`id`),\n\tKEY `id` (`id`),\n\tGLOBAL INDEX `g_i_user_id`(`user_id`) COVERING (`fee`, id) DBPARTITION BY mod_hash(`user_id`) TBPARTITION BY mod_hash(`user_id`) DBPARTITIONS 2 TBPARTITIONS 2\n) ENGINE = InnoDB CHARSET = utf8\nDBPARTITION BY mod_hash(id) DBPARTITIONS 2\nTBPARTITION BY mod_hash(id) TBPARTITIONS 2",
            "function":{
                "properties":{
                    "dbNum":"2",
                    "mappingFormat":"c${targetIndex}/db1_${dbIndex}/travelrecord_${index}",
                    "tableNum":"2",
                    "tableMethod":"mod_hash(id)",
                    "storeNum":2,
                    "dbMethod":"mod_hash(id)"
                }
            },
            "shardingIndexTables":{
                "travelrecord_g_i_user_id":{
                    "createTableSQL":"CREATE TABLE IF NOT EXISTS db1.travelrecord_g_i_user_id (\n\t`user_id` varchar(100) DEFAULT NULL,\n\t`fee` decimal(10, 0) DEFAULT NULL,\n\t`id` bigint NOT NULL AUTO_INCREMENT,\n\tPRIMARY KEY (`id`)\n)\nDBPARTITION BY mod_hash(`user_id`)\nTBPARTITION BY mod_hash(`user_id`) TBPARTITIONS 2",
                    "function":{
                        "properties":{
                            "dbNum":"2",
                            "mappingFormat":"c${targetIndex}/db1_${dbIndex}/travelrecord_g_i_user_id_${index}",
                            "tableNum":"2",
                            "tableMethod":"mod_hash(`user_id`)",
                            "storeNum":2,
                            "dbMethod":"mod_hash(`user_id`)"
                        }
                    }
                }
            }
        }
    },
    "views":{}
}

shardingIndexTables是shardingTables中元素的一个属性,travelrecord_g_i_user_id的配置与分片表的配置没有区别

~~其中travelrecord中的createTableSQL就带有全局二级索引的信息,在自动Hash分片下,该建表语句可以直接用于建立分片表与索引表。~~

以下是mycat2中索引表的表名自动生成规则

CREATE UNIQUE GLOBAL INDEX `g_i_user_id` ON `db1`.`travelrecord`(`user_id`) 
    COVERING(`fee`,id) 
    dbpartition by mod_hash(`user_id`) tbpartition by mod_hash(`user_id`) dbpartitions 2 tbpartitions 2

索引表名= 原分片表名+”_”+索引名
索引库名=原分库名

所以索引名就是索引表名去掉(原分片表名+”_”)

例子

例子1 查询原分片表,分片键是id


select * from db1.travelrecord where id = 1
MycatView(distribution=[[db1.travelrecord]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_1 AS `travelrecord` WHERE (`travelrecord`.`id` = ?))

例子2 查询索引表,分片键是user_id

select * from db1.travelrecord where user_id = 1
MycatProject(id=[$0], user_id=[$1], traveldate=[$3], fee=[$2], days=[$4], blob=[$5])
  MycatSQLTableLookup(condition=[=($0, $7)], joinType=[inner], type=[BACK], correlationIds=[[$cor0]], leftKeys=[[0]])
    MycatView(distribution=[[db1.travelrecord_g_i_user_id]], conditions=[=($0, ?0)])
    MycatView(distribution=[[db1.travelrecord]])
    //查询原分片表
Each(targetName=c0, sql=SELECT * FROM (SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id`         FROM db1_0.travelrecord_0 AS `travelrecord`         WHERE (`travelrecord`.`user_id` = ?)) AS `t0` WHERE ((`t0`.`id`) IN ($cor0)) union all SELECT * FROM (SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id`         FROM db1_0.travelrecord_1 AS `travelrecord`         WHERE (`travelrecord`.`user_id` = ?)) AS `t0` WHERE ((`t0`.`id`) IN ($cor0)))
    //查询原分片表
Each(targetName=c1, sql=SELECT * FROM (SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id`         FROM db1_1.travelrecord_2 AS `travelrecord`         WHERE (`travelrecord`.`user_id` = ?)) AS `t0` WHERE ((`t0`.`id`) IN ($cor0)) union all SELECT * FROM (SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id`         FROM db1_1.travelrecord_3 AS `travelrecord`         WHERE (`travelrecord`.`user_id` = ?)) AS `t0` WHERE ((`t0`.`id`) IN ($cor0)))
    //查询索引表
Each(targetName=c0, sql=SELECT `travelrecord_g_i_user_id`.`id`, `travelrecord_g_i_user_id`.`user_id`, `travelrecord_g_i_user_id`.`fee` FROM db1_0.travelrecord_g_i_user_id_1 AS `travelrecord_g_i_user_id` WHERE (`travelrecord_g_i_user_id`.`user_id` = ?))

其中查询原分片表的sql可以在执行器中进一步进行分区剪裁(因为主键是分片键),实际上执行SQL只有两条

例子3 查询索引表,分片键是user_id且只查询覆盖列

select fee from db1.travelrecord where user_id = 1
MycatView(distribution=[[db1.travelrecord_g_i_user_id]], conditions=[=($0, ?0)])
Each(targetName=c0, sql=SELECT `travelrecord_g_i_user_id`.`fee` FROM db1_0.travelrecord_g_i_user_id_1 AS `travelrecord_g_i_user_id` WHERE (`travelrecord_g_i_user_id`.`user_id` = ?))

可以看出只有一条SQL

例子4 使用全局二级索引注释(2021-7-26后)

 EXPLAIN SELECT * FROM db1.travelrecord FORCE INDEX(g_i_user_id) WHERE user_id =1 
plan
MycatProject(id=[$0], user_id=[$1], traveldate=[$3], fee=[$2], days=[$4], blob=[$5])
  MycatHashJoin(condition=[=($0, $7)], joinType=[inner])
    MycatView(distribution=[[db1.travelrecord_g_i_user_id]], conditions=[=($0, ?0)])
    MycatView(distribution=[[db1.travelrecord]], conditions=[=(CAST($1):DOUBLE, CAST(?0):DOUBLE NOT NULL)])
Each(targetName=c0, sql=SELECT `travelrecord_g_i_user_id`.`id`, `travelrecord_g_i_user_id`.`user_id`, `travelrecord_g_i_user_id`.`fee` FROM db1_0.travelrecord_g_i_user_id_1 AS `travelrecord_g_i_user_id` WHERE (`travelrecord_g_i_user_id`.`user_id` = ?))
Each(targetName=c0, sql=SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id` FROM db1_0.travelrecord_0 AS `travelrecord` WHERE (`travelrecord`.`user_id` = ?) union all SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id` FROM db1_0.travelrecord_1 AS `travelrecord` WHERE (`travelrecord`.`user_id` = ?))
Each(targetName=c1, sql=SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id` FROM db1_1.travelrecord_2 AS `travelrecord` WHERE (`travelrecord`.`user_id` = ?) union all SELECT `travelrecord`.`traveldate`, `travelrecord`.`days`, `travelrecord`.`blob`, `travelrecord`.`user_id`, `travelrecord`.`id` FROM db1_1.travelrecord_3 AS `travelrecord` WHERE (`travelrecord`.`user_id` = ?))

可以看出使用了travelrecord_g_i_user_id查询

写放大问题

explain insert db1.travelrecord (id,user_id) values(100,100)
对应实际执行的sql是
VertxExecuter.EachSQL(target=c0, sql=INSERT INTO db1_0.travelrecord_0 (id, user_id)
VALUES (?, ?), params=[100, 100])
VertxExecuter.EachSQL(target=c0, sql=INSERT INTO db1_0.travelrecord_g_i_user_id_1 (user_id, fee, id)
VALUES (?, NULL, ?), params=[100, 100])
而在不配置索引表的情况下是
VertxExecuter.EachSQL(target=c0, sql=INSERT INTO db1_0.travelrecord_0 (id, user_id)
VALUES (?, ?), params=[100, 100])

尽管Mycat2会把插入语句以集群一个连接为键进行分组并行插入,但是还是会有一点性能开销