MySQL 8.0新特性

標籤：MySQL 8.0新特性

一、MySQL 8.0中添加的功能

1、新的系統字典表

    整合了儲存有關資料庫物件資訊的交易資料字典，所有的中繼資料都用InnoDB引擎進行儲存

2、支援DDL 原子操作

    InnoDB表的DDL支援事務完整性，要麼成功要麼復原，將DDL操作復原日誌寫入到data dictionary 資料字典表 mysql.innodb_ddl_log 中用於復原操作

3、安全和使用者管理

• 新增caching_sha2_password認證外掛程式，並且是預設的身份認證外掛程式。效能和安全方面加強

• 許可權支援role

• 新增密碼記錄功能，限制重複使用以前的密碼
  

 

4、支援資源管理

    支援建立和管理資源群組，並允許將伺服器啟動並執行線程分配給特定的組，以便線程根據資源群組可用的資源執行

5、innodb 增強

• 自增列最佳化，修複MySQL的bug#199,該bug導致在DB重啟時，MySQL會將表上最大的自增值作為最大值，下次分配是分配max(id)+1,如果是歸檔表或者其它模式刪除資料後，DB系統重啟，自增值可能被重用

• 新增INFORMATION_SCHEMA.INNODB_CACHED_INDEXES，查看每個索引緩衝在InnoDB緩衝池中的索引頁數

• InnoDB暫存資料表都將在共用暫存資料表空間ibtmp1中建立

• 對於SELECT ... FOR SHARE和SELECT ... FOR UPDATE語句，InnoDB支援NOWAIT和SKIP LOCKED

• innodb_undo_tablespaces的最小值為2，並且不再允許將innodb_undo_tablespaces設定為0。 最小值2確保復原段始終在撤消資料表空間中建立，而不是在系統資料表空間中建立

• 支援 ALTER TABLESPACE ... RENAME TO 文法

• 新增innodb_dedicated_server，讓InnoDB根據伺服器上檢測到的記憶體量自動設定innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method

• 新增INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF視圖

• 新增了動態配置項 innodb_deadlock_detect，用來禁用死結檢查，因為在高並發系統中，當大量線程等待同一個鎖時，死結檢查會大大拖慢資料庫

• 支援使用innodb_directories選項在伺服器離線時將資料表空間檔案移動或恢複到新位置
  

  

6、MySQL 8.0更好支援文檔型資料庫和JSON

7、最佳化

• 不可見索引，開始支援invisible index，(感覺又和Oracle一樣了)，在最佳化SQL的過程中可以設定索引為不可見，最佳化器變不會利用不可見索引

• 支援降序索引，可以對索引定義 DESC，之前，索引可以被反序掃描，但影響效能，而降序索引就可以高效的完成

   

8、支援RANK(), LAG()、NTILE()等函數 

9、Regex增強，提供了REGEXP_LIKE()，EGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_SUBSTR()等函數  

10、新增備份鎖，允許線上備份期間的DML，同時防止可能導致快照不一致的操作。 備份鎖由LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE文法支援

 

11、字元集  預設字元集由latin1變為utf8mb4

 

12、設定檔增強

MySQL 8.0版本支援線上修改全域參數持久化，通過加上PERSIST關鍵字，可以將調整持久化到新的設定檔中，再次重啟db還可以應用到最新的參數。對於加上 PERSIST 關鍵字修改參數命令，MySQL系統會產生一個包含json格式資料的 mysqld-auto.cnf 檔案，比如執行：

    set PERSIST  expire_logs_days=10 ; #記憶體和json檔案都修改，重啟還生效

    set GLOBAL  expire_logs_days=10 ; #只修改記憶體，重啟丟失

系統會在資料目錄下產生一個包含如下內容的 mysqld-auto.cnf 的檔案：

{ "mysql_server": {"expire_logs_days": "10" } }

當 my.cnf 和 mysqld-auto.cnf 同時存在時，後者具有高優先順序。

13、長條圖

   MySQL 8.0 版本開始支援期待已久長條圖。最佳化器會利用column_statistics的資料，判斷欄位的值的分布，得到更準確的執行計畫。

   可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON  clo_name] 來收集或者刪除長條圖資訊

   

14、支援會話層級SET_VAR 動態調整部分參數，有利於提升語句效能。

select /*+ SET_VAR(sort_buffer_size = 16M) */ id  from test order id ; 

insert  /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

15、預設參數的調整

調整back_log的預設值，保持和 max_connections一致，增強突發流量帶來的串連處理容量。

修改 event_scheduler 預設為ON，之前預設是關閉的。

調整max_allowed_packet 的預設值，從4M增加到64M。

調整bin_log,log_slave_updates預設值為on。

調整expire_logs_days的到期時間為30天，老版本是7天，生產環境時，檢查該參數，防止binlog過多造成空間緊張。

