mysql 执行计划 缓存_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)