MySQL8.0新特性

1. 默认字符集由latin1变为utf8mb4

在8.0版本之前,默认字符集为latin1,utf8指向的是utf8mb3,8.0版本默认字符集为utf8mb4,utf8默认指向的也是utf8mb4。
注:在Percona Server 8.0.15版本上测试,utf8仍然指向的是utf8mb3,与官方文档有出入。

Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |

2. MyISAM系统表全部换成InnoDB表

系统表全部换成事务型的innodb表,默认的MySQL实例将不包含任何MyISAM表,除非手动创建MyISAM表。

# MySQL 5.7
mysql> select distinct(ENGINE) from information_schema.tables;
+--------------------+
| ENGINE             |
+--------------------+
| MEMORY             |
| InnoDB             |
| MyISAM             |
| CSV                |
| PERFORMANCE_SCHEMA |
| NULL               |
+--------------------+
6 rows in set (0.00 sec)
 
# MySQL 8.0
mysql> select distinct(ENGINE) from information_schema.tables;
+--------------------+
| ENGINE             |
+--------------------+
| NULL               |
| InnoDB             |
| CSV                |
| PERFORMANCE_SCHEMA |
+--------------------+
4 rows in set (0.00 sec)

3. 自增变量持久化

在8.0之前的版本,自增主键AUTO_INCREMENT的值如果大于max(primary key)+1,在MySQL重启后,会重置AUTO_INCREMENT=max(primary key)+1,这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199),一直到8.0才被解决,8.0版本将会对AUTO_INCREMENT值进行持久化,MySQL重启后,该值将不会改变。

4. DDL原子化

InnoDB表的DDL支持事务完整性,要么成功要么回滚,将DDL操作回滚日志写入到data dictionary 数据字典表 mysql.innodb_ddl_log 中用于回滚操作,该表是隐藏的表,通过show tables无法看到。通过设置参数,可将ddl操作日志打印输出到mysql错误日志中。

mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;
mysql> create table t1(c int) engine=innodb;
 
# MySQL错误日志:
2018-06-26T11:25:25.817245+08:00 44 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=41, thread_id=44, space_id=6, old_file_path=./db/t1.ibd]
2018-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 41
2018-06-26T11:25:25.819753+08:00 44 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=42, thread_id=44, table_id=1063, new_file_path=db/t1]
2018-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 42
2018-06-26T11:25:25.820556+08:00 44 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert : [DDL record: FREE, id=43, thread_id=44, space_id=6, index_id=140, page_no=4]
2018-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 43
2018-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 44
2018-06-26T11:25:25.825784+08:00 44 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 44

来看另外一个例子,库里只有一个t1表,drop table t1,t2; 试图删除t1,t2两张表,在5.7中,执行报错,但是t1表被删除,在8.0中执行报错,但是t1表没有被删除,证明了8.0 DDL操作的原子性,要么全部成功,要么回滚。

# MySQL 5.7
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 'db.t2'
mysql> show tables;
Empty set (0.00 sec)
 
# MySQL 8.0
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 'db.t2'
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

5. 参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化,通过加上PERSIST关键字,可以将修改的参数持久化到新的配置文件(mysqld-auto.cnf)中,重启MySQL时,可以从该配置文件获取到最新的配置参数。
例如执行:
set PERSIST expire_logs_days=10 ;
系统会在数据目录下生成一个包含json格式的 mysqld-auto.cnf 的文件,格式化后如下所示,当 my.cnf 和 mysqld-auto.cnf 同时存在时,后者具有更高优先级。

{
    "Version": 1,
    "mysql_server": {
        "expire_logs_days": {
            "Value": "10",
            "Metadata": {
                "Timestamp": 1529657078851627,
                "User": "root",
                "Host": "localhost"
            }
        }
    }
}

6. 新增降序索引