調整innodb_undo_log_truncate 預設為ON

調整innodb_undo_tablespaces 預設值為2

調整innodb_max_dirty_pages_pct_lwm 預設值10

調整innodb_max_dirty_pages_pct預設值為90

新增innodb_autoinc_lock_mode 預設值為2

16、InnoDB效能提升

廢除buffer pool mutex,將原來一個mutex拆分成多個，提高並發

拆分LOCK_thd_list 和 LOCK_thd_remove 這兩個mutex，大約可提高線程連結效率5%。

17、行緩衝

 MySQL8.0的最佳化器可以估算將要讀取的行數，因此可以提供給儲存引擎一個合適大小的row buffer來儲存需要的資料。大批量的連續資料掃描的效能將受益於更大的record buffer

 

18、改進掃描效能

改進InnoDB範圍查詢的效能，可提升全表查詢和範圍查詢 5-20%的效能。

19、成本模型

InnoDB緩衝區可以估算緩衝區中的有多少表和索引，這可以讓最佳化器選擇訪問方式時知道資料是否可以儲存在記憶體中還是必須儲存到磁碟上。

20、重構SQL分析器

改進SQL分析器。舊的分析器由於其文法複雜性和自頂向下的分析方式從而有嚴重的限制，導致難以維護和擴充。

二、MySQL8.0中被廢棄的特性

• 廢棄validate_password 外掛程式

• 廢棄ALTER TABLESPACE和DROP TABLESPACE ENGINE子句

• 廢棄JSON_MERGE()  -> JSON_MERGE_PRESERVE()取代

• 廢棄 have_query_cache 系統變數 
  

 

   

 

 

三、MySQL8.0被移除的功能

• query cache 功能被移除，相關的系統變數也被移除

• mysql_install_db  被 mysqld --initialize or --initialize-insecure 替代

• INFORMATION_SCHEMA下的INNODB_LOCKS和INNODB_LOCK_WAITS表已被刪除。 用Performance Schema data_locks和data_lock_waits表替代
  

• INFORMATION_SCHEMA下的四張表移除：GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, SESSION_STATUS
  

• InnoDB不再支援壓縮的暫存資料表。
  

• 不再支援 PROCEDURE ANALYSE()文法
  

Renamed InnoDB Information Schema Views

Old Name                                      　　New Name

INNODB_SYS_COLUMNS            　　INNODB_COLUMNS

INNODB_SYS_DATAFILES            　　INNODB_DATAFILES

INNODB_SYS_FIELDS                   　　INNODB_FIELDS

INNODB_SYS_FOREIGN                    　INNODB_FOREIGN

INNODB_SYS_FOREIGN_COLS    　　INNODB_FOREIGN_COLS

INNODB_SYS_INDEXES                    　INNODB_INDEXES

INNODB_SYS_TABLES                    　INNODB_TABLES

INNODB_SYS_TABLESPACES            INNODB_TABLESPACES

INNODB_SYS_TABLESTATS             　INNODB_TABLESTATS

INNODB_SYS_VIRTUAL                     INNODB_VIRTUAL

remove的server選項：

    --temp-pool 

    --ignore-builtin-innodb 

    --des-key-file 

    --log-warnings

    --ignore-db-dir    

 

remove的配置選項：

innodb_file_format

innodb_file_format_check

innodb_file_format_max

innodb_large_prefix

remove的系統變數

 information_schema_stats  ->information_schema_stats_expiry

ignore_builtin_innodb 

innodb_support_xa  

show_compatibility_56 

have_crypt 

date_format

datetime_format

time_format

max_tmp_tables

global.sql_log_bin(session.sql_log_bin保留）

log_warnings ->log_error_verbosity

multi_range_count

secure_auth

sync_frm

tx_isolation -> transaction_isolation

tx_read_only -> transaction_read_only

ignore_db_dirs

query_cache_limit

query_cache_min_res_unit

query_cache_size

query_cache_type

query_cache_wlock_invalidate

innodb_undo_logs -->innodb_rollback_segments

remove的狀態變數

Com_alter_db_upgrade 

Slave_heartbeat_period

Slave_last_heartbeat

Slave_received_heartbeats

Slave_retried_transactions, Slave_running

Qcache_free_blocks

Qcache_free_memory

Qcache_hits

Qcache_inserts

Qcache_lowmem_prunes

Qcache_not_cached

Qcache_queries_in_cache

Qcache_total_blocks

Innodb_available_undo_logs status 

remove的函數

JSON_APPEND() --> JSON_ARRAY_APPEND() 

ENCODE()

DECODE()

DES_ENCRYPT() 

DES_DECRYPT() 

remove的client選項：

--ssl  --ssl-verify-server-cert 被刪除，用--ssl-mode=VERIFY_IDENTITY |REQUIRED |DISABLED 替代 

--secure-auth

MySQL 8.0新特性