mysql5.7新特性探究

一、MySql5.7增加的特性

1、MySql服务方面新特性

1） 初始化方式改变

• MySql5.7之前版本初始化方式：

scripts/mysql_install_db

• MySql5.7版本初始化方式：

[root@darren mysql_new]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql_new/ --datadir=/usr/local/mysql_new/data/

2015-10-10T06:19:06.463455Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-10-10T06:19:10.230200Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-10-10T06:19:10.766701Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-10-10T06:19:10.972179Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d21c9212-6f16-11e5-8cb9-000c296da6f8.
2015-10-10T06:19:10.979028Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-10-10T06:19:10.982274Z 1 [Note] A temporary password is generated for root@localhost: u=SwU?ClN9fR

初始化完成后为root@localhost用户提供一个默认密码，5.7版本之前密码是空的，还是为安全考虑。

第一次进入mysql命令行，强制修改密码，否则你无法进行任何操作：

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> select user,host,password from mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> create table t1(id int auto_increment not null,c1 int ,c2 int ,c3 int primary key(id));
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> create database tt;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by "root";
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec）

mysql5.7版本中还去掉了test库。

2） MySql5.7版本支持为表添加计算列

所谓计算列，就是通过其他列计算得到的值。

• 5.7之前版本实现计算列一般通过触发器实现。如下：

mysql> create trigger insr_tig before insert on t1 for each row set new.c3=new.c1+new.c2;
Query OK, 0 rows affected (0.09 sec)

mysql> create trigger upd_tig before update on t1 for each row set new.c3=new.c1+new.c2;
Query OK, 0 rows affected (0.01 sec)

mysql> show triggers;
+----------+--------+-------+--------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger  | Event  | Table | Statement                | Timing | Created                | sql_mode                                                                          | Definer        | character_set_client | collation_connection | Database Collation |
+----------+--------+-------+--------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| insr_tig | INSERT | t1    | set new.c3=new.c1+new.c2 | BEFORE | 2015-10-10 14:45:31.28 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| upd_tig  | UPDATE | t1    | set new.c3=new.c1+new.c2 | BEFORE | 2015-10-10 14:45:55.33 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+--------+-------+--------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

mysql> insert into t1(c1,c2) values(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> update t1 set c1=5 where id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    5 |    2 |    7 |
+----+------+------+------+
1 row in set (0.00 sec)

• 5.7版本实现计算列只需要在建表/修改表时添加列的as参数就可以了，如下：

mysql> create table t2(id int auto_increment not null,c1 int ,c2 int ,c3 int as(c1+c2),primary key(id));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2(c1,c2) values(2,3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |    5 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> update t2 set c1=10 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |   10 |    3 |   13 |
+----+------+------+------+
1 row in set (0.00 sec)

3） 引入JSON列数据类型及相关函数

• 5.7版本之前，只能在varchar或是text等字符类型的列中存储json类型的字符串，并通过程序解析使用json字符串。
• 5.7版本：增加了json列类型以及json_开头的函数，如json_type(),json_object(),json_merge()等

 

mysql> show create table json_test;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                          |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_test | CREATE TABLE `json_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jos` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_object("key1",1,"key2",2);
+--------------------------------+
| json_object("key1",1,"key2",2) |
+--------------------------------+
| {"key1": 1, "key2": 2}         |
+--------------------------------+
1 row in set (0.00 sec)

mysql> insert into json_test(jos) values(json_object("key1",1,"key2",2));
Query OK, 1 row affected (0.00 sec)

mysql> select * from json_test;
+----+------------------------+
| id | jos                    |
+----+------------------------+
|  1 | {"key1": 1, "key2": 2} |
+----+------------------------+
1 row in set (0.00 sec)

 

2、MySql5.7在复制中的新特性

1）支持多源复制

MySql5.7之前版本是不支持多源复制的，也就是多主一从，我们一般都是这样的架构形式：

但是MySql5.7版本为我们实现了多主一从的方式，这种方式也有很多用处，比如我们利用从库备份，以前都是一主一从，现在可以将多主复制到一从上，这样省了很多机器，节约了成本。

 

2）基于库或者逻辑锁的多线程复制

• Mysql5.7版本之前：

从5.6版本开始支持多线程复制，只不过是对于每个数据库一个复制线程，作用不是很大。

• MySql5.7版本：

增加了slave_parallel_type参数进行并发控制，用户可以选择database还是logical_lock，如果logical_lock实现了基于事务的多线程复制。

3）在线变更复制方式

• MySql5.7之前

要把基于日志点的复制方式变为基于GTID的复制方式或者把基于GTID的复制方式变为基于日志点的复制方式必须要重启master服务器。

• MySql5.7之后

可以在线变更，不需要重启服务器

3、MySql5.7在Innodb中的新特性

1）支持在线变更innodb_buffer_pool大小

• MySql5.7之前：

要变更innodb_buffer_pool大小必须更改my.cnf文件后重启数据库服务器方生效。

• MySql5.7之后：

变为动态参数，可以在线调整大小。

2）增加innodb_buffer_pool导入导出功能

• MySql5.7之后：

增加以下参数控制innodb_buffer_pool的导入导出

innodb_buffer_pool_dump_pct

innodb_buffer_pool_dump_now

innodb_buffer_pool_dump_at_shutdown

innodb_buffer_pool_load_at_startup

innodb_buffer_pool_load_now

3）支持为Innodb建立表空间

• MySql5.7之前：

具有系统共享表空间和为每个表建立的独立表空间

• MySql5.7之后：

支持create tablespace语法为一个表或者多个表建立公用表空间。