MySQL · 特性分析 · MySQL 5.7新特性系列一

1. 背景

MySQL 5.7在2015-10-21釋出了GA版本，即5.7.9，目前小版本已經到了5.7.12。5.7新增了許多新的feature和最佳化，接下來一個系列，我們就一起來嚐嚐鮮。首先這次主要是預覽feature的變化以及相容性問題。後面的系列，會針對重要的feature展開來學習。

2 安全相關的特性

2.1 認證外掛

mysql.user表中的plugin更改成not null，5.7開始不再支援mysql_old_password的認證外掛，推薦全部使用mysql_native_password。從低版本升級到5.7的時候，需要處理兩個相容性問題。

[相容性]
需要先遷移mysql_old_password的使用者，然後進行user表結構的升級：

1. 遷移mysql_old_password使用者
MySQL 5.7.2之前的版本，是根據password的hash value來判斷使用的認證外掛型別，5.7.2以後的版本，plugin欄位為not null，就直接根據plugin來判斷了。新的密碼從password欄位中，儲存到新的欄位authentication_string中，password欄位廢棄處理。

如果user是隱式的mysql_native_password。直接使用sql進行變更：

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41); FLUSH PRIVILEGES; 

如果user是隱式的或者顯示的mysql_old_password， 首先透過以下sql進行查詢：

SELECT User, Host, Password FROM mysql.user WHERE (plugin = '' AND LENGTH(Password) = 16) OR plugin = 'mysql_old_password'; 

如果存在記錄，就表示還有使用mysql_old_password的user，使用以下sql進行使用者的遷移：

ALTER USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'DBA-chosen-password'; 

2. user表結構升級
透過mysql_upgrade直接進行升級，步驟如下[5.6->5.7]：

1. stop MySQL 5.6例項
2. 替換5.7的mysqld二進位制版本
3. 使用5.7啟動例項
4. run mysql_upgrade升級系統表
5. 重啟MySQL 5.7例項

2.2 密碼過期

使用者可以透過 ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;這樣的語句來使使用者的密碼過期。
並新增加 default_password_lifetime來表示使用者密碼自動過期時間，從5.7.10開始，其預設值從0變更到了360，也就是預設一年過期。
可以透過以下兩種方法禁止過期：

1. SET GLOBAL default_password_lifetime = 0; 2. ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; 

[相容性]
只需要透過mysql_upgrade升級mysql.user系統表就可以使用密碼過期新功能。

2.3 賬號鎖定

使用者可以透過以下語法進行賬號鎖定，阻止這個使用者進行登入：

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK; ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK; 

[相容性]
只需要透過mysql_upgrade升級mysql.user系統表就可以使用密碼過期新功能。

2.4 SSL連線

如果mysqld編譯使用的openssl，在啟動的時候，預設建立SSL， RSA certificate 和 key 檔案。
但不管是openssl還是yassl，如果沒有設定ssl相關的引數，mysqld都會在data directory裡查詢ssl認證檔案，來儘量開啟ssl特性。

[相容性]
不存在相容性的問題

2.5 安裝資料庫

5.7開始建議使用者使用 mysqld --initialize來初始化資料庫，放棄之前的mysql_install_db的方式，新的方式只建立了一個root@localhost的使用者，隨機密碼儲存在~/.mysql_secret檔案中，並且賬號是expired，第一次使用必須reset password，並且不再建立test db。

[相容性]
不存在相容性的問題

3 sql mode變更

5.7 sql_mode的預設值變更為：

mode_no_engine_substitution |
      mode_only_full_group_by |
      mode_strict_trans_tables |
      mode_no_zero_in_date |
      mode_no_zero_date |
      mode_error_for_division_by_zero |
      mode_no_auto_create_user 

而在5.7之前，sql_mode的預設值都只有mode_no_engine_substitution。
所以在5.7預設的情況下，比如grant不存在的使用者的時候，會報一下錯誤：

ERROR 1133 (42000): Can't find any matching row in the user table 

必須先使用create user，然後再使用grant user。

[相容性]
預設sql mode發生變更會導致sql的行為不一致。

4. online alter table

支援online rename index操作， in_place並且不需要table copy。
[相容性]
不存在相容性的問題

5. InnoDB增強

5.1 varchar長度變更支援inplace

變更varchar 型別欄位的長度支援inplace方法，但有一個限制，即用於表示varchar欄位長度的位元組數不能發生變化，也就是支援比如varchar的長度在255以下變更或者255以上的範圍進行變更，因為從小於255變更到大於255，其size的位元組需要從1個增加到2個。

