全表扫描总共话了0.0017730秒
各个阶段消耗的时间是：
- mysql> show profile for query 54;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.000065 |
- | checking query cache for query | 0.000073 |
- | Opening tables | 0.000037 |
- | System lock | 0.000024 |
- | Table lock | 0.000053 |
- | init | 0.000044 |
- | optimizing | 0.000022 |
- | statistics | 0.000032 |
- | preparing | 0.000030 |
- | executing | 0.000020 |
- | Sending data | 0.001074 |
- | end | 0.000091 |
- | query end | 0.000020 |
- | freeing items | 0.000103 |
- | storing result in query cache | 0.000046 |
- | logging slow query | 0.000019 |
- | cleaning up | 0.000020 |
- +--------------------------------+----------+
- 17 rows in set (0.00 sec)
|
第一次不走缓存的话，需要检查是否存在缓存中，打开表，初始化等操作，最大的开销在于返回数据。
第二步：根据主键查询数据。
- mysql> select name ,gender from person4pri where id in (1,2);
- +----------+--------+
- | name | gender |
- +----------+--------+
- | zhaoming | male |
- | wenwen | female |
- +----------+--------+
- 2 rows in set (0.01 sec)
|
查看其执行计划：
- mysql> explain select name ,gender from person4pri where id in (1,2);
- +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
- | 1 | SIMPLE | person4pri | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
- +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
|
从执行计划中我们可以看出，走的是范围索引。
再看其执行消耗的时间：
- mysql> show profiles;
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
- | 63 | 0.00135700 | select name ,gender from person4pri where id in (1,2) |
- | 64 | 0.00079200 | explain select name ,gender from person4pri where id in (1,2) |
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
- 15 rows in set (0.01 sec)
|
这次查询消耗时间为0.00079200。
查看各个阶段消耗的时间：
- mysql> show profile for query 63;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.000067 |
- | checking query cache for query | 0.000146 |
- | Opening tables | 0.000342 |
- | System lock | 0.000027 |
- | Table lock | 0.000115 |
- | init | 0.000056 |
- | optimizing | 0.000032 |
- | statistics | 0.000069 |
- | preparing | 0.000039 |
- | executing | 0.000022 |
- | Sending data | 0.000100 |
- | end | 0.000075 |
- | query end | 0.000022 |
- | freeing items | 0.000158 |
- | storing result in query cache | 0.000045 |
- | logging slow query | 0.000019 |
- | cleaning up | 0.000023 |
- +--------------------------------+----------+
- 17 rows in set (0.00 sec)
|
看出最大的消耗也是在Sending data，第一次也是需要一些初始化操作。
