MySQL-存储过程不使用查询缓存 [英] Mysql - Stored procedure not using the query cache

问题描述

我刚刚将SQL select语句转换为存储过程

SQL语句使用select语句需要 第一次运行0.4784s，之后运行0.0003s

存储过程每次运行需要0.4784s.

我认为没有使用查询缓存

我该如何纠正?

代码的简化版本

SELECT * FROM  Venues WHERE  VenueName  = :TheVenue

=======

CREATE PROCEDURE  GetVenues
(
  TheVenue  VarChar(22)
)
BEGIN
    SELECT * FROM  Venues WHERE  VenueName  =   TheVenue 
END;

解决方案

欢迎使用MySQL ...很难在存储过程中获取任何东西以利用查询缓存.开发人员文章对MySQL查询缓存的实际了解对此进行详细讨论.在参考文档的此处和 MySQL Performance Blog .. >

基本上，不依赖于在存储过程内部执行的查询的缓存.尽管第一个参考文献确实声称有可能.如果您使用存储过程来封装复杂的逻辑，通常这不是问题.我看到的大多数问题是由于使用存储过程进行非常简单的查询所引起的，其中 VIEW 就足够了.

I have just converted a SQL select statement into a stored procedure

The SQL Statement use select statement takes 0.4784s to run the first time and 0.0003s after that

The Stored procedure takes 0.4784s to run every time.

I assume the query cache is not been used

How can I rectify this?

A simplified version of the code

SELECT * FROM  Venues WHERE  VenueName  = :TheVenue

=======

CREATE PROCEDURE  GetVenues
(
  TheVenue  VarChar(22)
)
BEGIN
    SELECT * FROM  Venues WHERE  VenueName  =   TheVenue 
END;

解决方案

Welcome to MySQL... it is really difficult to get anything within a stored procedure to take advantage of the query cache. The dev article A Practical Look at the MySQL Query Cache discusses this in some detail. The limitations are also mentioned in the reference documentation here and on the MySQL Performance Blog.

Basically, don't depend on caching of queries executed inside of stored procedures. It is near impossible to get it to work though the first reference does claim that it is possible. This usually isn't a problem if you are using stored procedures to encapsulate complicated logic. Most of the problems that I have seen were caused by using stored procedures for very simple queries where a VIEW would have sufficed.

这篇关于MySQL-存储过程不使用查询缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！