###一、新特性
公眾號 MySQL學習

Mysql和Innodb增強：

1、undo可以新增表空間
    mysql> CREATE UNDO TABLESPACE undo_3 ADD DATAFILE 'file_name.ibu';
    Query OK, 0 rows affected (0.11 sec)
    ALTER UNDO TABLESPACE innodb_undo_001 set INACTIVE; 可以處於非啟用狀態
    INFORMATION_SCHEMA.INNODB_TABLESPACES where name like '%undo%' 的state欄位
    會說明是否處於啟用狀態
    可以刪除undo
    DROP UNDO TABLESPACE tablespace_name;
    但是預設會建立的2個undo tablespace不能刪除(innodb_undo_tablespaces)引數控制
    且innodb_undo_log_truncate變數預設開啟
2、innodb_rollback_segments為每一個undo tablespac定義segments 以前是總的
3、innodb_autoinc_lock_mode當前版本設定為2
4、innodb_dedicated_server引數可以自動分配
   innodb_buffer_pool_size
   innodb_log_file_size
   innodb_flush_method
5、innodb_max_dirty_pages_pct_lwm引數預設設定為10%,之前為0%
6、innodb_max_dirty_pages_pct引數預設設定為90%,之前為75%
7、INNODB_TABLESPACES_BRIEF字典表用來提供表空間名，目錄，型別等
8、Serialized dictionary information(SDI)用於儲存一份字典資訊到innodb檔案中
9、innodb支援原子性DDL
10、innodb_log_buffer_size變為可以動態修改
11、8.0.12支援快速加欄位ALGORITHM=INSTANT
12、8.0.13臨時表空間自動回收
13、資料字典採用innodb支援事務，支援原子性DDL
14、8.0.16後mysqld自動升級
15、新的caching_sha2_password密碼外掛可用，如果想使用老的則使用mysql_native_password外掛,這需要更改引數
    default_authentication_plugin，或者建立使用者使用 create user test4 IDENTIFIED WITH mysql_native_password by 'fsdgxcxcbxcb';
16、角色支援
17、MySQL當前儲存了密碼歷史資訊，可以限制再次使用老的密碼
18、8.0.11新的set persist 用於global級別和持久化配置檔案mysqld-auto.cnf,persisted_globals_load受本引數影響
    如果只想修改mysqld-auto.cnf可以使用set persist_only語句，RESET PERSIST 用於清空，可以RESET PERSIST var
    清空某一個系統變數
19、新的命令restart，用於重啟mysqld服務
20、Innodb持久化自增值
21、innodb_deadlock_detect引數增加用於判斷是否進行死鎖檢測
22、innodb_undo_log_truncate預設開啟
23、innodb_temp_tablespaces_dir用於儲存使用者臨時表和內部臨時表 叫做Session Temporary Tablespaces，
    預設會建立10個.ibt檔案，回話退出後會釋放。INNODB_SESSION_TEMP_TABLESPACES用於記錄臨時表空間資訊。
    老的innodb_temp_data_file_path只用於儲存臨時回滾段，叫做Global Temporary Tablespace，另外
    INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 可以檢視建立的臨時表資訊（測試8.0是否可以看到內部臨時表）
24、內部臨時表使用了新的記憶體引擎TempTable代替掉了memory引擎。預設由引數internal_tmp_mem_storage_engine
    控制。引數temptable_max_ram控制其最大使用記憶體（預設1G），當超過記憶體大小將會使用物理臨時表，引數
    temptable_use_mmap用於控制是使用mmap方式還是innodb引擎作為物理臨時表。
    
