1 测试用表及定义
1） product表及定义
MySQL [test]> show create table product;
CREATE TABLE `product` (
`id` bigint(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`producer` bigint(20) DEFAULT NULL,
`type` varchar(10) DEFAULT NULL,
PRIMARY KEY USING BTREE (`id`),
GLOBAL INDEX `g_i_emp_id` (`producer`) PARTITION BY KEY (`producer`) PARTITIONS 2,
GLOBAL INDEX `g_i_emp_id_name` (`producer`) COVERING (`name`) PARTITION BY KEY (`producer`) PARTITIONS 2,
KEY `_local_g_i_emp_id` (`producer`),
KEY `_local_g_i_emp_id_name` (`producer`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 3;
2）emp表及定义
MySQL [test]> show create table emp;
CREATE TABLE `emp` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`dept_no` int(11) DEFAULT NULL,
`salary` bigint(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 100005 DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 2;
2 emp表所在的表组内表的情况
MySQL [information_schema]> select TABLE_SCHEMA,TABLE_GROUP_NAME,TABLE_NAME from TABLE_DETAIL where table_group_name like 'tg1';
+--------------+------------------+------------+
| TABLE_SCHEMA | TABLE_GROUP_NAME | TABLE_NAME |
+--------------+------------------+------------+
| test | tg1 | emp_bonus |
| test | tg1 | emp_bonus |
| test | tg1 | emp |
| test | tg1 | emp |
| test | tg1 | emp_info |
| test | tg1 | emp_info |
可以看出，product表同emp表不在同一表组，它们的分区方式并不一致。
3 运行两表关联的sql，看一下执行计划
MySQL [test]> explain select a.id,a.name, b.name as prod_name from emp a, product b where a.id=b.producer;
LOGICAL EXECUTIONPLAN
Project(id="id", name="name", prod_name="name0")
BKAJoin(condition="producer = id", type="inner")
Gather(concurrent=true)
LogicalView(tables="emp[p1,p2]", shardCount=2, sql="SELECT `id`, `name` FROM `emp` AS `emp`")
Gather(concurrent=true)
LogicalView(tables="product[p1,p2,p3]", shardCount=3,
sql="SELECT `name`, `producer` FROM `product` AS `product` WHERE (`producer` IN (...))")
因为分区方式不一致，两个表不属于同一个表组，join操作不能下推到存储节点执行。
4 创建一个全局二级索引
1） 在product表的producer列上创建一个全局二级索引
ALTER TABLE product ADD GLOBAL INDEX `g_i_emp_id_name` (`producer`) covering('name') partition by key(`producer`) partitions 2;
2） 查看tg1（emp所在tablegroup）内的表及索引
MySQL [test]> select TABLE_SCHEMA,TABLE_GROUP_NAME,TABLE_NAME from information_schema.TABLE_DETAIL where table_group_name like 'tg1';
+--------------+------------------+-----------------------+
| TABLE_SCHEMA | TABLE_GROUP_NAME | TABLE_NAME |
+--------------+------------------+-----------------------+
| test | tg1 | g_i_emp_id_$5917 |
| test | tg1 | emp_bonus |
| test | tg1 | emp |
新建索引和emp属于同一tablegroup
3） 查看一下执行计划
MySQL [test]> explain select a.id,a.name, b.name as prod_name from emp a, product b where a.id=b.producer;
LOGICAL EXECUTIONPLAN
Project(id="id", name="name", prod_name="name0")
BKAJoin(condition="producer = id", type="inner")
Gather(concurrent=true)
LogicalView(tables="emp[p1,p2]", shardCount=2, sql="SELECT `id`, `name` FROM `emp` AS `emp`")
Gather(concurrent=true)
LogicalView(tables="product[p1,p2,p3]", shardCount=3,
sql="SELECT `name`, `producer` FROM `product` AS `product` WHERE (`producer` IN (...))")
因为需要回表，join操作仍然不能下推到存储节点执行。
5 创建一个全局覆盖二级索引
1） 在product表producer列上创建一个覆盖索引，覆盖name列
ALTER TABLE product ADD GLOBAL INDEX `g_i_emp_id_name` (`producer`) covering('name') partition by key(`producer`) partitions 2;
2） 检查新建索引所属tablegroup
MySQL [test]> select TABLE_SCHEMA,TABLE_GROUP_NAME,TABLE_NAME from information_schema.TABLE_DETAIL where table_group_name like 'tg1';
+--------------+------------------+-----------------------+
| TABLE_SCHEMA | TABLE_GROUP_NAME | TABLE_NAME |
+--------------+------------------+-----------------------+
| test | tg1 | g_i_emp_id_$5917 |
| test | tg1 | emp_bonus |
| test | tg1 | emp |
| test | tg1 | g_i_emp_id_name_$5745 |
新建的索引同emp属于一个tablegroup
3） 看一下执行计划
MySQL [test]> explain select a.id,a.name, b.name as prod_name from emp a, product b where a.id=b.producer;
LOGICAL EXECUTIONPLAN
Gather(concurrent=true)
IndexScan(tables="emp[p1,p2],g_i_emp_id_name_$5745[p1,p2]", shardCount=2, sql="SELECT `emp`.`id`, `emp`.`name`,
`g_i_emp_id_name_$5745`.`name` AS `prod_name` FROM `emp` AS `emp` INNER JOIN
`g_i_emp_id_name_$5745` AS `g_i_emp_id_name_$5745` ON (`emp`.`id` = `g_i_emp_id_name_$5745`.`producer`)")
HitCache:false
Source:PLAN_CACHE
TemplateId: 447db302
整个join操作被下推到存储节点，使用了g_i_emp_id_name做了索引扫描。
6 结论
从这个小实验可以看出，PolarDB-X可以使用全局覆盖索引将连接操作下推到存储节点执行。
