关于innodb:第16期索引设计MySQL-的索引结构

32次阅读

共计 3486 个字符,预计需要花费 9 分钟才能阅读完成。

上一章讲了数据库基本上都用 B+ 树来存储索引的起因:适宜磁盘存储,可能充分利用多叉均衡树的个性,磁盘预读,并且很好的反对等值,范畴,程序扫描等。这篇次要介绍 MySQL 两种罕用引擎,MyISAM 和 InnoDB 的索引组织形式,理解这些存储形式,对数据库优化很有帮忙。

MySQL 的索引依照存储形式分为两类:

汇集索引: 也称 Clustered Index。是指关系表记录的物理程序与索引的逻辑程序雷同。因为一张表只能依照一种物理程序寄存,一张表最多也只能存在一个汇集索引。与非汇集索引相比,汇集索引有着更快的检索速度。

MySQL 里只有 INNODB 表反对汇集索引,INNODB 表数据自身就是汇集索引,也就是常说 IOT,索引组织表。非叶子节点依照主键程序寄存,叶子节点寄存主键以及对应的行记录。所以对 INNODB 表进行全表程序扫描会十分快。

非汇集索引: 也叫 Secondary Index。指的是非叶子节点依照索引的键值程序寄存,叶子节点寄存索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外,其余的都是二级索引。MYISAM,memory 等引擎的表索引都是非汇集索引。 简略点说,就是索引与行数据离开存储。 一张表能够有多个二级索引。

MYISAM 表:

MYISAM 表是典型的数据与索引拆散存储,主键和二级索引没有本质区别。比方在 MYISAM 表里主键、惟一索引是一样的,没有本质区别。

假如表 t1 为 MYISAM 引擎,列为 ID,姓名,性别,年龄,手机号码。其中 ID 为主键,年龄为二级索引。记录如下:

那对应的两个 B+ 树索引如下图所示,

主键字段索引树:

上图是一个 3 阶的 B+ 树,非叶子节点依照主键的值排序存储,叶子节点同样依照主键的值排序存储,并且蕴含指向磁盘上的物理数据行指针。

年龄字段索引树:

上图年龄字段索引树同样是一个 3 阶的 B+ 树,非叶子节点依照年龄字段的值顺序存储,叶子节点保留年龄字段的值以及指向磁盘上的物理数据行指针。

从下面两张图能够看出,MYISAM 表的索引存储形式最大的毛病没有依照物理数据行顺序存储,这样无论对主键的检索还是对二级索引的检索都须要进行二次排序。

举个简略例子演示下,

以下 SQL 1 默认没有排序,乱序输入;须要依照 ID 程序输入,就得用 SQL 2,显式加 ORDER BY。

mysql
# SQL 1
mysql> select * from t1;
+-------+----------+--------+------+--------------+
| id    | username | gender | age  | phone_number |
+-------+----------+--------+------+--------------+
| 10001 | 小花     | 女     |   18 | 18501877098  |
| 10005 | 小李     | 女     |   21 | 15827654555  |
| 10006 | 小白     | 男     |   38 | 19929933000  |
| 10009 | 小何     | 男     |   35 | 19012378676  |
| 10002 | 小王     | 男     |   20 | 17760500293  |
| 10003 | 小赵     | 女     |   29 | 13581386000  |
| 10004 | 小青     | 女     |   25 | 13456712000  |
| 10007 | 小米     | 男     |   23 | 19800092354  |
| 10008 | 小徐     | 女     |   22 | 18953209331  |
+-------+----------+--------+------+--------------+
9 rows in set (0.00 sec)

# SQL 2
mysql> select * from t1 order by id;
+-------+----------+--------+------+--------------+
| id    | username | gender | age  | phone_number |
+-------+----------+--------+------+--------------+
| 10001 | 小花     | 女     |   18 | 18501877098  |
| 10002 | 小王     | 男     |   20 | 17760500293  |
| 10003 | 小赵     | 女     |   29 | 13581386000  |
| 10004 | 小青     | 女     |   25 | 13456712000  |
| 10005 | 小李     | 女     |   21 | 15827654555  |
| 10006 | 小白     | 男     |   38 | 19929933000  |
| 10007 | 小米     | 男     |   23 | 19800092354  |
| 10008 | 小徐     | 女     |   22 | 18953209331  |
| 10009 | 小何     | 男     |   35 | 19012378676  |
+-------+----------+--------+------+--------------+
9 rows in set (0.00 sec)