25、新的檢視INFORMATION_SCHEMA.INNODB_CACHED_INDEXES 用於檢視索引的快取情況
26、新增innodb_parallel_read_threads引數用於check table並行訪問cluster效能，但是用處不大，8.0.17(count(*)?)
27、新增innodb_idle_flush_pct引數預設100，用於控制空閒重新整理髒頁。
28、直方圖統計資訊 histogram_generation_max_mem_size引數控制直方圖最大的記憶體使用(預設20000000)，
mysql> SET histogram_generation_max_mem_size = 2000000;
mysql> USE employees;
mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
Table: employees.employees
Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.
mysql> SELECT HISTOGRAM->>'$."sampling-rate"'
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = "employees"
AND COLUMN_NAME = "birth_date";
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 0.0491431208869665 |
+---------------------------------+
29、8.0.20 精細化 double wirte控制
innodb_doublewrite_dir
Defines the doublewrite buffer file directory.
? innodb_doublewrite_files
Defines the number of doublewrite files.
? innodb_doublewrite_pages
Defines the maximum number of doublewrite pages per thread for a batch write.
? innodb_doublewrite_batch_size
Defines the number of doublewrite pages to write in a batch.

30、8.0.21 lock_sys->mutex全域性鎖進行細化。
31、8.0.21 redo可以臨時關閉，如果在load 資料的時候
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF |
+-------------------------+-------+
 Run the data load operation.
mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON |
+-------------------------+-------+
32、8.0.21 innodb_validate_tablespace_paths 引數用於關閉啟動的時候檢查Innodb檔案，加快啟動速度
33、8.0.21 原子化DDL 實現，create table  select
34、字符集預設變為uft8mb4

最佳化器增強：
35、不可見索引增加，在檢視STATISTICS中進行顯示
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';

受到 引數optimizer_switch選項 use_invisible_indexes的影響，預設為off
最佳化器會忽略不可見索引，聚集索引不能設定為不可見

mysql> CREATE TABLE t2 (
    -> i INT NOT NULL,
    -> j INT NOT NULL,
    -> UNIQUE j_idx (j)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible
不可見索引不影響索引的維護。

36、倒順索引支援
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);

37、函式索引支援

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `i` int(11) NOT NULL,
  `j` int(11) NOT NULL,
  UNIQUE KEY `j_idx` (`j`),
  KEY `ASCII` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

38、去掉不必要的where 8.0.14
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1
->
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2
->
SELECT * FROM t1 LEFT JOIN t2 WHERE condition_1 AND condition_2

39、8.0.16 in 子查詢semijoin 支援exists
40、8.0.17 not in和not exists支援antijoin
41、8.0.21 in 在 update和delete中可以使用semijoin最佳化了
mysql> explain format=tree update tu1 set name='lll' where id in (select id from tu2) \G
*************************** 1. row ***************************
EXPLAIN: -> Update tu1 (buffered)
    -> Nested loop inner join
        -> Remove duplicates from input sorted on id
            -> Filter: (tu2.id is not null)  (cost=0.55 rows=3)
                -> Index scan on tu2 using id  (cost=0.55 rows=3)
        -> Index lookup on tu1 using id (id=tu2.id)  (cost=0.85 rows=1)

1 row in set (0.00 sec)

mysql> explain update tu1 set name='lll' where id in (select id from tu2);
+----+-------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+-------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref         | rows | filtered | Extra                               |
+----+-------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+-------------------------------------+
|  1 | SIMPLE      | tu2   | NULL       | index | id            | id   | 5       | NULL        |    3 |   100.00 | Using where; Using index; LooseScan |
|  1 | UPDATE      | tu1   | NULL       | ref   | id            | id   | 5       | test.tu2.id |    1 |   100.00 | NULL                                |
+----+-------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+-------------------------------------+

42、CTE( common table expression) 支援
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

43、新的備份鎖，堵塞DDL但是允許DML
INSTANCE FOR BACKUP
UNLOCK INSTANCE

44、配置管理介面
admin_address=127.0.0.1
admin_port=33062
create_admin_listener_thread引數控制是否使用管理執行緒介面進行建立連線
預設為OFF

45、hash join

8.0.19 hash_join=off和NO_HASH_JOIN失效，使用NO_BNL代替，且使用BNL的情況基本被hash取代掉了

mysql> set optimizer_switch='hash_join=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain format=tree select /*+ NO_HASH_JOIN (tu1,tu2) */ * from tu1,tu2; 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (no condition)  (cost=2.60 rows=18)
    -> Table scan on tu1  (cost=0.28 rows=6)
    -> Hash
        -> Table scan on tu2  (cost=0.55 rows=3)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain format=tree select /*+ NO_BNL (tu1,tu2) */ * from tu1,tu2; 
