Mysql常用操作以及优化方案

常用操作

查看空闲连接

# Command 列显示为“Sleep”的这一行，表示现在系统里面有一个空闲连接
show processlist
#户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout 控制的，默认值是 8 小时。

常见优化配置

1、使用长连接

Mysql建立连接的过程通常是比较复杂的，建议使用中要尽量减少建立连接的动作，也就是尽量使用长连接。
问题：
但是全部使用长连接后，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
解决方案：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2、innodb_flush_log_at_trx_commit

这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

3、sync_binlog

这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

4、transaction-isolation

将启动参数 transaction-isolation 的值设置成 READ-COMMITTED
可以用该命令查看当前隔离级别

show variables like 'transaction_isolation'

5、autocommit=1

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。而且并不会自动提交，所以建议设置为autocommit=1
查找持续时间超过 60s的长事务sql语句

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

6、MAX_EXECUTION_TIME

通过设置改命令，来控制每个语句执行的最长时间(毫秒)

(1) 全局设置 SET GLOBAL MAX_EXECUTION_TIME=1000; 
(2) 对某个session设置 SET SESSION MAX_EXECUTION_TIME=1000;

7、innodb_undo_tablespaces

设置改参数为2,当出现大事务导致回滚段过大，这样设置后清理起来更方便
innodb_undo_tablespaces是控制undo是否开启独立的表空间的参数 为0表示：undo使用系统表空间，即ibdata1
不为0表示：使用独立的表空间，一般名称为 undo001 undo002，存放地址的配置项为：innodb_undo_directory 一般innodb_undo_tablespaces 默认配置为0，innodb_undo_directory默认配置为当前数据目录