MySQL 主要索引类型

Author：那随意了
发布时间：August 24, 2023
354 views
No comments
6013 words
Categories：数据库

MySQL 主要索引类型有如下几种：

1.主键索引
2.唯一索引
3.普通索引
4.空间索引
5.全文索引

假设有如下一张表

CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `u1` int unsigned NOT NULL DEFAULT '0',
  `u2` int unsigned NOT NULL DEFAULT '0',
  `u3` varchar(20) NOT NULL DEFAULT '',
  `u4` varchar(35) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

聚簇索引

索引与数据存放在一起，找到索引的同时也找到了数据；聚簇索引具有唯一性，一张表只有一个聚簇索引。
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个非空唯一索引代替。如果没有，InnoDB 会使用隐藏的_rowid 列来作为聚簇索引。

非聚簇索引

索引与数据分开存放，索引结构的叶子节点指向了数据的对应行。

主键索引

# 查看创建的索引关键词
PRIMARY KEY (`id`)

唯一索引

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

# 创建唯一索引
mysql> alter table t1 add unique idx_u1(`u1`);

# 查看创建的索引关键词
UNIQUE KEY `idx_u1` (`u1`)

单列索引

即索引创建在单个列上

# 创建单列索引
mysql> alter table t1 add index idx_u1(`u1`);

# 查看创建的索引关键词
KEY `idx_u1` (`u1`)

多列索引

创建多列索引时记得遵循最左匹配原则，把区分度较高的字段放前面

# 创建多列索引
mysql> alter table t1 add index idx_u1_u2(`u1`,`u2`);

# 查看创建的多列索引关键词
KEY `idx_u1_u2` (`u1`,`u2`)

前缀索引

即当字段的长度超过索引限制，可以为字段的部分前缀创建索引

# 创建前缀索引，取字符串的前4个字符
mysql> alter table t1 add index idx_u3(`u3`(4));

# 查看创建的索引关键词
KEY `idx_u3` (`u3`(4))

倒序索引

8.0 版本出的新功能，以往创建倒叙索引可以创建成功，但实际上仍然是顺序的

# 5.7 创建倒序索引
mysql> select u1 from t1 limit 10;
+----+
| u1 |
+----+
| 12 |
| 23 |
| 12 |
| 34 |
+----+
10 rows in set (0.00 sec)
# 创建倒序索引
mysql> alter table t1 add index idx_u3(u3 desc);
# 实际查出来还是顺序
mysql> select u1 from t1 limit 10;
+----+
| u1 |
+----+
| 12 |
| 12 |
| 12 |
| 12 |
+----+

# 8.0 创建倒序索引
mysql> select u1 from t1 limit 5;
+----+
| u1 |
+----+
| 12 |
| 23 |
| 12 |
| 34 |
+----+
5 rows in set (0.00 sec)

# 添加索引
mysql> alter table t1 add index idx_u1(u1 desc);

# 查询倒序索引成功
mysql> select u1 from t1 limit 5;
+----+
| u1 |
+----+
| 74 |
| 74 |
| 74 |
| 74 |
+----+
5 rows in set (0.00 sec)

函数索引

# 创建函数索引
mysql> alter table t1 add index idx_abs_u2 ((ABS(`u2`)));

# 查看创建的索引关键词
KEY `idx_abs_u2` ((abs(`u2`)))

表达式索引

# 创建表达式索引
mysql> alter table t1 add index idx_u1u2 ((u1 + u2));

# 查看创建的索引关键词
KEY `idx_u1u2` (((`u1` + `u2`)))

不可见索引

# 设置不可见（invisible），修改为可见（visible）
mysql> ALTER TABLE t1 ALTER INDEX idx_u1 INVISIBLE;

# 查看不可见索引关键词
KEY `idx_u1` (`u1` DESC) /*!80000 INVISIBLE */

# 解析查询时时全表扫描
mysql> explain select * from t1 order by u1 desc limit 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 50184 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

# 执行SQL时即便force index也不可用，提示不存在
mysql> explain select * from t1 force index(idx_u1)  order by u1 desc limit 3;
ERROR 1176 (42000): Key 'idx_u1' doesn't exist in table 't1'

空间索引

空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。

