二進制日志binlog作用：
1、以二進制形式記錄更改資料庫的SQL語句（insert,update,delete,create,drop,alter等）
2、用于Mysql主從複制
3、增量資料庫備份及恢複
日志會記錄成每一行資料被修改成的形式，然後再slave端再對相同的資料進行修改，隻記錄要修改的資料，隻有value，不會有sql多表關聯的情況。
優點：在row模式下，bin-log中可以不記錄執行的sql語句的上下文相關資訊，僅僅需要記錄哪一條記錄被修改了，修改成什麼信樣了，是以row的日志内容會非常清楚的記錄下每一行資料修改的細節，非常容易了解。而且不會出現在某些特定情況下的存儲過程和function，以及trigger的調用和處罰無法被正确複制問題。
缺點：在row模式下，所有執行的語句當記錄到日志中的時候，都将以每行記錄的修改來記錄，這樣可能會産生大量的日志内容。比如有這樣一條update語句：updateproduct set name='gongli' 這條語句不是記錄的一條，而是修改每一條的都會記錄下來
每一條會修改資料的sql都會記錄到master的binlog中，slave在複制的時候sql程序會解析成和原來master端相同的sql再執行。
優點：在Statement模式下首先就是解決了row模式下的缺點，不需要記錄記錄每一行日志的變化，減少了bin-log日志量，節省了I/O以及存儲資源，提高性能。因為它們隻需要激勵在master上所執行的語句的細節以及執行語句時候的上下文資訊。
缺點：在Statement模式下，由于它記錄的執行語句，是以，為了讓這些語句在slave端也能正确執行，那麼它還必須記錄每條語句在執行的時候的一些相關資訊，也就是上下文資訊，以保證所有語句在slave端被執行的時候能夠得到和在master端執行時候的結果。另外，由于MySQL現在發展較快，很多的新功能不斷的加入，使MySQL的複制遇到了不小的挑戰，自然複制的時候涉及到越複雜的内容，bug也就越容易出現。在Statement中，目前已經發現不少情況會造成MySQL的複制出現問題，主要是修改資料的時候使用了某些特定的函數或者功能的時候會出現。比如：sleep()函數在有些版本中就不能直接複制，在存儲過程中使用了last_insert_id()函數，可能會使slave和master上得到不一緻的id等等。由于rowlevel是基于每一行來記錄的變化，是以不會出現類似的問題
從官方文檔中看到，之前的MySQL一直都隻有基于Statement的複制模式，知道5.1.5版本的MySQL才開始支援row模式。從5.0開始，MySQL的複制已經解決了大量老版本中出現的無法正确複制的問題。但是由于存儲過程的出現，給MySQL replication又帶來了更大的挑戰。另外，看到官方文檔說，從5.1.8版本開始，MySQL提供了除Statement和row之外的第三種模式：mixed，實際上就是前兩種模式的結合。在mixed模式下，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和row之間選擇一種。新版本中的Statement還是和以前一樣，僅僅記錄執行的語句。而新版本的MySQL中對row模式也做了優化，并不是所有的修改都會以row模式來記錄，比如遇到表結構變更的時候就會以Statement模式來記錄，如果sql語句确實是update或者delete等修改資料的語句，那麼還是會記錄所有行的變更。
Statement:
優點：記錄的簡單，内容少
缺點：導緻主從不一緻
例：
100w條記錄
updatetest set name='tintin';
binilog 裡面就隻用update test set name='tintin';
ROW 行級模式
優點：記錄資料詳細（每行），主從一直
缺點：占用大量的磁盤空間，降低了磁盤的性能
binilog 裡面就用100w條update test set name='tintin';語句
MIXED混合模式
對于函數，觸發器，存儲過程
會自動的使用row-level模式
[root@db01 ~]# mysql -uroot -p789 -S/data/3306/mysql.sock -e "show variables like '%log_bin%';"
+---------------------------------+-------+
| Variable_name | Value |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
配置檔案裡面配置
[root@db01 ~]# egrep "log-bin"/data/3306/my.cnf
log-bin = /data/3306/mysql-bin
[root@db01 ~]# mysql -uroot -p789 -S/data/3306/mysql.sock -e "show variables like '%binlog_format%';"
+---------------+-----------+
| Variable_name | Value |
| binlog_format | STATEMENT|
[root@db01 3306]# ls
data mysql-bin.000001 mysql-bin.000004 mysqld.pid
my.cnf mysql-bin.000002 mysql-bin.000005 mysql_oldboy3306.err
mysql mysql-bin.000003 mysql-bin.index mysql.sock
[root@db01 3306]# mysqlbinlog mysql-bin.000005
<a href="http://s1.51cto.com/wyfs02/M02/86/92/wKioL1fEFEWj2_7PAABgkFuSPsw227.jpg-wh_500x0-wm_3-wmp_4-s_148908413.jpg" target="_blank"></a>
update一條資料
mysql> select * from test2;
+----+--------+
| id | name |
| 1 | oldboy|
| 2 | gongli|
| 3 |inca |
| 4 |sb |
| 5 |kaka |
| 6 |doubi |
6 rows in set (0.00 sec)
mysql> update test2 set name="laowang"where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test2;
+----+---------+
| id | name |
| 1 |oldboy |
| 2 |gongli |
| 3 |inca |
| 4 |sb |
| 5 |laowang |
| 6 |doubi |
檢視binlog日志記錄
# at 7610
#160828 22:39:12 server id 1 end_log_pos 7717 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1472395152/*!*/;
update test2 set name="laowang" whereid=5
/*!*/;
# at 7717
#160828 22:39:12 server id 1 end_log_pos 7744 Xid = 299
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> show variables like 'binlog_format';
| binlog_format | STATEMENT |
1 row in set (0.02 sec)
mysql> set global binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)
1 row in set (0.00 sec)
mysql> show global variables like'binlog_format';
+---------------+-------+
| Variable_name | Value |
| binlog_format | ROW |
臨時修改：
mysql> set global binlog_format ='ROW';
永久修改：
修改my.cnf配置檔案
update表test2
mysql> select * from test2;
mysql> update test2 setname='dadou';
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from test2;
+----+-------+
| id | name |
| 1 | dadou|
| 2 | dadou|
| 3 | dadou|
| 4 | dadou
data mysql mysql-bin.000005 mysql-bin.000007 mysql-bin.index mysql_oldboy3306.err
my.cnf mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysqld.pid mysql.sock
[root@db01 3306]# mysqlbinlog mysql-bin.000008
BINLOG '
GP3CVxMBAAAAMQAAAJ0CAAAAACMAAAAAAAEABm9sZGJveQAFdGVzdDIAAgP+Av48AA==
GP3CVxgBAAAAogAAAD8DAAAAACMAAAAAAAEAAv///AEAAAAGb2xkYm95/AEAAAAFZGFkb3X8AgAA
AAZnb25nbGn8AgAAAAVkYWRvdfwDAAAABGluY2H8AwAAAAVkYWRvdfwEAAAAAnNi/AQAAAAFZGFk
b3X8BQAAAAdsYW93YW5n/AUAAAAFZGFkb3X8BgAAAAVkb3ViafwGAAAABWRhZG91 這裡我們會看到這些編碼
'/*!*/;
# at 831
#160828 23:02:48 server id 1 end_log_pos 858 Xid = 12
檢視row模式下的編碼
mysqlbinlog --base64-output="decode-rows"--verbose mysql-bin.000008
再次檢視
<a href="http://s1.51cto.com/wyfs02/M02/86/92/wKiom1fEFHTwqZzYAAB4WTyumqM113.jpg-wh_500x0-wm_3-wmp_4-s_3995186017.jpg" target="_blank"></a>
本文轉自 kesungang 51CTO部落格，原文連結：http://blog.51cto.com/sgk2011/1843651，如需轉載請自行聯系原作者
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)