注意：減少varchar的長度，仍然需要table copy。

5.2 最佳化InnoDB臨時表

因為InnoDB臨時表的資料不再不受redo保護，而redo只保護臨時表的後設資料，所以大幅提升了臨時表的效能。
並且InnoDB臨時表的後設資料儲存在一個新的系統表中即innodb_temp_table_info，
臨時表將建立一個統一的表空間，我們稱之為臨時表空間，其目錄地址可以透過引數innodb_temp_data_file_path來設定。系統在啟動的時候，都會新建這個表空間，重啟會刪除重建。

例如：

mysql> show global variables like '%temp_data_file_path%'; +----------------------------+-----------------------+ | Variable_name              | Value                 | +----------------------------+-----------------------+ | innodb_temp_data_file_path | ibtmp1:12M:autoextend | +----------------------------+-----------------------+ 

並且5.7儲存引擎預設都變更成InnoDB了：

mysql> show global variables like '%storage_engine%'; +----------------------------------+--------+ | Variable_name                    | Value  | +----------------------------------+--------+ | default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 

注意： 在開啟gtid的情況下，非auto commit或者顯示begin的context下，create 或者drop 臨時表，仍然和5.6一樣：

ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. 

另外， insert into t select * from t也會遇到錯誤，不能在一個sql語句中reference兩次臨時表。

備註： 因為InnoDB臨時表進行了比較大的變動，我們會專門進行一次詳細的介紹。

5.3 InnoDB原生支援DATA_GEOMETRY型別

並且支援在spatial data types上建立index，加速查詢。

5.4 buffer pool dump

buffer pool dump和load支援一個新的引數innodb_buffer_pool_dump_pct，即dump的比例，並且使用innodb_io_capacity 來控制load過程中的IO吞吐量。

5.5 多執行緒flush dirty

從5.7.4開始，innodb_page_cleaners引數可以設定，支援多執行緒flush dirty page，加快髒塊的重新整理。

5.6 NVM file system

MySQL 一直使用double write buffer來解決一個page寫入的partial write問題，但在linux系統上的Fusion-io Non-Volatile Memory (NVM) file system支援原子的寫入。
這樣就可以省略掉double write buffer的使用， 5.7.4以後，如果Fusion-io devices支援atomic write，那麼MySQL自動把dirty block直接寫入到資料檔案了。這樣減少了一次記憶體copy和IO操作。

5.7 InnoDB分割槽表

MySQL 5.7之前的版本，InnoDB並不支援分割槽表，分割槽表的支援是在ha_partition引擎上支援的，從5.7開始，InnoDB支援原生的分割槽表，並且可以使用傳輸表空間。

[相容性]
mysql_upgrade會掃描ha_partition引擎支援的InnoDB表，並升級成InnoDB分割槽表，5.7.9之後，可以透過命令ALTER TABLE … UPGRADE PARTITIONING.進行升級。如果之前的版本大量使用了分割槽表，要注意使用mysql_upgrade會消耗非常長的時間來升級分割槽表。

5.8 動態調整buffer pool size

MySQL 5.7.5之後，可以online動態調整buffer pool size，透過設定動態的引數innodb_buffer_pool_size來調整，並且根據Innodb_buffer_pool_resize_status狀態來檢視resize的進度，因為resize的過程是以chunk為大小，把pages從一個記憶體區域copy到另一片記憶體的。

5.9 加快recovery

MySQL 5.7.5之前，在recovery的過程中，需要掃描所有的ibd檔案，獲取元資訊， 5.7.5之後，新加了一種redo log型別，即MLOG_FILE_NAME， 記錄從上一次checkpoint以來，發生過變更的檔案，這樣在recovery的過程中，只需要開啟這些檔案就可以了。
[相容性]
因為增加了新的log record type，需要安全的關閉5.7之前的例項，清理掉redo。

5.10 表空間管理

支援建立表空間，例如

CREATE TABLESPACE `tablespace_name` ADD DATAFILE 'file_name.ibd' [FILE_BLOCK_SIZE = n] 

並可以在建立表的時候，指定屬於哪個表空間，

[相容性]
因為可以任意指定空間目錄，要注意升級過程中，不要漏掉目錄。

5.11 InnoDB Tablespace Encryption

支援InnoDB資料檔案加密，其依賴keyring plugin來進行秘鑰的管理，後面我們單獨來介紹InnoDB加密的方法，並且RDS也實現了一種InnoDB資料檔案透明加密方法，並透過KMS系統來管理秘鑰。例如：

create table t(id int) encryption='y'; 

轉自：