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语法为一个表或者多个表建立公用表空间。
