索引

索引分类

  • 普通索引
  • 唯一索引
  • 单列索引
  • 组合(多列)索引
  • 全文索引
  • 空间索引

创建索引

随表创建

在创建表的过程中一并创建索引

创建普通索引

1
2
3
4
5
6
7
create table book(
bookid int not null,
bookname varchar(255) not null,
authors varchar(255) not null,
year_publication year not null,
index (year_publication) -- 创建普通索引
);

创建唯一索引

1
2
3
4
5
create table t1(
id int not null,
name char(30) not null,
unique index UniqIdx (id) -- 创建唯一索引
);

创建单列索引

1
2
3
4
5
create table t2(
id int not null,
name char(50) null,
index SingIdx (name(20)) -- 创建单列索引
);

创建组合索引

1
2
3
4
5
6
7
create table t3(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index MultiIdx (id, name, age) -- 创建多列索引 -- 查询时需遵循“最左前缀”原则
);

后期创建

在已存在的表中创建索引

两种方法:

  1. 使用Alter Table语句创建
  2. 使用Create Index语句创建

使用ALter Table创建索引

语法格式:
1
alter table <表名> add [unique|fulltext|spatial] [index|key] [索引名] (字段名[长度],...) [asc|desc]

使用Create Index创建索引

语法格式:
1
create [unique|fulltext|spatial] index <索引名> on <表名> (字段名[长度],...) [asc|desc]

组合索引的“左前缀”原则

查询顺序遵循最左前缀的情况

1
explain select * from t3 where id = 1 and name = 'joe';
01

查询顺序不遵循左前缀的情况

1
explain select * from t3 where name = 'joe' and age = 1;
02

对比possible_keys及key项可知,仅遵循左前缀的查询语句使用了索引。

查看索引 (show index)

语法格式:

1
show index from <表名> [from <数据库名>]
查询样例

查询结果参数说明:

  • Table:表示创建索引的数据表名,这里是 tb_stu_info2 数据表。
  • Non_unique:表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。
  • Key_name表示索引的名称
  • Seq_in_index:表示该列在索引中的位置,如果索引是单列的,则该列的值为1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。
  • Column_name表示定义索引的列字段
  • Collation:表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。
  • Cardinality:索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
  • Sub_part:表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为NULL。
  • Packed:指示关键字如何被压缩。若没有被压缩,值为 NULL。
  • Null:用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。
  • Index_type:显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。
  • Comment:显示评注。

索引
http://example.com/2022/11/07/索引/
作者
Charry
发布于
2022年11月7日
许可协议