MySQL从入门到跑路

SQL语言

SQL（Structured Query Language，结构化查询语言）是用于管理和操作关系数据库的一种标准编程语言。

SQL分类：

DDL(Data Definition Language)：数据定义语言，用于操作数据库、表、字段，常见命令：CREATE、ALTER、DROP、RENAME、TRUNCATE。
DML(Data Manipulation Language)：数据操作语言，用于操作数据库表中的数据，即增删改等，常见命令：INSERT、DELETE、UPDATE。
DCL(Data Control Language)：数据控制言语，用于控制数据库用户的访问权限和安全性，常见命令：GRANT、REVOKE。
DQL(Data Query Language)：数据查询语言，用于查询数据库表中的数据，常见命令：SELECT。

Note： 虽然SELECT视作DQL语句，但是有时候也可以视作DML语句，在MySQL文档中就被视作Data Manipulation Statements。

DDL - (数据库/表/字段操作)

数据库操作

1.查看所有数据库

show databases;

2.创建数据库

create database database_name;

-- 也可以加字段
create database database_name IF NOT EXISTS;

-- 也可以指定字符集
create database database_name charset utf8mb4;

3.使用数据库

use database_name;

4.删除数据库

drop database databse_name;

表操作

1.创建表

CREATE TABLE IF NOT EXISTS table_name(
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	...
	字段N 字段N类型[COMMENT 字段N注释]
)[COMMENT 表注释]

2.查看表

-- 查询所有表
show tables;

-- 查询建表语句
show create table table_name;

-- 查看表描述
desc table_name;

3.修改表名

alter table emp rename to tbl_emp;

-- 等价 RENAME 语句
RENAME table emp to tbl_emp;

-- 区别在于 RENAME 语句可以同时修改多个表名
RENAME table old_table1 to new_table1, old_table2 to new _table2;

4.删除表

drop table if exists tbl_emp;

5.清空表（删除再创建新表）

truncate table tbl_emp;

字段操作

1.增加字段（Column）

alter table emp add nickName varchar(100);

2.修改数据类型

alter table emp modify nickname varchar(24);

3.修改字段名和类型

alter table emp change nickname username varchar(20);

4.删除字段

alter table emp drop username;

DML - (增删改操作)

1.添加数据

-- 指定字段添加数据
INSERT INTO table_name(字段1,字段2,...,字段N) values(值1,值2,...,值N);

-- 全字段添加数据
INSERT INTO table_name VALUES(值1,值2,...,值N);

-- 批量添加数据
INSERT INTO table_name VALUES(值),(值),...,(值);

-- 也可以从其他表复制数据插入
INSERT INTO table_name SELECT * FROM table_name;

2.修改数据

UPDATE table_name SET 字段1=值1,字段2=值2,...,字段N=值N WHERE 条件;

3.删除数据

DELETE FROM table_name WHERE 条件

DQL - (数据查询)

基础查询

1.查询某个字段

select name from emp;

2.查询所有字段

select * from emp;

3.取别名查询

select name as 名称 from emp;

4.去重查询

select distinct(name) from emp;

条件查询

语法

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

条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN…AND…	在某个范围之内（包括最小值、最大值）
IN(…)	在IN之后的列表中的值，多选一
LIKE 占位符	模糊匹配(`_`匹配单个字符,`%`匹配多个字符)
IS NULL/IS NOT NULL	是否为NULL
AND 或 &&	并且
OR 或 \|\|	或者
NOT 或 !	非，不是

聚合查询

什么是聚合？

将一列数据作为一个整体，进行纵向计算。

1.统计数量

select count(*) from emp;

-- 性能对比
-- count(字段) < count(主键) < count(1) ≈ count(*)

2.求最大值

select max(score) from student;

3.求最小值

select min(score) from student;

4.求平均值

select avg(score) from student;

5.求总和

select sum(score) from student;

分组查询

语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

Note： WHERE 和HAVING的区别

执行时机不同：WHERE在分组之前执行，不满足条件的不参与分组；而HAVING是分组之后过滤。
判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以。

使用

1.分组统计数量

select job,count(*) from emp group by job;

2.根据条件分组统计数量

select job,count(*) from emp where age>20 group by job;

3.根据条件分组统计数量，并且根据数量进行过滤

select job,count(*) from emp where age>20 group by job having count(*) > 2;

Note： 分组操作通常搭配聚合函数执行，分组后查询字段为聚合函数和分组字段，查询其他字段没有意义！

排序查询

语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2...;

排序方式

ASC：升序（默认值）
DESC：降序

Note： 多字段排序时，只有第一个字段排序结果相同，才会根据第二个字段排序。

使用

1.单字段排序

select * from emp order by age desc;

2.多字段排序

select * from emp order by age desc,salary desc;

分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

Note：

起始索引从0开始，起始索引=（页码-1）* 查询记录数。
分页查询是数据库的方言，MySQL的分页关键字是LIMIT。
如果查询的是第一页数据，起始索引可以省略，比如：LIMIT 10。

使用

1.查询第1页数据

select * from emp limit 0,10;

-- 等价
select * from emp limit 10;

2.查询第8页数据

-- 起始索引=（8-1）*10=70
select * from emp limit 70, 10;

DQL 执行顺序

SELECT 
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	字段
HAVING
	分组后条件列表
ORDER BY
	字段
LIMIT
	分页参数

-- FROM > WHERE > GROUP BY > SELECT > HAVING > ORDER BY > LIMIT

DCL - (用户访问权限控制)

用户管理

查询用户

select  * from mysql.user;

# 查询用户
select host, user,plugin from mysql.user;

创建用户

create user 'test'@'localhost' identified by '123456';

-- 指定密码验证插件
create user if not exists 'test'@'localhost' identified with mysql_native_password by '123456';

-- 查看所有校验插件
select * from information_schema.plugins where plugin_type='AUTHENTICATION';

-- 查看默认的校验插件
show variables like 'default_authentication_plugin';

-- 自MySQL 8.0.34版本起，`mysql_native_password`身份验证插件已被弃用,从这个版本后看到的默认都是`caching_sha2_password`。

修改用户密码

alter user 'test'@'%' identified by '123';

删除用户

drop user 'test'@'localhost';

权限控制

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询权限
INSERT	插入权限
DELETE	删除数据权限
UPDATE	修改数据权限
ALTER	修改表
DROP	删除表/数据库
CREATE	创建数据表/库

官网链接-权限介绍

查询权限

show grants for '用户名'@'主机名'；

-- 栗子
show grants for 'test'@'localhost';

授予权限

grant 权限列表 on 数据库.数据表 to '用户名'@'主机名';

-- 栗子
grant all on clcao.* to 'test'@'localhost';

# 可选操作
flush privileges;

撤销权限

revoke 权限列表 on 数据库.数据表 from '用户名'@'主机名';

-- 栗子
revoke create on clcao.* from 'test'@'localhost';

Note:

多个权限之间，使用逗号分隔
数据库和数据表都可以使用通配符*

函数

1. 字符串函数

CONCAT(s1,s2,…,sn)：字符串拼接
LOWER(str)：转为小写
UPPER(str)：转为大写
LPAD(str,n,pad)：字符串填充（左），达到n个长度，用pad拼接
RPAD(str,n,pad)：字符串填充（右）
TRIM(str)：去掉头尾空格
SUBSTRING(str,start,len)：从start开始截取len长度的字符，注意start 从1开始

