Mysql性能分析

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语句来检查表及其对应的索引。