关系型数据库的搜索结果-阿里云开发者社区

"关系型数据库"

共 98671 条结果

全部问答文章公开课课程电子书技术圈体验

MySQL中的开启和关闭事务|学习笔记

开发者学堂课程【JDBC数据库开发进阶：MySQL中的开启和关闭事务】学习笔记，与课程紧密联系，让用户快速学习知识。课程地址：/learning/course/32MySQL中的开启和关闭事务目录：1.事务的四大特性：ACID2.MySQL中的操作事务3.jabc中操作事务而在MySQL中该如何操作事务？事务其实是有两个端点的，分为事务的开始（start transaction）和结束（commit 表示提交事务或rollback 表示回滚事务，事务执行失败，回到原来的状态）接下来创建表！选择mydb3，将以下程序执行：为了方便演示事务，我们需要创建一一个account表:。CREATE TABLE account('id INT PRIMARY KEY AUTO_ INCREMENT,NAME VARCHAR(30),balance NUMERIC(10.2));INSERT INTO account(NAME,balanse) VALUES('zs', 100000);INSERT INTO account(NAME,balance) VALUES('Is', 10000);INSERT INTO account(NAME, balane) VALUES('ww', 10000); SELECT * FROM account; 首先事务主要包括：1.事务的四大特性：ACID2.MySQL中的操作事务3.jabc中操作事务而在MySQL中该如何操作事务？事务其实是有两个端点的，分为事务的开始（start transaction）和结束（commit 表示提交事务或rollback 表示回滚事务，事务执行失败，回到原来的状态）接下来我们按照操作来做一次执行，打开执行的程序，输入mysql -uroot -p，输入密码然后进入，再选择数据库mudb3,再查询一下数据库中的表再输入show tables;又要选择account表，故输入select *front account；先查询一下数据库中的表，就显示以下确认数据没问题之后，就开始转账操作首先使用transaction,输入start transaction;表示事务开始了，接下来执行的所有操作就都在这个事务之内，直到出现commit或rollback接下来，就开始执行update ,输入update account set balance=900 where name='zs'；这句话的作用就是更新张三的账户余额，就是把张三减去100块钱，结果张三的账户余额就变成了900 之后我们再输入update account set balance=1100 where name='ls'；查李四的账户余额就变成了1100但进行到这一步，此时的事务并未提交，而最后再输入rollback，则事务就相当于没有发生，就会回滚到刚刚开始。虽然整个操作进行完并没有使得最后的结果发生变化，但并不证明操作没有进行，因为每次操作后查找时都显示了不一定的变化。由此我们应该明白事务可以回滚并且可以提交！！接下来，再重新进行一轮新的操作，输入start transaction;再更新数据update account set balance -balanc-100 where name=’zs’;最后select *fron account;查询表而之后输入update account set balance -balanc+100 where name=’ls’;将balance-100变成balance+100之后,zs变ls张三变成李四之后，再查询表，执行成功就可以发现表的数据发生了改变之后再输入commit之后数据就无法在发生改变，且并不在回滚。事务回滚是回滚到开始的时候，而输入commit之后事务就已经结束了。无法再进行回滚！！如果要回滚，只能重新再开启一个事务！！之后再输入rollback。

文章

关系型数据库 · MySQL · Java · 数据库连接 · 数据库 · 数据安全/隐私保护 · 开发者

2022-10-06

JDBC中的事务 |学习笔记

开发者学堂课程【JDBC数据库开发进阶：JDBC中的事务】学习笔记，与课程紧密联系，让用户快速学习知识。课程地址：/learning/course/32JDBC中的事务处理?事务：在jdbc中处理事务，都是通过connection完成，都是自动提交，手动提交就是开启事务。自动提交：既执行一条可提交，不用我们提交，事务往往是把多个事务弄在一起，往往是try开始时提交事务，结束时提交事务，如未发生错误，则执行事务，如发生错误，在commit时发生异常，它本身不可能异常，如异常则可能是失联，这时回滚即可。如未提交事务，回滚事务，这时事务自动回滚以失败告终，格式极其重要接下来就是写代码。数据库：首先我们写项目的时候，要导包，mysql驱动导进来，现在的项目，需要将准备工作做好，如以前的小工具都需提前弄好，演示转账，得有一个dao，dao里面写一个updatebalanca，告诉金额，指定用户的余额，用runtime作一个转换，得到指定工具。第二件事要给出sql模板，创建pstmt，所有参数都用问号代替，，再下一步，对参数进行赋值，问号的顺序需要明白，最后执行，用executeupdate。转账方法，from到to，格式：开启事务…提交事务，回滚事务，对事务的操作必须使用connection对象。要保证在一个事务内，必须前后使用一个connection对象，如是不同的connection，则不是一个对象，创建事务对象，在开启事务和提交事务的中间，给from减去相应金额，给to加上相应金额在这中间抛一个异常，这两句使用的connection不能保证都是同一个。所有对connection的操作都在service层进行的处理，明天要去处理这一问题，把所有对connection的操作隐藏起来，需要自定义的工具隐藏起来。 boolean.：设置是否为自动提交事务，如果true（默认值是true）表示自动提交，也就是每条执行的SQL语句都是一个单独的事务，如果设置false，那么就相当于开启了事务了 con.setautocommit(false)：表示开启事务！给from减去相应金额给to减去相应金额 commit()：提交结束事务；con.commit（）；表示提交事务。 rollback（）：回滚结束事务。con.rollback（）；表示回滚事务。对事务的操作必须使用connection对象，同一事务中的所有操作，都是使用同一个connection对象。如果N次使用的都是不同的connection对象，那么就不是同一操作。 public void 1. 得到连接对象 2. 给出sq1模板，创建pstmt 3. 对参数进行赋值 4. 执行之演示所有对connection的操作都在service层进行的处理，把所有对connection的操作隐藏起来，这需要使用自定义的小工具，所有对事务的控制都转移到public class 修改指定用户的余额，param name ，param balance。列入转账，要public class 转账方法，从from到to。创建dao对象，在原先基础上加上相应金额或者减去相应金额，所有对连接的管理都转移到业务上了。

文章

SQL · Java · 关系型数据库 · MySQL · 数据库连接 · 数据库 · 开发者

2022-10-06

【MySQL】浅谈日志系统

MySQL相关文章慢sql搜集分析工具搭建binlog概念二进制日志文件，记录了所有的`DDL`（数据库定义语言，create、alter、drop）和`DML`（数据库操作语言，select、update、insert、delete）语句，以事件形式记录，包含语句所执行的消耗的时间。用作于主从架构下的数据同步，也可以用作误删操作后基于binlog恢复到指定时间点。通过自带的mysqlbinlog命令恢复。两阶段提交时也会依赖binlog实现事务数据一致。存储规则binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。对于每个binlog日志文件，通过一个统一的index文件来组织。redolog概念重做日志，主要记录事务执行后的状态，用来恢复未写入磁盘的已成功事务更新的数据。据库对数据做修改时会将磁盘中的数据加载到buffer pool中，然后再buffer pool中修改，这时内存中的数据与磁盘数据不一致成为脏页如果这时候db发生重启，这些数据由于还在内存中未同步到磁盘中(随机io)，会发生数据丢失为了避免这种现象，当buffer pool的数据发生变化结束后把相应修改记录保存在redolog文件中当db发生崩溃时恢复的时候，可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。redolog是innodb引擎独有的日志存储规则数据空间有限，循环写入，write pos表示当前位置，一边写一遍后移，checkpoint表示要清除的位置记录对数据的修改操作，主要目的是的随机IO变成顺序IO，提升性能。redolog没有记录完整数据，真正落盘不需要redolog，等到merge时直接将buffer pool持久化到磁盘与binlog的区别redolog属于引擎层日志是innodb引擎特有，binlog属于server层日志。redolog是**物理日志**，记录了是“哪个会话哪个时间在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的**原始逻辑sql**，比如"给 ID=2 这一行的 c 字段加 1"redolog是循环写入有最大限制，binlog可以追加，不会覆盖之前的日志。两阶段提交MySQL为了保证数据安全，数据所有操作都是先写日志再写磁盘整个写入逻辑划分成 redolog prepare -> binlog -> redolog commit校验数据binlog有记录，redolog commit: 正常事务binlog有记录，redolog prepare: 写入库成功，提交事务binlog无记录，redolog prepare: 写入数据库失败，回滚事务undolog概念回滚日志，用于存放数据被修改前的值，如果修改异常可以使用undo日志来实现回滚操作，保证事务的一致性。用于MySQL实现事务原理与MVCC特性undolog分为insert undolog，insert语句产生的的日志，事务提交后update undolog，delete和update语句产生的日志，事务提交后放到history list中，等待purge线程进行最终删除changebuffer概念当需要更新数据时，如果数据在内存中则直接更新，如果不在innodb会把更新操作缓存在changebuffer中，当下次需要访问这条数据时，将这条数据从磁盘加载到内存并执行change buffer的逻辑，**减少随机读的IO的消耗。**主要目的节省随机读磁盘的io消耗，合并多次操作为批操作提高性能。原理changebuffer也会落盘，将changebuffer的的操作合并到原数据页得到新结果的过程叫做mergemerge过程磁盘读入数据将changebuffer内容依次应用到内存写入redolog，数据变化

文章

SQL · 存储 · 关系型数据库 · MySQL · 数据库 · 数据安全/隐私保护

2022-10-06

【笔记】开发指南—DDL语句—分库分表语法—CREATE TABLE

语法CREATE [SHADOW] TABLE [IF NOT EXISTS] tbl_name (create_definition, ...) [table_options] [drds_partition_options] create_definition: col_name column_definition | mysql_create_definition | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...) [global_secondary_index_option] [index_option] ... # 全局二级索引相关 global_secondary_index_option: [COVERING (col_name,...)] [drds_partition_options] # 分库分表子句 drds_partition_options: DBPARTITION BY db_partition_algorithm [TBPARTITION BY table_partition_algorithm [TBPARTITIONS num]] [LOCALITY=locality_option] db_sharding_algorithm: HASH([col_name]) | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name) | UNI_HASH(col_name) | RIGHT_SHIFT(col_name, n) | RANGE_HASH(col_name, col_name, n) table_sharding_algorithm: HASH(col_name) | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name) | UNI_HASH(col_name) | RIGHT_SHIFT(col_name, n) | RANGE_HASH(col_name, col_name, n) # 可以在创建单表时指定该表的存储位置 locality_option: 'dn=storage_inst_id_list' storage_inst_id_list: storage_inst_id[,storage_inst_id_list] # 以下为MySQL DDL语法 index_sharding_col_name: col_name [(length)] [ASC | DESC] index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' index_type: USING {BTREE | HASH}说明 PolarDB-X DDL语法基于MySQL语法，以上主要列出了差异部分，详细语法请参见MySQL 文档。分库分表子句和参数DBPARTITION BY hash(partition_key)：指定分库键和分库算法。TBPARTITION BY { HASH(column) | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(column)（可选）：默认与DBPARTITION BY相同，指定物理表使用什么方式映射数据。TBPARTITIONS num（可选）：每个库上的物理表数目（默认为1），如不分表，就不需要指定该字段。拆分函数的详细介绍，请参见拆分函数。全局二级索引定义子句[UNIQUE] GLOBAL：定义全局二级索引，UNIQUE GLOBAL代表全局唯一索引。index_name：索引名，也是索引表的名称。index_type：索引表中分库分表键上局部索引的类型，支持范围请参见MySQL 文档。index_sharding_col_name,...：索引列，包含且仅包含索引表的全部分库分表键。详情请参见全局二级索引。global_secondary_index_option：全局二级索引的扩展语法。COVERING (col_name,...)：覆盖列，索引表中除索引列以外的其他列，默认包含主键和主表的分库分表键。详情请参见全局二级索引。drds_partition_options：索引表的分库分表子句，详情请参见分库分表子句和参数。index_option：索引表中分库分表键上局部索引的属性，详情请参见MySQL 文档。全链路压测影子表子句SHADOW：创建全链路压测影子表，表名必须以test为前缀，前缀后的表名部分必须与关联的正式表名一致，且正式表必须先于影子表创建。LOCALITY通过LOCALITY指定单表的存储位置。说明通过LOCALITY语法指定了数据库或单表的位置之后，不支持再修改该库或该表的存储位置。数据库中单表的存储位置不受该库存储位置的影响。创建单表时若未指定存储位置，则会被随机放置在一个存储节点上，且后续该PolarDB-X实例上创建的所有未指定存储位置的单表，均会被放置在该存储节点上。单库单表建一张单库单表，不做任何拆分。CREATE TABLE single_tbl( id bigint not null auto_increment, name varchar(30), primary key(id));查看逻辑表的节点拓扑，可以看出只在0库创建了一张单库单表的逻辑表。mysql> show topology from single_tbl;+------+------------------------------------------------------------------+------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | single_tbl |+------+------------------------------------------------------------------+------------+1 row in set (0.01 sec)假设已有一个PolarDB-X实例，该实例上已有polardbx-storage-0-master和polardbx-storage-1-master两个存储节点，其中polardbx-storage-1-master上已创建了一个数据库db1。现需要在db1库中创建一张表，并指定其的存储位置为polardbx-storage-0-master，语法如下：mysql>CREATE TABLE tb1 (id int) LOCALITY='dn=polardbx-storage-0-master';创建成功后，您可以通过如下语句查看该表的拓扑结构：mysql>SHOW TOPOLOGY FROM tb1;返回结果如下：+----+------------------+------------+| ID | GROUP_NAME | TABLE_NAME | +----+------------------+------------+| 0 | DB1_000000_GROUP | tb1 |+----+------------------+------------+1 row in set说明以上返回结果表示tb1表位于DB1_000000_GROUP分库。分库不分表假设已经建好的分库数为8，建一张表，只分库不分表，分库方式为根据ID列哈希。CREATE TABLE multi_db_single_tbl( id bigint not null auto_increment, name varchar(30), primary key(id)) dbpartition by hash(id);查看该逻辑表的节点拓扑，可以看出在每个分库都创建了1张分表，既只做了分库。mysql> show topology from multi_db_single_tbl;+------+------------------------------------------------------------------+---------------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+---------------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_single_tbl || 1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_single_tbl || 2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_single_tbl || 3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_single_tbl || 4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_single_tbl || 5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_single_tbl || 6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_single_tbl || 7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_single_tbl |+------+------------------------------------------------------------------+---------------------+8 rows in set (0.01 sec)分库分表您可以使用如下拆分方式进行分库分表：使用哈希函数做拆分使用双字段哈希函数做拆分使用日期做拆分说明以下示例均假设已经建好的分库数为8。使用哈希函数做拆分建一张表，既分库又分表，每个库含有3张物理表，分库拆分方式为按照ID列进行哈希，分表拆分方式为按照bid列进行哈希。您可以先根据ID列的值进行哈希运算，将表中数据分布在多个子库中，每个子库中的数据再根据bid列值的哈希运算结果分布在3个物理表中。CREATE TABLE multi_db_multi_tbl( id bigint not null auto_increment, bid int, name varchar(30), primary key(id)) dbpartition by hash(id) tbpartition by hash(bid) tbpartitions 3;查看该逻辑表的节点拓扑，可以看出在每个分库都创建了3张分表。mysql> show topology from multi_db_multi_tbl;+------+------------------------------------------------------------------+-----------------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+-----------------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_00 || 1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_01 || 2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_02 || 3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_03 || 4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_04 || 5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_05 || 6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_06 || 7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_07 || 8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_08 || 9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_09 || 10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_10 || 11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_11 || 12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_12 || 13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_13 || 14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_14 || 15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_15 || 16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_16 || 17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_17 || 18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_18 || 19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_19 || 20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_20 || 21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_21 || 22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_22 || 23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_23 |+------+------------------------------------------------------------------+-----------------------+24 rows in set (0.01 sec)查看该逻辑表的拆分规则，可以看出分库分表的拆分方式均为哈希，分库的拆分键为ID，分表的拆分键为bid。mysql> show rule from multi_db_multi_tbl;+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | multi_db_multi_tbl | 0 | id | hash | 8 | bid | hash | 3 |+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.01 sec)使用双字段哈希函数做拆分使用要求拆分键的类型必须是字符类型或数字类型。路由方式根据任一拆分键后N位计算哈希值，以哈希方式完成路由计算。N为函数第三个参数。例如RANGE_HASH(COL1, COL2, N)，计算时会优先选择COL1，截取其后N位进行计算。COL1不存在时按COL2计算。适用场景适合于需要有两个拆分键，并且仅使用其中一个拆分键值进行查询时的场景。假设用户的里已经分了8个物理库，现业务有如下的场景：一个业务想按买家ID和订单ID对订单表进行分库。查询时条件仅有买家ID或订单ID。此时可使用以下DDL对订单表进行构建：create table test_order_tb ( id bigint not null auto_increment, seller_id varchar(30) DEFAULT NULL, order_id varchar(30) DEFAULT NULL, buyer_id varchar(30) DEFAULT NULL, create_time datetime DEFAULT NULL, primary key(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by RANGE_HASH(buyer_id, order_id, 10) tbpartition by RANGE_HASH(buyer_id, order_id, 10) tbpartitions 3;说明两个拆分键皆不能修改。插入数据时如果发现两个拆分键指向不同的分库或分表时，插入会失败。使用日期做拆分除了可以使用哈希函数做拆分算法，您还可以使用日期函数MM、DD、WEEK或MMDD来作为分表的拆分算法，具体步骤请参见如下示例。建一张表，既分库又分表，分库方式为根据userId列哈希，分表方式为根据actionDate列，按照一周七天来拆分（WEEK(actionDate)计算的是DAY_OF_WEEK）。比如actionDate列的值是2017-02-27，这天是星期一，WEEK(actionDate)算出的值是2，该条记录就会被存储到2（2 % 7 = 2）这张分表（位于某个分库，具体的表名是 user_log_2）；比如actionDate列的值是2017-02-26，这天是星期天，WEEK(actionDate)算出的值是1，该条记录就会被存储到1（1 % 7 = 1）这张分表（位于某个分库，具体的表名是 user_log_1）。CREATE TABLE user_log( userId int, name varchar(30), operation varchar(30), actionDate DATE) dbpartition by hash(userId) tbpartition by WEEK(actionDate) tbpartitions 7;查看该逻辑表的节点拓扑，可以看出在每个分库都创建了7张分表（一周7天）。mysql> show topology from user_log;+------+------------------------------------------------------------------+------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_0 || 1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_1 || 2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_2 || 3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_3 || 4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_4 || 5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_5 || 6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_6 || 7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_0 || 8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_1 || 9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_2 || 10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_3 || 11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_4 || 12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_5 || 13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_6 |...| 49 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_0 || 50 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_1 || 51 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_2 || 52 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_3 || 53 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_4 || 54 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_5 || 55 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_6 |+------+------------------------------------------------------------------+------------+56 rows in set (0.01 sec)说明由于返回结果较长，这里用...做了省略处理。查看该逻辑表的拆分规则，可以看出分库的拆分方式为哈希，分库的拆分键为userId，分表的拆分方式为按照时间函数WEEK进行拆分，分表的拆分键为actionDate。mysql> show rule from user_log;+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | user_log | 0 | userId | hash | 8 | actionDate | week | 7 |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.00 sec)查看给定分库键和分表键参数时， SQL被路由到哪个物理分库和该物理分库下的哪张物理表。建一张表，既分库又分表，分库方式为根据userId列哈希，分表方式为根据actionDate列，按照一年12个月进行拆分（MM(actionDate)计算的是MONTH_OF_YEAR）。比如actionDate列的值是2017-02-27，MM(actionDate)算出的值是02，该条记录就会被存储到02（02 % 12 = 02）这张分表（位于某个分库，具体的表名是 user_log_02）。比如actionDate列的值是2016-12-27?#xff0c;MM(actionDate)算出的值是12，该条记录就会被存储到00（12 % 12 = 00）这张分表（位于某个分库，具体的表名是 user_log_00）。CREATE TABLE user_log2( userId int, name varchar(30), operation varchar(30), actionDate DATE) dbpartition by hash(userId) tbpartition by MM(actionDate) tbpartitions 12;查看该逻辑表的节点拓扑，可以看出在每个分库都创建了12张分表（1年有12个月）。mysql> show topology from user_log2;+------+------------------------------------------------------------------+--------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+--------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_00 || 1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_01 || 2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_02 || 3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_03 || 4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_04 || 5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_05 || 6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_06 || 7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_07 || 8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_08 || 9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_09 || 10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_10 || 11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_11 || 12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_00 || 13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_01 || 14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_02 || 15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_03 || 16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_04 || 17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_05 || 18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_06 || 19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_07 || 20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_08 || 21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_09 || 22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_10 || 23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_11 |...| 84 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_00 || 85 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_01 || 86 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_02 || 87 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_03 || 88 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_04 || 89 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_05 || 90 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_06 || 91 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_07 || 92 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_08 || 93 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_09 || 94 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_10 || 95 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_11 |+------+------------------------------------------------------------------+--------------+96 rows in set (0.02 sec)说明由于返回结果较长，这里用...做了省略处理。查看该逻辑表的拆分规则，可以看出分库的拆分方式为哈希，分库的拆分键为userId，分表的拆分方式为按照时间函数MM进行拆分，分表的拆分键为actionDate。mysql> show rule from user_log2;+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | user_log2 | 0 | userId | hash | 8 | actionDate | mm | 12 |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.00 sec)建一张表，既分库又分表，分库方式为根据userId列哈希，分表方式为按照一个月31天进行拆分（函数DD(actionDate)计算的是DAY_OF_MONTH）。比如actionDate列的值是2017-02-27，DD(actionDate)算出的值是27，该条记录就会被存储到27（27 % 31 = 27）这张分表（位于某个分库，具体的表名是user_log_27）。CREATE TABLE user_log3( userId int, name varchar(30), operation varchar(30), actionDate DATE) dbpartition by hash(userId) tbpartition by DD(actionDate) tbpartitions 31;查看该逻辑表的节点拓扑，可以看出在每个分库都创建了31张分表（按每个月有31天处理）。mysql> show topology from user_log3;+------+------------------------------------------------------------------+--------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+--------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_00 || 1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_01 || 2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_02 || 3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_03 || 4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_04 || 5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_05 || 6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_06 || 7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_07 || 8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_08 || 9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_09 || 10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_10 || 11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_11 || 12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_12 || 13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_13 || 14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_14 || 15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_15 || 16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_16 || 17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_17 || 18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_18 || 19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_19 || 20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_20 || 21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_21 || 22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_22 || 23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_23 || 24 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_24 || 25 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_25 || 26 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_26 || 27 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_27 || 28 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_28 || 29 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_29 || 30 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_30 |...| 237 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_20 || 238 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_21 || 239 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_22 || 240 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_23 || 241 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_24 || 242 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_25 || 243 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_26 || 244 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_27 || 245 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_28 || 246 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_29 || 247 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_30 |+------+------------------------------------------------------------------+--------------+248 rows in set (0.01 sec)说明由于返回的结果较长，这里用...做了省略处理。查看该逻辑表的拆分规则，可以看出分库的拆分方式为哈希，分库的拆分键为userId，分表的拆分方式为按照时间函数DD进行拆分，分表的拆分键为actionDate。mysql> show rule from user_log3;+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | user_log3 | 0 | userId | hash | 8 | actionDate | dd | 31 |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.01 sec)建一张表，既分库又分表，分库方式为根据userId列哈希，分表方式为按照一年365天进行拆分，路由到365张物理表（MMDD(actionDate) tbpartitions 365计算的是DAY_OF_YEAR % 365。比如actionDate列的值是2017-02-27，MMDD(actionDate)算出的值是58，该条记录就会被存储到58这张分表（位于某个分库，具体的表名是user_log_58）。CREATE TABLE user_log4( userId int, name varchar(30), operation varchar(30), actionDate DATE) dbpartition by hash(userId) tbpartition by MMDD(actionDate) tbpartitions 365;查看该逻辑表的节点拓扑，可以看出在每个分库都创建了365张分表（按每年有365天处理）。mysql> show topology from user_log4;+------+------------------------------------------------------------------+---------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+---------------+...| 2896 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_341 || 2897 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_342 || 2898 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_343 || 2899 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_344 || 2900 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_345 || 2901 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_346 || 2902 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_347 || 2903 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_348 || 2904 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_349 || 2905 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_350 || 2906 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_351 || 2907 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_352 || 2908 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_353 || 2909 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_354 || 2910 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_355 || 2911 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_356 || 2912 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_357 || 2913 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_358 || 2914 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_359 || 2915 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_360 || 2916 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_361 || 2917 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_362 || 2918 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_363 || 2919 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_364 |+------+------------------------------------------------------------------+---------------+2920 rows in set (0.07 sec)说明由于返回的结果较长，这里用...做了省略处理。查看该逻辑表的拆分规则，可以看出分库的拆分方式为哈希，分库的拆分键为userId，分表的拆分方式为按照时间函数MMDD进行拆分，分表的拆分键为actionDate。mysql> show rule from user_log4;+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | user_log4 | 0 | userId | hash | 8 | actionDate | mmdd | 365 |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.02 sec)建一张表，既分库又分表，分库方式为根据userId列哈希，分表方式为按照一年365天进行拆分，路由到10张物理表（MMDD(actionDate) tbpartitions 10计算的是DAY_OF_YEAR % 10。CREATE TABLE user_log5( userId int, name varchar(30), operation varchar(30), actionDate DATE) dbpartition by hash(userId) tbpartition by MMDD(actionDate) tbpartitions 10;查看该逻辑表的节点拓扑，可以看出在每个分库都创建了10张分表（按照一年365天进行拆分，路由到10张物理表）。mysql> show topology from user_log5;+------+------------------------------------------------------------------+--------------+| ID | GROUP_NAME | TABLE_NAME |+------+------------------------------------------------------------------+--------------+| 0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_00 || 1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_01 || 2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_02 || 3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_03 || 4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_04 || 5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_05 || 6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_06 || 7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_07 || 8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_08 || 9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_09 |...| 70 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_00 || 71 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_01 || 72 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_02 || 73 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_03 || 74 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_04 || 75 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_05 || 76 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_06 || 77 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_07 || 78 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_08 || 79 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_09 |+------+------------------------------------------------------------------+--------------+80 rows in set (0.02 sec)说明由于返回的结果较长，这里用...做了省略处理。查看该逻辑表的拆分规则，可以看出分库的拆分方式为哈希，分库的拆分键为userId，分表的拆分方式为按照时间函数 MMDD进行拆分，路由到10张物理表，分表的拆分键为actionDate。mysql> show rule from user_log5;+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | user_log5 | 0 | userId | hash | 8 | actionDate | mmdd | 10 |+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.01 sec)广播表子句BROADCAST用来指定创建广播表。广播表是指将这个表复制到每个分库上，在分库上通过同步机制实现数据一致，有秒级延迟。这样做的好处是可以将JOIN操作下推到底层的RDS（MySQL），来避免跨库JOIN。关于如何使用广播表来做SQL优化，详情请参见SQL调优指南。CREATE TABLE brd_tbl( id bigint not null auto_increment, name varchar(30), primary key(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;其他MySQL建表属性您在分库分表的同时还可以指定其他的MySQL建表属性，例如：CREATE TABLE multi_db_multi_tbl( id bigint not null auto_increment, name varchar(30), primary key(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(id) tbpartition by hash(id) tbpartitions 3;全局二级索引本小节介绍如何在建表时定义全局二级索引：定义全局二级索引定义全局唯一索引说明以下示例均假设已经建好的分库数为8。定义全局二级索引示例CREATE TABLE t_order ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `order_id` varchar(20) DEFAULT NULL, `buyer_id` varchar(20) DEFAULT NULL, `seller_id` varchar(20) DEFAULT NULL, `order_snapshot` longtext DEFAULT NULL, `order_detail` longtext DEFAULT NULL, PRIMARY KEY (`id`), GLOBAL INDEX `g_i_seller`(`seller_id`) dbpartition by hash(`seller_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);其中：主表：t_order只分库不分表，分库的拆分方式为按照order_id列进行哈希。索引表：g_i_seller只分库不分表，分库的拆分方式为按照seller_id列进行哈希，未指定覆盖列。索引定义子句：GLOBAL INDEX `g_i_seller`(`seller_id`) dbpartition by hash(`seller_id`)。通过SHOW INDEX查看索引信息，包含拆分键order_id上的局部索引，和seller_id、id、order_id上的GSI，其中seller_id为索引表的拆分键，id和order_id为默认的覆盖列（主键和主表的拆分键）。说明关于GSI的限制与约定，请参见使用全局二级索引时的注意事项，关于SHOW INDEX详细说明，请参见SHOW INDEX 。mysql> show index from t_order; +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+ | TABLE | NON_UNIQUE | KEY_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT | INDEX_COMMENT | +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+ | t_order | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | t_order | 1 | auto_shard_key_order_id | 1 | order_id | A | 0 | NULL | NULL | YES | BTREE | | | | t_order | 1 | g_i_seller | 1 | seller_id | NULL | 0 | NULL | NULL | YES | GLOBAL | INDEX | | | t_order | 1 | g_i_seller | 2 | id | NULL | 0 | NULL | NULL | | GLOBAL | COVERING | | | t_order | 1 | g_i_seller | 3 | order_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | | +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+通过SHOW GLOBAL INDEX可以单独查看GSI信息。详细说明请参见SHOW GLOBAL INDEX。mysql> show global index from t_order; +--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ | SCHEMA | TABLE | NON_UNIQUE | KEY_NAME | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS | +--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ | d7 | t_order | 1 | g_i_seller | seller_id | id, order_id | NULL | seller_id | HASH | 8 | | NULL | NULL | PUBLIC | +--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+查看索引表的结构，索引表包含主表的主键、分库分表键和默认的覆盖列，主键列去除了AUTO_INCREMENT属性，并且去除了主表中的局部索引。mysql> show create table g_i_seller;+------------+-----------------------------------------------------------+| Table | Create Table |+------------+-----------------------------------------------------------+| g_i_seller | CREATE TABLE `g_i_seller` ( `id` bigint(11) NOT NULL, `order_id` varchar(20) DEFAULT NULL, `seller_id` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`seller_id`) |+------------+-----------------------------------------------------------+定义全局唯一索引CREATE TABLE t_order ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `order_id` varchar(20) DEFAULT NULL, `buyer_id` varchar(20) DEFAULT NULL, `seller_id` varchar(20) DEFAULT NULL, `order_snapshot` longtext DEFAULT NULL, `order_detail` longtext DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING(`seller_id`, `order_snapshot`) dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);其中：主表：t_order 只分库不分表，分库的拆分方式为按照order_id列进行哈希。索引表：g_i_buyer 只分库且分表，分库和分表的拆分方式均为按照buyer_id列进行哈希，覆盖列包含 seller_id和order_snapshot。索引定义子句：UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING(`seller_id`, `order_snapshot`) dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3。通过SHOW INDEX 查看索引信息，包含拆分键order_id上的局部索引，和buyer_id、id、order_id、seller_id和order_snapshot上的GSI，其中buyer_id为索引表的拆分键，id和order_id 为默认的覆盖列（主键和主表的拆分键），seller_id和order_snapshot为显示指定的覆盖列。说明关于GSI的限制与约定，请参见使用全局二级索引时的注意事项，关于SHOW INDEX详细说明，请参见SHOW INDEX 。mysql> show index from t_order; +--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+ | TABLE | NON_UNIQUE | KEY_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT | INDEX_COMMENT | +--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+ | t_order_dthb | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | t_order_dthb | 1 | auto_shard_key_order_id | 1 | order_id | A | 0 | NULL | NULL | YES | BTREE | | | | t_order | 0 | g_i_buyer | 1 | buyer_id | NULL | 0 | NULL | NULL | YES | GLOBAL | INDEX | | | t_order | 1 | g_i_buyer | 2 | id | NULL | 0 | NULL | NULL | | GLOBAL | COVERING | | | t_order | 1 | g_i_buyer | 3 | order_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | | | t_order | 1 | g_i_buyer | 4 | seller_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | | | t_order | 1 | g_i_buyer | 5 | order_snapshot | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | | +--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+通过SHOW GLOBAL INDEX可以单独查看GSI信息。详细说明请参见SHOW GLOBAL INDEX。mysql> show global index from t_order; +--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ | SCHEMA | TABLE | NON_UNIQUE | KEY_NAME | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS | +--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ | d7 | t_order | 0 | g_i_buyer | buyer_id | id, order_id, seller_id, order_snapshot | NULL | buyer_id | HASH | 8 | buyer_id | HASH | 3 | PUBLIC | +--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+查看索引表的结构，索引表包含主表的主键、分库分表键、默认覆盖列和GSI定义中指定的覆盖列，主键列去除了AUTO_INCREMENT属性，并且去除了主表中局部索引，全局唯一索引默认会创建一份数据表来实现全局的唯一性支持。mysql> show create table g_i_buyer;+-----------+--------------------------------------------------------------------------------------------------------+| Table | Create Table |+-----------+--------------------------------------------------------------------------------------------------------+| g_i_buyer | CREATE TABLE `g_i_buyer` ( `id` bigint(11) NOT NULL, `order_id` varchar(20) DEFAULT NULL, `buyer_id` varchar(20) DEFAULT NULL, `seller_id` varchar(20) DEFAULT NULL, `order_snapshot` longtext, PRIMARY KEY (`id`), UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3 |+-----------+-----------------------------------------------------------------------------------------------------

