MYSQL存储结构

前言

需要提前了解B树、B+树特性。

目录

1. MYSQL的数据结构
2. 主索引、辅助索引
3. 通过索引查询数据时出现的关键词

MYSQL的数据结构

MYSQL数据选用B+树结构存储数据

示例表

create table users(

id int,

name varchar(50),

age tinyint(4)

);

数据存储结构示例

MYSQL存储数据的最小单位：页（page）

#查询innodb引擎的每页字节数

SHOW GLOBAL STATUS like 'Innodb_page_size';

MYSQL一条数据存储结构（叶子节点与分支节点结构相同）

索引的出现

索引(Index)是帮助 MySQL 高效获取数据的数据结构。

MYSQL 支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同。

常见索引

主键索引：数据唯一、非空，表中只能存在一个

唯一索引：数据唯一

普通索引：最基本的索引，没有任何限制

联合索引：多列组合创建一个索引

全文索引：最基本的索引，没有任何限制（不建议创建，通过ES辅助分词查询）

示例

#id主键，age普通索引
create table users(
  id int,
  name varchar(50),
  age tinyint(4),
  primary key(id),
  key idx_age(age)
) engine innodb;

左侧为数据存储结构（主键+完整数据），右侧为age的索引存储结构（仅包含索引+主键）

主索引、辅助索引

主索引

主索引也称为一级索引

一张表选取主索引顺序：主键索引 > 表中第一个唯一索引 + 非空 > 数据表自动生成

辅助索引

辅助索引也称为二级索引

未被选中一级索引的其他索引都称为辅助索引

联合索引

联合索引比较特殊，由多个字段联合起来创建的一种索引结构，并且结构会按照创建的字段顺序进行存储

示例

create table test(
  id int,
  a int,
  b int,
  key idx_a_b(a,b)
) engine innodb;

通过索引查询数据时出现的关键词

回表

查询字段不存在索引中或通过二级索引查询数据的过程

索引覆盖

查询语句中字段为索引中的字段，提升查询效率

如果查询字段超出索引范围，会进行回表操作。

#查询id字段，id > 10 取30条
select id from users where id > 10 limit 30;

#查询 phone,name字段，phone = 15800003333
select phone,name from users where phone = '15800003333' 
and age > 10 and name like '张三%';

最左原则

通过联合索引查询数据时，必须包含联合索引第一列

#phone = 15800003333 & 年龄30
select * from users where phone = '15800003333' and age  = 30;

#不符合最左原则
select phone,name from users where name = '张三' and age = 10;

小疑问

为何选用B+树存储？

需要结合B+树特性，及MYSQL数据存储、查询几方面结合考虑

MYSQL最多存储数据量为多少？

最大存储量根据磁盘限制有关，不过不是问题关键点，当数据量过多引发的磁盘IO次数影响整体性能，如何控制磁盘IO次数则为实际目的，减少磁盘IO可以通过减少B+树层数进行控制，一般层数控制在2层即可存储千万级数据。

粗略计算公式（没有计算页中其他占用）：

假设：主键索引为bigint（8字节），索引占用为（6字节），一页限制16KB，一条记录占用量1KB

一页存储主键数量 16*1024/（8+6） ≈ 1170条

三层 = 16*1024/(8+6) * 16*1024/(8+6) * 16/1 ≈ 21907748.571428571428571

通过公式分析：主键大小、数据量大小会实际影响页存储数据量

实际关注：树的层级（影响IO次数）、索引大小(影响叶子节点存储数据量)、数据大小（影响叶子节点存储数据量）

MYSQL索引为何选择建议连续性？

影响数据的分裂，不连续会造成重新整理数据页

MYSQL数据删除为何数据表空间不变？

和索引连续性类似