选择MySQL 8的背景:MySQL 5.6已经停止版本更新了,对于 MySQL 5.7 版本,其将于 2023年 10月31日 停止支持。后续官方将不再进行后续的代码维护。 另外,MySQL 8.0 全内存访问可以轻易跑到 200W QPS,I/O 极端高负载场景跑到 16W QPS,如下图:
MySQL 8.0 Sysbench 基准测试:IO Bound Read Only
MySQL 8.0 Sysbench 基准测试:读写
MySQL 8.0 Sysbench Benchmark:双写入缓冲区,IO Bound Read Write
上面三个图来自于MySQL官网:https://www.mysql.com/why-mysql/benchmarks/mysql/ 除了高性能之外,MySQL 8还新增了很多功能,我找了几个比较有特点的新特性,在这里总结一下。 本文使用的MySQL版本为
8.0.29
在MySQL之前的版本,创建用户和给创建的用户授权可以一条语句执行完成:
grant all privileges on *.* to 'zhangsan'@'%' identified by 'Fawai@kuangtu6';
MySQL 5.7创建用户及授权
在MySQL 8中,创建用户和授权需要分开执行,否则会报错,执行不成功:
MySQL 8执行结果
在 MySQL 8
中,需要分2步完成创建用户和授权的操作:
-- 创建用户
create user 'zhangsan'@'%' identified by 'Fawai@kuangtu6';
-- 授权
grant all privileges on *.* to 'zhangsan'@'%';
在执行创建用户时,出现了如下错误:
root密码没有修改执行一些语句会报错
这是因为我的 MySQL 8
安装完成后,进入命令行用的还是临时密码,并未修改root的初始密码,需要修改密码才允许操作。
修改密码操作:
-- 修改root密码
alter user user() identified by 'Root@001';
再创建用户即可:
mysql> create user 'zhangsan'@'%' identified by 'Fawai@kuangtu6';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'zhangsan'@'%';
Query OK, 0 rows affected (0.00 sec)
在MySQL中,可以用 show variables
命令查看一些设置的MySQL变量,其中密码认证插件的变量名称是 default_authentication_plugin
。
「MySQL 5.7版本」 :
mysql> show variables like '%default_authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)
「MySQL 8版本」 :
mysql> show variables like '%default_authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.07 sec)
可以看出,5.7
版本的默认认证插件是 mysql_native_password
, 而 8.0
版本的默认认证插件是 caching_sha2_password
。
caching_sha2_password
这个认证插件带来的问题是,我们直接在客户端连接MySQL会连不上,比如用Navicat
:
认证插件不允许客户端连接
我们可以临时修改一下认证插件为 mysql_native_password
,再看一下是否能连接上,修改命令为:
mysql> alter user 'zhangsan'@'%' identified with mysql_native_password by 'Fawai@kuangtu6';
Navicat客户端连接MySQL 8
此时,我们来看一下 user
表中的插件信息:
认证插件信息
zhangsan
用户的认证插件改为了mysql_native_password
,而其他的认证插件仍为默认的 caching_sha2_password
。
当然,alter user
修改插件的方式只能作为临时修改,而要永久修改,则需要修改MySQL配置文件 /etc/my.cnf
中的配置:
认证插件默认配置
然后重启MySQL服务即可。
「MySQL 8」增加了密码管理功能,开始允许限制重复使用以前的密码:
MySQL不同版本密码管理比较
这里有几个属性,其中:
修改 「password_history」 全局策略:
-- 修改密码不能和最近2次一致
set persist password_history=2;
而如果要修改用户级别的 「password_history」 ,命令为:
alter user 'zhangsan'@'%' password history 2;
下面来修改一下密码试试。
-- zhangsan的原密码是Fawai@kuangtu6,执行修改密码操作,仍修改密码为Fawai@kuangtu6,根据密码策略不允许与最近2次的密码相同,应该修改不成功
alter user 'zhangsan'@'%' identified by 'Fawai@kuangtu6';
密码策略生效
如果把全局参数 「password_history」 改为0,则对于「root」用户就没有此限制了:
「MySQL 8」 对索引也有相应的增强,增加了方便测试的 「隐藏索引」 ,真正的 「降序索引」 ,还增加了 「函数索引」。
MySQL 8开始支持隐藏索引 「(invisible index」),也叫不可见索引。隐藏索引不会被优化器使用,但仍然需要进行维护-创建、删除等。其常见应用场景有:「软删除」、「灰度发布」。
有了 「隐藏索引」 ,大大方便了我们做测试,可以说是非常的体贴了!
下面举个例子看看隐藏索引怎么用法。
创建一个表 「t_test」 ,并创建一个正常的索引 idx_name
,一个隐藏索引 idx_age
:
create table t_test(id int, name varchar(20), age int);
create index idx_name on t_test(name);
create index idx_age on t_test(age) invisible;
此时,看一下索引信息:
mysql> show index from t_test\G
*************************** 1. row ***************************
Table: t_test
Non_unique: 1
Key_name: idx_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t_test
Non_unique: 1
Key_name: idx_age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: NO
Expression: NULL
2 rows in set (0.01 sec)
普通索引的 「Visible」 属性值为「OFF」,隐藏索引为「ON」。
再来看一下MySQL优化器怎么处理这两种索引的:
隐藏索引
可以看到,隐藏索引在查询的时候并不会用到,就跟没有这个索引一样,那么 「隐藏索引」 的用处到底是个什么玩意呢?
这里可以通过优化器的开关--optimizer_switch
,
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
看到 「use_invisible_indexes」 配置默认是 「OFF」 的,将其打开看看效果:
-- 在会话级别设置查询优化器可以看到隐藏索引
set session optimizer_switch="use_invisible_indexes=on";
再来看一下隐藏索引 「idx_age」 是否生效:
使隐藏索引生效
「666!!!」
这样的话就方便我们项目做灰度发布了,项目上线前,我想测试一下添加的新索引是否有用,可以先将其设置为隐藏索引,这样不会影响线上业务,在会话级别将隐藏索引打开进行测试,发现没有问题后转为可见索引。
可见索引与隐藏索引转换的SQL语句:
-- 转换成可见索引
alter table t_test alter index idx_age visible;
-- 转换成隐藏索引
alter table t_test alter index idx_age invisible;
MySQL 8支持 「降序索引」 :DESC
在索引中定义不再被忽略,而是导致键值以降序存储。
以前,可以以相反的顺序扫描索引,但会降低性能。降序索引可以按正序扫描,效率更高。
当最有效的扫描顺序混合了某些列的升序和其他列的降序时,降序索引还使优化器可以使用多列索引。
举个例子,在 「MySQL 8」 和 「MySQL 5.7」 中均执行如下建表语句:
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
然后看一下表的索引信息:
image-20220620165303728
具体的用处在哪里呢?插入一些数据看一下。
insert into t(c1, c2) values (1, 10),(2, 20),(3, 30),(4, 40),(5, 50);
MySQL 8中降序索引的使用
在之前的MySQL版本中,查询时对索引进行函数操作,则该索引不生效,基于此,MySQL 8中引入了 「函数索引」 。
还是举个简单的例子看一下:创建一个表t2,字段c1上建普通索引,字段c2上建upper函数(将字母转成大写的函数)索引。
create table t2(c1 varchar(10), c2 varchar(10));
create index idx_c1 on t2(c1);
create index idx_c2 on t2((upper(c2)));
创建函数索引
通过show index from t2\G
看一下:
普通索引和函数索引
下面来分别查询一下,看看索引的使用情况:
函数索引的使用
由于c1字段上是普通索引,使用upper(c1)
查询时并没有用到索引优化,而c2字段上有函数索引upper(c2)
,可以把整个upper(c2)
看成是一个索引字段,查询时索引生效了!
「函数索引的实现原理:」
函数索引在MySQL中相当于新增了一个列,这个列会根据函数来进行计算结果,然后使用函数索引的时候就会用这个计算后的列作为索引,其实就是增加了一个虚拟的列,然后根据虚拟的列进行查询,从而达到利用索引的目的。
MySQL 8.0 支持原子数据定义语言 (DDL) 语句。此功能称为原子 DDL。原子 DDL 语句将与 DDL 操作关联的数据字典更新、存储引擎操作和二进制日志写入组合到单个原子操作中。
操作要么被提交,适用的更改被持久化到数据字典、存储引擎和二进制日志中,要么被回滚,即使服务器在操作期间停止。
举个简单的例子:数据库中有表t1,没有表t2,执行语句删除t1和t2。
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
上面是在 「MySQL 8」 中的操作,可以看到该操作并没有删除掉表t1,那么在之前的版本呢,下面在 「MySQL 5.7」 版本中进行同样的操作:
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
Empty set (0.00 sec)
虽然也有报错提示说t2表不存在,但是t1表是真实的被删除掉了!
「TIPS」:如果确需要执行drop表操作,请使用 if exists
来防止删除不存在的表时出现的错误。
一个原子 DDL 操作内容包括:
支持与表相关的 DDL:
Common Table Expressions(CTE)通用表达式,也就是「MySQL 8」中的 「with」 语句。
通过一个简单的例子了解一下。
idx展示1~10行,可以直接select 1 union select 2 ...select 10这样:
select 1 as idx
UNION
select 2 as idx
UNION
select 3 as idx
UNION
select 4 as idx
UNION
select 5 as idx
UNION
select 6 as idx
UNION
select 7 as idx
UNION
select 8 as idx
UNION
select 9 as idx
UNION
select 10 as idx;
通过CTE表达式,可以用递归的方式简化为如下写法:
with recursive cte(idx) as
(
select 1
UNION
select idx+1 from cte where idx<10
)
select * from cte;
再比如,有这样一个场景,查看某个员工的上下级关系,就可以通过CTE递归查出来。
这里 「https://dev.mysql.com/doc/refman/8.0/en/with.html」 有更多比较好的例子,大家可以看一下。
「MySQL 8」 还有很多比较实用的新特性,比如 :
「Window Function」,对于查询中的每一行,使用与该行相关的行执行计算。
「JSON增强」
「InnoDB 其他改进功能」 ,比如死锁检查控制 innodb_deadlock_detect
,对于高并发的系统,禁用死锁检查可能带来性能的提高。
这里不多做举例了(有没有一种可能是作者太懒?),官方文档上面那是相当的详细!