MySQL: what is a sample page?
I recently ran into an issue where the plan EXPLAIN
for a MySQL query changed a lot and negatively affected the execution time of the query. In our efforts to fix the problem, we looked at increasing innodb_stats_sample_page
the default MySQL default 8. During my search, I found literature on the pros and cons of doing this, and how to choose the optimal value for this variable, but I couldn't find any explanation of what actually is sample page
.
Their purpose is clear:
The MySQL query optimizer uses estimated statistics about key distributions to select indexes for an execution plan based on the relative selectivity of the index. Certain operations force InnoDB to select random pages from each index in the table to estimate the cardinality of the index. (This technique is known as random dives.) These operations include the ANALYZE TABLE statement, the SHOW TABLE STATUS statement, and the first access to the table after a restart.
What the page doesn't explain what is sample page
. So what is it sample page
?
source to share
InnoDB stores all records inside a fixed-size block, which is usually called a "page" (although InnoDB sometimes calls it a "block"). All pages are currently the same size, 16KB.
https://dev.mysql.com/doc/internals/en/innodb-page-structure.html
source to share
A page is a block of memory that stores multiple records (for one table), the indexing algorithm can take a random page of a table (that is, records stored on any page of the table) to make some assumptions about all the records in the table. Therefore, one can guess how useful it would be to use a specific index on this table without analyzing each record.
Consult the documentation for details on page / block structure
Why a random page and not random posts? Since the page is stored in contiguous memory that can be accessed in a single memory operation, so in a memory operation, you can get more than one record.
How do you determine which page to analyze? Any page is taken as there is no specific page that is a better example than another page.
Will it always select the same page unless I change the records in that table? No, it is not, every time he tries to try this table, it will accept different random pages. But if there are no large differences between the records, different sampling pages may result in the same indexes being used. But if there are big differences (for example, at the beginning the table is null for a specific field, and then half of it was added with a non-null value for that field), a different index can be used.
source to share