文章

存储 · SQL · 算法 · 关系型数据库 · MySQL · 测试技术 · 数据库 · 索引 · RDS

2022-10-06

开发指南—DDL语句—分库分表语法—CREATE DATABASE

语法create_database_stmt: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [database_option_list] database_option_list: database_option [database_option ...] database_option: [DEFAULT] {CHARACTER SET | CHARSET} [=] charset_name | [DEFAULT] COLLATE [=] collation_name | LOCALITY=locality_option} | [PARTITION_MODE = partition_mode_option] locality_option: 'dn=storage_inst_id_list' storage_inst_id_list: storage_inst_id[,storage_inst_id_list] partition_mode_option: 'partitioning' |'sharding'参数说明参数说明database_name指定要修改属性的数据库名称。如果不指定，会对当前默认数据库进行修改。CHARSET charset_name指定要修改的字符集。COLLATE collation_name指定校对规则。LOCALITY创建数据库时指定该库的存储位置。PARTITION_MODE指定逻辑库所使用的分区模式，支持两种分区模式：partitioning：使用MySQL类型的分区表建表语法（例如，partition byHash/Range/List）进行分区建表。sharding：使用DRDS模式的分库分表建表语法（例如，dbpartition by / tbpartition by）。如果不指定，默认是partitioning。数据库的分区模式不可更改，建库时一旦指定，不能变更。示例创建数据库test，并指定字符集为UTF8。mysql> create database test PARTITION_MODE=sharding default CHARACTER SET UTF8;Query OK, 1 row affected (0.00 sec)在实例中创建一个数据库，并通过以下命令指定其存储位置为polardbx-storage-0-master节点。mysql> CREATE DATABASE db1 PARTITION_MODE=sharding LOCALITY='dn=polardbx-storage-0-master';说明如果在创建数据库时未指定数据库的存储位置，系统将默认在所有存储节点中均匀分布数据库。数据库中分表的存储位置与该库的存储位置保持一致，以实现分表上的数据隔离。创建成功后，您可以通过以下语句查看数据库的存储位置信息。mysql> SHOW CREATE DATABASE db1 PARTITION_MODE=sharding;返回结果如下所示：+----------+------------------------------------------------------------------------+| DATABASE | CREATE DATABASE |+----------+------------------------------------------------------------------------+| db1 | CREATE DATABASE `db1` / LOCALITY = "dn=polardbx-storage-0-master" / |+----------+------------------------------------------------------------------------+1 row in set

