1.16 MySQL Statistics

1. What Are MySQL Statistics MySQL query cost is calculated based on statistics 2. What Statistics Are There 2.1. Persistent Disk-Based Statistics - These statistics are stored on disk, meaning they remain after the server restarts. 2.1.1. Storage Location - Stored in two tables - innodb_table_stats stores statistics about tables, with each row corresponding to one table's statistics - innodb_index_stats stores statistics about indexes, with each row corresponding to one statistical item for an index

DatabasesCreated 06/07/2021Updated 07/18/20211 min readhistorical

This is a historical learning note and may contain outdated or incomplete understanding.

1. What Are MySQL Statistics

MySQL query cost is calculated based on statistics.

2. What Statistics Are There

2.1. Persistent Disk-Based Statistics

These statistics are stored on disk, meaning they remain after the server restarts.

2.1.1. Storage Location

Stored in two tables
- innodb_table_stats stores statistics about tables. Each row corresponds to the statistics of one table.
- innodb_index_stats stores statistics about indexes. Each row corresponds to one statistical item for one index.

2.1.2. How to Update Them

Enable innodb_stats_auto_recalc.
- When the number of changed records exceeds 10% of the table size, the statistics are updated.
Manually invoke the ANALYZE TABLE statement to update statistics.

2.2. Non-Persistent Memory-Based Statistics

These statistics are stored in memory. When the server is shut down, these statistics are cleared. After the server restarts, they are collected again only in certain appropriate scenarios.