mysql的執行計劃快取問題

對於mysql的效能測試，如果以某個單獨的sql併發跑，基本沒有什麼意義（當然也可以關閉快取，這樣比較有意義），因為如果sql一樣的話，直接從快取返回，速度很快。另外mysql解析sql語句的代價也是挺大的，但是mysql沒有快取執行計劃等，一般每次去查詢的都要重新解析的： 

 剛剛開始弄mysql，不知道其他大型網站是怎麼解決這個問題的

mysql> show profile cpu,block io for query 1;

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

| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

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

| starting                       | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |

| checking query cache for query | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |

| checking privileges on cached  | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |

| sending cached result to clien | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |

| logging slow query             | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |

| cleaning up                    | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |

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

 

 

mysql> show profile cpu ,block io for query 2;

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

| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

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

| starting                       | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |

| checking query cache for query | 0.000082 | 0.001000 |   0.000000 |            0 |             0 |

| Opening tables                 | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |

| System lock                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |

| Table lock                     | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |

| init                           | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |

| optimizing                     | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |

| statistics                     | 0.001680 | 0.001000 |   0.000000 |            0 |             0 |

| preparing                      | 0.001619 | 0.002000 |   0.000000 |            0 |             0 |

| Creating tmp table             | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |

| executing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |

| Copying to tmp table           | 0.001007 | 0.000999 |   0.000000 |            0 |             0 |

| Sorting result                 | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |

| Sending data                   | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |

| end                            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |

| removing tmp table             | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |

| end                            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |

| query end                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |

| freeing items                  | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |

| storing result in query cache  | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |

| logging slow query             | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |

| cleaning up                    | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |

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

22 rows in set (0.00 sec)

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-680168/，如需轉載，請註明出處，否則將追究法律責任。