2. 数值函数

CEIL(x)：向上取整
FLOOR(x)：向下取整
MOD(x,y)：求余
RAND()：随机
ROUND(x,y) 四舍五入，保留y位小数

3. 日期函数

CURDATE()
CURTIME()
NOW()
YEAR()
MONTH()
DAY()
DATE_ADD(date,INTERVAL expr type)
DATEDIFF(date1,date2)

-- 栗子
select date_add(now(),INTERVAL 70 DAY); 当前日期往后70天

4. 流程函数

IF(value,t,f)    如果value为true则返回t，否则返回f
IFNULL(value1,value2)   如果value1为空返回value2否则返回value1
case when then else end

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

Note:

字符串
数值
日期
流程控制

约束

作用于表中的字段，用于限制存储在表中的数据。

常见约束

约束	描述	关键字
非空约束	限制字段不为空	NOT NULL
唯一约束	字段数据唯一不重复	UNIQUE
主键约束	唯一标识，非空且唯一	PRIMARY KEY
默认约束	字段的默认值	DEFAULT
检查约束	保证数据满足某一个条件	CHECK
外键约束	用于两张表之间保证数据的完整性一致性	FOREIGN KEY

-- 栗子
create table user (
	id int primary key auto_increment comment '主键',
	name varchar(10) not null unique comment '姓名',
	age int check(120>=age>0) comment '年龄',
	status char(1) default 1 comment '状态',
	gender char(1) comment '性别'
) comment '用户表';

-- 插入数据
insert into user(name,age,status,gender) values('Tom',19,'1','男'),('Bob',20,'1','男');

-- 查看语法错误
show warnings\G;


-- 准备数据
create table dept (
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not nuLL comment '部门名称'
) comment '部门表';

insert into dept (id,name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

create table emp( 
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',  
age int comment '年齢',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
) comment '员工表';

INSERT INTO emp (id, name,age,job,salary,entrydate,managerid,dept_id) VALUES  
(1,'金庸',66,'总裁',20000,'2000-01-01',nULL,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),(3,'杨道',33,'开发',8400,'2000-11-03',2,1),(4,'书一笑',48,'开发',11000,'2002-02-05',2,1),(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

添加外键

alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) [on update cascade on delete cascade];

-- 指定外键行为
-- 当更新或者删除主表数据时候，从表会同步修改或者删除

删除外键

alter table emp drop foreign key fk_emp_dept_id;

外键行为列表

删除/更新行为

行为	描述
NO ACTION	不允许删除和修改
RESTRICT	同上
CASCADE	级联行为
SET NULL	设置空
SET DEFAULT	设置默认值

Note:

外键当中的默认行为: NO ACTION

外键用来让两张表数据建立联系，从而保证数据的完整性和一致性，建立外键的表叫子表，引用的那张表叫父表，有时候也叫主表，子表也叫从表。

外键约束

添加外键

-- 方式1  建表时候添加外键
CREATE TABLE table_name(
	CONSTRAINT 外键名称 foreign key(外键字段) REFERENCES(主键字段);
);

-- 方式2 建表后添加外键
ALTER TABLE table_name ADD CONSTRAINT 外键名称 foreign key(外键字段) REFERENCES(主键字段);

多表查询

多表关系

一对多（多对一,多的一方建立外键）
一对一（多用于单表拆分）
多对多（需要中间表建立联系）


-- 多对多
create table student (
	id int auto_increment primary key comment 'ID',
	name varchar(10) comment '姓名',
	no varchar(10) comment '学号'
)comment '学生表';

insert into student values (nuLL,'黛绮丝','2000100101'),(nULl,'谢逊','2000100102'),(nULL,'殷天正','2000100103'),(nULL,'韦一笑','2000100102');

create table course(
	id int auto_increment primary key comment 'ID',
	name varchar(10) comment '课程名称'
)comment '课程表';

insert into course values (null,'Java'), (null, 'PHP'), (null, 'MySQL'), (null, 'Hadoop');

create table student_course(
	id int auto_increment  primary key comment '主键',
	studentid int not null comment '学生ID', 
	courseid int not null comment '课程ID',  
	constraint fk_courseid foreign key (courseid) references course (id),
	constraint fk_studentid foreign key (studentid) references student(id)
)comment '学生课程中间表';

insert into student_course values (null, 1,1), (null, 1,2), (null, 1,3), (null, 2,2), (null, 2,3), (null, 3, 4) ;

连接查询

内连接

内连接查询的是两张表的交集数据。

隐式内连接

select * from emp,dept where emp.dept_id=dept.id;

显式内连接

select * from emp join dept on emp.dept_id=dept.id;

外连接

左外连接

select * from emp left join dept on emp.dept_id=dept.id;

右外连接

select * from emp right join dept on emp.dept_id=dept.id;

Note:

内连接查询的是两张表的交集。
外连接查询的是两张表的交集+左（右）边的表。

自连接

一张表自己连接自己

select a.*,b.name as '领导' from emp a left join emp b on a.managerid=b.id;

联合查询

将多次查询的结果合并起来。

union（去重合并）
union all (不会去重复)

Note:

对于多张表的查询结果列数必须相同，且字段类型需要相同。
union会去重合并查询而union all 不会。

子查询

标量字查询（查询结果为单个值）
列子查询（查询结果是一列）
行子查询（查询结果为一行）

-- 行子查询
select * from emp where (salary,managerId) = (select salary,managerId from emp)

表子查询（查询结果为表格）

事务

事务的特性

A(Atomicity) 原子性    事务是不可拆分的最小单元。
C(Consistency)一致性    事务操作的数据要保证一致性。
I(Isolation)隔离性      隔离机制，并发情况下一个事务不会影响另一个事务的操作。
D(Durablity)持久性      事务一旦提交或者回滚，对数据的修改将是永久性的。

事务操作

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

并发事务问题

脏读 一个事务读到另一个事务未提交的事务
不可重复读 一个事务先后读取同一个数据，但是两次数据不一致
幻读 一个事务查询数据时没有数据，但是插入数据时又已经存在该数据了。

事务的隔离级别

Read uncommitted 读未提交 
Read committed 读已提交
Repeatable Read 可重复读（MySQL的默认隔离级别）
Serializable 序列化

读未提交允许脏读（一个事务可以读取到另一个未提交事务的更改）
读已提交解决脏读问题，允许不可重复读（一个事务中先后读取同一个数据不一致）
可重复读解决不可重复读问题，允许幻读（一个事务查询一条数据不存在，另一个事务插入新数据后，再次读取又读取到了数据）

查看事务的隔离级别

select @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ| SERIALIZABLE]

存储引擎

MySQL体系结构

在这里插入图片描述

存储引擎介绍

存储引擎基于表而不是数据库，因此也叫表类型。

查看当前数据库支持的存储引擎

show engines;

-- 指定存储引擎建表
create table my_myisam(
	id int,
	name varchar(10)
)engine=myisam;

create table my_memory(
	id int,
	name varchar(10)
)engine=memory;

存储引擎特点

InnoDB

MySQL 5.5之后，InnoDB为MySQL的默认存储引擎

特点

支持事务
支持外键
支持行级锁

文件

文件名为xxx.ibd，InnoDB引擎的每张表都会对应这样的一个表空间文件，用于存储表结构，索引和数据。取决于参数：innodb_file_per_table

-- 查看变量（独立表空间开关）
show variables like 'innodb_file_per_table';

