//
优化SQL语句的一般步骤
//
在mysql中，SQL优化是很常见的一种需求，我自己这方面的经验也不是特别充足，在我自己的认知中，通常情况下，会通过下面的步骤去优化一个慢日志较多MySQL服务。
1、通过show global status命令查看当前执行最多的是哪些SQL动作。
例如Com_select指的是select的次数，Com_insert代表insert的次数，通常情况下：Com_insert，Com_select，Com_update和Com_delete用的最多。其中insert语句，如果是批量插入的话，只会累计1
上面的这几个参数是针对所有的存储引擎的，下面的是针对innodb存储引擎的：
innodb_rows_read select查询返回的行数；
innodb_rows_insert 执行insert操作返回的行数；
innodb_rows_update 执行update操作更新的行数；
innodb_rows_delete 执行delete操作删除的行数
通过这些参数，可以很快的确定当前MySQL主要是以更新为主还是以查询为主。
对于事务应用比较多的场景，可以通过Com_commit和Com_rollback来了解事务的提交和回滚情况。
2、通过pt-query-digest工具来分析MySQL的慢日志，其中慢日志的目录是slow_query_log_file，而慢日志的阈值是参数long_query_time控制的。
mysql> show variables like '%slow%file%'; +---------------------+------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------+ | slow_query_log_file | /data/mysql_4306/log/slowquery.log | +---------------------+------------------------------------+ 1 row in set (0.00 sec) mysql> show variables like '%long_query%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
pt-query-digest工具可以解析出来当前的慢日志的一个分析报告。具体用法，之前的文章介绍过，这里不再赘述。
3、通过explain或者desc命令来分析SQL的执行计划。
一般可以发现SQL是否使用了索引以及索引是否需要优化等信息。
4、通过profile命令来查看当前最主要的耗费时间的步骤。
mysql> select count(1) from t1; +----------+ | count(1) | +----------+ | 1 | +----------+ 1 row in set (0.11 sec) mysql> show profiles; +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00123300 | show databases | | 2 | 0.00016775 | SELECT DATABASE() | | 3 | 0.00092900 | show databases | | 4 | 0.00122325 | show tables | | 5 | 0.00134250 | show tables | | 6 | 0.11396400 | select count(1) from t1 | +----------+------------+-------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> show profile for query 6; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000089 | | Executing hook on transaction | 0.000013 | | starting | 0.000011 | | checking permissions | 0.000008 | | Opening tables | 0.000040 | | init | 0.000015 | | System lock | 0.000015 | | optimizing | 0.000007 | | statistics | 0.000024 | | preparing | 0.000029 | | executing | 0.113622 | | end | 0.000011 | | query end | 0.000006 | | waiting for handler commit | 0.000014 | | closing tables | 0.000013 | | freeing items | 0.000022 | | cleaning up | 0.000027 | +--------------------------------+----------+ 17 rows in set, 1 warning (0.00 sec)
上面的分析不难看出，这条SQL大部分时间都浪费在了executing上面，为了更清晰的看到结果，可以通过information_schema表对上面的结果进行排序：
mysql> select state,sum(duration) as total_r , round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=6),2) as pct_r,count(*) as calls,sum(duration)/count(*) as 'R/Call' from information_schema.profiling where query_id group by state order by total_r desc; +--------------------------------+----------+--------+-------+--------------+ | state | total_r | pct_r | calls | R/Call | +--------------------------------+----------+--------+-------+--------------+ | executing | 0.114058 | 100.08 | 6 | 0.0190096667 | | Opening tables | 0.001226 | 1.08 | 6 | 0.0002043333 | | checking permissions | 0.000852 | 0.75 | 56 | 0.0000152143 | | starting | 0.000692 | 0.61 | 8 | 0.0000865000 | | Creating tmp table | 0.000524 | 0.46 | 4 | 0.0001310000 | | init | 0.000410 | 0.36 | 6 | 0.0000683333 | | statistics | 0.000361 | 0.32 | 5 | 0.0000722000 | | preparing | 0.000165 | 0.14 | 5 | 0.0000330000 | | freeing items | 0.000138 | 0.12 | 7 | 0.0000197143 | | cleaning up | 0.000108 | 0.09 | 7 | 0.0000154286 | | optimizing | 0.000092 | 0.08 | 6 | 0.0000153333 | | waiting for handler commit | 0.000084 | 0.07 | 9 | 0.0000093333 | | System lock | 0.000079 | 0.07 | 5 | 0.0000158000 | | closing tables | 0.000067 | 0.06 | 6 | 0.0000111667 | | query end | 0.000039 | 0.03 | 6 | 0.0000065000 | | end | 0.000035 | 0.03 | 6 | 0.0000058333 | | removing tmp table | 0.000018 | 0.02 | 4 | 0.0000045000 | | Executing hook on transaction | 0.000013 | 0.01 | 1 | 0.0000130000 | +--------------------------------+----------+--------+-------+--------------+ 18 rows in set, 2 warnings (0.00 sec)
当然，profile还支持查看all、cpu、block io、content switch、page faults等明细类型，例如查看上述语句在CPU资源上消耗的时间：
show profile cpu for query 6；
5、通过trace分析优化器如何选择执行计划。
这块儿后续找一篇文章展开说说。
基本上以上的步骤，可以足够我们定位MySQL服务慢的问题，后续根据实际问题，来进行相应的处理即可。
