（一） MySQL 查询分析

目前由于公司的项目老是服务挂掉，加之进度跟得紧，所以鲜有时间来研究自己的东西。

接下来，这一段时间会专注MySQL的性能调优。

题外话： 我们每个人都有自己的职业规划，我个人认为要按照自己的规划在各个时间段

内应该去专注某些东西。最后将是一个大的综合。我们都知道，如果你只会Java不行、

只会SQL语句不行、只会...... 我们不要 “只”，比如要成为一名优秀的工程师（我指的是真

正意义上的软件工程师，而不是很多人眼里的码农），要成为一名架构师（不是只会吹嘘

概念的Guy），要成为一名CTO.......这些，如果你仅仅是“只会”的话不行，而是需要专注相

应的某些方面。

                                                           2013-10-23  22:59

Let's get down to business.(让我们言归正传吧。 哈哈 show一下英文)。

1.  性能优化简介

    性能，表现在很多方面，如：每秒查询次数、CPU利用率以及吞吐量等等。我所关注的也

是普片关注的，一个非常重要的原则——响应时间。所以在整个MySQL调优系列提及到的性

能就是指响应时间，除非特别说明。

2.  从分析日志角度进行剖析MySQL查询  

3.  剖析单条SQL语句

3.1.  使用SHOW  PROFILES

         从MySQL5.1后引入了SHOW  PROFILE命令，默认的禁用的。可以通过服务器变量在会

话（连续）级别动态的修改。 如下：

/*  MySQL中的标志位 1 表示肯定，0 表示否。目前为止我还没发现反着来的  */

         SET  PROFILING = 1;   

如下图：

                            图3.1-1 启用剖析功能

使用SHOW PROFILES 命令查看执行过的SQL情况。如下图：

                             图3.1-2 

 使用 SHOW PROFILE FOR QUERY  ID ; 剖析单条语句的执行情况，如下图：

 

 使用 SHOW PROFILE FOR QUERY  ID ; 剖析单条语句的执行情况，如下图：

 

通过上面几张图我们可以清楚SQL执行的步骤，已经每个步骤花费的时间。

其实我可自己写SQL来分析。比如我可以按照SQL执行的每个步骤所花费的时间做

降序排序，这样我们就能很明了SQL哪些步骤需要优化，非常便于分析。如下：

SQL如下：

SET @query_id  := 1;  /* 这里的数字 1 是SQL的PROFILS 的ID，可以根据需要更改  */

SELECT STATE , SUM(duration) AS Total_R,

     ROUND(

        100 * SUM(duration)/

(SELECT SUM(duration)

  FROM  INFORMATION_SCHEMA.PROFILING 

  WHERE query_id = @query_id

  ),2) AS Pct_R,

     COUNT(*)  AS Calls,

     SUM(duration) / COUNT(*) AS  "R/Call"

     FROM INFORMATION_SCHEMA.PROFILING 

     WHERE query_id = @query_id

     GROUP BY STATE

     ORDER BY Total_r  DESC;

如下图：

 

由于时间关系，就只能下次详细分析上面的数据。便于我们更精确的定位问题。

2013-10-25  21:46

接着上面，我们分析上面的执行不走步骤，也已经按照各个步骤所花时间比例展现了，下面

先给出官方文档的解释，然后稍作翻译。具体每个执行步骤应该怎么去优化，还得去深入研究，

至少，我们知道问题所在了。

MySQL 5.6 官守手册地址：

MySQL5.6 SQL执行步骤解释 

其实这也反应到MySQL的执行计划中的State列。mysql列出的状态主要有以下几种：

Checking table
正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
正在执行 FLUSH TABLES，等待其他线程关闭数据表。
Killed
发 送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位， 不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。
Sorting for group
正在为 GROUP BY 做排序。
Sorting for order
正在为 ORDER BY 做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。 正尝试打开一个表。
Removing duplicates
正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update
正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求.
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 --skip-external-locking参数来禁止外部系统锁。
Upgrading lock
INSERT DELAYED 正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待 GET_LOCK()。
Waiting for tables
该 线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情 况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。
waiting for handler insert
INSERT DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。