位置：首页-资讯-数据库

MySQL之索引(入门级讲解）

2023-08-31 15:33

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

3.4Hash索引和B+tree索引的对比

🎁个人主页：tq02的博客_CSDN博客-C语言,Java,Java数据结构领域博主
🎥 本文由 tq02 原创，首发于 CSDN🙉
🎄 本章讲解内容：MySQL索引

🎁欢迎各位→点赞👍 + 收藏⭐ + 评论📝+关注✨

🎥学习专栏： C语言 JavaSE MySQL基础

1.1索引的简介

索引是什么？本质而言是一种数据结构，实现通常使用B树及其变种B+树。

抽象点：我们把索引比作一本书的目录，而目录的作用就是使我们迅速地找到我们需要的内容，而为什么需要索引？当数据表中含有上亿条数据时，我们进行查询是通过遍历表，一条一条筛选时，是不现实的，但是我们可以使用索引，就可知我们需要的数据大概的位置。

常见的索引方式：字典，可以有两种方式进行查询，第一种是拼音，第二种是部首，而这就是两种不同的索引方式。

1.2.索引的优缺点

优点：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

创建索引需要消耗额外的空间
有可能会拖慢增删改的速度，试想一下，当你修改了数据表的信息时，你的目录也就是索引也可能需要修改的。

结：个人而言，缺点相比较与优点，我们还是利大于弊的，在很多情况下，我们都是以查询操作为主，而增删改较少。

索引分为：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

2.1查看索引

查看索引，有人会问，我未创建索引，也会有索引嘛？在数据库中有三种约束类型存在时会自动生成索引，因此在查询时，即使未创建索引，若有这种条件存在，依然会生成索引。

三种约束类型：主键、unique、外键

也称：主键索引、唯一索引

语法：show index from 表名；

存在主键时

主键不允许重复，因此进行插入或者修改时，需要先查询，查看插入/修改后结果是否存在。

存在unique时

存在外键约束时

当然除了以上的三种，我们还可以查看到手动创建的索引。

2.2创建索引

索引创建分为2种，第一种是创建表时创建索引，第二种是给存在的表创建索引
索引的创建，前提是表中的数据较少或者无数据，若含有大量数据会引起非常大规模的硬盘IO操作，进一步导致数据库卡死。

2.2.1 创建表时创建索引

语法格式： CREATE TABLE 表名(

字段名称字段类型 [完整性约束条件],

...,

[UNIQUE / FULLTEXT / SPATIAL] INDEX / KEY [索引名称](字段名称[(长度)] [ASC|DESC])

);

创建普通索引

创建了2个索引，分别为id和username

CREATE TABLE test1(id int,username varchar(20),index in_id(id),KEY in_username(username));

创建唯一索引

CREATE TABLE test2(id int,username varchar(20) unique;    //自动生成);

创建全文索引

CREATE TABLE test3(id intusername VARCHAR(20) ,FULLTEXT INDEX full_userDese(username));

创建单列索引

CREATE TABLE test4(id int UNSIGNED AUTO_INCREMENT KEY,test1 VARCHAR(20) NOT NULL,test2 VARCHAR(20) NOT NULL,INDEX in_test1(test1)，UNIQUE in_test1(test1));

创建多列索引

CREATE TABLE test4(id int UNSIGNED AUTO_INCREMENT KEY,test1 VARCHAR(20) NOT NULL,test2 VARCHAR(20) NOT NULL,test3 VARCHAR(20) NOT NULL,INDEX mul_t1_t2_t3(test1,test2,test3)UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)  //二者都可以);

创建空间索引

CREATE TABLE test1(id TINYINT UNSIGNED AUTO_INCREMENT KEY,SPATIAL INDEX spa_test(test))ENGINE=MyISAM;

2.2.2存在的表上创建索引

概念：在存在的表上创建索引，可以直接创建，也可以将某列修改为某种索引

直接创建：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名 {字段名称[(长度)] [ASC|DESC]};

简化：create index 索引名 on 表名(字段名) //其他条件不设置

修改表方式创建：ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称](字段名称[(长度)] [ASC|DESC]); //将某个列修改为索引