乐趣区

关于mysql:MySQL-常见索引类型介绍

jiezi
  • 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

微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

退出移动版