【MYSQL】其他索引的创建使用方式

2024-04-18 07:05:35

MySQL其他索引的创建使用方式

前面的案例中，聊到了咱们有三种方式创建索引，在创建时可通过INDEX、KEY两个关键字创建，但这种方式建立的索引仅是普通索引，接着再来聊一聊MySQL数据库其他类型的索引该如何创建以及使用。

但不管是何种类型的索引，都可以通过前面聊到的三种方式创建。

1.1、唯一索引的创建与使用

唯一索引在创建时，需要通过UNIQUE关键字创建：如下：

-- 方式①
CREATE UNIQUE INDEX indexName ON tableName (columnName(length));

-- 方式②
ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);

-- 方式③
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  UNIQUE INDEX [indexName] (columnName(length))  
);

在已有的表基础上创建唯一索引时要注意，如果选用的字段，表中字段的值存在相同值时，这时唯一索引是无法创建的，比如：

SELECT * FROM `zz_article`;
+------------+--------------------------+-------------------+
| article_id | article_name             | special_column    |
+------------+--------------------------+-------------------+
|          1 | MySQL架构篇：.......     | 《全解MySQL》     |
|          2 | MySQL执行篇：.......     | 《全解MySQL》     |
|          3 | MySQL设计篇：.......     | 《全解MySQL》     |
|          4 | MySQL索引篇：.......     | 《全解MySQL》     |
|          5 | MySQL索引篇：.......     | 《全解MySQL》     |
+------------+--------------------------+-------------------+

CREATE UNIQUE INDEX i_article_name ON zz_article (article_name);

比如上述文章表中，第4、5条数据是重复的，此时创建利用SQL语句创建唯一索引，就会抛出1062错误码：

ERROR 1062 (23000): Duplicate entry 'MySQL索引篇：.......' for key 'i_article_name'

在这种情况下，就只能先删除重复数据，然后才能创建唯一索引成功。

同时，当唯一索引创建成功后，它同时会对表具备唯一约束的作用，当再使用INSERT语句插入相同值时，会同样会抛出1062错误码：

INSERT INTO `zz_article` VALUES(6,"MySQL索引篇：.......","《全解MySQL》");

1062 - Duplicate entry 'MySQL索引篇：.......' for key 'i_article_name'

这里会提示你插入的哪个值，已经在表中存在，因此无法插入当前这条数据。

1.2、主键索引的创建与使用

前面聊到过，主键索引其实是一种特殊的唯一索引，但主键索引却并不是通过UNIQUE关键字创建的，而是通过PRIMARY关键字创建：

-- 方式①
ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);

-- 方式②
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  PRIMARY KEY [indexName] (columnName(length))  
);

在这里要注意：

创建主键索引时，必须要将索引字段先设为主键，否则会抛1068错误码。
这里也不能使用CREATE语句创建索引，否则会提示1064语法错误。
同时创建索引时，关键字要换成KEY，并非INDEX，否则也会提示语法错误。

还是以之前的文章表为例，如下：

-- 对非主键字段创建主键索引
ALTER TABLE zz_article ADD PRIMARY KEY i_special_column(special_column);
-- 报错信息如下：
1068 - Multiple primary key defined

-- 使用CREATE关键字创建主键索引
CREATE PRIMARY KEY i_article_id ON zz_article (article_id);
-- 报错信息如下：
1064 - You have an error in your SQL syntax; check....

-- 使用INDEX关键字创建索引
ALTER TABLE zz_article ADD PRIMARY INDEX i_article_id(article_id);
-- 报错信息如下：
1064 - You have an error in your SQL syntax; check....

-- 创建主键索引正确的方式
ALTER TABLE zz_article ADD PRIMARY KEY i_article_id(article_id);

当然，一般主键索引都会在建表的DDL语句中创建，不会在表已经建立后再创建。

但似乎无论在讲普通索引，还是唯一索引、主键索引的时候，我们都没有讲如何使用这些创建好的索引查询数据，其实这一点无需咱们考虑，参考之前《SQL执行篇》中查询语句的执行流程，在一条SELECT语句来到MySQL时，会经历优化器优化的过程，而优化器则会自动帮咱们选择一个最合适的索引查询数据。当然，前提是查询条件中涉及到了索引字段才行。

