MySQL 5.6的72個新特性（譯）

一，安全提高


1.提供儲存加密認證資訊的方法，使用.mylogin.cnf檔案。使用mysql_config_editor可以建立此檔案。這個檔案可以進行連線資料庫的訪問授權。mysql_config_editor會進行加密而不是明文儲存。客戶端只會在記憶體中進行解密。這樣密碼以非明文方式儲存，不會在命令列或者環境變數中暴露。更多資訊，訪問 Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.


2.使用sha256_password，支援更為強大的使用者密碼加密方式。這個外掛是內建的。更多資訊訪問 Section 6.3.6.2, “The SHA-256 Authentication Plugin


3.mysql.user表現在增加password_expired列，預設值是’N'，使用新的ALTER USER命令可以設定為’Y'。當密碼過期後，使用此賬號的後續連線都會報錯，只到使用者使用SET PASSWORD命令建立一個新密碼。更多資訊訪問Section 13.7.1.1, “ALTER USERSyntax”


4.現在提供密碼安全策略


? 使用明文指定密碼時，密碼會被當前的密碼策略檢查，如果太弱會被拒絕（返回ER_NOT_VALID_PASSWORD 錯誤）。會影響 CREATE USER, GRANT, 和 SET PASSWORD命令。密碼作為引數被password()，old_password()引用時也會被檢查。


? 密碼的強狀程度可以被新函式VALIDATE_PASSWORD_STRENGTH() 檢查。此函式把密碼做為引數，返回0(弱)-100（強）。


? 以上都是validate_password外掛提供，更多資訊見 Section 6.1.2.6, “The Password Validation Plugin”.


? mysql_upgrade如果發現使用4.1以前的雜湊密碼會警告。這樣的賬號必須升級到更安全的雜湊密碼。見Section 6.1.2.4, “Password Hashing in MySQL”


5.登入記錄被更改，所以密碼不會再被明文記載在general log，bin log,slow log。見Section 6.1.2.3, “Passwords and Logging”


6.start slave語句被修改，可以指定引數。密碼可以存放在master.info 檔案。見Section 13.4.2.5, “START SLAVE Syntax”?


二，預設值更改


7. 從5.6.6開始，預設值與以前不同，動機是提供更好的效能，避免手工更改。見 Section 5.1.2.1, “Changes to Server Defaults”.


三，Innodb加強


8. 可以在Innodb 表上建全文索引，使用 MATCH() … AGAINST語句。這個特性包括一個新的近似搜尋符號 @，和幾種新的配置項以及INFORMATION_SCHEMA表。見Section 14.2.4.12.3, “FULLTEXT Indexes”?


9. 幾種ALTER TABLE操作不再拷備表，不會阻塞insert,update,delete或者全部寫入操作。這就是所謂的online DDL.見Section 14.2.2.6, “Online DDL for InnoDB Tables”


10.單獨表空間下，對於.ibd files你有更多的自主性。 file-per-table模式建立表時可以指定MySQL資料目錄以外的目錄。比如，把壓力大的表放到SSD裝置上，或者把一個大表放到一個大HDD上。你可以把一個表從一個例項匯出，然後匯入另一個例項，不會引起因為快取資料，進行中的事務以及內部因素如 space ID以及LSN引起的資料不一致。見：Section 14.2.5.2.33, “Tablespace Management”


11.你可以設定Innodb的未壓縮表的page size 為8KB或者4KB，或者預設的16KB。使用引數innodb_page_size來配置。建立例項的時候指定此引數。同一例項Innodb tablespaces共享此頁面大小。更小的頁面大小對某種混合壓力負載可以避免冗餘或者低效的IO，尤其是針對塊大小小的SSD 裝置。


blackjack, roulette, slots, slots, blackjack,roulette, casino, blackjack, blackjack, blackjack,slots
12.改進的 adaptive flushing 演算法使得多種workloads下I/O操作更有效率和一致性。新演算法和預設配置期待可以提高多數使用者的效能和併發。見Section 14.2.5.2.8, “Improvements to Buffer Pool Flushing”