# 建表
CREATE TABLE `gis_position` (
  `id` int NOT NULL,
  `gis` geometry NOT NULL COMMENT '空间位置信息',
  `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,12)) VIRTUAL,
  primary key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空间位置信息';

# 创建空间索引
mysql> alter table gis_position add SPATIAL KEY `idx_gis` (`gis`) ;

# 查看索引关键信息
SPATIAL KEY `idx_gis` (`gis`)

全文索引

# 创建全文索引
mysql> alter table t1 add fulltext index idx_fulltext_u4(`u4`) with parser ngram;

# 查看索引关键信息
FULLTEXT KEY `idx_fulltext_u4` (`u4`) /*!50100 WITH PARSER `ngram` */

mysql5.7中全文检索

再mysql5.7.6之前,全文索引只支持英文全文索引,不支持中文全文索引,需要利用分词器吧中文段落处理成分词,然后存入数据库

从5.7.6开始,mysql内置里ngram全文解析器,用来支持中文,日文,韩文分词

mysql的全文索引主要用于全文字段的检索场景，支持char、varchar、text几个字段加全文索引，仅支持InNoDB与MyISAM引擎。
mysql内置了ngram解析器来支持中文、日文、韩文等语言的文本，全文索引支持通过建表语句来创建或者建表后新增。
mysql全文索引支持三种模式：
● 布尔模式(IN BOOLEAN MODE)
● 自然语言模式(NATURAL LANGUAGE MODE)
● 查询拓展(QUERY EXPANSION)

用法

ngram全文解析器

ngram就是一段文字里面连续的n个字的序列

ngram全文解析器能够对文本进行分词,单个单词是连续的n个字的序列

mysql中默认用全局变量ngram_token_size来配置ngram中n的大小,它的取值范围默认是1-10,默认值是2.通常ngram_token_size设置为要查询的单词的最小字数.如果需要搜索单字,就要把ngram_token_size设置为1.再默认值是2的情况下,搜索单字是得不到任何结果的.因为中文单词最少是2个汉字,推荐使用默认值2

全局变量ngram_token设置俩种方法

启动命令 mysqId时传参如下

mysqld --ngram_token_size=2

或者修改mysql配置文件my.ini中,末尾增加一行ngram_token_size的参数设置

在数据库创建的时候指定全文索引

CREATE TABLE `news`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '内容',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标题',
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX `idx_full_text`(`content`) WITH PARSER `ngram`
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

自然语言模式是默认全文检索模式，简单地说就是把检索关键词当做自然语言来处理，自然语言模式也等价于布尔模式中的无操作符模式，下面三种查询，结果是一样的：

-- 自然语言模式
select * from news where MATCH (content) against ('技术 武汉 光谷' IN NATURAL LANGUAGE MODE);
-- 布尔模式 无操作符
select * from news where MATCH (content) against ('技术 武汉 光谷' in Boolean MODE);
-- 默认模式
select * from news where MATCH (content) against ('技术 武汉 光谷');

转载自Mysql全文索引_青青子衣衿的博客-CSDN博客

转载自MySQL 常见索引类型介绍 (qq.com)

Last modification：November 17, 2023

如果觉得我的文章对你有用，请随意赞赏

MySQL 主要索引类型

那随意了 • 2023 年 08 月 24 日

<h1>MySQL 主要索引类型有如下几种：</h1><ul><li>1.主键索引</li><li>2.唯一索引</li><li>3.普通索引</li><li>4.空间索引</li><li>5.全文索引</li></ul><p>假设有如下一张表</p><pre><code>CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `u1` int unsigned NOT NULL DEFAULT '0',
  `u2` int unsigned NOT NULL DEFAULT '0',
  `u3` varchar(20) NOT NULL DEFAULT '',
  `u4` varchar(35) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB</code></pre><h2>聚簇索引</h2><blockquote><p>索引与数据存放在一起，找到索引的同时也找到了数据；聚簇索引具有唯一性，一张表只有一个聚簇索引。<br>聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个非空唯一索引代替。如果没有，InnoDB 会使用隐藏的<code>_rowid</code> 列来作为聚簇索引。</p></blockquote><h2>非聚簇索引</h2><blockquote><p>索引与数据分开存放，索引结构的叶子节点指向了数据的对应行。</p></blockquote><h2>主键索引</h2><pre><code># 查看创建的索引关键词
PRIMARY KEY (`id`)</code></pre><h2>唯一索引</h2><ul><li>索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一</li></ul><pre><code># 创建唯一索引
mysql&gt; alter table t1 add unique idx_u1(`u1`);

# 查看创建的索引关键词
UNIQUE KEY `idx_u1` (`u1`)</code></pre><h2>单列索引</h2><ul><li>即索引创建在单个列上</li></ul><pre><code># 创建单列索引
mysql&gt; alter table t1 add index idx_u1(`u1`);

# 查看创建的索引关键词
KEY `idx_u1` (`u1`)</code></pre><h2>多列索引</h2><ul><li>创建多列索引时记得遵循最左匹配原则，把区分度较高的字段放前面</li></ul><pre><code># 创建多列索引
mysql&gt; alter table t1 add index idx_u1_u2(`u1`,`u2`);

# 查看创建的多列索引关键词
KEY `idx_u1_u2` (`u1`,`u2`)</code></pre><h2>前缀索引</h2><ul><li>即当字段的长度超过索引限制，可以为字段的部分前缀创建索引</li></ul><pre><code># 创建前缀索引，取字符串的前4个字符
mysql&gt; alter table t1 add index idx_u3(`u3`(4));

# 查看创建的索引关键词
KEY `idx_u3` (`u3`(4))</code></pre><h2>倒序索引</h2><ul><li>8.0 版本出的新功能，以往创建倒叙索引可以创建成功，但实际上仍然是顺序的</li></ul><pre><code># 5.7 创建倒序索引
mysql&gt; select u1 from t1 limit 10;
+----+
| u1 |
+----+
| 12 |
| 23 |
| 12 |
| 34 |
+----+
10 rows in set (0.00 sec)
# 创建倒序索引
mysql&gt; alter table t1 add index idx_u3(u3 desc);
# 实际查出来还是顺序
mysql&gt; select u1 from t1 limit 10;
+----+
| u1 |
+----+
| 12 |
| 12 |
| 12 |
| 12 |
+----+

# 8.0 创建倒序索引
mysql&gt; select u1 from t1 limit 5;
+----+
| u1 |
+----+
| 12 |
| 23 |
| 12 |
| 34 |
+----+
5 rows in set (0.00 sec)

# 添加索引
mysql&gt; alter table t1 add index idx_u1(u1 desc);

# 查询倒序索引成功
mysql&gt; select u1 from t1 limit 5;
+----+
| u1 |
+----+
| 74 |
| 74 |
| 74 |
| 74 |
+----+
5 rows in set (0.00 sec)</code></pre><h2>函数索引</h2><pre><code># 创建函数索引
mysql&gt; alter table t1 add index idx_abs_u2 ((ABS(`u2`)));

# 查看创建的索引关键词
KEY `idx_abs_u2` ((abs(`u2`)))</code></pre><h2>表达式索引</h2><pre><code># 创建表达式索引
mysql&gt; alter table t1 add index idx_u1u2 ((u1 + u2));

# 查看创建的索引关键词
KEY `idx_u1u2` (((`u1` + `u2`)))</code></pre><h2>不可见索引</h2><pre><code># 设置不可见（invisible），修改为可见（visible）
mysql&gt; ALTER TABLE t1 ALTER INDEX idx_u1 INVISIBLE;

# 查看不可见索引关键词
KEY `idx_u1` (`u1` DESC) /*!80000 INVISIBLE */

# 执行SQL时即便force index也不可用，提示不存在
mysql&gt; explain select * from t1 force index(idx_u1)  order by u1 desc limit 3;
ERROR 1176 (42000): Key 'idx_u1' doesn't exist in table 't1'</code></pre><h2>空间索引</h2><blockquote><p>空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。</p></blockquote><pre><code># 建表
CREATE TABLE `gis_position` (
  `id` int NOT NULL,
  `gis` geometry NOT NULL COMMENT '空间位置信息',
  `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,12)) VIRTUAL,
  primary key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空间位置信息';

