索引01之初始索引

初识索引

一：如果从来没有过索引

1：全盘扫描

由于MySQL是作为存储层部署在业务系统的最后端，所有的业务数据最终都要入库落盘

随着一个项目在线上运行的时间越来越久，数据库中的数据量自然会越来越多，而数据体积出现增长后，当需要从表查询一些数据时，效率会越发低下。

在正常情况下，表的查询性能和数据量是成反比的，也就是数据越多，查询越慢。

假设有如下数据和查询语句：

SELECT * FROM zz_student WHERE name = "张九";

上面给出了一张学生表，其中有七位学生信息，而此时要查询姓名为「张九」的学生信息时，MySQL底层是如何检索数据的呢？

会触发磁盘IO，对表中的数据进行逐条读取并判断

也就是说，在这里想要查找到符合要求的数据，至少要经过六次磁盘IO才能检索到目标（暂且先不考虑局部性读取原理与随机IO）。

那假设这个表中有1000W条数据呢？要查的目标数据位于表的900W行以后怎么办？

岂不是要触发几百万次磁盘IO才能检索到数据啊，如果真的这样去干，其效率大家可想而知。

在这种情况下，又该如何去提升数据库的查询性能呢？

因为查询往往都是一个业务系统中最频繁的操作，一般项目的写/读请求比例都遵循三七定律

就是30%的请求会涉及到写库操作，另外70%则属于查库类型的操作。

2：字典目录

小时候由于刚接触汉字，很多字都不认识，所以通常每个人小时候都会拥有一本「新华字典」

但一本字典那么厚，我们是一页页去翻的吗？并不是，字典中有目录索引，我们可以根据音节、偏旁等方式查找不认识的字。

在「新华字典」中一页页翻找某个汉字，就类似于我们前面给出的全表扫描方式，效率特别特别低，而通过目录索引则能够在很短的时间内找到目标汉字。

既然字典中都存在目录索引页，能帮助小时候的我们快速检索汉字，那这个思想能否应用到数据库中来呢？

答案是当然可以，并且MySQL也提供了索引机制，索引是数据库中的核心组件之一，一张表中建立了合适的索引后，在面对海量数据查询时，能够事半功倍

二：索引机制概述

经过上述「新华字典」的案例后可得知：索引就是用来帮助表快速检索目标数据的

1：MySQL索引的创建方式

1.1：使用CREATE语句创建

----> 创建索引【create index indexName】在【on】xx表的xx字段

create index indexName on tableName (columnName(length)) [asc|desc]
# 或者将index -> key一样，都是创建普通索引
create key indexName on tableName (columnName(length)) [asc|desc]

这种创建方式可以给一张已存在的表结构添加索引，其中需要指定几个值：

• indexName：当前创建的索引，创建成功后叫啥名字。
• tableName：要在哪张表上创建一个索引，这里指定表名。
• columnName：要为表中的哪个字段创建索引，这里指定字段名。
• length：如果字段存储的值过长，选用值的前多少个字符创建索引。
• ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。

1.2：使用ALTER语句创建

----> 修改xx表【alter table tableName】，为xx表添加索引【add index】作用于xx字段

alter table tableName add index indexName(columnName(length)) [asc|desc]

这里的参数和create方式的参数含义都相同

1.3：建表时DDL语句中创建

create table tableName(  
  columnName1 int(8) not null,   
  columnName2 ....,
  .....,
  index [indexName] (columnName(length))  
);

这种方式就比较适合在库表设计时，已经确定了索引项的情况下建立。

2：查询索引，删除索引，指定索引

2.1：查询索引

不管通过哪种方式建立索引，本质上创建的索引都是相同的

当索引创建完成后，可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引

每个字段的含义：

• Table：当前索引属于那张表。
• Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
• Key_name：当前索引的名字。
• Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
• Column_name：当前索引是位于哪个字段上建立的。
• Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
• Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
• Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
• Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
• Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
• Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
• Comment：创建索引时，是否对索引有备注信息。

🎉 这条命令在后续排除问题、性能调优时，会有不小的作用

比如可以通过分析其中的Cardinality字段值，如果该值少于数据的实际行数，那目前索引有可能失效

2.2：删除索引

在MySQL中并未提供修改索引的命令，也就说当你建错了索引，只能先删再重新建立一次 drop index...on...

drop index indexName on tableName;

2.3：指定索引

当建立了一条索引后，可以强制性的为SELECT语句指定索引force index(xxx)，如下：

select * from tableName force index(indexName) WHERE .....;

FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询

这个关键字的用法是：一条查询语句在有多个索引可以检索数据时，显式指定一个索引，减少优化器选择索引的耗时。

⚠️ 你对于你整个业务系统十分熟悉，那可以这样干。但如果不熟悉的话，还是交给优化器来自行选择，否则会适得其反！如果当前的查询SQL压根不会走指定的索引字段，哪这种方式是行不通的

3：索引的本质

数据库是基于磁盘工作的，所有的数据都会放到磁盘上存储，而索引也是数据的一种，因此与表数据相同，最终创建出的索引也会在磁盘生成本地文件。

不过索引文件在磁盘中究竟以何种方式存储，这是由索引的数据结构来决定的。

同时，由于索引机制最终是由存储引擎实现，因此不同存储引擎下的索引文件，其保存在本地的格式也并不相同。

越早建立索引越好

建立索引的工作在表数据越少时越好，如果你想要给一张百万、千万条数据级别的表新创建一个索引，那创建的耗时也不短

因为索引本质上和表是一样的，都是磁盘中的文件，那也就代表着创建一个索引，并不像单纯的给一张表加个约束那么简单

而是会基于原有的表数据，重新在磁盘中创建新的本地索引文件。

假设表中有一千万条数据，那创建索引时，就需要将索引字段上的1000W个值全部拷贝到本地索引文件中，同时做好排序并与表数据产生映射关系。

三：索引的分类

1：数据结构层次的分类

前面提到，索引建立后也会在磁盘生成索引文件，那每个具体的索引节点该如何在本地文件中存放呢？

这点是由索引的数据结构来决定的。

比如索引的底层结构是数组，那所有的索引节点都会以Node1→Node2→Node3→Node4…这样的形式，存储在磁盘同一块物理空间中

不过MySQL的索引不支持数组结构，或者说数组结构不适合作为索引结构，MySQL索引支持的数据结构如下：

• B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。（重点）
• Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。
• …

B+树和哈希索引是最常见的索引结构，几乎大部分存储引擎都实现了

在MySQL中创建索引时，其默认的数据结构就为B+Tree，如何更换索引的数据结构呢？如下：

create index indexName on tableName (columnName(length) [ASC|DESC]) using hash;

就是在创建索引时，通过USING关键字显示指定索引的数据结构（必须要为当前引擎支持的结构）。

同时索引会被分为有序索引和无序索引，这是指索引文件中存储索引节点时，会不会按照字段值去排序。

那一个索引到底是有序还是无序，就是依据数据结构决定的，例如B+Tree、R-Tree等树结构都是有序，而hash结构则是无序的。

2：字段数量层次的分类

而从表字段的层次来看，索引又可以分为单列索引和多列索引

这两个称呼也比较好理解，单列索引是指索引是基于一个字段建立的，多列索引则是指由多个字段组合建立的索引。

单列索引也会分为很多类型，比如：

• 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。
• 主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。
• 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。
• …还有很多很多，只要是基于单个字段建立的索引都可以被称为单列索引。

多列索引的概念前面解释过了，不过它也有很多种叫法，例如：

• 组合索引、联合索引、复合索引、多值索引…

但不管名称咋变，描述的含义都是相同的，即由多个字段组合建立的索引。

使用多列索引时要注意：当建立多列索引后，一条SELECT语句，只有当查询条件中了包含了多列索引的第一个字段时，才能使用多列索引[最左前缀匹配]

假设有多列索引(id, name, age)

-- 无法使用多列索引的SQL语句
select * from zz_user where name = "竹子" and age = "18";

-- 能命中多列索引的SQL语句
select * from zz_user where name = "竹子" and id = 6;

无论是单列还是多列，都可以存在一个前缀索引的概念，还记得创建索引时指定的length字段吗？

• length：如果字段存储的值过长，选用值的前多少个字符创建索引。

使用一个字段值中的前N个字符创建出的索引，就可以被称为前缀索引

前缀索引能够在很大程度上，节省索引文件的存储空间，也能很大程度上提升索引的性能

3：功能逻辑层次的分类

以功能逻辑划分索引类型，这也是最常见的划分方式，从这个维度来看主要可划分为五种：

• 普通索引、唯一索引、主键索引、全文索引、空间索引

🎉 全文索引和空间索引都是5.7版本后开始支持的索引类型，不过这两种索引都只有MyISAM引擎支持

4：存储方式层次的分类

从存储方式来看，MySQL的索引主要可分为两大类：