MySQL在语法上很早就已经支持降序索引,但实际上创建的仍然是升序索引,如下MySQL 5.7 所示,c2字段降序,但是从show create table看c2仍然是升序。8.0可以看到,c2字段降序。

# MySQL 5.7
mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.03 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
 
# MySQL 8.0
mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.06 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

再来看看降序索引在执行计划中的表现,在t1表插入10万条随机数据,查看select * from t1 order by c1 , c2 desc;的执行计划。从执行计划上可以看出,5.7的扫描数100113远远大于8.0的5行,并且使用了filesort。

DELIMITER ;;
CREATE PROCEDURE test_insert ()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<100000
DO
insert into t1 select rand()*100000, rand()*100000;
SET i=i+1;
END WHILE ;
commit;
END;;
DELIMITER ;
CALL test_insert();
 
# MySQL 5.7
mysql> explain select * from t1 order by c1 , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100113 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
 
# MySQL 8.0
mysql> explain select * from t1 order by c1 , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

降序索引只是对查询中特定的排序顺序有效,如果使用不当,反而查询效率更低,比如上述查询排序条件改为 order by c1 desc, c2 desc,这种情况下,5.7的执行计划要明显好于8.0的,如下:

# MySQL 5.7
mysql> explain select * from t1  order by c1 desc , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
 
# MySQL 8.0
mysql> explain select * from t1 order by c1 desc , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100429 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

7. group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序,如需要排序,必须显式加上order by 子句。

# 表结构
mysql> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `group_own` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
 
# 表数据
mysql> select * from tb1;
+----+------+-----------+
| id | name | group_own |
+----+------+-----------+
|  1 | 1    |         0 |
|  2 | 2    |         0 |
|  3 | 3    |         0 |
|  4 | 4    |         0 |
|  5 | 5    |         5 |
|  8 | 8    |         1 |
| 10 | 10   |         5 |
+----+------+-----------+
7 rows in set (0.00 sec)
 
# MySQL 5.7
mysql> select count(id), group_own from tb1 group by group_own;
+-----------+-----------+
| count(id) | group_own |
+-----------+-----------+
|         4 |         0 |
|         1 |         1 |
|         2 |         5 |
+-----------+-----------+
3 rows in set (0.00 sec)
 
# MySQL 8.0.11
mysql> select count(id), group_own from tb1 group by group_own;
+-----------+-----------+
| count(id) | group_own |
+-----------+-----------+
|         4 |         0 |
|         2 |         5 |
|         1 |         1 |
+-----------+-----------+
3 rows in set (0.00 sec)
 
# MySQL 8.0.11显式地加上order by进行排序
mysql> select count(id), group_own from tb1 group by group_own order by group_own;
+-----------+-----------+
| count(id) | group_own |
+-----------+-----------+
|         4 |         0 |
|         1 |         1 |
|         2 |         5 |
+-----------+-----------+
3 rows in set (0.00 sec)

8. JSON特性增强

MySQL 8 大幅改进了对 JSON 的支持,添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数,以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

在主从复制中,新增参数 binlog_row_value_options,控制JSON数据的传输方式,允许对于Json类型部分修改,在binlog中只记录修改的部分,减少json大数据在只有少量修改的情况下,对资源的占用。

9. redo & undo 日志加密

增加以下两个参数,用于控制redo、undo日志的加密。
innodb_undo_log_encrypt
innodb_undo_log_encrypt

10. innodb select for update跳过锁等待

select … for update,select … for share(8.0新增语法) 添加 NOWAIT、SKIP LOCKED语法,跳过锁等待,或者跳过锁定。
在5.7及之前的版本,select…for update,如果获取不到锁,会一直等待,直到innodb_lock_wait_timeout超时。

在8.0版本,通过添加nowait,skip locked语法,能够立即返回。如果查询的行已经加锁,那么nowait会立即报错返回,而skip locked也会立即返回,只是返回的结果中不包含被锁定的行。

