###一、新特性
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"' | +---------------------------------+ | 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> 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优化了 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 skip_name_resolve 会关闭域名解析必须建立root@127.0.0.1 用户登录
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
