我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL数据库之索引详解

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL数据库之索引详解

今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容。

一、MySQL索引简介

索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种“目录”。MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的索引是表空间的一个组成部分。
MySQL数据库一共支持5种类型的索引,分别是普通索引、唯一性索引、主键索引、复合索引和全文索引,下面,我将对这四种类型的索引一一介绍。

二、MySQL五种类型索引详解

(一)普通索引

普通索引是MySQL数据库中的一种普通的索引,添加普通索引的列对数据没有特殊要求,普通索引能起到的作用就是加快差查询速度。
在创建数据表时添加普通索引SQL语句示例如下:


create table exp(id int , name varchar(20),index exp_name(name));

或者是把index换成key,如下:


create table exp (id ,int , name varahcr (20) ,  key exp_name(name));

在上述SQL命令中,key或者index表示添加索引,后面紧跟着的是索引名称,后面括号里的是要添加索引的列。
本文介绍的所有索引相关的SQL语句,如果没有特殊说明,index都可以换成key,为了节省文章篇幅,这一点在以后就不再赘述了。
此外,我们也可以在添加索引时,不指定索引的名称,这时,MySQL会自动为该索引添加与该字段同名的索引名。
执行结果如下:

在这里插入图片描述

创建数据表后向表内新添加普通索引SQL语句示例如下:


alter table exp add index exp_id(id);

执行结果如下:

在这里插入图片描述

创建数据表后删除普通索引的SQL语句示例如下:


alter table drop index exp_name;

执行结果如下:

在这里插入图片描述

注意,在上述命令中,exp_name是索引的名字而不是含有索引的字段的名字,如果我们忘记了该表中的索引名称,可以执行以下SQL命令进项查询:


show index from exp;

其中,exp是表名,该命令执行结果如下:

在这里插入图片描述

从上面几张图片可以看出,添加普通索引后,在使用desc查看表结构时,会发现Key列上出现MUL,这就表示该列添加有普通索引。

(二)唯一性索引

唯一性索引,是在普通索引的基础上,要求添加该索引的列所有的值只能出现一次。唯一性索引常用语添加在注入身份证号、学号等字段中,不可以添加在注入名字、等字段中。
唯一性索引的添加与普通索引几乎完全相同,只不过要把普通索引的关键字key和index换成unique key和unique index。
在创建数据表时添加唯一性索引的SQL语句示例如下:


create table exp (id int, name varchar(20), unique key (name));

上述命令执行结果如下:

在这里插入图片描述

可以看出,添加唯一性索引的字段,在使用desc命令查询表结构时,Key列中会显示UNI,表示该字段添加了唯一性索引。
在创建数据表后添加唯一性索引的SQL语句实例如下:

在这里插入图片描述

删除唯一性索引的SQL语句示例如下:


alter table exp drop index name;

执行结果如下:

在这里插入图片描述

(三)主键索引

主键索引,是数据库的所有索引中查询速度最快的,并且每个数据表只能有1个主键索引列。主键索引的列,不允许出现重复的数据,也不允许为空值。
添加、删除主键索引与普通索引和唯一性索引非常相似,只不过将key换成了primary key而已。相关SQL命令如下:


create table exp(id int ,name varchar(20), primary key (id));alter table exp add primary key (id);

添加了主键索引的列,会在desc查看表结构时Key列上显示PRI,如下所示:

在这里插入图片描述

主键索引的删除可以执行命令:


alter table exp drop primary key;

注意,在该SQL语句总,key不能换成index。
有时,我们在尝试删除主键索引时,MySQL后拒绝,这可能是因为该字段添加了auto_increment属性的缘故,我们可以把该字段修饰符删除,就可以删除该字段的主键索引了,如下所示:

在这里插入图片描述

(四)复合索引

如果想要创建一个包含不同的列的索引,我们就可以创建符合索引。其实,复合索引在业务场景中应用的非常频繁,比如,如果我们想要记录数据包的内容,则需要将IP和端口号作为标识数据包的依据,这时就可以把IP地址的列和端口号的列创建为复合索引。复合、添加和删除索引创建SQL语句示例如下:


create table exp (ip varchar(15),port int ,primary key (ip,port));
alter table exp add pirmary key(ip ,port);
alter table exp dorp priamary key;

复合索引在创建后,在使用desc查看数据表结构时,会在Key列中发现多个PRI,这就表示这些含有PRI的列就是复合索引的列了。如下所示:

