MySql

MySQL

SQL语言的四大组成部分：

DDL（数据定义语言）用于创建、删除和修改数据库对象，如表和数据库；
DML（数据操纵语言）涉及数据的增删改操作；
DQL（数据查询语言）专注于数据查询；
DCL（数据控制语言）用于设置和撤销用户权限。

mysql 连接

语法：mysql -u用户名 -p密码 [-h数据库服务器ip地址 -p端口号（mysql的默认端口3306）]

数据库操作

alt text

表操作

create table 表名{
    字段1 字段类型 [约束] [comment 字段1注释],
    ...,
    字段n 字段类型 [约束] [comment 字段n注释]
}[comment 表注释];

约束:
alt text

数据类型：

数字类型

tinyint （小整数值 -127～128 如果是无符号unsigned 0~255）
int
bigint （极大整数值）
float
double
decimal （精度最高的浮点数）

字符串类型

char 定长字符串 0-255 bytes
varchar 不定长字符串 0-65535 bytes

时间日期类型

data （只有日期 YYYY-MM-DD）
datatime 混合时间类型（日期 + 时间 YYYY-MM-DD HH：MM：SS）

添加/修改/删除数据

alt text

查询数据

查询数据语法
alt text

基本查询

alt text

条件查询

select 字段列表 from 表名 where 条件列表;

alt text

分组查询

select 聚合函数（[distinct | all] 字段列表）from 表名;

如果指定了distinct , 则表示在计算时要取消指定列中的重复值

alt text

select 字段列表 from 表名 [where 条件列表] group by 分组字段名 [having 分组后过滤条件];

聚合函数的使用：
在 MySQL 5.7 版本中引入了 ONLY_FULL_GROUP_BY SQL 模式，这个模式要求当使用 GROUP BY 子句时，SELECT 列表中的所有列都必须是聚合函数（如 SUM(), COUNT(), MAX(), MIN() 等）的结果，或者是 GROUP BY 子句中指定的列。如果没有使用 GROUP BY 子句，那么 SELECT 列表中的所有列都必须是聚合函数的结果。

总结：

如果查询的逻辑需要对某些列进行分组，那么应该在查询中添加 GROUP BY 子句，并在 SELECT 列表中只包含分组的列或者聚合函数。
如果不需要对数据进行分组，那么应该从 SELECT 列表中移除非聚合列

-- 没有分组，使用聚合函数
SELECT AVG(salary), SUM(salary)
FROM employees;

-- 分组后，使用聚合函数
SELECT department_id, AVG(salary), MAX(salary)
FROM employees
GROUP BY department_id;

where 和 having 的区别
alt text

排序查询

select 字段列表 from 表名 [where 条件列表] group by 分组字段名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式分为 ASC （升序，默认值）和 DESC （降序）

分页查询

select 字段列表 from 表名 limit 起始索引，查询记录数;

起始索引 = （页码 - 1）* 每页展示记录数

数据查询的相关函数

用于信息统计：
alt text

SELECT * FROM users WHERE IF(id > 3, name = '王2', name = '王3');

SELECT * FROM users WHERE IF(id > 3, name = '王2', true);

select
    case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end,
    count(*)
    from tb_emp group by job;

多表查询

内连接

查询两个表的交集，消除多余的笛卡尔积
alt text

外连接

左外连接：左表数据 + 两个表的并集
右外连接：右表数据 + 两个表的并集

alt text

子查询

SQL语句中嵌套select语句，称为嵌套查询，或者子查询
语法：select * from t1 where column1 = (select column1 from t2 ...);

子查询外部的语句可以是select/delete/update/insert 中任意一个

子查询分类：

标量子查询
列子查询
行子查询
表子查询

事务

事务是一组操作的集合，它是一个不可分割的工作单元。事务会把所有操作作为一个整体一起向系统提交或者撤销操作请求。即这些操作要么全做，要么都不做（要么全部成功，要不全部失败）。

开启事务：start transaction; / begin;
提交事务：commit;
回滚事务：rollback;

注意：当我们执行了DDL语句之后，系统会自动的执行一次commit，连带着前面的添加数据一起完成了提交。

索引

MySQL 用的是B+树索引（多路平衡搜索树）

语法：

创建索引：create [unique] index 索引名 on 表名（字段名）;
删除索引：drop index 索引名 from 表名;
查看索引：show index from 表名;

预编译SQL

通过 @Delete("delete from emp where id=#{id};") 进行预编译SQL
作用：

性能更高
防止SQL注入，更加安全
#{} ：执行SQL时，会将 #{} 替换成？，生成预编译SQL；会自动设置参数值;
${} : 直接拼接字符串

