一、账户与安全
1、MySQL 8 性能好
可达到 200W QPS;
2、用户创建和授权:
5.7 一句话实现用户的 创建和授权 语句;
grant all privileges on *.* to 'allwe'@'%' identified by 'password';
8 分开执行;
create user 'allwe'@'%' identified by 'password'; //创建用户语句
grant all privileges on *.* to 'allwe'@'%' ; //授权语句
3、认证插件更新:
查询认证方式:select user,host,plugin from mysql.user;
5.7 mysql_native_password; //安全级别低
8 caching_sha2_password; //安全级别高
8 修改登录密码认证插件
查询插件 show variables like 'default_authentication';
1、修改插件 在my.cnf文件中修改配置(需要重启MySQL);
配置:default-authentication-plugin=mysql_native_password
2、直接修改用户密码的插件
命令:alter user 'allwe'@'%' identified with mysql_native_password by 'password';
4、密码管理:
5.7 不允许重复修改密码:123456 -> 123456(×);
8 允许重复修改密码;
开启限制后记录历史密码,限制最近使用的N次密码;
1、检查密码配置:show variables like 'password%';
结果:password_history = 3;//不允许使用最近的N次历史密码;
命令:修改【限制历史密码次数】:set persist password_history = 3;
2、直接修改用户密码的限制
命令:alter user 'allwe'@'%' password history 3;
查看配置:select user,host,password_reuse_history from mysql.user;
//也是不让使用最近的N次;
3、修改密码时是否需要输入旧密码:
结果:password_require_current = OFF;//默认不需要
4、不让使用几天内用过的密码:
结果:password_reuse_interval = 3;
二、索引增强(隐藏、降序、函数索引)
1、隐藏索引
特点:不可见,不会被优化器使用;
支持软删除:
删除、创建索引,都会消耗性能;
在mysql 8 中可以将索引进行软删除,暂时不使用索引;
隐藏索引可以删除和恢复;
支持灰度发布:
线上测试;
加索引会影响业务,加隐藏索引不会影响;
在测试环境,可以通过一些设置,查询优化器会使用这个索引,如果这个索引没问题,那就改成正式索引;
相关操作命令:
创建隐藏索引: create index j_idx on table(j) invisible;
检查索引:show index from table\G;
visible = NO;//不可见索引
检查是否使用隐藏索引:select @@optimizer_switch\G;
use_invisible_indexes = off; //是否使用隐藏索引
当前会话启用隐藏索引:set session optimizer_switch="use_invisible_indexes=on";
修改索引:alter table t1 alter index j_idx visible; //可见
2、降序索引
MySQL 8 -> innoDB支持降序索引;
创建方式:create table t2 (c1 int,c2 int ,index idx_1(c1 asc,c2 desc));
检查索引:show create table t2\G;
支持索引字段按倒序排序,联合索引支持每个字段都可以有自己的排序方式;
3、函数索引
老版本:使用group by 【字段】;会默认对字段做升序排序;
MySQL 8 不会自动排序;
UPPER函数:create index idx_c1 on table((UPPER(c1))); //对UPPER函数做个函数索引
检查 show index from from table\G;
Expressions = upper(c1);
查询语句中,可以对索引列使用对应的函数,不会让索引失效;
底层实现:新增了一个不可见列(函数 + 索引列),查询时用的索引列是这个不可见列;
三、通用表表达式
CTE:common table expression ;
相当于一个with 子句;
1、示例:查询上级层级关系(id,name,m_id(上级id))
with recursive staff_view(id,name,m_id) as
-- 查询到的数据都存到staff_view视图里面
(
-- 第一次进递归运行的SQL
select id,name,cast(id as char(200))
from staff where m_id = 0
union all
-- 第一次递归之后运行的SQL
select s2.id,s2.name,concat(s1.m_id,'-',s2.id)
from staff_view as s1 join staff as s2
on s2.id = s1.m_id
)
select * from staff_view order by id;
2、支持select、insert、update、delete;
四、窗口函数
特点:窗口函数与【分析、聚合、分组】类似
1、普通函数示例
-- sum,avg,count,max,min;
select
year,
country,
product,
sum,
-- 创建一个新的列,求每个国家商品数量总和
sum(sum) over (partition by country) as country_sum,
-- 加一列平均值
avg(sum) over (partition by country,product) as country_product_avg
from sales
order by XXX;
2、专用函数示例
-- 没写完
五、原子DDL操作、JSON增强及其他
1、DDL操作:drop、create 语句
老版本不支持原子操作,有的操作成功,有的失败;
MySQL 8 支持原子操作,要么都成功,要么都失败;
2、JSON增强
mongoDB noSQL数据 --> JSON
3、自增列持久化
解决长期使用自增列重复的风险;
4、死锁检查控制
增加了一个新的动态变量,用来控制是否启用死锁检查;
在高并发环境下,关闭死锁检查可能会提高性能;
innodb_deadlock_detect
5、锁定语句选项
在select ... for share 和 select ... for update 中支持两个参数:
nowait :如果请求的行被其他事务占用,那么语句立即返回;
skip locked:舍弃掉被占用的行数据,直接不返回;
6、innodb 其他功能改进
支持部分快速DDL,alter table algorithm=instant;
innob 临时表使用共享的临时表空间:ibtmp1;
默认创建2个UNDO表空间,不再使用系统表空间;
支持重命名表空间;