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

MySQL为什么会选错索引

作者头像
只喝牛奶的杀手
发布2021-10-14 11:52:31
9820
发布2021-10-14 11:52:31
举报
文章被收录于专栏:只喝牛奶的杀手

优化器的逻辑

MySQL server层的优化器负责选择索引。而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

话外音:优化器也是根据抽样去选择index,复杂情况下抽样肯定会有问题。

扫描行数是怎么判断的

MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。

那么,MySQL 是怎样得到索引的基数的呢?这里,我给你简单介绍一下 MySQL 采样统计的方法。为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。

举个例子:

取n=3个数据页统计,假设共有10个索引数据页;page1:10个不同值;page2:20个不同值;page3:15 个不同值;索引基数=(10+20+15)/3*10=150;即n个数据页的平均不同值乘上总的页面数得到索引基数,其实就是一种随机取样统计,因为选择全部数据页取平均值代价太高不可取,被取得数据页就是样本。注:下面提到n其实有默认值,提到n=3只是为了更好理解计算过程。

数据行数的1/M,比如有10万条,M=10,那么结果就是10000,也就是说当变更的数据行数超过10000的时候,就会重新做一次索引统计。

话外音:涉及到cardinality数据量大都会精度丢失,比如:ElasticSearch近似聚合cardinality 。

使用 show index from table_name 命令,查看表索引的基数 2. 使用 analyze table table_name 命令,重新统计索引信息,解决采样导致的扫描行数出错的问题。

优化器优化时会考虑的因素:

  • 扫描行数
  • 是否使用临时表
  • 是否需要排序 话外音:涉及到这个点,都要考虑你的index 是否生效。

索引选择异常和处理

  • 采用 force index 强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价。
  • 可以考虑修改语句,引导 MySQL 使用我们期望的索引。语义的逻辑是相同的。
  • 在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。不需要的索引也可以通过ignore index的方式忽略。

什么情况要用force index ?

  1. SQL比较复杂的情况下,order by字段和where条件后面出现多种符合条件(本质造成扫描行数不准确,以及排序)
  2. 嵌套子查询(本质是临时表)

但是尽量不要用!force index也会有一些问题,不优雅,如果索引改了名字,这个语句也得改,还得发版,显得很麻烦。在这里就不说迁移数据库的问题了。

话外音:能通过调整索引和调整SQL语句的、甚至调整应用程序的都不要使用force index。


每周一句:你对别人怎么样,别人就对你怎么样。己所不欲,勿施于人!

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

本文分享自 只喝牛奶的杀手 微信公众号,前往查看

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

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

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