案例0:binlog_format为mixed时,对于一些批量更新的sql非常有利,比如update xx set name='xxx';但是有一些条件下mysql检测到无法满足statement模式时,会自动转换成row模式,这时如果主库一个sql是全表扫描,那么反应到从库上时就是N次全表扫描了,这通常也是很多情况下用户问:为什么我主库上执行1分钟,但从库上执行却需要1小时以上,从库再怎么单线程也不至于这么慢吧?下面是官网给出的在mixed下,自动从statement转换成row模式的条件
When running in MIXED logging format, the server automatically switches from statement-based to rowbased logging under the following conditions:
• When a function contains UUID().
• When one or more tables withAUTO_INCREMENT columns are updated and a trigger or stored
function is invoked. Like all other unsafe statements, this generates a warning ifbinlog_format =
STATEMENT.
• When the body of a view requires row-based replication, the statement creating the view also uses it. For
example, this occurs when the statement creating a view uses theUUID() function.
• When a call to a UDF is involved.
• When any INSERT DELAYED is executed for a nontransactional table.
• If a statement is logged by row and the session that executed the statement has any temporary tables,
logging by row is used for all subsequent statements (except for those accessing temporary tables) until
all temporary tables in use by that session are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all
subsequent statements using that table are unsafe. The server approximates this condition by treating
all statements executed during the session as unsafe until the session no longer holds any temporary
tables.
• When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
• When USER(), CURRENT_USER(), or