逻辑存储结构

在这里插入图片描述

TableSpace 表空间

segment 段
- Extent 区（固定1M）
  - Page 页（固定16K）
    - Row 行

Note:

一个区有64页，每一页大小为16K。

MyISAM

MySQL早期默认的存储引擎,如果数据读和插入为主，删除和修改很少并且对事务的完整性和并发性要求不高，可使用。MongoDB取代

特点

不支持事务
不支持外键
速度较快

文件
xxx.MYD 存储数据
xxx.MYI 存储索引
xxx.sdi 表结构

Memory

由于数据存储在内存中，因此只能作为临时表或者缓存表使用。Redis取代

特点

内存存放
hash索引（默认）
文件
xxx.sdi：存储数据表结构

区别对比

在这里插入图片描述

索引

高效获取数据的数据结构

索引结构

索引结构	描述
B+Tree	MySQL的索引结构
Hash索引
R-tree索引	主要用于地理位置
Full-text	全文索引

在这里插入图片描述

二叉树

缺点：顺序插入时，会形成单向链表，查询性能大大降低，大数据下，层级较深，检索速度慢。

红黑树：大数据下层级较深。

B-Tree（多路平衡查找树）

在这里插入图片描述

B+Tree

在这里插入图片描述

只有叶子节点存储数据，且叶子节点形成单向链表。

MySQL对B+Tree进行了优化，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高了区间访问的性能。

附: 数据结构可视化

Hash结构

采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

特点

只能用于等值比较（=，in）,不支持范围查询（between,<,>,…）
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就行，效率高于B+Tree索引

InnoDB为什么选择B+Tree结构

相对于二叉树而言，层级更少，效率更高
相对于B-Tree而言，由于B-Tree非叶子节点也会存放数据，而存储结构中每一页的大小是固定的16K如果非叶子节点存放数据就会导致能存放的键值更少，也就意味着指针更少，所以相同数据量的情况下B-Tree的层级会更高，效率较低。
而对比Hash结构而言，由于Hash结构只能用于等值比较，所以B+Tree会更加适合。

索引分类

主键索引只能有一个 Primary
唯一索引可以有多个 Unique
常规索引可以有多个
全文索引可以有多个 FullText

在InnoDB中还可以分为两种：

聚集索引将数据存储和索引放到一起，索引结构的叶子节点存放了行数据，必须有且只有一个
二级索引将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键。

Note:

聚集索引规则：
如果存在主键，则主键为聚集索引。
如果主键不存在，则选取第一个唯一索引。
如果没有唯一索引，则InnoDB默认生成一个rowid作为隐藏的聚集索引。

索引语法

创建索引

create [UNIQUE|FULLTEXT] index index_name on table_name (index_col,index_col2...);

查看索引

show index from table_name;

删除索引

drop index index_name on table_name;

SQL性能分析

SQL执行频次

通过SQL执行频率查看SELECT | UPDATE | INSERT | DELETE到底哪个执行频次高。

show global status like 'Com_______';

慢查询日志

-- 查看慢查询开关
show variables like 'slow_query_log';

-- 设置my.cnf配置文件慢查询
slow_query_log = 1  
slow_query_log_file = /path/to/slow_query.log  
long_query_time = N  # N为指定的时间阈值，单位为秒，默认为10秒


-- SQL命令设置
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';
SET GLOBAL long_query_time = N; # N为指定的时间阈值

缺点：只有超过指定时间的慢查询才会记录。

profile详情

select @@have_profiling;

select @@profiling;

-- 默认是关闭的，开启需要输入
set profiling=1;

-- 查看当前会话所有的耗时情况
show profiles;

-- 查看具体某个查询耗时情况

show profile for query query_id;
show profile cpu for query query_id;

explain执行计划

在这里插入图片描述

explain各字段的含义

id
表示select 字句或者表操作顺序，如果id相同从上往下执行，如果id不同，则id较大的则先执行。
select_type
select 类型，常见的有SIMPLE,PRIMARY,UNION,SUBQUERY
table
partitions
type
null,system,const,eq_ref,ref,range,index,all性能由好到坏。

查询常量一般才有null
查询系统表才有system
查询使用唯一索引有const
非唯一索引有ref

possible_keys
key
key_len
ref
rows
filtered
Extra

在这里插入图片描述

索引使用

1.最左前缀法则

如果索引了多列（联合索引），进行查询时索引最左列必须存在，如果跳过某个列，那么后面的字段索引将会失效。

2.范围查询

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。

3.索引列运算

不要在索引列上进行运算操作，否则索引列失效。

4.字符串不加引号

字符串字段类型不加引号时，索引失效。

5.模糊查询

如果模糊匹配在尾部索引不会失效，但是如果在头部索引失效。

-- 索引失效
select * from tb_user where name like '%Java';

-- 索引生效
select * from tb_user where name like 'Java%';

6.or连接的条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到。

7.SQL提示

SQL提示，加入一些认为的提示达到优化目的。

在这里插入图片描述

8.覆盖索引

尽量使用覆盖索引（查询使用了索引，并且返回的列在索引中全部能找到），避免使用select *；

Note:

using index condition：查找使用了索引，但是需要回表查询数据。
using where ,using index：查找使用了索引，但是返回的列都在索引列中，不需要回表查询数据。

9.前缀索引

当字段为字符串（varchar，text等），创建索引往往会很大，占用大量的磁盘IO，影响查询效率，此时可以建立索引时只采用字段部分前缀。

create index idx_xxxx on table_name(column(n));

前缀长度

根据索引的选择性决定，索引的选择性值得是不重复的索引值和数据表总记录数的比值，索引选择性越高查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

-- 索引选择性的计算
select count(distinct email)/count(*) from tb_user;

-- 通过调整数据得到索引选择性
select count(distinct substring(email,1,9))/count(*) from tb_user;

索引设计原则

数据量较大，且查询频繁
常用于查询条件 where，group by ,order by的字段建立索引
尽量选择区分度高的列建立索引
如果是字符串字段，且字符串列较长，建立前缀索引
尽量使用联合索引，减少单列索引
控制索引的数量，索引越多，构成的维护代价也会较大，影响增删改的效率
如果索引列不能存储Null值，创建表时候应该约束Not null

SQL优化

1.插入优化

insert 优化

批量插入(500-1000条数据)
手动事务提交
大批量插入数据通过load指令

mysql -local-infile -u root -p

set global local_infile=1;

load data local infile 'root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

# MySQL插入优化-性能对比

2.主键优化

在InnoDB引擎中，数据是根据主键顺序存放的，这种存储方式的表称为索引组织表。

在这里插入图片描述

主键设计原则

尽量减少主键长度
输入数据时尽量选择顺序插入，使用Auto_increment自增主键
尽量不要使用uuid主键或者其他自然主键，如身份证号
业务操作尽量避免对主键的修改

3.order by 优化

using filesort:在排序缓冲区进行排序，所有不是通过索引返回的有序数据都是using filesort。
using index:通过索引返回有序数据（尽量优化为using index）

在这里插入图片描述

Note：需要使用覆盖索引，否则索引失效，覆盖索引即查询的数据包含在索引内。

在这里插入图片描述

如果需要一个字段升序，一个字段降序，则可以重新创建一个索引：

create index idx_price_stock_ad on tb_sku(price asc,stock desc);

在这里插入图片描述

4.group by优化

