mysql基础-索引篇

Focus4149
209 阅读5分钟

这是我参与8月更文挑战的第2天，活动详情查看：8月更文挑战

定义及介绍

  1. 索引是帮助mysql获取高效数据的数据结构，所以索引的本质是：数据结构

  2. 索引的目的是在于提高查询效率，可以理解为字典、文章目录等

  3. 索引由于本身就是很大，很多时候不能全部存储在内存中，因此，一般以索引文件的形式存储在磁盘中

基础语法

创建索引

-- 创建索引
create [unique] index idxName on table_name(columnName(length))
-- 修改表结构索引
alter table table_name add [unique] index idxName(columnName)

查看索引

show index from table_name

删除索引

drop index idxName on table_name

其他常见的添加索引的命令

-- 添加主键索引
alter table table_name add primary key(column_list)

--添加唯一索引
alter table table_name add unique idxName(column_list)

-- 添加普通索引
alter table table_name add index idxName(column_list)

-- 添加全文索引 
alter table table_name add fulltext idxName(column_list)

索引分类

mysql中的索引根据不同的类别可以分为多个类

物理存储角度

  • 聚簇索引
  • 非聚簇索引，也叫辅助索引

数据结构角度

  • B+数索引
  • Hash索引
  • 全文索引（5.6之前仅在存储引擎为MyISam时可以）
  • R-tree索引

逻辑角度

  • 主键索引：是一种唯一索引，不允许空值
  • 单列索引：每个索引只能包含单个列，一个表中可以多个单列索引
  • 组合索引：每个索引至少包含2个列字段，查询服从最左分配原则
  • 唯一索引：增加此索引的列在表中的值必须唯一
  • 空间索引：针对空间列字段加的索引

B-、B+树索引

索引是在存储引擎层面实现的，而不是server层面实现的。多个存储引擎之间对于所有的支持和实现存在一定的差别

B-Tree(B树)

定义

m阶的B树是一种平衡的m路搜索树，每获取一个节点，都是进行一次磁盘的IO操作

3阶B树

3-s-b-tree.png

性质

  • 每个节点最多有m个孩子
  • 除根节点和叶子节点外，其他每个节点至少有Ceil(m/2)个孩子
  • 若根节点不是子节点，则至少有2个孩子
  • 所有叶子节点都在同一层，且不包含其他关键字信息
  • 每个非终端节点包含n个关键字信息，m/2 <= n <= m ， n=孩子个数-1
  • 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素值域

说明

  • 每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在的磁盘块的地址
  • 两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

B+树

4阶B+数图

4-s-b-plus-tree.png

定义与说明

  • B+树是B树的一种辩题，总体性能要好一些
  • B-树结构图中可以看出每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即每一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘IO次数，进而影响查询效率。
  • B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+树的高度

B+Tree和B-Tree的比较

不同点

  • 非叶子节点只存储键值信息
  • 所有叶子节点之间都有一个链指针
  • 数据记录都放在叶子节点中

优势

  • 单一节点存储更多的元素，使得查询的IO次数减少
  • 节点不存储数据，可以存储更多的键值
  • 所有查询都要查找到叶子节点，查询性能稳定；B-树是根据不同数据的不同的性能表现，例如数据出现在根节点，就只需要一次IO；出现叶子节点，就需要m次IO；性能是不固定的
  • 所有的叶子节点行程有序链表，便于范围查询。B-Tree做范围查找采用的是中序遍历的方式，性能要差一些。

hash索引

  • 主要是通过hash算法，将数据库字段转换成为订场的Hash值，与该记录的指针一并存储Hash表的对应位置；如果发生Hash碰撞，则在对应的Hash键下以链表的形式存储；常见的hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法
  • 检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到hash值，到对应的hash表对应的位置取出数据即可，如果发生hash碰撞，则需要在取值时进行筛选
  • Mysql目前只有Mermory引擎和NDB引擎支持Hash索引

Full-Text全文索引

  • 全文索引是myisam中一种特殊索引类型，主要用于全文检索；InnoDB从mysql5.6开始提供对全文索引的支持
  • 它用于替代在效率比较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次全模糊匹配多个字段
  • 使用B-树存放索引数据，但使用的是特定的算法，将字段数据分隔后在进行索引，索引文件存储的是分隔前的索引字符串集合，与分隔后的索引信息，对应BTree结构的节点存储是分隔后的词信息以及它在分隔前的索引字符串集合中的位置。
avatar
文章
阅读
粉丝