本文共 2455 字,大约阅读时间需要 8 分钟。
常见的数据库存储引擎有MyISAM和InnoDB(这里指的是数据表级别的存储引擎)。
由于一个库中有不同的表,而不同的表可能对应着不同的存储引擎。这里纠正一个错误的概念:可能有很多人认为,如果一个数据库使用的InnoDB存储引擎,那么这个数据库中所有的表都是InnoDB的 ,其实并不是这样的。我们在设计表的时候有个选项,选项里面是可以选择存储引擎的,里面有很多不同种类的存储引擎,也就是说一个库里面不同的表可以使用不同的存储引擎。
我们都知道,数据库的表数据以及索引结构都是存在磁盘上的。
默认是存储在在MySQL的根目录下的data文件夹下的 但是我们可能会有一个疑惑,那就是:我们自己的电脑安装完 MySQL 之后,也创建了很多的数据库啊、表啊,也存储了很多的数据,但是这些内容在MySQL的跟目录下面都找不到,那么存储到哪里了呢?有时翻遍了整个电脑,却找不到 MySQL 的数据文件到底在哪里,这就有些坑啦! 在这里,分享一个非常简单的能够立即定位到 MySQL 数据文件的存储位置方法,即在 MySQL 客户端键入如下命令:show global variables like "%datadir%";这里需要注意:在默认情况下,ProgramData文件夹是隐藏的。至于如何显示隐藏的文件夹,相信大家都知道,这里就不过多介绍了。
我们可以看到data目录下和我们实际传创建的库是一一对应的。
进入到具体的数据库目录下,可看到数据表也是一一对应的MyISAM索引文件和数据文件是分离的(非聚集)
可以看到,在data文件里,这个表对应的有三个文件,其中frm文件存储的是表的结构;MYD文件存储的是数据行;MYI文件存储的是表的索引字段索引文件底层存储如下图:(各个索引的存储结构相差不大)
比如现在查询SQL:select * from test_myisam where id = 49。 MySQL会先判断是否走索引,若是走,则会从对应的MYI文件中快速定位到49所在节点元素,把整个节点先加载到内存中,在内存中找到49所在具体元素对应的指针(从上图可以看到叶子节点的data存储的就是对应行数据所在位置的磁盘文件指针),然后根据文件指针到MYD文件中快速定位到对应的位置,然后就可以将数据直接加载到内存中去了。InnoDB索引实现(聚集)
tip小提示:将innodb_file_per_table关闭之后,建立innoDB表时只生成.frm文件,数据和索引都保存在共享表空间ibdata1中(InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1)。可以使用命令:show variables like 'innodb_file_per_table'
查看状态 这里,我们可以看到在data文件里,这个表对应的有两个文件,其中frm文件存储的是表的结构;idb文件就相当于把myiasm索引表的MYD和MYI文件合并起来,也就是存储的索引+数据
InnoDB不同的索引存储结构不同。 |
引文件底层存储如下图:(这里针对主键索引)
InnoDB索引与MyISAM索引存储的不同主要在叶子节点的data元素。 MyISAIM存储的是索引所在行的磁盘文件指针,而InnoDB存储的是索引所在行的其他所有字段数据InnoDB索引实现(聚集)
表数据文件本身就是按B+Tree组织的一个索引结构文件 聚集索引-叶节点包含了完整的数据记录tip常见面试问题:可前往进行查看
非主键索引文件底层存储如下图:
非主键元素和主键索引不同点在于叶子节点的data元素,非主键索引的叶子结点存储的不是当前行数据的所有其他列数据,而是存储的索引所在行的主键。介绍到这里,会产生新的疑问,根据非主键索引查询需要查询多次,因为要先根据非主键索引查找到对应的主键元素,然后再根据主键索引去主键索引文件中找到对应的数据,直白的说就是需遍历两棵树(非主键索引树+主键索引树),感觉性能相对于主键索引来说要低一点。那么InnoDB为什么这么设计呢,为什么非主键索引结构叶子节点存储的是主键值?
我们可以想一下,一张表既有主键索引,又有非主键索引。由于在往表里插入或修改行数据之前,需要先要把索引维护好 ,才能将数据真正插入或更新数据表。 如果不论什么索引的叶子结点中都存储当前行的所有数据,那么就意味着insert一行的话,可能会出现分布式事务的问题,要确保主键索引和非主键索引全部维护完成之后,行数据才能插入成功。 直白来说就是需要保证不同索引的一致性。如果主键索引维护完成了,非主键索引没有维护好,是不能把数据写到数据表里面去的。大家都知道要保证一致,就需要用到事务,但是这样性能会大打折扣,所以MySQL综合考虑之后这样设计。 从占用的存储空间来说,这样做会减少占用。如果非主键索引叶子节点存储的对应行的所有数据,那么就意味着有多少了索引就需要存储多少份数据,索引越多,浪费了的存储空间越多 所以,总的来说,MySQL这么设计,是为了一致性和节省存储空间很多小伙伴在工作的过程中使用的大多数是联合索引。
生产环境一般表的字段比较多,一般推荐使用联合索引而不是多个单个索引,每个索引都会对应一颗B+ Tree,数量越多,占用额存储空间越多。联合索引的底层结构如下:
可以看到,其实就是把多个字段放到了索引的Key上面(原来的单个索引Key只存储一个字段)。 由于 索引的本质是帮助MySQL高效获取数据的排好序的数据结构,所以联合索引也满足这样的特性: ①排好序:从左往右递增 联合索引有多个字段,是如何比较大小从而维护从左往右递增呢? 其实联合索引会逐个字段去比较,会按字段的顺序从左到右逐个比较 (也就是键索引的顺序)②数据结构(B+ Tree)
转载地址:http://yoyai.baihongyu.com/