文章

存储 · SQL · 关系型数据库 · MySQL · 分布式数据库 · 数据库 · 数据安全/隐私保护

2022-10-06

【笔记】开发指南—JSON函数

概览PolarDB-X目前支持mysql 5.7的所有json函数，可分为以下五大类：类别函数名JSON创建JSON_ARRAY JSON_OBJECT JSON_QUOTE JSON搜索JSON_CONTAINS JSON_CONTAINS_PATH JSON_EXTRACT -> ->> JSON_KEYS JSON_SEARCH JSON修改JSON_APPEND JSON_ARRAY_APPEND JSON_ARRAY_INSERT JSON_INSERT JSON_MERGE JSON_MERGE_PATCH JSON_MERGE_PRESERVE JSON_REMOVE JSON_REPLACE JSON_SET JSON_UNQUOTE JSON属性JSON_DEPTH JSON_LENGTH JSON_TYPE JSON_VALID JSON工具JSON_PRETTY JSON_STORAGE_SIZE JSON_ARRAY([val [, val] ...])描述：返回包含所有参数值的JSON数组（数组可为空）。示例：mysql> SELECT JSON_ARRAY(123, "polardb-x", NULL, TRUE);+------------------------------------------+| JSON_ARRAY(123, 'polardb-x', NULL, true) |+------------------------------------------+| [123,"polardb-x",null,true] |+------------------------------------------+JSON_OBJECT([key, val [, key, val] ...])描述：根据参数中键值对来创建一个JSON对象，如果key为NULL或参数个数不为2的倍数，将返回相应错误提示。示例：mysql> SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x');+---------------------------------------------+| JSON_OBJECT('id', 123, 'name', 'polardb-x') |+---------------------------------------------+| {"name":"polardb-x","id":123} |+---------------------------------------------+JSON_QUOTE(string)描述：将JSON值用双引号括起来，并对内部特殊处理字符进行转义，返回utf8mb4编码的字符串结果。若参数为NULL，返回值为NULL。该函数通常用于生成一个合法的JSON字符串字面量。示例：mysql> SELECT JSON_QUOTE(null), JSON_QUOTE('"abc"');+------------------+---------------------+| JSON_QUOTE(NULL) | JSON_QUOTE('"abc"') |+------------------+---------------------+| NULL | ""abc"" |+------------------+---------------------+JSON_CONTAINS(target, candidate [, path])描述：判断给定的candidate JSON文档是否包含了target JSON文档；如果给定了path参数，则是判断candidate能否找到包含target的路径path，结果返回1或0。如果只关心candidate是否存在路径path，可以使用JSON_CONTAINS_PATH函数。示例：mysql> SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';mysql> SELECT JSON_CONTAINS(@json_doc, '123', '$.a');+----------------------------------------+| JSON_CONTAINS(@json_doc, '123', '$.a') |+----------------------------------------+| 1 |+----------------------------------------+mysql> SELECT JSON_CONTAINS(@json_doc, 'null', '$.b');+-----------------------------------------+| JSON_CONTAINS(@json_doc, 'null', '$.b') |+-----------------------------------------+| 1 |+-----------------------------------------+mysql> SELECT JSON_CONTAINS(@json_doc, '123', '$.b');+----------------------------------------+| JSON_CONTAINS(@json_doc, '123', '$.b') |+----------------------------------------+| 0 |+----------------------------------------+mysql> SELECT JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c');+-----------------------------------------------+| JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c') |+-----------------------------------------------+| 1 |+-----------------------------------------------+JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)判断给定的json_doc JSON文档是否存在一条或多条路径path，结果返回1或0。参数one_or_all的取值规定如下：'one'：只要json_doc存在参数中的至少一条路径，就返回1，否则返回0；'all'：只有json_doc包含参数中的所有路径时，才返回1，否则返回0。示例：mysql> SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';mysql> SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.a&#39;, &#39;$.e');+----------------------------------------------------+| JSON_CONTAINS_PATH(@json_doc, 'one', '$.a&#39;, &#39;$.e') |+----------------------------------------------------+| 1 |+----------------------------------------------------+mysql> SELECT JSON_CONTAINS_PATH(@json_doc, 'all', '$.a&#39;, &#39;$.e');+----------------------------------------------------+| JSON_CONTAINS_PATH(@json_doc, 'all', '$.a&#39;, &#39;$.e') |+----------------------------------------------------+| 0 |+----------------------------------------------------+mysql> SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d');+-----------------------------------------------+| JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d') |+-----------------------------------------------+| 1 |+-----------------------------------------------+JSON_EXTRACT(json_doc, path[, path] ...)描述：从JSON文档中解析出路径path对应的部分。示例：mysql> SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]');+-------------------------------------------------+| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]') |+-------------------------------------------------+| 456 |+-------------------------------------------------+mysql> SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]&#39;, &#39;$[1]');+---------------------------------------------------------+| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]&#39;, &#39;$[1]') |+---------------------------------------------------------+| [123,456] |+---------------------------------------------------------+mysql> SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]&#39;, &#39;$[2]');+---------------------------------------------------------+| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]&#39;, &#39;$[2]') |+---------------------------------------------------------+| [123,[789,1000]] |+---------------------------------------------------------+column->path描述：->操作符提供了与JSON_EXTRACT函数相同的功能，返回path对应的列值。示例：mysql> SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;+-------------+| NAME |+-------------+| "polardb-x" |+-------------+column->>path描述：->>操作符在->操作符的基础上，对返回结果去除了双引号并反转义（参考JSON_UNQUOTE函数）。该操作符与以下两个函数组合等价：JSON_UNQUOTE( JSON_EXTRACT(column, path) )JSON_UNQUOTE( column -> path )示例：mysql> SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;+-------------+| NAME |+-------------+| "polardb-x" |+-------------+JSON_KEYS(json_doc[, path])以JSON数组的形式返回json_doc对象最外层的key值列表。示例：mysql> SELECT JSON_KEYS('{"a": 123, "b": {"c": 456}}');+------------------------------------------+| JSON_KEYS('{"a": 123, "b": {"c": 456}}') |+------------------------------------------+| ["a","b"] |+------------------------------------------+JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char[, path] ...])返回给定字符串search_str在json_doc中的路径，未找到则返回NULL。search_str中支持包含如LIKE中使用的%和_通配符作为模糊匹配。参数escape_char指定了转义字符，而参数one_or_all的取值规定如下：'one'：返回第一个匹配的路径值（哪一条路径第一个匹配到是不确定的）；'all'：以数组形式返回所有路径值（数组元素顺序是不确定的）。说明 PolarDB-X暂不支持**路径通配符示例：mysql> SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';mysql> SELECT JSON_SEARCH(@json_doc, 'one', 'abc');+--------------------------------------+| JSON_SEARCH(@json_doc, 'one', 'abc') |+--------------------------------------+| "$[0]" |+--------------------------------------+mysql> SELECT JSON_SEARCH(@json_doc, 'all', 'abc');+--------------------------------------+| JSON_SEARCH(@json_doc, 'all', 'abc') |+--------------------------------------+| ["$[0]&#34;,&#34;$[2].k2"] |+--------------------------------------+mysql> SELECT JSON_SEARCH(@json_doc, 'all', 'xyz');+--------------------------------------+| JSON_SEARCH(@json_doc, 'all', 'xyz') |+--------------------------------------+| NULL |+--------------------------------------+mysql> SELECT JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]');+----------------------------------------------------+| JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]') |+----------------------------------------------------+| "$1" |+----------------------------------------------------+mysql> SELECT JSON_SEARCH(@json_doc, 'all', '%a%');+--------------------------------------+| JSON_SEARCH(@json_doc, 'all', '%a%') |+--------------------------------------+| ["$[0]&#34;,&#34;$[2].k2"] |+--------------------------------------+JSON_APPEND(json_doc, path, val [, path, val] ...)即JSON_ARRAY_APPEND函数。说明该函数在MySQL5.7中已过时，在MySQL8.0中被移除。JSON_ARRAY_APPEND(json_doc, path, val [, path, val] ...)描述：将val追加到json_doc指定JSON数组末尾，并返回修改后的json_doc。当有多对(path, val)参数时，该函数会从左到右依次计算每一对参数，并将追加完前一对参数后的结果作为下一次计算的输入。示例：mysql> SET @json_doc = '{"a": 1, "b": [2, 3], "c": 4}'; mysql> SELECT JSON_ARRAY_APPEND(@json_doc, '$.b', 'x'); +------------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$.b', 'x') | +------------------------------------------+ | {"a":1,"b":[2,3,"x"],"c":4} | +------------------------------------------+ mysql> SELECT JSON_ARRAY_APPEND(@json_doc, '$.c', 'y'); +------------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$.c', 'y') | +------------------------------------------+ | {"a":1,"b":[2,3],"c":[4,"y"]} | +------------------------------------------+JSON_ARRAY_INSERT(json_doc, path, val [, path, val] ...)描述：将val插入到json_doc中JSON数组的指定位置，并返回修改后的json_doc。如果val插入位置超出了数组大小，则会追加为最后一个元素。当有多对(path, val)参数时，该函数会从左到右依次计算每一对参数，并将插入完前一对参数后的结果作为下一次计算的输入。示例：mysql> SET @json_doc #61; '["a", {"b": [1, 2]}, [3, 4]]';mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x');+-------------------------------------------+| JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x') |+-------------------------------------------+| ["a","x",{"b":[1,2]},[3,4]] |+-------------------------------------------+mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x');+--------------------------------------------+| JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x') |+--------------------------------------------+| ["a",{"b":[1,2]},[3,4],"x"] |+--------------------------------------------+mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x');+------------------------------------------------+| JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x') |+------------------------------------------------+| ["a",{"b":[1,"x",2]},[3,4]] |+------------------------------------------------+mysql> SELECT JSON_ARRAY_INSERT(@json_doc, '$[0]&#39;, &#39;x&#39;, &#39;$3', 'y');+-----------------------------------------------------------+| JSON_ARRAY_INSERT(@json_doc, '$[0]&#39;, &#39;x&#39;, &#39;$3', 'y') |+-----------------------------------------------------------+| ["x","a",{"b":[1,2]},[3,"y",4]] |+-----------------------------------------------------------+JSON_INSERT(json_doc, path, val [, path, val] ...)描述：将val插入到json_doc的指定位置。当有多对(path, val)参数时，该函数会从左到右依次计算每一对参数，并将插入完前一对参数后的结果作为下一次计算的输入。插入val时的行为，将区分以下两种情况：如果指定path处本身已存在值，则会忽略当前这轮处理的(path, val)对，不会覆盖原本已存在的值；如果指定path处不存在值，则分为以下三种情况：如果指定path处是对象内不存在的成员，则直接在对象内添加对应的键值对；如果指定path处是超出数组下标的元素，则会将val添加到该数组末尾（标量值会自动包装为只含一个元素的数组）；其他情况，则会忽略当前这轮处理的(path, val)对，不采取任何操作。请参考JSON_REPLACE函数、JSON_SET函数来比较三者用法的不同。示例：mysql> SET @json_doc = '{ "a": 1, "b": [2, 3]}';mysql> SELECT JSON_INSERT(@json_doc, '$.a&#39;, 10, &#39;$.c', '[true, false]');+-----------------------------------------------------------+| JSON_INSERT(@json_doc, '$.a&#39;, 10, &#39;$.c', '[true, false]') |+-----------------------------------------------------------+| {"a":1,"b":[2,3],"c":"[true, false]"} |+-----------------------------------------------------------+JSON_MERGE(json_doc, json_doc [, json_doc] ...)即JSON_MERGE_PRESERVE函数（注：该函数在MySQL5.7中已过时，在MySQL8.0.3中被移除）。JSON_MERGE_PATCH(json_doc, json_doc [, json_doc] ...)合并多个JSON文档，且会对相同的key进行去重。该函数将从左到右依次合并两个json_doc（下称为doc1与doc2），合并规则如下：如果doc1不是对象类型，则合并结果即为doc2；如果doc2不是对象类型，则合并结果也为doc2；如果doc1与doc2都是对象类型，则合并结果是包含了以下成员的对象：key只存在于doc1、而不存在于doc2的所有doc1成员；key只存在于doc2、而不存在于doc1，且值不是null字面量的所有doc2成员；对于key既存在于doc1、又存在于doc2且在doc2中值不为null字面量的成员，会将key对应的两个value再按上述规则进行递归合并。根据上述规则可知，如果有重复key则会保留doc2中对应的值。示例：mysql> SELECT JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}');+----------------------------------------------------------+| JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}') |+----------------------------------------------------------+| {"name":"polardb-x","id":123} |+----------------------------------------------------------+mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');+--------------------------------------------------+| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |+--------------------------------------------------+| {"a":1} |+--------------------------------------------------+mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', -> '{ "a": 5, "d":6 }');+---------------------------------------------------------------------------------+| JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |+---------------------------------------------------------------------------------+| {"a":5,"b":2,"c":4,"d":6} |+---------------------------------------------------------------------------------+JSON_MERGE_PRESERVE(json_doc, json_doc [, json_doc] ...)描述：合并多个JSON文档，会保留所有的值而不会根据相同key去重。合并规则如下：两个数组的合并结果为一个数组；两个对象的合并结果为一个对象；标量类型值会自动包装为数组类型进行合并；一个对象和一个数组合并，则会把对象包装为含单个元素数组并进行合并。示例：(可以对比与JSON_MERGE_PATCH函数的不同)mysql> SELECT JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}');+-------------------------------------------------------------+| JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}') |+-------------------------------------------------------------+| {"name":"polardb-x","id":123} |+-------------------------------------------------------------+mysql> SELECT JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}');+-----------------------------------------------------+| JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}') |+-----------------------------------------------------+| {"a":1,"b":[2,null]} |+-----------------------------------------------------+mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', -> '{ "a": 5, "d":6 }');+------------------------------------------------------------------------------------+| JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |+------------------------------------------------------------------------------------+| {"a":[1,3,5],"b":2,"c":4,"d":6} |+------------------------------------------------------------------------------------+JSON_REMOVE(json_doc, path [, path] ...)描述：删除json_doc下指定路径的元素。当有多个path参数时，该函数会从左到右依次计算路径并删除元素，并将前一次删除后的结果作为下一次计算的输入。若指定路径不存在，不会报错。示例：mysql> SET @json_doc = '["a", ["b", "c"], "d"]';mysql> SELECT JSON_REMOVE(@json_doc, '$[1]');+--------------------------------+| JSON_REMOVE(@json_doc, '$[1]') |+--------------------------------+| ["a","d"] |+--------------------------------+JSON_REPLACE(json_doc, path, val [, path, val] ...)描述：用val替换json_doc的指定位置的值，只会替换已存在的值。当有多对(path, val)参数时，该函数会从左到右依次计算每一对参数，并将计算完前一对参数后的结果作为下一次计算的输入。若指定路径不存在，不会报错。示例：mysql> SET @json_doc = '{ "a": 1, "b": [2, 3]}';mysql> SELECT JSON_REPLACE(@json_doc, '$.a&#39;, 10, &#39;$.c', '[true, false]');+------------------------------------------------------------+| JSON_REPLACE(@json_doc, '$.a&#39;, 10, &#39;$.c', '[true, false]') |+------------------------------------------------------------+| {"a":"10","b":[2,3]} |+------------------------------------------------------------+JSON_SET(json_doc, path, val [, path, val] ...)描述：在json_doc的指定位置插入或更新值val。该函数会从左到右依次计算每一对参数，并将计算完前一对参数后的结果作为下一次计算的输入。将区分以下两种情况：如果指定path处本身已存在值，会用参数val的值覆盖原本已存在的值；如果指定path处不存在值，则分为以下三种情况：如果指定path处是对象内不存在的成员，则直接在对象内添加对应的键值对；如果指定path处是超出数组下标的元素，则会将val添加到该数组末尾（标量值会自动包装为只含一个元素的数组）；其他情况，则会忽略当前这轮处理的(path, val)对，不采取任何操作。示例：mysql> SET @json_doc = '{ "a": 1, "b": [2, 3]}';mysql> SELECT JSON_SET(@json_doc, '$.a&#39;, 10, &#39;$.c', '[true, false]');+-------------------------------------------------+| JSON_SET(@j, '$.a&#39;, 10, &#39;$.c', '[true, false]') |+-------------------------------------------------+| {"a":10,"b":[2,3],"c":[true,false]} |+-------------------------------------------------+JSON_UNQUOTE(json_val)描述：去除json值外围的双引号，对转义字符进行反转义，返回utf8mb4编码的字符串结果。示例：mysql> SELECT JSON_UNQUOTE('"abc"');+-----------------------+| JSON_UNQUOTE('"abc"') |+-----------------------+| abc |+-----------------------+mysql> SELECT JSON_UNQUOTE('"a\tbc"');+--------------------------+| JSON_UNQUOTE('"a\tbc"') |+--------------------------+| a bc |+--------------------------+mysql> SELECT JSON_UNQUOTE('"\t\u0032"');+------------------------------+| JSON_UNQUOTE('"\t\u0032"') |+------------------------------+| 2 |+------------------------------+JSON_DEPTH(json_doc)描述：返回json_doc的最大深度，规则如下：空数组、空对象以及标量类型，深度均为1；只有单个元素的数组深度为1；对象中如果所有成员的value深度均为1，则自身的深度为2。示例：mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');+-------------------------------+| JSON_DEPTH('[10, {"a": 20}]') |+-------------------------------+| 3 |+-------------------------------+JSON_LENGTH(json_doc [, path])描述：返回json_doc的长度，规则如下：标量类型的长度为1；数组类型的长度为数组元素个数；对象类型的长度为对象成员个数；结果不包含内部嵌套的数组或对象长度。示例：mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');+-----------------------------------------+| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |+-----------------------------------------+| 2 |+-----------------------------------------+JSON_TYPE(json_val)描述：返回参数json_val的json类型。示例：mysql> SET @json_obj = '{"a": [10, true]}';mysql> SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a'));+-------------------------------------------+| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a')) |+-------------------------------------------+| ARRAY |+-------------------------------------------+mysql> SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]'));+----------------------------------------------+| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]')) |+----------------------------------------------+| INTEGER |+----------------------------------------------+mysql> SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]'));+----------------------------------------------+| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]')) |+----------------------------------------------+| BOOLEAN |+----------------------------------------------+JSON_VALID(val)描述：判断参数值val是否为合法的json格式。示例：mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');+---------------------+-----------------------+| JSON_VALID('hello') | JSON_VALID('"hello"') |+---------------------+-----------------------+| 0 | 1 |+---------------------+-----------------------+JSON_PRETTY(json_doc)描述：通过添加换行与缩进，以更直观的方式打印出json_doc。示例：mysql> SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';mysql> SELECT JSON_PRETTY(@json_doc);+---------------------------------------------------------------------------+| JSON_PRETTY(@json_doc) |+---------------------------------------------------------------------------+| [ "abc", [ { "k1":123 }, "def" ], { "k2":"abc" }, { }] |+---------------------------------------------------------------------------+JSON_STORAGE_SIZE(json_doc)描述：返回json_doc以二进制形式存储的字节大小。示例：mysql> SET @json_doc = '[999, "polardb-x", [1, 2, 3], 888.88]';mysql> SELECT JSON_STORAGE_SIZE(@json_doc) AS Size;+------+| Size |+------+| 48 |+------+