+-------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=3.10 rows=18)
    -> Table scan on tu2  (cost=0.55 rows=3)
    -> Table scan on tu1  (cost=0.45 rows=6)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

46、EXPLAIN ANALYZE 8.0.18

可以檢視執行計劃執行的實際值，但是語句經過了實際執行。

mysql> explain analyze select /*+ NO_HASH_JOIN (tu1,tu2) */ * from tu1,tu2;         
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (no condition)  (cost=2.60 rows=18) (actual time=0.233..0.339 rows=21 loops=1)
    -> Table scan on tu1  (cost=0.28 rows=6) (actual time=0.029..0.127 rows=7 loops=1)
    -> Hash
        -> Table scan on tu2  (cost=0.55 rows=3) (actual time=0.073..0.125 rows=3 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

47、timestamp和datetime現在接受輸入時區 8.0.19

mysql> insert into ttstmap values('2019-12-11 10:40:30-05:00');
Query OK, 1 row affected (0.01 sec)

mysql> select *from ttstmap;
+---------------------+
| dt                  |
+---------------------+
| 2019-12-11 23:40:30 |
+---------------------+
1 row in set (0.00 sec)

48、ON DUPLICATE KEY UPDATE 可以使用別名 8.0.19

mysql> create table testiii(a int primary key,b int );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testiii values(1,1);
Query OK, 1 row affected (0.01 sec)
以前：
mysql> INSERT INTO testiii values(1,1) ON DUPLICATE KEY UPDATE a=VALUES(a)+VALUES(b); 
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql> select *from testiii;
+---+------+
| a | b    |
+---+------+
| 2 |    1 |
+---+------+
1 row in set (0.00 sec)

現在：
mysql> INSERT INTO testiii values(2,1) as new ON DUPLICATE KEY UPDATE a=new.a+new.b; 
Query OK, 2 rows affected (0.00 sec)

mysql> select *from testiii;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

49、index hint

Index Hints 待看

###二、將棄用的功能
1、密碼外掛將使用元件的方式提供 
INSTALL COMPONENT 'file://component_validate_password';
UNINSTALL COMPONENT 'file://component_validate_password';
2、utf8mb3字符集將會棄用，使用 utf8mb4代替。
3、AUTO_INCREMENT將不會支援小數型別，使用整數代替。
4、UNSIGNED 對 FLOAT, DOUBLE, DECIMAL型別將會棄用，使用check約束代替。
5、FLOAT(M,D) 和 DOUBLE(M,D)這種指定精度的語法將會棄用。

mysql> create table testggo(id float(10,4));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                          |
+---------+------+------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Specifying number of digits for floating point data types is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------------------------------+

6、ZEROFILL屬性將會棄用
7、 &&, ||, ! ,將會被 AND OR NOT代替
8、relay_log_info_repository=FILE，master_info_repository=FILE 將會棄用，全部使用table模式
9、 max_length_for_sort_data引數將會棄用
10、 mysql_upgrade將會棄用
11、MYSQL_PWD環境變數將會棄用
12、expire_logs_day變數將會棄用，使用binlog_expire_logs_seconds變數代替，
    如果同時設定那麼 binlog_expire_logs_seconds 將會代替expire_logs_day
    引數
13、 innodb_undo_tablespaces引數將會移除，預設為2個undo tablespace

###三、已經移除的功能
1、innodb_locks_unsafe_for_binlog引數去除
2、information_schema_stats引數移除。information_schema_stats_expiry引數加入。
3、新建使用者只能用create user命令，grant建立使用者移除。sql_mode 中的NO_AUTO_CREATE_USER也一併移除。
4、PASSWORD()函式移除，這意味著使用SET PASSWORD ... = PASSWORD('auth_string')將不會支援
   可以使用IDENTIFIED WITH mysql_native_password as進行代替如下

mysql> create user test10 IDENTIFIED WITH mysql_native_password by 'fsdgxcxcbxcb'; 
Query OK, 0 rows affected (0.00 sec)

mysql> select user,authentication_string from mysql.user where user='test10';
+--------+-------------------------------------------+
| user   | authentication_string                     |
+--------+-------------------------------------------+
| test10 | *D3BC4200335920014DCFBE416B82DB9C53B0E233 |
+--------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> create user test11 IDENTIFIED WITH mysql_native_password as '*D3BC4200335920014DCFBE416B82DB9C53B0E233';   
Query OK, 0 rows affected (0.00 sec)

5、query cache完全移除
6、online ddl的DDL log移除，使用innodb_ddl_log table進行代替，但是本表預設不會顯示。
可以將其列印到日誌檔案：
set global log_error_verbosity=3;
SET GLOBAL innodb_print_ddl_logs=1;

7、tx_isolation 和 tx_read_only 引數移除，使用transaction_isolation 和 transaction_read_only引數代替
8、sync_frm引數移除，應為不存在frm檔案了
9、log_warnings引數移除，使用log_error_verbosity變數代替
log_error_verbosity Value Permitted Message Priorities
ERROR
ERROR, WARNING
ERROR, WARNING, INFORMATION
10、global 級別的sql_log_bin移除，sql_log_bin只能用於session級別
11、去掉沒有使用的變數date_format, datetime_format, time_format, max_tmp_tables 
12、GROUP BY 後面的ASC、DESC移除，使用order by來保證順序
13、EXPLAIN 檢視執行計劃去掉EXTENDED 和 PARTITIONS 選項，預設開啟。
14、\N 不在是 NULL的同義詞，請使用NULL
15、mysql_install_db初始化庫的程式已經移除，
16、5.7非Innodb分割槽表升級到8不支援，需要 ALTER TABLE ... ENGINE=INNODB進行重建
17、information_schema中的GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS,
SESSION_STATUS移除，使用performance_schema中的代替
18、INFORMATION_SCHEMA 中的INNODB_LOCKS 和 INNODB_LOCK_WAITS移除，使用Performance Schema中的
data_locks 和 data_lock_waits 代替。
19、innodb 壓縮臨時表將不支援，如果開始 innodb_strict_mode（預設為ON），那麼將會報錯，如果不開啟則會警告
並且以非壓縮方式建立。

Moving Tablespace Files While the Server is Offline
20、以下引數移除
 innodb_file_format  ：以前有Antelope和Barracuda格式
 innodb_file_format_check
 innodb_file_format_max
 innodb_large_prefix ：預設開始
 
21、innodb_support_xa引數移除，預設支援分散式事務
22、innodb_undo_logs變數移除， innodb_rollback_segments引數代替。
23、internal_tmp_disk_storage_engine引數移除，預設使用innodb引擎（8.0.16）

###四、移除的引數和統計值

Com_alter_db_upgrade
Innodb_available_undo_logs
Qcache_free_blocks
Qcache_free_memory
Qcache_hits
Qcache_inserts
Qcache_lowmem_prunes
Qcache_not_cached
Qcache_queries_in_cache
Qcache_total_blocks
Slave_heartbeat_period
Slave_last_heartbeat
Slave_received_heartbeats
Slave_retried_transactions
Slave_running
date_format
datetime_format
des-key-file
group_replication_allow_local_disjoint_gtids_join
have_crypt
ignore-db-dir
ignore_builtin_innodb
ignore_db_dirs
innodb_checksums
innodb_disable_resize_buffer_pool_debug
innodb_file_format
innodb_file_format_check
innodb_file_format_max
innodb_large_prefix
innodb_locks_unsafe_for_binlog
innodb_scan_directories
innodb_stats_sample_pages
innodb_support_xa
innodb_undo_logs
internal_tmp_disk_storage_engine
log-warnings
log_builtin_as_identified_by_password
log_error_filter_rules
log_syslog
log_syslog_facility
log_syslog_include_pid
log_syslog_tag
max_tmp_tables
metadata_locks_cache_size
metadata_locks_hash_instances
multi_range_count
old_passwords
query_cache_limit
query_cache_min_res_unit
query_cache_size
query_cache_type
query_cache_wlock_invalidate
secure_auth
show_compatibility_56
skip-partition
sync_frm
temp-pool
time_format
tx_isolation
tx_read_only
bootstrap
partition

MySQL:8.0.21版本特性

相關文章