mysql调优(六)

Author： PrinceLei
发布时间：May 8, 2020
497views
One comment
5495 words
Categories：技术分享 mysql

# mysql调优(六) 
## mysql日志
mysql日志一般分为三种，redo log、undo log、bin log，其中redo与undo归属于innodb存储引擎，bin log归属于mysql server。
事务具有4个特征ACID，A表示原子性，通过undo log实现，I为隔离性，通过锁来实现，D为持久性，通过redo log实现，A、I、D共同保证C，即一致性。
![log buffer.png][1]

![redo commit.png][2]

当执行一个写操作，mysql会将记录放入log buffer中，log buffer再将其放入OS buffer，最后OS buffer通过fsync系统调用将其溢写到磁盘中。
该过程有三种实现方式(如图)，可以通过参数来配置,其中第2种最安全，但是效率低，建议使用第三种。

redo日志
- 当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录操作到磁盘中
- redo log是固定大小的，是循环写的过程
- 有了redo log之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，这叫做crash-safe

undo日志
- undo log是为了实现事务的原子性，在mysql数据库InnoDB存储引擎中，还用undo log来实现多版本并发控制(简称: MVCC)
- 在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方称为undo log)。然后进行数据修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态
- 注意: undo log是逻辑日志，可以理解为:
    - 当delete一条记录时，undo log中会记录一条对应的insert记录
    - 当insert一条记录时，undo log中会记录一条对应的delete记录
    - 当update一条记录时，undo log中会记录一条对应的相反的update记录

bin log——服务端的日志文件
- bin log是server层的日志，主要做mysql功能层面的事情
- 与redo log的区别:
    1. redo是innodb独有的，binlog是所有引擎都可以使用的
    2. redo是物理日志，记录的是在某个数据页上做了什么修改，bin log是逻辑日志，记录的是这个语句的原始逻辑
    3. redo是循环写的，空间会用完，bin log是可以追加写的，不会覆盖之前的日志信息
- bin log中会记录所有的逻辑，并且采用追加写的方式
- 一般在企业中数据库会有备份系统，可以定期执行备份，备份的周期可以自己设置
- 恢复数据的过程:
    1. 找到最近一次的全量备份数据
    2. 从备份的时间点开始，将备份的bin log取出来，重放到要恢复的那个时刻

数据更新的流程:
1. 执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
2. 执行器拿到数据之后会先修改数据，然后调用引擎接口重新写入数据
3. 引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
4. 执行器生成这个操作的bin log
5. 执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成

redo log的两阶段提交
- 先写redo log后写bin log: 假设在redo log写完，bin log还没有写完的时候，mysql进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于bin log没写完就crash了，这时候bin log里面就没有记录这个语句。因此，之后备份日志的时候，存起来的bin log里面就没有这条语句。然后你会发现，如果需要用这个bin log来恢复临时库的话，恢复出来的这一行c的值就是0，与原库的值不同
- 先写bin log后写redo log: 如果在bin log写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0.但是bin log里面已经记录了“把c从0改成1”这个日志。所以，在之后用bin log来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同
因此，需要prepare和commit进行两阶段提交，确保redo log与bin log内容一致
![redo_log两阶段提交.jpg][3]

prepare: redolog写入log buffer，并fsync持久化到磁盘，在redolog事务中记录2PC的XID，在redolog事务打上prepare标识
commit: binlog写入log buffer，并fsync持久化到磁盘，在binlog事务中记录2PX的XID，同时在redolog事务打上commit标识
其中，prepare和commit阶段所提到的“事务”，都是指内部XA事务，即2PC。
## mysql服务器参数设置
### general
`datadir=/var/lib/mysql`数据文件存放的目录
`socket=/var/lib/mysql/mysql.sock`mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
`pid_file=/var/lib/mysql/mysql.pid`存储mysql的pid
`port=3306`mysql服务的端口号
`default_storage_engine=InnoDB`mysql默认存储引擎
`skip-grant-tables`当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql
### character
`character_set_client`客户端数据的字符集
`character_set_connection`mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式
`character_set_results`mysql发送给客户端的结果集所用的字符集
`character_set_database`数据库默认的字符集
`character_set_server`mysql server的默认字符集
### connection
`max_connections`mysql的最大连接数，如果数据库的并发连接请求比较大，应该调高该值
`max_user_connections`限制每个用户的连接个数
`back_log`mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源
`wait_timeout`mysql在关闭一个非交互的连接之前需要等待的时长
`interactive_timeout`关闭一个交互连接之前需要等待的秒数
### log
`log_error`指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息
`log_bin`指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句bin log
`binlog_do_db`指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中
`binlog_ignore_db`指定不将更新记录到二进制日志的数据库
`sync_binlog`指定多少次写日志后同步磁盘
`general_log`是否开启查询日志记录
`general_log_file`指定查询日志文件名，用于记录所有的查询语句
`slow_query_log`是否开启慢查询日志记录
`slow_query_log_file`指定慢查询日志文件名称，用于记录耗时比较长的查询语句
`long_query_time`设置慢查询的时间，超过这个时间的查询语句才会记录日志
`log_slow_admin_statements`是否将管理语句写入慢查询日志
### cache
`key_buffer_size`索引缓存区的大小（只对myisam表起作用）
#### query cache
`query_cache_size`查询缓存的大小，未来版本被删除`show status like '%Qcache%';`查看缓存的相关属性
- Qcache_free_blocks：缓存中相邻内存块的个数，如果值比较大，那么查询缓存中碎片比较多
- Qcache_free_memory：查询缓存中剩余的内存大小
- Qcache_hits：表示有多少此命中缓存
- Qcache_inserts：表示多少次未命中而插入
- Qcache_lowmen_prunes：多少条query因为内存不足而被移除cache
- Qcache_queries_in_cache：当前cache中缓存的query数量
- Qcache_total_blocks：当前cache中block的数量

`query_cache_limit`超出此大小的查询将不被缓存
`query_cache_min_res_unit`缓存块最小大小
`query_cache_type`缓存类型，决定缓存什么样的查询
- 0表示禁用
- 1表示将缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存
- 2表示只缓存select语句中通过sql_cache指定需要缓存的查询

`sort_buffer_size`每个需要排序的线程分派该大小的缓冲区
`max_allowed_packet=32M`限制server接受的数据包大小
`join_buffer_size=2M`表示关联缓存的大小
`thread_cache_size`服务器线程缓存，这个值表示可以重新利用保存再缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个线程将被重新请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值即可
- Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程
- Threads_connected：代表当前已建立连接的数量
- Threads_created：代表最近一次服务启动，已创建线程的数量，如果该值比较大，那么服务器会一直再创建线程
- Threads_running：代表当前激活的线程数

### INNODB
`innodb_buffer_pool_size=`该参数指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的80%
`innodb_flush_log_at_trx_commit`主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2(上图中写入磁盘的三种方式)
`innodb_thread_concurrency`设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
`innodb_log_buffer_size`此参数确定日志文件所用的内存大小，以M为单位
`innodb_log_file_size`此参数确定数据日志文件的大小，以M为单位
`innodb_log_files_in_group`以循环方式将日志文件写到多个文件中
`read_buffer_size`mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
`read_rnd_buffer_size`mysql随机读的缓冲区大小
`innodb_file_per_table`此参数确定为每张表分配一个新的文件

[1]: https://www.princelei.club/usr/uploads/2020/05/3167443884.png
  [2]: https://www.princelei.club/usr/uploads/2020/05/1269254309.png
  [3]: https://www.princelei.club/usr/uploads/2020/06/3436111291.jpg

Last modification：June 21st, 2020 at 10:28 am