搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > molashaonian > MySQL只走一个索引,还是会索引合并?索引下推会怎样?

MySQL只走一个索引,还是会索引合并?索引下推会怎样?

molashaonian 2020-08-02

MySQL是否每次只能使用一个索引?

答案当然不是的,MySQL每次可以使用多个索引,即 index merge(索引合并),但大多数情况下都只会使用一个索引,那这是为什么咧?

1. 为什么会有index merge

  • MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描

  • 我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说,其实就是:对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)

  • 索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描,而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式:unions(合并)、intersections(交集)、unions-of-intersections(先取交集再合并)

  • 索引合并详细讲解可参考文末推荐阅读

2. 为什么很少见index merge

  • 与其说是“数据库查询只能用到一个索引”,倒不是说是 和全表扫描/只使用一个索引的速度比起来,去分析两个索引二叉树更加耗费时间,所以绝大多数情况下数据库都是是用一个索引。

  • 如这条语句:

select count(1from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'
  • 我们来想象一下当数据库有N个索引并且查询中分别都要用上他们的情况:
    查询优化器(用大白话说就是生成执行计划的那个东西)需要进行N次主二叉树查找[这里主二叉树的意思是最外层的索引节点],此处的查找流程大概如下:
    查出第一条column1主二叉树等于1的值,然后去第二条column2主二叉树查出foo的值并且当前行的coumn1必须等于1,最后去column主二叉树查找bar的值并且column1必须等于1和column2必须等于foo。
    如果这样的流程被查询优化器执行一遍,就算不死也半条命了,查询优化器可等不及把以上计划都执行一遍,贪婪算法(最近邻居算法)可不允许这种情况的发生,所以当遇到以下语句的时候,数据库只要用到第一个筛选列的索引(column1),就会直接去进行表扫描了。

  • 所以与其说是数据库只支持一条查询语句只使用一个索引,倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
    所以如上条的情况,最佳推荐是使用index(column1,column2,column3) 这种联合索引,此联合索引可以把b+tree结构的优势发挥得淋漓尽致:
    一条主二叉树(column=1),查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询,在二级二叉树查询到column2=foo后,去三级二叉树column3=bar查找。

  • 当然建了联合索引也需要看数据的分布,索引的可用度高低,尽量把区分度高的字段放前面,或者根据实际的业务场景来做出对字段的先后排序。

索引下推会怎样?

  • 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。

  • 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。

  • 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

  • 索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

推荐阅读(查看原文可访问)

MySQL 优化之 index merge(索引合并)
MySQL 8.0索引合并
Mysql性能优化:什么是索引下推?
使用MySQL 5.7虚拟列提高查询效率
Java 全栈知识体系

下篇笔录

SQL优化思想汇总

版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《MySQL只走一个索引,还是会索引合并?索引下推会怎样?》的版权归原作者「molashaonian」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注molashaonian微信公众号

molashaonian微信公众号:gh_b3172dc73dc1

molashaonian

手机扫描上方二维码即可关注molashaonian微信公众号

molashaonian最新文章

精品公众号随机推荐