tips: 如果是在引号中，不能直接使用#{} 传递参数，而要用concat拼接函数
ex. '%张%'
concat('%', #{}, '%')

用户控制/权限控制

-- 一、用户控制

-- 创建用户‘cslb’ ，只能够在当前主机localhost中访问数据库，密码为‘123456’
create user 'cslb'@'localhost' identified by '123456';

-- 创建用户，可以在任意主机访问
create user 'allhost'@'%' identified by '123456';

-- 修改用户密码
alter user 'cslb'@'localhost' identified by '1234';

-- 删除用户
drop user 'allhost'@'%';

-- 二、权限控制
      -- 多个权限之间使用逗号分隔

-- 1.查询权限
show grants for 'cslb'@'localhost';

-- 2.授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant all on tlias.* to 'cslb'@'localhost';

-- 3.撤销权限 revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
revoke select on tlias.* from 'cslb'@'localhost';

函数

字符串函数

alt text

char_length(str) : 返回字符串长度

数值函数

alt text

日期函数

alt text

流程函数

alt text

用于信息统计：
alt text

SELECT * FROM users WHERE IF(id > 3, name = '王2', name = '王3');

SELECT * FROM users WHERE IF(id > 3, name = '王2', true);

select
    name,
    case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地点'
    from emp;

函数的使用

-- 字符串函数
update emp set workno = lpad(workno,5,'0') where id>=1;

update emp set workno = substr(workno,5,1) where id>=1;

select * from emp where char_length(name) = 2;

-- 数值函数
select ceil(1.5) as '向上取';

select rand() as '0-1的随机数（包括0，不包括1）';

select round(2.3464,2) as '求数的四舍五入的值，保留两位小数';

select lpad(round(rand()*1000000,0),6,'0') as '六位数随机值';

-- 日期函数
select now() as '当前时间';

select name,datediff(curdate(),entrydate) as '入职天数' from emp order by datediff(curdate(),entrydate) desc;

-- 流程函数
-- if
select if(false,'ok','Error');

-- ifnull
select ifnull('ok','isnull'); # 'ok'
select ifnull('','isnull'); # ''
select ifnull(null,'isnull'); # null

select
    name,
    case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地点'
    from emp;

约束

alt text

外键约束

外键约束的删除和更新行为
alt text

alt text

外键的删除和更新行为语句
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade

多表查询

多表关系

一对多
- 在多的一方建立外键，指向一的一方的主键
多对多
- 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一
- 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）
- 作用：一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

去除笛卡尔积

-- 结果是一个笛卡尔积
select * from emp, dept;

-- 多表查询加上连接查询的条件来去除无效的笛卡尔积（null值的样本不会查询到）
select * from emp, dept where emp.dept_id = dept.id;

连接查询

内连接

查询两个表的交集，消除多余的笛卡尔积
alt text

外连接

左外连接：左表数据 + 两个表的并集
右外连接：右表数据 + 两个表的并集

alt text

自连接

当前表与自身的连接查询，自连接必须使用表别名
alt text

自连接查询，可以是内连接查询，也可以是外连接查询

-- 笛卡尔积
select * from tb_user,tb_user_edu;
select * from emp,dept;

-- 消除笛卡尔积
-- 1.隐式内连接
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

-- 2.显式内连接  inner join ... on ...
select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;

-- 3.左外连接(交集数据和左表所有数据)
select e.name,d.name from emp e left outer join  dept d on e.dept_id=d.id;

-- 4.右外连接(交集数据和右表所有数据)
select e.*,d.* from emp e right outer join dept d on e.dept_id=d.id;

-- 5.自连接（必须要给表起别名）
-- select a.*,b.* from emp a,emp b where a.managerid=b.id;
-- 查询员工姓名及其领导姓名
select a.name '员工姓名',b.name '领导姓名' from emp a,emp b where a.managerid=b.id;

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A ... union[all] select 字段列表 from 表B ...;

tips:

联合查询时，多张表的列数要保持一致，并且查询的字段类型也要一致
union all的话是直接合并； union 会进行去重

-- 查询薪资大于5000 和 年龄大于50的员工
select * from emp where salary>5000
union all
select * from emp where age>50;

子查询（嵌套查询）

SQL语句中嵌套select语句，称为嵌套查询，或者子查询
语法：select * from t1 where column1 = (select column1 from t2 ...);

子查询外部的语句可以是select/delete/update/insert 中任意一个

标量子查询

alt text

-- 一、标量子查询
-- 1. 查询“销售部”的员工信息
-- a. 查询销售部的部门id
select id from dept where name = '销售部';
-- b. 根据部门id查询销售部的员工
select * from emp where dept_id = (select id from dept where name = '销售部');

列子查询

子查询返回的结果是一列
常用操作符：
alt text

-- 二、列子查询
-- 1. 查询“销售部”和“市场部”的员工信息
-- a. 查询“销售部”和“市场部”的部门id
select id from dept where name = '销售部' or name = '市场部';
-- b. 根据部门id 查询 “销售部”和“市场部”员工（使用 in 关键字）
-- select * from emp where dept_id in (2,4);
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

-- 2. 查询比财务部所有人工资都高的员工信息

-- a. 查询财务部所有人的最高工资
select max(salary) from emp where dept_id = (select id from dept where name = '财务部');

-- b. 查询比财务部所有人工资都高的员工信息
select *
from emp where salary >
               (select max(salary) from emp where dept_id = (select id from dept where name = '财务部')) order by salary asc;

行子查询

子查询返回的结果是一行

-- 三、行子查询
-- 1. 查询与“张无忌”的薪资及领导相同的员工
--    select * from emp where (salary,managerid) = (12500,1);

-- a. 查询“张无忌”的薪资及领导
select salary,managerid
from emp where name='张无忌';

-- b. 查询与“张无忌”的薪资及领导相同的员工
select *
from emp where (salary,managerid) =
               (select salary,managerid from emp where name='张无忌');

表子查询

子查询返回的结果是多行多列; 将查询到的结果当成一个新表

（） in （）

-- 四、表子查询
-- 1. 查询与 “鹿杖客” 和 “宋远桥” 的薪资及职位相同的员工
-- a. 查询 “鹿杖客” 和 “宋远桥” 的薪资及职位
select salary,job
from emp where name='鹿杖客' or name = '宋远桥';

-- 查询与 “鹿杖客” 和 “宋远桥” 的薪资及职位相同的员工 (使用in关键字)
select *
from emp where (salary,job) in
               (select salary,job from emp where name='鹿杖客' or name = '宋远桥');


-- 2. 查询入职日期是2006-01-01之后的员工信息及部门信息
select *
from emp where enterdate>'2006-01-01';
-- 使用左外连接；自连接会少一个无部门的员工 的信息
select e.*,d.* from (select * from emp where enterdate>'2006-01-01') e left join dept d on e.dept_id = d.id;
-- 
select e.*,d.* from (select * from emp where enterdate>'2006-01-01') e ,dept d where e.dept_id = d.id;

SQL索引

索引结构

为什么InnoDB存储引擎选择使用 B+tree索引结构？

相对于二叉树
相对于B树
相对于Hash索引

索引分类

alt text

在InnoDB存储引擎中，根据索引的存储形式可以分为：

聚集索引：将数据存储和索引放到了一块，索引结构的叶子结点保存了行数据
- 必须有，而且只有一个
二级索引：将数据和索引分开存储，索引结构的叶子结点关联的是对应的主键
- 可以存在多个

聚集索引选取规则

如果存在主键，主键就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果都没有，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

对于select * from user where name = 'Arm';的回表查询示意图

alt text

因此，根据id查询的效率高 于根据其余字段查询效率

索引语法

查询索引
show index form 表名;
创建常规索引
create index 索引名称 on 表名(字段名);
创建唯一索引
create unique index 索引名称 on 表名(字段名);
创建联合索引
create index 索引名称 on 表名(字段名,...,字段名);
删除索引
drop 索引名称 on 表名

SQL性能分析

查询数据库操作执行频次
show global status like 'Com_______';
慢查询日志
profiling 详情

查询是否支持 select @@have_profiling;
查询开启状态 select @@profiling;
开启 set profiling = 1;
查看SQL语句执行时间：show profiles;
查询指定query id的耗时情况：show profiles for query id;

explain

索引使用

对于联合索引的使用规则：

最左前缀法则
如果索引了多列（联合索引），要遵守 最左前缀法则：

查询要从建立联合索引时最左列的字段开始，否则索引会全部失效。并且不跳过索引中的列，如果跳过了，后面的索引将部分失效。（跟SQL语句的书写顺序无关）（是否全部生效根据key_len判断）
范围查询
联合索引时，出现范围查询（> ，<）时，范围查询右侧的列索引失效。
建议：使用大于等于或者小于等于，就不会失效了

对于所有索引的使用规则

索引列运算
不要在索引列上进行运算操作，索引将失效
字符串不加引号
字符串类型字段使用时，不叫引号，索引将失效
模糊查询

如果仅仅是尾部模糊匹配，索引不失效（‘张%’）
如果是头部模糊匹配，索引失效（‘%张’）（‘%张%’）

or连接的条件
用or分割的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引将不会用到
数据分布影响
如果MySQL评估使用索引比全表查询慢，则不使用索引
SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为提示来达到优化操作的目的

select * from 表名 use/ignore/force index(索引名称) where 查询条件;

use：建议使用
ignore：忽略索引
force：强制使用

覆盖索引

尽量使用覆盖索引：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，不需要去回表查询。（因此要避免使用select *）

explain 的Extra字段

NULL: 没有使用索引，需要回表查询
using index condition :查找使用了索引，但是需要回表查询
Using where;using index : 查找使用了索引，但需要返回的数据都在索引列中能找到，因此不需要回表查询

前缀索引
当字段类型为字符串时，有时候需要索引很长的字符串，这会导致索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。
此时只需要将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
create index 索引名称 on 表名(字段名(n));

前缀长度的选择
根据索引的选择性来决定；索引选择性越高，查询效率越高
选择性：不重复的索引值和数据表的记录总数的比值。

select count(distinct 字段名)/count(*) from 表名
select count(distinct substring(字段名,start,end) )/count(*) from 表名

单列索引和联合索引的选择问题

在业务场景中，如果存在多个查询条件，建议建立联合索引，而非多个单列索引

因为如果使用单列索引，很容易出现回表查询的情况，造成性能下降。

联合索引的B+tree：
alt text

索引选取原则
alt text

SQL优化

插入数据

insert优化

批量插入
手动提交事务
主键顺序插入
load指令

主键优化

主键设计原则：

满足业务要求下，尽量降低主键长度
插入数据，尽量选择顺序插入（B+tree的页合并、页分裂），选择使用AUTO_INCREASE 自增主键
不要使用UUID或者其他自然主键，因为这些是无序的
操作时，避免修改主键（会导致索引结构变化）

order by优化

排序查询时，Extra 字段分为 Using filesort 和 Using index（通过有序索引顺序扫描直接返回有序数据）

根据排序字段建立合适的索引，多字段排序时也遵循建立联合索引时的最左前缀法则
尽量使用覆盖索引，否则排序索引失效
多字段排序，默认是升序，如果想要降序，则要在建立索引时指定规则（asc/desc）
如果不可避免得出现filesort，可以增加排序缓冲区大小 sort_buffer_size（默认256k）

group by优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

limit优化

优化思路：覆盖索引（先找出数据的主键id）+ 子/连接查询
select * from table t, (select id from table order by id limit 100000,10) a where t.id = a.id;

count优化

count(*)的效率最高。InnoDB并不会把全部字段都取出来，而是专门做了优化，不取值，直接按行相加
alt text

update优化

使用更新语句时，要注意更新的查询条件要有索引。
因为InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，是否会从行锁升级为表锁。导致并发性能降低

视图

视图是基于 SQL 语句的结果集的可视化的虚拟表；本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。

作用：

简单： 简化用户对数据的理解，也可以简化操作，那些被经常使用的复杂的 SQL 查询可以定义为视图；
安全： 通过只给用户访问视图的权限，保证数据的安全性；
数据独立： 视图可以帮助用户屏蔽真实表结构变化带来的影响

视图检查条件

with local/cascaded check option
cascaded 会检查级联的所有视图；local只会检查加了with check option的

视图更新要求：
alt text

存储过程

存储过程：事先经过编译并存储在数据库中的一段SQL语句的集合
存储过程思想：SQL语句的代码封装和复用，从而提高效率

游标

游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

触发器

触发器是和表有关的数据库对象；指在insert/update/delete操作之后或者之前，触发并执行触发器中定义的SQL语句集合。行级触发器（比如更新了5行数据，则触发器会执行5次）
alt text
作用：确保数据的完整性、日志记录、数据校验

创建触发器语法：

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

锁

MySQL中，按照锁的粒度分为：

全局锁：锁定数据库中的所有表
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁）： 又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）： 又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

	S 锁	X 锁
S 锁	兼容	冲突
X 锁	冲突	冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显式加共享锁或排他锁。

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

全局锁

主要用于数据备份（InnoDB可以通过 --single-transaction 参数实现不加锁的数据备份）

加锁：flush tables with read lock;
解锁：unlock tables;

表级锁

表锁

表共享读锁（read lock）
表独占写锁（write lock）

语法：

加锁：lock tables 表名… read/write
释放锁：unlock tables / 客户端断开连接

读锁：
alt text

写锁：
alt text

元数据锁（MDL）

元数据锁主要是维护表元数据的数据一致性，在表上有活动事务时，不可以对元数据进行写入操作；为了避免 DDL 和 DML 的冲突，保证读写的正确性

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

alt text

意向锁

为了解决表锁和行锁的冲突问题
如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

InnoDB引入意向锁，使得表锁不用每一行都进行检查是否加锁。

意向共享锁（IS）：由语句select... lock in share mode 添加
意向排他锁（IX）：有insert、update、delete、select... for update 添加

意向锁互斥情况：
意向锁内部之间不会互斥

	IS 锁	IX 锁
read 表锁共享锁	兼容	互斥
write 表锁排他锁	互斥	互斥

这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥

行级锁

InnoDB的数据是基于聚集索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。行级锁分为三类：

行锁（update）： 锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持。
间隙锁（Gap Lock）： 锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
临键锁（Next-Key Lock）： 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。主要为了避免并发事务下出现幻读

行锁

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

不同语句对应的加行锁类型：
执行DML语句时，会自动得加上行锁
alt text

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB默认使用 next-key 锁进行搜索和索引扫描，以防止幻读。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

间隙锁 & 临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁（next-key lock）退化为间隙锁。
索引上的范围查询(唯一索引) – 会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

MVCC

快照读

当前读

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。
alt text

InnoDB引擎

逻辑存储结构
表空间、段、区、页、行
alt text

架构

alt text

内存结构

Buffer Pool（缓冲池）
用来缓存表数据与索引数据，减少磁盘 IO 操作，提升效率
Change Buffer（更改缓冲区）
在进行 DML 操作时，如果请求的是辅助索引（非唯一键索引）没有在缓冲池中时，并不会立刻将磁盘页加载到缓冲池，而是在 CB 记录缓冲变更，等未来数据被读取时，再将数据合并恢复到 BP 中。
Adaptive Hash Index（自适应哈希索引）
用于优化对与缓冲池数据的查询
Log Buffer（日志缓冲区）
用来保存要写入磁盘上 log 文件（ Redo/Undo ）的数据，日志缓冲区的内容定期刷新到磁盘 log 文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到 BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘 I/O 。

磁盘结构

后台线程

后台线程的作用：在合适的时机，将缓冲池中的数据刷新到磁盘中

事务原理

原子性：undo log
持久性：redo log
隔离性：锁 + MVCC
一致性：undo log + redo log

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性 （事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。）

redo log 包括两部分：

内存中的 redo log buffer
磁盘中的 redo log file

redo log 机制
当事务提交之后会把所有修改信息都存到该日志文件中, 用于当刷新脏页到磁盘发生错误时, 进行数据恢复使用。（也称为先行日志）

redo log 机制流程图

alt text

为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢
因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。
而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。
=>顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

undo log

回滚日志 用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。
逻辑日志与物理日志的区别

逻辑日志是记录每一步执行的操作
物理日志是记录存放的数据

MVCC

MVCC定义：指维护一个数据的多个版本，使得读写操作没有冲突

实现原理：
快照读为MySQL实现MVCC提供了一个非阻塞读功能。

需要依赖于数据库记录中的三个隐式字段、undo log日志、readView

三个隐式字段
alt text

MySQL 系统数据库

MySQL数据库初始自带四个系统数据库
alt text

MySQL管理常用工具

mysqlshow
alt text

MySql

https://cs-lb.github.io/2024/10/22/Java/MySql/

作者

Liu Bo

发布于

2024年10月22日

许可协议

Java-Mybaits 上一篇

JavaWeb知识下一篇

MySql

MySQL

mysql 连接

数据库操作

表操作

添加/修改/删除 数据

查询数据

基本查询

条件查询

分组查询

排序查询

分页查询

数据查询的相关函数

多表查询

内连接

外连接

子查询

事务

索引

预编译SQL

用户控制/权限控制

函数

字符串函数

数值函数

日期函数

流程函数

函数的使用

约束

外键约束

多表查询

连接查询

内连接

外连接

自连接

联合查询

子查询（嵌套查询）

标量子查询

列子查询

行子查询

表子查询

SQL索引

索引结构

索引分类

索引语法

SQL性能分析

索引使用

SQL优化

插入数据

主键优化

order by优化

group by优化

limit优化

count优化

update优化

视图

视图检查条件

存储过程

游标

存储函数

触发器

锁

共享锁和排他锁

全局锁

表级锁

表锁

元数据锁（MDL）

意向锁

行级锁

行锁

间隙锁 & 临键锁

MVCC

快照读

当前读

InnoDB引擎

架构

内存结构

磁盘结构

后台线程

事务原理

redo log

添加/修改/删除数据