从千万级数据查询，来聊一聊索引结构和数据库原理( 二 ) _数据库原理

文章插图

与B-Tree相比， B+Tree做了以下一些改进：1、非叶子节点，只存储键值信息，这样极大增加了存放索引的数据量。2、所有叶子节点之间都有一个链指针。对于区间查询时，不需要再从根节点开始，可直接定位到数据。3、数据记录都存放在叶子节点中。根据二叉树的特点，这个是顺序访问指针，提升了区间访问的性能。通过这样的设计，一张千万级的表最多只需要3次磁盘交互就可以找出数据。
二、Mysql部分原理说明这一部分我们选举几个日常面试过程中或者使用过程中比较常见的问题通过问答的形式来进行讲解。
2.1、数据库引擎MyISAM和InnoDB有什么区别

MyISAM：
在Mysql8之前，默认引擎是MyISAM ，其目标是快速读取。
特点：
1、读取非常快，如果频繁插入和更新的话，因为涉及到数据全表锁，效率并不高
2、保存了数据库行数，执行count时，不需要扫描全表；
3、不支持数据库事务；
4、不支持行级锁和外键；
5、不支持故障恢复。
6、支持全文检索FullText ，压缩索引。
建议使用场景：
1、做很多count计算的，（如果count计算后面有where还是会全表扫描）
2、插入和更新较少，查询比较频繁的
InnoDB：
在Mysql8里，默认存储引擎改成了InnoDB 。
特点
1、支持事务处理、ACID事务特性
2、实现了SQL标准的四种隔离级别
3、支持行级锁和外键约束
4、可以利用事务日志进行数据恢复
5、不支持FullText类型的索引，没有保存数据库行数，计算count(*)需要全局扫描
6、支持自动增加列属性auto_increment
7、最后也是非常重要的一点：InnerDB是为了处理大量数据时的最大性能设计，其CPU效率可能是其他基于磁盘的关系型数据库所不能匹敌的。
建议使用场景
1、可靠性高或者必须要求事务处理
2、表更新和查询相当的频繁，并且表锁定的机会比较大的情况下，指定InnerDB存储引擎。

2.2 表和数据等在Mysql中是如何存储的我们新建一个数据库mds_demo ，里面有两张表：order_info,user

文章插图

我们找到mysql存放数据的data目录，存在一个mds_demo的文件夹，同时我们也找到了order_info和user的文件。

文章插图

为什么两张表产生了不同的文件呢？原因很简单，因为创建这两张表时使用了不同的引擎

文章插图

MyISAM引擎在创建表的时候，会创建三个文件
.MYD文件：存放表里的数据
.MYI文件：存放索引数据
.sdi文件： Serialized Dictionary Information的缩写。在Mysql5里没有sdi文件，但会有一个FRM文件，用户存放表结构信息。在MySQL8.0中重新设计了数据字典，改为sdi 。
MyISAM的索引和数据是分开的，并且索引是有压缩的，所以存储文件就会小很多， MyISAM应对错误码导致的数据恢复的速度很快。
InnerDB引擎在创建表的时候，只有1个文件.ibd ，即存放了索引又存放了文件，参见B+Tree 。所以它也被称之为聚集索引，即叶子节点包含完整的索引和数据，对应的MyISAM为非聚集索引。
补充说明一下：存储引擎是针对表的，而不是针对数据库，同一个库的不同的表可以使用不同的引擎。

2.3 为什么InnoDB必须要有主键，并且推荐使用整型的自增主键？通过上面的讲解这个问题其实已经很清楚了，为了满足MySQL的索引数据结构B+树的特性，必须要有索引作为主键，可以有效提高查询效率。有的童鞋可能会说我创建表的时候可以没有主键啊，这个其实和Oracle的rownum一样，如果不指定主键， InnoDB会从插入的数据中找出不重复的一列作为主键索引，如果没找到不重复的一列， InnoDB会在后台增加一列rowId做为主键索引。所以不如我们自己创建一个主键。