同上，尽量使用覆盖索引，且遵循最左前缀法则。

在这里插入图片描述

Note：

多字段分组时，比如上面品牌分组后继续按照分类分组。
where子句也可以满足最左前缀法则。

5.limit 优化

起始位置越往后效率越低。

覆盖索引+自查询方式优化。

select * from tb_user u,(select id from tb_user limit 9000000,10) a where a.id=u.id;

6.count优化

MyISAM引擎把一个表的总记录数存在磁盘中，因此count效率极高。
InnoDB比较麻烦需要一行一行记录读取计算。

count聚合函数，一行一行读取数据，如果不是NULL则进行累加。

count使用

count(*) 、count(1)、 count(字段)、count(主键)

在这里插入图片描述

7.update 优化

尽量根据索引字段更新（行级锁），如果不是索引字段会出现表锁，操作事务的时候容易造成阻塞。

Note:

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且索引不能失效，否则升级为表锁。

比如：

在这里插入图片描述

视图/存储过程/触发器

1.视图

虚拟存在的表，视图中的数据并不真实存在；视图依托表而存在，如果相关表不存在了，则使用视图会报错。

创建视图

create [or replace] view 视图名称 as select 语句 [with cascaded|local check option];

-- 栗子
create or replace view stu_v_1 as select id,name from student where id <=10;

Note：视图是基于表创建的，因此内部数据实际存储位置也在基表中，对视图中数据的操作同理。

由于视图是SELECT语句结果集，当语句存在条件时，对视图插入数据时候，会出现视图查询不到数据的情况。比如下表：

在这里插入图片描述

为了解决这个问题，MySQL提供了视图检查选项：with check option；包括两种：

CASCADED：级联检查（默认）
LOCAL：本地检查

具体使用

create view emp_v_1 as select name,age from emp where age>=20 with cascaded check option;

当存在另一个视图时，级联效果会联合另一个视图的条件作为检查。

create view emp_v_2 as select name,age from emp_v_1 where age <=30 with check option;

当插入数据时，不仅需要检查age<=30同时还需要检查级联的条件age>=20。
在这里插入图片描述

Note：CASCADE和LOCAL的区别

二者都会递归向上检查是否满足条件，CASCADE无论上一级是否定义了check option都会关联条件
而LOCAL则需要根据上一级是否定义了check option进行判断。

查询视图

-- 查看视图创建语句
show create view 视图名称;

-- 查看某个数据库下所有视图
select table_name from information_schema.tables where table_schema=数据库名称 and table_type='VIEW';

-- 查询视图数据
select * from 视图名称;

在这里插入图片描述

修改视图

-- 方式一
create [or replace] view 视图名称 as select 语句 [with cascaded|local check option];

-- 方式二
alter view 视图名称 as select 语句 [with cascaded|local check option];

删除视图

drop view [if exists] 视图名称

视图更新

在这里插入图片描述

2.存储过程

SQL语言层面代码的封装和重用。

创建


create procedure 存储过程名称(参数列表)
begin
	-- SQL语句
end;


-- 栗子
-- 定义SQL语句结束符号
delimiter //
create procedure p1()
begin
	select count(*) from student;
end//
-- 将控制台结束符修改回来 ;
delimiter ;

调用

call 存储过程名称;

查看存储过程

-- 查看某个数据库下的存储函数
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_SCHEMA  
FROM information_schema.ROUTINES  
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA='clcao';
-- 查询所有存储过程
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_SCHEMA  
FROM information_schema.ROUTINES  
WHERE ROUTINE_TYPE = 'PROCEDURE';

-- 查看创建语句
show create procedure p1;

删除

drop procedure if exists p1;

2.1变量

系统变量 MySQL系统提供的变量,属于服务层面，分为全局变量GLOABL和会话变量SESSION。

查看系统变量

show [SESSION|GLOBAL] variables;

-- 模糊匹配
show [SESSION|GLOBAL] variables like '';

-- 精确查找
select @@profiling;

设置系统变量

set [SESSION|GLOBAL] 变量名=xxx;

-- 栗子
set session autocommit = 0;    -- 关闭自动提交事务

用户定义变量 用户自定义的变量，使用的时候直接@变量名即可，其作用域是当前连接

赋值

set @myage=10
set @mysage :=10 -- 推荐用法，用于区分比较符号 = 

-- 一次赋值多个变量
select @myage :=10,@myhobby='java';

select 字段 into @变量名 from 表名;

使用

select @变量名

局部变量 需要declare声明，其作用域在BEGIN…END块。

delimiter //
create procedure p2()
BEGIN
	declare stu_count int default 0;
	select count(*) into stu_count from student;
	select stu_count;
END//
delimiter ;

2.2 if

语法

IF 条件 THEN

elseif 条件 then

else

end if


-- ---------------- 
delimiter //
create procedure p3()
BEGIN 
	declare score int default 58;
	declare result varchar(10);
	
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
	select result;
END //
delimiter ;

call p3;
drop procedure p3;

2.2 参数

IN 输入参数
OUT 输出参数
INOUT 即可作为输入也可作为输出参数

语法

create procedure p3([IN|OUT|INOUT] 参数名 参数类型)
BEGIN
	-- SQL语句
END

栗子

delimiter //
create procedure p3(in score int,out result varchar(10))
BEGIN 
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
END //
delimiter ;

call p3(68,@result);
select @result;

drop procedure p3;

-- 栗子2
delimiter //
create procedure p4(inout score double)
BEGIN
	set score := score*0.5;
END //
delimiter ;

set @score=78;
call p4(@score);
select @score;

2.3 case

语法

-- 语法1
case case_value
	when when_value1 then statement_1
	when when_value2 then statement_2
	else statement
END CASE;

-- 语法2
case 
	when search_condition1 then statement_1
	when search_condition2 then statement_2
	else statement
END CASE;

2.4 while

语法

while 条件 do
	-- SQL
end while

栗子

delimiter //
create procedure p7(in n int)
begin
	declare total int default 0;
	while n>0 do
		set total := total + n;
		set n := n-1;
	end while;
	select total;
end//

delimiter ;

call p7(100);

2.5 Repeat

语法

repeat
	-- SQL
	until 条件
end repeat

栗子

delimiter //
create procedure p8(in n int)
begin
	declare total int default 0;

	repeat
		set total := total + n;
		set n := n - 1;
	until n <=0
	end repeat;

	select total;
end//

delimiter ;

call p8(100);

2.6 loop

在这里插入图片描述

语法

[begin_label] loop
	-- SQL 逻辑
end loop [end_label]

-- 退出循环
LEAVE      -- 等价 break 直接退出循环
ITERATE    -- 等价 continue 直接进入下一次循环

栗子

delimiter //
create procedure p9(in n int)
BEGIN
	declare total int default 0;

	sum:loop
		if n<=0 then
			leave sum;
		end if;

		set total := total + n;
		set n := n-1;
	end loop sum;

END//

delimiter ;

call p9(100);

2.7 游标

在这里插入图片描述

create procedure p11(in uage int)
BEGIN
	declare u_cursor cursor for select name,profession from tb_user where age <= uage;
	declare uname varchar(100);
	declare upro varchar(100);
	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
		id int primary key auto_increment;
		name varchar(100);
		profession varchar(100)
	);

	-- 开启游标
	open u_cursor;
	-- 循环遍历游标
	while true do
		fetch u_cursor into uname,upro;
		insert into tb_user_pro values(null,uname,upro);
	end while;

	-- 关闭游标
	close u_cursor;
