【MySQL】索引

欢迎来到Cefler的博客😁
🕌博客主页：折纸花满衣
🏠个人专栏：MySQL

---

👉🏻索引

MySQL的索引是数据库管理系统中一个非常重要的部分，它用于快速查找和访问数据库中的数据。索引就像一本书的目录，可以帮助我们更快地定位到书中的特定信息。

以下是关于MySQL索引的一些关键点：

1. 定义：

   • 索引是一个数据结构，它允许数据库系统快速访问数据表中的特定数据。
   • 它由数据库管理系统自动创建和维护，用于加速数据的检索速度。

2. 类型：

   • B-Tree索引：最常见的索引类型，适用于全值匹配、范围查询等。
   • 哈希索引：基于哈希表的索引，适用于等值查询。
   • 空间索引 (R-Tree)：用于地理空间数据类型。
   • 全文索引：用于全文搜索。
   • 聚簇索引（InnoDB特有的）：数据行的物理顺序与键值的逻辑（索引）顺序相同。
   • 非聚簇索引：数据行的物理顺序与键值的逻辑（索引）顺序不同。

3. 优点：

   • 提高数据检索速度。
   • 加速表与表之间的连接。
   • 使用索引可以在查询的过程中，使用优化隐藏器，提高系统的性能。

4. 缺点：

   • 创建索引和维护索引需要时间，这会降低插入、更新和删除的速度，因为每当表中的数据更改时，索引也必须进行更新。
   • 索引需要占用磁盘空间。

5. 何时使用索引：

   • 在经常需要搜索的列上创建索引。
   • 在经常作为连接条件的列上创建索引。
   • 在经常需要根据范围进行搜索的列上创建索引。
   • 在经常需要排序的列上创建索引。
   • 在经常需要作为WHERE子句条件的列上创建索引。

6. 注意事项：

   • 不要过度索引，只创建真正需要的索引。
   • 删除不再需要的索引。
   • 使用EXPLAIN命令来查看查询是否使用了索引以及如何使用索引。
   • 定期分析表和索引的使用情况，以便进行优化。
   • 考虑使用复合索引（多个列的组合索引）来优化多列的查询条件。

7. 创建和删除索引：

   • 使用CREATE INDEX语句创建索引。
   • 使用DROP INDEX语句删除索引。
   • 在创建表时，也可以直接在CREATE TABLE语句中定义索引。

👉🏻主键索引

主键索引的创建

主键索引是MySQL中用于唯一标识表中每一行记录的索引。它确保表中的每一行数据都具有唯一的标识，并且不允许有空值。主键索引的创建可以通过以下两种方式：

1. 在创建表时直接指定

CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id)
);

在这个例子中，id列被设置为主键，MySQL会自动为这个列创建主键索引。

2. 在表创建后添加

如果表已经被创建，但是还没有主键索引，可以使用ALTER TABLE语句来添加：

ALTER TABLE students ADD PRIMARY KEY (id);

主键索引的查询

主键索引的查询通常非常快速，因为MySQL可以直接通过主键索引定位到数据。查询时，可以直接使用主键值作为WHERE子句的条件：

SELECT * FROM students WHERE id = 1;

在这个例子中，MySQL会利用主键索引快速定位到id为1的记录，并返回查询结果。

主键索引的删除

在某些情况下，可能需要删除主键索引。删除主键索引之前，需要确保没有其他依赖于主键的约束或关联关系存在。删除主键索引的步骤如下：

1. 使用SHOW CREATE TABLE命令查看表的结构

这个命令会显示表的创建语句，包括主键索引的定义。

SHOW CREATE TABLE students;

2. 确定要删除的主键索引的名称

虽然主键索引通常没有显式的名称（除非在创建时指定了），但在某些情况下，可能需要知道它的名称以便删除。通常，主键索引的名称就是“PRIMARY”。

3. 使用ALTER TABLE语句删除主键索引

ALTER TABLE students DROP PRIMARY KEY;

执行这个命令后，主键索引将被删除，并且id列将不再具有唯一性约束。需要注意的是，删除主键索引后，表中的数据可能不再具有唯一性标识，这可能会影响到数据的完整性和查询性能。因此，在删除主键索引之前，需要仔细考虑并确保没有其他依赖于主键的约束或关联关系存在。

👉🏻唯一索引

唯一索引的创建

唯一索引确保索引列中的每个值都是唯一的，与主键索引类似，但允许有空值。以下是创建唯一索引的几种方式：

1. 创建索引时指定唯一性

使用CREATE UNIQUE INDEX语句来创建唯一索引：

CREATE UNIQUE INDEX index_name ON table_name(column_name);

这里的index_name是你为索引指定的名称，table_name是表名，column_name是你希望创建索引的列名。

2. 在修改表结构时添加唯一索引

使用ALTER TABLE语句和ADD UNIQUE子句来添加唯一索引：

ALTER TABLE table_name ADD UNIQUE (column_name);

或者，如果你希望给唯一索引指定一个名称：

ALTER TABLE table_name ADD UNIQUE index_name (column_name);

3. 在创建表时直接指定唯一索引

在创建表的时候，可以直接在列定义后使用UNIQUE关键字来指定唯一索引：

CREATE TABLE table_name (
    column_name datatype UNIQUE,
    ...
);

或者，如果你希望为多个列组合创建唯一索引：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...,
    UNIQUE (column1, column2)
);

唯一索引的查询

要查询MySQL表中是否存在唯一索引，你需要查看该表的索引信息。以下是如何查询表中所有索引（包括唯一索引）的步骤：

1. 连接到MySQL数据库并选择目标数据库。
2. 使用SHOW INDEX FROM语句查询表的索引信息：

SHOW INDEX FROM table_name;

