MYSQL 8 推荐的理由

MYSQL 8 推荐的理由

祖仙教小凡仙 海鲨数据库架构师

推荐理由

是 MYSQL 8.0.23 比较MYSQL 5.7版本上来说 并没有多大新功能添加. 只是不断完善各个方面.相对来说更接近ORACLE用户的使用习惯. 更好的开发特性,更好的安全特性,更好的维护特性.更高的并发特性. 最新的就是MGR 高可用架构.

推荐特性点是 1 事务性数据字典;2 原子DDL; 3 INNODB增强; 4 字符集支持; 5 窗口函数; 6备份锁; 7连接管理; 8 优化器索引;9 不可见列;10 HASH JOIN 11 JSON 支持 12 MGR高可用. 对高并发的追求 建议采用MYSQL GTID 主从架构 MHA管理; 对低并和高可用追求,建议采用MYSQL的组复制MGR架构+MYROUTE管理

1 事务性数据字典

 
数据字典表以InnoDB表存储字典数据，位于mysql数据库下，对外不可见。有专门的表空间mysql.idb，位于数据目录下。但是可以通过informaction_schema下面的一些表来查询字典数据。

在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如：.frm, .par, .trn, .isl, .db.opt等都在MySQL8.0中不存在了。 

2 原子DDL

 
事务性数据字典的引入，使得原子DDL成为可能。作为事务要不提交，要不回滚。分为表级和非表级语句，表级支持InnoDB。非表级包括：create, drop等语句。 

3 安全和账号管理

 
Mysql系统数据库下的权限表现在都是InnoDB的，以前是MyISAM的，对于多个用户进行操作时会出现部分成功，部分失败的情况。现在要不成功，要不rollback。 

4 认证插件

 
新的caching_sha2_password认证插件作为默认的认证插件，比mysql_native_password更安全，性能更好。但可能在现网会出现一个连接相关的问题。　　1）系统变量default_authentication_plugin去改变，在my.ini文件的[mysqld]下面设置default_authentication_plugin=mysql_native_password即可 

 
　　如果希望只是某一个用户通过mysql_native_password的方式认证，可以修改数据库mysql下面的user表的字段，执行以下命令：

　　>alter user '用户名'@'主机' identified width mysql_native_password by '密码';
 

5 密码管理

对于密码管理更加严格控制，可以维护密码历史信息。启用密码重用策略。　　

password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制　　

　　password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制 password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成ON

　　查询当前MySQL密码管理策略相关变量，使用以下命令：

　　

 
　　>show variables like 'password%'; 

设置全局的密码管理策略，在my.ini配置文件中，设置以上3个变量的值这种设置方式，需要重启mysql服务器；某些生产环境不允许重启，MySQL8.0提供了关键字persist,持久化，执行以下命令：

 
　　>set persist password_history=6;

　　这条命令会在数据目录下生成新的配置文件（/var/lib/mysql/mysqld-auto.cnf），下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的
　　
针对某一个用户单独设置密码管理策略

　　>alter user '用户名'@'主机' password history 5;

　　这样，这个用户的password_history 就被设置成了5,查看一下：

　　>show user,host,Password_reuse_history from user;
 

6 角色管理　　

　　角色：一组权限的集合

　　

　　一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有角色对应的权限

　　

 
　　1)创建一个角色

　　>create role '角色1';

　　

　　2)为这个角色赋予相应权限

　　>grant insert,update on *.* to '角色1';

　　

　　3)创建一个用户

　　>create user '用户1' identified by '用户1的密码';

　　

　　4)为这个用户赋予角色的权限

　　>grant '角色1' on *.* to '用户1'；

　　执行完上面4步，用户1就拥有了插入与更新的权限

　　

　　5)再创建1个用户

　　>create user '用户2' identified by '用户2的密码';

　　

　　6)为这个用户赋予同样的角色

　　>grant '角色1' on *.* to '用户2';

　　

　　执行完上面2步，用户2也用了角色1的权限，即插入与更新

　　查看用户权限，执行以下命令：

　　>show grants for '用户名';

　　7)启用角色,设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限

　　>set default role '角色名' to '用户名';

　　8)如果一个用户有多个角色，使用以下命令

　　>set default role all to '用户名';

　　MySQL中与用户角色相关的表：mysql.default_roles、mysql.role_edges,有兴趣的朋友可以进去查看下。

　　9)撤销权限

