mysql没有主键导致延迟_【转载】备库由于表无主键导致延迟

由于ROW模式的复制已经广泛使用，但对于没有主键的表而言，如果发生大更新，在备库上会表现出极大的延迟，因为在binlog中产生的大量行记录将无法根据主键快速查找，最差的情况，需要对每条修改的记录进行全表扫描。

5.6已经解决了这个问题，可以只扫描一次表；5.5最新的版本只是在错误日志里输出了一些信息。

Port 5.6的实现不太现实，因为改动太大。因此我做了些小改动，对于无主键表上的DELETE/UPDATE，转换为STATEMENT模式的binlog记录。

以下是一个改动非常简单的patch，基于Percona5.5.18

Index: /PS5518/branches/PS-r3633-nopk-logstmt/sql/sys_vars.cc

===================================================================

--- /PS5518/branches/PS-r3633-nopk-logstmt/sql/sys_vars.cc(revision 3639)

+++ /PS5518/branches/PS-r3633-nopk-logstmt/sql/sys_vars.cc(revision 3641)

@@ -396,6 +396,13 @@

CMD_LINE(OPT_ARG), DEFAULT(FALSE),

NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(binlog_direct_check));

+static Sys_var_mybool Sys_binlog_use_stmt_for_non_pk(

+ "binlog_use_stmt_for_non_pk",

+ "if a table doesn't have primary key ,then log the changes (SQLCOM_DELETE"

+ "and SQLCOM_UPDATE) using STATEMENT.",

+ SESSION_VAR(binlog_use_stmt_for_non_pk),

+ CMD_LINE(OPT_ARG), DEFAULT(FALSE));

+

static Sys_var_ulong Sys_bulk_insert_buff_size(

"bulk_insert_buffer_size", "Size of tree cache used in bulk "

"insert optimisation. Note that this is a limit per thread!",

Index: /PS5518/branches/PS-r3633-nopk-logstmt/sql/sql_class.h

===================================================================

--- /PS5518/branches/PS-r3633-nopk-logstmt/sql/sql_class.h(revision 3639)

+++ /PS5518/branches/PS-r3633-nopk-logstmt/sql/sql_class.h(revision 3641)

@@ -492,6 +492,7 @@

ulong binlog_format; ///< binlog format for this thd (see enum_binlog_format)

my_bool binlog_direct_non_trans_update;

+ my_bool binlog_use_stmt_for_non_pk;

my_bool sql_log_bin;

ulong completion_type;

ulong query_cache_type;

Index: /PS5518/branches/PS-r3633-nopk-logstmt/sql/sql_class.cc

===================================================================

--- /PS5518/branches/PS-r3633-nopk-logstmt/sql/sql_class.cc(revision 3639)

+++ /PS5518/branches/PS-r3633-nopk-logstmt/sql/sql_class.cc(revision 3641)

@@ -4495,10 +4495,14 @@

Get the capabilities vector for all involved storage engines and

mask out the flags for the binary log.

*/

+ my_bool table_no_key= false;

for (TABLE_LIST *table= tables; table; table= table->next_global)

{

if (table->placeholder())

continue;

+

+ if (table->table->s->primary_key >= MAX_KEY)

+ table_no_key= true;

if (table->table->s->table_category == TABLE_CATEGORY_PERFORMANCE ||

table->table->s->table_category == TABLE_CATEGORY_LOG)

@@ -4680,6 +4684,13 @@

/* log in row format! */

set_current_stmt_binlog_format_row_if_mixed();

}

+ /*if there is a table without any primary key,log in stmt format*/

+ else if (table_no_key &&

+ (variables.binlog_use_stmt_for_non_pk) &&

+ (variables.binlog_format == BINLOG_FORMAT_ROW ) &&

+ (lex->sql_command == SQLCOM_DELETE ||

+ lex->sql_command == SQLCOM_UPDATE))

+ clear_current_stmt_binlog_format_row();

}

}