# session1:
mysql> begin;
mysql> select * from t1 where c1 = 2 for update;
+------+-------+
| c1   | c2    |
+------+-------+
|    2 | 60530 |
|    2 | 24678 |
+------+-------+
2 rows in set (0.00 sec)
 
# session2:
mysql> select * from t1 where c1 = 2 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired  immediately and NOWAIT is set.
mysql> select * from t1 where c1 = 2 for update skip locked;
Empty set (0.00 sec)

11. 增加SET_VAR语法

在sql语法中增加SET_VAR语法,动态调整部分参数,有利于提升语句性能。

  • select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;
  • insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

12. 支持不可见索引

使用INVISIBLE关键字在创建表或者进行表变更中设置索引是否可见。索引不可见只是在查询时优化器不使用该索引,即使使用force index,优化器也不会使用该索引,同时优化器也不会报索引不存在的错误,因为索引仍然真实存在,在必要时,也可以快速的恢复成可见。

# 创建不可见索引
create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc) invisible );
# 索引可见
alter table t2 alter index idx_c1_c2 visible;
# 索引不可见
alter table t2 alter index idx_c1_c2 invisible;

13. 支持直方图

优化器会利用column_statistics的数据,判断字段的值的分布,得到更准确的执行计划。

可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。

直方图统计了表中某些字段的数据分布情况,为优化选择高效的执行计划提供参考,直方图与索引有着本质的区别,维护一个索引有代价。每一次的insert、update、delete都会需要更新索引,会对性能有一定的影响。而直方图一次创建永不更新,除非明确去更新它。所以不会影响insert、update、delete的性能。

# 添加/更新直方图
mysql> analyze table t1 update histogram on c1, c2 with 32 buckets;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| db.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
| db.t1 | histogram | status   | Histogram statistics created for column 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2 rows in set (2.57 sec)
 
# 删除直方图
mysql> analyze table t1 drop histogram on c1, c2;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| db.t1 | histogram | status   | Histogram statistics removed for column 'c1'. |
| db.t1 | histogram | status   | Histogram statistics removed for column 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2 rows in set (0.13 sec)

14. 新增innodb_dedicated_server参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_method三个参数。

15. 日志分类更详细

在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]

# MySQL 5.7
2018-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-08T09:07:20.117868+08:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
 
 
# MySQL 8.0
2018-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv'  entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-06-21T17:53:13.040520+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv'  entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-21T17:53:13.040542+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv'  entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

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权限才能创建、修改、删除资源组。
在Linux环境下,MySQL进程需要有 CAP_SYS_NICE 权限才能使用资源组完整功能。

[root@localhost~]# sudo setcap cap_sys_nice+ep /usr/local/mysql8.0/bin/mysqld
[root@localhost~]# getcap /usr/local/mysql8.0/bin/mysqld
/usr/local/mysql8.0/bin/mysqld = cap_sys_nice+ep

默认提供两个资源组,分别是USR_default,SYS_default

创建资源组:
create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
将当前线程加入资源组:
SET RESOURCE GROUP test_resouce_group;
将某个线程加入资源组:
SET RESOURCE GROUP test_resouce_group FOR thread_id;
查看资源组里有哪些线程:
select * from Performance_Schema.threads where RESOURCE_GROUP=’test_resouce_group';
修改资源组:
alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;
删除资源组 :
drop resource group test_resouce_group;

# 创建资源组
mysql>create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS |  THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default         | USER                |                      1 | 0-3      |                0 |
| SYS_default         | SYSTEM              |                      1 | 0-3      |                0 |
| test_resouce_group  | USER                |                      1 | 0-1      |                5 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)

# 把线程id为60的线程加入到资源组test_resouce_group中,线程id可通过Performance_Schema.threads获取
mysql> SET RESOURCE GROUP test_resouce_group FOR 60;
Query OK, 0 rows affected (0.00 sec)

