MySQL8.0+新特性
2019-09-03 11:30:38 0 举报AI智能生成
mysql8新特性
作者其他创作
大纲/内容
MySQL8.0+新特性
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
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，禁用自动密码过期。
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在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。
.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引
myisam表还支持三种不同的存储格式：
静态表(默认，但是注意数据末尾不能有空格，会被去掉)
动态表
压缩表
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
提供回滚和多个行版本控制(MVCC)
日志持久化过程
分支主题
0 条评论
回复 删除
下一页
