分区表的路由计算是直接依赖于分区列的数据类型的，特别是Key分区与Hash分区。因此，使用不同数据类型的分区列，其哈希值算法或比较算法（比如，大小写是否敏感）的实现不一样，会产生不一样的路由行为（MySQL的分区路由算法也是类型强相关的）。
如下所示，假如tbl_int表是分区列，类型是int且分区数是1024, 而tbl_bigint分区列类型是bigint且分区数目也是1024。它们虽然都是整数类型，但是由于数据类型不一样，对于同一个查询值（12345678）的路由结果也不一样：
show create table tbl_int;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_int | CREATE TABLE `tbl_int` (
`a` int(11) NOT NULL,
KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 1024 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
show create table tbl_bigint;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_bigint | CREATE TABLE `tbl_bigint` (
`a` bigint(20) NOT NULL,
KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 1024 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)mysql> create table if not exists tbl_bigint(a bigint not null)
-> partition by key(a) partitions 1024;
Query OK, 0 rows affected (28.41 sec)
explain select * from tbl_int where a=12345678;
+---------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+---------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_int[p260]", sql="SELECT `a` FROM `tbl_int` AS `tbl_int` WHERE (`a` = ?)") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: c90af636 |
+---------------------------------------------------------------------------------------------------+
4 rows in set (0.45 sec)
explain select * from tbl_bigint where a=12345678;
+------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_bigint[p477]", sql="SELECT `a` FROM `tbl_bigint` AS `tbl_bigint` WHERE (`a` = ?)") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 9b2fa47c |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
分区列大小写、字符集及校验集的说明
说明
分区列的字符集（charset）及校验集（collation）对分区表的路由算法会产生直接影响。比如，指定分区路由是否需要忽略大小写。如果分区表的校验集是区分大小写的，则分区路由在哈希与比较过程中便会区分大小写；如果分区表的校验集不区分大小写，则分区路由在哈希与比较过程中不会区分大小写。默认情况下，字符类型的分区列会使用字符集utf8及不区分区大小写的校验集 utf8_general_ci。
示例1
如果用户需要让分区表在路由时区分分区列的大小写，在建表时将分区表的校验集设置为区分大小写的校验集即可（如utf8_bin）。如下所示，分区表tbl_varchar_cs的分区表采用了CHARACTER SET utf8 COLLATE utf8_bin ，所以对于大小写不同的两个字符串 'AbcD' 与 'abcd' ，分区表会将它们路由不同的分区：
show create table tbl_varchar_cs;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_varchar_cs | CREATE TABLE `tbl_varchar_cs` (
`a` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`a`)
PARTITIONS 64 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)
explain select a from tbl_varchar_cs where a in ('AbcD');
+-------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+-------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_cs[p29]", sql="SELECT `a` FROM `tbl_varchar_cs` AS `tbl_varchar_cs` WHERE (`a` IN(?))") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 2c49c244 |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.11 sec)
explain select a from tbl_varchar_cs where a in ('abcd');
+-------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+-------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_cs[p11]", sql="SELECT `a` FROM `tbl_varchar_cs` AS `tbl_varchar_cs` WHERE (`a` IN(?))") |
| HitCache:true |
| Source:PLAN_CACHE |
| TemplateId: 2c49c244 |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)示例2
如果需要让分区表在路由时忽略分区列的大小写，在建表时将分区表的校验集设置为不区分大小写的校验集即可（如utf8_general_ci）。如下所示，分区表tbl_varchar_ci的分区表采用了CHARACTER SET utf8 COLLATE utf8_general_ci ，所以对于大小写不同的两个字符串 'AbcD' 与 'abcd' ，分区表则会将它们路由到同样的分区：
show create table tbl_varchar_ci;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_varchar_ci | CREATE TABLE `tbl_varchar_ci` (
`a` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`a`)
PARTITIONS 64 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
explain select a from tbl_varchar_ci where a in ('AbcD');
+------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_ci[p4]", sql="SELECT `a` FROM `tbl_varchar_ci` AS `tbl_varchar_ci` WHERE (`a` IN(?))") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 5c97178e |
+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.15 sec)
explain select a from tbl_varchar_ci where a in ('abcd');
+------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_ci[p4]", sql="SELECT `a` FROM `tbl_varchar_ci` AS `tbl_varchar_ci` WHERE (`a` IN(?))") |
| HitCache:true |
| Source:PLAN_CACHE |
| TemplateId: 5c97178e |
+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)分区列字符集与校验集的变更
由于分区表的路由算法与数据类型是相关的，如果分区列的字符集与校验集被修改，将会导致全表所有数据的重分布。所以请慎重地修改分区列的数据类型。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