　　>revoke insert,update on *.* from '角色名';
 

7资源管理

 
支持资源组的创建和管理，能将运行的线程分配给特定的资源组。 

8 InnoDB增强

 
8.1 自增列方面。

现在自增列计数器会在每次值修改时，将值写到REDO LOG中，并且在CHECKPOINT时写到存储引擎私有的系统表中。这就消除了以往重启实例自增列不连续的问题。

8.2      当索引损坏时，会把损坏标识写到redo log。在checkpoint时会将内存的损坏标识数据写到存储引擎私有的系统表中。

8.3新的动态变量innodb_deadlock_detect，可以禁用死锁检查。在高并发的系统中，无数个线程等待同一个锁，死锁检查可能会引起系统宕机。有时，禁用死锁检查更有效。当死锁发生时，可以依赖innodb_lock_wait_timeout设置让事务回滚。

8.3      新的information_schema.innodb_cached_indexes表可以报告每个索引在bufferpool中的索引页的数量。

8.5      InnoDB临时表现在创建在共享的临时表空间中。

8.6支持redo log和undo log的加密

8.7      对于select…for share和select…for update 锁读语句，支持nowait和skip locked选项。Nowait表示如果请求的行被去其他事务锁住了立即返回。SKIP LOCKED则会从结果集中移除上锁的行。

8.8      InnoDB存储引擎使用MySQL的数据字典，而不是用自己的和引擎相关的数据字典。

8.9      mysql库的系统表和数据字典表创建在单独的InnoDB表空间中，文件名为mysql.ibd. 以前这些表都是创建在各自的InnoDB表空间中。 

9 字符集支持

 
默认字符集从latin1变成了utf8mb4. 对于utf8mb4字符集增加了新的比较规则，比如utf8mb4_ja_0900_as_cs。 

10 数据类型支持 MySQL支持将字符常量或者表达式作为数据类型的默认值。包括能将表达式作为BLOB, TEXT, GEOMETRY, JSON等数据类型的默认值，这在以前是不可以的。

11 通用表表达式

 
在SELECT等语句前，使用WITH字句来对临时结果集进行命名。 

12 窗口函数

 
MySQL现在支持窗口函数，在一个查询中对每行进行计算。这些函数包括RANK(), LAG(), NTILE()。另外，有几个聚合函数也能用作窗口函数。比如SUM(),AVG()。 

13 内部临时表

 
内存内部临时表，默认的存储引擎从MEMORY变成了TempTable。TempTable对于VARCHAR和VARBINARY字段存储更高效。

Internal_tmp_mem_storage_engine:该变量用来定义内存内部临时表使用哪个引擎。允许取值有TempTable(默认)和MEMORY。

Temtable_max_ram: 内存内部临时表超过这个值就会将数据存储到磁盘上。 

14 日志记录

 
错误日志模块使用MySQL组件架构重写了，使用内置的组件来实现错误日志。

另外，还有一个可加载的JSON日志记录器。 要控制启用哪些日志组件，使用 log_error_services 系统变量。
 

15 备份锁

 
引入了一种新的备份锁(backup lock)，它允许在online备份的时候进行DML操作，同时可防止快照不一致。备份锁由lock instance forbackup和unlock instance语法支持。使用这些语句需要BACKUP_ADMIN权限。原来的extrabackup等备份软件是不是应该改写，不要使用flush table with read lock这样的语句来上锁。当有大事务时，会hung住，无法备份。如果采用lock instance for backup则没有问题。
 

16 复制增强

 
支持使用压缩格式，对JSON文档的部分更新记录binlog，能节省空间。如果使用的STATEMENT格式的binlog，该功能自动开启，或者通过binlog_row_value_options系统变量设为PARTIAL_JASON来开启。 

17 连接管理

 
允许配置一个专门端口用于管理连接，当连接数打满时可以用于连接数据库进行管理。

·        需要设置admin_address，默认该值为空。

·        使用的端口默认为33062, 由admin_port来设置。

·        该端口的没有连接数的限制。

·        需要有SERVICE_CONNECTION_ADMIN的权限

·        Create_admin_listener_thread系统变量用来决定是用普通连接的监听线程还是采用自己专用的线程。默认值为普通监听线程。
 

18 优化器索引

1.隐藏索引（invisible index）　　隐藏索引不会被优化器使用，但仍需要维护

 
　　应用场景：

　　1）软删除

