前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL为什么有时候会选错索引?

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

作者头像
AsiaYe
发布2020-05-08 15:55:18
1.2K0
发布2020-05-08 15:55:18
举报
文章被收录于专栏:DBA随笔
//

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字段上索引的字节数,而不会带来大量冗余的"回表"操作。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-05-06,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档