MySQL5.6中新增特性、不推薦使用的功能以及廢棄的功能

雖然已經使用MySQL5.6版本有一段時間了，但由於沒有和之前的版本作過詳細比較，所以對於哪些重要的或者不太重要的特性是在新版本中引入的，還有哪些特性已經或者將要從舊版本中移除的並沒有一個十分全面的瞭解。最近有一個將某資料庫例項由5.5升級為5.6的需求，所以對於MySQL5.6和5.5版本之間的差異做了一個簡單梳理。

新增
     Security improvements.

          可使用mysql_config_editor將登陸認證資訊以加密方式儲存在.mylogin.cnf檔案中，客戶端可從該檔案讀取以避免以明文方式使用登入資訊。

          透過內建sha256_password認證外掛支援更強的賬戶密碼加密。

          mysql.user表新增password_expired用以標識密碼是否過期。可透過alter user語句設定密碼過期。

          透過 validate_password 外掛實現密碼安全度檢查。

          對於使用MySQL4.1之前版本的Hash方法加密的賬戶密碼mysql_upgrade 會給出警告。

          在Unix平臺上mysql_install_db支援--random-passwords，可為初始root使用者生成強度更高的密碼並設定密碼過期、移除匿名使用者。

          各類日誌（慢查詢、二進位制日誌、通用日誌）包括mysql客戶端命令歷史記錄中不再會出現明文密碼內容。

          START SLVAE語法改進可在該語句中指定連線MASTER的引數，可避免將密碼之類的資訊儲存在master.info檔案

     Changes to server defaults.
          提供更好的效能並減少管理員對設定的手工修改

     InnoDB enhancements.
          開始支援全文索引。
          Online DDL（即一些ALTER TABLE操作無需複製表、不阻塞增刪改）。
          CREATE TABLE語句支援DATA DIRECTORY='directory' 可將file-per-table 下每個表對應的.ibd檔案存放在資料目錄以外的其他目錄，提升了靈活性繼而可提升效能，比如將.ibd放在單獨的SSD盤。
          支援transportable tablespaces、允許單表空間(每個表的.ibd檔案)動態匯入匯出。 FLUSH TABLE的FOR EXPORT從句可將buffer中未儲存的變化刷到.ibd檔案。之後可將.ibd檔案複製和後設資料檔案複製至其他伺服器例項(如SSD、HDD）。ALTER TABLE的DISCARD TABLESPACE和IMPORT TABLESPACE從句可將資料載入不同的MySQL例項。
          可透過innodb_page_size設定InnoDB頁大小（預設16KB可設定為8KB、4KB）以適應特定的負載和硬體來降低冗餘、提升系能。
          Buffer Pool重新整理演算法得以改進以增強效能和併發性，可透過一系列引數進行調整。
          與memcached整合可使用NoSQL-style API來訪問資料避免解析和構建查詢計劃的開銷（適用於簡單查詢、更新等）。
          最佳化用的統計資訊更加精確且可在服務重啟後保持並可控制取樣率。
          最佳化只讀事務對於ad-hoc queries和report-generating 應用提升了效能和併發。
          可將InnoDB undo log 從系統表空間移至一個或多個獨立表空間（如移動到SSD盤）。
          可透過 innodb_checksum_algorithm=crc32指定使用更快的checksum演算法。
          redo log檔案總大小可達到512G（之前為4G），透過innodb_log_file_size指定單個檔案大小，innodb_log_files_in_group指定檔案數量、innodb_log_group_home_dir指定存放路徑。
          可將MySQL中InnoDB表配置為只讀模式（透過--innodb-read-only），這樣可在只讀媒介如DVD、CD上訪問InnoDB表或者搭建多例項共享資料目錄的資料倉儲。
          可透過innodb_compression_level指定InnoDB compressed表的壓縮級別並透過innodb_log_compressed_pages指定是否將buffer pool中compressed pages由於更新操作引起的re-compressed pages儲存在redo log中。
          可透過innodb_compression_failure_threshold_pct, innodb_compression_pad_pct_max動態調整InnoDB壓縮表中的填充塊（用以避免DML時的重新壓縮，但數量過多時會增加壓縮失敗的機率）的比例，以降低壓縮失敗率而無需透過新的引數重建表或者以不同的頁大小重建整個例項。
          INFORMATION_SCHEMA中新增InnoDB相關係統表，可查詢InnoDB buffer pool、表後設資料、索引、外來鍵、底層效能相關的資料等。
          新的InnoDB可以使用LRU演算法釋放開啟的表所佔用的記憶體以緩解有大量表的伺服器上的記憶體負載。（可透過 table_definition_cache 調整開啟的InnoDB表可使用的記憶體量）
          內部效能最佳化，如透過拆分kernel mutex減少爭用、將flush操作由主執行緒移至獨立執行緒、使用多個重新整理執行緒、較少大記憶體系統上的buffer pool的爭用。
          更快的死鎖檢測演算法，InnoDB相關的死鎖資訊會全部寫入錯誤日誌。
          可在伺服器重啟後立即過載頁至InnoDB buffer pool以避免重啟後過長的“熱身”時間，尤其是InnoDB buffer pool較大的例項。可在伺服器關閉時dump出buffer pool中的資料下次重啟後載入、也可以在執行過程中匯入匯出。
          5.6.16後innochecksum可支援2G以上的檔案
          可透過innodb_status_output和innodb_status_output_locks,分別動態開閉InnoDB Monitor和InnoDB Lock Monitor（分別會在error log中列印出InnoDB相關資訊，後一個引數的開啟要以前一個引數的開啟為前提，以後後一個引數的結果是前一個結果的子集）。
          5.6.17後可以使用online DDL（ALGORITHM=INPLACE）透過如下操作OPTIMIZE TABLE、ALTER TABLE ... FORCE、ALTER TABLE ... ENGINE=INNODB 來重建表，以減少重建時間並允許併發的DML。

     Partitioning.
          分割槽最大量可達8192（包含子分割槽的量）
          可使用ALTER TABLE ... EXCHANGE PARTITION與非分割槽表交換分割槽表的一個分割槽或子分割槽（前提是這兩個表除了分割槽之外表結構一致、且非分割槽的表不為臨時表且無外來鍵引用或被引用的外來鍵非分割槽表中不包含要交換的分割槽邊界以外的值）
          可以限定在一個或者多個分割槽或子分割槽內查詢。支援的語句包括SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA、LOAD XML。
          Partition lock pruning透過消除分割槽上的鎖，極大提升了多分割槽表上DML、DDL操作的效能。


     Performance Schema.
               更加豐富的資訊。表輸入輸出，表事件過濾，執行緒事件過濾，表、索引I/O及表鎖摘要資訊，語句及語句的階段資訊等。相關的兩個引數為 --gtid-mode和--enforce-gtid-consistency。若使用GTIDs在開始一個新的slave或者指向一個新的master時就不必指定日誌檔案和檔案中的位置，過程可大大簡化。另外，因基於GTID的複製時完全基於事務的，因而很容易檢查主從一致性，只要master上提交的事務在slave上也提交過就可以保證一致。

     Replication and logging.

          開始支援使用GTIDs( global transaction identifiers)的基於事務的複製，使識別和追蹤在master上提交併在slave上應用的每個事務成為可能。
         
          可透過binlog_row_image控制 row-based複製中的row image所包含的列，使其可以包含所有列或者不包含BLOB和TEXT列或者僅包含必要的用於唯一標識並執行每一行上的變化的個別列，以便於節省磁碟開銷、網路開銷和記憶體使用。
         
          MySQL Server 對二進位制日誌的讀寫是 crash-safe的，因只記錄和讀取完整的事務/事件。預設，會記錄事件本身和事件的長度，然後利用這些資訊驗證事件是否被正確書寫。也可以透過binlog_checksum 變數使server使用CRC32演算法為事件寫校驗和，並透過 master_verify_checksum使server從日誌檔案中讀取校驗和， --slave-sql-verify-checksum 可以使slave的SQL thread從中繼日誌中讀取校驗和。

          支援選擇將master的連線資訊以及slave的中繼日誌資訊儲存在檔案或者表中，相關引數為--master-info-repository和--relay-log-info-repository。但為了保證複製的crash-safe，當選擇將master的連線資訊以及slave的中繼日誌資訊儲存在表中時相關的表必須是事務性引擎如InnoDB（5.6.6之後的版本會自動建立為InnoDB表但之前的版本會建立MyISAM表，因此在複製開始之前一定要修改為InnoDB引擎）

          mysqlbinlog工具可以從指定的主機以原始的二進位制格式備份二進位制日誌檔案。工具相關的命令列引數為 --read-from-remote-server和--raw

          支援延遲複製，也即，將slave延後於master一段特定的時間。改特性可以用於測試延遲帶來的影響或者挽救 DBA在master上不小心犯的錯誤（透過CHANGE MASTER TO中的MASTER_DELAY指定）。

          對於有多塊網路介面的salve機器可指定只使用其中某一個（透過CHANGE MASTER TO 語句中的 MASTER_BIND指定）

          新增log_bin_basename引數用於指定二進位制日誌檔案完整的路徑和檔名

          支援slave上多個執行緒並行執行master上傳遞下來的事務。透過 slave_parallel_workers指定slave上併發執行事務的worker數量。這種併發是基於資料庫的，也即資料庫內的操作是有先後順序的，主從上需以一致的順序執行，但不同資料庫之間則不需要這種順序，也即一個worker可不必等待其他worker在更新完某個庫之後再更新某庫。

              
     Optimizer enhancements.
          最佳化了SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;查詢效能
          實現了Disk-Sweep Multi-Range Read.有效減少了在大表且沒有快取在快取中的情況下在輔助索引上範圍掃描造成的隨機磁碟訪問。採用的辦法是先掃描找出相關行關聯的key，然後依據主鍵將key排序，最終依據排好序的key回表取出資料。
          實現了ICP(Index Comndition Pushdowndown)。若沒有ICP，存數引擎層根據索引定位表中的行並返回給Server層然後在這裡用WHERE從句中的條件對結果進行過濾。若使用ICP，如果WHERE條件中的部分列可由索引中的欄位進行評估那麼server層會將WHERE條件中的這一部分下推到儲存引擎層。引擎層進行評估後只取出滿足要求的行。這樣可有效減少引擎訪問表的次數和server訪問引擎的次數。
          EXPLAIN支援 DELETE, INSERT, REPLACE和UPDATE，以前只支援SELECT，且可以以JSON格式輸出。
          處理FROM從句中的子查詢（派生表）更加高效。首先FROM後的子查詢的物化過程直到查詢執行時需要這一部分的內容時才進行，另外查詢可能會在派生表上加索引以加快查詢。
          使用semi-join和物化策略最佳化子查詢。
          使用BKA(Batched Key Access)join演算法提升join效能。支援inner join、outer join、semi-join、nested outer joins和nested semi-joins。    
          新增tracing功能主要供開發者使用。透過一些列 optimizer_trace_xxx 變數和 INFORMATION_SCHEMA.OPTIMIZER_TRACE來提供

     Condition handling.
          可透過GET DIAGNOSTICS語句獲取各種診斷資訊

     Data types.
          對於TIME、DATETIME、TIMESTAMP型別的值支援小數的秒，最多可支援毫秒級別（也即6位的精度）。
          之前的版本中每個表至多隻有一個TIMESTAMP列可以初始化為當天日期時間(DEFAULT CURRENT_TIMESTAMP)或者更新至當天日期時間( ON UPDATE CURRENT_TIMESTAMP)。新版本的中取消了此限制.另外，之前只有TIMESTAMP列可以使用DEFAULT CURRENT_TIMESTAMP和 ON UPDATE CURRENT_TIMESTAMP，現在datetime列也可以。
          之前版本中TIMESTAMP列定義中什麼都不指定的話自動的預設為DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。現在可以透過 explicit_defaults_for_timestamp 變數關閉這一行為。

     Host cache.
          提供了更多有關導致連線錯誤的原因的資訊以及對host cache訪問的改進，其中包含客戶端IP和主機名可用以避免DNS查詢。具體實現如下：
          增加了一系列 Connection_errors_xxx狀態變數，提供有關連線錯誤的資訊，這些資訊是全域性的表示所有連線的各類錯誤資訊的聚合，不是針對於某個特定IP地址。
          performance_schema庫新增host_cache表且有更多的關於錯誤資訊的計數字段來track各類錯誤（非針對特定IP的錯誤），這樣一來便可透過查詢該表得知有多少主機被快取、哪些主機發生了哪類錯誤、連線錯誤量有多接近max_connect_errors限制。
          可透過 host_cache_size調整host cache大小


     OpenGIS.
          原生實現了OPENGIS規範定義的測量兩個幾何值之間關係的一系列函式。

降級
     ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE和NO_ZERO_IN_DATE 幾個sql mode 不贊成使用，MySQL5.7中這些值不會有任何影響，效果會被包含在strict SQL mode中((STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES)。MySQL升級時要注意這些問題。確保新版本中的SQL MODE不會影響應用。

     GROUP BY中的隱式排序不贊成使用，這屬於有MySQL的一個擴充套件，可能在將來版本中出於效能考慮被移除。所以最好使用ORDER BY語句明確指明排序。

     不推薦使用4.1之前版本的密碼和mysql_old_password認證外掛，不安全。為防止使用舊版本密碼雜湊可將secure_auth 開啟。對於DBA 建議將使用mysql_old_password 認證外掛的賬戶轉為使用 mysql_native_password認證外掛。

     不建議使用OLD_PASSWORD()和old_passwords=1來生成4.1版本之前的密碼。

     不建議使用 --skip-innodb或者--innodb=OFF, --disable-innodb；date_format, datetime_format 和 time_format； have_profiling, profiling 和 profiling_history_size ； innodb_use_sys_malloc 和innodb_additional_mem_pool_size； timed_mutexes 變數。這些都可能在將來的版本中移除。

     使用 --lc-messages-dir 和 --lc-messages 代替--language（用於指明錯誤資訊使用的語言）。

     不建議使用 ALTER IGNORE TABLE。

     不建議使用 msql2mysql, mysql_convert_table_format, mysql_find_rows, mysql_fix_extensions, mysql_setpermission, mysql_waitpid, mysql_zap, mysqlaccess 和 mysqlbug這些工具。

     不建議使用mysqlhotcopy工具。


移除
     --log選項，使用 --general_log 和 --general_log_file=file_name 代替。
     --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_oopen_cache變數代替。
     移除--init-rpl-role、--rpl-recovery-rank options選項, 和rpl_recovery_rank、Rpl_status狀態變數。
     取消engine_condition_pushdown 變數，改為optimizer_switch變數中的一個flag  engine_condition_pushdown。
     取消have_csv, have_innodb, have_ndbcluster和have_partitioning變數。使用SHOW PLUGIN或者查詢INFORMATION——SCHEMA中的PLUGINS表。
     sql_big_tables變數替換為big_tables.
     sql_low_priority_updates變數替換為low_priority_updates.
     sql_max_join_size變數替換為max_join_size.
     max_log_data_size變數替換為max_allowed_packet。
     FLUSH MASTER和FLUSH SLAVE語句替換為 RESET MASTER和 RESET SLAVE.
     SLAVE START和SLAVE STOP替換為START SLAVE和STOP SLAVE。
     取消SHOW AUTHORS和SHOW CONTRIBUTORS.
     取消SET 命令中的OPTION和ONE_SHOT修飾符。
     不允許加在儲存過程、儲存函式中將引數或者本地變數設定為DEFAULT( SET var_name = DEFAULT ).但可以將系統變數設定為DEFAULT。
     移除 SHOW ENGINE INNODB MUTEX。

        

轉載：http://blog.csdn.net/zyz511919766/article/details/49865091