一、新特性
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
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
网友评论