mysql5.7社区版性能优化浅谈

最大连接数

show variables like '%max_connections%';      最大连接数

 对于并发编程了解过的人知道，这个151不是越大越好，一般来说设置为 最大连接数 = cpu X 2 会比较合适， 因为受 IO 影响，连接数越大，反而越会占用IO，所以设置过大也可能导致性能降低。

 查询缓存

show variables like '%query_cache_type%';     server层默认关闭缓存，缓存默认值 1 MB

 mysql8中query缓存已被废除。原因如下：

1. 在每个缓存sql执行前，都要进入一个类似于synchronize同步锁，性能低。

2. 缓存的查询中对于缓存命中的要求非常严格，需严格匹配 sql 语句的大小写、一个字符一个字符匹配，逐字节完全匹配的。

3. 实际业务中，sql查询千变万化，缓存命中率低于98%，对于缓存来说，这个命中率是低的。

所以在使用过程中利大于弊的情况。

引擎选择

InnoDB引擎

innodb引擎的特点是处理大量的短事务。这也就是说我们使用该引擎就是希望尽可能的快，长事务是不适合innodb引擎的。

MyISAM是mysql5.1版本以前默认的存储引擎，它提供了全文检索、压缩、空间函数等等，它不像innodb是行锁，MyISAM是表锁，如果你有特殊需求，比如使用空间函数。

Perconna XtraDB引擎

Perconna Server 和 MariaDB用的都是这个XtraDB引擎，这个引擎是对于InnoDB的改进版本引擎，在管理性和性能方面都比InnoDB要强。

TokuDB引擎

怎么改进B+tree？分型树 数据结构，每个树节点都自带缓存机制，来提高读写效率。

TokuDB引擎进行了写优化，在写方面大大优于InnoDB, 而读方面是接近于InnoDB的。

启动 MySQL 服务器程序

在MNySQL 安装目录的 bin 目录下，有很多可执行文件。

mysqld

mysqld 这个可执行文件就代表着 MySOL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个命令不常用。

mysqld_safe

mysqld safe 是一个启动脚本，它会间接的调用 mysqld，而且还顺便启动了一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外,使用 mysqld_safe 启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因。

mysql.server

mysql.server     也是一个启动脚本，它会间接的调用 mysqld_safe.

mysql.server start      启动时添加参数start。该文件链接到support-files/mysql.server，间接的调用 mysqld_safe

mysql.server stop      停止

mysqld_multi

一台计算机上也可以运行多个服务器实例，也就是运行多个 NySQL服务器进程。可执行文件可以对每一个服务器进程的启动或停止进行监控。

参数的查看和修改
以参考 MySQL 官方手册：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
参数可以分为两类:动态(dynamic）参数和静态(static）参数，同时从作用范围又可以分为global全局变量和session会话变量。
动态参数意味着可以在 MySQL 实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改。
参数更改语句示例：
set global datadir='/usr/xxxxxx';             show variables like 'datadir';
set session system_var_name=xxx
set @@global.system_var_name=xxx
set @@session.system_var_name=xxx

show variables like 'log_error';                 error log file location, we can change it in "my.cnf" configuration file.

show variables like '%slow%';                  slow sql log file location.

MySQL 所有动态变量的可修改范围，可以参考 MySQL 官方手册的 Dynamic System Variables 的相关内容：
https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

show engines; 查看当前mysql server所有的存储引擎
show variables like '%storage_engine%'； 查看当前mysql 默认的存储引擎

日志文件
常见的日志文件有：错误日志（error log）、慢查询日志（slow query log）、
查询日志（query log）、二进制文件（bin log）。还有redo 日志、Undo 日志等等。
【错误日志】
错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录。当 MySQL 不能正常启动时，可以找该错误日志文件，该文件记录了错误信息。
show variables like 'log_error'; we can find the error log file location by this sql.
【慢查询日志】
慢查询日志可以帮助定位可能存在问题的 SQL 语句，从而进行 SQL 语句层面的优化。
【查询日志】
查询日志记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到了正确的执行。
默认文件名：主机名.log 从MySQL 5.1 开始，可以将查询日志的记录放入 mysql 架构下的 general_log表
【二进制日志（binlog）】
二进制日志记录了对 MySQL 数据库执行更改的所有操作。
二进制日志的几种作用：
恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制文件进行 point-in-time 的恢复
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或 standby）与一台 MySQL 数据库（一般称为 master 或 primary）进行实时同步
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击
log-bin 参数，默认为关闭，可以在 MySQL 的配置文件中指定如下的格式：
log-bin=name; "name"为二进制日志文件的名称(name 可省略)。默认的日志文件名：文件名为主机名，后缀名为二进制日志的序列号。且文件保存在数据库所在的datadir目录下
配置以后，就会在数据目录下产生类似于：
bin_log.00001 即为二进制日志文件；
bin_log.index 为二进制的索引文件；
二进制日志文件在默认情况下未开启，需要手动指定参数来启动。开启这个选项会对 MySQL 的性能下降 1%
【Canal log-bin】
ali 专门开源了这个技术，用于从mysql的bin log里读取数据到第三方媒介比如Elastic

 

 

 

 

end.