索引

索引分类

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

创建索引

随表创建

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

创建普通索引

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) -- 创建普通索引
);

创建唯一索引

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

创建单列索引

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

创建组合索引

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创建索引

语法格式:

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

使用Create Index创建索引

语法格式:

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

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

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

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

01

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

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

02

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

查看索引 (show index)

语法格式：

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：显示评注。