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

一、InnoDB变更

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

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

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

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

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

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

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

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

二、安全性提升

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

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

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

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

3. 自定义test数据库

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

4. 默认SSL加密

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

5. 密码过期策略

   MySQL 5.7 支持用户设置密码过期策略，要求用户在一定时间过后必须修改密码。

6. 审计日志:新增审计日志(Audit Log)功能,可记录数据库访问和操作的审计跟踪信息。

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

三、性能优化

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

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

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

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

3. 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查询使用->

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

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

四、GIS及空间函数增强

1. 新的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; -- 找出两条线的交点

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

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

3. 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模式及用户权限控制增强

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

GRANT SELECT (id, name) ON db1.t1 TO 'user1'@'localhost';  
-- 仅授权用户查询t1表的id和name字段REVOKE INSERT ON db1.t1 FROM 'user1'@'localhost'; 
-- 回收user1用户对db1.t1表的插入权限  

2. 全面支持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
BEGININSERT INTO logs VALUES (NOW());  -- 向logs表插入一条记录 
END;  

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

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

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

1. audit_log_xxx:控制审计日志行为的相关变量。

2. default_password_lifetime:控制密码过期时间的变量。

3. validate_password_xxx:控制密码策略相关变量。

4. innodb_xxx:控制InnoDB存储引擎行为的众多变量。如innodb_file_per_table控制表空间等。

5. sql_require_primary_key:强制要求生成的表必须有主键。

6. 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数据库。