MySQL~数据库中的索引、了解索引的分类、认识BTree索引、聚簇索引与非聚簇索引

目录

什么是索引

索引的作用

使用场景

索引的使用

索引的分类

BTree索引

主键索引(聚簇索引)

非聚簇索引

---

什么是索引

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现

索引的作用

· 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系，索引就相当于是目录；

· 索引的作用类似于书的目录，可以用于快速定位、检索数据

· 索引对于提高数据库性能有很大帮助.

使用场景

当满足以下条件时，需要考虑对表中的某列或某几列创建索引，以提高查找效率

· 数据量较大，且经常对这些列进行条件查询

· 该数据库表的插入操作以及对这些列修改的频率较低

· 索引会占用额外的磁盘空间.

如果是非条件查询列、或者需要经常做插入和删除操作、磁盘空间不足时，不考虑创建索引

索引的使用

在创建主键约束(primary key) 、唯一约束(unique)、外键约束(foreign key)时，会自动创建对应列索引

· 查看索引

show index from 表名;

·  创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

 · 删除索引

drop index 索引名 on 表名;

索引的分类

· 从索引数据结构划分：BTree索引、Hash索引、FULLTEXT全文索引、R TREE索引

· 从应用层次划分：普通索引、唯一索引、主键索引、复合索引

· 从索引键值类型划分：主键索引、辅助索引(二级索引)

· 从数据存储和索引键值逻辑关系划分：聚集索引(聚簇索引)、非聚集索引(非聚簇索引)

BTree索引

BTree索引通常是B-Tree或B+Tree索引.

· B-Tree能够加快数据的访问速度，因为B树所有的结点都保存有索引列及数据

· B+Tree是B-Tree的改进版本，它是数据库索引所采取的存储结构。B+树的叶子结点保存有索引列和数据，非叶子结点只有索引字段，B+树的叶子结点全部相连.

· B树与B+树的区别

①数据保存的位置不同：B+树保存在叶子节点，B树保存在所有结点中

②叶子节点的指向不同：B+树相邻的叶子节点通过指针相连，B树没有

· B+树的优势

①节点不存储数据，这样一个节点可以存储更多的key，可以使树更矮，从而减少IO操作次数

②查询性能稳定，每次查询都是从根节点遍历到叶子节点，查询路径长度相同，每次的查找效率相当，时间复杂度固定是O(logn)

③所有叶子节点形成有序链表，方便范围查找

主键索引(聚簇索引)

默认存储结构是B+树，一张表只能有一个主键索引。B+树的叶子节点上，存放主键字段(索引)及数据.

· 优点：查询速度快

· 缺点：主键需要是整型，且字段不要太长，效率低.

非聚簇索引

非主键索引都是非聚簇索引，可以使用多种类型索引，如B+树索引、hash索引等.

如果用B+树存储结构，其叶子节点存放索引字段值和主键的值.

· 搜索数据的方式

①先通过索引字段找到叶子节点上的主键值

②再通过主键值，找到整条数据(回表操作)

· 优点

更新代价相对比聚簇索引小，因为其叶子节点存储的是索引值和主键值，没有真实数据.

· 缺点

①依赖有序数据、②可能产生回表操作，导致效率更低