高性能Mysql——Mysql通过基数判断放弃索引而走全表扫描

引入

有时候我们使用explain来做分析的时候，有时候明明是设置了索引，但是也还是不走索引，而是走了全表扫描。是因为Mysql做出了一个估计——走全表扫描的效率相比索引来说更高。

那么Mysql判断全表扫描更高效的依据有哪些呢？

我们假设数据库表是这样的：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c` (`c`) USING BTREE
) ENGINE=InnoDB;

我们在进行查询操作的时候，例如

select * from t where 100 < c and c < 100000;

然而有些时候，就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据。

这是为什么呢？

什么时候走全表扫描

我们知道，根据非聚集索引的原理，为了查询到某一个c的值，会先在非聚集索引上查找对应的主键，然后在聚集索引上查找对应的节点。所以，每个合适的c，都会在数据库上走两次索引。

而如果走全表扫描，每个c只会被查询到一次，而像100<c<100000的这种情况，在c的总量n小于某个数值的时候，显然是全表扫描比走两次索引来的快。我们可以计算一下，如果走两次索引，那么如果c均匀分布在100-100000的时候，那么会走大约200000次索引扫描。只要全表的长度小于200000次，那么全表扫描显然是效率更高的。扫描行数越少，意味着I/O操作的次数越少。

mysql数据库正是遵循了上面这个原理，他会估计哪种方式效率更高，IO次数更少，然后选择使用合适的查找方式。不过，mysql是如何估计的呢？

之前我们的假设有一个前提，前提是c均匀分布在某一个范围内，而数据往往不是这么均匀的，mysql也不可能像我们之前分析的那样，有一个数学公式来判断大于某个值就应该走索引之类的。

mysql如何预测判断

系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以呢，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。基数越小，越意味着c的均匀范围分布。

所以呢，一个索引的基数越大，意味着走索引查询越有优势。

那么问题来了，怎么知道这个索引的基数呢？

系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。

既然是基于统计的，那么如果样本有问题就可能导致结果有问题，也不能保证mysql是百分百确性走索引或者走全表扫描是效率最高的。也就是mysql也可能出现失误的情况。例如你采样的那一部分数据刚好基数很小，然后就误以为索引的基数很小。然后就呵呵，系统就不走 c 索引了，直接走全部扫描了。

所以呢，说了这么多，得出结论：由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。

不过呢，我们有时候也可以通过强制走索引的方式来查询，例如：

select * from t force index(a) where c < 100 and c < 100000;

既然会预测错索引的基数，这也意味着，当我们的查询语句有多个索引的时候，系统有可能也会选错索引哦，这也可能是 SQL 执行的很慢的一个原因。