概述

本章，您将学习到 MySQL 的索引知识，内容包括：

索引基本原理
磁盘与索引的关系
索引的使用语法与原则

本文档将介绍索引的使用语法以及原则

索引的具体划分

提示

在 MySQL 中，若无特殊说明，InnoDB 中的索引都特指 BTREE/B+TREE 这种结构的索引类型。

这是 MySQL 中索引的具体划分：

从数据结构进行划分
- BTREE/B+TREE
- HASH 索引
- R-TREE
- Full-Text 索引
从物理存储进行划分
- 聚集索引
- 非聚集索引（也称二级索引、辅助索引）
按照字段特征进行划分
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 前缀索引
按照字段个数进行划分
- 单列索引（也称单值索引）
- 联合索引（也称复合索引、组合索引、多列索引、多值索引）

添加索引的方式

新建表时添加索引
在已有表的情况下添加索引
修改表的结构以添加索引

主键索引和唯一索引

由于这两个索引比较特殊，所有放在最前面进行说明。

在前面文章《MySQL基础12——DDL》的约束部分，作者说明了一些知识点：

主键约束、外键约束、唯一约束都会自动生成索引
列级约束支持 6 大约束（not null、primary key、unique、default、check、foreign key）；但表级约束只支持其中的 4 个约束（primary key、unique、check、foreign key），且只有表级约束才能自定义约束名
在表级约束中，主键约束的约束名不能被自定义，会被强制命名为 PRIMARY
主键约束时，插入的数据允许一个空字符、非空值且唯一
唯一约束时，插入的数据具有唯一性 —— 只能有一个空字符，可允许多个 null
当从表有外键约束时，主表必须要有主键约束或者唯一约束
建表时可以使用多个列级约束组合使用，约束之间没有书写的先后顺序

Q：主键约束、外键约束、外键约束都会自动生成索引吗？

是的。见下面的例子：

create database school;

use school;

create  table if not exists major(
  id int unsigned primary key,
  majorname varchar(25)
);

create table if not exists stu_info(
  id int,
  stuname char(10),
  gender char(2),
  age tinyint unsigned,
  class varchar(10)  unique not null default  '七年级二班',
  majorid int unsigned,
  foreign key(majorid)  references  `major`(id) 
);

create table if not exists teacher_info(
  tid int unsigned primary key,
  teachername varchar(25)
);

create table if not exists class_schedule(
  weekinfo char(10) default 'Sunday',
  tid int unsigned,
  teacher varchar(25),
  constraint `fk_class_schedule_tid` foreign key(tid) references `teacher_info`(tid)
);

show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| class_schedule   |
| major            |
| stu_info         |
| teacher_info     |
+------------------+
4 rows in set (0.00 sec)

可使用 show index from 表名; 来查阅表的索引信息：

show index from major\G;
*************************** 1. row ***************************
        Table: major
   Non_unique: 0
     Key_name: PRIMARY   ← ← 这里
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   ← ← 这里
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

show index from stu_info\G;
*************************** 1. row ***************************
        Table: stu_info
   Non_unique: 0
     Key_name: class    ← ← 这里
 Seq_in_index: 1
  Column_name: class
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   ← ← 这里
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: stu_info
   Non_unique: 1
     Key_name: majorid   ← ← 这里
 Seq_in_index: 1
  Column_name: majorid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE    ← ← 这里
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

show index from class_schedule\G;
*************************** 1. row ***************************
        Table: class_schedule
   Non_unique: 1
     Key_name: fk_class_schedule_tid   ← ← 这里
 Seq_in_index: 1
  Column_name: tid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE    ← ← 这里
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

\G 表示将输出的各列以单行方式进行垂直展现：

Table - 索引来源的表名称
Non_unique - 该索引列中的值是否非唯一。0 表示 no，1 表示 yes
Key_nane - 索引名称
Seq_in_index - 该列在索引中的位置，单列索引都是 1
Column_name - 索引所在的列名
Collation - 列以何种方式存储在索引中，A 表示升序（从小到大）；D 表示降序（从大到小）；NULL 表示未排序
Cardinality - 某列作为索引时不重复记录数的预估值，用来判断索引的创建是否合理。你可以把它理解为索引基数，即单个字段或多个字段的数据去重后的不同数值个数。MySQL 中有一个东西叫选择度，它是指索引基数与数据总行数的比值，其值应该尽可能地接近1，越接近1，创建索引后的效率与效果就越明显。比如你有一张学生表，如果有 1w 行的数据，但是如果对性别字段创建索引，则选择度=2/10000=0.0002 ，显然这时候创建的索引非常不合理。
Sub_part - 列中被编入的索引字符数量。部分索引（前缀索引）中，若仅索引列的前 n 个字符，则这里会显示相应的 n。当索引整列数据时，此处为 null
Packed - 压缩方式，没有则为 null
Null - 是否允许 null
Index_type - 索引类型
Comment - 注释
Index_Comment - 索引注释
Visible - 索引是否可见
Expression - 索引表达式