13.你可以透過NoSQL API開發應用訪問InnnoDB表。它使用流行的memcached守護程式來響應對於key-value對的Add,Set和GET請求。這樣避免瞭解析和構建query execution plan的成本。你可以使用NoSQL API或者SQL來訪問同一份資料。比如：你可以透過NoSQL API快速訪問和查詢表資料，使用SQL來進行復雜查詢以及相容已有的應用。更多見Section 14.2.10, “InnoDB Integration with memcached” 。


14.Innodb的最佳化器統計會以更確定的間隔收集，同時伺服器重啟後還能保持，使得plan stability更穩定。你還可以控制InnoDB索引的樣本數量，使得最佳化器的統計更準確，以提主查詢執行計劃。更多見 Section 14.2.5.2.9, “Persistent Optimizer Statistics for InnoDB Tables”


15.最佳化了只讀事務，提高了特定查詢和報告生成應用的效能和併發。這個最佳化是自動的，或者你可以指定START TRANSACTION READ ONLY 引數。更多見 Section 14.2.5.2.2, “Optimizations for Read-Only Transactions”


16.可以把Innodb 的undo log移出system tablespace到一個或者多個獨立的tablespaces。undo log的I/O模式使得將這些表空間移到SSD裝置成為一個好選擇，同時將系統表空間放在普通磁碟上。更多見：Section 14.2.5.2.3, “SeparateTablespaces for InnoDB Undo Logs”


17.Innodb redo log大小從最大4GB提高到512GB，透過引數 innodb_log_file_size 配置。


18.–innodb-read-only 選項可以讓MySQL執行在只讀模式。你可以透過DVD,CD等只讀媒體訪問Innodb 表，還可以共享資料目錄來建立多個資料倉儲。更多見： Section 14.2.6.1, “Support for Read-Only Media”


19.多個關於Innodb的新INFORMATION_SCHEMA表，提供了關於buffer pool，表的後設資料，索引，資料字典中的外來鍵，以及更細的效能粒度資訊，補充了Performance Schema表的資訊。


20.開啟多個表時，Innodb限制了保持表資訊的記憶體。


21.Innodb強化了幾種內部效能，包括拆分kernel mutex來減少競爭，將重新整理操作移出主執行緒，允許使用多個清理執行緒，以及減少了大記憶體系統中的buffer_pool競爭。


22.Innodb使用了一種新的，更快的演算法來檢測deadlocks. 所有死鎖資訊都可以被記錄到錯誤日誌。


23.為了避免大buffer_pool的例項重啟服務時過長的warmup 時間，你可以在重啟後立即載入快取頁面。MySQL可以在關閉時匯出完全資料檔案，檢閱此檔案找出重啟時需要載入的pages。你可以在任何手工匯入匯出buffer_pool，比如在效能測試時或者在執行復雜的OLAP查詢後。


四，分割槽


24.最大分割槽數量提高到8192，這包括所有的分割槽和子分割槽。


25.使用ALTER TABLE … EXCHANGE PARTITION 可以在未分割槽表和分割槽表和子分割槽表之間交換分割槽。這可以用來匯出匯入分割槽。更多見：Section 17.3.3, “Exchanging Partitions and Subpartitions with Tables”.


26.可以在分割槽表中顯示查詢一個或者多個分割槽，或者更改資料。比如，表t有int 列c，有4個分割槽p0-03，查詢SELECT * FROM t PARTITION (p0, p1) WHERE c < 5 只返回po,p1符合條件結果。


以下語句支援顯式分割槽查詢


SELECT
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA
LOAD XML
譯：更多見：Section 17.5, “Partition Selection”.


27.減少分割槽鎖提高了有很多分割槽的表的多數DML和DDL操作，減少的是未被語句影響的分割槽上的鎖。這樣的語句包括SELECT, SELECT … PARTITION, UPDATE, REPLACE, INSERT, 以及很多其它語句。更多，包括哪些語句效能提高見Section 17.6.4, “Partitioning and Locking”.


五，Performance Schema