在返回的结果中，Non_unique列表示索引是否唯一（0表示唯一索引，1表示非唯一索引）。

唯一索引的删除

要删除MySQL表中的唯一索引，可以使用ALTER TABLE语句或DROP INDEX语句。以下是两种删除方法：

1. 使用ALTER TABLE语句删除

ALTER TABLE table_name DROP INDEX index_name;

这里的index_name是你要删除的唯一索引的名称。

2. 使用DROP INDEX语句删除

如果你知道索引的名称，并且想要明确地删除它，可以使用DROP INDEX语句：

DROP INDEX index_name ON table_name;

再次强调，index_name是你要删除的唯一索引的名称，table_name是表名。

👉🏻普通索引

普通索引的创建

普通索引（也称为单列索引或二级索引）是MySQL中最基本的索引类型，它没有任何限制，只是简单地加快数据的检索速度。以下是创建普通索引的几种方式：

1. 创建索引时指定

使用CREATE INDEX语句来创建普通索引：

CREATE INDEX index_name ON table_name(column_name);

这里的index_name是你为索引指定的名称，table_name是表名，column_name是你希望创建索引的列名。

2. 在修改表结构时添加索引

使用ALTER TABLE语句和ADD INDEX子句来添加普通索引：

ALTER TABLE table_name ADD INDEX index_name (column_name);

3. 在创建表时直接指定索引

在创建表的时候，可以直接在列定义后使用INDEX或KEY关键字来指定普通索引：

CREATE TABLE table_name (
    column_name datatype,
    ...,
    INDEX index_name (column_name)
);

或者：

CREATE TABLE table_name (
    column_name datatype,
    ...,
    KEY index_name (column_name)
);

普通索引的查询

要查询MySQL表中是否存在普通索引，你需要查看该表的索引信息。以下是如何查询表中所有索引（包括普通索引）的步骤：

1. 连接到MySQL数据库并选择目标数据库。
2. 使用SHOW INDEX FROM语句查询表的索引信息：

SHOW INDEX FROM table_name;

在返回的结果中，Key_name列表示索引的名称，Column_name列表示索引关联的列名，Non_unique列表示索引是否唯一（1表示非唯一索引，0表示唯一索引）。

普通索引的删除

要删除MySQL表中的普通索引，可以使用ALTER TABLE语句或DROP INDEX语句。以下是两种删除方法：

1. 使用ALTER TABLE语句删除

ALTER TABLE table_name DROP INDEX index_name;

这里的index_name是你要删除的普通索引的名称。

2. 使用DROP INDEX语句删除

如果你知道索引的名称，并且想要明确地删除它，可以使用DROP INDEX语句：

DROP INDEX index_name ON table_name;

再次强调，index_name是你要删除的普通索引的名称，table_name是表名。

请注意，在删除索引之前，你应该确保这个索引不再被查询或其他操作所使用，因为删除索引可能会影响查询性能。

👉🏻全文索引

全文索引的创建

MySQL的全文索引主要用于在InnoDB或MyISAM表中搜索信息。以下是创建全文索引的几种方式：

1. 在创建表时添加全文索引：

   CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       ...,
       FULLTEXT (column_name)
   );
   

   这里，FULLTEXT (column_name)指定了需要在哪个列上创建全文索引。

2. 在已经存在的表上添加全文索引：

   ALTER TABLE table_name ADD FULLTEXT (column_name);
   

   或者，如果你希望给全文索引指定一个名称：

   ALTER TABLE table_name ADD FULLTEXT index_name (column_name);
   

全文索引的查询

全文索引主要使用MATCH()和AGAINST()函数来进行搜索操作。基本语法格式为：

SELECT column_name(s) FROM table_name WHERE MATCH(col1, col2, ...) AGAINST (expr [search_modifier]);

其中，MATCH(col1, col2, ...)定义了要在哪些列上进行全文搜索，而AGAINST(expr [search_modifier])则指定了要搜索的字符串和搜索模式。

搜索模式可以是以下几种类型：

• 自然语言模式搜索（默认）：不使用任何修饰符。
• 布尔模式搜索：使用IN BOOLEAN MODE修饰符，支持操作符，如+表示包含，-表示不包含。
• 查询扩展模式搜索：使用WITH QUERY EXPANSION修饰符，相当于自然语言模式下的一个扩展，执行两次检索。

全文索引的删除

要删除MySQL表中的全文索引，可以使用ALTER TABLE语句结合DROP INDEX子句。语法如下：

ALTER TABLE table_name DROP INDEX index_name;

这里的index_name是你要删除的全文索引的名称。如果全文索引是在创建表时直接定义的，并且没有指定名称（即使用了默认的索引名），则可以通过查看表的索引信息来确定索引的名称。

注意：

• 全文索引只能用于MyISAM和InnoDB存储引擎的表。
• 全文索引只能为CHAR、VARCHAR或TEXT列创建。
• 在MySQL 5.7.6及更高版本中，全文索引支持中文、日文和韩文（CJK）的内置全文ngram解析器。
• 对于大型数据集，将数据加载到没有FULLTEXT索引的表中然后创建索引，通常比将数据加载到具有现有FULLTEXT索引的表中要快得多。
• 可以通过修改MySQL配置文件（如my.cnf）中的innodb_ft_min_token_size和innodb_ft_max_token_size参数来调整全文索引中单词的最小和最大长度。
• 如果需要支持3个字符的全文索引，可以将ft_min_word_len的值修改为3，并重启MySQL服务。

---

如上便是本期的所有内容了，如果喜欢并觉得有帮助的话，希望可以博个点赞+收藏+关注🌹🌹🌹❤️ 🧡 💛,学海无涯苦作舟,愿与君一起共勉成长