Mysql性能分析.md
辅助命令
- SHOW VARIABLES
- show table
- show profiles
- Analyze Table XXX
- optimize table xxx ;# 释放空间
- ANALYZE TABLE xxx ;# 修正索引散列值
进行分析需要先了解mysql运行情况,一般使用show status 分析sql运行状态。
table_locks 分析
innodb 和MyISAM都实现了表锁,表锁包括表共享读锁(Table Read Lock)和表独占写锁。MyISAM在执行查询语句前,会自动给相关的表加读锁,在执行更新操作的前,会自动给相关的表加写锁;自动加锁时,MyISAM总是一次获得sql语句相关表的全部锁,这也是MyISAM表不会出现死锁的原因。
查看表锁状态
如果table_locks_waited值较高,且存在性能问题,则说明存在着较严重的表级锁争用情况。
输出结果如下
参数 | 统计结果 | 描述 |
---|---|---|
Table_locks_immediate | 4058 | 表示立即释放表锁数 |
Table_locks_waited | 0 | 表示需要等待的表锁数 |
如果table_locks_waited值较高,且存在性能问题,则说明存在着较严重的表级锁争用情况。
- infodb 、myisam 都支持表锁
Innodb_row_lock 分析
innodb实现了行数,InnoDB行锁实现是通过索引上的索引项加锁实现的,意味着:只有通过索引条件检索数据,InnoDB才会使用行锁,否则使用表锁。
SHOW STATUS WHERE variable_name LIKE '%row_lock%';
Variable_name | Value | 描述 |
---|---|---|
Innodb_row_lock_current_waits 0 | 当前锁等待的数量 | |
Innodb_row_lock_time | 1411838 | 自系统启动到现在,锁定的总时间,单位:毫秒 ms。 |
Innodb_row_lock_time_avg | 1173 | 平均锁定的时间,单位:毫秒 ms |
Innodb_row_lock_time_max | 51614 | 最大锁定时间,单位:毫秒 |
Innodb_row_lock_waits | 1203 | 自系统启动到现在,锁等待次数,即锁定的总次数。 |
输出结果中 Innodb_row_lock_waits 有1203次,表示发生了1203锁等待事件。平均锁定时间 1173 ms。合计锁定时间有 1411秒。
table cache
Mysql在使用独立表空间时,可以通过 table cache机制来优化表加载机制。可以使用如下命令查询当前是否使用独立表空间:
show variables like '%per_table%';
table cache 使用实际情况查询方式:
SHOW GLOBAL STATUS WHERE variable_name LIKE '%table_open%' OR variable_name LIKE 'open%tables';
输出:
| Variable_name |Value | 描述|
| - | - | -|
| Variable_name | Value
| Open_tables | 2000 | 打开后在缓存中的表数量 < table_open_cache|
| Opened_tables | 38867830 | 打开的所有表数量|
| Table_open_cache_hits | 244113301 ||
| Table_open_cache_misses | 38867347 ||
| Table_open_cache_overflows | 38850842 ||
- 查看 时间打开的表空间
SHOW OPEN TABLES
- 缓存表配置查询
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';
- 查看超时时间
show variables like '%timeout%';
数据缓存
- innodb_buffer_pool_size
问题诊断
# 查询进程 如果有 SUPER 权限,则可以看到全部的线程,否则,只能看到自己发起的线程
SHOW PROCESSLIST;
# 查看下在锁的事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
# 查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查询是否锁表
show OPEN TABLES where In_use > 0;
SHOW INDEX
使用SHOW INDEX语句来查看索引的散列程度
MySQL 的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了。
Analyze Table
通过Analyze Table语句来修复索引:
需要注意的是,如果开启了binlog,那么Analyze Table的结果也会写入binlog,我们可以在analyze和table之间添加关键字local取消写入。
先查询索引散列值情况
select index from 'tableName'
在优化散列值
Analyze Table 'tableName'
Checksum Table
数据在传输时,可能会发生变化,也有可能因为其它原因损坏,为了保证数据的一致,我们可以计算checksum(校验值)。
使用MyISAM引擎的表会把checksum存储起来,称为live checksum,当数据发生变化时,checksum会相应变化。
在执行Checksum Table时,可以在最后指定选项qiuck或是extended;quick表示返回存储的checksum值,而extended会重新计算checksum,如果没有指定选项,则默认使用extended。
Optimize Table
经常更新数据的磁盘需要整理碎片,数据库也是这样,Optimize Table语句对MyISAM和InnoDB类型的表都有效。
如果表经常更新,就应当定期运行Optimize Table语句,保证效率。
与Analyze Table一样,Optimize Table也可以使用local来取消写入binlog。
Check Table
数据库经常可能遇到错误,譬如数据写入磁盘时发生错误,或是索引没有同步更新,或是数据库未关闭MySQL就停止了。
遇到这些情况,数据就可能发生错误:
Incorrect key file for table: ‘ ‘. Try to repair it.
此时,我们可以使用Check Table语句来检查表及其对应的索引。