END

3. 存储函数

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

基本语法

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic...]
BEGIN
	-- SQL 语句
	RETURN ...;
END

characteristic说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句
READS SQL DATA ：包含读取数据的语句，但不包含写入数据的语句。

查看存储函数

-- 查看某个数据库下的存储函数
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_SCHEMA  
FROM information_schema.ROUTINES  
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_SCHEMA='clcao';

-- 查询所有存储函数
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_SCHEMA  
FROM information_schema.ROUTINES  
WHERE ROUTINE_TYPE = 'FUNCTION';

-- 查看创建语句
show create function fun1;
show function status like 'fun1';

使用

1.计算1到n的累加值

create function if not exists fun1(n int)  
returns int DETERMINISTIC  
begin  
    declare total int default 0;  
  
    while n > 0 do  
        set total := total + n;  
        set n := n - 1;  
    end while;    return total;  
end;  

-- 使用
select fun1(10);

4. 触发器

触发器是与表相关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。

这种特性可以帮助数据库完成数据的完整性，日志记录以及数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，目前仅支持行级触发器，不支持语句级触发。

触发器类型	NEW 和 OLD
INSERT型	NEW 表示将要或者已经新增的数据
UPDATE型	OLD 表示修改之前的数据，NEW 表示修改之后的数据
DELETE型	OLD 表示将要或者已经删除的数据

Note：

行级触发器，比如update影响五条数据，则会触发5次
语句级触发器，比如update影响五条数据，仅触发1次

语法

CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELTE
ON 表名 FOR EACH ROW  -- 行级触发器
BEGIN
	-- 触发器语句
END;

查看触发器

show triggers;

-- 通过表查看
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT  
FROM information_schema.TRIGGERS  
WHERE TRIGGER_SCHEMA = 'clcao';


-- 使用 mysqlshow 工具
mysqlshow --triggers 数据库名

删除触发器

DROP trigger 触发器名称

使用

创建日志记录表

-- 创建日志记录表
CREATE TABLE IF NOT EXISTS `user_logs` (  
    `id` INT(11) NOT NULL AUTO_INCREMENT,  
    `operation` VARCHAR(20) NOT NULL,  
    `operation_time` DATETIME NOT NULL,  
    `operation_id` INT(11) NOT NULL,  
    `operation_params` TEXT,  
    primary key (`id`)  
);

1.插入数据触发器

-- 创建触发器
create trigger user_logs_trigger  
    after insert on user  
    for each rowbegin  
    insert into user_logs(operation, operation_time, operation_id, operation_params)  
    values ('insert', now(), new.id, concat('{"name": "' , new.name , '", "age": ' , new.age || '}'));  
end;

2.更新数据触发器

create trigger tb_user_update_trigger  
    after update on user  
    for each rowbegin  
    insert into user_logs(operation, operation_time, operation_id, operation_params)  
    values ('update', now(), new.id, concat('修改的数据为: 原始 name =', old.name , ', age = ', old.age , '; 修改后 name = ', new.name , ', age = ', new.age));  
end;

3.删除数据触发器

create trigger tb_user_delete_trigger  
    after delete on user  
    for each rowbegin  
    insert into user_logs(operation, operation_time, operation_id, operation_params)  
    values ('delete', now(), OLD.id, concat('删除的数据为：name = ', old.name , ', age = ', old.age));  
end;

锁

协调多个进程并发访问某一资源的机制。

分类

全局锁
表级锁
行级锁

1. 全局锁

全局锁对整个数据库实例进行枷锁；典型场景是数据备份，对所有表进行锁定，保证一致性视图，保证数据的完整性。

Q：什么是数据一致性？

比如一个正在运行的数据库，存在商品表、订单表和库存表，当你进行备份的时候，如果没有加全局锁，那么当你备份好商品表，由于还有业务插入数据进来，导致后面尚未备份的订单表和库存表有新的数据，此时再备份，商品和订单就对应不上了！这就是数据不完整。

使用

一致性数据备份

-- mysql 命令行执行
flush tables with read lock;

-- shell 终端执行
mysqldump -uroot -p1234 clcao >clcao.sql

-- mysql 命令行执行
unlock tables;

-- InnoDB引擎不需要开启全局锁
mysqldump --single-transaction -uroot -pccl@2023 clcao > clcao.sql

Note：

加全局锁需要具备一定权限的，通常为root用户才行。
加锁会导致DML语句阻塞，也就是业务阻塞，推荐使用不需要全局锁的方式备份。

2. 表级锁

表级锁，每次锁住整个数据表，锁的粒度较大，发生锁冲突的概率最高，并发程度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

分类

表锁
元数据锁（Meta Data Lock MDL）
意向锁

2.1 表锁

分类

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

2.1.1 读锁

读锁允许自身和其他客户端读，但是不能写。

语法

-- 加锁
lock tables 表名 read

-- 释放锁
unlock tables / 客户端断开

在这里插入图片描述

2.1.2 写锁

写锁允许自身读和写，但是不允许其他客户端读和写，体现出来独占特性。

语法

-- 加锁
lock tables 表名 write

-- 释放锁
unlock tables / 客户端断开

在这里插入图片描述

2.2 元数据锁

在这里插入图片描述

Q：怎么理解上面的兼容和互斥？

元数据锁的共享指的是允许什么操作，比如对表进行select时候，会自动加上SHARED_READ类型的MDL锁，这个锁能干嘛？他允许你进行DML语句，比如继续SELECT或者UPDATE或者DELETE都可以；但是不允许进行DDL语句的修改表结构，也就是和EXCLUSIVE互斥的。

同样的，当你进行alter的时候，会自动加上EXCLUSIVE类型的MDL锁，这个锁能干嘛？他只允许当前客户端进行DML语句，不允许任何其他操作。

查看元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

使用

1.共享读写MDL锁，互斥DDL语句
在这里插入图片描述

2.排他MDL锁，互斥DML语句
在这里插入图片描述

由于MySQL 8.0之后的版本对DDL原子操作的支持（隐式自动提交），无法在事务中执行alter语句，然后查看元数据锁，就不演示alter语句排斥DML了。

Note：

MDL锁的目的是避免DML语句和DDL语句的冲突。
观察元数据锁需要在事务中执行语句，一旦事务提交，元数据锁就释放了。
元数据锁中的读写是锁的类型，区别于表中的读写锁的概念；可以理解为DML语句的细分，这里的共享读写都属于DML语句，DML语句之间不互斥，仅仅互斥DDL语句。

2.3 意向锁

意向锁（Intention Lock）为了避免DML在执行时，加的行锁和表锁之间的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行的加锁情况，使用意向锁来减少行锁的检查。

当执行SELECT … LOCK IN SHARE MODE语句时，加的是意向共享锁（IS）
当执行DML语句以及SELECT … FOR UPDATE时，加的是意向排他锁（IX）

在这里插入图片描述

查看意向锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

使用

1.意向共享锁（IS）
在这里插入图片描述

2.意向排他锁（IX）
在这里插入图片描述

Note：

意向锁的目的是为了优化表级锁和行锁之间冲突检查性能的
意向共享锁需要显示语句lock in share mode添加
意向排他锁除了显示语句select…for update，其他的DML语句会自动添加

3. 行级锁

