MySQL的索引知识笔记

MySQL的索引知识笔记

前言

文章首先介绍MySQL的整体大致架构，了解我们执行一句SQL是怎样一个逻辑，可以有一个宏观的认识。接着围绕索引知识来逐步深入到一些数据结构的实现，然后又包含应用层面的东西，使得文章不是太抽象。

MySQL整体架构

返回目录

整体架构

• 客户端
  • 人为干预
    • 连接器:采用数据库连接池，减少频繁的开关连接
    • 优化器:可以查看sql语句的执行计划，可以采用对应的优化策略，来加快查询
    • 存储引擎的选择
  • 连接器
    • 连接器负责和客户建立连接，获取权限、维持和管理连接
    • 连接可以分为两类
      • 长连接：周期性断开的长连接，推荐使用
      • 短连接
  • 缓存查询
    • 当执行查询语句的时候先查询缓存，如果命中就直接返回结果；否则继续
    • 不推荐缓存：
      • 容易造成性能浪费：如果表经常进行更新，那么就会清空之前的缓存并且加载新的缓存
  • 分析器
    • 语法分析：
      • 根据词法规则判断这个sql语句是否满足mysql语法，如果不符合就会报出相关错误
    • 词法分析，语法分析，就是找出字符串对应的数据
      • 把字符串T识别成表名T
      • 把字符串ID识别成列ID
  • 优化器
    • 在具体执行sql语句之前，要经过优化器的处理
      • 当表中有多个索引的时候，决定用哪个索引
      • 当sql语句需要做多表关联的时候，决定表的连接顺序
    • 不同的执行方式对SQL语句的执行效率影响比较大
      • RBO：基于规则的优化
      • CBO：基于成本的优化
  • 执行器
    • SQL语句的实际执行组件

存储引擎

• 存储引擎：不同的存放位置，不同的文件格式
  • InnoDB 磁盘
  • MyISAM 磁盘
  • Memory 内存
  • 补充：不同的存储引擎，数据文件和索引文件存放的位置是不同的，因此有了分类
    • 聚簇索引：数据和文件放在一起(innodb)
      • .frm 存放的是表结构
      • .ibd 存放数据文件和你和索引文件
      • 注意：mysql的innodb存储引擎默认情况下会把所有的数据文件放到表空间中，不会为每一个单独的表保存一份数据文件，如果需要将每一个表单独使用文件保存，设置如下属性：set global innodb_file_per_table=on;
    • 非聚簇索引：数据和索引单独一个文件(MyISAM)
      • .frm 存放表结构
      • .MYI 存放索引数据
      • .MYD 存放实际数据

MySQL索引

返回目录

索引基础

索引是什么？

• 索引是存储在IO磁盘的一个文件，里面就是一个以索引列作为顺序逻辑判断的B+树的数据结构
• 索引就是为了加快对数据搜索，可以减少对磁盘的IO次数
• 索引在SQL查询中是非显示的，如果查询的表中包含索引列，数据库就会根据索引自动起作用

索引怎么创建？

• 自动创建：当在表上定义一个PRIMARY KEY或者UNIQUE约束的时候，Oracle数据库会自动创建一个对应的唯一索引。
• 手动创建：用户可以创建索引来加速查询。

索引中和计算机原理相关的知识

我们将磁盘中一个4K的空间称之为页，通过页读取的速度会更加快。所以数据库索引在进行索引的遍历的时候也是一页一页的读取。

哈希表

• hash
  • 哈希表可以完成索引的存储，每次再添加索引的时候需要计算指定列的hash值，取模运算后计算出下标，将元素插入下标位置即可
  • 适用场景：
    • 等值查询
    • 表中的数据是无序数据(范围查找的时候比较浪费时间，需要挨个进行遍历操作)
  • 注意：在企业中多数查询还是等值查询，所以hash不是非常适合作为数据库的索引

树

