MySQL为什么有时候会选错索引？

MySQL为什么有时候会选错索引？

//

最近在极客时间看丁奇大佬的《MySQL45讲》，真心觉得讲的不错，把其中获得的一些MySQL方向的经验整理整理分享给大家，有兴趣同学可以购买相关课程进行学习。

今天分享的内容是MySQL为什么有时候会选错索引？

先给出一个结论：在一些不断删除历史数据和新增数据的场景下，MySQL会出现选错索引的情况。

MySQL的优化器是负责选择一个最优的执行方案去执行一个SQL，某个SQL在执行的过程中，扫描的行数越少，那么这个SQL的执行效率就越高。当表中有多个索引时，应用每个索引需要扫描的行数都是不同的。

这里我们需要确定SQL的扫描行数是怎么确定的？

当表中有多个索引时，MySQL在执行某个特定的SQL前，并不能知道使用当前索引来执行SQL要扫描的行数是多少，而是只能根据索引的统计信息来估算这个SQL可能需要访问的行数。我们知道，索引的基数决定了索引的使用效果，当索引的基数较大时，索引的区分度比较高，扫描的行数会比较少。那么MySQL究竟是如何获得一个表的索引统计信息的？

MySQL在采样统计的时候，会默认选择N个数据页，然后统计这个数据页上的不同值的个数，然后取平均值，再乘以这个索引的总数据页数量，就得到了索引的基数，这个基数是个估计值，不准确。

当某个SQL变更的数据行通过1/M时，会重新维护一次索引统计信息。MySQL中使用参数innodb_stats_persistent来控制索引统计信息的保存位置：

当该值为on，则统计信息会持久化存储，此时采样系数N=20，变更系数M=10；

当该值为off，则统计信息保存在内存中，此时采样系数N=8，变更系数M=16；

在一个频繁进行删除和插入的表中，统计信息很可能会出现不准确的情况，在这种情况下，我们应该怎么办？

1、可以使用analyze table t的方法来对表的索引信息做重新统计，提高索引统计信息的准确性，该过程没有修改表的数据，只是对表加了MDL读锁。

2、optimize table t，该方法会重建表并且重新统计表的索引信息。

如果还是不能解决问题，那么可以考虑采用下面3种方法：

3、使用force index的方法来强制SQL使用某个索引来执行。

4、考虑修改语句，引导MySQL使用我们期望的索引。

5、删除指定索引，重建其他更合适的索引来进行替代。

//

怎么给字符串字段加索引？

//

假设我们有一个邮箱的登录系统，每个用户都需要输入邮箱和密码来进行登录，为了找到对应的邮箱账号和密码，势必会需要在MySQL的表里面为邮箱字段建立索引。那么形如yeyz@mail.cn的邮箱账号，如何添加索引才是最好的呢？？？

这便是本节需要讨论的问题。

方案一：在email字段上添加索引。

该方法会保证email全字段都建立索引，当我们查询一个邮箱账号的时候，仅仅需要找到email二级索引B+树上的响应记录，然后回表到聚集索引查询密码即可。

方案二：在email字段的前若干个字符上添加索引

该方法可以节省二级索引B+树上的字节数，但是带来的问题是可能扫描到很多无效的索引值。例如我们要查看email为zhangsan@mail.cn的记录时，如果在email的前6位上创建了索引，需要在二级索引的B+树上找到索引值为"zhangs"的所有记录,但是满足"zhangs"开头的索引记录可能有很多，例如zhangsan01、zhangsi、zhangshi等等，这就导致我们可能需要将一些冗余的记录进行“回表”查询。所以：使用前若干个字符添加索引的方案减少了索引的大小，但是有可能增加"回表"的成本。除此之外，前缀索引还有可能导致"覆盖索引"无法应用。

如何解决"回表"成本增加的问题？

我们可以使用下面的方法：

1、先使用select count(distinct email) from t;的方法统计出来表的所有不重复记录S。

2、然后select count(distinct left(email,N)) from t；统计出来取email字段最左边N个字符的不重复记录M，其中N可以从某个小值开始，一直增加，直到不重复记录M和总的记录S几乎相等，这样可以最大程度上减少email字段上索引的字节数，而不会带来大量冗余的"回表"操作。