MySQL中myisam和innodb的主键索引有什么区别？

小灰灰 2022-06-08 04:29 192阅读 0赞

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

![Center][]

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

![Center 1][]

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![Center 2][]

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL [系统][Link 1]会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

![Center 3][]

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

[Center]: /images/20220608/fae3b1371273400fabf6d9bd88c5b1d5.png
[Center 1]: /images/20220608/d2d8090273044cc087c6961bc8cda0e8.png
[Center 2]: /images/20220608/0383c894b86d4cf68cd4c9a3884ac496.png
[Link 1]: http://www.2cto.com/os/
[Center 3]: /images/20220608/2cf6b59fe3354853977ea1fcdba1bcfb.png

发表评论取消回复

表情：

评论列表（有 0 条评论，192人围观）

还没有评论，来说两句吧...

相关阅读

相关 MyISAM 和 InnoDB 有什么区别？

前言：MySQL 默认引擎变化在 MySQL 5.5版本时，InnoDB 存储引擎取代了原先的引擎 MyISAM 成为新的默认存储引擎那么我们要思考为什么 MySQ

妖狐艹你老母/ 2024年03月22日 06:05/ 0 赞/ 78 阅读

相关 MySQL主键索引和唯一索引有什么区别？

> 主索引不能重复且不能为空，唯一索引不能重复，但可以为空； > 一张表只能有一个主索引，但可以有多个唯一索引； > 主索引的查询性能要高于唯一索引。 My

水深无声/ 2024年02月17日 10:09/ 0 赞/ 67 阅读

相关 MySQL常用的存储引擎 InnoDB 和 MyISAM 有什么区别？

> InnoDB 和 MyISAM 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务，它们主要区别如下： > > InnoDB 支持崩溃后安全恢复，M

阳光穿透心脏的1/2处/ 2024年02月17日 09:04/ 0 赞/ 59 阅读

相关主键与索引有什么区别？

主键（Primary Key）和索引（Index）在数据库管理系统中起着不同的作用，主要区别在于它们的功能和用途。主键（Primary Key）是数据库表中的一种特殊列，用

我就是我/ 2024年02月05日 17:53/ 0 赞/ 51 阅读

相关 Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

文章目录 1. 定义 InnoDB： MyISAM： 2. 区别： 3. 使用： 1. 定义 InnoDB： I

刺骨的言语ヽ痛彻心扉/ 2022年10月15日 06:52/ 0 赞/ 62 阅读

相关 MySQL中myisam和innodb的主键索引有什么区别？

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图： ![Center][] 这里设表一共有三列，

小灰灰/ 2022年06月08日 04:29/ 0 赞/ 193 阅读

相关 Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

区别： 1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和c

Dear 丶/ 2022年05月24日 06:14/ 0 赞/ 186 阅读

相关 5.3、MySql 学习-MySql中InnoDB和MyISAM的主键索引和二级索引

文章目录前言基于 B+Tree 的索引 InnoDB 的主键索引 MyISAM 的

我会带着你远行/ 2021年12月12日 09:59/ 0 赞/ 298 阅读

相关主键和唯一索引有什么区别

CREATE TABEL T( ID INT ); CREATE UNIQUE INDEX T\_INDEX\_ID ON T(ID); 和 CREA

谁践踏了优雅/ 2021年09月11日 05:04/ 0 赞/ 398 阅读

相关 MySQL 中 InnoDB 和 MyISAM 区别

MySQL 中 InnoDB 和 MyISAM 区别 1、事务和外键 InnoDB具有事务，支持4个事务隔离级别，回滚，崩溃修复能力和多版本并发的事务安全，包括ACID。...

小灰灰/ 2020年06月07日 14:56/ 0 赞/ 904 阅读