文章

存储 · JSON · 关系型数据库 · MySQL · 数据格式

2022-10-06

【笔记】开发指南—JSON函数

文章

存储 · JSON · 关系型数据库 · MySQL · 数据格式

2022-10-06

【笔记】开发指南—函数—加密和压缩函数

支持的加密和压缩函数PolarDB-X目前支持mysql 5.7的大部分未过时的加密和压缩函数，具体信息见下表：函数名描述AES_DECRYPT 使用AES算法解密AES_ENCRYPT 使用AES算法加密RANDOM_BYTES 返回随机字节向量MD5 计算MD5 128位校验和SHA1, SHA 计算SHA-1 160位校验和SHA2 计算SHA2校验和对于加密和压缩函数，如果需要存储字节类型的返回结果，推荐使用VARBINARY或BLOB字段，避免尾部空白字符的移除或字符集转换问题（如CHAR/VARCHAR/TEXT类型）。AES_DECRYPT(crypt_str, key_str [, init_vector])根据输入的密文crypt_str、密钥key_str以及初始向量init_vector（可选参数），返回解密后的明文结果。具体解密算法与使用方法可参考AES_ENCRYPT函数。AES_ENCRYPT(str, key_str [, init_vector])根据输入的明文str、密钥key_str以及初始向量init_vector（可选参数），返回加密后的密文结果。在加密时具体使用的AES算法由系统变量block_encryption_mode决定，该变量取值的格式为aes-keylen-mode，其中keylen为密钥的位长度（合法取值为128/192/256），mode为加密模式，PolarDB-X支持以下六种加密模式：加密模式是否需要初始向量ECB否CBC是CFB1是CFB8是CFB128是OFB是对于需要初始向量的加密模式，初始向量必须大于等于16字节（超出16字节部分将自动截断）；对于不需要初始向量的加密模式，初始向量参数将被自动忽略。示例：mysql> SET block_encryption_mode = 'aes-128-ofb'; mysql> SET @iv = RANDOM_BYTES(16); mysql> SET @key = SHA2('secret key', 224); mysql> set @crypto = AES_ENCRYPT('polardb-x', @key, @iv); mysql> select @crypto; +---------------------------+ | @crypto | +---------------------------+ | ?÷s,(?????A}?O | +---------------------------+ mysql> SELECT AES_DECRYPT(@crypto, @key, @iv); +---------------------------------+ | AES_DECRYPT(@crypto, @key, @iv) | +---------------------------------+ | polardb-x | +---------------------------------+RANDOM_BYTES(len)返回len字节长度的随机二进制字符串，长度len的合法取值为1~1024。示例：mysql> select HEX(RANDOM_BYTES(16));+----------------------------------+| HEX(RANDOM_BYTES(16)) |+----------------------------------+| C83CF8A2499F407E15F34F6E32948CEA |+----------------------------------+MD5(str)计算MD5 128位检验和。示例：mysql> select MD5('polardb-x');+----------------------------------+| MD5('polardb-x') |+----------------------------------+| fa4900656bcd39dc90024e733fa4531f |+----------------------------------+SHA1(str), SHA(str)计算SHA-1 160位校验和。该函数安全性强于MD5。示例：mysql> select SHA1('polardb-x');+------------------------------------------+| SHA1('polardb-x') |+------------------------------------------+| a2e83af051f032b500f13c369976298208d821d1 |+------------------------------------------+SHA2(str, hash_length)计算SHA-2族散列算法结果，参数hash_length决定了检验和结果的位数，合法取值包括：224、256、384、512和0（等价于256）。该函数安全性强于MD5和SHA1。示例：mysql> select SHA2('polardb-x', 384);+--------------------------------------------------------------------------------------------------+| SHA2('polardb-x', 384) |+--------------------------------------------------------------------------------------------------+| 20222037666be5234d9af3c391f9c3a1a3e39b910f3f8081c32d972acca890c818d6c70025ff6c6d4b648bd91d66a3fe |+--------------------------------------------------------------------------------------------------+