28.記錄表的輸入與輸出，操作包括行級訪問表和臨時表，如insert,upate,delete.


29.表的事件過濾，以庫或者表名為基礎。


30.執行緒的事件過濾，更多關於執行緒的資訊被蒐集


31.表和索引I/O以及表鎖的統計表。


32.記錄命令以及命令的階段。


33.服務啟動的的時候配置引數，以前只能執行時設設定。


六，複製和日誌


34.支援以全域性統一事務ID（GTID)為基礎的複製。當在主庫上提交事務或者被從庫應用時，可以定位和追蹤每一個事務。


35.使用–gtid-mode and –enforce-gtid-consistency 引數啟動複製可以開啟GTIDs。更多資訊見Section 16.1.4.5, “Global Transaction ID Options and Variables”.


36.如果使用了GTIDs，啟動一個新的複製從庫或切換到一個新的主庫，就不必依賴log檔案或者pos位。


37.GTID複製是全部以事務為基礎，使得檢查主從一致性變得非常簡單。如果所有主庫上提交的事務也同樣提交到從庫上，一致性就得到了保證。


38.更多見Section 16.1.3, “Replication with Global Transaction Identifiers”.


39.行復制現在支援行映像控制。可以只記錄唯一定位列和變化列（非全部列），這樣可以節省磁碟空間，網路流量和記憶體使用。你可以使用引數binlog_row_image ，設定為minimal(記錄必要列)，full(全部列)，noblob（不包括blob或者text的所有列）來控制最小或者最大記錄。更多見System variables used with the binary log,


40.binlog的讀寫現在是崩潰安全的，因為只有完整的事件（或者事務）才會被記錄和讀取。預設會記錄事件的大小以及事件本身，使用大小來驗證事件被正確記錄。你也可以使用引數binlog_checksum 設定使用crc32記錄事件的校驗值。使用引數 master_verify_checksum可以讓服務讀取校驗值。–slave-sql-verify-checksum 引數使從庫讀relay日誌的時候讀取校驗值 。


41.MySQL支援在表中儲存主庫連線資訊了。使用引數–master-info-repository 和 –relay-log-info-repository 來設定。設定 –master-info-repository 為表，會記錄連線資訊到slave_master_info 表。設定–relay-log-info-repository 為表，會記錄relay log資訊到slave_relay_log_info表。這幾個表都是自動建立在mysql系統庫。


重要：為了保證複製安全，lave_master_info 和 slave_relay_log_info表必須使用事務引擘比如innodb，預設是使用myisam引擘,意味著在開始複製前，你必須把這些表改為事務引擘，以保證安全。使用語句ALTER TABLE … ENGINE=… 完成。如果複製執行時，請不要更改


42.mysqlbinlog 現在支援以原始格式備份binlog，使用選項–read-from-remote-server and –raw。mysqlbinlog連線伺服器，請求日誌檔案，以原始格式寫入輸出檔案。參見：Section 4.6.8.3, “Usingmysqlbinlog to Back Up Binary Log Files”.


43.MySQL現在支援延遲複製，預設是0秒。使用CHANGE MASTER TO引數 MASTER_DELAY 來設定延遲。


44.延遲複製用來防護主庫的使用者誤操作（DBA可以回滾一個延遲複製從庫到誤操作前）或者測試當系統有延遲時系統行為。見Section 16.3.9, “Delayed Replication”.


45.如果複製從庫有多個網路介面，可以只使用CHANGE MASTER TO 命令的引數MASTER_BIND 來只使用其中一個。


46.增加系統引數log_bin_basename ，指定完整的路徑和檔名，log_bin 只控制是否開啟binlog.


同樣適用relay_log_basename 。


47.現在支援多執行緒複製。如果開啟，sql執行緒作為協調者協調多個工作執行緒，數量取決於slave_parallel_workers 。現在多執行緒複製以單庫為基礎，特定庫的更新的相對順序和主庫一樣。不過，沒有必要協調不同庫之間的事務。事務可以被分佈到每個庫，意味著一個複製從庫的工作執行緒可以順序執行事務而不必等待其它庫的更新完成。