# 创建空间索引
mysql&gt; alter table gis_position add SPATIAL KEY `idx_gis` (`gis`) ;

# 查看索引关键信息
SPATIAL KEY `idx_gis` (`gis`)</code></pre><h2>全文索引</h2><pre><code># 创建全文索引
mysql&gt; alter table t1 add fulltext index idx_fulltext_u4(`u4`) with parser ngram;

# 查看索引关键信息
FULLTEXT KEY `idx_fulltext_u4` (`u4`) /*!50100 WITH PARSER `ngram` */</code></pre><h1>mysql5.7中全文检索</h1><p>再mysql5.7.6之前,全文索引只支持英文全文索引,不支持中文全文索引,需要利用分词器吧中文段落处理成分词,然后存入数据库</p><p>从5.7.6开始,mysql内置里ngram全文解析器,用来支持中文,日文,韩文分词</p><p>mysql的全文索引主要用于全文字段的检索场景，支持char、varchar、text几个字段加全文索引，仅支持InNoDB与MyISAM引擎。<br>mysql内置了ngram解析器来支持中文、日文、韩文等语言的文本，全文索引支持通过建表语句来创建或者建表后新增。<br>mysql全文索引支持三种模式：<br>● 布尔模式(IN BOOLEAN MODE)<br>● 自然语言模式(NATURAL LANGUAGE MODE)<br>● 查询拓展(QUERY EXPANSION)</p><h2>用法</h2><h2>ngram全文解析器</h2><p>ngram就是一段文字里面连续的n个字的序列</p><p>ngram全文解析器能够对文本进行分词,单个单词是连续的n个字的序列</p><p>mysql中默认用全局变量ngram_token_size来配置ngram中n的大小,它的取值范围默认是1-10,默认值是2.通常ngram_token_size设置为要查询的单词的最小字数.如果需要搜索单字,就要把ngram_token_size设置为1.再默认值是2的情况下,搜索单字是得不到任何结果的.因为中文单词最少是2个汉字,推荐使用默认值2</p><p>全局变量ngram_token设置俩种方法</p><p>启动命令 mysqId时传参如下</p><p><code>mysqld --ngram_token_size=2</code></p><p>或者修改mysql配置文件my.ini中,末尾增加一行ngram_token_size的参数设置</p><p>在数据库创建的时候指定全文索引</p><pre><code class="lang-sql">CREATE TABLE `news`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '内容',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标题',
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX `idx_full_text`(`content`) WITH PARSER `ngram`
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
</code></pre><p>自然语言模式是默认全文检索模式，简单地说就是把检索关键词当做自然语言来处理，自然语言模式也等价于布尔模式中的无操作符模式，下面三种查询，结果是一样的：</p><pre><code class="lang-sql">-- 自然语言模式
select * from news where MATCH (content) against ('技术 武汉 光谷' IN NATURAL LANGUAGE MODE);
-- 布尔模式 无操作符
select * from news where MATCH (content) against ('技术 武汉 光谷' in Boolean MODE);
-- 默认模式
select * from news where MATCH (content) against ('技术 武汉 光谷');</code></pre><p>转载自<span class="external-link"><a class="no-external-link" href="https://blog.csdn.net/Princeliu999/article/details/128539216" target="_blank"><i data-feather="external-link"></i>Mysql全文索引_青青子衣衿的博客-CSDN博客</a></span></p><p>转载自<span class="external-link"><a class="no-external-link" href="https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653938504&idx=1&sn=5ccca39452a073258f8588fae8a158be&chksm=bd3b79228a4cf034f2d52c35a7163a4d88761e859c9ba04d634eddbd2f35ed93a9991246b912&scene=27" target="_blank"><i data-feather="external-link"></i>MySQL 常见索引类型介绍 (qq.com)</a></span></p>

MySQL 主要索引类型

MySQL 主要索引类型有如下几种：

聚簇索引

非聚簇索引

主键索引

唯一索引

单列索引

多列索引

前缀索引

倒序索引

函数索引

表达式索引

不可见索引

空间索引

全文索引

mysql5.7中全文检索

用法

ngram全文解析器

Leave a Comment Cancel reply

docker常用命令

postForObject发送参数接方一直为null

git的常用命令

python、node、php、go、java性能对比测试

innodb引擎四大特性

深入理解JVM hotSpot的算法细节实现

java9模块化

RocketMQ5.0 Proxy

Fass

tire树

MySQL 主要索引类型