一文看懂MYSQL8 vs MYSQL5.7增加了哪些特性
概述
最近部分項目要上Mysql8.0 ,故從官網整理一下mysql8相比mysql5.7增加的一些特性，僅供參考。
一、新的系統字典表
整合了存儲有關數據庫對象信息的事務數據字典，所有的元數據都用InnoDB引擎進行存儲
二、安全和用戶管理
- 新增caching_sha2_password認證插件，并且是默認的身份認證插件。性能和安全方面加強
- 權限支持role
- 新增密碼歷史記錄功能，限制重復使用以前的密碼
三、 innodb 增強
- 新增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 語法
- 新增INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF視圖
- 新增了動態配置項 innodb_deadlock_detect，用來禁用死鎖檢查，因為在高并發系統中，當大量線程等待同一個鎖時，死鎖檢查會大大拖慢數據庫
- 支持使用innodb_directories選項在服務器脫機時將表空間文件移動或恢復到新位置
- 新增innodb_dedicated_server，讓InnoDB根據服務器上檢測到的內存量自動配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method。當innodb_dedicated_server啟用時，InnoDB根據服務器上檢測到的內存量自動配置以下選項：
- innodb_dedicated_server：自動配置緩沖池大小
四、MySQL 8.0更好支持文檔型數據庫和JSON
不可見索引，開始支持invisible index，在優化SQL的過程中可以設置索引為不可見，優化器不會利用不可見索引
支持降序索引，可以對索引定義 DESC，之前，索引可以被反序掃描，但影響性能，而降序索引就可以高效的完成
支持RANK(), LAG()、NTILE()等函數
正則表達式增強，提供了REGEXP_LIKE()，EGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_SUBSTR()等函數
新增備份鎖，允許在線備份期間的DML，同時防止可能導致快照不一致的操作。 備份鎖由LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE語法支持
默認字符集由latin1變為utf8mb4
五、配置文件增強
MySQL 8.0版本支持在線修改全局參數持久化，通過加上PERSIST關鍵字，可以將調整持久化到新的配置文件中，再次重啟db還可以應用到最新的參數。對于加上 PERSIST 關鍵字修改參數命令，MySQL系統會生成一個包含json格式數據的 mysqld-auto.cnf 文件，比如執行：
set PERSIST binlog_expire_logs_seconds = 604800 ; #內存和json文件都修改，重啟還生效
set GLOBAL binlog_expire_logs_seconds = 604800 ; #只修改內存，重啟丟失
系統會在數據目錄下生成一個包含如下內容的 mysqld-auto.cnf 的文件：
{ "mysql_server": {" binlog_expire_logs_seconds ": "604800" } }
當 my.cnf 和 mysqld-auto.cnf 同時存在時，后者具有高優先級。
六、直方圖
MySQL 8.0 版本開始支持期待已久直方圖。優化器會利用column_statistics的數據，判斷字段的值的分布，得到更準確的執行計劃。
可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 來收集或者刪除直方圖信息
支持會話級別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);
七、InnoDB性能提升
廢除buffer pool mutex, 將原來一個mutex拆分成多個，提高并發拆分LOCK_thd_list 和 LOCK_thd_remove 這兩個mutex，大約可提高線程鏈接效率5%。
1、行緩存
MySQL8.0的優化器可以估算將要讀取的行數，因此可以提供給存儲引擎一個合適大小的row buffer來存儲需要的數據。大批量的連續數據掃描的性能將受益于更大的record buffer。
2、改進掃描性能
改進InnoDB范圍查詢的性能，可提升全表查詢和范圍查詢 5-20%的性能。
3、成本模型
InnoDB緩沖區可以估算緩存區中的有多少表和索引，這可以讓優化器選擇訪問方式時知道數據是否可以存儲在內存中還是必須存儲到磁盤上。
