mysql 运行状态分析 运行故障排查

一、优化概述

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。

除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。

二、查询与索引优化分析

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

show 命令

我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：

Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）

Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）show variables like "slow%"

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

慢查询日志

慢查询 可以记录执行时间比较慢的语句 和 未使用索引的语句

一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

1，slow_query_log 这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2，long_query_time 当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短(单位秒)。

3，slow_query_log_file 记录日志的文件名。

4，log_queries_not_using_indexes 这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

– 表的读取顺序

– 数据读取操作的操作类型

– 哪些索引可以使用

– 哪些索引被实际使用

– 表之间的引用

– 每张表有多少行被优化器查询

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   85 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

EXPLAIN字段：

id: 查询序号即为sql语句执行的顺序

select_type

select类型，它有以下几种值

2.1 simple 它表示简单的select,没有union和子查询

2.2 primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary,上图中就是这样

2.3 union union语句的第二个或者说是后面那一个.现执行一条语句，explain

Table：显示这一行的数据是关于哪张表的

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key：实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MySQL认为必须检索的用来返回请求数据的行数

type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL

Extra：关于MYSQL如何解析查询的额外信息，主要有以下几种

Extra:参数说明

using index：只用到索引,可以避免访问表.

using where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.

using tmporary：用到临时表

using filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)

range checked for eache record(index map:N)：没有好的索引.

type：参数说明

system、const：可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.

eq_ref：访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)

ref：访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生

range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)

index：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描

ALL：全表扫描，应该尽量避免

profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。

如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

profiling默认是关闭的。可以通过以下语句查看 select @@profiling;

打开功能： mysql>set profiling=1; 执行需要测试的sql 语句：

开启后执行了sql语句 就可以通过show profiles;命令查看执行时间

mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration   | Query                     |
+----------+------------+---------------------------+
|        1 | 0.00417000 | select * from user        |
|        2 | 0.00214700 | select count(*) from user |
+----------+------------+---------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profiles\G; 可以得到被执行的SQL语句的时间和ID

mysql>show profile for query 1; 得到对应SQL语句执行的详细信息

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.001587 |
| checking permissions | 0.000351 |
| Opening tables       | 0.000015 |
| init                 | 0.000021 |
| System lock          | 0.000264 |
| optimizing           | 0.000014 |
| statistics           | 0.000014 |
| preparing            | 0.000012 |
| executing            | 0.000003 |
| Sending data         | 0.001485 |
| end                  | 0.000018 |
| query end            | 0.000120 |
| closing tables       | 0.000026 |
| freeing items        | 0.000157 |
| cleaning up          | 0.000083 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)