Mysql全局优化以及Mysql8.0新增特性

有浊
836 阅读8分钟

Mysql全局优化总结

image.png 从上图可以看出在sql及索引层面进行优化，效率最高，且消耗的成本最少。 服务端参数层面： 下面参数都是服务端参数，默认在配置文件的 [mysqld] 标签下

max_connections=3000

连接的创建和销毁都需要系统资源，比如内存、文件句柄，业务说的支持多少并发，指的是每秒请求数，也就是QPS。

一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。

如果3000个用户同时连上mysql，最小需要内存3000 * 256KB=750M，最大需要内存3000*64MB=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_thread_concurrency = 64

此参数用来设置InnoDB线程的并发数，默认为0，表示不被限制，若要设置则与CPU的核心数相同或者是CPU核心数的两倍不会导致后端大量的并发过来导致

  • redo log的刷盘策略

innodb_flush_log_at_trx_commit= 1

  • binlog的刷盘策略

    sync_binlog = 1

redolog 和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一样，该参数对应的分配内存也是每个线程连接独享

除了上述的参数配置之外还会有很多的配置，比如连接数的配置，与系统的要求的并发有关，包括有多少台服务器,Tomcat、Nginx等服务器做一个综合的考虑。 max_connection=3000

Mysql8.0新特性

  1. 新增降序索引

只有InnoDB存储引擎支持降序索引，MySQL在语法上很早就已经支持降序索引，但实际上创建的仍然是升序索引,在Mysql 8中是支持降序索引的

  1. group by不再隐式排序

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

image.png

image.png 3. 增加隐藏索引

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

增加隐藏索引的作用是什么？

  1. 新增函数索引

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

  1. 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可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能。

  1. 死锁检查控制

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

image.png 7. undolog文件不再使用系统表空间

默认创建2个UNDO表空间，不再使用系统表空间。

  1. binlog日志过期时间精确到秒

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

  1. 新增窗口函数的概念
  • 从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。
  • 在聚合函数后面加上over()就变成分析函数了，后面可以不用再加group by制定分组，因为在over里已经用partition关键字指明了如何分组计算，这种可以保留原有表数据的结构，不会像分组聚合函数那样每组只返回一条数据

mysql> select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;

over()里如果不加条件，则默认使用整个表的数据做运算

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

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

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

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

  1. 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等等。

avatar
文章
阅读
粉丝