id
username
gender
age
phone_number
+-------+----------+--------+------+--------------+
小花
女
18
小李
女
21
小白
男
38
小何
男
35
小王
男
20
小赵
女
29
小青
女
25
小米
男
23
小徐
女
22
+-------+----------+--------+------+--------------+9rowsinset(0.00sec)#SQL2mysqlselect*fromt1orderbyid;+-------+----------+--------+------+--------------+
id
username
gender
age
phone_number
+-------+----------+--------+------+--------------+
小花
女
18
小王
男
20
小赵
女
29
小青
女
25
小李
女
21
小白
男
38
小米
男
23
小徐
女
22
小何
男
35
+-------+----------+--------+------+--------------+9rowsinset(0.00sec)接下来看看INNODB的主键索引和二级索引的组成方式。INNODB表:INNODB表本身是索引组织表,也就是说索引就是数据。下图表T1的数据行以聚簇索引的方式展示,非叶子节点保存了主键的值,叶子节点保存了主键的值以及对应的数据行,并且每个页有分别指向前后两页的指针。INNODB表不同于MYISAM,INNODB表有自己的数据页管理,默认16KB。MYISAM表数据的管理依赖文件系统,比如文件系统一般默认4KB,MYISAM地块大小也是4KB,MYISAM表的没有自己的一套崩溃恢复机制,全部依赖于文件系统。INNODB表这样设计的优点有两个:1.数据按照主键顺序存储。主键的顺序也就是记录行的物理顺序,相比指向数据行指针的存放方式,避免了再次排序。我们知道,排序消耗最大。现在表t1的直接拿出来就是按照主键ID排序。mysqlmysqlselect*fromt1;+-------+----------+--------+------+--------------+
id
username
gender
age
phone_number
+-------+----------+--------+------+--------------+
小花
女
18
小王
男
20
小赵
女
29
小青
女
25
小李
女
21
小白
男
38
小米
男
23
小徐
女
22
小何
男
35
+-------+----------+--------+------+--------------+9rowsinset(0.00sec)2.两个叶子节点分别含有指向前后两个节点的指针,这样在插入新行或者进行页分裂时,只需要移动对应的指针即可。再来看下INNODB表的二级索引,如下图所示:INNODB二级索引的非叶子节点保存索引的字段值,上图索引为表t1的字段age。叶子节点含有索引字段值和对应的主键值。这样做的优点是当出现数据行移动或者数据页分裂时,避免二级索引不必要的维护工作。当数据需要更新的时候,二级索引不需要重建,只需要修改聚簇索引即可。但是也有缺点:1.二级索引由于同时保存了主键值,体积会变大。特别是主键设计不合理的时候,比如用UUID做主键。我详细介绍如何设计合理的主键。2.对二级索引的检索需要检索两次索引树。第一次通过检索二级索引叶子节点,找到过滤行对应的主键值;第二次通过这个主键的值去聚簇索引中查找对应的行。举个例子:如下SQL语句,检索年龄为23的行记录:mysqlselect*fromt1whereage=23;会拆分成以下两个SQL语句:先通过索引字段age找到对应的主键值:.mysqlselectidfromt1whereage=23;再去聚簇索引上根据主键ID=检索到需要的数据行,如果表第一次读取,就需要回表。mysqlselect*fromt1whereid=;不过MySQL对这块做了很好的优化,提前做了数据预热(数据预热,这里就不讲了,可以参考MySQL手册,手册上介绍得很详细)。本篇内容介绍到此,简单回顾下本篇内容。本篇主要介绍MySQL常见的两种引擎MYISAM和INNODB的索引组织方式以及各自的优缺点。有问题欢迎批评指正,我来介绍MySQL如何很好的对主键进行设计。