MySQL8.0+新特性
2019-09-03 11:30:38 0 举报AI智能生成
mysql8新特性
作者其他创作
大纲/内容
1. 默认字符集由latin1变为utf8mb4
在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4
是utf-8的扩展，一个字符集能存4个字节，不但可以支持中文，还可以支持表情包、emoji表情
2.MyISAM系统表全部换成InnoDB表
系统表全部换成事务型的innodb表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表。
3. 自增变量持久化
自增主键AUTO_INCREMENT=max(primary key)+1
AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变。
4. DDL原子化
InnoDB表的DDL支持事务完整性，要么成功要么回滚，将DDL操作回滚日志写入到data dictionary 数据字典表 mysql.innodb_ddl_log 中用于回滚操作
5. 参数修改持久化
MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。
查看mysql的所有全局变量的值:
SHOW GLOBAL VARIABLES
6. 新增降序索引
7. group by 不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
8. JSON特性增强
MySQL 8.0添加了新的JSON函数，并提高了对JSON值进行排序和分组的性能。
JSON表函数
使用SQL机制来处理JSON数据，JSON_TABLE() 创建JSON数据的关系视图。它将JSON数据评估的结果映射到关系行和列
JSON聚合函数
JSON_ARRAYAGG() 来生成JSON数组并 JSON_OBJECTAGG() 生成JSON对象。这使得可以将多行中的JSON文档组合成JSON数组或JSON对象
JSON合并函数
JSON_MERGE() MySQL 8.0中不推荐使用
JSON漂亮功能
JSON_PRETTY() 函数接受JSON本机数据类型或JSON的字符串表示形式，并以人类可读的方式返回JSON格式的字符串，并带有新行和缩进。
JSON大小函数
JSON_STORAGE_SIZE() 回报的JSON数据类型字节的实际大小。在 JSON_STORAGE_FREE() 返回以字节为单位，包括分段和填充保存就地更新一个JSON二进制类型的自由空间。
JSON改进了排序
MySQL 8.0通过使用可变长度排序键为JSON值排序/分组提供了更好的性能。
JSON部分更新
MySQL的8.0增加了对部分更新支持 JSON_REMOVE() ， JSON_SET() 以及 JSON_REPLACE() 功能。如果只更新JSON文档的某些部分，我们希望向处理程序提供有关更改内容的信息，以便存储引擎和复制不需要编写完整文档。
GIS
MySQL 8.0提供地理支持。这包括空间参考系统（SRS）的元数据支持，以及SRS感知空间数据类型，空间索引和空间函数。简而言之，MySQL 8.0可以理解地球表面的纬度和经度坐标
空间参考系统（SRS）
ST_SPATIAL_REFERENCE_SYSTEMS 信息模式视图提供有关空间数据可用的空间参考系统的信息。
SRID感知空间数据类型
SRID感知空间索引
SRID感知空间功能
9. redo & undo 日志加密
innodb_undo_log_encrypt
innodb_undo_log_encrypt
10. innodb select for update跳过锁等待
select ... for update，select ... for share(8.0新增语法) 添加 NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。
通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。
11. 增加SET_VAR语法
在sql语法中增加SET_VAR语法，动态调整部分参数，有利于提升语句性能。
12. 支持不可见索引
13. 支持直方图
14. 新增innodb_dedicated_server参数
能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method三个参数
15. 日志分类更详细
在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]
16. undo空间自动回收
innodb_undo_log_truncate参数在8.0.2版本默认值由OFF变为ON，默认开启undo日志表空间自动回收。
innodb_undo_tablespaces参数在8.0.2版本默认为2，当一个undo表空间被回收时，还有另外一个提供正常服务。
innodb_max_undo_log_size参数定义了undo表空间回收的最大值，当undo表空间超过这个值，该表空间被标记为可回收。
17. 增加资源组
MySQL 8.0新增了一个资源组功能，用于调控线程优先级以及绑定CPU核。
MySQL用户需要有 RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。
默认提供两个资源组，分别是USR_default，SYS_default
18. 增加角色管理
角色可以认为是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无需为每个用户单独授权。
1、MySQL用户管理
1.1、验证插件和密码加密方式的变化，caching_sha2_password是默认的身份验证插件
1.2、用户授权和修改密码
1.3、密码过期时间管理，要全局建立自动密码到期策略，请使用default_password_lifetime系统变量。其默认值为0，禁用自动密码过期。
1.4、MySQL用户密码重用策略设置,MySQL允许限制重复使用以前的密码。
2、MySQL8.0的角色管理
2.1、创建角色并授予用户角色权限
2.2、检查角色权限
要验证分配给用户的权限，使用 SHOW GRANTS
2.3、撤消角色或角色权限
正如可以授权某个用户的角色一样，可以从帐户中撤销这些角色：REVOKE role FROM user;REVOKE可以用于角色修改角色权限。
2.4、删除角色
要删除角色，请使用DROP ROLE：
MyISAM与InnoDB两者之间区别
一、表锁差异
MyISAM:
myisam只支持表级锁
InnoDB
Innodb支持事务
事务隔离级别
未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)
行级锁
行锁模式
共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁
死锁
解决死锁的方法：
1、 数据库参数
2、 应用中尽量约定程序读取表的顺序一样
3、 应用中处理一个表时，尽量对处理的顺序排序
4、 调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）
二、数据库文件差异
MyISAM:
myisam属于堆表
myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。
.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引
myisam表还支持三种不同的存储格式：
静态表(默认，但是注意数据末尾不能有空格，会被去掉)
动态表
压缩表
InnoDB
innodb属于索引组织表
innodb有两种存储方式
共享表空间存储
所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件
多表空间存储
每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。
三、索引差异
1、关于自动增长
myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。
2、关于主键
myisam允许没有任何索引和主键的表存在，
myisam的索引都是保存行的地址。
innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
innodb的数据是主索引的一部分，附加索引保存的是主索引的值。
3、关于count()函数
myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值
innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。
4、全文索引
myisam支持 FULLTEXT类型的全文索引
innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询）
5、delete from table
使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)
6、索引保存位置
myisam的索引以表名+.MYI文件分别保存。
innodb的索引和数据一起保存在表空间里。
事务日志
redo log
redo log的基本概念
redo log包括两部分
一是内存中的日志缓冲(redo log buffer)
该部分日志是易失性的
二是磁盘上的重做日志文件(redo log file)
该部分日志是持久的
undo log
redo log的基本概念
提供回滚和多个行版本控制(MVCC)
日志持久化过程
分支主题
0 条评论
下一页
