MySQL:索引知识

目录

MySQL与硬盘IO

局部性原理

page

B+树结构

聚簇

聚簇索引

非聚簇索引

操作索引

主键索引

唯一索引

辅助索引

删除索引

辅助索引

复合索引

---

在 MySQL 数据库优化中，数据存储结构以及索引的作用至关重要。本篇博客将概述 MySQL 数据的存储与检索原理，探讨数据库性能优化的关键知识。

MySQL与硬盘IO

数据库作为持久化保存数据的应用，数据自然要存放在硬盘中，而面对高频的查询，就不可避免的要进行频繁的IO，索引的出现就是为了减少IO次数，更加高效的查询。

对于一般的磁盘来说，一个基本IO单位是扇区，常规的是512 byte。

对于操作系统来说，不会以扇区为单位读取，而是以块为单位读取，一般来说基本单位是4 kb。

对于MySQL来说，IO单位小意味着单次读取到的数据少，进而导致IO次数多，查询效率低。以InnoDB存储引擎为例，MySQL的IO基本单位为16 kb。

---

局部性原理

• 时间局部性：如果一个数据被访问，在不久后它很可能再次被访问
• 空间局部性：如果一个数据被访问，其周围的数据不久后很有可能再次被访问

对操作系统来说，其加载数据时会把该数据周围一部分数据都加载进去，因为周围的数据访问的概率很高，后续访问时不必重新去硬盘读取数据，减少了IO次数。

MySQL也是同理的，MySQL访问数据时，也会把该数据附近的数据一起读取出来，很有可能下一个查询也在这一次读取到的数据中，就减少了IO次数。

这就意味着MySQL在存储数据时，要把关系接近的数据存到一起去，MySQL把这样一个关系接近的，总共16 kb的数据放在一个page中，也就是说MySQL以page为单位管理数据。

---

page

基于局部性原理，将关系接近的数据放到一个page中统一管理：MySQL中自然不止一个page，而是有多个page，此时就要用数据结构进行管理，多个page此时就被连接成了链表的形式：当要查询数据时，就一张张page读取出来，然后遍历page内部的数据

其实在page内部不仅仅有数据，还有页目录。页目录是页内部的一个组件，用于加速对页中记录的查找。页目录存储在页尾的数组，包含指向页内各个记录分组的指针。

页内的记录按照顺序排列，页目录会将这些记录分成若干组，每组通常包含4-8条记录，页目录中的每个条目指向这些组的最后一条记录

当需要在页内查找记录时，首先通过二分查找在页目录中定位到可能包含目标记录的组。然后在这个小组内进行线性查找，快速定位到具体记录

加入页目录后，当一个page被读取上来后，只需要查找一遍页目录就可以知道有没有要查找的数据了，此时查找的效率就变高了

---

B+树结构

以上基于链表的page结构，如果要查询的数据所在的位置很靠后，那么依然要从前往后读取大量page，导致读取了很多无效的page，此时可以给page也加上目录：如图，第一层page内部的条码指向下层page的第一个条目，此时MySQL读取数据时，先读取上层page，根据每个page的第一个数据，快速锁定要读取的page。上层节点存储下层节点的信息，只有最后一层叶子节点存储数据，叶子节点通过链表互相连接（只有叶子节点通过链表连接）--B+树。

之所以叶子节点要通过链表连接起来，是因为当一个page内部的数据查询完毕，有可能接下来查询的内容就在下一张page中，此时就不再通过根节点往下查询，而是直接通过链表next指针访问下一张page。

这样一个基于B+树的结构，就是索引

在InnoDB和MyISAM引擎中，都使用B+Tree作为索引结构，也有部分其它存储引擎会使用hash结构作为索引，比如NDB，但是B+Tree依然是主流。因为hash结构的索引，每次查询数据都要重新计算哈希函数，范围查找能力很差。

另外的，也有人想用红黑树这样的结构来作为索引，其实未尝不可，但是红黑树是二叉树，高而窄。树越高，要读取的page就越多，IO次数越多，效率就低了，所以还是B+这种矮胖的树适合索引。

---

聚簇

聚簇索引

先前讲解的索引，都是基于InnoDB的，在InnoDB中，索引底层的叶子节点存储数据，也就是说索引和数据是一起存储的。

使用InnoDB引擎存储的表，结构如下：

• frm：存储表的结构
• ibd：存储索引和数据

将索引和数据存储在一起的索引，叫做聚簇索引。