# 资源组里有线程时,删除资源组报错
mysql> drop resource group test_resouce_group;
ERROR 3656 (HY000): Resource group test_resouce_group is busy.

# 修改资源组
mysql> alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default         | USER                |                      1 | 0-3      |               0 |
| SYS_default         | SYSTEM              |                      1 | 0-3      |               0 |
| test_resouce_group  | USER                |                      1 | 2-3      |               8 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)

# 把资源组里的线程移出到默认资源组USR_default
mysql> SET RESOURCE GROUP USR_default FOR 60;
Query OK, 0 rows affected (0.00 sec)

# 删除资源组
mysql> drop resource group test_resouce_group;
Query OK, 0 rows affected (0.04 sec)

18. 增加角色管理

角色可以认为是一些权限的集合,为用户赋予统一的角色,权限的修改直接通过角色来进行,无需为每个用户单独授权。

# 创建角色
mysql> create role role_test;
Query OK, 0 rows affected (0.03 sec)
 
# 给角色授予权限
mysql> grant select on db.* to 'role_test';
Query OK, 0 rows affected (0.10 sec)
 
# 创建用户
mysql> create user 'read_user'@'%' identified by '123456';
Query OK, 0 rows affected (0.09 sec)
 
# 给用户赋予角色
mysql> grant 'role_test' to 'read_user'@'%';
Query OK, 0 rows affected (0.02 sec)
 
# 给角色role_test增加insert权限
mysql> grant insert on db.* to 'role_test';
Query OK, 0 rows affected (0.08 sec)
 
# 给角色role_test删除insert权限
mysql> revoke insert on db.* from 'role_test';
Query OK, 0 rows affected (0.10 sec)
 
# 查看默认角色信息
mysql> select * from mysql.default_roles;
+------+-----------+-------------------+-------------------+
| HOST | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-----------+-------------------+-------------------+
| %    | read_user | %                 | role_test         |
+------+-----------+-------------------+-------------------+
1 row in set (0.00 sec)
 
# 查看角色与用户关系
mysql> select * from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------+---------+-----------+-------------------+
| %         | role_test | %       | read_user | N                 |
+-----------+-----------+---------+-----------+-------------------+
1 row in set (0.00 sec)
 
# 删除角色
mysql> drop role role_test;
Query OK, 0 rows affected (0.06 sec)

mysql utf8_unicode_ci与utf8_general_ci的区别

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快,但准确度稍差。
utf8_unicode_ci 准确度高,但校对速度稍慢。

如果你的应用有德语、法语或者俄语,请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。
附:

ci是 case insensitive, 即 “大小写不敏感”, a 和 A 会在字符判断中会被当做一样的;
bin 是二进制, a 和 A 会别区别对待。

例如你运行:
SELECT * FROM table WHERE txt = ‘a’
那么在utf8_bin中你就找不到 txt = ‘A’ 的那一行, 而 utf8_general_ci 则可以。

utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会照成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容

spring-cloud搭建ribbon(负载均衡)

Spring cloud配置Ribbon用于负载君和,如果要在细节上配置更多,那么需要阅读一下官方文档。

* RoundRobinRule:轮询
* RandomRule:随机
* AvailabilityFilteringRule: 会先过滤掉由于多次访问故障而处于断路器跳闸状态的服务,以及并发的连接数量
*      超过阈值的服务,然后对剩余的服务列表按照轮询策略进行访问;
* WeightedResponseTimeRule: 根据平均响应时间计算所有服务的权重,响应时间越快,服务权重越大,被选中的机率越高;
*      刚启动时,如果统计信息不足,则使用RoundRobinRule策略,等统计信息足够时,会切换到WeightedResponseTimeRule
* RetryRule: 先按照RoundRobinRule的策略获取服务,如果获取服务失败,则在指定时间内会进行重试,获取可用的服务;
* BestAvailableRule: 会先过滤掉由于多次访问故障而处于断路器跳闸状态的服务,然后选择一个并发量最小的服务;
* ZoneAvoidanceRule: 默认规则,复合判断server所在区域的性能和server的可用性选择服务器;

