底层是如何存储的
磁盘存储数据原理
- 磁盘（硬盘）：通常由多个磁盘片组成，每个磁盘片上有多个磁道（同心圆），每个磁道又分为多个扇区。一个扇区是一个基本的存储单元，各个扇区存放数据量相同（如1KB）
- 磁头：用于读/写磁盘。磁头是一个小型的电磁铁（线圈+铁芯），可以产生磁场也可以检测磁场。
读取数据
- 读取原理：将磁信号转为电信号，读取数据时，磁头不产生磁场，而是检测磁盘上的磁化方向。
- 读取过程
- 磁头移动到指定的磁道和扇区位置。
- 线圈切割磁感线，线圈中出现感应电流（电磁感应原理）。通过检测感应电流的大小和方向，可以判断磁化方向
- 根据磁化方向，磁头判断该位置存储的是0还是1。
写入数据
- 写入过程
- 磁头移动到指定的磁道和扇区位置。
- 根据要写入的数据（0或1），磁头产生相应的磁场方向（电流通过线圈产生磁场）
- 磁盘上的磁性材料被磁化为相应的方向，从而记录下数据。
行结构
以下是一行数据在磁盘中存储的信息
- 真实数据：记录每个字段的值
- 额外信息
- 变长字段长度列表：记录每个变长字段的实际长度，比如text类型长度是不固定的，只有记录每个变长字段的长度，才能知道后面字段的位置。
- NULL值的列表：Null值是不记录在真实数据这部分的，所以要记录一下列名
- 记录头：主要记录指向下一条记录的指针、deleted标等
一行数据最长是700多个字节，超出的部分就算溢出，当前数据记录一个指针，超出的部分单独放到溢出页，指针指过去，可能有多个溢出的列。
页结构
行数据是通过页的方式管理起来的，一页是16KB；这是页的组成，行数据放在User Records部分
Infimum/supremum：Inf的next指向页中的第一条有效数据，最后一条数据的next指向Sup- 每页之间以双链表的形式连接起来，页面的整体组织方式如下图
- 记录1和记录2在物理上不一定是连续的，比如记录2是插入到记录1和记录3之间的
- 页内查找：是通过二分法加快查找速度，即每8条数据为一组
- 分裂：如果一页的空闲空间不足以存下要插入的数据，就会分裂成两页
- 索引：如何快速找到数据页，就需要用到索引
区
- 区：一页是16K，64个连续的页成为一个区，也就是1M是一个区。磁盘的分配最小单位是区。
数据更改时页的变化
- 对于
update：- 如果更新后的记录长度没有变化，就可以复用当前
record的磁盘空间，就地更新 - 如果不一样大，则会先删除再创建，因而这种情况一句update会产生两个
undolog的日志
- 如果更新后的记录长度没有变化，就可以复用当前
- 对于
delete：- 则是先把
delete标识改为true（事物执行的中间状态），然后事务提交后，把这条从链表中拆下来，扔到PAGE_FREE这个已删除记录的链表中（该过程为purge），将可能用于insert新记录去复用。PAGE_FREE链表在特定的实际也会被后台线程清理。
- 则是先把
- 对于
insert- 插入时是按照顺序插入的，先找到插入的页，然后找到要插入的行的位置，进行插入。
- 分裂：因为一页的大小是固定的，如果插入一行可能会导致一页的内存不够的情况，此时会分裂，分裂不光是新建一页存数据，还需要把指针父子节点的关系打点好
索引
B+树
-
索引就是给一张表建一个目录，加快查找数据页的速度，每个索引就是一张表
-
MySql索引数据结构：B+树
为什么使用B+树？ -
二分法（二叉树）：由于顺序查找比较慢，所以可以考虑用二分法查找，即构建一颗二叉树，但是随着数据不断增多，可能会导致二叉树一边很高，一边很矮，导致查询效率变低
-
平衡二叉树：为了解决二叉树一边高一边矮，可以使用平衡二叉树，但是当数据量很大时，树依然会变得很高，导致查询效率低；因为平衡二叉树每一层存储的数据量是一定的，第一层1个，第二层2个，第三层四个···，存储1000个数据就需要10层，每次分叉就需要读取一次硬盘数据
-
B树（多路平衡查找树）：每个节点可以包含多个数据（按照顺序排列），每个节点可以有多个分叉，所以B树比较扁平化，缺点：有的数据在根节点附近就能很快查到，有的数据在叶子节点，则查找速度比较慢，导致查询效率不稳定，另外它也不适合做范围查找，因为数据散落在不同的节点上
-
B+树：将所有数据都放到叶子节点上，且子节点一定包含父节点，再将叶子节点用指针全部连接起来，就能解决范围查找的问题了
-
通过B+树的方式组织索引，上面介绍的页结构是数据页的结构，在页内可以使用二分查找，但是如何找到数据页就需要用到索引。
-
B+树是一种多叉搜索树，可以有效的降低查询复杂度。树的每个节点是一个页，其中叶子节点是真正存储数据的，又叫数据页，而其他节点只存储索引值和页号，叫做目录页，数据页和目录页都是索引页index类型的页。
-
下图中每条记录的类型用数字0123表示了，0就是真实的数据记录，1就是只有索引+页号的记录，2是Inf记录，3是Sup记录。
-
第一行：2 1 1 3 / 2 0 0 3（目录页是2113，数据页是2003）
-
2：表示首节点
-
3：表示尾节点
-
0/1：表示普通节点
-
第二行：橙色数据，代表id
-
第三行：绿色数据，代表页号
-
以根节点(页33)为例：1≤id<320的都到30页去找，320≤id的到32页找
聚簇索引与二级索引
- 聚簇索引：又叫主键索引，上图就是聚簇索引，他的叶子节点记录的数据是该条记录完整的数据。
- 二级索引：除了主键外的其他索引，下图就是二级索引，二级索引也会创建B+树，只不过叶子节点不再需要存储所有列的内容，只存储主键和索引列的值两项即可，找到主键后再回到聚簇索引查对应的其他列数据即可，这个过程叫
回表，此外目录节点也需要存储主键+索引列，而不是只存索引列，这样提前命中就可以更早的去回表，如下是二级索引的树结构。橘色是二级索引列，深蓝是主键值
联合索引
- 联合索引也属于二级索引，是将多个列同时作为索引，底层也是建立一个B+树，只不过需要同时存储多个列和主键的值
- B+树节点的大小排序是先按照第一列排序，如果第一列相等，就按照第二列排序，第二列相等，就按照第三列 ···。即最左匹配原则
- 例如a、b两列的联合索引，就可以被where a=1 and b=2这种查询命中，也可以被where a=1这种命中，但是where b=2就不能使用这个联合索引。而对于range条件where a>1 and a<10 and b=1这个查询，a可以使用该索引，b=1不能用该索引，需要a>1 a<10拿出数据挨着比较b是否=1，即索引下推
覆盖索引
select age, id from user where age>10; -- 其中age是二级索引
- 对于上面这个SQL语句，二级索引查到的结果中已经有age和id了，就不用回表了，可以直接返回
总结
- 建索引的优缺点
- 优点：大大增加查询速度
- 缺点：占物理空间，进行增删改的时候也要动态的维护索引
- 索引不适合的场景：数据量少、更新比较频繁、区分度低的字段（如性别）
- 索引会失效的情况：查询条件包含or、like；索引字段上使用 !=、< >、not in、is null、is not null
- 创建索引的方法
// 主键索引：建表的时候，Mysql会自动用主键作为索引
// 唯一索引(唯一索引的列可以包含NULL值)
CREATE UNIQUE INDEX index_name ON table_name (column_name);
// 二级索引(普通索引)
CREATE INDEX index_name ON table_name (column_name);
// 联合索引(即在多个列上创建索引)
CREATE INDEX index_name ON table_name (column1, column2, ...);
// 全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column_name);
// 在现有表上添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
事务
事务隔离级别
- read uncommited(RU): 有脏读问题，即事务A写的数据，还没提交，事务B就读到了，A回滚后，B使用的是脏数据。
- Read Commited(RC): 有不可重复读问题，即事务A查到一条数据，事务B修改了这条并提交，事务A再次查询发现，同一条数据前后两次读出来的结果不一样了。
- Repeatable Read(RR): 这是MySql的默认隔离级别，有幻读问题，即事务A按照条件P查出了一批数据，而事务B插入了一批符合P的数据并提交。此时事务A又用P去查数据，发现条数比之前多了。
- serializable: 无任何并发问题，因为不支持并发，串行执行事务。
MVCC（多版本并发控制）
每一条数据在被事务修改后，历史版本的数据不会被立即删除，每个历史的版本都会保存到undo log页中，通过roll_pointer（roll_pointer指向上一个版本）将一条数据的所有版本串联起来，这就是版本链。注意：版本链中也存在未提交的数据。
下面都是RR隔离级别为例，讲述MVCC工作流程。
trx_id是当事务有写操作时才会申请的自增id，如果纯读事务trx_id=0。
RR的实现是在第一句select的时候创建ReadView，直到事务结束都使用这个视图实现的，而ReadView本质是记录当前所有已经提交和未提交的事务，RR读取数据的时候查看数据的trx_id，如果不是已经提交的事务，就顺着roll_pointer向前直到找到已经提交的版本。
RR和RC最大的区别就是RR是第一句select建立view，而RC是每一句select都重新创建view。
Readview
ReadView能判断一个trx_id是已经提交的还是正在运行的，主要通过以下四个字段：
- max_trx_id：下一个要分配的trx_id
- min_trx_id：所有活跃的trx中最小的id
- m_ids：所有活跃的trx id列表
- creator_trx_id：当前事务的id（可能是0，因为当前可能没有写操作）
trx_id拿来之后先判断是不是>=max_trx_id，如果是的话说明是未来提交的事务，不能用；
然后判断是不是<min_trx_id，如果是的话说明已经提交的事务，直接用；
如果在两者之间，需要判断是不是=creator_trx_id，如果是的话说明是自己改的，直接用；
如果不是的话，需要判断是不是in m_ids，如果是的话说明是未提交的事务，不能用，否则可以用。
ReadView是一个简单的数据结构，RR下每个有读操作的事务都会生成一个ReadView，RC下则可能有多个。ReadView以列表的形式存放在特殊的位置。当一个事务结束后，ReadView就会被删除，因而列表中存放的ReadView都是active的事务。从这个列表中，可以筛选出所有的min_trx_id中最小的，如果比这个id还小2个版本的undolog说明不会被任何事务所依赖了，那这部分就可以被清理了。有个专门的线程来做清理工作。
快照读与当前读
- 快照读：依赖ReadView的读取又叫快照读、视图读、一致性读。保证了不会出现不可重复读和幻读。
- 当前读：
- 写操作用当前读，例如：update user set age=20 where age=19;
- select for update 和 select in share mode 使用当前读
- 当前读就有可能出现幻读，例如
事务A 事务B
开始 开始
select for update(n条)
insert一条符合条件的数据
提交
select for update(n+1条)
提交
因为insert和select for update都是当前读，所以都是最新数据，因而第二次就读出了比第一次多一条的数据。也就是产生了幻读。
为了解决当前读的幻读，mysql引入了锁机制。
锁
行锁
- 目的：解决当前读可能会出现幻读问题，两个事务对同一条数据进行写时，防止并发问题
- 记录锁：就是锁住当前条目，例如update xx where id=1会把id=1这一条锁住防止其他事务对其修改。
- 间隙锁(gap)：是锁住B+树中当前条目和前一条之间的缝隙，例如update xx where id<1假如找到0条数据，并且存在id=1这条，那么就需要锁住(-无穷，1)，防止其他事务在这个范围插入了数据，导致后续当前读读取的条目增加。
- 临键锁(next-key)：就是记录锁+间隙锁，也就是左开右闭区间的锁，例如update xx where id<=1假如找到1条id=1这条数据，那么就需要锁住(-无穷,1]这个范围，因为1也不能被修改，这就是next-key lock。
对于以上三种锁，每种又分互斥锁(X锁)和共享锁(S锁)，X锁加锁后的数据不能被其他事务读或写，S锁加锁后的数据不能被其他事务写，但是可以被读，update、delete等写sql和select for update都是加X锁，而select in share mode是S锁。
- 插入意向锁：是指前面gap或者next-key锁住一个范围之后，如果另一个事务想要在这个范围插入数据会被阻止，并且会分配一把锁给这个事务，只不过是waiting等锁的状态，等gap释放就可以插入了。
- 隐式锁：是针对insert的，默认是不加锁的，减少开销，当另一个事务要访问对应的id的时候，会看这条数据的trx_id来判断是不是已经提交的事务，如果不是，那就需要给这条数据加锁，锁的持有trx是记录中的trx_id，同时给自己创建一把对这条记录的锁，waiting状态。隐式锁可以减少insert时锁的创建，只有发生竞争的时候由另一 个事务惰性创建，借助了trx_id这个隐式的条件。
查看锁：
SELECT * FROM performance_schema.data_locks;
加锁的原则就是防止其他事务影响这句sql中where条件去select的再次运行，对于主键需要锁住聚簇索引，而对于二级索引需要锁住二级索引，并通过回表锁住聚簇索引，防止数据其他字段被修改。
select * from user where id = 1 for update;
-- 记录锁X锁，锁住聚簇索引中id=1的这条
update user set age = age+1 where name = 'lily';
-- 记录锁X锁，锁住二级索引中name='lily'这条，并且回表锁住id=1的这条
-- 如果name不是唯一索引，那么这句加next-key
select * from user where id>=3 for update;
-- 3记录锁，5 next-key,10 next-key,sup next-key
select * from user where age>1 for update;
-- 当前读没有命中索引，每一条记录都加next-key，性能很差
select * from user where name>='sam' for update;
-- 理论上是，二级索引中 sam 记录锁，tim next-key，sup next-key，聚簇索引中 id=3 记录锁，id=10 记录锁
-- 实际是sam也是next-key，原因不明
表锁
表锁的效果等价于对每一条数据加next-key锁，表锁的S锁和X锁使用非常少，代价较大，性能较差，以下方式在事务中获取表锁。
LOCK TABLES user READ;
LOCK TABLES user WRITE;
行锁和表锁存在一定的互斥关系，例如如果在这个表中有在用的行X锁，那想要获取这个表X锁，也是不行的，为了更快的判断这个情况，在进行加行X锁的时候(S锁类似)，需要先给表加IX锁。IX和IS又叫表的意向锁。
自增锁AUTO-INC：比较特殊是专门针对自增键的，比较简单，但是他的生效范围与其他不同，其他行锁都是事务范围的，也就是事务结束的时候，锁才释放，但是自增锁是insert这一句结束就释放锁。
MDL
当我们进行表结构修改DDL的时候使用的并不是表锁，而是server级别的元数据锁(Metadata Lock, MDL)，这就不是innodb引擎级别的锁了。
死锁
当一个事务锁的顺序是id=2,id=3，另一个事务锁顺序反过来，并发运行时，互相持有对方的资源，就会出现死锁。死锁出现时，mysql会选择较小的事务进行回滚，并向上报错。
主从复制
- 作用
- 为了解决Mysql的单点故障，以及提高MySQL的整体服务性能
- 主从复制中分为 主服务器（master）和 从服务器（slave），主服务器负责写，从服务器负责读
- MySQL支持的复制类型
- 基于语句的复制（默认）：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句，效率比较高
- 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
- 混合类型的复制：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
性能调优
- 表结构和索引
- 分库分表：通过分库分表，减少单张表的数据量
- 读写分离：基于mysql的主从复制实现，通过路由的方式使写请求只在master上进行，读请求在slave上进行
- 数据类型：为字段选择合适的数据类型
- 索引：为查询操作创建必要的索引
- SQL语句优化
- 通过慢查询分析需要优化的SQL
- 利用explain或者profile这些工具去分析SQL的执行计划
- 避免使用select * 查询
- 尽可能使用索引扫描
- MySql参数优化
- 设置Buffer_pool的大小
- 设置刷盘策略，平衡好数据安全性和性能的关系
- 硬件及系统配置
- CPU核心数
- 磁盘的读写性能
- 网卡、内存的大小
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
