如何使用MySQL将表预加载到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.

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

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.

我发现了预加载数据的引用，也称为“预热”缓冲池，例如在缓冲池中快速预加载Innodb表或mysqldump.azundris.com/archives/70-Innodb-cache-preloading-使用-blackhole.html。该策略基本上涉及在每个表上强制执行表扫描，因为MySQL没有用于预加载数据的本机方式。

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?

我不想手动创建一个脚本，列出我的数据库中的每个表，并且必须这样做。如何创建一个脚本，并自动为每个表执行选择，并自动选取一个非索引列以执行表扫描？

2 个解决方案

#1

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.

您可以将它放入存储过程，并使用光标检查结果集。从每一行创建一个准备好的语句，然后执行。

#2

This query will return table names in your database, so you don't have to input them manually:

此查询将返回数据库中的表名，因此您不必手动输入它们：

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database name'

Then for each table name force table scan.

然后为每个表名强制表扫描。

#1