数据库:物理操作系统文件或其他形式文件类型集合。
实例:mysql数据库由后台线程以及一个共享内存组成,可以简单理解为操作数据库文件的程序。
一个实例操作一个数据库,集群模式下除外。
1.2 mysql体系结构
连接池组件、管理服务和工具组件、SQL 接口组件、查询分析器组件、优化器组件、缓冲(Cache)组件、插件式存储引擎、物理文件
InnoDB:支持事务,面向OLTP应用。支持行锁,支持外键,支持MVCC。1.2版本后支持全文索引。
MyISAM:不支持事务,面向OLAP应用。支持全文索引。MyISAM表由MYD(存放数据)文件与MYI(存放索引)文件构成。MyISAM只缓存索引。
Memory(HEAP):表中的数据都存放在内存中,默认使用哈希索引,速度快。只支持表锁,存放varchar是按照定长char进行,浪费内存。
Master Thread:主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性。
IO Thread:处理Async IO请求的回调处理。四个IO Thread:write,read,insert buffer,log IO Thread
Purge Thread:事务提交后,回收undo页,减轻master thread的负担
Page Cleaner Thread:脏页刷新,减轻master thread的负担。脏页:内存与磁盘数据不一致
- 缓冲池:索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希索引、InnoDB存储的锁信息、数据字典等,页大小默认为16kb。
- LRU List,Free List,Flush List
LRU:最近最少使用,管理缓存池中页的可用性。MySQL对LRU进行优化,最新访问的页会插入到LRU列表尾部3/8处。
Free List:管理空闲页
Flush List:刷新脏页 - redo log:默认8MB,不需要太大。因为redo log刷新回外部redo log文件频率很高(master thread 每秒回刷,事务提交回刷,redo log buffer剩余空间小于一半时回刷)。
- 额外内存池:存放固定对象,数据结构,基本信息(LRU,锁,等待信息…)
Checkpoint要解决的问题
缩短数据库恢复时间,db宕机时,只需要回复检查点后的redo log
缓冲池不够用时,刷新脏页。LRU尾部的页若为脏页,需要刷回磁盘
redo log不可用时,刷新脏页
Checkpoint通过LSN(long sequence number:8B)标记版本
两种Checkpoint
a. Sharp checkpoint:数据库关闭时,脏页刷回磁盘
b. Fuzzy checkpoint:数据库运行时,将一部分脏页刷回磁盘
Fuzzy checkpoint 触发:
-
Master Thread:1s or 10s的频率刷新一定比例的脏页,异步。
-
FLUSH_LRU_LIST checkpoint:保证LRU列表有足够的空闲页
-
Async/Sync Flush checkpoint:保证redo log的循环可用性
-
Dirty page too mush:脏页占据缓冲池太多时(默认75%),强制checkpoint
InnoDB 1.0.x之前的master thread, 包含四个循环:loop,background loop,flush loop,suspend loop
- loop(主循环):主要包含频率为1s和10s的操作,比如redo log刷新、脏页刷新、合并插入缓冲,删除无用的undo页等
- background loop(后台循环):当前没有用户活动时or db关闭时,切换至后台循环,主要做:删除无用undo页、合并插入缓冲、脏页刷新等
- flush loop(刷新循环):刷新脏页
- suspend loop(挂起循环):将master thread挂起
InnoDB 1.2.x之前的master thread,新增了一系列可调参数,提高InnoDB性能。
- 新增innodb_io_capacity:表示磁盘io吞吐量,默认200.按照该值的比例确定刷新脏页的数量。
- 调整了innodb_max_dirty_page_pct:脏页占据缓存池比例。从90变为75
- 新增innodb_adaptive_flushing:决定每秒刷新脏页的数量,按照redo log产生速度来决定最合适的脏页刷新数量
- 新增innodb_purge_batch_size:默认20,控制每次full purge回收undo页的数量
InnoDB 1.2.x的master thread,继续优化,主要时将脏页刷新操作,分离成一个单独的page cleaner thread,减轻master thread负担。
2.5 InnoDB关键特性Insert Buffer:提高性能
解决问题:插入聚集索引时,速度很快,因为大都是连续存放。插入非聚集索引时,需要离散访问,性能下降。 insert buffer就是要解决非聚集索引插入性能低的问题。
使用场景:非唯一辅助索引(secondary index)的插入或更新。辅助索引等价于非聚集索引(叶子节点存放主键值和指向记录的指针),相对于聚集索引(叶子节点存放完整记录)
工作方式:
实现方式:Insert Buffer是一个B+树,叶子节点存放:space id(表id),offset(表记录偏移),record(插入内容/记录)
Double Write:保证数据页的可靠性
**解决问题:**在脏页刷新的过程过,数据库宕机导致数据页不完整,造成后续数据丢失。MySQL数据页默认16KB,OS默认数据页2KB or 4KB,数据库的宕机有可能导致数据页的不完整。
工作方式:
Adaptive Hash Index: 自适应的为热点数据建立Hash索引
建立Hash索引的条件:
- 以改模式访问了100次
- 页通过该模式访问了N次,N=页中记录数/16
- 等值条件(where a=‘xxx’)
Async IO
- AIO对应Sync IO,同步IO:用户发出一个IO请求,需等待请求返回,然后继续其他操作。
- AIO可以merge IO操作,例如user发出三个IO请求,读取MySQL的(space id, offset),分别是(5, 2), (5, 3), (5, 4),AIO会合并这三个IO为:读取从(5, 2)开始的连续48KB的页。
Flush Neighbor Page
工作方式:刷新脏页首先检查当前区(extent:MySQL为1MB,即包含64个连续的页)的所有页,如果是脏页就一并刷新。结合AIO的merge,可以将多个IO操作合并为一个IO操作。
错误日志error log: 记录MySQL的启动、运行、关闭过程
二进制日志binlog: 记录了对MySQL数据库执行的所有更改操作(增删改,“改”包括更改表结构)
- 查看binlog:
mysqlbinlog --no-defaults -v ..databinlog.000008
- binlog 缓冲写入磁盘方式
慢查询日志slow query log:记录慢sql
查询日志log:记录所有对数据库请求的信息
3.2 redo log-
作用:记录了对于InnoDB存储引擎的事务日志
-
redo log结构
-
redo log缓冲写入磁盘方式
crash-safe
假如binlog记录了两条日志:
# orginal status: id=1, age=18 # 记录1 update user set age = age + 1 where id = 1; # 记录2 update user set age = age + 1 where id = 1;
记录1已刷盘,记录2未刷盘,此时MySQL宕机。重启后,binlog无法判断记录1和2中哪条记录已经写入磁盘,无法执行数据恢复;
但是对于redo log,记录1已刷盘后,记录1就会从redo log中失效。重启后,直接把redo log中的数据恢复到内存中即可。所以redo log是crash-safe的。
该图展示了一组4个文件的redo log日志,checkpoint之前表示擦除完了的,即可以进行写的(覆盖写),擦除之前会更新到磁盘中,write pos是指写的位置,当write pos和checkpoint相遇的时候表明redo log已经满了,这个时候数据库停止进行数据库更新语句的执行,转而进行redo log日志同步到磁盘中。
在InnoDB中,都是按照主键顺序存储的。一个表若没有显式定义主键,InnoDB会选择一个not null & unique的索引作为主键,如果不符合则隐式生成一列作为主键。
4.2 InnoDB逻辑存储结构- 表空间:InnoDB逻辑存储结构的最高层。若用户启动了innodb_file_per_table参数,那么每个表的数据、索引都会放在各自的表空间中;但是一些其他信息:undo信息、事务信息、Insert Buffer索引页等,还是存放在共享表空间ibdata1中。
- 段:InnDB按照索引组织表,因此数据段对应B+树的叶子节点,索引段对应B+树的非叶子节点。其他还有回滚段。
- 区:大小固定1MB,InnoDB每页大小16KB(可调),每个区有64个连续页。对于一些小表,InnoDB还有优化策略。
- 页(块):InnoDB磁盘管理最小单位,包含数据页、undo页、事务数据页、系统页等。
- 行:row,每一页最多存储16KB/2-200=7992行记录。
Compact格式(MySQL 5.0后默认)
Redundant格式
Note:Redundant行格式中对于值为NULL的char类型,存储是占用空间的。
行溢出数据
对于char类型的存储,在多字节字符集(指的是某字符编码集一个字符占多于一个字节)的情况下,char与varchar实际行存储是没有区别的。
4.4 约束InnoDB约束类型
- 主键约束:Primary Key
- 非空约束:Not NULL
- 唯一约束:Unique Key
- 外键约束:Foreign Key
- Default
约束与索引的区别
作用上等价,约束是个逻辑概念,索引是个具体的数据结构
触发器
触发器的作用是,在执行UPDATE,DELETE,INSERT命令之前 or 之后自动调用的 SQL命令 或者 存储过程。trigger也是实现约束的一种手段。
# 创建触发器
CREATE [DEFINED = {user | CURRENT_USER}]
TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tab_name FOR EACH ROW trigger_statement
# eg
DELIMITER $$
CREATE TRIGGER tgr_usercash_update BEFORE UPDATE ON usercash
FOR EACH ROW
BEGIN
IF new.cash - old.cash > 0 THEN
INSERT INTO usercash_err_log SELECT old.userid, old.cash, new.cash, USER(), NOW();
SET new.cash = old.cash;
END IF;
END;
$$
before:(insert、update)可以对new进行修改,after不能对new进行修改,三者都不能修改old数据。
外键
定义外键
foreign
CREATE TABLE `son` ( `sid` int DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pid` int DEFAULT NULL, UNIQUE KEY `sid` (`sid`), CONSTRAINT `fk1` FOREIGN KEY (`sid`) REFERENCES `parent` (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 # 定义son.pid列上的主键`fk1`,引用parent表pid列,pid列必须有unique约束或者主键约束 # InnoDB自动为外键约束的列添加索引4.5 视图
视图的作用:根据select语句创建而来,从一张表或多张表导出而产生,用户不需要访问基本表,只需要访问视图即可。起到一个安全层的作用,视图是虚拟表,没有物理存储。
4.6 分区表分区与分表
- 分区:把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上。目的是突破磁盘读写能力,提高sql性能。MySQL支持局部分区,指的是每个分区都有对应的数据文件与索引文件。
- 分表:把表分成N个小表,此时一个表变成了多个表。
分区类型
- range分区:
CREATE TABLE sales(
id INT PRIMARY KEY,
money INT UNSIGNED NOT NULL,
date DATETIME
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(date))(
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022)
);
- list分区:相当于range来说,list取值是离散的
CREATE TABLE t(
id INT PRIMARY KEY,
age INT NIT NULL
)ENGINE=INNODB
PARTITION BY LIST(age)(
PARTITION p1 VALUES IN (10, 12, 13),
PARTITION p2 VALUES IN (11, 14, 15)
);
- hash分区:使用用户定义的函数进行分区
- key分区:使用MySQL提供的函数进行分区
- columns分区:以上四个分区方式都只支持整数型参数,columns分区支持参数类型更多。
子分区
MySQL支持在range和list分区的基础,再次进行hash或key分区。
分区对于NULL值的处理
- range:range分区中认为NULL比任何值都小,同MySQL order by处理逻辑。
- list:需要指定NULL处在哪个分区,否则error
- hash & key:返回0
- B+树索引:balance
- 全文索引:倒排
- Hash索引:InnoDB根据表使用情况自动生成Hash索引,不能人为干预。见章节2.5
B+树数据结构示意图:
- 聚集索引(clustered index):B+树叶子节点存放整条记录。InnoDB自动按照一张表的主键构造一棵B+树,聚集索引对于一个表是唯一的。
- 辅助索引(secondary index):也称为非聚集索引,B+树叶子节点不存放整条记录。可以这样理解:辅助索引叶子节点存放主键,然后再通过主键索引找到对应行记录。
B+树索引的管理
- FIC(Fast Index Creation):创建辅助索引时,对表加S锁,不需要重建表,提高索引创建速度。
- Online DDL:在执行创建or删除的DDL语句时,将INSERT,UPDATE,DELETE这类DML操作日志写入到缓冲中,待完成索引创建后再将重做应用到表上。
查看表的索引
show index show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | user | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ # Table: 表名称 # Non_unique: 是否非唯一索引 # Key name:索引名称 # Seq_in_index:索引中该列的位置,联合索引中,该字段意义比较明显 # Column_name:索引列的名称 # Collation:列以什么方式存储在索引中,可取值为A或NULL,B+树总为A,表示排序的。 # Cardinality:基数,表示索引中唯一值的大概统计 # Sub_part:索引是否为该列的一部分 # Pack:关键字如何被压缩 # Null:索引列是否包含NULL值
Cardinality值
基数,表示索引中唯一值的统计值。rows表示表中记录数,Cardinality/rows越接近1,表明该索引价值越高;Cardinality/rows越接近0,用户需要考虑是否有必要创建这个索引。
Cardinality值统计时间点: 如果每次对表的修改,都去统计Cardinality值,成本过高;InnoDB在UPDATe,INSERT操作时,按照以下规则判断是否进行Cardinality值统计:
- 表中的1/16数据被修改;
- 对同一行的修改超过2_000_000_000次。
统计方式:采样统计。记所有叶子节点数量为 N N N,随机选择8个叶子节点,分别统计节点中的唯一值的数量,记为 N i N_i Ni,估计 C a r d i n a l i t y = ∑ i = 1 8 N i ∗ N / 8 Cardinality=sum_{i=1}^8{N_i}*N/8 Cardinality=∑i=18Ni∗N/8
联合索引 是指对表的多个列进行索引。
CREATE TABLE t(
a INT,
b INT,
PRIMARY KEY(a),
KEY index_a_b(a, b)
);
联合索引B+树结构——以上表t为例
由上图可以看出,B+树叶子节点(2, 1)/(2, 4)/(3, 2)/(4, 1)/(4, 3)/(5, 2),按照(a, b)的顺序进行排放,按照a一级排序,按照b二级排序。a整体有序,b部分有序。
因此才产生了最左前缀原则:
# 联合索引(a, b) select ... from t where a = XXX and b = XXX; # 走索引 select ... from t where a = XXX; # 走索引 select ... from t where b = XXX; # 不走索引 select ... from t where a = XXX order by b; # 走索引 # 联合索引(a, b, c) select ... from t where a = XXX and b = XXX order by c; # 走索引 select ... from t where a = XXX order by c; # 不走索引
覆盖索引:InnoDB存储引擎支持覆盖索引,即能从辅助索引中就可以得到查询结果,就不需要走聚集索引。辅助索引中并不存放整条记录,大小要远小于聚集索引,能够减少IO操作。
哈希索引:InnoDB采用除法散列k mod m,链表方式解决哈希冲突。快速查找缓冲池中的页,将查询关键字转化成一个自然数,然后除法散列。InnoDB存储引擎的表空间都有一个space_id,用户要查询的应该是表空间的某个连续的16KB页,即offset,转化方式:space_id<<20 + space_id + offset,然后通过除法散列到各个“内存槽”。
全文索引
- 概念:将存储于数据库中的整本书或者整篇文章的任意内容信息查找出来的技术。类似于:select XXX from table where col like “%XXX%”;
- 倒排索引:根据内容查文档id
inverted file index:{单词,单词所在文档id}
full inverted index:{单词,(单词所在文档id,在文档中的具体位置)} - 全文检索:InnoDB采用full inverted index
锁是区别文件管理系统与数据库管理系统的关键特性。
6.1两种类型的行级锁:S锁:共享锁,允许事务读
X锁:排他锁,允许事务读写
兼容性:
概述:为表锁,支持多粒度锁定,即允许事务在行级的锁与表级的锁同时存在。
类型:
- IS锁:意向共享锁,事务想要获取一张表中某几行的共享锁
- IX锁:意向排他锁,事务想要获取一张表中某几行的排他锁
应用场景:
–没有意向锁的场景– 事务A要获取表t的共享锁,此时事务A要做两个检查:
- step1:判断表是否已被其他事务用表锁锁表
- step2:判断表中的每一行是否已被行锁锁住(浪费时间)
–有意向锁的场景– 事务A要获取表t的共享锁,此时事务A要做两个检查:
- step1:不变
- step2:发现表t上有意向共享锁,说明表t的某些行被加了S锁,此时事务A被阻塞。
IS、IX、表级S锁、表级X锁 的兼容性
举例说明
user表如下:id INT PK, name VARCAHAR(20)
- 事务A获取某一行的X锁,未提交事务
select * from user where id = 2 for update; # 此时事务A获取了user表的IX锁,以及获取了id=2的行级X锁
- 事务B获取user表的共享锁
lock table user read; # 表级共享锁 # 事务B成功获取user表的IX锁 # 事务B检测到事务A持有user表的IX锁,说明user表里某些行存在X锁 # 事务B加锁请求阻塞
- 事务C获取某一行的X锁
select * from user where id = 1 for update; # 事务C检测到事务A持有user表的IX锁,但是IX锁之间不排斥,事务C成功获取user表的IX锁 # 因为id 为 1 的数据行上不存在任何排他锁,最终事务 C 成功获取到了该数据行上的排他锁。
6.3 一致性非锁定读&一致性锁定读