每次操作锁住对应的行数据，锁定粒度最小，并发访问发生冲突的概率最小，并发效率最高。InnoDB基于索引组织数据的，行锁是通过索引上的索引项加锁来实现的，而不是对记录加的锁。

分类

行锁
间隙锁
临间锁

在这里插入图片描述

3.1 行锁

行锁（Record Lock）：锁定当个行记录的锁。

分类

共享锁（S）
排他锁（X）

在这里插入图片描述

查看锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

使用

1.共享锁（S）
在这里插入图片描述

2.排他锁（X）
在这里插入图片描述

3.非索引列检索数据升级为表锁
在这里插入图片描述

Note：

行锁一定要避免升级为表锁（将所有记录添加行级排他锁）
行锁分类等同表锁，只是一个锁的是整张表，一个锁的是行数据。

3.2 间隙锁\临键锁

在这里插入图片描述

使用

1.唯一索引间隙锁
在这里插入图片描述

2.非唯一索引临键锁
在这里插入图片描述

Note：

间隙锁的主要目的是为了解决幻读问题。
由于非唯一索引，前后都可能插入数据，因此除了后一个记录加间隙锁外，还需要将自身添加临键锁，锁住自己及之前的间隙，防止数据插入

InnoDB引擎

1. 逻辑存储结构

TableSpace > segment > Extent(1M) > Page(16K) > Row

在这里插入图片描述

2. 架构

在这里插入图片描述

图来源：MySQL Docs # InnoDB Architecture

其中左半边为内存架构，右半边为磁盘架构。

2.1 内存架构

主要包括：

缓冲池（Buffer Pool）
更改缓冲区（Change Buffer）
自适应哈希索引（Adaptive Hash Index）
日志缓冲区（Log Buffer）

2.1.1 缓冲池

缓冲池（Buffer Pool）是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作的时候，先操作缓冲池中的数据（如果没有则从磁盘加载），然后以一定频率刷新到磁盘上。（完全可以理解为Redis缓存的作用）

缓冲池以页（Page）为单位，底层根据链表结构管理页，根据页的状态分类：

free page：空闲页，从未使用过
clean page：被使用的页，数据没有被修改过
dirty page：脏页，被使用的页，数据被修改，其中数据和磁盘不一致

Q1：为什么需要缓冲池？

如果每次执行SQL操作时候都直接操作磁盘，会严重影响性能，通过设计缓冲区，针对已经缓存的数据可以直接操作；然后以一定的频次将缓冲区的数据进行IO写入磁盘，减少IO次数，提高性能。

Q2：什么是脏页？

进行增删改操作后，数据被修改，但是还没有刷新到磁盘的数据页，这样的页称为脏页。

查看Buffer Pool

SHOW ENGINE INNODB STATUS\G;

-- 查看缓冲池大小,单位byte
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Note：缓冲池通过LRU（最近最少使用）策略淘汰页来管理。

2.1.2 更改缓冲区

更改缓冲区（Change Buffer）主要针对二级索引 ，当进行DML语句时，如果Buffer Pool中没有数据，并不会直接从磁盘加载这些数据，而是将操作记录在Change Buffer中，之后在IO空闲时，后台线程周期的执行DML语句，然后合并到Buffer Pool中，最后Buffer Pool一定的周期刷新到磁盘中。

此参数用于设置更改缓冲区的大小：innodb_change_buffer_max_size

在这里插入图片描述

Q1：Change Buffer存在的意义是什么？不可以直接在缓冲池操作吗？

这是因为对于非唯一二级索引，在维护二级索引上，由于插入或者删除或者修改数据涉及的key是随机的，他并不像聚集索引那样有序或者部分有序，这意味着这样的变更需要更加昂贵的IO，引入Change Buffer可以让这样的IO操作，均摊到空闲时间，从而避免IO过高导致性能下降。

Q2：如果没有立即执行，那我进行SELECT查询时候如何保证我获取到最新数据？

在进行其他操作时候会检查Change Buffer是否有需要进行的操作，如果涉及则立即执行并合并到Buffer Pool，然后查询时候直接从Buffer Pool获取数据。

在这里插入图片描述

2.1.3 自适应哈希索引

自适应哈希索引（Adaptive Hash Index）用于优化Buffer Pool数据的查询。InnoDB引擎会监控对表上索引页的查询，如果发现hash索引可以提升速度，则建立hash索引。

自适应哈希索引，是系统根据情况自动完成的，由参数：innodb_adaptive_hash_index 控制开关（默认开启）。

Q：自适应哈希索引如何提升效率的？

自适应哈希索引本身是为了优化Buffer Pool中数据页检索效率的，相对于本地而言，由于直接操作内存自然会比较快，同时对于一些热点页，比如经常查询的数据，会自动升级为哈希索引，而哈希索引的特点就是一次直接命中，因此通过自适应哈希索引可以有效的提高热点数据的操作。

2.1.4 日志缓冲区

日志缓冲区（Log Buffer）用来保存要写入磁盘中的log日志数据（redo log、undo log），默认大小16MB。

参数：

innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘的时机
- 0：日志每秒刷入到磁盘
- 1：日志每次事务提交时候刷新到磁盘
- 2：日志每次事务提交刷新到磁盘，并且每秒刷新到磁盘

2.2 磁盘架构

主要包括：

系统表空间（System Tablespace）
每个表的文件表空间（File-Per-Table Tablespaces）
通用表空间（General Tablespaces）
撤销表空间（Undo Tablespaces）
临时表空间（Temporary Tablespaces）
双写缓冲区（Doublewrite Buffer files）
重做日志（Redo log）

2.2.1 系统表空间

系统表空间是更改缓冲区的存储区域。如果表是系统表空间，他也可能包含表和索引数据。

参数：

innodb_data_file_path

2.2.2 每个表的文件表空间

每个表的文件表空间包含单个InnoDB的索引结构和数据，并保存在文件系统的单个文件中，以表名.ibd命名。

参数：

innodb_file_per_table

在这里插入图片描述

2.2.3 通用表空间

需要使用语法CREATE TABLESPACE创建通用表空间，在创建表时可以指定表空间。

语法

-- 创建通用表空间
CREATE TABLESPACE 表空间名称 add datafile 表空间文件名称 engine=innodb;

-- 创建表指定表空间
CREATE TABLE tb_tablespace(
	id int primary key auto_increment,
	name varchar(10)
)engine=innodb tablespace 表空间名称

在这里插入图片描述

2.2.4 撤销表空间

MySQL实例在初始化的时候会自动创建两个默认的undo表空间（初始大小16MB），用于存储undo log日志。

在这里插入图片描述

2.2.5 临时表空间

InnoDB使用会话临时表空间和全局临时表空间用于存储用户创建的临时表等数据。

2.2.6 双写缓冲区

Buffer Pool在将数据写入磁盘前，先将数据页写入双写缓冲区文件，便于出现故障时候恢复数据。

参数：

innodb_doublewrite：控制是否启用双写缓冲区，如果关心性能而不是数据完整性可关闭

两个文件：

#ib_16384_0.dblwr
#ib_16384_1.dblwr

在这里插入图片描述

Q：如果数据页写入双写缓冲区时发生故障，那双写缓冲区不是没有意义了吗？

的确，双写缓冲区也是磁盘文件，意味着即使先写入双写缓冲区同样可能发生故障；但是由于双写缓冲区在磁盘的位置固定，且写入的数据是顺序写入的，通常发生故障的概率会更低，因此双写区域的存在一定程度上可以帮助故障异常恢复数据，确保数据完整性。

