Indexing for High Performance - High Performance MySQL - page 197

InnoDB calculates statistics for indexes when tables are first opened, when you run
ANALYZE TABLE , and when the table's size changes significantly (a size change of 1/16th
or 2 billion row insertions, whichever comes first).
InnoDB also calculates statistics for queries against some INFORMATION_SCHEMA tables,
SHOW TABLE STATUS and SHOW INDEX queries, and when the MySQL command-line client
has autocompletion enabled. This can actually become a pretty serious problem on
large servers with lots of data, or when I/O is slow. Client programs or monitoring tools
that cause sampling to occur can cause a lot of locking and heavy load on the server,
as well as frustrating users with slow startup times. And you can't observe the index
statistics without changing them, because SHOW INDEX will update the statistics. You
can disable the innodb_stats_on_metadata option to avoid all of these problems.
If you're using Percona Server, which includes Percona XtraDB instead of standard
InnoDB, you can configure the behavior further. The innodb_stats_auto_update option
lets you disable auto-resampling, effectively freezing statistics unless you run ANALYZE
TABLE manually. This can help if you're struggling with unstable query plans. We cre-
ated this feature at the request of some customers with very large deployments.
For even more query plan stability, and for faster system warmups, you can use a system
table to store index statistics so they are stable across server restarts and don't need to
be recomputed when InnoDB opens the table for the first time after booting up. This
feature is available in Percona Server 5.1 and in the development milestone releases of
standard MySQL 5.6. The Percona Server feature is enabled with the innodb_
use_sys_stats_table option, and there will also be index statistics persistence in
MySQL 5.6, controlled by the innodb_analyze_is_persistent option.
If you configure your server not to update index statistics automatically, you need to
do it manually with periodic ANALYZE TABLE commands, unless you know that the sta-
tistics won't change in ways that will create bad query plans.
Reducing Index and Data Fragmentation
B-Tree indexes can become fragmented, which might reduce performance. Fragmented
indexes can be poorly filled and/or nonsequential on disk.
By design B-Tree indexes require random disk accesses to “dive” to the leaf pages, so
random access is the rule, not the exception. However, the leaf pages can still perform
better if they are physically sequential and tightly packed. If they are not, we say they
are fragmented, and range scans or full index scans can be many times slower. This is
especially true for index-covered queries.
The table's data storage can also become fragmented. However, data storage fragmen-
tation is more complex than index fragmentation. There are three types of data
fragmentation: