【MySQL】数据库进阶之索引内容详解（上篇 | 索引分类与操作）

💁 个人主页：黄小黄的博客主页
❤️ 支持我：👍 点赞 🌷 收藏 🤘关注
🎏 格言：一步一个脚印才能承接所谓的幸运

本文来自专栏：MySQL8.0学习笔记
本文参考视频：MySQL数据库全套教程
欢迎点击支持订阅专栏 ❤️

写在前面

 hello，大家好，我是黄小黄同学。本篇给大家带来的是数据库中索引的分类与操作相关内容。你将学习到单列索引、组合索引、全文索引以及空间索引。关于索引的内部原理比如Hash与B+Tree将放到下篇进行讲解，欢迎关注。

文章目录

1 索引概述

🆔 何为索引？
索引是通过某种算法，构建出一个数据模型，用于快速找出在某列中具有特定值的行。 如果不使用索引，MySQL则会从第一条记录开始读完整个表，从而找出相关的行。
 表越大，需要查询花费的时间就越多。如果表中查询的列有一个索引，MySQL能够快速到达这个位置去搜索数据文件，而不必查看所有数据，可以很大程度减少时间。
索引类似于目录，通过页码找到对应的内容。

举一个形象的例子，兔子和乌龟挑选箱子，只有一个箱子有宝物，其余都是空箱子。而有宝物的箱子被胡萝卜标记，兔子更倾向于有胡萝卜的箱子，而乌龟则需要一个个尝试，掂量，才能知道哪个箱子藏着宝物。

2 索引分类

1.按照数据结构分：

• Hash索引： 利用某种算法，给每一行数据按照某一字段，生成一个hashCode值，然后基于此关联索引。hashCode值依据具体公式而定，有可能会存在不同行但是hashCode相同，这种情况称之为哈希冲突。 但是即使发生冲突，查找效率也比全盘扫描快。
• B+Tree索引： 通过B+Tree这一树形结构来描述索引与行之间的关系。B+Tree具体内容后续将在数据结构与算法专栏相关内容讲述。在这里只需要大概了解即可。

2.按照索引功能分：

• 单列索引：普通索引、唯一索引、主键索引
• 组合索引
• 全文索引
• 空间索引

3 单列索引

单列索引是指一个索引只包含单个列，但是一个表中可以有多个单列索引。

3.1 普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，仅仅是为了让查询数据更加快速。

在下面的例子中，我们给name列添加了普通索引 index_name，使用的方式为 在创建表的时候直接指定， 相关代码如下：

CREATEDATABASE forindextest;USE forindextest;-- 在创建表的时候直接指定indexCREATETABLE student
(
    sid       INTPRIMARYKEY,
    card_id   VARCHAR(20),
    name      VARCHAR(20),
    gender    VARCHAR(20),
    age       INT,
    birth     DATE,
    phone_num VARCHAR(20),
    score     DOUBLE,INDEX index_name (name)-- 给name列创建索引);

除了创建表时添加索引外，也可以 直接创建：

• 以给student表中的gender列添加索引为例

CREATEINDEX index_gender ON student(gender);

同样可以使用修改表的形式添加索引：

ALTERTABLE student ADDINDEX index_age(age);

如果想要查看该表的索引的话，可以使用下面的语句：

SHOW INDEXES FROM student;

3.2 唯一索引

唯一索引的 索引列必须唯一，但是允许有空值。 如果是组合索引，则列值的组合必须唯一。

比如在上面的例子中，我们可以给card_id（身份证号）与phone_num（手机号码）添加唯一索引。创建唯一索引同样有三种方式，具体如下：

方式1 创建表的时候直接指定：

• 给card_id列创建唯一索引

CREATETABLE student
(
    sid       INTPRIMARYKEY,
    card_id   VARCHAR(20),
    name      VARCHAR(20),
    gender    VARCHAR(20),
    age       INT,
    birth     DATE,
    phone_num VARCHAR(20),
    score     DOUBLE,UNIQUE index_card_id(card_id););

方式2 直接创建：

• 给card_id列创建唯一索引

CREATEUNIQUEINDEX index_card_id ON student(card_id);

方式3 修改表结构的方式添加索引：

• 给phone_num列添加唯一索引

ALTERTABLE student ADDUNIQUE index_phone_num(phone_num);

3.3 主键索引

每张表一般都会有一个主键，在我们创建表时，MySQL会自动在主键列上建立一个索引，我们称之为主键索引。主键具有唯一性并且不能为NULL， 所有主键索引是一种特殊的唯一索引。

