I am trying to optimize a query of the form SELECT SQL_NO_CACHE col FROM TABLE ... When I first connect to the database and execute the query it takes about 9 seconds. When I execute the query the second time it takes almost 0.1 seconds. I place the SQL_NO_CACHE in the query to ensure that mysql is not reading the result from cache. My question is why does the first execution of the query, right after the connecting to the database (mysql -uroot ... ) takes significantly longer than subsequent executions. What is the actual execution time of the query?
我正在尝试优化SELECT SQL_NO_CACHE col FROM TABLE表单的查询...当我第一次连接到数据库并执行查询时，大约需要9秒。当我第二次执行查询时，它需要差不多0.1秒。我将SQL_NO_CACHE放在查询中以确保mysql不从缓存中读取结果。我的问题是为什么在连接到数据库之后第一次执行查询（mysql -uroot ...）比后续执行要长得多。查询的实际执行时间是多少？
1 个解决方案
#1
2
MySQL can take a while to warm up its internal caches. Remember, SQL_NO_CACHE means avoid the query cache only. The index cache is the most important from a performance perspective. If the index has not been read, there's a significant penalty the first time it's used.
MySQL可能需要一段时间才能使其内部缓存变暖。请记住，SQL_NO_CACHE意味着仅避免查询缓存。从性能角度来看，索引缓存是最重要的。如果未读取索引，则在第一次使用时会有重大损失。
If you're using InnoDB, which you should be, ensure that your buffer pool is sufficiently large. Most servers should allocate at least several GB of memory.
如果您正在使用InnoDB，请确保缓冲池足够大。大多数服务器应至少分配几GB的内存。
#1
2
MySQL can take a while to warm up its internal caches. Remember, SQL_NO_CACHE means avoid the query cache only. The index cache is the most important from a performance perspective. If the index has not been read, there's a significant penalty the first time it's used.
MySQL可能需要一段时间才能使其内部缓存变暖。请记住，SQL_NO_CACHE意味着仅避免查询缓存。从性能角度来看，索引缓存是最重要的。如果未读取索引，则在第一次使用时会有重大损失。
If you're using InnoDB, which you should be, ensure that your buffer pool is sufficiently large. Most servers should allocate at least several GB of memory.
如果您正在使用InnoDB，请确保缓冲池足够大。大多数服务器应至少分配几GB的内存。