2.2.7 重做日志

重做日志是基于磁盘的数据结构，用于崩溃恢复期间，以更正不完整事务写入的数据。

在这里插入图片描述

重做日志可用来实现事务的持久性。日志由两部分组成：重做日志缓冲（redo log buffer）和重做日志文件（redo log），前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息保存到该日志中，用于刷新脏页到磁盘时发生错误时，可以进行数据恢复。

Note：MySQL 8.0之前，日志文件为ib_logfile0和ib_logfile1。redo log是物理记录，即记录那些字节修改，而不是逻辑记录，即记录Insert、Update、Delete语句。

2.2.8 撤销日志

撤销日志（undo log）是与单个读写事务关联的撤销日志记录的集合，主要用于回滚事务。

2.3 后台线程

在这里插入图片描述

查看后台线程

show engine innodb status\G;

在这里插入图片描述

3. 事务原理

事务指的是一组操作要么全部执行成功要么全部执行失败。

事务的特性：

原子性（Automicity）
一致性（Consistency）
持久性（Durability）
隔离性（Isolation）

通过redo log 和undo log保证事务的原子性、一致性和持久性。

通过锁和MVCC保证事务的个理性。

3.1 redo log

redo log用于保证事务的持久性，重做日志是InnoDB引擎独有的，它让MySQL具备了崩溃恢复能力。

在这里插入图片描述

当开启一个事务的时候，如果事务中的DML语句涉及二级索引，则会在Change Buffer中记录变更，之后一定的时机将变更合并到Buffer Pool，在Buffer Pool进行的变更操作，都会被物理记录在日志缓冲池中，至于什么时候将日志缓冲池的日志顺序写入磁盘文件，取决于刷盘策略。

通过redo log即便在数据页写入磁盘出现故障，MySQL也能够根据redo log恢复数据。

Q1：没有redo log不是一样可以持久化数据到磁盘吗？

从最终结果看，只要数据保存到磁盘中，我们都说完成了持久化；但由于日志的追加写入比起直接随机写入表空间文件要稳定的多，当服务器宕机时候，依旧可以通过redo log完成数据恢复，从这层意义上，数据只要写入redo log文件记录了变更，就完成了数据的持久化。

Q2：日志缓冲区的刷盘策略是怎样的？

日志刷新到磁盘并不是提交事务就会刷新的，存在下面几种情况：

事务提交时，取决于设置的参数：innodb_flush_log_at_trx_commit，默认时1，表示事务提交时刷新。
当日志缓冲区使用大概一半的时候，会刷新缓冲区日志到重做日志文件中。
检查点（Check Point），当缓冲区（Buffer Pool）刷新脏页到表空间文件时，同时会刷新日志缓冲区数据到重做日志文件。
正常关闭服务器的时候，会刷新数据到重做日志文件。

3.2 undo log

undo log用于保证事务的原子性。撤销日志的作用：提供回滚和MVCC（多版本控制）。

不同于redo log，undo log记录的是逻辑结构，也就是说当执行delete语句时候，对应的undo log 记录一条insert 语句（反向恢复数据），当执行rollback回滚操作的时候，就可以从undo log进行数据回滚。

undo log 的销毁：事务提交时，并不会直接删除undo log，因为它还需要用于MVCC。

undo log 的存储：undo log采用段的方式进行管理和存储，内部包括1024个undo log segment。

4. MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。它是一种并发控制的方法，用于提高数据库的并发性能和数据一致性。

4.1 前置概念

4.1.1 当前读

读取的记录是最新版本，读取的时候还要保证其他并发事务不能修改当前记录，会对当前读取的记录加锁。日常操作包括：

select ... lock in share mode,

select ... for update，

update,

insert,

delete

在这里插入图片描述

4.1.2 快照读

简单的Select语句就是快照读，快照读读取的是记录的可见版本，有可能是历史版本，不加锁的非阻塞读。

不同的事务隔离级别：

Read Committed：每次select都是快照读
Repeatable Read：只有第一次select是快照读
Serializable：快照读会退化为当前读

4.1.3 MVCC

多版本并发控制，指维护一个数据的多个版本，比如快照读，就是一个数据的版本，这使得读写操作没有冲突。MVCC的具体实现除了快照读，还依赖数据库记录中的三个隐式字段、undo log日志和readView。

4.2 实现原理

4.2.1 记录中的隐藏字段

在这里插入图片描述

通过ibd2sdi工具可查看表空间文件结构隐藏字段

ibd2sdi xxx.ibd

在这里插入图片描述

4.2.2 undo log

回滚日志，当执行insert、update、delete的时候产生的便于数据回滚的日志。

当执行insert的时候，只在回滚的时候需要，事务提交后可立即删除undo log 日志。

而执行update和delete的时候，产生的undo log日志不仅在回滚时需要，在快照读也需要，不会立即删除。

undo log 版本链

假如并发时候，有4个事务同时操作一条记录，那么undo log日志就需要维护这条记录的4个版本，而每个版本的隐藏字段：DB_ROLL_PTR，就是指向上一个版本的指针，由此形成一个链表就是undo log版本链。

在这里插入图片描述

4.2.3 readView

ReadView会记录当前活跃的事务（即未提交的事务）。

在这里插入图片描述

Q：怎么理解ReadView？

可以理解ReadView就是一个包含上面四个核心字段信息的对象，什么时候创建ReadView？在读已提交的隔离级别下，每次select都会创建，创建的时候就会获取当前时间点未提交的事务ID，在默认可重复度的隔离级别下，第一次select时候创建

在这里插入图片描述

Q：怎么理解上面的图示？

存在5个并发事务，在事务执行期间会生成一个undo log版本链。

那么在事务5的时候select便会创建一个ReadView，这个时候未提交的事务就有345。

那么问题来了，这个时候的select应该读取undo log版本链中的哪个版本的数据？

按照规则，我们依次从版本链的头节点即当前记录开始计算，当前记录的隐藏字段：DB_TRX_ID=4，即trx_id=4，他不是当前事务（创建ReadView的事务5），因此条件1️⃣不满足；

trx_id=4 < min_trx_id = 3吗？不满足

trx_id=4 > max_trx_id = 6吗？不满足

min_trx_id =3 <= trx_id=4 <= max_trx_id=6满足，但是他在未提交的事务中！意味着当前数据（即第一条记录）这个版本不满足上面的条件，因此不会读取这个版本的数据！

然后顺着版本链，比对下面的版本，以此类推，最后找到最开始的版本，也就是读取到的是已提交事务的修改后的版本，未提交的事务版本不可读取！

日志

错误日志

用于记录MySQL服务器启动和停止时候发生严重错误时的信息，当数据库无法正常使用时，查看此日志。

查看日志位置

show variables like '%log_err%';

在这里插入图片描述

二进制日志

二进制日志（binlog）记录了所有DDL语句和DML语句，不包括查询语句（SELECT,SHOW）。

作用：数据恢复和主从复制。

查看日志位置

show variables like '%log_bin%';

包括如下日志格式
在这里插入图片描述

show variables like '%binlog_format%';

查看二进制日志文件

sudo mysqlbinlog -v data/binlog.000051 | tail -n 20

在这里插入图片描述

删除日志

在这里插入图片描述

-- 默认30天即 259200 秒
show variables like '%binlog_expire_logs_seconds%';

