【MySQL】MySQL 5.7 新特性整理

最新推荐文章于 2024-05-19 00:33:45 发布

白萝卜弟弟

最新推荐文章于 2024-05-19 00:33:45 发布

阅读量1.6k

点赞数 2

分类专栏：数据存储学习探索文章标签： mysql 数据库

本文链接：https://blog.csdn.net/stxyg/article/details/131168282

版权

学习探索同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据存储

1 篇文章 0 订阅

订阅专栏

MySQL 5.7 新特性有很多，有以下这些

一、InnoDB变更

默认存储引擎改为InnoDB:MySQL 5.7将默认存储引擎改为InnoDB,以推动其成为事实标准。这意味着任何未指定存储引擎的表都将使用InnoDB。


CREATE TABLE t1 (id INT); -- 使用InnoDB

多页表空间:新引入多页表空间,用于代替原来的系统表空间和页文件。每个表空间可容纳多个表,并支持多达128TB的数据。


CREATE TABLESPACE ts_1 ADD DATAFILE 'ts_1.ibd' SIZE 1G;
CREATE TABLE t1 (id INT) TABLESPACE ts_1;

临时表空间:新增临时表空间的概念,临时表不指定表空间时会使用临时表空间。临时表空间的大小可配置,默认自适应。


SET GLOBAL tmp_table_size = 100*1024*1024;  -- 配置临时表最大空间为100M
CREATE TEMPORARY TABLE t1 (id INT); -- 不指定表空间,使用临时表空间

自适应哈希索引:新引入自适应哈希索引,可自动调整索引的大小来控制存储空间,并且保持查询性能。


CREATE TABLE t1 (id INT, KEY idx1 (id)) ENGINE=INNODB;

二、安全性提升

更安全的密码策略:新增更严格的密码策略,包括密码长度限制、大小写混合、特殊字符及历史密码检查等。


SET GLOBAL validate_password_policy=MEDIUM;    -- 设置中等密码强度策略
CREATE USER 'user1' IDENTIFIED BY 'Pass@123';  -- 密码需符合策略

安全的初始密码:新增初始密码选项,可通过–initialize或–initialize-insecure选项设置root用户初始密码。


mysqld --initialize-insecure --user=mysql   --设置root初始密码,不安全 
mysqld --initialize  --设置root复杂初始密码,安全

自定义test数据库

MySQL 5.7 默认安装完成后没有 test 数据库。用户可以自行创建 test 数据库并对其进行权限控制。
默认SSL加密

MySQL 5.7 采用了更加简单的 SSL 安全访问机制，默认连接使用 SSL 的加密方式。
密码过期策略

MySQL 5.7 支持用户设置密码过期策略，要求用户在一定时间过后必须修改密码。
审计日志:新增审计日志(Audit Log)功能,可记录数据库访问和操作的审计跟踪信息。


SET GLOBAL audit_log_enabled=ON;   -- 启用审计日志   
SET GLOBAL audit_log_exclude_users='MYSQL.EVENT';  -- 排除事件用户

三、性能优化

SQL临时表现在存储在内存中:SQL临时表现在默认存储在内存临时表空间中, 之前使用磁盘空间,新特性可提升查询性能。


CREATE TEMPORARY TABLE t1 (id INT); -- 临时表存储在内存中

Descending Indexes:新增降序索引,可加速降序查询。

 
CREATE TABLE t1 (id INT, KEY (id DESC));  -- 降序索引
SELECT * FROM t1 ORDER BY id DESC; -- 使用降序索引,性能提升

JSON支持:新增JSON数据类型,可有效存储和查询JSON文档。JSON类型数据可直接索引,查询性能较高。


CREATE TABLE t1 (jdoc JSON);  
INSERT INTO t1 VALUES('{"name": "zhou", "age": 30}');  
SELECT * FROM t1 WHERE jdoc->"$.name" = "zhou";   -- JSON查询使用->

子查询优化:优化器现在可以将IN子查询转换为JOIN查询,提高查询效率。


SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);  
-- 上述查询可能被优化为JOIN查询

四、GIS及空间函数增强

新的GIS函数:新增多个GIS函数,如ST_Distance()、ST_Intersection()、ST_IsValid()等,用于计算几何对象之间的距离、交集以及判断几何对象的有效性。


SELECT ST_Distance(pt1, pt2) AS distance 
FROM points;  -- 计算两个点之间的距离

SELECT ST_Intersection(ls1, ls2) AS intersect  
FROM lines; -- 找出两条线的交点

空间参照系支持:支持使用空间参照系定义GIS数据。可定义投影坐标系及地理坐标系(WGS 84)等。


CREATE TABLE geom (g GEOMETRY(POINT, 4326));  -- 使用WGS 84坐标系

