MySQL索引的创建使用方式-KeepStudy

MySQL索引的创建使用方式

### 唯一索引的创建与使用
唯一索引在创建时，需要通过**UNIQUE**关键字创建：如下：
```sql
-- 方式①
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))  
);
```
在已有的表基础上创建唯一索引时要注意，如果选用的字段，表中字段的值存在相同值时，这时唯一索引是无法创建的，会抛出1062错误码：
```sql
ERROR 1062 (23000): Duplicate entry 'MySQL索引篇：.......' for key 'i_article_name'
```
在这种情况下，就只能先删除重复数据，然后才能创建唯一索引成功。

同时，当唯一索引创建成功后，它同时会对表具备唯一约束的作用，当再使用INSERT语句插入相同值时，会同样会抛出1062错误码，提示你插入的哪个值，已经在表中存在，因此无法插入当前这条数据。

### 主键索引的创建与使用
主键索引其实是一种特殊的唯一索引，但主键索引却并不是通过UNIQUE关键字创建的，而是通过PRIMARY关键字创建：
```sql
-- 方式①
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，否则也会提示语法错误。

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

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

```sql
-- 方式①
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。

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

![](/uploads/20230215/2023021512015517621.png)

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

- 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()则指定要搜索的关键字，也就是要搜索的词语，接下来简单的讲一下三种搜索模式。

###### 自然语言模式
这种模式也是在使用全文索引时，默认的搜索模式，使用方法如下：
```sql
SELECT * FROM TableName WHERE MATCH(列) AGAINST('搜索词');
```
例子
```sql
SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('M');
```

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

```sql
-- 查询文章名中包含 [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表示使用布尔搜索模式，除此外，大家唯一疑惑的就在于：相关性这个词，其实这个词也不难理解，就是检索数据后，数据的优先级顺序，当相关性越高，对应数据在结果中越靠前，当相关性为负，则相应的数据排到最后。

###### 查询拓展搜索
```sql
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%模糊查询，效率会更高。

### 空间索引的创建与使用
空间索引这玩意儿实际上很多项目不会用到，我用的次数也不多，但如果你要用到这个索引，那可以通过SPATIAL关键字创建，如下：
```sql
ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);
```

但在创建空间索引的时候，有几个注意点需要牢记：
- 目前MySQL常用引擎中，仅有MyISAM支持空间索引，所以表引擎必须要为它。
- 空间索引必须要建立在类型为GEOMETRY、POINT、LINESTRING、POLYGON的字段上。

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

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

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

顶部

MySQL索引

目录

相关推荐