MySQL中的数据组织是以页为单位的
每一页中的信息包括:
页头
记录页面的控制信息,共占56字节,包括页的左右兄弟页面指针(用来连接相邻的页面),页面空间使用情况等
虚记录
最大虚记录: 比页内最大主键还大
最小虚记录:比页内最小主键还小
记录堆
行记录存储区: 分为有效记录和已删除记录两种
自由空间链表
已删除记录组成的链表
未分配空间
页面未使用的存储空间
页尾
页面组后部分,占8个字节,主要存储页面的校验信息页内记录维护
顺序保证
MySQL中的数据是逻辑连续的
页与页之间的关系图
插入策略
自由空间链表
未分配空间
插入数据时,应该先从自由空间链表中找地插,实在没有空间了再存入未分配空间中,这样空间的利用率就高了
如果页面中已删除的记录很多,但是又直接从未分配空间中插入数据,则会导致页面中的空洞越来越多,表占用的空间很大,但是有效的记录又很少页内查询
遍历
二分查找
这样,就保证了读和写的效率,读使用slot进行二分查找,写使用逻辑连续
MySQL InnoDB索引实现原理及使用优化分析
聚簇索引
数据存储在主键索引中
数据按主键顺序存储
自增主键VS随机主键
由上图可知: 自增主键比随机主键写入的效率更高,因为随机主键会导致节点的分列和数据的移动
二级索引
除主键索引以外的索引
叶子中存储主键值(Data变成了PK)
一次查询需要走两遍索引(需要回表)
主键大小会影响所有索引的大小
比如通过商品id去商品表中查询对应的商品记录,这时候需要对商品id建立索引(商品id不能作为自增主键是因为会暴露出新增的商品数量),
先通过商品id回表找到主键id,然后再根据主键id查到对应的记录
后面又将商品id作为了主键是因为商品id是通过雪花算法生成的,并不是单纯的int类型的自增
联合索引
Key由多个字段组成
最左匹配原则
一个索引只创建一棵树
按第一列排序,第一列相同按第二列排序
如果不是按照最左开始查找,无法使用索引
不能跳过中间列
某列使用范围查询,后面的列不能使用索引索引使用优化分析
存储空间
1.索引文件大小
2.字段大小->页内节点个数->树的层数
主键选择
1.自增主键: 顺序写入,效率高;
2.随机主键: 节点分列,数据移动;
那么我们该如何去设计主键呢?
自增主键是一个无意义的东西,只是用来做一个唯一标识,不能用来做查询(通过主键id之外的字段走的是二级索引,selectById走的是一级索引)
如果自增主键是有意义的,则是业务主键,可以使用一级索引
比如将商品id作为主键,使用雪花算法生成,时间戳+机器id+count计数,这样即能保证唯一,又能保证自增
这样,商品id就是一个业务主键
联合索引使用
1.按索引区分度排序
比如将年龄age和性别sex两个字段作为联合索引, 明显通过年龄查找能排除掉更多的数据,如果男女数量相同,则通过性别查找只能排除掉50%的数据,意思就是年龄比性别的区分度更高,所有将年龄放在前面可以更快的找到想要的数据
而且,如果索引的区分度太低了的话,MySQL在查询的时候可能会不走索引,比如将年龄作为索引,年龄为10到100之间,你查询age>0的数据,就不会走索引,而会全表扫描
2.覆盖索引
这是联合索引的一个特性,举个例子,比如通过uid查手机号phone,第一种是将uid作为索引,这时候需要走二级索引,因为叶子节点中存的是PK,所以只能先通过uid找到PK,然后再回表找到对应的手机号,使用覆盖索引就是将uid和phone作为一个联合索引,这时候都不需要走到叶子节点,在索引节点就可以根据uid找到对应的phone,效率更高
字符串索引
1.设置合理长度
2.不支持以%开头的模糊查询(会走全表扫描, 所以才出现了ES的倒排索引)
MySQL事务基本概念
事务特性 ACID
A(Atomicity原子性): 全部成功或全部失败 分布式事务都需要考虑原子性的问题
I(Isolation隔离性): 并行事务之间互不干扰 Seata考虑了隔离性
D(Durability持久性): 事务提交后, 永久生效
C(Consistency一致性): 通过AID保证
隔离级别
并发问题
脏读: 读取到未提交的数据
(事务T1第一次select id=100,查不到,另一个事务T2 Insert id=100但是没有commit,这时候T1再查就查到了,所以脏读也是一种不可重复读)
不可重复度: 两次读取结果不同
(事务T1第一次select id=100,查不到,另一个事务T2 Insert id=100,如果没有commit,T1依然查不到,但如果commit了,T1再查就能查到)
幻读: select操作得到的结果所表征的数据状态无法支撑后续的业务操作
(不管事务T2是否commit,事务T1都无法读取到T2插入的数据)
但是当T1想插入id=100的数据的时候会由于主键冲突而插入不了,这就很诡异,明明查询id=100的时候查不到数据,但是又插入不了MySQL事务实现原理
多版本并发控制
解决读-写冲突
隐藏列
MySQL数据库中的每一条记录都有一个DB_TRX_ID和DB_ROLL_PTR字段,前者记录的是插入或者修改这条记录的事务id, 后者记录的是上个版本的版本号,根据该版本号能找到上一个版本的数据, 当事务开启的时候就会创建一个活跃事务列表,里面记录了当前未提交的事务id,当该事务去读取数据库中某条记录的时候,会先读取DB_TRX_ID字段,根据这个事务id结合活跃事务列表来进行一系列的判断,判断规则如下图:
快照读的好处是快照读不加锁,并发性能更好
可通过"等价原则"来判断可见性
版本未提交, 不可见
版本已提交,如果是在创建
undo log
1.回滚日志
2.保证事务原子性
3.实现数据多版本
delete undo log:用于回滚, 提交即清理;
update undo log:用于回滚, 同时实现快照读, 不能随便删除
undolog如何清理?
根据系统活跃的最小活跃事务id Read view(把所有的事务都能见到的数据清理掉,没必要保留它的历史版本)
为什么Inno DB count(*)这么慢?
因为一条一条数记录去了,那么为什么不用一个计数器记录数据的条数呢?因为每个事务看到的数据都是不一样的,用一个计数器没法记录
redo log
实现事务的持久性
1.记录修改
2.用于异常恢复
3.循环写文件
redolog的意义:
1.体积小, 记录页的修改, 比写入页代价低
2.末尾追加, 随机写变顺序写, 发生改变的页不固定
锁粒度
行级锁
间隙锁
表级锁
类型
共享锁(S)
读锁, 可以同时被多个事务获取, 阻止其他事务对记录的修改;
排他锁(X)
写锁, 只能被一个事务获取, 允许获得锁的事务修改数据
行级锁
1.作用在索引上
2.聚簇索引 & 二级索引
间隙锁
1.解决可重复读模式下的幻读问题;
2.GAP锁不是加在记录上;
3.GAP锁锁住的位置, 是两条记录之间的GAP;
4.保证两次当前读返回一致的记录
唯一锁不需要使用GAP锁, 非唯一锁才需要
表级锁(偏运维,平时开发基本用不上)
全表扫描 “表锁”(打引号是因为最终还是行锁,只是把所有记录都锁住了)
死锁
如何选择索引?
联合索引: 优于多列独立索引
索引顺序: 选择性高的在前面
覆盖索引: Key里面包含要查询的数据
索引排序: 索引同时满足查询和排序
MySQL使用经验
1.数据库字符集使用utf8mb4
2.VARCHAR按实际需要分配长度 太长了部分数据需要存到溢出页中,需要拼接数据
3.文本字段建议使用VARCHAR
4.时间字段建议使用long 大部分数据库都支持的类型, 方便数据迁移或者换数据库
5.bool字段建议使用tinyint
6.枚举字段建议使用tinyint
7.交易金额建议使用long
8.禁止使用 “%” 前导的查询
9.禁止在索引列进行数学运算, 会导致索引失效;
select * from t1 where id+1 > 1121 不会使用索引
select * from t1 where id > 1121 - 1 会使用索引
10.表必须要有主键, 建议使用业务主键
11.单张表中索引数量不超过5个
12.单个索引字段数不超过5个 (就是联合索引的字段数不超过5个)
13.字符串索引使用前缀索引, 前缀长度不超过10个字符; (不建议将所有的字符都建为索引)
分库分表
是否分表: 建议单表不超过1kw
分表方式:
取模: 存储均匀 & 访问均匀
按时间: 冷热库 比如订单表就适合冷热库的方式,因为每个订单都是有周期的,超过了一定周期就可以认为是冷数据了
分库
按业务垂直分: 比如user表放在一个库, 订单表放在一个库
水平拆分多个库: 比如一个库中存了1024张user表, 但是DB使用存储上限的,当存不下的时候比如可以用4个库,每个库存256张user表
