一顽聂、新特性
Mysql和Innodb增強(qiáng)：
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; 可以處于非激活狀態(tài)
INFORMATION_SCHEMA.INNODB_TABLESPACES where name like '%undo%' 的state字段
會(huì)說明是否處于激活狀態(tài)
可以刪除undo
DROP UNDO TABLESPACE tablespace_name;
但是默認(rèn)會(huì)建立的2個(gè)undo tablespace不能刪除(innodb_undo_tablespaces)參數(shù)控制
且innodb_undo_log_truncate變量默認(rèn)開啟
2、innodb_rollback_segments為每一個(gè)undo tablespac定義segments 以前是總的
3甲雅、innodb_autoinc_lock_mode當(dāng)前版本設(shè)置為2
4脱拼、innodb_dedicated_server參數(shù)可以自動(dòng)分配
innodb_buffer_pool_size
innodb_log_file_size
innodb_flush_method
5节视、innodb_max_dirty_pages_pct_lwm參數(shù)默認(rèn)設(shè)置為10%,之前為0%
6、innodb_max_dirty_pages_pct參數(shù)默認(rèn)設(shè)置為90%,之前為75%
7益楼、INNODB_TABLESPACES_BRIEF字典表用來提供表空間名猾漫，目錄点晴，類型等
8、Serialized dictionary information(SDI)用于存儲(chǔ)一份字典信息到innodb文件中
9悯周、innodb支持原子性DDL
10粒督、innodb_log_buffer_size變?yōu)榭梢詣?dòng)態(tài)修改
11、8.0.12支持快速加字段ALGORITHM=INSTANT
12禽翼、8.0.13臨時(shí)表空間自動(dòng)回收
13坠陈、數(shù)據(jù)字典采用innodb支持事務(wù)，支持原子性DDL
14捐康、8.0.16后mysqld自動(dòng)升級(jí)
15仇矾、新的caching_sha2_password密碼插件可用，如果想使用老的則使用mysql_native_password插件,這需要更改參數(shù)
default_authentication_plugin解总，或者建立用戶使用 create user test4 IDENTIFIED WITH mysql_native_password by 'fsdgxcxcbxcb';
16贮匕、角色支持
17、MySQL當(dāng)前保存了密碼歷史信息花枫，可以限制再次使用老的密碼
18刻盐、8.0.11新的set persist 用于global級(jí)別和持久化配置文件mysqld-auto.cnf,persisted_globals_load受本參數(shù)影響
如果只想修改mysqld-auto.cnf可以使用set persist_only語(yǔ)句，RESET PERSIST 用于清空劳翰，可以RESET PERSIST var
清空某一個(gè)系統(tǒng)變量
19敦锌、新的命令restart，用于重啟mysqld服務(wù)
20佳簸、Innodb持久化自增值
21乙墙、innodb_deadlock_detect參數(shù)增加用于判斷是否進(jìn)行死鎖檢測(cè)
22、innodb_undo_log_truncate默認(rèn)開啟
23生均、innodb_temp_tablespaces_dir用于保存用戶臨時(shí)表和內(nèi)部臨時(shí)表 叫做Session Temporary Tablespaces听想，
默認(rèn)會(huì)建立10個(gè).ibt文件，回話退出后會(huì)釋放马胧。INNODB_SESSION_TEMP_TABLESPACES用于記錄臨時(shí)表空間信息汉买。
老的innodb_temp_data_file_path只用于存儲(chǔ)臨時(shí)回滾段，叫做Global Temporary Tablespace佩脊，另外
INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 可以查看建立的臨時(shí)表信息（測(cè)試8.0是否可以看到內(nèi)部臨時(shí)表）
24蛙粘、內(nèi)部臨時(shí)表使用了新的內(nèi)存引擎TempTable代替掉了memory引擎。默認(rèn)由參數(shù)internal_tmp_mem_storage_engine
控制威彰。參數(shù)temptable_max_ram控制其最大使用內(nèi)存（默認(rèn)1G）出牧，當(dāng)超過內(nèi)存大小將會(huì)使用物理臨時(shí)表，參數(shù)
temptable_use_mmap用于控制是使用mmap方式還是innodb引擎作為物理臨時(shí)表抱冷。
25崔列、新的視圖INFORMATION_SCHEMA.INNODB_CACHED_INDEXES 用于查看索引的緩存情況
26、新增innodb_parallel_read_threads參數(shù)用于check table并行訪問cluster性能，但是用處不大赵讯，8.0.17(count(*)?)
27盈咳、新增innodb_idle_flush_pct參數(shù)默認(rèn)100，用于控制空閑刷新臟頁(yè)边翼。
28鱼响、直方圖統(tǒng)計(jì)信息 histogram_generation_max_mem_size參數(shù)控制直方圖最大的內(nèi)存使用(默認(rèn)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"' |
+---------------------------------+
| 0.0491431208869665 |
+---------------------------------+
29组底、8.0.20 精細(xì)化 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全局鎖進(jìn)行細(xì)化。
31债鸡、8.0.21 redo可以臨時(shí)關(guān)閉江滨，如果在load 數(shù)據(jù)的時(shí)候
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 參數(shù)用于關(guān)閉啟動(dòng)的時(shí)候檢查Innodb文件厌均，加快啟動(dòng)速度
33唬滑、8.0.21 原子化DDL 實(shí)現(xiàn)，create table select
34棺弊、字符集默認(rèn)變?yōu)閡ft8mb4
優(yōu)化器增強(qiáng)：
35晶密、不可見索引增加，在視圖STATISTICS中進(jìn)行顯示
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';
受到 參數(shù)optimizer_switch選項(xiàng) use_invisible_indexes的影響模她，默認(rèn)為off
優(yōu)化器會(huì)忽略不可見索引稻艰，聚集索引不能設(shè)置為不可見
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
不可見索引不影響索引的維護(hù)。
36侈净、倒順?biāo)饕С?br>
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尊勿、函數(shù)索引支持
mysql> desc select ascii(i) from t2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | ASCII | 4 | NULL | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
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優(yōu)化了
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參數(shù)控制是否使用管理線程接口進(jìn)行建立連接
默認(rèn)為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
可以查看執(zhí)行計(jì)劃執(zhí)行的實(shí)際值振惰，但是語(yǔ)句經(jīng)過了實(shí)際執(zhí)行歌溉。
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現(xiàn)在接受輸入時(shí)區(qū) 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)
現(xiàn)在：
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字符集將會(huì)棄用漫谷，使用 utf8mb4代替。
3蹂析、AUTO_INCREMENT將不會(huì)支持小數(shù)類型舔示，使用整數(shù)代替。
4电抚、UNSIGNED 對(duì) FLOAT, DOUBLE, DECIMAL類型將會(huì)棄用惕稻，使用check約束代替。
5蝙叛、FLOAT(M,D) 和 DOUBLE(M,D)這種指定精度的語(yǔ)法將會(huì)棄用俺祠。
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屬性將會(huì)棄用
7甥温、 &&, ||, ! ,將會(huì)被 AND OR NOT代替
8锻煌、relay_log_info_repository=FILE，master_info_repository=FILE 將會(huì)棄用姻蚓，全部使用table模式
9宋梧、 max_length_for_sort_data參數(shù)將會(huì)棄用
10、 mysql_upgrade將會(huì)棄用
11狰挡、MYSQL_PWD環(huán)境變量將會(huì)棄用
12捂龄、expire_logs_day變量將會(huì)棄用，使用binlog_expire_logs_seconds變量代替加叁，
如果同時(shí)設(shè)置那么 binlog_expire_logs_seconds 將會(huì)代替expire_logs_day
參數(shù)
13倦沧、 innodb_undo_tablespaces參數(shù)將會(huì)移除，默認(rèn)為2個(gè)undo tablespace
三它匕、已經(jīng)移除的功能
1展融、innodb_locks_unsafe_for_binlog參數(shù)去除
2、information_schema_stats參數(shù)移除豫柬。information_schema_stats_expiry參數(shù)加入告希。
3、新建用戶只能用create user命令烧给，grant建立用戶移除燕偶。sql_mode 中的NO_AUTO_CREATE_USER也一并移除。
4础嫡、PASSWORD()函數(shù)移除指么，這意味著使用SET PASSWORD ... = PASSWORD('auth_string')將不會(huì)支持
可以使用IDENTIFIED WITH mysql_native_password as進(jìn)行代替如下
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進(jìn)行代替晚唇，但是本表默認(rèn)不會(huì)顯示。
可以將其打印到日志文件：
set global log_error_verbosity=3;
SET GLOBAL innodb_print_ddl_logs=1;
7姑廉、tx_isolation 和 tx_read_only 參數(shù)移除缺亮，使用transaction_isolation 和 transaction_read_only參數(shù)代替
8、sync_frm參數(shù)移除桥言，應(yīng)為不存在frm文件了
9萌踱、log_warnings參數(shù)移除，使用log_error_verbosity變量代替
log_error_verbosity Value Permitted Message Priorities
ERROR
ERROR, WARNING
ERROR, WARNING, INFORMATION
10号阿、global 級(jí)別的sql_log_bin移除并鸵，sql_log_bin只能用于session級(jí)別
11、去掉沒有使用的變量date_format, datetime_format, time_format, max_tmp_tables
12扔涧、GROUP BY 后面的ASC园担、DESC移除，使用order by來保證順序
13枯夜、EXPLAIN 查看執(zhí)行計(jì)劃去掉EXTENDED 和 PARTITIONS 選項(xiàng)弯汰，默認(rèn)開啟。
14湖雹、\N 不在是 NULL的同義詞咏闪，請(qǐng)使用NULL
15、mysql_install_db初始化庫(kù)的程序已經(jīng)移除摔吏，
16鸽嫂、5.7非Innodb分區(qū)表升級(jí)到8不支持，需要 ALTER TABLE ... ENGINE=INNODB進(jìn)行重建
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 壓縮臨時(shí)表將不支持滤祖，如果開始 innodb_strict_mode（默認(rèn)為ON）才避，那么將會(huì)報(bào)錯(cuò)，如果不開啟則會(huì)警告
并且以非壓縮方式建立氨距。
Moving Tablespace Files While the Server is Offline
20、以下參數(shù)移除
innodb_file_format ：以前有Antelope和Barracuda格式
innodb_file_format_check
innodb_file_format_max
innodb_large_prefix ：默認(rèn)開始
21棘劣、innodb_support_xa參數(shù)移除俏让，默認(rèn)支持分布式事務(wù)
22、innodb_undo_logs變量移除， innodb_rollback_segments參數(shù)代替首昔。
23寡喝、internal_tmp_disk_storage_engine參數(shù)移除，默認(rèn)使用innodb引擎（8.0.16）
四勒奇、移除的參數(shù)和統(tǒng)計(jì)值
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
