玉面飞龙的BLOG

There’re a  few different optimizations possible when dealing with indexes in MySQL. The first is rebalancing and optimizing the fill factor for index trees. This is done with the OPTIMIZE TABLE command when table is DML active and has lots of fragment and we can do it in maintains window.

A second operation that could be performed periodically is ANALYZE on the table. When ANALYZE is performed on a table, it will udate the internal planner stats that MYSQL uses to execute a query. This operation is also done automatically when an OPTIMIZE is performed on a table as well.

The storage engine being used influences how it does this collection and what impacts are on availability when run.MyISAM automatically maintains stats as INSERT and DELETE is performed on rows from a table, so the stats are normally pretty well up-to-date at any given point. As as result, it is not necessary to analyze tables very frequently. When it is done, it will put a read lock in the table as it reads through the entire table updating these planner stats. It will take about the same amount of time as doing a full table scan. This is  warm operation.

The maintaince of planner stats works a bit different with InnoDB. First off, InnoDB only updates the stats when 1/16 of the table changes. So if there was a million rows in the table, after 62500 data changes, it would automatically redo the planner stats to keep them updated. InnoDB works by sampling the indexes so it is much faster than the full read that MyISAM does. It also makes use of the non-locking read to do this, so it its a totally hot operation.

以上摘录自MYSQL官方培训文档，说明MYSQL可以根据存储模式的不同采用不同方式自动维护统计信息。经过测试，InnoDB的十六分之一也只是个近似。

由于算法限制，innoDB类型的表统计信息并不能够100%准确，也说明在面对复杂查询的时候，MYSQL要无力的多，hint便更是上上之选。

ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.

MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using ANALYZE TABLE. In the few cases that ANALYZE TABLE does not produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set the max_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans.

DBA在MYSQL上SQL Design&Review的时候更要懂的自己的数据–简约而不简单 。