首先maven文件:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.test</groupId>
    <artifactId>router</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>router</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.M3</spring-cloud.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-ribbon</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>

</project>

配置文件:

server:
  address: 127.0.0.1
  port: 7001
  undertow:
    io-threads: 19 # 设置IO线程数, 它主要执行非阻塞的任务,它们会负责多个连接, 默认设置每个CPU核心一个线程
    worker-threads: 24 # 阻塞任务线程池, 当执行类似servlet请求阻塞操作, undertow会从这个线程池中取得线程,它的值设置取决于系统的负载
    buffer-size: 1024 # 以下的配置会影响buffer,这些buffer会用于服务器连接的IO操作,有点类似netty的池化内存管理
    direct-buffers: true # 是否分配的直接内存
    always-set-keep-alive: true #socket-binding="http",保持长连接

spring:
  application:
    name: router-server
  profiles:
    active: test
  cloud:
    loadbalancer:
      retry:
        enabled: true #重试机制开启为true,关闭为false

eureka:
  instance:
    appname: router-server
    virtual-host-name: router-server
    secure-virtual-host-name: router-server
    instance-id: ${server.address}:router-server-peer:${server.port}
    hostname: localhost
#    non-secure-port: 6001 #非安全通信端口
#    non-secure-port-enabled: true #是否启用非安全端口接受请求
#    secure-port: 6444 #安全通信端口
#    secure-port-enabled: true #是否启用安全端口接受请求
    prefer-ip-address: false #是否优先使用IP地址作为主机名的标识,默认false
    lease-renewal-interval-in-seconds: 30 #eureka节点定时续约时间,默认30
    lease-expiration-duration-in-seconds: 90 #eureka节点剔除时间,默认90
  client:
    register-with-eureka: true #将自己注册到eureka注册中心,单节点关闭
    fetch-registry: true #从注册中心获取注册信息,单节点关闭
    registry-fetch-interval-seconds: 30 #从注册中心获取注册信息的时间间隔
    eureka-server-connect-timeout-seconds: 15
    eureka-server-read-timeout-seconds: 10
    service-url:
      defaultZone: http://${eureka.instance.hostname}:7000/eureka/ #注册到另外两个节点,实现集群

ribbon:
  eureka:
    enabled: true

注解开启服务:

@EnableEurekaClient
@SpringBootApplication
public class RouterApplication {

    public static void main(String[] args) {
        SpringApplication.run(RouterApplication.class, args);
    }

}

写一个RestTemplate服务层,用于公共接口转发:

package com.test.router.system.service;

import com.test.router.system.core.BaseService;
import com.test.router.system.core.ResultResp;
import com.test.router.system.service.resources.IAPIDispatcherService;
import org.springframework.http.*;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestTemplate;

import javax.annotation.Resource;
import java.util.Map;

@Service
public class APIDispatcherServiceImpl extends BaseService implements IAPIDispatcherService {

    @Resource
    private RestTemplate restTemplate;


    @Override
    public ResultResp<Map<String, Object>> getForObject(String name, Object... uriVariables) {
        return restTemplate.getForObject("http://api-server/" + name, ResultResp.class);
    }

    @Override
    public ResultResp<Map<String, Object>> postForObject(String name, Map<String, Object> body) {
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        HttpEntity<Map<String, Object>> data = new HttpEntity<>(body,headers);

        ResponseEntity<ResultResp> resp = restTemplate.postForEntity("http://api-server/" + name , data, ResultResp.class);
        return resp.getBody();
    }

    @Override
    public ResultResp<Map<String, Object>> putForObject(String name, Integer id, Map<String, Object> body) {
        HttpEntity<Map<String, Object>> data = new HttpEntity<>(body);
        ResponseEntity<ResultResp> resp = restTemplate.exchange("http://api-server/" + name + "/{1}", HttpMethod.PUT, data, ResultResp.class, id);
        return resp.getBody();
    }

    @Override
    public ResultResp<Map<String, Object>> deleteForObject(String name, Integer id) {
        ResponseEntity<ResultResp> resp = restTemplate.exchange("http://api-server/" + name + "/{1}", HttpMethod.PUT, null, ResultResp.class, id);
        return resp.getBody();
    }
}

最后需要一个控制器,用于转发使用:

package com.test.router.app.v1;

import com.test.router.app.base.BaseV1Controller;
import com.test.router.constant.Constant;
import com.test.router.system.base.IDefaultRestController;
import com.test.router.system.core.ResultResp;
import com.test.router.system.model.http.Request;
import com.test.router.system.service.resources.IAPIDispatcherService;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Map;

@RestController
public class TestController extends BaseV1Controller implements IDefaultRestController {


    @Resource
    private IAPIDispatcherService dispatcherService;

    /**
     * http://127.0.0.1:7001/v1/router?name=v2/test&namespace=api&user=avc
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/router", method = RequestMethod.GET)
    @Override
    public ResultResp<Map<String, Object>> doGet(HttpServletRequest request, HttpServletResponse response) {
        String namespace = request.getParameter("namespace");
        String name = request.getParameter("name");

        if (Constant.NAMESPACE_API.equals(namespace)) {
            Map<String, String> args = this.initUriVariables(name, request);
            StringBuffer sb = new StringBuffer();
            if (name.lastIndexOf("?") == -1) {
                name += "?";
            }
            sb.append(name);
            args.forEach((k, v) -> {
                sb.append(k).append("=").append(v).append("&");
            });
            return dispatcherService.getForObject(sb.toString(), null);
        }

        return new ResultResp<>();
    }

    /**
     * http://127.0.0.1:7001/v1/router?user=avc
     *
     *
     * {
     *     "header":{
     *         "namespace":"api",
     *         "name":"v2/test"
     *     },
     *     "payload":{
     *         "username":"张三",
     *         "age":15
     *     }
     * }
     * @param body
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/router", method = RequestMethod.POST)
    @Override
    public ResultResp<Map<String, Object>> doPost(@RequestBody Request<Map<String, Object>> body, HttpServletRequest request, HttpServletResponse response) {
        String namespace = body.getHeader().getNamespace();
        String name = body.getHeader().getName();

        if (Constant.NAMESPACE_API.equals(namespace)) {
            Map<String, String> args = this.initUriVariables(name, request);
            StringBuffer sb = new StringBuffer();
            if (name.lastIndexOf("?") == -1) {
                name += "?";
            }
            sb.append(name);
            args.forEach((k, v) -> {
                sb.append(k).append("=").append(v).append("&");
            });
            return dispatcherService.postForObject(sb.toString(), body.getPayload());
        }

        return new ResultResp<>();
    }

    @Override
    public ResultResp<Map<String, Object>> doPut(@PathVariable Integer id, @RequestBody Request<Map<String, Object>> obj, HttpServletRequest request, HttpServletResponse response) {
        return null;
    }

    @Override
    public ResultResp<Map<String, Object>> doDelete(@PathVariable Integer id, HttpServletRequest request, HttpServletResponse response) {
        return null;
    }
}

这样子基本上完成,主要使用浏览器打开http://127.0.0.1:7001/v1/router?name=v2/test&namespace=api&user=avc,就会根据name对应的值去获取api-service的对应的接口。

更多请参阅:

https://cloud.spring.io/spring-cloud-static/spring-cloud-netflix/2.2.0.RC1/reference/html/#spring-cloud-ribbon

spring-cloud搭建EurekaServer

Spring cloud配置EurekaServer很简单,如果要在细节上配置更多,那么需要阅读一下官方文档。

首先是maven:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.test</groupId>
    <artifactId>server</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>server</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.M3</spring-cloud.version>
    </properties>

    <dependencies>
        <!--        <dependency>-->
        <!--            <groupId>org.springframework.cloud</groupId>-->
        <!--            <artifactId>spring-cloud-bus</artifactId>-->
        <!--        </dependency>-->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-server</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-ribbon</artifactId>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>

</project>

其次就是配置:

server:
  address: 127.0.0.1
  port: 7000
  undertow:
    io-threads: 19
    worker-threads: 24
    buffer-size: 1024
    direct-buffers: true
    always-set-keep-alive: true

spring:
  application:
    name: eureka-server
  profiles:
    active: test


eureka:
  instance:
    appname: eureka-server
    virtual-host-name: eureka-server
    secure-virtual-host-name: eureka-server
#    non-secure-port: 6000
#    non-secure-port-enabled: true
#    secure-port: 6443
#    secure-port-enabled: true
    lease-renewal-interval-in-seconds: 15
    lease-expiration-duration-in-seconds: 45
    instance-id: ${server.address}:eureka-server-peer:${server.port}
    hostname: localhost
  client:
    register-with-eureka: true
    fetch-registry: true
    registry-fetch-interval-seconds: 30
    service-url:
      defaultZone: http://${eureka.instance.hostname}:${server.port}/eureka/
  server:
    enable-self-preservation: false
    eviction-interval-timer-in-ms: 1000

最后就是注解开启:

package com.test.server;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.netflix.eureka.server.EnableEurekaServer;


@EnableEurekaServer
@SpringBootApplication
public class ServerApplication {

    public static void main(String[] args) {
        SpringApplication.run(ServerApplication.class, args);
    }

}

使用浏览器打开http://127.0.0.1:7000就可以看到效果了。

 

spring cloud服务发现注解@EnableDiscoveryClient与@EnableEurekaClient

使用服务发现的时候提到了两种注解,一种为@EnableDiscoveryClient,一种为@EnableEurekaClient,用法上基本一致,今天就来讲下两者,下文是从stackoverflow上面找到的对这两者的解释:

There are multiple implementations of “Discovery Service” (eureka, consul, zookeeper).
@EnableDiscoveryClient lives in spring-cloud-commons and picks the implementation on the classpath.
@EnableEurekaClient lives in spring-cloud-netflix and only works for eureka. If eureka is on your classpath, they are effectively the same.

意思也就是spring cloud中discovery service有许多种实现(eureka、consul、zookeeper等等),@EnableDiscoveryClient基于spring-cloud-commons, @EnableEurekaClient基于spring-cloud-netflix。

其实用更简单的话来说,就是如果选用的注册中心是eureka,那么就推荐@EnableEurekaClient,如果是其他的注册中心,那么推荐使用@EnableDiscoveryClient。

注解@EnableEurekaClient上有@EnableDiscoveryClient注解,可以说基本就是EnableEurekaClient有@EnableDiscoveryClient的功能,

另外上面的注释中提到,其实**@EnableEurekaClient**z注解就是一种方便使用eureka的注解而已,可以说使用其他的注册中心后,

都可以使用@EnableDiscoveryClient注解,但是使用@EnableEurekaClient的情景,就是在服务采用eureka作为注册中心的时候,使用场景较为单一。

@EnableEurekaClient
我们具体的对@EnableEurekaClient的源码看一下,如下:

/**
* Convenience annotation for clients to enable Eureka discovery configuration
* (specifically). Use this (optionally) in case you want discovery and know for sure that
* it is Eureka you want. All it does is turn on discovery and let the autoconfiguration
* find the eureka classes if they are available (i.e. you need Eureka on the classpath as
* well).
*
* @author Dave Syer
* @author Spencer Gibb
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
@EnableDiscoveryClient
public @interface EnableEurekaClient {

}

123498