• 树

  • 多叉树
  • 二叉树
    • 树并不是平衡的，容易形成长链表，这样子的话查询的效率可能就是时快时慢，非常不稳定，并且平均的查询效率就会比较低
  • AVL树(平衡树)
    • AVL树是一颗严格意义上的平衡树，元素的查询效率会比较高，但是在进行元素插入的时候会进行1到N次的旋转，严重影响插入性能
  • 红黑树
    • 红黑树是基于AVL树的一个升级，通过损失了部分查询性能来提升插入的性能，因为红黑树在树失去平衡的时候并不会马上进行旋转来保持树的平衡，只要红黑树中最低的子树和最高字数之差小于2倍

• 总结：二叉树的深度无法控制并且插入数据的性能比较低，所以这些树满足不了索引对查询速度和插入速度的需求

B树

• B树
  • 搜索有可能在非叶子结点结束，性能逼近二分查找
  • 所有键值分布在整颗树中，这是相比于B+树的劣势，导致每次读取磁盘的节点数量变少
  • 每个节点最多拥有m个子树
  • 分支结点至少拥有m/2颗子树(除了根节点和叶子结点外都是分支节点)
  • 所有叶子节点都在同一层、每个节点最多可以有 m-1 个key，并且可以升序排列

图解：

• 一个节点上有两个升序排序的关键字和三个执行子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键字划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为16和34，P1指针指向的子树的数据范围都是小于16，P2指向的子树的数据范围都是在16~36之间的，P3指向的子树的数据范围为大于34。

缺点

• 由于每个节点都有key,同时包含data，如果data的数据类型是长字符串或者比较大的数据类型的话就会导致下问题发生
  • 每个节点可以存放的节点数量变少
  • 当值树的深度减少

B+树

基本实现逻辑和B树差不多，下面介绍一下B+树的改进

• 主要改进：非叶子节点仅仅存储key，但是不会进行数据的存储，优化结果如下：
  • 每个节点可以包含的key更加多
  • 树的深度变小
  • 每次磁盘读取的结果中会有更多key，使得能够更快的接近目标
• 改进：叶子节点两两指针互相连接
  • 顺序查询的性能更加高

注意：在B+Tree上有两个头指针，一个指向关键字最小的叶子节点

索引种类

返回目录

mysql的索引有五种分类：主键索引、唯一索引、普通索引、全文索引、组合索引。通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。

• 主键索引
  • 一张表的主键上会自动创建一个主键索引
• 唯一索引
  • 有着唯一约束(unique)的字段上可以拥有的索引
• 普通索引
  • 基本的索引类型，值可以为空，没有唯一性的限制。
  • (覆盖索引)，比较特殊的情况，需要在查询的结果视图中包含主键字段
    • 逻辑推断：因为普通索引中的data中的数据通常是**(普通索引字段,主键)**的形式，所以如果结果视图中包含两者之外的字段的话就需要在遍历了普通索引树之后找到主键key，然后再到主键树中进行遍历查找所有字段的视图，然后返回结果视图需要的字段
    • 覆盖索引参考博客
    • 示例：
• 全文索引
  • 全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建
  • MyISAM和InnoDB在5.6版本之后都是支持的
• 组合索引
  • 多列值组成一个索引，专门用于组合搜索(最左匹配原则)
  • 组合索引解释博客

其他补充

返回目录

索引下推

就是在准备回表的时候对后面的条件再次判断，从而减少回表的次数。

知识点补充：做了索引的话其实做了两件事

• 每次查询数据库的时候需要先对索引进行一波遍历
  • 这个还要考虑你的存储引擎：
    • InnoDB:索引和数据存储在一个文件中，只需要对一个文件进行处理
    • MyISAM:索引和数据存储在不同的文件中，需要对两个文件进行处理
• 公司里建议将主键设置成自增的，并且设置索引
• 索引的维护是比较麻烦的事情，建立索引的时候需要三思
• 如果对多个类都进行了索引，就会进行多次的索引B+树的遍历。。。我们对这个过程有一个专业的名词对这个进行描述，叫做回表