侧边栏壁纸
博主昵称
梦之中小俊

以前高中时,羡慕大学考试只要及格就行;现在大学了,怀念高中考试及不及格都行??

MySQL 常见索引类型介绍

梦之中小俊
2023-03-08 / 0 评论 / 414 阅读 / 推送成功!

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` */
本文共 个字数,平均阅读时长 ≈ 分钟,您已阅读:0时0分0秒。
3

打赏

评论 (0)

OωO
  • ::(呵呵)
  • ::(哈哈)
  • ::(吐舌)
  • ::(太开心)
  • ::(笑眼)
  • ::(花心)
  • ::(小乖)
  • ::(乖)
  • ::(捂嘴笑)
  • ::(滑稽)
  • ::(你懂的)
  • ::(不高兴)
  • ::(怒)
  • ::(汗)
  • ::(黑线)
  • ::(泪)
  • ::(真棒)
  • ::(喷)
  • ::(惊哭)
  • ::(阴险)
  • ::(鄙视)
  • ::(酷)
  • ::(啊)
  • ::(狂汗)
  • ::(what)
  • ::(疑问)
  • ::(酸爽)
  • ::(呀咩爹)
  • ::(委屈)
  • ::(惊讶)
  • ::(睡觉)
  • ::(笑尿)
  • ::(挖鼻)
  • ::(吐)
  • ::(犀利)
  • ::(小红脸)
  • ::(懒得理)
  • ::(勉强)
  • ::(爱心)
  • ::(心碎)
  • ::(玫瑰)
  • ::(礼物)
  • ::(彩虹)
  • ::(太阳)
  • ::(星星月亮)
  • ::(钱币)
  • ::(茶杯)
  • ::(蛋糕)
  • ::(大拇指)
  • ::(胜利)
  • ::(haha)
  • ::(OK)
  • ::(沙发)
  • ::(手纸)
  • ::(香蕉)
  • ::(便便)
  • ::(药丸)
  • ::(红领巾)
  • ::(蜡烛)
  • ::(音乐)
  • ::(灯泡)
  • ::(开心)
  • ::(钱)
  • ::(咦)
  • ::(呼)
  • ::(冷)
  • ::(生气)
  • ::(弱)
  • ::(狗头)
泡泡
阿鲁
颜文字
取消
  1. 头像
    6767 Lv.1
    iPhone · Safari
    沙发

    画图

    回复
  2. 头像
    6767 Lv.1
    Windows 10 · Google Chrome
    板凳

    表情

    回复
  3. 头像
    透露 Lv.2
    Android · QQ Browser
    第30楼

    649494848

    回复