48.既然不同庫的事務在主從庫的上順序可能不同，簡單的最近執行的事務不能保證以前的事務在從庫都執行了。這對於多執行緒複製時日誌和恢復有特殊意義。對於如何在多執行緒複製的時候解釋binlog資訊，請見Section 13.7.5.35,“SHOW SLAVE STATUS Syntax”


七，最佳化器提升


49.查詢最佳化器對於以下查詢（子查詢）更有效率SELECT … FROM single_table … ORDER BYnon_index_column[DESC] LIMIT [M,]N;


此類在大結果集中顯示幾行的查詢在網站中很常見。如：SELECT col1, … FROM t1 … ORDER BY name LIMIT 10; SELECT col1, … FROM t1 … ORDER BY RAND() LIMIT 15;


排序快取由 sort_buffer_size.指定。如果Ｎ行被排序的元素足夠小可以被入排序快取(M+N行如果M被指定)，可以避免使用合併檔案，整個查詢可以都放入記憶體中。見Section 8.2.1.3, “Optimizing LIMIT Queries”


50.最佳化器使用MRR。使用非聚集索引進行索引掃描時，如果表很大沒有快取，會導致大量的隨機磁碟訪問。使用MRR最佳化，最佳化器會先對掃描索引，然後收集每行的主鍵並對主鍵排序，此時就可以用主鍵順序訪問基表。這樣就用順序訪問代替了隨機磁碟訪問。


51.最佳化器使用ICP，沒有ICP，引擘使用索引定位行，並返回給服務層，使用where丟棄不符合條件記錄。使用ICP後，如果索引中只有部分能被where條件利用，MySQL將where條件壓到引擘層。引擘層使用索引項進行評估，只有滿足條件的會被讀取。ICP可以減少引擘層對基表的訪問，同時減少了服務層對引擘層的訪問。更多見：Section 8.13.4, “Index Condition Pushdown Optimization”


52.EXPLAIN命令現在可以用在DELETE, INSERT, REPLACE,UPDATE語句上。以前，只能用在查詢語句上。另外，現在支援json格式輸出。見Section 13.8.2, “EXPLAIN Syntax”


53.最佳化器地於from子句的子查詢更有效率。查詢執行中會物化子查詢以提高效能。另外，最佳化器還可能對派生表建立索引來加速行檢索。更多見：Section 8.13.15.3, “Optimizing Subqueries in the FROMClause (Derived Tables)”


54.最佳化器使用semi-join和物化來最佳化子查詢，見：Section 8.13.15.1, “Optimizing Subqueries with Semi-Join Transformations”, and Section 8.13.15.2, “Optimizing Subqueries with Subquery Materialization”


55.對關聯表查詢或者使用join buffer的查詢，新演算法BKA被使用。BKA支援inner join,outer join,semi-join，包手nested outer joins 和 nested semi-joins。好處是提高了表掃描的效能。更多見：Section 8.13.11, “Block Nested-Loop and Batched Key Access Joins”


56.最佳化器有追蹤功能，對於開發者很有用。optimizer_trace_xxx 系統引數和INFORMATION_SCHEMA.OPTIMIZER_TRACE 表提供介面，更多見 MySQL Internals: Tracing the Optimizer.


八，條件處理


57.MySQL現在支援 GET DIAGNOSTICS 命令，可以提供診斷資訊，如前一條sql命令為什麼有異常，更多見：Section 13.6.7.3, “GET DIAGNOSTICS Syntax”


58.另外，一些低效的條件處理被修正，使得更像標準的SQL


59.可以在某個塊範圍定義選擇哪個handler，以前一個儲存程式只能有一個全域性的handler.


60.條件順序被更準確的解決。


61.診斷區清理改變了。錯誤#55843導致條件處理在handler被啟用以前被清理，使得條件資訊無效。現在可以使用GET DIAGNOSTICS得到此資訊。當handler存在時條件資訊會被清理掉，如果它還沒有在handler執行時被清理的話。


