当前位置：首页 -> AI编程基础 -> 数据库编程

MySQL SQL分析 - 参数化查询vs query cache功能(二)

2014-11-24 15:39:41 · 作者: · 浏览: 1
标签: MySQL SQL分析 参数 查询 query cache 功能
cache lock | 0.000023 | | init | 0.000090 | | checking query cache for query | 0.000499 | | checking permissions | 0.000030 | | Opening tables | 0.000131 | | init | 0.000277 | | System lock | 0.000042 | | Waiting for query cache lock | 0.000005 | | System lock | 0.000443 | | optimizing | 0.000364 | | statistics | 0.000107 | | preparing | 0.000059 | | executing | 0.000019 | | Sending data | 0.290067 | | end | 0.000483 | | query end | 0.000169 | | closing tables | 0.000158 | | freeing items | 0.000252 | | Waiting for query cache lock | 0.000063 | | freeing items | 0.000305 | | Waiting for query cache lock | 0.000015 | | freeing items | 0.000095 | | storing result in query cache | 0.000145 | | cleaning up | 0.000330 | +--------------------------------+----------+ 25 rows in set, 1 warning (0.01 sec)

从上面看出， 第一次执行该 SQL， MySQL 需要对 SQL 进行锁缓存，初始化，从缓存中查询是否具备之前缓存过的 SQL，检查用户权限， 表权限，打开表，锁定内存，定制执行计划，执行语句，把数据从磁盘中放入内存中操作，关闭表，锁定数据， 缓存数据等操作， 工作原理与 ORACLE 类似
按照 QUERY CACHE 原则， 假如 SQL 语句改变 (tbcrbtnumb0_.business_ring_id=11024) 替换该变量值， 那么该 SQL 会被看作为一个新的 SQL， 这个时候， MySQL 将会对整个 SQL 做一次全新的操作， 如上（黄线标注描述）
分析 SQL 三 
mysql> select tbcrbtnumb0_.id as id41_, tbcrbtnumb0_.business_ring_id as business2_41_, tbcrbtnumb0_.application_no as applicat3_41_, tbcrbtnumb0_.mobile as mobile41_ from tb_crbt_numbers_load tbcrbtnumb0_ where 1=1 and tbcrbtnumb0_.business_ring_id=11021;
+-------+---------------+---------------+-------------+
| id41_ | business2_41_ | applicat3_41_ | mobile41_   |
+-------+---------------+---------------+-------------+
| 30835 |         11021 | TH20121127259 | 02038688592 |
+-------+---------------+---------------+-------------+
1 row in set (0.34 sec)
mysql>






 show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000795 |
| Waiting for query cache lock   | 0.000077 |
| init                           | 0.000045 |
| checking query cache for query | 0.000337 |
| checking permissions           | 0.000040 |
| Opening tables                 | 0.000113 |
| init                           | 0.000488 |
| System lock                    | 0.000050 |
| Waiting for query cache lock   | 0.000030 |
| System lock                    | 0.000289 |
| optimizing                     | 0.000512 |
| statistics                     | 0.000278 |
| preparing                      | 0.000078 |
| executing                      | 0.000028 |
| Sending data                   | 0.322662 |
| end                            | 0.004777 |
| query end                      | 0.001703 |
| closing tables                 | 0.000526 |
| freeing items                  | 0.000874 |
| Waiting for query cache lock   | 0.000311 |
| freeing items                  | 0.001809 |
| Waiting for query cache lock   | 0.000105 |
| freeing items                  | 0.000184 |
| storing result in query cache  | 0.000966 |
| cleaning up                    | 0.000678 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

上 SQL二，三结果可以看到， 当 WHERE 条件改变， MySQL 会把这两个 SQL 识别为一个新的 SQL， 需要重新操作。
SQL 分析四， 假如我们重新执行 SQL 三操作，看看结果如何？（注意，这个时候 QUERY CACHE 真正发挥作用） 
mysql> select tbcrbtnumb0_.id as id41_, tbcrbtnumb0_.business_ring_id as business2_41_, tbcrbtnumb0_.application_no as applicat3_41_, tbcrbtnumb0_.mobile as mobile41_ from tb_crbt_numbers_load tbcrbtnumb0_ where 1=1 and tbcrbtnumb0_.business_ring_id=11021;
+-------+---------------+---------------+-------------+
| id41_ | business2_41_ | applicat3_41_ | mobile41_   |
+-------+---------------+---------------+-------------+
| 30835 |         11021 | TH20121127259 | 02038688592 |
+-------+---------------+---------------+-------------+
1 row i