我正在嘗試預熱緩衝池。執行查詢並將輸出重定向到 /dev/null 就足夠了嗎？

• 例如 mysql -u username -p -e “select id from tbl where 1=1” > /tmp/results.txt

謝謝

我看到問題中的一條評論提到記憶體索引

請注意，緩衝池中只會記憶體一個索引（很可能是 PRIMARY，但請檢查 EXPLAIN 以確定）。您可能還需要記憶體中的二級索引，因此您必須執行額外的 SELECT

碰巧我有文章Feb 04, 2012讓您進行這些查詢（MySQL 暖程序）。從那篇文章中，以下是顯示您需要執行的所有查詢的查詢：

載入 INNODB 緩衝池

SELECT DISTINCT
   CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
   ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
   FROM
   (
       SELECT
           engine,table_schema db,table_name tb,
           index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
       FROM
       (
           SELECT
               B.engine,A.table_schema,A.table_name,
               A.index_name,A.column_name,A.seq_in_index
           FROM
               information_schema.statistics A INNER JOIN
               (
                   SELECT engine,table_schema,table_name
                   FROM information_schema.tables WHERE
                   engine='InnoDB'
               ) B USING (table_schema,table_name)
           WHERE B.table_schema NOT IN ('information_schema','mysql')
           ORDER BY table_schema,table_name,index_name,seq_in_index
       ) A
       GROUP BY table_schema,table_name,index_name
   ) AA
ORDER BY db,tb

載入 MyISAM 密鑰記憶體

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER 
BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT 
B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema
NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb
;

載入 INNODB 緩衝池和 MyISAM 密鑰記憶體

SELECT DISTINCT
   CONCAT('SELECT ',ndxcollist,' FROM ',
   db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
   SELECT
       engine,table_schema db,table_name tb,index_name,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
   FROM (
       SELECT
           B.engine,A.table_schema,A.table_name,
           A.index_name,A.column_name,A.seq_in_index
       FROM
           information_schema.statistics A INNER JOIN
           (
               SELECT engine,table_schema,table_name
               FROM information_schema.tables
               WHERE engine IN ('InnoDB','MyISAM')
           ) B USING (table_schema,table_name)
       WHERE
           B.table_schema NOT IN ('information_schema','mysql')
           AND A.index_type <> 'FULLTEXT'
       ORDER BY
           table_schema,table_name,index_name,seq_in_index
       ) A
   GROUP BY
       table_schema,table_name,index_name
) AA
ORDER BY
   engine DESC,db,tb
;

只需將輸出重定向到 /root/IndexWarmup.sql 等文本文件

然後，登錄到mysql並執行

mysql> source /root/IndexWarmup.sql

可選建議

我強烈建議升級到 MySQL 5.6。為什麼？您可以執行以下操作：

轉儲和載入 InnoDB 緩衝池關閉和啟動的地圖

[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

按需轉儲和載入 InnoDB 緩衝池的映射

mysql> set global innodb_buffer_pool_dump_now = 1;
mysql> set global innodb_buffer_pool_load_now = 1;

這樣，您就不必將索引頁讀入您從未訪問過的緩衝池中。

請參閱 MySQL 文件

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_dump_now
• innodb_buffer_pool_load_now

引用自：https://dba.stackexchange.com/questions/63459