泪梦红尘blog

泪梦红尘blog

关注
发信
关注(28)粉丝(399)

MySQL优化之索引解析

索引的本质

MySQL索引或者说其他关系型数据库的索引的本质就只有一句话，以空间换时间。

索引的作用

索引的分类

数据结构上面的分类

  • HASH 索引

    1. 等值匹配效率高
    2. 不支持范围查找

  • 树形索引

    1. 二叉树，递归二分查找法，左小右大

    2. 平衡二叉树，二叉树到平衡二叉树，主要原因是左旋右旋

      1. 缺点1，IO次数过多
      2. 缺点2，IO利用率不高，IO饱和度

    3. 多路平衡查找树（B-Tree）

      1. 特点，大大的减少了树的高度

    4. B+树

      1. 特点，采用左闭合的比较方式

      2. 根节点支节点没有数据区，只有叶子结点才包含数据区（说白了就是即便在根节点和子节点已经定位到，因为没有数据区的原因也不会停留，会一直找到叶子结点为止。）

        • 当我们搜索13这条数据时，在根节点和子节点 都能定位，但是一直会找到叶子结点。

        MySQL优化之索引解析-LMLPHP

    二叉树平衡二叉树，B树对比

MySQL优化之索引解析-LMLPHP

B树和B+树比较

MySQL优化之索引解析-LMLPHP

  • B+树在B树上面的优化

    1. IO效率更高（B树每个节点都会保留数据区，而B+树则不会，假设我们查询一条数据要遍历三层，那么显然B+树查询中IO消耗更小）

    2. 范围查找效率更高（如图，B+树已经形成了一个天然链表形式，只需要根据最结尾的链式结构查找）

      MySQL优化之索引解析-LMLPHP

    3. 基于索引的数据扫描效率更高。

索引类型的分类

  • 索引类型可分为两类:

    1. 主键索引
    2. 辅佐索引(二级索引)
      1. 唯一性索引
      2. 复合索引
      3. 普通索引
      4. 覆盖索引

B+树在储存引擎层面落地

  • 我们创建两个表分别为test_innodb（采用InnoDB作为储存引擎）test_myisam（采用MyISAM作为储存引擎）下图是两张表磁盘落地的相关文件，这两个储存引擎在B+树磁盘落地式截然不同的。

    MySQL优化之索引解析-LMLPHP

B+树在MyISAM落地
  • *.frm文件是表格骨架文件比如这个表中的id字段name字段是什么类型的存储在这里
  • *.MYD（D=data）则储存数据
  • *.MYI （I=index）则储存索引

MySQL优化之索引解析-LMLPHP

  • 比如现在执行如下sql语句 ，那么在MyISAM中他就是先在test_myisam.MYI中查找到103然后拿到0x194281这个地址然后再去test_myisam.MYD中找到这个数据返回。

    SELECT id,name from test_myisam where id =103

    MySQL优化之索引解析-LMLPHP

  • 如果test_myisam表中，id为主键索引，name也是一个索引，那么在test_myisam.MYI中则会有两个平级的B+树，这也导致MyISAM引擎中主键索引和二级索引是没有主次之分的，是平级关系。因为这种机制在MyISAM引擎中，有可能使用多个索引，在InnoDB中则不会出现这种情况。

B+树在InnoDB落地

​MySQL优化之索引解析-LMLPHP

MySQL优化之索引解析-LMLPHP

  • InnoDB不像MyISAM来独立一个MYD 文件来存储数据，它的数据直接存储在叶子结点关键字对应的数据区在这保存这一个id列所有行的详细记录。

  • InnoDB 主键索引和辅助索引关系

    我们现在执行如下SQL语句,他会先去找辅助索引，然后找到辅助索引下101的主键，再去回表（二次扫描）根据主键索引查询103这条数据将其返回。

    SELECT id,name from test_myisam where name ='zhangsan'

    这里就有一个问题了，为什么不像MyISAM在辅助索引下直接记录磁盘地址，而是要多此一举再去回表扫描主键索引，这个问题在下面相关面试题中回答，记一下这个问题是这里来的。

MySQL优化之索引解析-LMLPHP

相关面试题

  • 为什么MySQL选择B+树作为索引结构

  • B+树在MyISAM和InnoDB落地区别。

  • 如何判断一条sql语句是否使用了索引。

  • 为什么主键索引最好选择自增列？

  • 为什么经常变动的列不建议使用索引？

  • 为什么说重复度高的列，不建议建立索引？

  • 什么是联合索引

  • 什么是覆盖索引

  • 什么是ICP机制

  • 在InnoDB表中不可能没有主键对还是不对原因是什么？

    1. 首先这句话是对的，但是情况有三种:
      1. 就是在你手动显式指定这一个字段为主键时候，会以这一个字段为聚集索引。
      2. 在没有显式指定主键时候有两种情况：
        1. 他会寻找第一个UK（unique key）作为主键索引组织索引编排。
        2. 如果既没有指定主键也没有UK的情况下，此时会以rowId（在InnoDB表中每一个记录都会有一个隐藏（6byte）的rowId）为聚集索引。

  • 什么是回表操作

    在InnoDB 中基于辅助索引查询的内容，从辅助索引中无法直接获取，需要基于主键索引的二次扫描的操作叫做回表操作。

  • 为什么在InnoDB 中辅助索引叶子结点数据区记录的是主键索引的值而不是像MyISAM中去记录磁盘地址。

    1. 这个原因其实很简单，因为主键索引的数据结构是会经常发生变化的，如果在辅助索引数据区记录磁盘地址，那么假设我们有10个辅助索引，当我们主键索引结构发生变化后，还要一个个去通知辅助索引，且主键索引结构是经常发生变化的，增删都有可能影响他的数据结构。
      版权归属： 泪梦红尘
      本文链接： https://www.bss2.com/archives/mysql-opt-index
03-15 19:18
Powered by LMLPHP ©2024 泪梦红尘blog 0.037180