文章

存储 · 算法 · 关系型数据库 · MySQL · 数据安全/隐私保护

2022-10-06

MySQL 8.0中对EXISTS、NOT EXISTS的持续优化

MySQL在8.0.16版本之前，对 IN和 EXISTS处理是不一样的，EXISTS只能采用子查询方式，所以执行计划中能看到DEPENDENT SUBQUERY。但可以把IN优化成semi join，优化器开关（optimizer_switch）中有几个相关的开关loosescan=on firstmatch=on duplicateweedout=on materialization=onMySQL从8.0.16开始，增加对EXISTS的优化，和IN一样也支持自动转换成semi join。从8.0.18开始，又增加了对NOT EXISTS转变成anti join的优化。我们来看下同一个SQL在5.7版本和8.0.18版本中的不同表现1. 测试环境两个测试表的DDL# t1表共有30万条记录[[email protected]]> show create table t1\G 1. row ** Table: t1Create Table: CREATE TABLE t1 ( id int unsigned NOT NULL AUTO_INCREMENT, seq int unsigned NOT NULL DEFAULT '0', name varchar(20) NOT NULL DEFAULT '', x int DEFAULT NULL, PRIMARY KEY (id), KEY k1 (seq)) ENGINE=InnoDB AUTO_INCREMENT=300001;# t2表共有19条记录[[email protected]]> show create table t2\G 1. row ** Table: t2Create Table: CREATE TABLE t2 ( id int NOT NULL, nu int DEFAULT NULL, name varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (id)) ENGINE=InnoDB;数据随机填充。2. MySQL 5.7下的执行计划及成本5.7还不支持anti-join优化，只能是用子查询。[[email protected]]> explain select * from t1 where not exists ( select 1 from t2 where t1.x = t2.nu);+----+--------------------+-------+------+------+---------+------+--------+----------+-------------+| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------+------+---------+------+--------+----------+-------------+| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | 376310 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | 19 | 10.00 | Using where |+----+--------------------+-------+------+------+---------+------+--------+----------+-------------+[[email protected]]> show warnings;+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1276 | Field or reference 'yejr.t1.x' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | / select#1 / select yejr.t1.id AS id,yejr.t1.seq AS seq,yejr.t1.name AS name,yejr.t1.x AS x from yejr.t1 where exists(/ select#2 / select 1 from yejr.t2 where (yejr.t1.x = yejr.t2.nu)) is false |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+#该SQL耗时 1.34秒[[email protected]]> select * from t1 where not exists ( select 1 from t2 where t1.x = t2.nu);299994 rows in set (1.34 sec)#从统计结果中的 Handler_read_rnd_next 值来看，应该是做了一次笛卡尔积扫描[[email protected]]> show status like 'handl%read%';+-----------------------+---------+| Variable_name | Value |+-----------------------+---------+| Handler_read_first | 300001 || Handler_read_key | 300001 |...| Handler_read_rnd_next | 6299939 |+-----------------------+---------+这里要纠个偏，不少人说MySQL对子查询支持不好，实际上是因为优化器无法将这个子查询改写优化成JOIN查询。像下面这个子查询SQL，就可以被自动优化了[[email protected]]> explain select * from t1 where exists ( select nu from t2 where t1.seq=t2.nu and nu >= 10);+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+----------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+----------------------------------------+| 1 | SIMPLE | t2 | range | nu | nu | 4 | NULL | 17 | 111.76 | Using where; Using index; LooseScan || 1 | SIMPLE | t1 | ref | k1 | k1 | 4 | yejr.t2.nu | 1 | 100.00 | Using join buffer (Batched Key Access) |+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+----------------------------------------+2 rows in set, 2 warnings (0.00 sec)[[email protected]]> show warnings;| Level | Code | Message || Note | 1276 | Field or reference 'yejr.t1.seq' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | / select#1 / select `yejr`.`t1`.`id` AS `id`,`yejr`.`t1`.`seq` AS `seq`,`yejr`.`t1`.`name` AS `name`,`yejr`.`t1`.`x` AS `x` from `yejr`.`t1` semi join (`yejr`.`t2`) where ((`yejr`.`t1`.`seq` = `yejr`.`t2`.`nu`) and (`yejr`.`t2`.`nu` >= 10)) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3. MySQL 8.0.19下的执行计划及成本这时支持对anti-join的优化，优化器会对SQL进行改写优化。[[email protected]]> explain select * from t1 where not exists ( select 1 from t2 where t1.x = t2.nu);+----+--------------+-------------+--------+---------------------+---------------------+---------+-----------------+--------+----------+-------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+--------+---------------------+---------------------+---------+-----------------+--------+----------+-------------------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 376310 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | zhishutang.t1.x | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 19 | 100.00 | NULL |+----+--------------+-------------+--------+---------------------+---------------------+---------+-----------------+--------+----------+-------------------------+3 rows in set, 2 warnings (0.00 sec)#直接优化成anti join了[[email protected]]> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1276 | Field or reference 'yejr.t1.x' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | / select#1 / select yejr.t1.id AS id,yejr.t1.seq AS seq,yejr.t1.name AS name,yejr.t1.x AS x from yejr.t1 anti join (yejr.t2) on((<subquery2>.nu = yejr.t1.x)) where true |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+# explain analyze结果[[email protected]]> explain analyze select * from t1 where not exists ( select 1 from t2 where t1.x = t2.nu);| -> Nested loop anti-join (actual time=0.058..248.704 rows=299994 loops=1) -> Table scan on t1 (cost=37975.50 rows=376310) (actual time=0.035..82.504 rows=300000 loops=1) -> Single-row index lookup on <subquery2> using <auto_distinct_key> (nu=t1.x) (actual time=0.000..0.000 rows=0 loops=300000) -> Materialize with deduplication (actual time=0.000..0.000 rows=0 loops=300000) -> Filter: (t2.nu is not null) (cost=2.15 rows=19) (actual time=0.006..0.011 rows=19 loops=1) -> Table scan on t2 (cost=2.15 rows=19) (actual time=0.005..0.009 rows=19 loops=1)#该SQL耗时 0.15 秒[[email protected]]> select * from t1 where not exists ( select 1 from t2 where t1.x = t2.nu);299994 rows in set (0.15 sec)[[email protected]]> show status like 'handl%read%';+-----------------------+--------+| Variable_name | Value |+-----------------------+--------+| Handler_read_first | 2 || Handler_read_key | 200574 |...| Handler_read_rnd_next | 300021 |相对于5.7的性能有了很大提升。4. 一个小小的脑洞测试过程中我突发奇想，在MySQL 8.0.19版本下，如果把oiptimizer_switch里的semijoin关闭后，应该也相当于关闭anti join优化。用EXPLAIN ANALYZE再查看这个SQL的执行计划及耗时，结果是这样的| -> Filter: exists(select #2) (cost=37975.50 rows=376310) (actual time=54.977..1976.963 rows=6 loops=1) -> Table scan on t1 (cost=37975.50 rows=376310) (actual time=0.054..70.366 rows=300000 loops=1) -> Select #2 (subquery in condition; dependent) -> Limit: 1 row(s) (actual time=0.006..0.006 rows=0 loops=300000) -> Filter: (t1.x = t2.nu) (cost=0.44 rows=2) (actual time=0.006..0.006 rows=0 loops=300000) -> Table scan on t2 (cost=0.44 rows=19) (actual time=0.002..0.004 rows=19 loops=300000)看起来的确如此。在8.0.19中，也修复了松华老师之前在8.0.18版本遇到的小bug。最后提醒大家不要轻易关闭 semijoin 开关，以防连 anti-join 优化也跟着消失，这就不划算了。MySQL优化器的确在不断进步中，欣慰，也建议大家适时更新到高版本。参考：Antijoin in MySQL 8（https://mysqlserverteam.com/antijoin-in-mysql-8/ ）MySQL 8 Anti-Join 几点总结全文完。 </div>

