Database

MySQL优化六(查看Query性能)

MySQL 优化的文章有很多，从比较全局的角度看，优化还是跟业务有很大关系的，就是因为业务上的需要，在某些场景下，对于数据库服务器产生了压力和瓶颈，因此才有必要进行一些优化。

首先谈谈工作中碰到的一些场景。某年一个社交应用，因为没做好应对大数据的准备，上线后 MySQL服务器一直处于 I/O 非常高的情况，花了一段时间才修好。

还有一次做报表应用，虽说单独 数据库服务器，单独 Web 应用服务器，因为数据量实在庞大，而计算特别多， CPU也吃不消了。

经过了几次事件，痛定思痛，对 MySQL 进行了全面的研究，阅读了很多文档，并且在实际中也解决了不少问题，因此记录下来，希望能和各路朋友进行交流。

需要说明的是，大部分都是以使用为前提来说明的，不涉及源码和MySQL 内核之类的，不过有很多大神的博客值得去品读，之前已经推荐过一位，今天再来一位 平民架构

本文总结了一下MySQL数据库如何找到瓶颈的一些方式，以及释疑。

服务器部分，前面有一系列文章已经触及过了，不再另述。

查看服务器状态

首先，看看服务器有什么问题没。

错误日志 慢速日志

这两个日志是必须的

错误日志没什么好说，有错就去改了吧。

慢速日志中，根据业务或者尝试，设定慢速时间，获取一部分需要优化的SQL，之后进入分析阶段。

MySQL 提供工具可以将慢速查询到处， mysqldumpslow

服务器状态
show status
show variables
show processlist

使用这些命令，查看服务器状态，看看是否有不合适的，至于判断标准，提供两个思路，因为也挺复杂的，未来估计也要大篇幅介绍。

  • 如果是PHP工程师，可以使用 PHPMyAdmin 工具，看看有没有红色提示
  • 某些跟性能有关的参数是有一些计算方法的，这里是跟硬件有关，因此考虑的因素比较多
  • 如果是I/O 密集型的，其中一项缓存，那么内存要够大，有相关计算方法，调优命中率
  • 如果是 CPU 密集，可能 Buffer重要点

能力有限，提供思路，轻拍。。。

其他工具

系统部分有很多，例如 top， 主要是查看 MySQL 服务器在系统级别上的用量

mysqlard 据说是一个收集 MySQL 服务器的守护进程，可以查看很多信息，未考证。

mysqlreport 是个工具， 可以看看 http://hackmysql.com/mysqlreport , 很详细的说。

Explain

Explain 是用在 sql 解析器里面的，譬如

EXPLAIN SELECT id FROM table1

这句查看查询语句的性能，列出本句所产生的一些影响，执行之后的字段如下：

select_type:

查询有几种类型，摘录自MySQL 文档 如下：

SIMPLE

Simple SELECT (not using UNION or subqueries) 简单查询

PRIMARY

Outermost SELECT 外部查询，比如select条件是另一个select

UNION

Second or later SELECT statement in a UNION , UNION中第二个或后面的select

DEPENDENT UNION

Second or later SELECT statement in a UNION, dependent on outer query , UNION 中第二个或后面的select，依赖外部query

UNION RESULT

Result of a UNION ， union查询

SUBQUERY

First SELECT in subquery， 子查询的第一个

DEPENDENT SUBQUERY

First SELECT in subquery, dependent on outer query ，子查询的第一个，取决于外部query

DERIVED

Derived table SELECT (subquery in FROM clause) ，衍生表查询，即在from语句中的子查询

UNCACHEABLE SUBQUERY

A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query，子查询无法缓存，必须为每一行做重新评估的

table:

表名

type:

join type， 联合类型。

system: 只有一行的表，例如系统表，比较特别。

const: 非常快速，表明是主键或 唯一索引的查询。

eq_ref: 关联表查询，表明关联的2个字段分别可能是 主键 或 非 NULL 的唯一索引。这个是 join 查询最好的一种可能性。结果集应该是一条，是一种 1对1 的关系。

ref: 关联若干条记录的查询，可能关联字段不是 主键 或 唯一索引。 1对多 关系， 也是个不错的join。

fulltext: 使用了 FULLTEXT 索引

ref_or_null: 类似 ref， 但是 MySQL会对包含 NULL 的行做额外搜索

index_merge: 表示使用了 Index Merge optimization 算法， 之后的 key 列包含了所使用的索引， key_len 列包含了索引最长键

unique_subquery: 使用了外联子查询， 比如 IN 操作，并且子查询中的查询结果为 主键

index_subquery: 类似 unique_subquery， 子查询中结果为 非主键

range: 含有比较操作，如 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()， key 列包含了使用的索引， key_len 列包含了索引最长键

index: 和ALL一样，只是被用到 索引

ALL: 全表扫描，比较差的查询。

possible_keys:

可能用到的 索引

key:

用到的索引

key_len:

key的长度

ref:

哪个列或常量被用到和索引做比较

rows:

影响的行数

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)：没有好的索引.

Profiling

Profiling 命令是个查看 MySQL 中 sql query 的执行性能，时间。

首先在 MySQL 中打开profiling 选项

set profiling=1;

看看开启了没：

show variables like 'profiling%';

这里会出现 profiling_history_size ， 表示的是 记录条数

每次执行完 查询 的 语句后， 执行

show profiles;

可以看到所有最近的sql 执行情况，并且，每个 sql 都有个 id 号，之后使用 id 号 执行如下

show profile for query id;

整个过程很清晰的会出来，例如： 初始化用了多久， 打开表， 上系统锁， 上表锁， 执行， 等等。

以上是本博总结的一些基本的找出数据库瓶颈的方法和思路。