　　　删除索引，在线上，如果删除错了索引，只能通过创建索引的方式将其添加回来，对于一些大的数据库而言，是比较耗性能的；为了避免删错，可以先将其设置为不可见，优化器这时候就不会使用它，但是后台仍然在维护，确定后，再删除。

　　2）灰度发布

　　　　与软删除差不多，如果想要测试一些索引的功能或者随后可能会使用到这个索引，可以先将其设置为隐藏索引，对于现有的查询不会产生影响，测试后，确定需要该索引，可以将其设置成可见索引。

　　创建隐藏索引，执行如下命令（如果是不隐藏，则不需要后面的invisible关键字）：

　　>create index 索引名称 on 表名(字段名) invisible;

　　查询某一张表的索引，执行如下命令：

　　>show index from 表名；

　　使用explain语句查看查询优化器对索引的使用情况

　　>explain select * from 表名 where 条件;

　　查询优化器有很多开关，有一个是use_invisible_indexes(是否使用隐藏索引),默认是off(不适用)，将其设置成on,即可使用隐藏索引。查看当前查询优化器的所有开关变脸，执行如下命令：

　　>select @@optimizer_switch;

　　设置已经存在的索引为可见或者隐藏，执行如下命令：

　　>alter table 表名 alter index 索引名 visible;

　　>alter table 表名 alter index 索引名 invisible;
主键不可以设置为隐藏所以。
 

2.降序索引（descending index）

 
　　MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序所以，且必须是BTREE降序索引，MySQL8.0不在对group by操作进行隐式排序。 

3.函数索引

 
　　索引中使用函数表达式,支持JSON数据节点的索引,　函数索引是基于虚拟列的功能实现的

假设用户表（tb_user）的的用户登录账号(username)不需要区分大小写，则可以创建一个函数索引

>create indexusername_upper_index on tb_user((upper(username)));

这样在查询的时候 SELECT *FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。

上面的函数索引，也可以通过MySQL5.7已有的虚拟计算列来模拟，为用户表（tb_user）创建新的一列（new_column）,这一列是计算列，不需要赋值，它的值就是username的大写。

>alter tbale tb_user addcolumn new_column varchar(10) generated always as (upper(username));

然后给new_column创建一个索引，可以达到模拟MySQL8.0中的函数索引的效果。 

19 不可见列

 
列可以定义为不可见，例如：

# 创建表时，可使其不可见（ALTER TABLE 也支持）

mysql> CREATE TABLE t1 (col1INT, col2 INT INVISIBLE);

mysql> INSERT INTO t1 (col1,col2) VALUES(1, 2), (3, 4);

# SQL 语句通过显式引用它来使用不可见列

mysql> SELECT * FROM t1;

+------+

| col1 |

+------+

|    1 |

|    3 |

+------+

# 如果未引用不可见的列，则该列将不会出现在结果中

mysql> SELECT col1, col2FROM t1;

+------+------+

| col1 | col2 |

+------+------+

|    1 |   2 |

|    3 |   4 |

+------+------+ 

20 Doublewrite 文件页加密

 
InnoDB 自动加密属于加密表空间的 Doublewrite 文件页面，无需采取任何措施。使用相关表空间的加密密钥对Doublewrite 文件页进行加密。同一表空间中被写入数据的加密页面也会被写入 Doublewrite 文件。属于未加密表空间的 Doublewrite 文件页面保持未加密状态。在恢复过程中，加密的Doublewrite 文件页面是未加密状态并检查是否损坏。 

21 提高账户确定性

 
为了让 TCP 连接匹配账户更具确定性，在匹配主机名指定的账户前，匹配账户的主机名部分将以以下顺序检查使用主机 IP 地址指定账户。

# 指定 IP 地址的帐户

mysql> CREATE USER'user_name'@'127.0.0.1';

mysql> CREATE USER'user_name'@'198.51.100.44';

# 使用 CIDR 表示法指定为 IP 地址的帐户

mysql> CREATE USER'user_name'@'192.0.2.21/8';

mysql> CREATE USER'user_name'@'198.51.100.44/16';

# 使用带子网掩码格式的指定为 IP 地址的账户

mysql> CREATE USER 'user_name'@'192.0.2.0/255.255.255.0';

mysql> CREATE USER'user_name'@'198.51.0.0/255.255.0.0';
 