新建表时添加索引

普通索引（单列）

需要注意的是，单张表中可以包含多个单列索引。

示例如下所示：

create database testdb;

use testdb;

# 这里的 key 是关键字，后面的 index_emp_empname 表示普通单列索引的名称，括号里的 empname 则是针对的列
create table if not exists emp(
  empname varchar(5) not null comment '员工姓名',
  gender enum('男','女','保密') comment '员工性别',
  key index_emp_empname(empname)
);

# 查看索引信息
show index from emp;
*************************** 1. row ***************************
        Table: emp
   Non_unique: 1
     Key_name: index_emp_empname
 Seq_in_index: 1
  Column_name: empname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

普通索引（多列）

use testdb;

# 创建一张水果的价格表
create table if not exists fruit_price(
  barcode int unsigned not null comment '条码',
  fname varchar(8) not null comment '水果名称',
  quantifier varchar(3) default '个' comment '单位',
  price decimal(4,2) comment '价格',
  key index_fruit_price(fname,price)
);

show index from fruit_price\G;
*************************** 1. row ***************************
        Table: fruit_price
   Non_unique: 1
     Key_name: index_fruit_price
 Seq_in_index: 1
  Column_name: fname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: fruit_price
   Non_unique: 1
     Key_name: index_fruit_price
 Seq_in_index: 2
  Column_name: price
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

唯一索引（单列）

use testdb;

create table if not exists account(
companyname varchar(20)  comment '账号所属公司名称',
loginname varchar(25)   comment '登录的账号',
`date` date comment '注册日期',
lastuse date  comment '最后一次使用的时间',
unique key index_account_loginname(loginname)
);

show index from account\G;
*************************** 1. row ***************************
        Table: account
   Non_unique: 0
     Key_name: index_account_loginname
 Seq_in_index: 1
  Column_name: loginname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

唯一索引（多列）

use testdb;

create table if not exists album(
  copyrightname  varchar(20)  comment '版权所属公司名称',
  singer  varchar(15)  comment '歌手名称',
  `comment` varchar(300) comment '专辑的说明信息',
  `date` date comment '发布日期',
  name varchar(20) comment '专辑名称',
  unique key index_singer_name(singer,name)
);

show index from album\G;
*************************** 1. row ***************************
        Table: album
   Non_unique: 0
     Key_name: index_singer_name
 Seq_in_index: 1
  Column_name: singer
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: album
   Non_unique: 0
     Key_name: index_singer_name
 Seq_in_index: 2
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

全文索引（单列）

使用时注意

Innodb 中，全文索引仅针对数据类型为 char、varchar、text 的列，并且每张表仅允许有一个全文索引。全文索引中，一张表的同一个 Key_name （通过 show index from 表名; 查看）都可以被看作单个全文索引。

全文索引：其作用是为了解决长文本的模糊查询的速度问题。模糊查询即 DQL 中有 like 关键字的查询（如 where xxx like '%test%'）

use testdb;

create table if not exists title1(
  name varchar(10),
  `comment`  text,
  fulltext key index_fulltext(name)
);

show index from title1\G;
*************************** 1. row ***************************
        Table: title1
   Non_unique: 1
     Key_name: index_fulltext
 Seq_in_index: 1
  Column_name: name
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT  ← ← 这里就不是 BTREE 了
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

全文索引（多列）

use testdb;

create table if not exists title2(
  name varchar(10),
  `comment`  text,
  fulltext key index_fulltext_double(name,`comment`)
);

*************************** 1. row ***************************
        Table: title2
   Non_unique: 1
     Key_name: index_fulltext_double  ← ←
 Seq_in_index: 1
  Column_name: name
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: title2
   Non_unique: 1
     Key_name: index_fulltext_double  ← ←
 Seq_in_index: 2
  Column_name: comment
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT  
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

由于 Key_name 都是 index_fulltext_double，因此这可以被看作同一个全文索引。

语法兼容

新建表时添加的索引，关键字 key 也可以替换为 index

在已有表时添加索引