3.4 如何删除索引？

删除索引的语法格式如下：

-- 1 方式一dropindex 索引名 on 表名;-- 2 方式二altertable 表名 dropindex 索引名;

4 组合索引

4.1 组合索引概述

🆔 何为组合索引？

• 组合索引也被称为复合索引，指在建立索引时可以使用多个字段。比如在上面的例子中，将身份证号和手机号同时设置为唯一索引；
• 复合索引使用复合最左原则;
• 复合索引同样可以创建为普通索引和唯一索引

🅰️ 语法格式：

-- 创建组合索引createindex index_name on table_name(column1(length), column2(length));

4.2 何为复合最左原则

⭕️举个例子，假设我们通过如下的语句给手机号和姓名两列添加了复合唯一索引：

createuniqueindex index_phone_name on student(phone_num, name);

在查询的时候，如果查询为姓名，则使用不到刚刚创建的索引，因为在创建组合索引时先创建的是phone_num的， 具体操作示例如下：

select*from student where name ='xxx';select*from student where phone_name ='xxxxxx';select*from student where phone_num ='xxx'and name ='xxx';select*from student where name ='xxx'and phone_num ='xxx';

在上面的代码中，只有2、3、4的情况可以使用到索引index_phone_name，因为条件里必须包含索引前面的字段才能进行匹配；
而3和4相比where条件的顺序不一样，但是4却也可以使用到索引。因为mysql本身进行了一层sql优化，它会根据sql识别出来使用哪个索引，我们可以认为3和4在mysql看来是等价的。

5 全文索引

5.1 全文索引概述

🆔 何为全文索引？

• 全文索引的关键字是fulltext;
• 全文索引 主要用来查找文本中的关键字， 而不是直接与索引中的值进行比较，更像是一个搜索，基于相似度进行查询；

❓ 使用like+%就可以实现模糊匹配了，为什么还要使用全局索引呢？

like+%在文本比较少的时候是比较合适的，但是对于大量的文本数据检索是不可想象的。全文索引在大量的数据面前，会更加快速。但是也有缺点，比如有可能存在精度问题。

补充：

• MySQL5.6以前的版本，只有MyISAM存储引擎支持全文索引；
• MySQL5.6及其之后的版本，MyISAM与InnoDB存储引擎均支持全文索引；
• 只有字段类型为char、varchar、text及其系列才可以建立全文索引；
• 当数据量比较大时，先将数据放入一个没有全局索引的表中，然后再用create index创建fulltext索引，要比先为一张表建立fulltext然后再写入数据快很多；

想对一个词语使用全局索引搜索，这个词语的长度必须在最小搜索长度和最大搜索长度之间， 查看默认值的语句如下：

show variables like'%ft%';

5.2 全文索引的操作演示

1.数据准备： 使用下面的语句进行数据准备，创建表和全文索引并插入一定量的数据，数据如图所示：

CREATETABLE article
(
    id           INTPRIMARYKEYAUTO_INCREMENT,
    title        VARCHAR(255),
    content      VARCHAR(1000),
    writing_date DATE,--FULLTEXT (content) -- 创建全文索引);-- 插入数据INSERTINTO article
VALUES(NULL,'Dog and cat','I like dog, but you like cat.','2022-07-07');INSERTINTO article
VALUES(NULL,'Who are you?','I am human.','2022-07-07');-- 添加全文索引CREATE FULLTEXT INDEX index_content ON article(content);

2.使用全文索引：
语法格式如下：

match(col1, col2...) against(expr[search_modifier]);

操作示例如下：

SELECT*FROM article WHEREMATCH(content) AGAINST('Do');-- 没有结果 长度小于3SELECT*FROM article WHEREMATCH(content) AGAINST('Dog');

6 空间索引

🆔 简介：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型
• 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，分别为GEOMETRY、POINT、LINESTRING、POLYGON
• MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引
• 创建空间索引的列，必须将其声明为NOT NULL
• 空间索引使用频率较少

⭕️ 操作：

createtable shop_info(
    id primarykeyauto_incrementcomment'id',
    shop_name varchar(64)notnullcomment'门店名称',
    geom_point geometrynotnullcomment'经纬度',
    spatial key geom_index(geom_point));

写在最后

🌟以上便是本文的全部内容啦，后续内容将会持续免费更新，如果文章对你有所帮助，麻烦动动小手点个赞 + 关注，非常感谢 ❤️ ❤️ ❤️ !
如果有问题，欢迎私信或者评论区！

共勉：“你间歇性的努力和蒙混过日子，都是对之前努力的清零。”