MySQL的存储引擎
这里主要了解一下InnoDB和MyISAM,这两种引擎的索引都是使用B+树的结构来存储的。
InnoDB
InnoDB中有两种索引:主键索引(聚簇索引、聚集索引)、辅助索引(非聚簇索引、非聚集索引)
主键索引
每张表只有一个主键索引,B+树结构,叶子结点存储了主键的值以及完整的行记录,其他结点只存储主键的值。
如果定义了主键,InnoDB会自动使用主键来创建聚簇索引。如果没有定义主键,InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引,InnoDB会隐式的定义一个主键来作为聚簇索引。
注:聚簇索引的叶子结点称为数据页
辅助索引
每张表可以有多个辅助索引,B+树结构,叶子结点存储了索引字段的值以及主键的值,其他结点只存储索引字段的值。
MyISAM
MyISAM使用的是非聚簇索引,两颗B+树除了存储内容不同,结点结构完全一致。表数据存储在独立的地方,这两颗B+树的叶子结点都使用一个地址指向真正的表数据。
对于表结构来说,这两个索引是没有任何差别的,两颗B+树也是完全独立的,通过辅助键去检索也无需访问主键的索引树。
注:MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址
检索过程图例
我们假设表存有4条数据,其中id为主键索引,name为辅助索引。如下图(图源自网络),可以看出聚簇索引与非聚簇索引的区别。
InnoDB数据检索过程
1、如果需要查询id=14的数据,只需要在主键索引中检索就可以了。
2、如果需要搜索name='Ellison’的数据,则需要分两步:
先在辅助索引中检索到name='Ellison’的数据,获取id为14
再到主键索引中检索id为14的记录
注:辅助索引这个查询过程在mysql中叫做回表。
MyISAM数据检索过程
1、在索引中找到对应的关键字,获取关键字对应的记录的地址;
2、通过记录的地址查找到对应的数据记录。
MyISAM和InnoDB的区别
InnoDB | MyISAM | |
---|---|---|
B+树索引 | 聚簇索引 | 非聚簇索引 |
事务支持 | 支持事务(这是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一) | 不支持事务 |
外键支持 | 支持外键(一个包含外键的InnoDB表转MyISAM会失败) | 不支持外键 |
表锁差异 | 最小的锁粒度是行锁,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁 | 最小的锁粒度是表锁,一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,并发访问受限。(这也是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一) |
表主键 | 如果没有定义主键或者非空唯一索引,会自动生成一个隐式主键(用户不可见) | 允许没有任何索引和主键的表存在,索引都是保存行的地址 |
表的总行数 | 没有保存表的总行数,如果执行**select count(*) from table;**会遍历整张表 | 保存有表的总行数,如果执行select count(*) from table;会直接取出该值(如果加了WHERE条件,MyISAM和InnoDB的处理方式都一样) |
CRUD操作 | 如果你的数据需要执行大量的INSERT或UPDATE,出于性能方面考虑,应该使用InnoDB | 如果你的数据要执行大量的SELECT操作,MyISAM是不错的选择 |
全文索引 | 不支持FULLTEXT类型的全文索引,但是可以使用sphinx插件支持全文索引 | 支持FULLTEXT类型的全文索引 |
存储结构 | 所有的表都保存于同一个数据文件中(共享表空间)或者独立的表空间文件 | 每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件(.frm)、数据文件(.MYD)、索引文件(.MYI)。 |
注:支持事务和行级锁,是InnoDB的最大特色
页结构
MySQL中页是InnoDB存储数据的基本单位,也是MySQL中磁盘与内存交互的基本单位(管理数据的最小单位),默认是16KB。MySQL中采用B+树来存储数据,页相当于B+树中的一个结点。
页结构分为Page头部、Page主体、Page尾部,如下图(图源自网络)
Page头部详细信息如下图(图源自网络)
Page头部中保存了两个指针,分别指向前一个Page和后一个Page,形成了一个双向链表的结构,如下图(图源自网络)
Page主体部分如下图(图源自网络)
行数据和索引都位于Page的User Records部分,占据了Page的大部分空间。字符串形式的"Infimum"代表开头,"Supremum"代表结尾,与User Records组成了一个单向链表的结构。(最初的数据是按照插入的先后顺序排列的,但是随着数据的插入和删除,数据物理顺序会变得混乱,但数据会通过链表的方式保持着逻辑上的先后顺序)
把User Records的组织形式与若干个Page组合起来,如下图(图源自网络)
将上述内容结合起来看B+树的结构,如下图(图源自网络)
页目录(Page Directory)
页目录在靠近页尾部的地方,记录着每组记录中最后一条记录的地址偏移量,每个偏移量被放在目录槽(slot)中
分组
最小记录(Infimum)所在的分组只能有1条记录,最大记录(Supremum)所在的分组拥有的记录条数取值范围只能在[1,8],其余分组的记录条数取值范围只能在[4,8],并且每个分组中只有最大的那条记录的n_owned才会有值,其余的记录n_owned值为0,如下图(图源自网络)
- n_owned 表示当前分组中一共有几条记录
数据检索过程
查询数据时,先通过B+树定位到数据所在的页,然后将页整体加载到内存中,再通过二分法在Page Directory中检索数据,缩小范围。比如在上图中检索记录 7:
通过二分法查找到 7 位于 slot3 和 slot2 之间;然后从 slot3 向右一个一个找,就可以找到记录 7。
走到 slot2 指向的记录 8 结束,如果还没有找到记录 7,则说明不存在记录 7。
页结构特点总结
1、B+树的叶子页之间是用双向链表连接的,能够实现范围查询;
2、页内部的记录之间是用单向链表连接的,便于访问下一条数据;
3、InnoDB为了加快页内部记录的查询,对页内记录加了个有序的稀疏索引(页目录)
- 注:稠密索引:对每一条记录都有索引项与之对应(占用空间大,检索快)
稀疏索引:索引项只对应部分记录(占用空间小,检索相对于稠密索引慢)
索引分类
- 分为聚簇索引和非聚簇索引
聚簇索引(主键索引)
每个表一定会有一个聚簇索引,整张表的数据存储在聚簇索引中,MySQL中采用B+树结构存储,其中非叶子结点只存储主键的值,叶子结点存储主键的值以及对应的行记录数据。
非聚簇索引(辅助索引)
每张表可以有多个非聚簇索引。也是B+树结构存储,其中非叶子结点只存储索引字段的值,叶子结点存储索引字段的值以及对应行记录数据的主键值。在MySQL中,非聚簇索引又分为:
-
单列索引:只包含一个列的索引
-
复合索引(联合索引):包含多个列的索引
-
唯一索引:索引列的值必须唯一,允许有一个空值
索引管理
创建索引
# 方式一
create [unique] index 索引名称 on 表名(列名[(length)],列名[(length)]...);
# 方式二
alter table 表名 add [unique] index 索引名称 (列名[(length)],列名[(length)]...);
# 示例
create index idx_name on user_info(name(20));
alter table user_info add index idx_name_age (name(20),age);
如果字段是char、varchar类型,length可以小于实际字段的长度;如果是blob,text等长文本类型的,必须指定length。(实际开发中根据实际文本区分度决定索引长度,通过减小索引长度来减小索引文件的大小)
[unique]:可以省略,加上了unique代表创建唯一索引。
列名处如果只写一个字段,就是单列索引,写多个字段,就是复合索引,多个字段用逗号隔开。
删除索引
drop index 索引名称 on 表名;
# 示例
drop index idx_name on user_info;
查看索引
# 查看表中的suo'y
show index from 表名;
# 示例
show index from user_info;
show index语法的列解释
列名 | 列解释 |
---|---|
Table | 表名 |
Non_unique | 唯一索引。不能包括重复值则为0,否则为1 |
Key_name | 索引名称,名称相同表示的是同一个索引,即联合索引 |
Seq_in_index | 索引中的列序列号,从1开始(如图中的3,4条记录),能根据这个值推断出联合索引中索引的前后顺序 |
Column_name | 索引的列名 |
Collation | 列以何种方式存储在索引中,在MySQL中,有值 ‘A’ (升序)或 NULL (无分类) |
Cardinality | 索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 可以更新。值越大,当进行联合时,MySQL使用该索引的机会就越大(一般根据该值来判断该字段是否适合建立索引) |
Sub_part | 前置索引。如果列只是部分编入索引,该值就是编入索引的字符的数目。如果整列编入索引,则该值为NULL |
Packed | 指示关键字如何被压缩。如果没被压缩则为NULL |
Null | 如果列允许为空,则为YES |
Index_type | 索引方法。主要有(BTREE、HASH) |
Comment Index_comment | 备注 |