mysql怎么实现行级索_MySQL Replication--TABLE_ID与行格式复制

BINLOG中的TABLE_ID

在ROW格式的二进制中，事件信息中没有列的信息，需要通过Table_Map将表名对于的表信息加载到cache中，然后根据事件信息中的列下标来定位到数据列，每次表信息加载到Cache中时，会得到一个自增的ID值，即Table_ID：

# at 1794#190717 13:08:44 server id 4294967295 end_log_pos 1855 GTID last_committed=5 sequence_number=6 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa01:9'/*!*/;

# at1855#190717 13:08:44 server id 4294967295 end_log_pos 1919 Query thread_id=306 exec_time=0 error_code=0

SET TIMESTAMP=1563340124/*!*/;BEGIN

/*!*/;

# at1919#190717 13:08:44 server id 4294967295 end_log_pos 1956Rows_query

#delete fromtb001

# at1956#190717 13:08:44 server id 4294967295 end_log_pos 2002 Table_map: `db001`.`tb001` mapped to number 108# at2002#190717 13:08:44 server id 4294967295 end_log_pos 2069 Delete_rows: table id 108flags: STMT_END_F

BINLOG'XK0uXR3/JQAAAKQHAACAABFkZWxldGUgZnJvbSB0YjAwMQ==

XK0uXRP/LgAAANIHAAAAAGwAAAAAAAEABWRiMDAxAAV0YjAwMQACAwMAAg==

XK0uXSD/QwAAABUIAAAAAGwAAAAAAAEAAgAC//wIAAAAAQAAAPwPAAAAAQAAAPwWAAAAAQAA

APwdAAAAAQAAAA=='/*!*/;

###DELETE FROM`db001`.`tb001`

###WHERE###@1=8 /*INT meta=0 nullable=0 is_null=0*/###@2=1 /*INT meta=0 nullable=1 is_null=0*/###DELETE FROM`db001`.`tb001`

###WHERE###@1=15 /*INT meta=0 nullable=0 is_null=0*/###@2=1 /*INT meta=0 nullable=1 is_null=0*/###DELETE FROM`db001`.`tb001`

###WHERE###@1=22 /*INT meta=0 nullable=0 is_null=0*/###@2=1 /*INT meta=0 nullable=1 is_null=0*/###DELETE FROM`db001`.`tb001`

###WHERE###@1=29 /*INT meta=0 nullable=0 is_null=0*/###@2=1 /*INT meta=0 nullable=1 is_null=0*/# at2069#190717 13:08:44 server id 4294967295 end_log_pos 2096 Xid = 30

COMMIT/*!*/;

在基于行的复制模式下，为什么BINLOG中使用TABLE_ID而不直接使用表名：

1、如果某事务中一条SQL语句修改了100万行记录，那么会在该事务对应的BINLOG中记录这100万记录相应的信息，通过TABLE_ID方式来映射表名，那么仅需要存储一次表名即可，后面的使用TABLE_ID代替，可以有效降低BINLOG长度。

导致TABLE_ID发生变化的操作有：

1>DDL语句执行2>Flush Tables语句执行3>Table被加载到Table Cahche

MYSQL使用Table Cache来存放表定义信息，当Table Cache中无空闲空间来存放新的表信息后，会根据算法将一部分近期没有被使用的表信息换出Table Cache。如果TableCache设置过小，而数据库中存在大量的用户表需要加载到Table Cahche中，那么会导致表定义信息频繁地被换入换出，导致Table_ID急剧增大。

查看TABLE CACHE相关信息：

#查看TableCache的配置

SHOW VARIABLESLIKE '%table%cache%';+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| table_definition_cache | 16384 |

| table_open_cache | 16384 |

| table_open_cache_instances | 16 |

+----------------------------+-------+

#查看缓存的表定义信息

SHOW STATUSLIKE '%open%table%';+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| Com_show_open_tables | 0 |

| Open_table_definitions | 118 |

| Open_tables | 231 |

| Opened_table_definitions | 6 |

| Opened_tables | 100 |

| Slave_open_temp_tables | 0 |

+--------------------------+-------+

Open_table_definitions： The number ofcached .frm files.

Opened_table_definitions ： Thenumber of.frm files that have been cached.

Open_tables： Thenumber of tables that are open.

Opened_tables ： Thenumber of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

如果Open_tables等于或接近table_open_cache，说明Table Cache已满或已快满。

如果Opened_tables值较大，则说明表定义信息被频繁换入换出Table Cache，参数table_open_cache可能设置过小。

TABLE_ID导致的BUG

在定义Table id时采用8byte的ulong类型列来存放，但在同步的SQL线程中使用4byte的uint类型列来存放，因此当同步的SQL线程中Table id值超过2^32的时，会导致应用SQL失败，即主库上的二进制可以同步到从库的中继日志中，但应用二进制日志日志失败，导致主从数据丢失。

PS1： 基于STATEMENT格式的二进制日志不需要使用table_id来查看表信息

PS2： 主库和从库上的Table ID没有任何关联关系，每个实例上的Table id都是独立生成的。