中牟建设工程信息网站,网站建设先进城市,在线搜索引擎,36kr源码WordPress性能翻倍
账户与安全
用户的创建和授权 在MySQL之前的版本#xff0c;创建用户和给创建的用户授权可以一条语句执行完成#xff1a;
grant all privileges on *.* to test% identified by suibowenkuangtu6;在MySQL 8中#xff0c;创建用户和授权需要分开执行#xff0c…性能翻倍
账户与安全
用户的创建和授权 在MySQL之前的版本创建用户和给创建的用户授权可以一条语句执行完成
grant all privileges on *.* to test% identified by suibowenkuangtu6;在MySQL 8中创建用户和授权需要分开执行否则会报错执行不成功
-- 创建用户
create user test% identified by suibowenkuangtu6;
-- 授权
grant all privileges on *.* to test%;认证插件 5.7 版本的默认认证插件是 mysql_native_password 而 8.0 版本的默认认证插件是 caching_sha2_password 。 caching_sha2_password 这个认证插件带来的问题是我们直接在客户端连接MySQL会连不上比如用Navicat 我们可以临时修改一下认证插件为 mysql_native_password 再看一下是否能连接上修改命令为
alter user zhangsan% identified with mysql_native_password by Fawaikuangtu6;在线修改的系统参数支持持久化
在之前的版本中通过set、set global的形式修改某个系统变量时这种方式设置的参数值都是一次性的也就是修改过的参数并不会被同步到本地当MySQL重启时这些调整过的参数又会回归默认值如果想要让调整过的参数生效就必须要手动停止MySQL然后去修改my.ini/my.conf文件修改完成后再重启数据库服务这时才能让参数永久生效。
这种方式无疑是十分痛苦的尤其是在做数据库线上调优时修改参数后重启又会失效有时重启忘记再次调整参数最终导致数据库服务出现问题这种体验令人很糟心。
而在MySQL8.0中则彻底优化了这个问题推出了在线修改参数后支持持久化到本地文件的机制也就是通过SET PERSIST命令来完成如下
-- 调整事务的隔离级别针对于当前连接有效
set transaction isolation level read uncommitted;-- 调整事务的隔离级别针对于全局有效重启后会丢失
set global tx_isolation read-committed;-- 调整事务的隔离级别针对于全局有效并且会持久化到本地重启后不会丢失
set persist global.tx_isolation repeatable-read;
通过set persist命令持久化的参数可以通过下述命令来查看
select * from performance_schema.persisted_variables;其实参数持久化的原理也非常简单当执行set persist命令时会将改变过的参数写入到本地的mysqld-auto.cnf文件中MySQL每次启动时都会读取这个文件中的值如果该文件中存在参数则会直接将其加载从而实现了一次修改永久有效。
移除查询缓存
Query Cahce查询缓存的设计初衷很好也就是利用热点探测技术对于一些频繁执行的查询SQL直接将结果缓存在内存中之后再次来查询相同数据时就无需走磁盘而是直接从查询缓存中获取数据并返回。
select * from zz_users where user_id1;
select * from zz_users where user_id 1;比如上述这两条SQL语句在我们看来是不是一样的确实都是在查询ID1的用户数据但奇葩的事情出现了MySQL的查询缓存会把它当做两条不同的SQL也就是假如上面的第一条SQL其查询结果被放入到了缓存中第二条SQL依旧无法命中这个缓存会继续走表查询的形式获取数据Why 因为MySQL查询缓存是以SQL的哈希值来作为Key的上面两条SQL虽然一样但是后面的查询条件有细微差别user_id1、user_id 1也就是一条SQL有空格一条没有。 由于这一点点细微差异会导致两条SQL计算出的哈希值完全不同因此无法命中缓存是不是很鸡肋还有多种情况user_id 1、user_id 1空格处于的前后位置不同也会导致缓存失效。 也正是由于方方面面的原因所以查询缓存在MySQL8.0中被完全舍弃了即移除掉了查询缓存区各方面原因如下
①缓存命中率低几乎大部分SQL都无法从查询缓存中获得数据。②占用内存高将大量查询结果放入到内存中会占用至少几百MB的内存。③增加查询步骤查询表之前会先查一次缓存查询后会将结果放入缓存额外多几步开销。④缓存维护成本不小需要LRU算法淘汰缓存同时每次更新、插入、删除数据时都要清空缓存中对应的数据。⑤查询缓存是专门为MyISAM引擎设计的而InnoDB构建的缓冲区完全具备查询缓存的作用。
因为上述一系列原因再加上项目中一般都会使用Redis先做业务缓存因此能来到MySQL的查询语句几乎都是要从表中读数据的所以查询缓存的地位就显得更加突兀所以在8.0版本中就直接去掉了毕竟弊大于利带来的收益达不到设计时的预期。
锁机制优化
在MySQL8.0中的锁机制主要出现了两点优化一方面对获取共享锁的写法进行了优化如下
-- MySQL8.0之前的版本
SELECT ... LOCK IN SHARE MODE;-- MySQL8.0及后续的版本
SELECT ... FOR SHARE;第二方面则支持非阻塞式获取锁机制可以在获取锁的写法上加上NOWAIT、SKIP LOCKED关键字这样在未获取到锁时不会阻塞等待使用SKIP LOCKED未获取到锁时会直接返回空使用NOWAIT会直接返回并向客户端返回异常。用法如下
select ... for update nowait;
select ... for update skip locked;索引增强
在8.0中官方再一次对索引机制动刀首先对联合索引提供了一种跳跃扫描机制的支持也就意味着使用联合索引时就算未遵循最左前缀匹配原则也可以使用联合索引来检索数据。除此之外还有另外三种新的索引特性隐藏索引、降序索引以及函数索引。 索引跳跃式扫描机制Index Skip Scan 最左前缀匹配原则也就是SQL的查询条件中必须要包含联合索引的第一个字段这样才能命中联合索引查询但实际上这条规则也并不是100%遵循的。因为在MySQL8.0版本中加入了一个新的优化机制也就是索引跳跃式扫描这种机制使得咱们即使查询条件中没有使用联合索引的第一个字段也依旧可以使用联合索引看起来就像跳过了联合索引中的第一个字段一样这也是跳跃扫描的名称由来。 比如此时通过(A、B、C)三个列建立了一个联合索引此时有如下一条SQL
SELECT * FROM tb_xx WHERE B xxx AND C xxx;这条SQL既不符合最左前缀原则也不具备使用索引覆盖的条件因此绝对是不会走联合索引查询的但思考一个问题这条SQL中都已经使用了联合索引中的两个字段结果还不能使用索引这似乎有点亏啊对不因此MySQL8.x推出了跳跃扫描机制但跳跃扫描并不是真正的“跳过了”第一个字段而是优化器为你重构了SQL比如上述这条SQL则会重构成如下情况
SELECT * FROM tb_xx WHERE B xxx AND C xxx
UNION ALL
SELECT * FROM tb_xx WHERE B xxx AND C xxx AND A yyy
......
SELECT * FROM tb_xx WHERE B xxx AND C xxx AND A zzz;其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重然后基于去重后的值全部拼接起来查一遍 但是跳跃扫描机制也有很多限制比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发…总之有很多限制条件具体的可以参考《MySQL官网8.0》。 对于索引跳跃扫描机制可以通过set optimizer_switch ‘skip_scanoff|on’;命令来选择开启或关闭跳跃式扫描机制。 隐藏索引 隐藏索引并不是一种新的索引类型而是一种对索引的骚操作可以理解为对每个索引新增了一个开关按键主要用于测试环境和灰度场景在MySQL8.0版本中可以通过INVISIBLE、VISIBLE来控制索引的开关
当对一个索引使用INVISIBLE后会关闭这个索引优化器在执行SQL时无法发现和使用它。当对一个索引使用VISIBLE后会将索引从隐藏状态恢复到正常状态。
所谓的隐藏索引就是指将一个已经创建的索引“藏起来”被藏起来的索引是无法被优化器探测到的因此执行SQL语句时就算语句中显式使用了索引字段优化器也不会选择走这条索引。 这个特性主要是针对于调优、测试场景而研发的如果隐藏一个索引后在压测场景下不会对业务产生影响如果经过反复测试后依旧不影响SQL性能那这条索引则可以被判定为无用索引可以将其删除 降序索引 在创建索引时可以通过ASC、DESC来定义一个索引是按升序还是降序存储索引键但本质上这种语法在MySQL8.0之前就算你手动写明了DESC降序在创建时依旧会默认忽略也就是本质上还是按升序存储索引键的当你要对某个倒序索引的字段做倒序时依旧会发生filesort排序的动作。 到了MySQL8.0官方正式支持降序索引也就是当对一个字段建立降序索引后做降序查询时不需要再次排序可直接根据索引进行取值。
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);函数索引 在MySQL8.0中真正的支持了函数索引也就是基于函数去创建索引如下
alter table 表名 add index 索引名(函数(列名));-- 比如创建一个将字段值全部转为大写后的索引
alter table t1 add index fuc_upper(upper(c1));基于某个字段创建一个函数索引后之后基于该字段使用函数作为查询条件时依旧可以走索引如下
select * from t1 where upper(c1) ABC;不过有一点需要牢记使用什么函数创建的索引也仅支持相应函数走索引比如上面通过了upper()函数创建了一个索引因此upper(c1) ABC’这种情况可以走索引但使用其他函数时依旧会导致索引失效如lower(c1) ‘abc’
通用表表达式
CTE通用表表达式究竟是用来干什么事情的呢CTE是一个具备变量名的临时结果集也就是可以将一条查询语句的结果保存到一个变量里面后续在其他语句中允许直接通过变量名来使用该结果集语法如下
with CTE名称
as (查询语句/子查询语句)
select 语句;上述的语法是一个普通的CTE用法同时还有另一种递归的CTE用法先举个简单的例子来认识一下最基本的用法
-- MySQL8.0版本之前的子查询语句
select * from t1 where xx in (select xx from t2 where yy zzz);-- MySQL8.0中使用CTE表达式来代替
with cte_query as(select xx from t2 where yy zzz)select * from t1 join cte_query on t1.xx cte_query.xx;观察上述例子原本语句中需要使用in来对子查询的多个结果集做匹配使用CTE后可以将子查询的结果集保存在cte_query变量中后续的语句中可以将其当作成一张表然后来做连接查询。 其实看到这里CTE表达式是不是有些类似于临时表的概念但它会比临时表更轻查询更快。 CTE表达式除开可以与select语句嵌套外还可以与其它类型的语句嵌套例如with delete、with update、with recursive、with with、insert with等
窗口函数
窗口函数可谓是MySQL8.0中最大的亮点之一但在尝试去学习时会发现很难理解先来看看窗口函数的定义。 窗口函数是一种分析型的OLAP函数因此也被称之为分析函数它可以理解成是数据的集合类似于group by分组的功能但之前的MySQL版本基于某个字段分组后会将数据压缩到一行显示如下
select * from zz_users;
-------------------------------------------------------------
| user_id | user_name | user_sex | password | register_time |
-------------------------------------------------------------
| 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 |
| 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 |
| 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 |
| 4 | 猫熊 | 女 | 8888 | 2022-09-17 23:48:29 |
-------------------------------------------------------------select user_id from zz_users group by user_sex;
-----------
| user_id |
-----------
| 1,4 |
| 2,3 |
-----------而窗口函数则不会将数据压缩成一行也就是表中数据原本是多少行分组完成后依旧是多少行窗口函数的语法如下
[window 窗口函数名 as (window_spec) [, 窗口函数名 AS (window_spec)] ...]窗口函数名(窗口名/表达式)
over ([partition_defintion][order_definition][frame_definition]
)
一眼看下来结果还是令人不理解对吗这先别急看不懂也没关系后面会举例说明先来看看MySQL8.0中提供了哪些窗口函数呢如下 序号函数 row_number()按序排列相同的值序号会往后推如88、88、89排序为1、2、3。 rank()并列排序相同的值序号会跳过如88、88、89排序为1、1、3。 dense_rank()并列排序相同的值序号不会跳过如88、88、89排序为1、1、2。 分布函数 percent_rank()计算当前行数据的某个字段值占窗口内某个字段所有值的百分比。 cume_dist() 小于等于当前字段值的行数与整个分组内所有行数据的占比。 前后函数 lag(expr,n)返回分组中的前n条符合expr条件的数据。 lead(expr,n)返回分组中的后n条符合expr条件的数据。 首尾函数 first_value(expr)返回分组中的第一条符合expr条件的数据。 last_value(expr)返回分组中的最后一条符合expr条件的数据。 其它函数 nth_value(expr,n)返回分组中的第n条符合expr条件的数据。 ntile(n)将一个分组中的数据再分成n个小组并记录每个小组编号。
这样看过去似乎也有些令人迷糊毕竟之前对窗口函数则这块接触比较少因此下面来举个简单的例子切身感受一下还是以之前的用户表为例需求如下
按性别分组并按照ID值从大到小对各分组中的数据进行排序最后输出。
这需求一听就知道一条SQL绝对搞不定在之前版本中需要创建临时表来实现借助临时表来拆成多步完成而在MySQL8.0中则可以借助窗口函数轻松实现如下
select -- 使用 row_number() 序号窗口函数row_number() over(-- 基于性别做分组然后基于 ID 做倒序partition by user_sex order by user_id desc) as serial_num,user_id, user_name, user_sex, password, register_time
fromzz_users;-------------------------------------------------------------------------
| serial_num | user_id | user_name | user_sex | password | register_time |
-------------------------------------------------------------------------
| 1 | 4 | 猫熊 | 女 | 8888 | 2022-09-17 23:48:29 |
| 2 | 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 |
| 1 | 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 |
| 2 | 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 |
-------------------------------------------------------------------------上述这条SQL就是基于序号窗口函数的实现其实发现会尤为简单观察执行结果也会发现使用窗口函数分组后并不会将数据压缩到一行而是将同一分组的数据在结果集中相邻显示
其他特性
在前面的内容中就已经将MySQL8.0中较为重要的变更和特性做了详细阐述但MySQL8.0整体的改变也比较大因此这里再列出一些其它方面的特性如下
将默认的UTF-8编码格式从latin替换成了utf8mb4后者包含了所有emoji表情包字符。增强NoSQL存储功能优化了5.6版本引入的NoSQL技术并完善了对JSON的支持性。InnoDB引擎再次增强对自增、索引、加密、死锁、共享锁等方面做了大量改进与优化。支持定义原子DDL语句即当需要对库表结构发生变更时变更操作可定义为原子性操作。支持正则检索新增REGEXP_LIKE()、EGEXP_INSTR()、REGEXP_REPLACE()、REGEXP_SUBSTR()等函数提供支持。优化临时表临时表默认引擎从Memory替换为TempTable引擎资源开销少性能更强。锁机制增强除开前面聊到的锁特性变更外新引入了一种备份锁获取/释放锁语法如下获取锁LOCK INSTANCE FOR BACKUP、释放锁UNLOCK INSTANCE
更多请参考《MySQL官网-8.0版手册》
