MySQL优化命令汇总

1、查看当前服务器配置的最多连接数

-- 查看当前服务器配置的最多连接数
show variables like 'max_connections'; 

#设置方法：
set GLOBAL max_connections=10000; 

2、查看当前已使用最大连接数

SHOW GLOBAL STATUS LIKE '%max_used_connections%';

3、查看最大连接数，应该是与上面查询到的连接数相同，才会出现too many connections的情况

show global variables like 'wait_timeout';

#设置方法：
set global wait_timeout=300; 

show global variables like 'interactive_timeout';

#设置方法：
set global interactive_timeout=500;

4、查看连接数，可以发现有很多连接处于sleep状态，这些其实是暂时没有用的，所以可以kill掉。查看进程状态，通过此命令可以查看哪些sql在等待锁

show FULL processlist;

5、kill之前没用的sleep连接

 kill id;

6、查看正在被锁定的的表

show status like 'Table%';
-- Table_locks_immediate  指的是能够立即获得表级锁的次数
-- Table_locks_waited  指的是不能立即获取表级锁而需要等待的次数
-- Table_locks_waited/Table_locks_immediate=0.3%  如果这个比值比较大的话，说明表锁造成的阻塞比较严重 

show OPEN TABLES where In_use > 0;
-- 查看哪些表在使用中，In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。

show status like '%lock%';
-- 如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。
-- Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。

7、QPS(每秒Query量) QPS = Questions(or Queries) / seconds 

show  global  status like 'Question%';

8、TPS(每秒事务量) 

show global status like 'Com_commit'; 
show global status like 'Com_rollback'; 

9、查看当前服务器最大打开文件数

SHOW VARIABLES LIKE 'open%';
-- Open_files系统当前打开的文件数
-- Opened_files系统打开过的文件总数
show global status like 'open%file%';

10、修复mysql服务最大打开文件数

#查看linux系统最大打开文件数限制：
ulimit -n
65535

#设置65535最大打开文件数，配置文件/etc/security/limit.conf，追加：
*           soft   nofile        65535
*           hard   nofile        65535


#MySQL服务配置文件/lib/systemd/system/mysqld.service，最后追加：
LimitNOFILE=65535
LimitMEMLOCK=65535

#然后执行以下命令重启mysql

systemctl daemon-reload

systemctl restart  mysqld.service