接下来看看 INNODB 的主键索引和二级索引的组成形式。

INNODB 表:

INNODB 表自身是索引组织表,也就是说索引就是数据。下图表 T1 的数据行以聚簇索引的形式展现,非叶子节点保留了主键的值,叶子节点保留了主键的值以及对应的数据行,并且每个页有别离指向前后两页的指针。

INNODB 表不同于 MYISAM,INNODB 表有本人的数据页治理,默认 16KB。MYISAM 表数据的治理依赖文件系统,比方文件系统个别默认 4KB,MYISAM 的块大小也是 4KB,MYISAM 表的没有本人的一套解体复原机制,全副依赖于文件系统。

INNODB 表这样设计的长处有两个:

  1. 数据依照主键顺序存储。主键的程序也就是记录行的物理程序,相比指向数据行指针的寄存形式,防止了再次排序。咱们晓得,排序耗费最大。当初表 t1 的间接拿进去就是依照主键 ID 排序。
mysql
   mysql> select * from t1;
   +-------+----------+--------+------+--------------+
   | id    | username | gender | age  | phone_number |
   +-------+----------+--------+------+--------------+
   | 10001 | 小花     | 女     |   18 | 18501877098  |
   | 10002 | 小王     | 男     |   20 | 17760500293  |
   | 10003 | 小赵     | 女     |   29 | 13581386000  |
   | 10004 | 小青     | 女     |   25 | 13456712000  |
   | 10005 | 小李     | 女     |   21 | 15827654555  |
   | 10006 | 小白     | 男     |   38 | 19929933000  |
   | 10007 | 小米     | 男     |   23 | 19800092354  |
   | 10008 | 小徐     | 女     |   22 | 18953209331  |
   | 10009 | 小何     | 男     |   35 | 19012378676  |
   +-------+----------+--------+------+--------------+
   9 rows in set (0.00 sec)
  1. 两个叶子节点别离含有指向前后两个节点的指针,这样在插入新行或者进行页决裂时,只须要挪动对应的指针即可。

再来看下 INNODB 表的二级索引,如下图所示:

INNODB 二级索引的非叶子节点保留索引的字段值,上图索引为表 t1 的字段 age。叶子节点含有索引字段值和对应的主键值。

这样做的长处是当呈现数据行挪动或者数据页决裂时,防止二级索引不必要的保护工作。当数据须要更新的时候,二级索引不须要重建,只须要批改聚簇索引即可。

然而也有毛病:

  1. 二级索引因为同时保留了主键值,体积会变大。特地是主键设计不合理的时候,比方用 UUID 做主键。下一篇我具体介绍如何设计正当的主键。
  2. 对二级索引的检索须要检索两次索引树。第一次通过检索二级索引叶子节点,找到过滤行对应的主键值;第二次通过这个主键的值去聚簇索引中查找对应的行。

举个例子:

如下 SQL 语句,检索年龄为 23 的行记录:

mysql
select * from t1 where age = 23;

会拆分成以下两个 SQL 语句:

先通过索引字段 age 找到对应的主键值:10005.

mysql
select id from t1 where age=23;

再去聚簇索引上依据主键 ID = 10005 检索到须要的数据行,如果表第一次读取,就须要回表。

mysql
select * from t1 where id = 10005;

不过 MySQL 对这块做了很好的优化,提前做了数据预热(数据预热,这里就不讲了,能够参考 MySQL 手册,手册上介绍的很具体)。

本篇内容介绍到此,简略回顾下本篇内容。本篇次要介绍 MySQL 常见的两种引擎 MYISAM 和 INNODB 的索引组织形式以及各自的优缺点。有问题欢送批评指正,下一篇我来介绍 MySQL 如何很好的对主键进行设计。


对于 MySQL 的技术内容,你们还有什么想晓得的吗?连忙留言通知小编吧!

正文完
 0