644
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 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 in set (0.00 sec)
mysql> show profile; +--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.001367 |
| Waiting for query cache lock | 0.000071 |
| init | 0.000027 |
| checking query cache for query | 0.000163 |
| checking privileges on cached | 0.000129 |
| checking permissions | 0.000386 |
| sending cached result to clien | 0.000164|
| cleaning up | 0.000079 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.01 sec)
參考 SQL 分析三，四，數據查詢需要使用的時間（綠色標注部分）很明顯， SQL 分析四返回速度塊了很多，另外，從係統返回的 SQL 分析看出來，係統直接從緩存中返回數據給客戶， 沒有重複進行 SQL 分析及磁盤 I/O 操作。（藍色標注部分） 因此， QEURY CACHE 明顯加速了 SQL 返回結果。
但必須注意，隻有兩個 SQL 相同的情況下，才能夠獲得 QUERY CACHE 的優點。
參數化查詢
參數化查詢能夠在一定情況下避免了 SQL 注入， 而 ORACLE 也比較推薦使用參數化查詢， ORACLE 每次執行 SQL （無論 SQL 是否語法一致）都存在 SQL 分析，
假如SQL語法不一樣，則進行硬解析，需要重新定製執行計劃
假如SQL語法不一致則進行軟解析，避免重複定製執行計劃，減少 CPU 消耗，增加 SQL 語句返回時間。
MySQL 官方文檔中並沒有提出到這點。
對 MySQL 進行參數化查詢分析
SQL 參數化分析一
mysql> set @num=11204;
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=@num;
+-------+---------------+---------------+-----------+
| id41_ | business2_41_ | applicat3_41_ | mobile41_ |
+-------+---------------+---------------+-----------+
| 31051 | 11204 | 1222570 | 85237810 |
| 31052 | 11204 | 1222570 | 82685386 |
| 31053 | 11204 | 1222570 | 82783689 |
| 31054 | 11204 | 1222570 | 82685106 |
| 31055 | 11204 | 1222570 | 38880051 |
+-------+---------------+---------------+-----------+
5 rows in set (0.37 sec)
mysql> show profile; +--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.001858 |
| Waiting for query cache lock | 0.000089 |
| init | 0.000150 |
| checking query cache for query | 0.001143 |
| checking permissions | 0.000970 |
| Opening tables | 0.000544 |
| init | 0.000743 |
| System lock | 0.000170 |
| optimizing | 0.000332 |
| statistics | 0.000293 |
| preparing | 0.000134 |
| executing | 0.000057 |
| Sending data | 0.438626 |
| end | 0.000694 |
| query end | 0.000221 |
| closing tables | 0.000300 |
| freeing items | 0.000521 |
| cleaning up | 0.000360 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.01 sec)
變量值不變情況下，重複執行該 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=@num;
+-------+---------------+---------------+-----------+
| id41_ | business2_41_ | applicat3_41_ | mobile41_ |
+-------+---------------+---------------+-----------+
| 31051 | 11204 | 1222570 | 85237810 |
| 31052 | 11204 | 1222570 | 82685386 |
| 31053 | 11204 | 1222570 | 82783689 |
| 31054 | 11204 | 1222570 | 82685106 |
| 31055 | 11204 | 1222570 | 38880051 |
+-------+---------------+---------------+-----------+
5 rows in set (0.34 sec)
mysql> show profile; +--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.001188 |
| Waiting for query cache lock | 0.000052 |
| init | 0.000030 |
| checking query cache for query | 0.001791 |
| checking permissions | 0.000172 |
| Opening tables | 0.000614 |
| init | 0.001346 |
| System lock | 0.000268 |
| optimizing | 0.000626 |
| statistics | 0.000674 |
| preparing | 0.000439 |
| executing | 0.000028 |
| Sending data | 0.327278 |
| end | 0.000649 |
| query end | 0.000217 |
| closing tables | 0.000408 |
| freeing items | 0.000692 |
| cleaning up | 0.000570 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.01 sec)
分析結果
從數據返回時間上看來（藍色標注）， 使用參數化查詢，並沒有在時間返回上獲得優勢。
從SQL執行計劃上看來（紫色標注), 相同 SQL 語句使用參數化查詢，係統同樣會重新定製執行計劃，產生磁盤I/O， 並沒有想 ORACLE 一樣獲得性能上的優化。
另外，參數化查詢是不會在 query cache 內存塊中取結果的。
可以看做每次使用參數化查詢都會被認為是一個全新的 SQL 進行分析， （沒有學習到 ORACLE 的精髓，比較失望）
注意，使用參數化查詢時， 即時使用 SQL_cache 語法, 也無法使用 query cache 功能。
常見開發下有幾種選擇
1. 直接把 SQL 變量值提交至 MySQL API 執行，（可利用 QUERY CACHE 功能，有部分 SQL 能夠進行數據加速）但可能會遇到 SQL 注入， 升級程序麻煩。
2. 利用 procudure, function 等功能先吧 SQL 進行打包然後再調用執行， 類似參數化查詢， 無法獲得 QUERY CACHE 功能， 令程序清晰， 程序升級，修改比較方便， 並且有效防止了 SQL 注入。
最後更新：2017-04-03 14:53:50