索引的简单理解

最新推荐文章于 2024-07-20 20:04:43 发布

蓝洋YUAN

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量75

点赞数

分类专栏： MySQL 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_72224952/article/details/132693093

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

什么是索引？

索引是一种利用某种规则的数据结构与实际数据的关系加快数据查找的功能。

举个例子：就好比一本书，索引就是目录，我们就可以通过目录快速定位到所需要的内容，通过牺牲一部分存储数据的空间来建立目录，来加快查找的速度

索引创建原则

比较频繁作为查询条件的字段应该创建索引

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

更新非常频繁的字段不适合作创建索引

不会出现在where子句中的字段不该创建索引

建立共识

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。

MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。

而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时MySQL IO的基本单位就是Page（16KB内存和磁盘交互数据是以块4kb为单位，使用MySQL使用page是为使每次IO都能尽能多传递数据，减少IO次数）。

为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。

为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数

为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗?

答：如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5 等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。

索引原理

索引的存储原理大致可以概括为一句话：以空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中（MyISAM），也可能和数据一起存储在数据文件中（InnoDB））。

数据库在使用没有索引的字段搜索时默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕

MySQL索引使用的数据结构

主要有BTree（B+树）索引和hash索引。

对于hash索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；

其余大部分场景建议选择BTree索引。

索引为什么不选择其他的数据结构？（io次数，以及范围查找考虑）

链表？

线性遍历

二叉搜索树？

退化问题，可能退化成为线性结构

AVL &&红黑树？

虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。

Hash？（不适合范围查找）

官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行，另外还有其他差别，有兴趣可以查一下。

为什么选择B+树不选择B树?

先看看两者的区别

B树节点，既有数据，又有Page指针，而B+，只有叶子节点有数据，其他目录页，只有键值和 Page指针 B+叶子节点，全部相连，而B没有

为何选择B+

节点不存储数据，这样一个节点就可以存储更多的key（也就是目录相关条目）。可以使得树更矮，所以IO操作次数更少。叶子节点相连，更便于进行范围查找

各种结构的详细原因可以看看：MySQL索引详解（一文搞懂）-阿里云开发者社区 (aliyun.com)

索引的分类

主键索引：primary key

设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

索引列的值必须唯一，但允许有空值（Null）数据库自动建立索引。

复合索引：

一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方年遵循OpenGIS几何数据模型规则。

前缀索引：

在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

聚簇索引 VS 非聚簇索引

InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引

聚簇索引主键索引：（如果建表时没有设置主键会默认在隐藏字段形成一个隐藏主键）

InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图：

所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询

使用这个idx_index(a，b，c)这种多列形成的索引叫做组合索引，当我们需要的数据刚好是组合索引的成员时不需要进行回表查询，这样可以提高效率。这种情况就是==覆盖索引

所以一般聚簇的普通索引推荐使用复合索引

为何 InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间了。

非聚簇索引主键索引：

对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

索引操作

创建主键索引

第一种方式

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id))

第三种方式：

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点：一个表中，最多有一个主键索引，当然可以使符合主键

主键索引的效率高（主键不可重复）创建主键索引的列，它的值不能为null，且不能重复

主键索引的列基本上是int

唯一索引的创建

-- 1.在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
-- 2.创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
--3.
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点：一个表中，可以有多个唯一索引

查询效率高如果在某一列建立唯一索引，必须保证这列不能有重复数据

如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建

--1.
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);
--2.
create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

--3.
create table user10(id int primary key, name varchar(20), email
varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：一个表中可以有多个普通索引，普通索引在实际开发中用的比较多

如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

查询索引