MySQL索引

四、索引

索引概述、索引结构、索引分类、索引语法、SQL性能分析、索引使用、索引设计原则

1. 索引概述

  1. 索引(index)是帮助MySQL高效获取数据的数据结构(有序)。

  2. 优势:提高数据检索的效率,降低数据库IO成本,通过索引排序,降低数据的排序成本,降低cpu的消耗

    缺点:索引也是占数据空间的,索引大大提高了查询效率但同时降低了更新表的速度,insert,update,delete

2.索引结构

索引是在存储引擎层实现的,不同的存储引擎有不同的结构

  1. B+Tree索引,最常见的索引类型,大部分引擎都支持B+树索引

  2. Hash索引,底层数据结构是用哈希表实现的,只有精确匹配索引列的查询有效,不支持范围查询,

  3. R-tree空间索引,空间索引是MySAM引擎的特殊引用类型,主要用于地理空间数据类型

  4. Full-text全文索引,是一种通过建立倒排索引,快速匹配文档格式。类似于Luncene,Solr,es

基本所有的存储引擎都支持b+树

  1. B-Tree(多路平衡查找树),每个节点可以有多个子节点,指针个数比 节点个数多1.

    达到节点个数中间节点会向上分裂,(5阶每个节点最多存储4个key)

  2. B+Tree,每个节点都在叶子节点中,叶子节点之间形成一个单向链表,上面非叶子节点只是起到索引作用

    在MySQL中索引结构对B+Tree进行了优化,增加一个指向相邻叶子节点的链表指针,提高区间访问性能

    键值,数据,指针,页/块(16k)

  3. Hash索引结构和HashMap底层原理一样,只能用于查询,一次检索就可以了,效率高于B+Tree,支持Hash索引的是Memory,而InnoDB具有自适应哈市功能。

  4. 为什么InnoDB存储引擎选择使用B+Tree索引结构?

    1. 二叉树顺序插入会形成链表结构,红黑树虽然有所优化,但本质还是二叉树,

    2. 相对于二叉树,在一定数据的情况下,B+Tree层级更少,搜索效率高,

    3. 对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针也跟着减少,同样保存大量的数据,只能增加树的高度,导致性能降低,

    4. 相对Hash索引,B+Tree支持范围匹配及排序操作,hash只能用于单次查询。

3.索引分类

  1. 主键索引:针对于表中主键创建的索引,默认自动创建,只能有一个,primary

  2. 唯一索引:避免同一列中某个数据列中的重复值,可以有多个,unique

  3. 常规索引:快速定位特定数据,可以有多个

  4. 全文索引:全文索引是查找文本中的关键字,而不是索引中的值,可以有多个,fulltext

  1. 聚集索引:将数据与索引放到了一块,索引结构的叶子节点保存了行数据,(比如id对应的行数据都存在在聚集索引叶子节点上,可以根据id查到一行的数据)必须有,而且只有一个

  2. 二级索引:将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键,(name字段作为二级索引,其叶子节点存储是每个字段对应的id,然后再根据id查询)可以存在多个,

    聚集索引选用规则:

    如果存在主键,主键索引就是聚集索引。

    如果不存在主键,将使用第一个唯一索引(unique)作为聚集索引

    如果没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

    InnoDB主键索引的B+Tree高度为多高呢?

    假设:一行数据的大小是1k,一页中可以存16行这样的数据。InnoDB的指针占用6个字节空间,主键即使为bigint,占用字节数为8。

    高度为2:根节点,n * 8 + ( n + 1) * 6 = 16 * 1024 ,n约等1170,数据个数:1171 * 16 = 18736

    高度为3:1171 * 1171 * 16 = 21939856

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值