MYSQL性能分析

MYSQL性能分析

1.慢SQL查询
开启慢sql查询机制

set global slow_query_log=on
set global long_query_time=2;
set global log_queries_not_using_indexes=on

slow_query_log:慢查询log日志记录 on(开启)/off(关闭);
long_query_time:SQL语句执行时间超过2就被认为是慢查询语句;
log_queries_not_using_indexes:查询未使用索引是否开启记录慢查询日志 on(开启)/off(关闭);

查询慢sql开启的状态 可以查看到慢sql日志已开启 以及慢sql日志文件位置

show variables like '%slow%';

已开启
查看慢sql个数

show global status like '%slow%';

慢sql个数
优化策略:
参考慢sql个数,根据慢sql日志查看执行较慢的sql,针对慢sql进行sql优化。

2.mysql连接数设置不合理
开启mysql执行日志

use mysql;
show variables LIKE '%general_log%'  查看mysql日志开启状态;
set global general_log=ON; 开启mysql日志记录  on(开启)/off(关闭)

查看mysql状态
(1)如果mysql日志中出现 Too many connections的情况,则更改最大连接数。
查看最大连接数

show variables like '%max_connections%';

查看mysql连接数
查看当前的连接数

show status like 'Threads%';

查看当前连接数
如果当前运行的线程数大于最大线程数,则更改mysql最大连接数

set global max_connections=xxxx 进行设置

3.mysql线程死锁
输入SHOW ENGINE INNODB STATUS\G;出现lock关键字则表示存在线程死锁
查看最近死锁的日志 show engine innodb status;
查看mysql错误日志:show variables like 'log_error';
数据库日志中搜索block,能搜到block的话就是存在数据库死锁,查看对应的sql,优化造成死锁的sql。

4.mysql进程使用优化
查看正在创建的进程数

show global status like 'Thread%';

进程数
查看进程缓存数

show variables like 'thread_cache_size';

缓存数
优化建议:
如果正在创建的进程数>缓存进程数,并且正在创建的进程数较大,建议增加缓存进程数。

set global thread_cache_size=16  增加缓存进程数

5.mysql线程使用优化
输入show processlis;t,查看线程使用情况。
查看进程

Converting HEAP to MyISAM  查询结果太大时,把结果放到磁盘(严重)
Create tmp table  创建临时表(严重)
Copying to tmp table on disk  把内存临时表复制到磁盘(严重)
locked  被其他查询锁住(严重)

6.查看具体sql的执行效率

set profiling=1;  会话级别的profile
show variables like '%profil%';  查看profiling系统变量

查看
查询具体sql语句执行的时间

show profiles;

CHAKAN