MySQL 常见索引类型介绍

Only1You1

发布于 2022-8-22 16:02

4480浏览

0收藏

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=InnoDB1.
2.
3.
4.
5.
6.
7.
8.

聚簇索引

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

非聚簇索引

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

主键索引

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

唯一索引

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

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

# 查看创建的索引关键词
UNIQUE KEY `idx_u1` (`u1`)1.
2.
3.
4.
5.

单列索引

● 即索引创建在单个列上

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

# 查看创建的索引关键词
KEY `idx_u1` (`u1`)1.
2.
3.
4.
5.

多列索引

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

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

# 查看创建的多列索引关键词
KEY `idx_u1_u2` (`u1`,`u2`)1.
2.
3.
4.
5.

前缀索引

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

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

# 查看创建的索引关键词
KEY `idx_u3` (`u3`(4))1.
2.
3.
4.
5.

倒序索引

● 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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.

函数索引

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

# 查看创建的索引关键词
KEY `idx_abs_u2` ((abs(`u2`)))1.
2.
3.
4.
5.

表达式索引

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

# 查看创建的索引关键词
KEY `idx_u1u2` (((`u1` + `u2`)))1.
2.
3.
4.
5.

不可见索引

# 设置不可见（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'
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

空间索引

空间索引是对空间数据类型的字段建立的索引，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`)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

全文索引

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

# 查看索引关键信息
FULLTEXT KEY `idx_fulltext_u4` (`u4`) /*!50100 WITH PARSER `ngram` */1.
2.
3.
4.
5.

分类

数据库

标签

GreatSQL

已于2022-8-22 16:02:39修改

51CTO

51CTO博客

51CTO学堂

MySQL 常见索引类型介绍

订阅鸿蒙技术特刊，精选内容抢先看