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优化。
2.mysql连接数设置不合理
开启mysql执行日志
use mysql;
show variables LIKE '%general_log%' 查看mysql日志开启状态;
set global general_log=ON; 开启mysql日志记录 on(开启)/off(关闭);
(1)如果mysql日志中出现 Too many connections的情况,则更改最大连接数。
查看最大连接数
show variables like '%max_connections%';
查看当前的连接数
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;