R-Tree索引 improve:R-Tree索引进行改进,支持更多几何类型并优化性能。R-Tree索引可用于空间查询和邻域分析。


CREATE INDEX idx ON geom (g) USING RTREE; -- 在geom表创建R-Tree索引

五、新增fractional seconds时钟

时钟精度提高到微秒级(6位)。可用于需要高精度的场景,如高频交易记录。


CREATE TABLE t1 (
  ts TIMESTAMP(6),   -- 精确到微秒
  dt DATETIME(6)     -- 精确到微秒
);

INSERT INTO t1 VALUES(NOW(6), NOW(6));   -- NOW()也可达微秒精度

六、SQL模式及用户权限控制增强

增强的用户权限控制:用户权限控制支持授予和回收更细致的权限,实现更高的安全性要求。


GRANT SELECT (id, name) ON db1.t1 TO 'user1'@'localhost';  
-- 仅授权用户查询t1表的id和name字段

REVOKE INSERT ON db1.t1 FROM 'user1'@'localhost'; 
-- 回收user1用户对db1.t1表的插入权限

全面支持SQL标准模式:全面支持SQL标准模式概念,提供更高级的数据库封装及权限管理。


CREATE SCHEMA scm1;                  -- 创建模式  
CREATE TABLE scm1.t1 (id INT);      -- 创建模式下的表
GRANT ALL ON scm1.t1 TO 'user1';    -- 授权用户对模式中的表操作权限

七、新增AFTER命令

新增AFTER DDL触发器(Trigger)可在DDL语句执行后触发,主要用于审计和日志记录。


CREATE TRIGGER trig1 
AFTER CREATE ON SCHEMA db1  --  在db1模式执行CREATE语句后触发
FOR EACH STATEMENT
BEGIN
  INSERT INTO logs VALUES (NOW());  -- 向logs表插入一条记录 
END;

CREATE TABLE db1.t1; – 执行一条CREATE语句,会触发AFTER触发器插入一条日志。

八、新增许多系统变量和状态变量

用于确定和控制 MySQL 的许多方面行为。主要包括:

audit_log_xxx:控制审计日志行为的相关变量。
default_password_lifetime:控制密码过期时间的变量。
validate_password_xxx:控制密码策略相关变量。
innodb_xxx:控制InnoDB存储引擎行为的众多变量。如innodb_file_per_table控制表空间等。
sql_require_primary_key:强制要求生成的表必须有主键。
log_throttle_xxx:控制日志记录频率的相关变量。示例:


SET GLOBAL audit_log_enabled=ON;   -- 启用审计日志
SET GLOBAL validate_password_length=10;  -- 设置密码最小长度为10
SET GLOBAL innodb_file_per_table=ON;   -- 启用文件每个表一个表空间
SET GLOBAL sql_require_primary_key=ON;  -- 要求表必须有主键

九、MySQL 5.7提供了CONTROL插件,用于限制连接数或特定主机的最大连接数。

可用于保护MySQL免受拒绝服务攻击或限制资源的连接。安装插件:


INSTALL PLUGIN conn_control SONAME 'conn_control.so';

示例限制root用户最大10个连接及来自主机192.168.0.1的连接数至多3个:


SELECT * FROM mysql.conn_control WHERE USER='root' AND HOST='localhost';
UPDATE mysql.conn_control SET MAX_CONNECTIONS=10 WHERE USER='root' AND HOST='localhost';

SELECT * FROM mysql.conn_control WHERE HOST='192.168.0.1'; 
INSERT INTO mysql.conn_control (HOST,MAX_CONNECTIONS) VALUES('192.168.0.1',3);

十、资源组新增资源组功能

可用于将MySQL资源(如CPU,内存)划分为不同组,并控制每个组可使用的资源量。可增加QoS,为不同的工作负载或用户提供不同级别的服务。创建资源组:


CREATE RESOURCE GROUP rg1 TYPE = USER  
                       VCPU = 2     -- 最多使用2个CPU 
                       MEMORY_LIMIT = 1073741824; -- 最大内存1GB

将用户添加到资源组:


GRANT USAGE ON *.* TO 'user1'@'localhost' REQUIRE RESOURCE GROUP rg1;

user1用户连接时,会被限定在rg1资源组中,最大可使用2个CPU和1GB内存。

十一、支持两类生成列（generated column）

MySQL 5.7 支持虚拟生成列和存储生成列。虚拟生成列仅将数据保存在表的元数据中，作为缺省的生成列类型；存储生成列则是将数据永久保存在磁盘上，需要更多的磁盘空间。

综上

MySQL 5.7中新增特性涉及较广,主要在提高安全性、优化性能、增强GIS及模式控制等方面。熟练掌握这些新特性,可更高效地使用和管理MySQL 5.7数据库。