前面也说过，你不想让优化器自动选择，也可以手动通过FORCE INDEX关键字强制指定。

1.3、全文索引的创建与使用

全文索引和其他索引不同，首先如果你想要创建全文索引，那么MySQL版本必须要在5.7及以上，同时使用时也需要手动指定，一起来先看看如何创建全文索引，此时需要使用FULLTEXT关键字：

-- 方式①
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);

-- 方式②
CREATE FULLTEXT INDEX indexName ON tableName(columnName);

不过在创建全文索引时，有三个注意点：

5.6版本的MySQL中，存储引擎必须为MyISAM才能创建。
创建全文索引的字段，其类型必须要为CHAR、VARCHAR、TEXT等文本类型。
如果想要创建出的全文索引支持中文，需要在最后指定解析器：with parser ngram。

此时还依旧是以文章表为例，为文章名称字段创建一个全文索引，命令如下：

ALTER TABLE 
    zz_article ADD 
FULLTEXT INDEX 
    ft_article_name(article_name) 
WITH PARSER NGRAM;

创建好全文索引后，当你想要使用全文索引时，优化器这时不能自动选择，因为全文索引有自己的语法，但在了解如何使用之前，得先清楚两个概念：最小搜索长度和最大搜索长度，先来看看全文索引的一些参数，可通过show variables like '%ft%';命令查询，如下：

多余的参数就不介绍了，重点讲一下其中的几个重要参数：

ft_min_word_len：使用MyISAM引擎的表中，全文索引最小搜索长度。
ft_max_word_len：使用MyISAM引擎的表中，全文索引最大搜索长度。
ft_query_expansion_limit：MyISAM中使用with query expansion搜索的最大匹配数。
innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小搜索长度。
innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大搜索长度。

那么究竟做最小搜索长度、最大搜索长度的作用是什么呢？其实这个是一个限制，对于长度小于最小搜索长度和大于最大搜索长度的词语，都无法触发全文索引。也就是说，如果想要使用全文索引对一个词语进行搜索，那这个词语的长度必须在这两个值之间。

其实这两个值自己可以手动调整的，最小值可以手动调整为1，MyISAM引擎的最大值可以调整为3600，但InnoDB引擎最大似乎就是84。

OK~，了解全文索引中的一些概念后，接下来看看如何使用全文索引，全文索引中有两个专门用于检索的关键字，即MATCH(column)、AGAINST(关键字)，同时这两个检索函数也支持三种搜索模式：

自然语言模式(默认搜索模式)
布尔搜索模式
查询拓展搜索

MATCH()主要是负责指定要搜索的列，这里要指定创建全文索引的字段，AGAINST()则指定要搜索的关键字，也就是要搜索的词语，接下来简单的讲一下三种搜索模式。

自然语言模式

这种模式也是在使用全文索引时，默认的搜索模式，使用方法如下：

+------------+--------------------------+-------------------+
| article_id | article_name             | special_column    |
+------------+--------------------------+-------------------+
|          1 | MySQL架构篇：.......     | 《全解MySQL》     |
|          2 | MySQL执行篇：.......     | 《全解MySQL》     |
|          3 | MySQL设计篇：.......     | 《全解MySQL》     |
|          4 | MySQL索引篇：.......     | 《全解MySQL》     |
+------------+--------------------------+-------------------+

SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL');

-- 运行结果如下：
+--------------+
| 搜索结果数量 |
+--------------+
|           4 |
+--------------+

一眼看过去，SQL就能看懂，毕竟都可以排版了一下SQL，不过多介绍了。唯一要注意的是，如果给定的关键词长度小于默认的最小搜索长度，那是无法使用全文索引的，比如下述这条SQL就不会触发：

SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('M');

布尔搜索模式

布尔搜索模式有些特殊，因为在这种搜索模式中，还需要掌握特定的搜索语法：

+：表示必须匹配的行数据必须要包含相应关键字。
-：和上面的+相反，表示匹配的数据不能包含相应的关键字。
>：提升指定关键字的相关性，在查询结果中靠前显示。
<：降低指定关键字的相关性，在查询结果中靠后显示。
~：表示允许出现指定关键字，但出现时相关性为负。
*：表示以该关键字开头的词语，如A*，可以匹配A、AB、ABC....
""：双引号中的关键字作为整体，检索时不允许再分词。
"X Y"@n：""包含的多个词语之间的距离必须要在n之间，单位-字节，如：
- 竹子熊猫@10：表示竹子和熊猫两个词语之间的距离要在10字节内。
.......