---

非聚簇索引

在MyISAM中，索引底层的叶子节点不存储数据，而是存储指向数据的指针，也就是说索引和数据是分离存储的。

使用MyISAM引擎存储的表，结构如下：

• frm：存储表的结构
• MYD：存储数据
• MYI：存储索引

将索引和数据分离存储的索引，叫做非聚簇索引。

---

操作索引

主键索引

• 创建主键索引

建表时直接在列后面指定primary key：

create table test1(
id int primary key,
name varchar(20)
);

建表后通过alter增加主键：

alter table test3 add primary key(id);

一个表中只能有一个主键或复合主键，由于主键不可重复，所以主键索引的效率更高。

查询索引：

通过show keys from 表名或者show index from 表名即可查询索引。

1. Table：索引所属的表名。
2. Non_unique：如果索引不能包括重复值则为0,如果可以则为1
3. Key_name：索引的名称，如果是主键，则显示为 PRIMARY
4. Seq_in_index：在那一列创建索引
5. Column_name：索引列的名称
6. Index_type：使用的索引方法(BTREE, FULLTEXT, HASH, RTREE)

也可以通过desc查询：

此处Key列表示索引，PRI表示主键索引。

---

唯一索引

所谓唯一索引，其实就是列值必须唯一，也就是unique属性。

• 创建唯一索引：

建表时直接在列后面指定unique：

create table test1(
id int primary key,
name varchar(20) unique
);

建表时在末尾通过unique()指定几列为唯一索引：

create table test2(
id int primary key,
name varchar(20),
unique(name)
);

建表后通过alter增加唯一索引：

alter table test3 add unique(name);

通过show index from查询：

此时输出结果有两个，第一个是先前创建的主键索引，第二个是创建的唯一索引。对于主键索引，索引名默认为PRIMARY，对于其它索引，索引名默认为列名，如果有多个列作为索引，那么为第一个列的列名。当列被加上唯一索引，Key值变为UNI，表示唯一索引。

---

辅助索引

辅助索引也叫普通索引，不仅仅唯一的primary key和unique可以创建索引，一般的列也可以创建索引。

• 创建辅助索引：

建表时在末尾通过index()指定几列为索引：

create table test3(
id int primary key,
name varchar(20),
index(name)
);

建表后通过alter增加索引：

alter table test2 add index(name);

通过create index创建索引：

create index 索引名 on 表名;

第二个索引就是创建的辅助索引，其Non_unique = 1表示列值可以重复。

当列被加上了索引，Key值会变成MUL，表示辅助索引。

---

删除索引

• 删除主键索引

alter table 表名 drop primary key;

如果删掉主键索引，那么对于列的主键属性也会消失。

• 删除一般索引

alter table 表名 drop index 索引名;

drop index 索引名 on 表名;

---

辅助索引

通过索引的操作，可以发现不仅仅主键或者唯一键可以创建索引，一般的列也可以创建辅助索引。

• 非聚簇索引：

对于MyISAM这非聚簇索引而言，其本身数据和索引就是分离的，通过列值构建起辅助索引后，叶子节点存储指向数据的指针。此时辅助索引没有导致额外的数据开销，索引指向原先的数据。

• 聚簇索引：

对于InnoDB这种聚簇索引而言，其索引的叶子节点存储的是数据的值，那么如果创建多个索引，岂不是每一个索引都要保存一份数据？

其实不是，对于聚簇索引而言，其辅助索引存储的是主键的值：左侧的主键索引，叶子节点中存储的data代表整行数据。而右侧的辅助索引，叶子节点存储的pri只代表一个主键值。

当通过辅助索引查询时，会通过key找到对应主键，然后再通过主键去主键索引查找正行数据，这个过程叫做回表查询。

---

复合索引

所谓复合索引，其实就是用多个列一起作为key来创建索引，比如：

create table test4(
id int primary key,
name varchar(20),
email varchar(30),
index(name,email)
);

查询索引时发现，emali和name这两列对应的索引名称都叫做name，也就是创建索引index(name, email)时第一个列的名称。

复合索引的key是要存储在索引中的，也就是说(name,eamil)会作为一个整体存在索引内部充当key值。 

如果要通过name查询email，由于email就在key中，此时不会进一步查询数据库，而是直接返回email，这叫做索引覆盖。

如果经常用一个值查询另一个值，此时就可以用这两个值创建一个复合索引，利用索引覆盖机制，提高查询效率。