文章

SQL · 关系型数据库 · MySQL · 测试技术

2022-10-06

MySQL 8.0 hash join有重大缺陷？(2)

徐春阳老师发文爆MySQL 8.0 hash join有重大缺陷。文章核心观点如下：多表（比如3个个表）join时，只会简单的把表数据量小的放在前面作为驱动表，大表放在最后面，从而导致可能产生极大结果集的笛卡尔积，甚至耗尽CPU和磁盘空间。就此现象，我也做了个测试。1. 利用TPC-H工具准备测试环境TPC-H工具在这里下载 http://www.tpc.org/tpch/default5.asp。默认并不支持MySQL，需要自己手动做些调整，参见 https://imysql.com/2012/12/21/tpch-for-mysql-manual.html。在本案中，我指定的 Scale Factor 参数是10，即：[[email protected] dbgen]# ./dbgen -s 10 && ls -l *tbl -rw-r--r-- 1 root root 244847642 Apr 14 09:52 customer.tbl -rw-r--r-- 1 root root 7775727688 Apr 14 09:52 lineitem.tbl -rw-r--r-- 1 root root 2224 Apr 14 09:52 nation.tbl -rw-r--r-- 1 root root 1749195031 Apr 14 09:52 orders.tbl -rw-r--r-- 1 root root 243336157 Apr 14 09:52 part.tbl -rw-r--r-- 1 root root 1204850769 Apr 14 09:52 partsupp.tbl -rw-r--r-- 1 root root 389 Apr 14 09:52 region.tbl -rw-r--r-- 1 root root 14176368 Apr 14 09:52 supplier.tbl2. 创建测试表，导入测试数据。查看几个表的数据量分别是：+----------+------------+----------+----------------+-------------+--------------+| Name | Row_format | Rows | Avg_row_length | Data_length | Index_length |+----------+------------+----------+----------------+-------------+--------------+| customer | Dynamic | 1476605 | 197 | 291258368 | 0 || lineitem | Dynamic | 59431418 | 152 | 9035579392 | 0 || nation | Dynamic | 25 | 655 | 16384 | 0 || orders | Dynamic | 14442405 | 137 | 1992294400 | 0 || part | Dynamic | 1980917 | 165 | 327991296 | 0 || partsupp | Dynamic | 9464104 | 199 | 1885339648 | 0 || region | Dynamic | 5 | 3276 | 16384 | 0 || supplier | Dynamic | 99517 | 184 | 18366464 | 0 |+----------+------------+----------+----------------+-------------+--------------+提醒：几个测试表都不要加任何索引，包括主键，上表中 Index_length的值均为0。3. 运行测试SQL本案选用的MySQL版本是8.0.19：[[email protected]]> \s...Server version: 8.0.19-commercial MySQL Enterprise Server - Commercial...徐老师是在用TPC-H中的Q5时遇到的问题，本案也同样选择这个SQL。不过，本案主要测试Hash Join，因此去掉了其中的GROUP BY和ORDER BY子句。先看下执行计划吧，都是全表扫描，好可怕...[[email protected]]> desc select count(*)-> from-> customer,-> orders,-> lineitem,-> supplier,-> nation,-> region-> where-> c_custkey = o_custkey-> and l_orderkey = o_orderkey-> and l_suppkey = s_suppkey-> and c_nationkey = s_nationkey-> and s_nationkey = n_nationkey-> and n_regionkey = r_regionkey-> and r_name = 'AMERICA'-> and o_orderdate >= date '1993-01-01'-> and o_orderdate < date '1993-01-01' + interval '1' year;+----------+------+----------+----------+----------------------------------------------------+| table | type | rows | filtered | Extra |+----------+------+----------+----------+----------------------------------------------------+| region | ALL | 5 | 20.00 | Using where || nation | ALL | 25 | 10.00 | Using where; Using join buffer (Block Nested Loop) || supplier | ALL | 98705 | 10.00 | Using where; Using join buffer (Block Nested Loop) || customer | ALL | 1485216 | 10.00 | Using where; Using join buffer (Block Nested Loop) || orders | ALL | 14932433 | 1.11 | Using where; Using join buffer (Block Nested Loop) || lineitem | ALL | 59386314 | 1.00 | Using where; Using join buffer (Block Nested Loop) |+----------+------+----------+----------+----------------------------------------------------+加上 format=tree 再看下（真壮观啊。。。） 1. row **EXPLAIN: -> Aggregate: count(0)-> Inner hash join (lineitem.L_SUPPKEY = supplier.S_SUPPKEY), (lineitem.L_ORDERKEY = orders.O_ORDERKEY) (cost=40107736685515472896.00 rows=4010763818487343104) -> Table scan on lineitem (cost=0.07 rows=59386314) -> Hash -> Inner hash join (orders.O_CUSTKEY = customer.C_CUSTKEY) (cost=60799566599072.12 rows=6753683238538) -> Filter: ((orders.O_ORDERDATE >= DATE'1993-01-01') and (orders.O_ORDERDATE < <cache>((DATE'1993-01-01' + interval '1' year)))) (cost=0.16 rows=165883) -> Table scan on orders (cost=0.16 rows=14932433) -> Hash -> Inner hash join (customer.C_NATIONKEY = nation.N_NATIONKEY) (cost=3664985889.79 rows=3664956624) -> Table scan on customer (cost=0.79 rows=1485216) -> Hash -> Inner hash join (supplier.S_NATIONKEY = nation.N_NATIONKEY) (cost=24976.50 rows=24676) -> Table scan on supplier (cost=513.52 rows=98705) -> Hash -> Inner hash join (nation.N_REGIONKEY = region.R_REGIONKEY) (cost=3.50 rows=3) -> Table scan on nation (cost=0.50 rows=25) -> Hash -> Filter: (region.R_NAME = 'AMERICA') (cost=0.75 rows=1) -> Table scan on region (cost=0.75 rows=5)看起来的确是把最小的表放在最前面，把最大的放在最后面。在开始跑之前，我们先看一眼手册中关于Hash Join的描述，其中有一段是这样的：Memory usage by hash joins can be controlled using the join_buffer_sizesystem variable; a hash join cannot use more memory than this amount. When the memory required for a hash join exceeds the amount available, MySQL handles this by using files on disk. If thishappens, you should be aware that the join may not succeed if a hash join cannot fit into memory and it creates more files than set for open_files_limit. To avoid such problems, make either of the following changes:- Increase join_buffer_size so that the hash join does not spill over to disk.- Increase open_files_limit.简言之，当 join_buffer_size 不够时，会在hash join的过程中转储大量的磁盘表（把一个hash表切分成多个小文件放在磁盘上，再逐个读入内存进行hash join），因此建议加大 join_buffer_size，或者加大 open_files_limit 上限。所以，正式开跑前，我先把join_buffer_size调大到1GB，并顺便看下其他几个参数值：[[email protected]]> select @@join_buffer_size, @@tmp_table_size, @@innodb_buffer_pool_size;+--------------------+------------------+---------------------------+| @@join_buffer_size | @@tmp_table_size | @@innodb_buffer_pool_size |+--------------------+------------------+---------------------------+| 1073741824 | 16777216 | 10737418240 |+--------------------+------------------+---------------------------+并且为了保险起见，在执行SQL时也用 SET_VAR（8.0新特性）设置了 join_bufer_size，走起。好在最后这个SQL有惊无险的执行成功，总耗时2911秒。# Query_time: 2911.426483 Lock_time: 0.000251 Rows_sent: 1 Rows_examined: 76586082当然了，这个SQL执行过程中的代价也确实非常大，产生了大量的磁盘（不可见）临时文件。我每隔几秒钟就统计一次所有临时文件的总大小，并且观察磁盘空间剩余量。/data 分区最开始可用空间是 373GB，这条SQL在峰值吃掉了约170GB，着实可怕。# 刚开始/dev/vdb 524032000 132967368 391064632 26% /data# 峰值时/dev/vdb 524032000 319732288 204299712 62% /dataCPU的负载从监控上看倒是还算能接受，最高约38.4%4. 补充测试上面的测试中，优化器"擅自"修改了驱动顺序，那加上straight_join看看会怎样[[email protected]]> EXPLAIN STRAIGHT_JOIN select count(*)from customer straight_join orders straight_join lineitem straight_join supplier straight_join nation straight_join regionwhere c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= date '1993-01-01' and o_orderdate < date '1993-01-01' + interval '1' year;+----------+----------+----------+----------------------------------------------------+| table | rows | filtered | Extra |+----------+----------+----------+----------------------------------------------------+| customer | 1485216 | 100.00 | NULL || orders | 14932433 | 1.11 | Using where; Using join buffer (Block Nested Loop) || lineitem | 59386314 | 10.00 | Using where; Using join buffer (Block Nested Loop) || supplier | 98705 | 1.00 | Using where; Using join buffer (Block Nested Loop) || nation | 25 | 10.00 | Using where; Using join buffer (Block Nested Loop) || region | 5 | 20.00 | Using where; Using join buffer (Block Nested Loop) |+----------+----------+----------+----------------------------------------------------+#format=tree模式下| -> Aggregate: count(0) -> Inner hash join (region.R_REGIONKEY = nation.N_REGIONKEY) (cost=204565289351994015744.00 rows=8021527039324357632) -> Filter: (region.R_NAME = 'AMERICA') (cost=0.00 rows=1) -> Table scan on region (cost=0.00 rows=5) -> Hash -> Inner hash join (nation.N_NATIONKEY = customer.C_NATIONKEY) (cost=200554431911464173568.00 rows=-9223372036854775808) -> Table scan on nation (cost=0.00 rows=25) -> Hash -> Inner hash join (supplier.S_NATIONKEY = customer.C_NATIONKEY), (supplier.S_SUPPKEY = lineitem.L_SUPPKEY) (cost=160446786739199049728.00 rows=-9223372036854775808) -> Table scan on supplier (cost=0.00 rows=98705) -> Hash -> Inner hash join (lineitem.L_ORDERKEY = orders.O_ORDERKEY) (cost=16253562153466286.00 rows=16253535510797654) -> Table scan on lineitem (cost=0.01 rows=59386314) -> Hash -> Inner hash join (orders.O_CUSTKEY = customer.C_CUSTKEY) (cost=24638698342.46 rows=2736915995) -> Filter: ((orders.O_ORDERDATE >= DATE'1993-01-01') and (orders.O_ORDERDATE < <cache>((DATE'1993-01-01' + interval '1' year)))) (cost=0.94 rows=165883) -> Table scan on orders (cost=0.94 rows=14932433) -> Hash -> Table scan on customer (cost=153126.35 rows=1485216)最后实际执行耗时[[email protected]]> mysql> select /+ set_var(join_buffer_size=1073741824) / STRAIGHT_JOIN count(*)...+----------+| count(*) |+----------+| 72033 |+----------+1 row in set (4 min 12.31 sec)这个SQL执行过程中，只产生了很少几个临时文件，影响几乎可以忽略不计的那种。这次之所以会比较快，是因为orders 表在第二顺序执行，对它还附加了WHERE条件，过滤后数据量变小了（全表1500万，过滤后227万），因此整体执行时间缩短了。靠着 straight_join拯救了危机。此外，在测试的过程中，我还做过一次只有3个表的全表join，下面是执行计划[[email protected]]> desc select count(*) from orders o , lineitem l, partsupp ps whereo.O_CUSTKEY = l.L_SUPPKEY and l.L_PARTKEY = ps.PS_AVAILQTY;+-------+----------+----------+----------------------------------------------------+| table | rows | filtered | Extra |+-------+----------+----------+----------------------------------------------------+| ps | 7697248 | 100.00 | NULL || l | 59386314 | 10.00 | Using where; Using join buffer (Block Nested Loop) || o | 14932433 | 10.00 | Using where; Using join buffer (Block Nested Loop) |+-------+----------+----------+----------------------------------------------------+在这个执行计划中，就不会出现徐老师说的问题，不再简单的把最小的表作为驱动表，最大的表放在最后面。这条SQL耗时304秒，还好吧。# Query_time: 304.889654 Lock_time: 0.000178 Rows_sent: 1 Rows_examined: 82986052写在最后在前几天我的文章《MySQL没前途了吗？》中，其实已经说了MySQL目前不适合做OLAP业务，即便有Hash Join也不行，毕竟其适用的场景很有限。本案中几个表完全没任何索引，这属于很极端的场景，不应该允许此类现象发生。另外，在已经明确需要走Hash Join的情况下，就应该人为干预，提前加大join_buffer_size，减少执行过程中生成的临时文件。当然了，如果遇到多表JOIN不符合预期时，还可以用STRAIGHT_JOIN强制设定驱动顺序，也可以规避这个问题。不过，MySQL在偏OLAP场景上的性能的确还有很大提升空间，对此我持谨慎乐观态度，比如把ClickHouse给直接收编了呢：）对于本文，我心里不是很有底气，毕竟不是啥源码大神，如果理解上的错误，还请留言指正，不吝感激。SQL优化大神郑松华对本文亦有贡献，谢谢二位老师。全文完。 </div>

文章