优化SQL的一般步骤

通过show status命令了解各种SQL的执行频率

mysql> show status like 'Com_%';

通常比较关心以下几个统计参数：

参数	说明
Com select	执行SELECT操作的次数, 一次查询只累加1
Com insert	执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次
Com update	执行UPDATE操作的次数
Com delete	执行DELETE操作的次数
Com_commit/Com_rollback	事务提交和回滚的情况
……	…….

定位执行效率较低的SQL语句

通过慢查询日志

在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题
通过show processlist

查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

show profile的使用

-- 查看当前MySQL是否支持profile
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)
-- 默认是关闭的，可以通过set语句在session级别开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
-- 开启profile
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 再次查询，已经开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

执行show profiles，查看执行sql的query id

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00021675 | select @@profiling |
|        2 | 0.00007650 | show tables        |
|        3 | 0.02193350 | show databases     |
+----------+------------+--------------------+
3 rows in set, 1 warning (0.00 sec)

通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间

mysql> show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000056 |
| init                 | 0.000015 |
| System lock          | 0.000008 |
| optimizing           | 0.000008 |
| statistics           | 0.000016 |
| preparing            | 0.000016 |
| executing            | 0.021611 |
| Sending data         | 0.000036 |
| end                  | 0.000008 |
| query end            | 0.000006 |
| closing tables       | 0.000005 |
| removing tmp table   | 0.000009 |
| closing tables       | 0.000009 |
| freeing items        | 0.000025 |
| cleaning up          | 0.000021 |
+----------------------+----------+
17 rows in set, 1 warning (0.00 sec)