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

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

2014-11-24 15:39:41 · 作者: · 浏览: 0
标签: MySQL SQL分析 参数 查询 query cache 功能
MySQL SQL分析 - 参数化查询vs query cache功能
query cache, mysql 5 开始附带的一个功能， 与引擎无关， 只与数据查询语法相关。
测试描述： 当前使用中是 MySQL-5.6.14 Linux RHEL6 64 位系统产生环境, 使用 INNODB 引擎， 分配 innodb 2g 内存空间 
[root@TiYanPlat ~]# uname -a
Linux TiYanPlat 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64x86_64 GNU/Linux
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.14    |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.01 sec)

Query cache 功能：
利用 qeury_cache_size 定义内存大小， 内存用于把用户 SQL 放入内存中， 包括 SQL 语句， 包括SQL 语句执行的结果
假如下一次查询时使用相同的 SQL 语句， 则直接从内存中获得结果， 不再进行 SQL 分析， 不在进行磁盘 I/O 读数据。加速数据查询返回结果。
实现目标，开启 QCACHE 功能， 如 my.cnf 定义
query-cache-size=16777216
query-cache-type=ON
查询 数据库中是否使用当前功能 
mysql> show status like '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 440      |
| Qcache_free_memory      | 12306960 |
| Qcache_hits             | 13176    |
| Qcache_inserts          | 29777    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 45862    |
| Qcache_queries_in_cache | 2098     |
| Qcache_total_blocks     | 4701     |
+-------------------------+----------+
8 rows in set (0.02 sec)

参数返回结果不再一一详细描述， 自行参考官方文档， 从上返回结果可以看到，使用中的数据库 SQL 命中率 （Qcache_hits） 并不理想，原因与业务有关。
SQL 分析一， 使用了 QUERY CACHE 的好处
原理， 利用 EXPLAIN 分析当前 SQL 执行计划， 利用 PROFILE 功能分析当前 SQL 执行计划，过程
执行下面语句进行分析 
mysql> explain 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=11024;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | tbcrbtnumb0_ | ALL  | NULL          | NULL | NULL    | NULL |180182 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
当前语法执行的是全表扫描，另外，需要从 180182 行中扫描相关结果

SQL 分析二， 判断第一次执行该 SQL 时候的执行过程
mysql> set profiling=1;
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=11024;
+-------+---------------+---------------+-------------+
| id41_ | business2_41_ | applicat3_41_ | mobile41_   |
+-------+---------------+---------------+-------------+
| 30838 |         11024 | TH20121127229 | 02038688592 |
+-------+---------------+---------------+-------------+
1 row in set (0.30 sec)
mysql> show profile;                                                                                                          +--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000191 |
| Waiting for query