MySQL 常见索引类型介绍 mysql数据库sql

GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

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` */

Enjoy GreatSQL :)
# 关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili：
https://space.bilibili.com/13...
微信&QQ群：
可搜索添加GreatSQL社区助手微信好友，发送验证信息“加群”加入GreatSQL/MGR交流微信群
QQ群：533341697
【MySQL 常见索引类型介绍】微信小助手：wanlidbc