阅读 248
SQL 索引

SQL 索引

索引是什么

  • 官方介绍索引是帮助MySQL高效获取数据数据结构,更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度
  • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)
  • 通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉树)的索引

image.png

索引的优势和劣势

优势:

  • 可以提高数据检索的效率,降低数据库IO成本,类似于书的目录

  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

    • 被索引的列会自动进行排序,包括单列索引组合索引,只是组合索引的排序要更复杂
    • 如果按照索引列的顺序进行排序,对应order by语句来说,效率会提高很多

劣势:

  • 索引会占据磁盘空间
  • 索引虽然会提高查询效率,但是会降低更新表的效率. e.g: 每次对表进行增删改操作,Mysql不仅要保存数据,还要保存或更新对应的索引文件

索引的使用

1. 索引的类型

  • 主键索引: 索引列中的值必须是唯一的,不允许有空值

    ALTER TABLE table_name ADD PRIMARY KEY (column_name);

  • 普通索引: MySQL中基本索引类型,没有什么限制,允许再定义索引的列中插入重复值和空值(非聚集索引)

    ALTER TABLE table_name ADD INDEX index_name (column_name);

  • 唯一索引: 索引列中的值必须是唯一的,但是允许为空值

    CREATE UNIQUE INDEX index_name ON table_name(column_name);

  • 全文索引: 只能在文本类型CHAR、VARCHAR、TEXT类型字段上创建全文索引,字段长度比较大时,如果创建普通索引,在进行like模糊搜索时效率比较低,这时可以创建全文索引 (MyISAM和InnoDB中都可以使用全文索引)

  • 空间索引: MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型,MySQL在空间索引方面遵循OpenGIS几何数据模型规则.MySQL - 空间数据结构

  • 前缀索引: 再问题贲类型如CHAR、VARCHAR、TEXT类型上创建索引时,可以指定索引列的长度,但是数值类型不能指定

ALTER TABLE table_name ADD INDEX index_name(column(length));

  • 按照索引的数量区分
    • 单列索引: 索引中只有一个列
    • 组合索引: 使用两个以上字段创建的索引
  • 组合索引的使用,需要遵循最左前缀原则(最左匹配原则)
  • 一般情况下,建议使用组合索引代替单列索引(主键索引除外)

ALTER TABLE table_name ADD INDEX index_name(column1, column2);

索引的数据类型选择

索引的数据类型需要满足两个场景

场景一 等值查询: 根据某个值查找数据(最好的数据结构应该是hash, 时间复杂度O(1))

SELECT * FROM table_name WHERE id = 1

场景二 范围查询:根据某个范围区间查找数据

SELECT * FROM table_name WHERE id < 10

同时需要考虑时间和空间因素,在执行时间方面,我们希望通过索引,查询数据的时间尽可能小;在存储空间方面,我们希望索引不要消耗太多的内存和磁盘空间

需要同时满足两个场景时,可以使用二叉查找树(二叉排序树);

  • 特点: 大于根节点的数据排在右子树上,小于根节点排在左子树上,根结点的选取,第一个添加到树中的元素,理想状态下的时间复杂度: O(log2n)
  • 存在问题: 当根节点选择不合理时,可能由树退化成链表

image.png

为了预防这种问题,可以选择平衡二叉查找树

  • 特点: 时刻保持二叉树的左右子树的高度差小于等于1,如果大于1进行左旋或者右旋操作来保持平衡

时间复杂度: O(log2n)

  • 问题: MySQL的数据是存储在磁盘文件中的,查询处理数据时,需要先吧磁盘中的数据加载到内存中,磁盘IO操作非常耗时,访问二叉树的每个节点都会发生一次IO,当数据量过大时,从根节点遍历到叶子结点,需要多次磁盘IO(树的高度比较高)
  • 解决方案: 让树的高度降低,那就不要局限于二叉树了,让这颗树分更多的

e.g:key为bigint=8字节,每个节点有两个指针,每个指针为4字节,一个节点占用空间16字节,在InnoDB引擎中,一次IO会读取一页16K的数据量,而二叉树一次IO的有效数据量只有16字节,利用率极低

image.png

使用b-tree (b树,不是b-树)

  • 特点: 每个节点中保存多个元素,每个元素的左右两侧各有一个指针,指向下一个节点(左边元素小于根节点,右边元素大于等于根节点)
    1. b树的节点中存储着多个元素,每个内节点有多个分叉
    2. 节点中的元素包含键值和数据,节点中的键值从大到小排列(所有的节点都储存数据)
    3. 父节点当中的元素不会出现在子节点中
    4. 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接
  • 问题:
    1. 节点的度,取决于数据行的大小,数据页的大小固定为16K,数据行大的时候,度会变小(原因:中间节点保存了数据,占了过多的空间);
    2. 范围查询时效率不高(需要在父子磁盘块间 - 反复横跳)

image.png

终极解决方案: b+tree

  • 特点:
    1. 中间节点不保存数据,只包含主键信息,所有数据放到叶子结点中 - 解决节点占用空间过大
    2. 每个叶子结点通过双向指针链接 - 范围查询时,可以通过双向指针横向遍历直接跳转到下一个存放数据的磁盘块(数据有序存储)

MySQL中索引的存储形式

1.MyIsam引擎

  • 索引放到.MYI文件中,索引格式(b+tree结构):叶子节点中存储对应数据行的指针(偏移量),找到索引后还需要一次IO通过指针从数据文件中取出

image.png

  • 数据放到.MYD文件中

image.png

2.InnoDB引擎

  • .ibd文件中保存索引和数据信息,索引和数据保存在同一个文件中,
    • 主键索引:叶子结点就是记录行,存储完整的数据信息,根据主键索引查询,效率高于MyIsam引擎(IO次数少一次),因为数据和索引聚集存储在一起,所以也叫聚集索引/聚簇索引
    image.png
    • 辅助索引:辅助节点中,叶子节点保存主键信息,查询数据需要根据辅助索引找到对应的主键信息,然后根据主键索引找到对应的记录, 查询主索引的过程叫做回表, 效率低于MyIsam引擎

image.png

文章分类
后端
文章标签