62.以前當條件觸發時handler立即被啟用。現在只有當條件執行完成後再啟用，這樣可以選擇更為合適的handler.這對於觸發多個條件的語句和以往的處理是不同的，如當某個更高優先順序的條件稍後被觸發，而在此範圍內有handlers可以處理這些條件時。以前，只有第一個觸發的會被選擇，即使它的優先順序低。現在更高優先順序的會被選擇，即使不是第一個觸發的。


更多請見Section 13.6.7.6, “Scope Rules for Handlers”


九，資料型別


63.TIME, DATETIME, 和 TIMESTAMP 支援更小時的時間粒度，精確到微秒（6位）。見Section 11.3.6, “Fractional Seconds in Time Values”


64.以前每個表最多隻有一個TIMESTAMP 列可以用當前時間初始化和更新。這個限制沒有了。所有TIMESTAMP列都可以設定這2個屬性。另外，DATETIME 也支援這些屬性了。更多見Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”


65.可以用引數explicit_defaults_for_timestamp關閉預設以前的TIMESTAMP 的預設值及自動初始化、更新屬性。更多見 Section 5.1.4, “Server System Variables”。


66.YEAR(2)型別被拋棄，現存的表中的year(2)列會和以前一樣處理，但新建或者修改表結構時會轉化為YEAR(4).更多見：Section 11.3.4, “YEAR(2)Limitations and Migrating to YEAR(4)”


十，主機快取


67.現在提供更多關於客戶端連線失敗的資訊，並提高了對於主機快取的訪問，主機快取包括客戶端IP和主機名，避免了DNS解析。


68.新的狀態值 Connection_errors_xxx 提供了連線失敗的資訊，不適用於特寫客戶端IP。


69.主機快取增加了對特定IP錯誤的計數，以及一個新的host_cache Performance Schema表。可以明確知道有多少主機被快取，每個主機連線失敗的錯誤型別，以及錯誤主機距離max_connect_errors的上限有多遠


70.主機快取大小由引數 host_cache_size 配置。


更多見： Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”, 和 Section 20.9.9.1, “The host_cache Table”


十一，OpenGIS


71.OpenGIS定義函式可以測試兩個地理位置之間的關係。以前是MBR-based函式返回以矩形測量的結果。現在支援更為精準的形狀。這些函式加入ST_前輟，比如Contains() 使用矩形，ST_Contains()使用物件形狀。更多見Section 12.17.5.4.2, “Functions That Test Spatial Relationships Between Geometries”


十二，移除引數


72.移除引數


以下被引數移除，同時會顯示新的引數。


移除–log，換為–general_log 指定是否開啟，–general_log_file=file_name 指定檔名。


移除–log-slow-queries和 log_slow_queries，用–slow_query_log 開啟慢查詢日誌，用–slow_query_log_file=file_name 指定檔名。


移除–one-thread ，使用–thread_handling=no-threads 替換。


移除–safe-mode


移除–skip-thread-priority


移除–table-cache，改為table_open_cache


移除–init-rpl-role 和 –rpl-recovery-rank選項和rpl_recovery_rank 系統引數，以及Rpl_status狀態值。


移除engine_condition_pushdown，使用engine_condition_pushdown標識optimizer_switch 引數。


移除have_csv, have_innodb, have_ndbcluster, 和 have_partitioning，使用show engines替換。


移除sql_big_tables，使用big_tables。


移除sql_low_priority_updates ，使用low_priority_updates 。


移除 sql_max_join_size，使用 max_join_size 。


移除max_long_data_size，使用max_long_data_size。


移除 FLUSH MASTER 和 FLUSH SLAVE命令，使用RESET MASTER 和 RESET SLAVE 替換。


移除SLAVE START 和 SLAVE STOP命令，使用START SLAVE 和 STOP SLAVE替換。


移除SHOW AUTHORS 和 SHOW CONTRIBUTORS命令。


移除set命令的OPTION and ONE_SHOT修改器。


不允許在儲存過程中或者函式引數中或者儲存程式本地變數中使用default來指定（如：SET var_name = DEFAULT命令），但可以在指定系統變數時使用default。