Mysql高级-索引

最新推荐文章于 2022-10-03 22:38:31 发布

Gxbalunn

最新推荐文章于 2022-10-03 22:38:31 发布

阅读量115

点赞数 1

分类专栏： mysql高级文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45636230/article/details/119351364

版权

6 篇文章 0 订阅

订阅专栏

索引

在Mysql中，索引由数据库表中一列或多列组合而成，创建索引的目的是为了优化数据库的查询速度。其中，用户创建的索引指向数据库中具体数据所在的位置。当用户通过索引查询数据库中的数据时，不需要遍历所有数据库中的所有数据。这样大幅提高了查询效率。

为什么使用索引？索引就像书的目录，查找内容时不必逐页翻阅就能快速的找到所需内容。

优势

劣势

实际上索引也是一张表，存储了主键和索引字段，并指向实体表的记录，所以索引也会占用一定内存。
虽然大大提高了查询速度，但同时降低了更新表的速度。对表进行增删改时，不仅要保存新数据，还要保存索引文件，每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

设定为主键后数据库会自动建立索引。
alter table 表名 add primary key 表名（列名）;
删除主键索引。
alter table 表名 drop primary key;

创建单列索引
create index 索引名 on 表名（列名）;
删除索引
drop index 索引名;

创建唯一索引
create uniqu index 索引名 on 表名（列名）;
删除索引
drop index 索引名 on 表名;

创建复合索引
create index 索引名 on 表名（列1，列2...);
删除索引
drop index 索引名 on 表名;

查看索引
show index from 表名;

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

非叶子节点不存储数据，只存储索引，可以放更多的索引。所有的叶子结点之间都有一个链指针。数据都存放在叶子节点中。

找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。

索引的存储和数据的存储是分离的，也就是说找到了索引但是没找到数据，需要根据索引上的值（主键）再次回表查询，非聚簇索引也叫做辅助索引。

创建一个学生表，用三种查询来说明什么情况下是聚簇索引什么情况不是：

create table sudent(
	id bigint,
    no varchar(20),
    name varchar(20),
    primary key('id'),
    unique key 'idx_no'('no')
)

第一种，直接根据主键查询获取所有字段数据，此时主键就是聚簇索引，因为主键对应的索引叶子结点存储了 id=1 的所有字段的值。

select * from student where id = 1;

第二种，根据编号查询编号和名称，编号本身是一个唯一索引，但查询的列包含了学生编号和学生名称，当命中编号索引时，该索引的节点的数据存储的是主键id，需要根据主键id重新查询一次，所以这种查询下no不是聚簇索引。

select no,name from student where no = 'test';

第三种，我们根据编号查询编号，这种查询命中编号索引时，直接返回编号，因为所需要的数据就是该索引，不需要回表查询，这种场景下no是聚簇索引。

select no from student where no = 'test';

关注