Rechercher dans le manuel MySQL
8.10.2.4 Index Preloading
If there are enough blocks in a key cache to hold blocks of an entire index, or at least the blocks corresponding to its nonleaf nodes, it makes sense to preload the key cache with index blocks before starting to use it. Preloading enables you to put the table index blocks into a key cache buffer in the most efficient way: by reading the index blocks from disk sequentially.
Without preloading, the blocks are still placed into the key cache as needed by queries. Although the blocks will stay in the cache, because there are enough buffers for all of them, they are fetched from disk in random order, and not sequentially.
To preload an index into a cache, use the
LOAD INDEX INTO
CACHE
statement. For example, the following
statement preloads nodes (index blocks) of indexes of the
tables t1
and t2
:
- +---------+--------------+----------+----------+
- +---------+--------------+----------+----------+
- +---------+--------------+----------+----------+
The IGNORE LEAVES
modifier causes only
blocks for the nonleaf nodes of the index to be preloaded.
Thus, the statement shown preloads all index blocks from
t1
, but only blocks for the nonleaf nodes
from t2
.
If an index has been assigned to a key cache using a
CACHE INDEX
statement,
preloading places index blocks into that cache. Otherwise, the
index is loaded into the default key cache.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-index-preloading.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.