SQL索引怎么创建使用

SQL索引怎么创建使用

这篇文章主要讲解了“SQL索引怎么创建使用”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“SQL索引怎么创建使用”吧!

索引的作用

索引的作用就是加快查询速度,如果把使用了索引的查询看做是法拉利跑车的话,那么没有用索引的查询就相当于是自行车。目前实际项目中表的数据量越来越大,动辄上百万上千万级别,没有索引的查询会变得非常缓慢,使用索引成为了查询优化的必选项目。

索引的概念

索引其实是一种特殊的数据,也保存在数据库文件中,索引数据保存着数据表中实际数据的位置。类似书籍前面的目录,这个目录就保存了书中各个章节的页数,通过查看目录我们可以快速定位章节的页数,从而加快查找速度。

我们来看一段查询语句:

select * from book where id = 1000000;

假设书籍表中有几百万行数据,没索引的查询会遍历前面的100万行数据找到结果,如果我们在id上建立主键索引,则直接在索引上定位结果,速度要快得多。

索引的优缺点

优点:提高查询速度

缺点:本身也是数据,会占用磁盘空间;索引的创建和维护也需要时间成本;进行删除、更新和插入操作时,因为要维护索引,所以速度会降低。

使用索引的语法

创建索引

建表的同时创建索引

create table 表名

(

字段名类型,

...

字段名类型,

index 索引名称 (字段名)

);

建表后添加索引

alter table 表名 add index 索引名(字段名);

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

删除索引

alter table 表名 drop index 索引名;

drop index 索引名 on 表名;

查看表中的索引

show index from 表名;

查看查询语句使用的索引

explain 查询语句;

索引的分类

索引按功能分为:

普通索引,在普通字段上建立的索引,没有任何限制

主键索引,创建主键时,自动创建的索引,不能为空,不能重复

唯一索引,建立索引的字段数据必须是唯一的,允许空值

全文索引,在大文本类型(Text)字段上建立的索引

组合索引,组合多个列创建的索引,多个列不能有空值

代码示例:

-- 创建书籍表

create table tb_book

(

-- 创建主键索引

id int primary key,

-- 创建唯一索引

title varchar(100) unique,

author varchar(20),

content Text,

time datetime,

-- 普通索引

index ix_title (title),

-- 全文索引

fulltext index ix_content(content),

-- 组合索引

index ix_title_author(title,author)

);

-- 建表后添加主键索引

ALTER TABLE tb_bookADD PRIMARY KEY pk_id(id);

-- 建表后添加唯一索引

ALTER TABLE tb_bookADD UNIQUE index ix_title(title);

-- 建表后添加全文索引

ALTER TABLE tb_bookADD FULLTEXT index ix_content(content);

-- 查询时使用全文索引

SELECT * FROM tb_book MATCH(content) ANGAINST(‘胜利’);

-- 建表后添加组合索引

ALTER TABLE tb_bookADD INDEXix_book(title,author);

注意:创建组合索引时,要遵循”最左前缀”原则,把最常查询、排序的字段放左边,按重要性依次递减。

索引的使用策略

什么情况下要建立索引?

1)在经常需要查询和排序的字段上建立索引

2)数据特别多

什么情况下不要建立索引?

1)字段数据存在大量的重复,如:性别

2)数据很少

3)经常需要增删改的字段

什么情况下索引会失效?

1)模糊查询时,使用like ‘%张%’会失效,而like ‘张%’不会

2)使用is null或is not null查询时

3)使用组合索引时,某个字段为null

4)使用or查询多个条件时

5)在函数中使用字段时,如where year(time) = 2019

索引的结构

不同的存储引擎使用不同结构的索引:

聚簇索引,InnoDB支持,索引的顺序和数据的物理顺序一致,类似新华字典中的拼音目录排列和汉字排列顺序一致,聚簇索引一个表中只能有一个。

非聚簇索引,MyISAM支持,索引顺序和数据的物理顺序不一致,类似新华字典中的偏旁部首目录和汉字排列顺序不一致,非聚簇索引表可以有多个。


索引的数据结构主要是:BTree和B+Tree

BTree的数据结构如下,是一种平衡搜索多叉树,每个节点由key和data组成,key是索引的键,data是键对应的数据,在节点的两边是两个指针,指向另外的索引位置,而所有的键都是排序过的,这样在搜索索引时,可以使用二分查找,速度比较快,时间复杂度是h*log(n),h是树的高度,BTree是一种比较高效的搜索结构。


B+Tree的数据结构如下,是BTree的升级版,区别是非叶子节点不在存储具体的数据,只保存索引的键,数据保存到叶子节点中,并且叶子节点中没有指针只有键和数据。B+Tree的优点是:搜索效率更高,因为非叶子节点中没有保存数据,就可以保存更多的键,每一层的键越多,树的高度就会减少,这样查询速度就会提升。


感谢各位的阅读,以上就是“SQL索引怎么创建使用”的内容了,经过本文的学习后,相信大家对SQL索引怎么创建使用这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是恰卡编程网,小编将为大家推送更多相关知识点的文章,欢迎关注!

发布于 2021-12-22 21:58:17
收藏
分享
海报
0 条评论
44
上一篇:Java面试题中常见的问题有哪些 下一篇:web前端开发框架有哪些
目录

    0 条评论

    本站已关闭游客评论,请登录或者注册后再评论吧~

    忘记密码?

    图形验证码