• 聚簇索引：也被称为聚集索引、簇类索引
• 非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引

重点说一说这两类索引存储方式的区别，在说之前先回忆一下数组和链表的区别：

• 数组是物理空间上的连续，存储的所有元素都会按序存放在同一块内存区域中。
• 链表是逻辑上的连续，存储的所有元素可能不在同一块内存，元素之间以指针连接。

为啥要说这个呢？因为聚簇索引和非聚簇索引的区别也大致是相同的：

• 聚簇索引：逻辑上连续且物理空间上的连续。
• 非聚簇索引：逻辑上的连续，物理空间上不连续。

当然，这里的连续和数组不同，因为索引大部分都是使用B+Tree结构存储，所以在磁盘中数据是以树结构存放的

所以连续并不是指索引节点，而是指索引数据和表数据，也就是说聚簇索引中，索引数据和表数据在磁盘中的位置是一起的，而非聚簇索引则是分开的，索引节点和表数据之间，用物理地址的方式维护两者的联系。

不过一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引，或者称之为辅助索引、次级索引。

但也不要走进一个误区，虽然MySQL默认会使用主键上建立的索引作为聚簇索引，但也可以指定其他字段上的索引为聚簇索引

一般聚簇索引要求索引必须是非空唯一索引才行。

在innodb存储引擎中，数据在进行插入的时候必须要和某一个索引列绑定在一起：

• 如果表中有主键，那么选择主键
• 如果没有主键，选择唯一键
• 如果没有唯一键，那么系统会自动生成一个6字节的rowid来绑定存储

B+树是左⼩右⼤的顺序存储结构，节点只包含id索引列，⽽叶⼦节点包含索引列和数据，这种数据和索引在⼀起存储的索引⽅式叫做聚簇索引

⼀张表只能有⼀个聚簇索引。

假设没有定义主键，InnoDB会选择⼀个唯⼀的非空索引代替，如果没有的话则会隐式定义⼀个主键作为聚簇索引

非聚簇索引(⼆级索引)保存的是主键id值，这⼀点和myisam保存的是数据地址是不同的

四：其他索引的创建使用方式

1：唯一索引的创建和使用

唯一索引在创建时，需要通过UNIQUE关键字创建

# 方式一：我要创建唯一索引(create unique index) 在(on) xx 表上，用 xx 字段
create unique index indexName on tableName (columnName(length));

# 方式二：我要修改表(alter table)，修改的内容是添加唯一的索引(add unique index)在这个表的 xx 字段上
alter table tableName add unique index indexName(columnName);

# 方式三：
create table tableName(  
  columnName1 int(8) not null,   
  columnName2 ....,
  .....,
  unique index [indexName] (columnName(length))  
);

在已有的表基础上创建唯一索引时要注意
如果选用的字段，表中字段的值存在相同值时，这时唯一索引是无法创建的

🎉 当唯一索引创建成功后，它同时会对表具备唯一约束的作用，当再使用insert语句插入相同值时,会出现1062错误

2：主键索引的创建和使用

主键索引是特殊的唯一索引，是通过PRIMARY关键字创建

-- 方式一
alter table tableName add primary key indexName(columnName);

-- 方式二
create table tableName(  
  columnName1 int(8) not null,   
  columnName2 ....,
  .....,
  primary key [indexName] (columnName(length))  
);

⚠️ 下面三点需要注意：

创建主键索引时，必须要将索引字段先设为主键，否则会抛1068错误码。

不能使用CREATE语句创建索引，否则会提示1064语法错误。

创建索引时，关键字要换成KEY，并非INDEX，否则也会提示语法错误。

3：全文索引的创建

首先如果你想要创建全文索引，那么MySQL版本必须要在5.7及以上，同时使用时也需要手动指定

-- 方式一
alter table tableName add fulltext index indexName(columnName);

-- 方式二
create fulltext index indexName on tableName(columnName);

在创建全文索引时，有三个注意点：

• 5.6版本的MySQL中，存储引擎必须为MyISAM才能创建。
• 创建全文索引的字段，其类型必须要为CHAR、VARCHAR、TEXT等文本类型。
• 如果想要创建出的全文索引支持中文，需要在最后指定解析器：with parser ngram。

4：组合索引的创建

联合索引呢，实际上并不是一种逻辑索引分类，它是索引的一种特殊结构

联合索引的意思是可以使用多个字段建立索引

create index indexName on table (col1(length), col2(length)....);

alter table tableName add index indexName(col1(length), col2(length)....)