MySql索引

最新推荐文章于 2024-08-28 09:24:52 发布

顺其自然6

最新推荐文章于 2024-08-28 09:24:52 发布

阅读量323

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_56951318/article/details/127381368

版权

索引

数据结构

mysql的数据是存储在磁盘上的，满足对日常操作【CRUD】的高效稳定，可以通过提升硬件配置来解决。选用合适的数据结构也很关键，innodb采用的就是一种名为【B+树】的数据结构。

我们之前学习过的innodb数据是以【行】为单位，存在一个大小为16k【页】中，B+树的作用就是按照一个组织形式，将所有的【页】组织关联起来。

B-树

Balance（平衡），类似一种平衡二叉树。

B-树的特点：

所有的键值分步在整棵树中；
任何一个关键字出现且只出现在一个结点中
搜索有可能在非子结点结束
在关键字全集内做一次查找，性能逼近二分查找

B+树

是B-树的变体，是一种多路搜索树。

特点：

所有的关键字都存储在子结点
为所有的子结点增加了一个双向指针

选择使用B+树的原因：

相同的空间，不存放【整行数据】就能存【更多的id】，B+树能使每个结点能检索的【范围更大、更精确、极大的减少了I/O操作，层高较低，通常3到4层就可以支持百万级的数据量】
Mysql是关系型数据库，B+树子结点增加了双向指针，加强了区间的方文星，可以在范围内查询。

查询相同的记录，使用【id列】比使用【非主键列】快几百倍！！！

使用主键列查找数据时，可以利用B+树的特定，自上向下查询
使用非主键列只能从子结点进行【全表扫描】，一个一个的比较。

索引的分类和创建

聚簇索引和非聚簇索引

【主键和数据】共存的索引被称之为【聚簇索引】。

【username和数据】建立的索引【非聚簇索引】。

聚簇索引只有在innodb引擎中才存在，再MyIsam中是不存在的。

Innodb使用的时聚簇索引，他会将主键组织到一颗B+树中，而行数据存储在叶子结点上，

如果使用的是where id=1这样的条件查询主键，按照B+树的检索算法来查找对应的叶子结点，获得行的数据。

如果对username列进行搜索，并且username列已经建立索引，需要两个步骤：

第一步在非聚簇索引中检索username，到达其子结点获取对应的主键
第二步使用主键在聚簇索引B+数中再执行一次B+树检索操作，最后达到叶子结点集合获取整行的数据。

MyIsam使用的是非聚簇索引。

普通索引

创建索引：

-- 给username整列添加索引
create index idx_user_name on user(user_name);
-- 给email可以截取前几个字符
create index idx_email on user(email(5));

创建索引本身是一个非常耗资源的操作。

创建索引之后，查询效率会显著提升。

删除索引：

drop index idx_user_name on user;

修改索引：

-- 通过修改的方式来添加索引
ALTER table user add index idx_email (email)
-- 在创建表的同时创建索引
CREATE table student (
    sid int,
    sname varchar(20),
    gender varchar(1),
    index idx_sid (sid)
);

唯一索引

对列的要求：索引列的值不能重复。

创建唯一索引：

-- 创建唯一索引
create unique index idx_email on user(email);
-- 也可以通过修改的方式添加唯一索引
ALTER table user add unique index idx_email (email)

唯一索引和主键的区别：

唯一索引列允许空值，主键列不能为空

主键列在创建时，已经默认为非空+唯一索引

主键可以被其他表引用为外键，但是唯一索引不能用为外键

一个表只能有一个主键，但是可以创建多个唯一索引

主键更适合不容易更改的标识，如：身份证号，自增

唯一约束和唯一索引的区别：

都可以实现数据的唯一，数据都可以为null

创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束时会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。

创建一个唯一索引，这个索引就是一个独立的索引，是可以单独删除

建议，如果想要让索引和约束单独存在，先建立唯一索引，再建立唯一约束。