MySQL索引选择问题

  在进行数据查询的时候，MySQL优化器会自动为查询语句选择合适的索引，索引选择的正确与否对MySQL的执行速度有很大影响。使用explain语句可以查询得到执行计划的详细内容，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少，当然除了扫描行数之外也有其他考虑因素，包括是否需要回表等。
  那么MySQL怎么在执行前预判扫描行数呢？首先一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好，可以通过show index from t来查看索引的详细信息。MySQL通过采样的方式来估算每张表的基数，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。
  在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

  设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
  设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。
  可以看到无论是上面哪一种，对于数据量比较大的表来说，采样的数量都很小，所以并不能保证统计的准确，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以用analyze table t来更新统计信息。

  当选错索引时，可以采取的办法有：

1. 采用force index强行选择一个索引
2. 可以考虑修改语句，引导MySQL使用我们期望的索引
3. 可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

MySQL实战45讲