需要使用 DDL 中的 alter table 语法。

普通索引（单列）

语法为 —— alter table 表名 add key 索引名(字段名);

普通索引（多列）

语法为 —— alter table 表名 add key 索引名(字段名1,字段名2);

唯一索引（单列）

语法为 —— alter table 表名 add unique key 索引名(字段名);

唯一索引（多列）

语法为 —— alter table 表名 add unique key 索引名(字段名1,字段名2);

全文索引（单列）

语法为 —— alter table 表名 add fulltext key 索引名(字段名);

全文索引（多列）

语法为 —— alter table 表名 add fulltext key 索引名(字段名1,字段名2);

语法兼容

在已有表时添加索引，关键字 key 也可以替换为 index

查看索引

语法为 —— show index from 表名;

删除索引

语法一

drop index 索引名 on 表名;

索引名称可通过 show index from 表名; 的 Key_name 得到

语法二

alter table 表名 drop index 索引名;

在 MySQL 中，由于主键约束、唯一约束、外键约束会自动生成相关的索引，若使用语法一或语法二对这些索引进行删除，可能会遇到语法错误。由于这三种约束与其索引强关联，换言之，您可以通过删除这些约束来达到间接删除索引的目的：

对于主键约束（MySQL 中只能通过这种方式删除主键索引） - alter table 表名 drop primary key;
对于唯一约束或外键约束 - alter table 表名 drop constraint 自动生成的约束名; （自动生成的约束名可通过 show create table 表名; 查看得到）

其他索引

聚集索引：MySQL 中，聚集索引通常指主键约束生成的主键索引。若表中的列无主键约束（主键索引），则会选择首个具有唯一约束（唯一索引）的列作为聚集索引。若一张表既没有主键约束（主键索引），也没有唯一约束（唯一索引），那么 InnoDB 会自动创建一个 GEN_CLUST_INDEX 的隐式聚集索引。请注意！一张表只能有一个聚集索引。

非聚集索引（二级索引、辅助索引）：除主键索引与唯一索引外的其他索引，都可以被称为二级索引。

前缀索引：给字段数据的前 n 个字符添加索引，即前缀索引，可应用在单列以及多列上。MySQL 中规定，只有字符类（char、varchar）和二进制型（binary、varbinary）的数据才能使用前缀索引。前缀索引的示例如下：

use testdb;

create table vt1(
  name varchar(20),
  key index_name(name(5))
);

show index from vt1\G;
*************************** 1. row ***************************
        Table: vt1
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 5  ← ← 被编入的索引字符数量，由于创建表时前缀索引是 5（name(5)），因此此处也就是 5
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

删除这些示例库：

use world;

drop database school,testdb;

索引的使用原则

对查询频次高、数据量大的表建立索引
应该选择选择度高（索引基数与数据总行数的比值）的字段来应用索引
一张表的索引数量并不是越多越好，其应该尽可能地少，索引过多，除了增加表的维护之外，也降低了 DML 操作的效率
修改时的性能与查询效率不可以同时兼得，若修改的频次远远大于查询频次时，不应该创建索引
删除不再使用或很少使用的索引，这会释放出一定的存储空间
当表中的行数据被大量更新时，您还应该同时维护相关的索引（可能出现索引失效的问题）
根据实际需要选择前缀索引或全文索引

用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。

MySQL

登录管理

标签云

概述

索引的具体划分

添加索引的方式

主键索引和唯一索引

新建表时添加索引

普通索引（单列）

普通索引（多列）

唯一索引（单列）

唯一索引（多列）

全文索引（单列）

全文索引（多列）

语法兼容

在已有表时添加索引

普通索引（单列）

普通索引（多列）

唯一索引（单列）

唯一索引（多列）

全文索引（单列）

全文索引（多列）

语法兼容

查看索引

删除索引

语法一

语法二

其他索引

索引的使用原则

发送评论编辑评论

登 录 管 理

标 签 云

概述

索引的具体划分

添加索引的方式

主键索引和唯一索引

新建表时添加索引

普通索引（单列）

普通索引（多列）

唯一索引（单列）

唯一索引（多列）

全文索引（单列）

全文索引（多列）

语法兼容

在已有表时添加索引

普通索引（单列）

普通索引（多列）

唯一索引（单列）

唯一索引（多列）

全文索引（单列）

全文索引（多列）

语法兼容

查看索引

删除索引

语法一

语法二

其他索引

索引的使用原则

发送评论 编辑评论

推荐文章

登录管理

标签云

发送评论编辑评论