MySQL全局优化与Mysql 8.0新增特性

小超同学卷起来

已于 2023-07-01 01:26:52 修改

阅读量180

点赞数

分类专栏： MySQL专题文章标签： mysql 数据库

于 2023-06-30 23:28:16 首次发布

本文链接：https://blog.csdn.net/qq_27740127/article/details/131463519

版权

MySQL专题专栏收录该内容

8 篇文章 1 订阅

订阅专栏

一、MySQL 全局优化总结

在这里插入图片描述
从上图可以看出SQL及索引的优化效果是最好的，而且成本最低，所以工作中我们要在这块花更多时间。

补充一点配置文件my.ini或my.cnf的全局参数：
假设服务器配置为：
CPU：32核
内存：64G
DISK：2T SSD
下面参数都是服务端参数，默认在配置文件的 [mysqld] 标签下

max_connections=3000
连接的创建和销毁都需要系统资源，比如内存、文件句柄，业务说的支持多少并发，指的是每秒请求数，也就是QPS。
一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。
如果3000个用户同时连上mysql，最小需要内存3000256KB=750M，最大需要内存300064MB=192G。
如果innodb_buffer_pool_size是40GB，给操作系统分配4G，给连接使用的最大内存不到20G，如果连接过多，使用的内存超过20G，将会产生磁盘SWAP，此时将会影响性能。连接数过高，不一定带来吞吐量的提高，而且可能占用更多的系统资源。
max_user_connections=2980
允许用户连接的最大数量，剩余连接数用作DBA管理。
back_log=300
MySQL能够暂存的连接数量。如果MySQL的连接数达到max_connections时，新的请求将会被存在堆栈中，等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将被拒绝。
wait_timeout=300
指的是app应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
interactive_timeout=300
指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
innodb_thread_concurrency=64
此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能。
innodb_buffer_pool_size=40G
innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。
innodb_lock_wait_timeout=10
行锁锁定时间，默认50s，根据公司业务定，没有标准值。
innodb_flush_log_at_trx_commit=1
这个参数控制 redo log 的写入策略（参考我的上一篇文章）
sync_binlog=1
binlog写入磁盘机制（参考我的上一篇文章）
sort_buffer_size=4M
每个需要排序的线程分配该大小的一个缓冲区。增加该值可以加速ORDER BY 或 GROUP BY操作。
sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(4M)=2G。
join_buffer_size=4M
用于表关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

二、MySQL8.0 新特性之降序索引

建议使用8.0.17及之后的版本，更新的内容比较多。

新增降序索引

在这里插入图片描述
我们可以看到，建表的时候创建了c1，c2 字段的联合索引，c2 按照降序，但是查询的时候 Extra 还是用到了文件排序

在 MySQL8.0 下，同样的操作，我们发现降序索引是利用到了，Extra 是利用了索引排序

在这里插入图片描述
另外排序必须按照每个字段定义的排序或按相反顺序才能充分利用索引，联合索引是c1升,c2降，只有 c1 升，c2 降或者c1 降 c2 升才能利用索引排序，后面一个是反向扫描索引；但是其他情况就不会利用到索引排序

group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
在这里插入图片描述
我们可以看到 C2 字段默认排序了

为什么要去掉呢，是为了更好的兼容性，如果要迁移数据库用其他去数据库，那么 8.0 的版本迁移就不会出bug，反之 5.7 版本就会容易出问题

三、MySQL8.0 新特性之隐藏索引

使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见，但是数据库后台还是会维护隐藏索引的，在查询时优化器不使用该索引，即使用force index，优化器也不会使用该索引，同时优化器也不会报索引不存在的错误，因为索引仍然真实存在，必要时，也可以把隐藏索引快速恢复成可见。注意，主键不能设置为 invisible。
软删除就可以使用隐藏索引，比如我们觉得某个索引没用了，删除后发现这个索引在某些时候还是有用的，于是又得把这个索引加回来，如果表数据量很大的话，这种操作耗费时间是很多的，成本很高，这时，我们可以将索引先设置为隐藏索引，等到真的确认索引没用了再删除。

在这里插入图片描述

四、MySQL8.0 新特性之函数索引

之前我们知道，如果在查询中加入了函数，索引不生效，所以MySQL 8引入了函数索引，MySQL 8.0.13开始支持在索引中使用函数(表达式)的值。
原理：函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。
在这里插入图片描述

五、MySQL8.0 新特性之 Innobd 增强特性

innodb存储引擎select for update跳过锁等待

对于select … for share(8.0新增加查询共享锁的语法)或 select … for update，在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定。
在5.7及之前的版本，select…for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。
在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。
应用场景比如**查询余票记录**，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能。
在这里插入图片描述

新增innodb_dedicated_server自适应参数

在这里插入图片描述能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题，
前提是服务器是专用来给MySQL数据库的，如果还有其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序。

死锁检查控制

MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否执行 InnoDB 死锁检查，默认是打开的。死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。
在这里插入图片描述

undo文件不再使用系统表空间

默认创建2个UNDO表空间，不再使用系统表空间。
在这里插入图片描述

binlog日志过期时间精确到秒

之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

六、MySQL8.0 新特性之窗口函数

窗口函数(Window Functions)：也称分析函数
从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。
在这里插入图片描述

在这里插入图片描述

专用窗口函数：
序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
分布函数：PERCENT_RANK()、CUME_DIST()
前后函数：LAG()、LEAD()
头尾函数：FIRST_VALUE()、LAST_VALUE()
其它函数：NTH_VALUE()、NTILE()

在这里插入图片描述

七、MySQL8.0 新特性之其他重要特性

默认字符集由latin1变为utf8mb4

在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。

MyISAM系统表全部换成InnoDB表

将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表。

元数据存储变动

MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。可以看见下图test库文件夹里已经没有了frm文件。
在这里插入图片描述

自增变量持久化

在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重启后，该值将不会改变。
在这里插入图片描述

以下是8.0 版本

DDL原子化

InnoDB表的DDL支持事务完整性，要么成功要么回滚。
MySQL 8.0 开始支持原子 DDL 操作，其中与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子 DDL 操作内容包括：更新数据字典，存储引擎层的操作，在 binlog 中记录 DDL 操作。支持与表相关的 DDL：数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE。支持的其它 DDL ：存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER 语句。支持账户管理相关的 DDL：用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME等等。
在这里插入图片描述

在这里插入图片描述
因为 MyISAM系统表全部换成InnoDB表，支持事务了

参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效。
在这里插入图片描述

小超同学卷起来

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL全局优化与Mysql 8.0新增特性

从上图可以看出SQL及索引的优化效果是最好的，而且成本最低，所以工作中我们要在这块花更多时间。假设服务器配置为：CPU：32核内存：64G下面参数都是服务端参数，默认在配置文件的 [mysqld] 标签下=3000连接的创建和销毁都需要系统资源，比如内存、文件句柄，业务说的支持多少并发，指的是每秒请求数，也就是QPS。一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。
复制链接

扫一扫