在这里插入图片描述

注意,复合索引相当于一个多列的主键索引,因此,添加复合索引的任何一个列都不允许数据为空,并且这些列不允许数据完全相同,否则MySQL数据库会报错。如下所示:

在这里插入图片描述

(五)全文索引

全文索引主要是用于解决大数据量的情况下模糊匹配的问题。如果数据库中某个字段的数据量非常大,那么如果我们想要使用like+通配符的方式进行查找,速度就会变得非常慢。针对这种情况,我们就可以使用全文索引的方式,来加快模糊查询的速度。全文索引的原理便是通过分词技术,分析处文本中关键字及其出现的频率,并依次建立索引。全文索引的使用,与数据库版本、数据表引擎乃至字段类型息息相关,主要限制如下:
1、MySQL3.2版本以后才支持全文索引。
2、MySQL5.7版本以后MySQL才内置ngram插件,全文索引才开始支持中文。
3、MySQL5.6之前的版本,只有MyISAM引擎才支持全文索引。
4、MySQL5.6以后的版本,MyISAM引擎和InnoDB引擎都支持全文索引。
5、只有字段数据类型为char、varchar、以及text的字段才支持添加全文索引。
创建、添加以及删除全文索引SQL命令如下:


create table exp (id int ,content text ,filltext key (content))engine=MyISAM;
alter table exp add fulltext index (content);
alter table exp drop index content;

部分执行结果如下:

在这里插入图片描述

在创建了全文索引后,也不能够使用like+通配符的方式进行模糊查询,全文索引的使用有其特定的语法,如下所示:


select * from exp where match(content) against ('a');

其中,match后面的括号里是含有全文索引的字段,against后面的括号里是要模糊匹配的内容。
此外,全文索引的作用并不是唯一的,在很多场景下,我们并不会使用MySQL数据库内置的全文索引,而是使用第三方类似的索引以实现相同的功能。

三、MySQL索引使用原则

1、索引是典型的“以空间换时间”的策略,它会消耗计算机存储空间,但是会加快查询速度。
2、索引的添加,尽管加快了在查询时的查询速度,但是会减慢在插入、删除时的速度。因为在插入、删除数据时需要进行额外的索引操作。
3、索引并非越多越好,数据量不大时不需要添加索引。
4、如果一个表的值需要频繁的插入和修改,则不适合建立索引,反制,如果一个表中某个字段的值要经常进行查询、排序和分组的字段则需要建立索引。
5、如果一个字段满足建立唯一性索引的条件,就不要建立普通索引。

总结

到此这篇关于MySQL数据库之索引详解的文章就介绍到这了,更多相关MySQL索引内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL数据库之索引详解

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL数据库之索引怎么创建

在MySQL中,可以通过以下命令来创建索引:1. 创建唯一索引:```sqlCREATE UNIQUE INDEX index_name ON table_name (column_name);```2. 创建普通索引:```sqlCREA
2023-08-17

MySQL数据库索引及优化的示例详解

目录一、mysql 索引简介二、索引优化实战三、总结在日常的数据库使用过程中,我们经常需要对数据进行查询、插入、删除等操作。为了提高这些操作的效率,数据库的性能优化显得尤为重要。本文将带你深入了解 MySQL 数据库的索引以及如何进行优化实
2023-05-19

MongoDB数据库索引用法详解

一.索引详讲索引是什么,索引就好比一本书的目录,当我们想找某一章节的时候,通过书籍的目录可以很快的找到,所以适当的加入索引可以提高我们查询的数据的速度。准备工作,向MongoDB中插入20000条记录,没条记录都有number和name
2022-07-08

数据库索引技术之哈希索引

我们都知道,想要提升数据库的查询速度,索引必不可少 。那么,索引的底层结构都是怎样的呢?它们又是如何工作的呢?

数据库索引技术之B树索引

跟我们在 LSM树 一节中提到的 SSTable 一样,B树也是将数据组织成有序形式,因此支持范围查询。尽管如此,它们的底层结构却完全不同,B树有自己独特的设计哲学。

MySQL数据库之索引的作用是什么

MySQL数据库的索引是用于提高查询效率的一种数据结构。它可以帮助数据库系统快速定位和访问数据,减少数据的扫描量,从而提高查询的速度。具体来说,索引在数据库中是一种有序的数据结构,它包含了表中某一列或多列的值,以及对应的物理位置信息。当进行
2023-08-15

编程热搜

目录