Mysql索引知识梳理_mysql 5.7索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。我们拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的好处很多，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。2、更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。

索引创建

ALTER TABLE和CREATE INDEX的区别

使用ALTER TABLE语句创建索引

代码如下：

主键索引：alter table table_name add primary key (column) 
唯一索引：alter table table_name add unique [index_name] (column) 
普通索引：alter table table_name add index [index_name] (column) 
全文索引：alter table table_name add fulltext [index_name] (column) with parser ngram # 通过with parser ngram来指定查询规则
倒叙索引：alter table table_name add index [index_name] (column desc) 
前缀索引：alter table table_name add index [index_name] (column(10)) 
空间索引：alter table table_name add spatial [index_name] (column 
复合索引：alter table table_name add index [index_name] (column1, column2, column3)
1
2
3
4
5
6
7
8

索引名index_name可选，缺省时MySQL将根据第一个索引列赋一个名称，如下图：

使用CREATE INDEX语句对表增加索引

代码如下：

普通索引：create index index_name on table_name (column_list) 
唯一索引：create unique index index_name on table_name (column_list) 
全文索引：create fulltext index index_name on table_name (column_list);
复合索引：create index index_name on table_name (column1,column2);
倒叙索引：create index index_name on table_name (column1 desc); # asc为升序索引
前缀索引：create index index_name on table_name (column1(10)); # 取字符串的前10个字符作为索引数据
空间索引：create spatial index index_name on table_name (column_list); #只支持point类型列	
1
2
3
4
5
6
7

使用create方式创建索引必须要指定index_name。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现，其格式如下：

drop index index_name on table_name
alter table table_name drop index index_name
1
2

查看索引

使用show indexs from tblname;可以查看表索引信息，如下图（部分）：

Table

表的名称。

Non_unique

表示索引列数据是否可以重复，0：表示不可重复；1：表示可以重复。

Key_name

索引名称。

Seq_in_index

索引中的列序列号，从1开始。

Column_name

列名称。

Collation

数据以什么方式存储在索引中。在MySQL中，有值‘A’(升序)，‘D’(降序)或NULL(无分类)。

Cardinality

索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。

Sub_part

如果数据列的部分内容被编入了索引（如：前缀索引），则显示被索引的字符的数目。如果整列被编入索引，则为NULL。

Packed

指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null

如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Comment

略

常见索引种类

假设有如下一张表

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
1
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