今天本来想看下mysql的一条语句在MYSQL引擎中是如何的处理，无意中发现了很多其他的东西，就整理下吧。

?

查看SQL的执行情况：

1.通过show processlist 来查看系统的执行情况！

?

   mysql> show processlist ;
+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host      | db    | Command | Time | State | Info             |
+----+------+-----------+-------+---------+------+-------+------------------+
| 41 | root | localhost | mysql | Query   |    0 | NULL  | show processlist |
+----+------+-----------+-------+---------+------+-------+------------------+
1 row in set (0.00 sec)

?

该命令一般用于实时的去查看系统中运行较慢的SQL。

?

2.通过profiling来进行查看

这个命令是查看SQL的执行时间，能很直观的看出快慢。

?

2.1查看系统值：0代表还是关闭着分析功能

mysql> select @@profiling;

+-------------+

| @@profiling |

+-------------+

| ? ? ? ? ? 0 |

+-------------+

1 row in set (0.00 sec)

?

2.2打开工具

?

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

?

2.3准备基础数据

?

create table name
(
id int not null auto_increment,
first_name varchar(30) not null,
last_name  varchar(30) not null,
primary key (id),
index (last_name,first_name)
)

insert into name(first_name,last_name) values("xue","zhaoming")
insert into name(first_name,last_name) values("xue","zhaoyue") 
commit;
select * from name ;
select * from name ;

?

2.4 好戏开始了

?

mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                                                          |
+----------+------------+----------------------------+
|        8 | 0.00074500 | insert into name(first_name,last_name) values("xue","zhaoyue") |
|        9 | 0.00021200 | commit                                                         |
|       10 | 0.00063700 | select * from name                                             |
|       11 | 0.00026100 | select * from name  

?

通过上面的大家可以很清晰的看到在执行两边的select * from name语句的时候实际执行的效率是不一样。那么我们来具体看看为什么不一样的。

?

mysql> SHOW PROFILE FOR QUERY 10;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000048 |
| checking query cache for query | 0.000231 |                  ---检查是否在缓存中
| Opening tables                 | 0.000024 |                  ---打开表
| System lock                    | 0.000014 |                  ---锁系统
| Table lock                     | 0.000032 |                  ---锁表 
| init                           | 0.000027 |                  ---初始化
| optimizing                     | 0.000013 |                  ---优化查询
| statistics                     | 0.000019 |
| preparing                      | 0.000018 |                  ---准备
| executing                      | 0.000011 |                  ---执行
| Sending data                   | 0.000075 |
| end                            | 0.000014 |
| query end                      | 0.000010 |
| freeing items                  | 0.000057 |
| storing result in query cache  | 0.000022 |                  ---将结果放到缓存中
| logging slow query             | 0.000010 |                  ---
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

?

具体的查询过程如上面所示,那么为什么第二次的查看就快了呢？简单，看下第二次的查询信息

?

mysql> SHOW PROFILE FOR QUERY 11;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       |