如何使用MySQL将表预加载到INNODB缓冲池中? [英] How to preload tables into INNODB buffer pool with MySQL?

问题描述

我有一个使用MySQL的电子商务应用程序，我希望它更快.在以前访问过的网站上访问部件#时，该部件会快速加载，因为所有必需的数据已经在INNODB缓冲池中.但是，如果部分#以前从未加载过，则该数据尚未存储在缓冲池中，因此需要从磁盘读取该数据，这很慢.我将INNODB缓冲池设置为2GB，并且整个数据库只有350MB，因此有足够的空间将整个数据库加载到缓冲池中.从INNODB统计数据中可以看到，目前仅使用了大约一半的缓冲池.

I have an e-commerce application that uses MySQL, and I'd like it to be faster. When a part # is accessed on the website that has been accessed before, the part loads quickly because all the necessary data is already in the INNODB buffer pool. However, if the part # has never been loaded before, that data isn't in the buffer pool yet, so it needs to be read from disk, and that is slow. I set my INNODB buffer pool to be 2GB, and this entire database is only about 350MB, so there is plenty of room to load the entire database in the buffer pool. I can see from the INNODB statistics that only about half the buffer pool is used right now.

我发现了有关预加载数据的引用，也称为预热"缓冲池，例如

I've found references to pre-loading the data, also known as "warming up" the buffer pool, such as Quickly preloading Innodb tables in the buffer pool or mysqldump.azundris.com/archives/70-Innodb-cache-preloading-using-blackhole.html. The strategy basically involves forcing a table scan on each table since MySQL doesn't have a native way for preloading the data.

我不想手动创建一个列出数据库中每个表的脚本，而必须这样做.我如何创建一个脚本，该脚本会自动执行并为每个表进行选择，并自动选择一个未索引的列，以便执行表扫描?

I don't want to manually create a script that lists every single table in my database and have to do this. How can I create a script that goes through and does a select for each table automatically, and automatically picks out a non-indexed column so that a table scan is performed?

推荐答案

这应该为您提供要运行的查询列表；)

This should give you a list of queries to run ;)

SELECT 
  CONCAT('SELECT ',MIN(c.COLUMN_NAME),' FROM ',c.TABLE_NAME,' WHERE ',MIN(c.COLUMN_NAME),' IS NOT NULL')
FROM
  information_schema.COLUMNS AS c
LEFT JOIN (
  SELECT DISTINCT
    TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
  FROM
    information_schema.KEY_COLUMN_USAGE
) AS k
USING
  (TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
WHERE
  c.TABLE_SCHEMA = 'yourDatabase'
  AND k.COLUMN_NAME IS NULL
GROUP BY
  c.TABLE_NAME

您可以将其放入存储过程中，并使用光标遍历结果集.在每一行中创建一个准备好的语句，然后执行.

You can put it into stored procedure, and go over the resultset with cursor. Create a prepared statement from each row, and execute.

这篇关于如何使用MySQL将表预加载到INNODB缓冲池中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！