【MySQL数据库 | 第十八篇】索引分类与语法

我是一盘牛肉

已于 2023-06-15 03:52:45 修改

阅读量985

点赞数 14

分类专栏：【从零开始学习MySQL】文章标签：数据库职场和发展大数据

于 2023-06-15 03:52:13 首次发布

本文链接：https://blog.csdn.net/fckbb/article/details/131219370

版权

【从零开始学习MySQL】专栏收录该内容

27 篇文章 45 订阅

订阅专栏

前言：

在上一篇我们讲解了什么是索引以及索引的结构，这一篇我们将更进一步的讲解索引的分类以及语法介绍，大家准备好了嘛？

索引分类：

1. 主键索引
主键索引是一种特殊的唯一索引，它强制保证某列的值唯一性，不允许该列存在NULL值。因此，主键索引通常被用作表中记录的唯一标识符，可以极大的提高查询效率。如果没有为表定义主键索引，Mysql会使用一个内置的隐藏主键自动创建主键索引。
2. 唯一索引
唯一索引也是强制保证列值的唯一性，不过和主键索引的区别在于它允许NULL值，也可以定义多个唯一索引。在插入或更新数据时，唯一索引会自动检查是否有冲突，因此使用唯一索引来定义表的主键也是一种常用的方式。
3. 常规索引
常规索引是最基本的索引类型，它会对列的值进行二叉树排序，可以支持基于等值和范围查询的高效检索。常规索引可以通过CREATE INDEX语句或ALTER TABLE语句添加到表中，也可以使用前缀索引来减小索引大小。
4. 全文索引
全文索引是用于处理文本数据的一种特殊索引类型，它可以对文本列的词汇或词组进行分析和索引，从而支持基于自然语言的检索。与常规索引不同，全文索引不是基于二叉树结构，而是基于倒排索引结构。在MySQL中，全文索引只能用于MyISAM引擎，也可用于InnoDB引擎的全文索引（InnoDB全文索引需要MySQL版本大于5.6）。

总的来说，虽然这些索引类型都可以提高数据库的查询性能，但具体使用哪种类型还需要根据应用场景的具体需求来合理选择。例如，主键索引通常用于作为表的唯一标识，常规索引可用于WHERE条件中经常用到的字段，而全文索引则适用于处理文本数据的搜索和分析等场景。

而在InnoDB存储引擎之中，我们又把又把引擎分为了两大类：

聚集索引（Clustered index）：将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据，特点是必须有，而且只有一个。
二级索引（Secondary index）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，特点是可以存在多个。

正确理解这两个索引很重要，很多SQL优化都是在围绕着聚集索引和二级索引来做的。

聚集索引的选取规则：

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一的（UNIQUE）索引作为聚集索引。
如果表没有主键，也没有合适的唯一索引，那么InnoDB会自动生成一个rowid作为隐藏的聚集索引。

如图所示 id 为聚集索引，name 为二级索引

而我们在查询的时候，例如查询名字叫做Arm人的全部信息，内部的查询步骤应该是：先在二级索引中查询名字叫做Arm的人的id，查询到之后，我们再在聚集索引里面查询哪一个叶子节点的id值为Arm的id，查询到之后，因为该叶子节点存储着整行的信息，我们也就获得了Arm的全部信息。我们把这种查询方式叫做查询。

例题：
1.以下两个SQL语句哪一个执行效率高？

select * from user where id =10;
select * from user where name = 'Arm';
备注：id为主键，name创建的字段有索引

解答：第一个语句的执行速度快，因为id为主键，而主键自动绑定聚集索引，聚集索引的叶子字节点存储的就是整行的信息，我们只用查询一次就可以，而第二个语句的name的索引是二级索引，我们要先在二级索引中找到聚集索引的id，才可以进入聚集索引。因此第二个语句执行效率没有第一个语句高。

索引的语法：

1. 创建索引

在MySQL中，创建索引的语法格式为：

CREATE [UNIQUE] [INDEX|KEY] index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)

其中，‘ UNIQUE ’是指创建的是一个唯一索引。`INDEX`和`KEY`是等价的关键字，`index_name`是索引的名称，`table_name`是要创建索引的表名，`column1, column2, ...`是要创建索引的列名，`ASC`和`DESC`是可选的排序方式。

2. 删除索引

在MySQL中，删除索引的语法格式为：

DROP INDEX index_name ON table_name

其中，`index_name`是要删除的索引的名称，`table_name`是要删除索引的表名。

3. 查看索引

在MySQL中，查看索引的语法格式为：

SHOW INDEX FROM table_name

其中，`table_name`是要查看索引的表名。

4. 强制使用索引

在MySQL中，可以使用`FORCE INDEX`或`USE INDEX`语句强制使用某个索引，语法格式如下：


SELECT column_list
FROM table_name
FORCE INDEX (index_name)
WHERE condition_list

或者：

SELECT column_list
FROM table_name
 USE INDEX (index_name)
WHERE condition_list

其中，`column_list`是要查询的列名列表，`table_name`是要查询的表名，`condition_list`是查询条件，`index_name`是要使用的索引名称。