查询日志

查询日志记录了客户端所有的查询操作，默认不开启。

查看设置

show variables like '%general%';

在这里插入图片描述

慢查询日志

慢查询日志记录了所有执行时间超过long_query_time设置值的SQL语句，默认未开启，开启默认long_query_time=10，最小可设置0，精度可到微秒。

在这里插入图片描述

演示

-- 开启慢查询日志
set @@global.slow_query_log=1;

-- 设置慢查询时间

set @@session.long_query_time=2;

在这里插入图片描述

默认情况下，不会记录管理语句，也不会记录由于不使用索引进行查找的语句。

-- 开启记录管理语句
set log_slow_admin_statements=1;

-- 开启记录不使用索引的慢查询语句

set log_queries_not_using_indexes=1;

在这里插入图片描述

主从复制

主从复制是指主数据库将DDL语句和DML语句通过二进制日志传到从数据库，然后从数据库根据二进制日志重新执行（重做），从而使得从库和主库的数据保持一致。

MySQL支持主库同时向多台从库复制二进制文件，同时从库也可以作为主库传输二进制文件实现链状复制。

主要特点：

主库出现问题，可以快速切换从库提供服务。
实现读写分离，降低主库压力。
可以从从库执行备份，避免备份时候影响主库运行。

原理

在这里插入图片描述

主要操作都集中在从库的两个线程：IOthread负责读取二进制日志文件写入中继日志，SQLthread负责将中继日志重写入自身数据库。

搭建

主从搭建的前提是具备2个及以上的MySQL数据库实例，这里采用docker只需要修改端口即轻松实现。

环境

启动主库MySQL实例

docker run \
-p 3306:3306 \
--name mysql_master \
-v /etc/docker/mysql/master/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
--privileged \
-d \
mysql

启动从库MySQL实例

docker run \
-p 3307:3307 \
--name mysql_slave \
-v /etc/docker/mysql/slave/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
--privileged \
-d \
mysql

在这里插入图片描述

主库配置

1. 添加配置

MySQL命令行配置

-- 默认分配就是1，所以这里可以不配置也行
SET GLOABL server_id=1

-- 配置读写
SET GLOABL read_only=0

也可以通过修改配置文件，根据挂载目录/etc/docker/mysql/master/conf.d在宿主机添加配置文件。

[mysqld]
server-id=1

read-only=0

在这里插入图片描述

Note：配置文件需要重启，即docker restart mysql_master。建议通过配置文件配置，因为命令行配置重启服务后将失效（未持久化配置）。

2. 创建用户

从库通过此用户连接主库数据库然后读取二进制日志。

CREATE USER 'replica'@'%' IDENTIFIED BY 'Root@123456';

-- 分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

3. 获取二进制日志坐标

在这里插入图片描述

二进制日志记录的是所有的DDL和DML语句，为了保证主从数据的一致性，我们通过获取主库当前二进制日志文件的最后位置，让从库从这个位置开始读，保证后边的数据都可以复制；对于之前的数据，我们需要先备份出来然后倒入从库。

获取主库坐标

show master status\G;

在这里插入图片描述

记住文件名（File）和坐标点（Position）用于从库配置。

Q：为什么从库要这么配置，直接读取日志然后重做不好吗？

由于二进制日志文件不一定从最开始的数据开始（数据库刚安装），并且二进制日志文件存在多个，换句话说，二进制日志文件不等于主库的数据，因此需要基于当前日志文件以及当前坐标点开始推送事件给从库，让他从这个节点开始复制DDL和DML语句同步数据。

从库配置

1. 添加配置

MySQL命令行配置

-- 可以任意数字0~2^32-1;必须保证和其他实例区分
SET GLOABL server_id=2

-- 配置从库只读
SET GLOABL read_only=1

同理，推荐配置文件方式持久化配置。在宿主机目录/etc/docker/mysql/slave/conf.d添加配置文件。

[mysqld]
server-id=2

read-only=1

在这里插入图片描述

2. 导入备份数据

主库备份

mysqldump -uroot -proot --databases clcao > /tmp/clcaodb.sql

docker复制主库数据到从库

# 从主库复制文件到主机
docker cp mysql_master:/tmp/clcaodb.sql /tmp/

# 从主机复制文件到从库
docker cp /tmp/clcaodb.sql mysql_slave:/tmp/

从库恢复

mysql -uroot -proot < /tmp/clcaodb.sql

3. 连接主库

docker查看主库IP

docker inspect mysql_master | grep 'Networks' -A 15;

在这里插入图片描述

连接主库

-- 配置主库IP、用户名、密码以及二进制日志文件坐标点
CHANGE REPLICATION SOURCE TO SOURCE_HOST='172.17.0.2',SOURCE_USER='replica',SOURCE_PASSWORD='Root@123456',SOURCE_LOG_FILE='binlog.000003',SOURCE_LOG_POS=1407;

4. 开启复制

start replica;

5. 检查状态

查看状态

show replica status\G;

在这里插入图片描述

重置从库配置

RESET SLAVE ALL;

分库分表

当数据越来越大的时候，单数据库存储主要面临两个问题：

IO瓶颈：当热点数据越来越多，缓冲池Buffer Pool无法满足，就会产生大量的IO，效率较低；当请求数据过多时候，带宽不够还会存在网络瓶颈。
CPU瓶颈：排序、分组、连接查询等SQL会消耗大量CPU资源。

这个时候就需要增加服务器存储数据了。

分库分表的核心在于将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题。

拆分策略

按照粒度分拆库和拆表，按照维度分水平和垂直。

所以存在四种策略：

垂直分库
垂直分表
水平分库
水平分表

可以想象一张表（二维数组），垂直分就是竖着切一刀，所以垂直分表就会一张表把多个字段拆分为几张表的字段，数据量是一致的。而垂直分库则是一个数据库分散存储多张表。水平则是横着切一刀，数据进行分散，是按照行记录拆的。

垂直分库

假如单数据库db存在表tb1、tb2、tb3，将他垂直分库到两个服务器就是：服务器1的db数据库存储tb1这张表，服务器2的db数据库存储tb2、tb3这两张表。

垂直分表

按照字段粒度，假如单数据库db存在表tb，字段有id,name,age，将他垂直分表到两个服务器就是：服务器1的表tb字段为id,name，服务器2的表tb字段为id,age；这两个服务器根据主键连接起来，合起来组成完整数据。

水平分库

假如存在单数据库db存在表tb1、tb2、tb3，每张表存在3条记录，将他水平分库到两个服务器就是：服务器1的db数据库存储三张表，但是每张表都只存储1条记录，服务器2则存储另外2条记录。

水平分表

假如存在一张表tb存在3条记录，将他水平分表到两个服务器就是：服务器1存储1条记录，服务器2存储另外2条记录。

实现技术

在这里插入图片描述

shardingJDBC

基于AOP原理，需要侵入代码对执行的SQL进行拦截、解析、改写和路由处理，只支持Java语言，性能较高。

MyCat

分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及shardingJDBC。

参考

[1]：B站视频# MySQL数据库入门到精通
[2]：MySQL文档# 15.1 Data Definition Statements
[3]：MySQL文档# 15.2 Data Manipulation Statements
[4]：MySQL文档# 17.4 InnoDB Architecture
[5]：MySQL文档# Chapter 19 Replication
[6]：文章 # MySQL三大日志(binlog、redo log和undo log)详解