22 更精准的 FLUSH 权限

 
授予 RELOAD 权限的用户可以执行各种操作。在某些情况下，为了使 DBA 避免授予 RELOAD 并使用户权限更接近允许的操作，已对 FLUSH 操作的更精细的特权控制，以使客户可以执行 FLUSHOPTIMIZER_COSTS，FLUSH STATUS，FLUSHUSER_RESOURCES 和 FLUSH TABLES 语句，无需 RELOAD 权限。 

23 优化 TRUNCATE / DROP

当用户对 InnoDB 表空间TRUNCATE 或 DROP 操作：

对有庞大缓冲池(>32GB)实例上的大表删除

对具有自适应哈希索引引用大量页面的表空间

TRUNCATE临时表空间

 
以上情况，MySQL 现在将其标记为已删除，然后从缓冲池懒惰地释放属于已删除表空间的所有页面，或者像释放页面一样重用它们。 

24 新增表空间 AUTOEXTEND_SIZE 属性

 
InnoDB 常规表 CREATE /ALTER TABLESPACE 子句和独立表空间的 CREATE / ALTER TABLE 子句新增自动扩展属性。原表空间的增长大小已在 InnoDB 内部硬编码为 1MB [默认]（page_size * 一个范围内的页面数）。设置后，表空间的增长大小可以由用户决定。 

25 新增 temptable_max_mmap 变量

 
新变量定义了 TempTable 存储引擎在开始将内部临时表数据存储到 InnoDB 磁盘内部临时表之前，被允许从内存映射文件分配的最大内存量。temptable_max_mmap= 0 设置将禁用从内存映射文件的分配。 

26 直接从禁用 GTID 的主机复制到启用 GTID 的从机

 
CHANGE REPLICATION SOURCE TO 语句新增选项：ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = [OFF，LOCAL，<UUID>] 

允许数据在非 GTID 实例和GTID 实例之间传输。

27 在 MTS 死锁检测基础结构中包含 MDL 和 ACL 锁

 
将提供多线程的 REPLICA 所需的线程序列化基础结构与 MDL 和 ACL 访问序列化基础结构集成在一起，该多线程 REPLICA 与 SOURCE 保持相同的提交顺序。其动机是能够在 REPLICA 主动处理变更流时在 REPLICA 上执行任何客户端语句。此类语句可能会创建死锁，必须对其进行检测，并最终将其破坏以继续执行。 

28 组复制

 
异步复制通道的自动连接故障转移，将确保接收方的发送方列表与组复制成员身份更改同步。 

29 HASH JOIN

 
优化哈希联接的哈希表的实现。目的是提高性能，使用更少的内存并改善内存控制。

用标准 C++11 替换了部分旧的InnoDB 代码。加强代码中使用原子性的规则和语义，从而使代码更符合标准。
 

30 JSON增强

 
1.内联路径操作符

column->>path 等价于之前的：JSON_UNQUOTE(column -> path)

JSON_UNQUOTE(JSON_EXTRACT(column,path))

2.JSON聚合函数

MySQL8.0和MySQL5.7.22增加了2个聚合函数

JSON_ARRAYAGG(),将多行数据组合成json数组

selecto_id,json_arrayagg(attribute) as attributes from t group by o_id;

JSON_OBJECTAGG()，用于生成json对象　　示例：select o_idjson_objectagg(attribute,value) as attributes from t group by o_id;

注意：json的聚合函数针对重复key,会使用最后的覆盖前面已有的值，如果下面的o_id=3，它的color有2个值，一个green,一个yellow,使用生成json的聚合函数的时候，前面的green会被覆盖掉。

3JSON实用函数

1)JSON_PRETTY()  输出json数据的时候，格式化。

select json_object('id',3,'name','Barney');

selectjson_pretty(json_object('id',3,'name','Barney'));

2)JSON_STORAGE_SIZE() json数据所占用的存储空间（单位：字节）

3)JSON_STORAGE_FREE() json数据更新后所释放的空间（单位：字节）

4.JSON合并函数

MySQL8.0废弃了JSON_MERGE()函数，推荐使用以下两个函数合并JSON数据

1)JSON_MERGE_PATCH()

JSON_MERGE_PRESERV()

5 JSON表函数　　
　　MySQL8.0新增了JSON_TABLE()函数，将JSON数据转换成关系表，可以将该函数的返回结果当做一个普通的临时表进行sql查询。 

查看更多关于MYSQL 8 推荐的理由的详细内容...