举个几个例子使用一下，如下：

-- 查询文章名中包含 [MySQL] 但不包含 [设计] 的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL -设计' IN BOOLEAN MODE);

-- 查询文章名中包含 [MySQL] 和 [篇] 的数据，但两者间的距离不能超过10字节
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL 篇"@10' IN BOOLEAN MODE);
    
-- 查询文章名中包含[MySQL] 的数据，
--    但包含 [执行] 关键字的行相关性要高于包含 [索引] 关键字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL +(>执行 <索引)' IN BOOLEAN MODE);

-- 查询文章名中包含 [MySQL] 的数据，但包含 [设计] 时则将相关性降为负
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL ~设计' IN BOOLEAN MODE);

-- 查询文章名中包含 [执行] 关键字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('执行*' IN BOOLEAN MODE);

-- 查询文章名中必须要包含 [MySQL架构篇] 关键字的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL架构篇"' IN BOOLEAN MODE);

同样的，上述的SQL语句应该都能看明白，最后的IN BOOLEAN MODE表示使用布尔搜索模式，除此外，大家唯一疑惑的就在于：相关性这个词，其实这个词也不难理解，就是检索数据后，数据的优先级顺序，当相关性越高，对应数据在结果中越靠前，当相关性为负，则相应的数据排到最后。

查询拓展搜索

查询拓展搜索其实是对自然语言搜索模式的拓展，比如举个例子：

SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL' WITH QUERY EXPANSION);

在自然语言模式的查询语句基础上，最后面多加一个WITH QUERY EXPANSION表示使用查询拓展搜索，这种模式下会比自然语言模式多一次检索过程，比如上述的例子中：

首先会根据指定的关键字MySQL进行一次全文检索。
然后第二阶段还会对指定的关键进行分词，然后再进行一次全文检索。

之前介绍全文索引参数时，也列出来了一个名为ft_query_expansion_limit的参数，这个参数就是控制拓展搜索时的拓展行数的，最大可以调整到1000。但由于Query Expansion的全文检索可能带来许多非相关性的查询结果，因此在实际情况中要慎用！！！

实际上，全文索引引入MySQL后，可以用它代替之前的like%模糊查询，效率会更高。

1.4、空间索引的创建与使用

空间索引这玩意儿实际上很多项目不会用到，我用的次数也不多，但如果你要用到这个索引，那可以通过SPATIAL关键字创建，如下：

ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);

但在创建空间索引的时候，有几个注意点需要牢记：

目前MySQL常用引擎中，仅有MyISAM支持空间索引，所以表引擎必须要为它。
空间索引必须要建立在类型为GEOMETRY、POINT、LINESTRING、POLYGON的字段上。

这个用的较少，就不展开细聊了~

1.5、联合索引的创建与使用

联合索引呢，实际上并不是一种逻辑索引分类，它是索引的一种特殊结构，前面给出的所有案例中，都仅仅是在单个字段的基础上建立索引，而联合索引的意思是可以使用多个字段建立索引。那该如何创建联合索引呢，不需要特殊的关键字，方法如下：

CREATE INDEX indexName ON tableName (column1(length),column2...);
ALTER TABLE tableName ADD INDEX indexName(column1(length),column2...);

你可以使用INDEX关键字，让多个列组成一个普通联合索引
也可以使用UNIQUE INDEX关键字，让多个列组成一个唯一联合索引
甚至还可以使用FULLTEXT INDEX关键字，让多个列组成一个全文联合索引
.......

但是前面也提过，SELECT语句的查询条件中，必须包含组成联合索引的第一个字段，此时才会触发联合索引，否则是无法使用联合索引的。

二、索引初识篇总结

OK~，在本篇中就对MySQL的索引机制有了全面认知，从索引的由来，到索引概述、索引管理、索引分类、唯一/全文/联合/空间索引的创建与使用等内容，进行了全面概述，相信本章看下来，足够让你对MySQL索引机制有一个系统化的体系，那么我们下篇再见。

码农公寓