MySQL

基础

MySQL概述

数据库是存储数据的仓库，数据是有组织的进行存储。称为：DataBase（DB）。

数据库管理系统是操纵和管理数据库的大型软件。称为：Database Management System（DBMS）。

SQL是操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。称为：Structured Query Language（SQL）。

利用 services.msc 命令可以启动停止 MySQL。

也可以使用net start mysql80启动，使用net stop mysql80 停止。

客户端连接：

1. MySQL 提供的客户端命令行工具。
2. 使用系统自带的命令行工具执行指令。mysql -u root -p

数据模型

客户端利用 DBMS 操纵多个数据库，每个数据库中利用表来维护数据，一个数据库中包含了多个表结构。

关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。使用表结构存储，格式统一，便于维护。使用 SQL 语言操作，标准统一，使用方便。

SQL

SQL 语句不区分大小写，关键字建议使用大写。

注释：

• 单行注释：– 注释内容 或 # 注释内容（MySQL特有）
• 多行注释：/* 注释内容 */

SQL 分类：

• DDL：数据定义（Definition）语言，用来定义数据库对象（数据库，表，字段）。
• DML：数据操作（Manipulation）语言，用来对数据库表中的数据进行增删改。
• DQL：数据查询（Query）语言，用来查询数据库中表的记录。
• DCL：数据控制（Control）语言，用来创建数据库用户、控制数据库的访问权限。

DDL

DDL：数据定义（Definition）语言，用来定义数据库对象（数据库，表，字段）。

数据库操作相关：

• 查询所有数据库：SHOW DATABASES;

• 查询当前数据库：SELECT DATABASE();

• 创建数据库：CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAUL CHARSET 字符集 ] [ COLIATE 排序规则];

• 删除数据库：DROP DATABASE [ IF EXISTS ] 数据库名;

• 使用数据库：USE 数据库名;

表结构操作相关：

• 查询当前数据库所有表：SHOW TABLES;
• 查询表结构：DESC 表名;
• 查询指定表的建表结构：SHOW CREATE TABLE 表名;
• 创建表结构：[…]为可选参数，最后一个字段后面没有逗号。

# 格式
CREATE TABLE 表名 (
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	...
	字段n 字段n类型[COMMENT 字段n注释]
) [COMMENT 表注释];

# 示例
create table tb_user (
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

• 删除表：DROP TABLE [IF EXISTS] 表名;
• 删除指定表，并重新创建该表：TRUNCATE TABLE 表名;
• 添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
• 修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
• 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
• 修改表名：ALTER TABLE 表名 RENAME TO 新表名;
• 删除字段：ALTER TABLE 表名 DROP 字段名;

字段类型：

• 数值类型：tinyint、smallint、mediumint、int、bigint、float、double、decimal（unsigned）。

  注意：使用 double 的时候需要指定一共几位数字和小数点后几位，比如：double(4,1)表示最多到4位数字（100.0），小数点后精确到1位。

• 字符串类型：tinyblob、blob、mediumblob、longblob 表示二进制形式的文本数据（二进制数据性能不高且不方便管理，一般会使用专门的文件服务器管理）；

  tinytext、text、mediumtext、longtext 表示文本数据；

  char 表示定长字符串、varchar 表示变长字符串。（使用 char 和 varchar 的时候需要指定字符串长度，例如：varchar(10)，多余的位置利用空格补齐，varchar 的性能较 char 低一点，因为需要根据实际使用计算空间）

• 日期时间类型：date（YYYY - MM - DD，日期值）；

  time（HH : MM : SS，时间值或者持续时间）；

  year（YYYY，年份值）；

  datetime（YYYY - MM - DD HH : MM : SS，混合日期和时间值）；

  timestamp（YYYY - MM - DD HH : MM : SS，混合日期和时间值，时间戳）。

DML

为书写方便，从此往下的关键字用小写字母书写。

DML：数据操作（Manipulation）语言，用来对数据库表中的数据进行增删改。

• 添加数据：insert into 表名(字段名1, 字段名2, ...) values(值1, 值2, ...);

• 给全部字段添加数据：insert into 表名 values(值1, 值2, ...);

• 批量添加数据：（插入数据时，指定的字段顺序需要和值的顺序一致）

  insert into 表名(字段名1, 字段名2, ...) values(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...)

  insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

insert into user(id, name, age) values(1, '小明', 18); # 指定字段名插入

insert into user(id, name, age) values(2, '小红', 19);

insert into user values(3, '小刚', 20); # 全部字段名插入

# 指定字段名批量插入
insert into user(id, name, age) values(4, 'test0', 21), (5, 'test1', 22), (6, 'test2', 23);

# 全部字段名批量插入
insert into user values(7, 'test3', 21), (8, 'test4', 22), (9, 'test5', 23);

select * from user; # 查询表结构

修改数据：update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

where 条件可以没有，如果没有这个where 条件，则是修改整张表的数据。

update user set name = 'itheima' where id = 1; # 把id = 1的字段的name修改成itheima

update user set age = 1 where name = '小红'; # 把name = '小红'字段的age修改为1

update user set name = 'test_name'; # 把整张表的name改成test_name

删除数据：delete from 表名 [where 条件]

delete from user where age = 18; # 删掉表中所有age = 18的数据

delete from user;  # 删掉表中的所有数据

# 多表连接时的删除语句
delete a
from 
	tablename as a
left join 
	temp_name as b
on 
	a.ID = b.ID
where 
	b.ID is not null

小结：

insert into user values (0, 'test_name0', 18, '2023-11-18'),
                        (1, 'test_name1', 19, '2023-11-19'),
                        (2, 'test_name2', 20, '2023-11-17');
# 添加上述数据

update user set date = '2023-11-18'; # 修改整张表的日期为2023-11-18

update user set name = 'test_name' where age = 18; # 把age = 18的name修改为test_name

delete from user where id = 0; # 把id = 0的字段全部删掉

insert into user (id, name) values (0, 'test_name0'); # 添加字段，但只添加id和name

update user set age = 18, date = '2023-11-18' where name = 'test_name0'; #把test_name0的数据改回来

DQL

数据查询（Query）语言，用来查询数据库中表的记录。

正常使用时，查询的频次要远大于增删改。

基础查询：

• 基本查询：select 字段1, 字段2, 字段3... from 表名;

  ​ select * from 表名;

• 设置别名：select 字段1[as 别名1], 字段2[as 别名2], 字段3[as 别名3]... from 表名;

• 去除重复记录：select distinct 字段列表 from 表名;（要去重哪个字段就在哪个字段前面加 distinct）

insert into user values (3, 'test_name3', 19, '2023-11-28'),
                        (4, 'test_name4', 20, '2023-11-28'),
                        (5, 'test_name5', 21, '2023-11-28');

select name from user; # 查询user表当中的所有name

select id, name from user; # 查询user表中的id和其对应的name

select * from user; # 查询user当中的所有数据

select name as '名字', id as '编号' from user; # 起别名的查询, as 可以省略

select id, age from user;

select distinct age from user;  # 去重的查询

条件查询：

条件查询：select 字段列表 from 表名 where 条件列表;

条件列表可以有如下：

比较运算符	功能
>, >=, <, <=, =	比较运算
<> 或 !=	不等于
between A and B	在某个范围内 [A, B]
in(...)	在 in 之后的列表中的值，多选一
like 占位符	模糊匹配（_匹配单个字符，%匹配任意字符）
is null	是 null

逻辑运算符	功能
and 或 &&	并且
`or 或
not 或 !	非

select * from user where age >= 19; #查询age<=19的数据

select * from user where date = '2023-1-1'; #查询date为2023-1-1的数据，注意等号=

select * from user where date is null; # 查询date为null值的数据

select * from user where date is not null; # 查询date不为null值的数据

select * from user where age between 19 and 20; # 查询age在[19, 20]区间内的数据

select id, name, age from user where age in(18,19,20); #in语法，查询满足18,19,20的年龄数据

select id, name, age from user where name like 'test_%'; # 模糊匹配，匹配name前缀为test_的数据

聚合函数：

所谓聚合函数，就是将一列数据作为一个整体，进行纵向计算。

所有的聚合函数是不会对 null 值进行计算的。

常见聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：select 聚合函数(字段列表) from 表名;

select count(age) from user; # 统计年龄字段的总数量, null值不会计入到总数中

select avg(age) from user; # 统计所有数据的平均年龄

select max(age) from user; # 统计所有数据中的最大年龄

select min(age) from user; # 统计所有数据中的最小年龄

select sum(age) from user; # 统计所有年龄的总和

select sum(age) from user where id = 1; # 统计所有id = 1的年龄的总和

分组查询：

分组查询一般和聚合函数一起使用。一般来讲，你按照什么分组，你就查询什么东西。

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

where 和 having 的区别：

• 执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
• 判断条件不同：where 不能对聚合函数进行判断，但是 having 可以。

insert into user values (0, 'testName0', 18, '2023-11-28', '湖南长沙'),
                        (1, 'testName1', 19, '2023-11-28', '湖南娄底'),
                        (2, 'testName2', 20, '2023-11-28', '湖南怀化'),
                        (3, 'testName3', 19, '2023-11-28', '湖南长沙'),
                        (4, 'testName4', 21, '2023-11-28', '湖南郴州'),
                        (5, 'testName5', 22, '2023-11-28', '湖南长沙'),
                        (6, 'testName6', 17, '2023-11-28', '湖南郴州'),
                        (7, 'testName7', 21, '2023-11-28', '湖南长沙'),
                        (8, 'testName8', 19, '2023-11-28', '湖南衡阳'),
                        (9, 'testName9', 20, '2023-11-28', '湖南岳阳');

select age, count(age) from user group by age; # 一般按照什么分组，就查什么

# 查询age在[18, 20]间的字段地址
select address, count(*) from user where age between 18 and 20 group by address;

# 在分好的组中查询address总数>=2的数据
select address, count(*) as address_count from user where age >= 18 && age <= 20 group by address having address_count >= 2;

排序查询：

语法：select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式：ASC（升序、默认值），DESC（降序）

如果是多字段排序，则会先按照第一个字段的排序方式进行排序，当第一个字段值相同的时候，才会根据第二个字段进行排序。

select id, name, age from user order by age DESC, id; # 年龄降序排列，如果年龄相同，则按照id排列

select id, name, age, date from user order by date, age; # 日期升序排列，如果日期相同，则按照年龄升序排列

分页查询：

语法：select 字段列表 from 表名 limit 起始索引, 查询记录数;

注意：

• 起始索引是从0开始的，起始索引(从0开始) = （查询页码(从1开始) - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略。

# 每一页有3条数据
select * from user limit 0, 3; # 查询第一页的数据

select * from user limit 6, 3; # 查询第三页的数据

DQL语句的编写和执行顺序：

DQL 语句的编写顺序：select、from、where、group by、having、order by、limit。

DQL 语句的执行顺序：from、where、group by、having、select、order by、limit。

书写别名的时候需要格外注意执行顺序，否则可能导致编译不通过！

关于返回 null 的问题

leetcode 只出现一次的最大数字

先上题解：传送门

这里给出最后的表格：

	sum	avg	max	min	count	ifnull	select	limit	窗口函数
空表格	null	null	null	null	0	空	（from）空；（\）null	空	null
表格值为null	null	null	null	null	0	\	null	null	null

故可以利用聚合函数或者不带 from 的 select 返回 null 值。

DCL

数据控制（Control）语言，用来创建数据库用户、控制数据库的访问权限。

管理用户：

• 查询用户：use mysql; select * from user;
• 创建用户：create user '用户名'@'主机名' identified by '密码';
• 修改用户密码：alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
• 删除用户：drop user '用户名'@'主机名';

# 创建一个用户itcast, 可以在本主机中访问数据库，密码是123456
create user 'itcast'@'localhost' identified by '123456';

# 创建一个用户heima, 可以在任意主机访问该数据库，密码123456
create user 'heima'@'%' identified by '123456';

# 修改用户haima的访问密码为1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

# 删除itcast
drop user 'itcast'@'localhost';

权限控制：

MySQL 中定义了很多种权限，但是常用的权限有以下几种：

权限	说明
all, all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库、表、视图
create	创建数据库、表

• 查询权限：show grants for '用户名'@'主机名';
• 授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
• 撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

create user 'testUser'@'localhost' identified by '1234';

show grants for 'testUser'@'localhost'; # 查询testUser的权限

# 给testUser这个用户授予访问itcast所有表的所有权限
grant all on itcast.* to 'testUser'@'localhost';

# 撤销testUser的所有权限
revoke all on itcast.* from 'testUser'@'localhost';

函数

字符串函数

MySQL 内置了很多字符串函数，其中比较常用的有：

函数	功能
concat(s1, s2, ..., sn)	字符串拼接
lower(str)	字符串转成小写
upper(str)	字符串转成大写
lpad(str, n, pad)	左填充，用 pad 对 str 的左边进行填充，使得填充后达到 n 个字符串长度
rpad(str, n, pad)	右填充，用 pad 对 str 的右边进行填充，使得填充后达到 n 个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str, start, len)	返回从字符串 str 从 start 位置（从1开始数）起的 len 个长度的字符串

select concat('hello', 'world'); # 字符串拼接

select lower('HELLO WORLD'); # 转为小写

select upper('hello world'); # 转为大写

select lpad('01', 7, 'abc'); # 左填充

select rpad('02', 8, 'abc'); # 右填充

select trim('      hello world         '); # 去掉前后的空格

select substring('hello world', 1, 4); # 截取字符串

# 使用字符串函数对数据进行更新
update te_user set name = substring(name, 5); # 全部人的名字进行截取

update te_user set name = lpad(name, 9, 'test'); # 使用test字符串对name进行左填充

数值函数

常见的数值函数如下：

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x, y)	返回 x / y 的模
rand()	返回 0 - 1 内的随机数
round(x, y)	求参数 x 的四舍五入，保留 y 位小数

select ceil(1.5); # 向上取整

select floor(1.5); # 向下取整

select mod(6, 4); # 取模运算

select rand(); # 随机数

select round(1.54232, 4); # 四舍五入

select round(1.54532, 2); # 四舍五入

select lpad(round(rand() * 1000000, 0), 6, '0'); # 随机生成一个六位的验证码

日期函数

常见的日期函数如下：

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定 date 的年份
month(date)	获取指定 date 的月份
day(date)	获取指定 date 的日期
date_add(date, interval expr type)	返回一个日期 / 时间值加上一个时间间隔 expr 后的时间值
datediff(date1, date2)	返回起始时间 date1 和结束时间 date2 之间的天数
date_format(date, format)	指定日期格式

insert into te_user values (0, 'testName0', 18, '2023-11-21', '湖南长沙'),
                        (1, 'testName1', 19, '2023-11-22', '湖南娄底'),
                        (2, 'testName2', 20, '2023-11-21', '湖南怀化'),
                        (3, 'testName3', 19, '2023-11-26', '湖南长沙'),
                        (4, 'testName4', 21, '2023-11-28', '湖南郴州'),
                        (5, 'testName5', 22, '2023-11-23', '湖南长沙'),
                        (6, 'testName6', 17, '2023-11-27', '湖南郴州'),
                        (7, 'testName7', 21, '2023-11-27', '湖南长沙'),
                        (8, 'testName8', 19, '2023-11-24', '湖南衡阳'),
                        (9, 'testName9', 20, '2023-11-25', '湖南岳阳');

select curdate(); # 当前日期

select curtime(); # 当前时间

select now(); # 当前日期 + 时间

select year(now()); # 显示年份

select month(now()); # 显示月份

select day(now()); # 显示天数

select date_add(now(), interval 70 month); # 当前时间往后推70个月

select date_add(now(), interval 70 day); # 当前时间往后推70天

select datediff(curdate(), '2004-08-09');

# 显示员工入职时间并按照入职日期排序
select name, date, datediff('2024-01-01', date) from te_user order by date;

流程函数

流程函数可以在 SQL 语句中实现条件筛选，从而提高语句效率。

函数	功能
if(value, res1, res2)	如果 value 为 true，则返回 res1，否则返回 res2
ifnull(value1, value2)	如果 value 不为空，返回 value1，否则返回 value2
case when [val1] then [res1] ... else [default] end	如果 val1 为 true，返回 res1，……否则返回 default 默认值
case [expr] when [val1] then [res1] ... else [default] end	如果 exp 的值等于 val1，返回 res1，……否则返回default 默认值

# if语句
select if(true, 'ok', 'Error');

# ifnull, 第一个值为null时，返回default
select ifnull(null, 'default');

# case语句 case when then else end
# 查询姓名和地址，如果是长沙，则展示新一线城市
select
    name,
    case address when '湖南长沙' then '新一线城市' else address end
from te_user;

insert into score values (1, 'Tom', 67, 88, 95),
                         (2, 'Rose', 23, 66, 90),
                         (3, 'Jack', 56, 98, 76);

select
    id,
    name,
    case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end '数学',
    english,
    chinese
from score;

case...when...then 语句还可以用来赋值：

update Salary
set sex = 
    case sex when 'm' then 'f'
    else 'm'
    end;
# 使用单条 update 语句将性别扭转

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的是为了保证数据库中数据的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段的数据不能为 null	not null
唯一约束	保证该字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识、要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	保证字段值满足某一个条件	check
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	foreign key

create table it_user(
    id int primary key auto_increment comment '主键', # 主键、自增
    name varchar(10) not null unique comment '姓名', # 非空、唯一
    age int check ( age >=0 && age <= 120 ) comment '年龄', # 在[0, 120]内
    status char(1) default '1' comment '状态', # 默认为1
    gender char(1) comment '性别'
) comment 'it用户表';

如果勾选了自动增加，不需要对该数据写入。

insert into it_user (name, age, status, gender) values ('Tom1', 19, '1', '男'), ('Tom2', 25, '0', '男'); # 主键 id是自动自增的，不需要自己写入

外键约束：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。具有外键的表叫做子表（从表），被外键索引的表叫做父表（主表）。

添加外键：

create table 表名 (
    字段名 数据类型
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);

# 或者：
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

删除外键：

alter table 表名 drop foreign key 外键名称;

示例：

# 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

# 删除外键
alter table emp drop foreign key fk_emp_dept_id;

外键约束的删除、更新行为：

行为	说明
no action	当父表中删除、更新对应记录时，首先检查该记录是否有对应外键，如果有，则不允许删除、更新。（与 restrict 一致）
restrict	当父表中删除、更新对应记录时，首先检查该记录是否有对应外键，如果有，则不允许删除、更新。（与 no action 一致）
cascade	当父表中删除、更新对应记录时，首先检查该记录是否有对应外键，如果有，则删除、更新该外键所对应的记录。
set null	当父表中删除、更新对应记录时，首先检查该记录是否有对应外键，如果有，则将子表中的数据设置为 null 值。
set default	父表有变更时，子表将外键设置为默认值（lnnodb 不支持）

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade on delete cascade;
# 表示更新的时候使用cascade的方式，删除的时候也使用cascade的方式

多表查询

多表关系

表结构一般有三种：一对多（多对一）、多对多、一对一。

• 一对多：例如一个部门下可以有多个员工，可以利用外键进行维护。
• 多对多：例如一个学生可以选修多门课程，同时，一门课程也可以被多个学生选择。可以建立一张中间表来进行维护，需要两个外键。
• 一对一：例如用户与用户详情的关系，常用作单表的拆分。实现方式是在任意一方增加一个外键，关联另一个表，并且外键要设置为唯一的（unique）。

多表查询概述

多表查询基础语法：select * from 表1, 表2, ...;

注意，这个时候的直接多表查询会使每一张表的字段一一分配，也就是做所谓的笛卡尔积。这样子会导致很多无效的信息出现，所以我们需要消除无效的笛卡尔积。可以使用 where 语句进行筛选。

示例：select * from emp, dept where emp.id = dept.id;

多表查询分为连接查询和子查询。其中，连接查询分为内连接，外连接和自连接三种。

• 内连接：相当于查询A、B交集部分的数据。
• 外连接：分为左外连接和右外连接。左外连接查询左表（A表）所有数据以及A、B交集部分的数据。右外连接查询右表（B表）所有数据以及A、B交集部分的数据。
• 自连接：当前表与自身的连接查询，自连接必须使用表别名。

内连接

隐式内连接：select 字段列表 from 表1, 表2, where 条件...;

显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

隐式内连接与显式内连接的作用结果一致，隐式内连接的语法更简单，阅读性更好。而显式内连接有更少的字段扫描，可以加快执行速度。

# 多表查询 隐式内连接
select emp.id, emp.name, emp.dept_id, dept.name from emp, dept where emp.dept_id = dept.id;

# 给表起别名 起完别名之后只能通过别名访问表
select * from emp e, dept d where e.dept_id = d.id;

# 多表查询 显式内连接(inner join)
select e.id, e.name, e.dept_id, d.name from emp e inner join dept d on e.dept_id = d.id;

外连接

• 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件...;此时，查的是表1的所有信息与1、2表的交集。（常用） 一般来讲，外连接与内连接的区别在于外连接允许 null 值存在。
• 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件...;此时，查的是表2的所有信息与1、2表的交集。

# 左外连接
select * from emp left outer join dept on emp.dept_id = dept.id;

# 右外连接
select * from emp right outer join dept on emp.dept_id = dept.id;

自连接

语法：select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

自连接，可以是内连接也可以是外连接。但是必须起别名！

# 多表查询 隐式自连接
select e1.id, e1.name, e1.manager_id, e2.name from emp e1, emp e2 where e1.manager_id = e2.id;

# 多表查询 自连接
select e1.id, e1.name, e1.manager_id, e2.name from emp e1 left join emp e2 on e2.id = e1.manager_id;

联合查询

leetcode 每个产品在商店的不同价格

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

语法：

select 字段列表 from 表1...
union [all]
select 字段列表 from 表2...;

如果是 union all，是直接将两个结果合并。如果只是 union，则会有去重的操作。

union 需要保证查询出来的两个结果有相同的列数和相同的类型。否则无法执行。

除此之外还有 intersect 进行集合的交运算，except 进行集合的差运算。

子查询

所谓子查询，指 SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询。

语法：select * from t1 where column1 = (select column1 from t2);

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

根据子查询的结果不同，分为：

• 标量子查询（子查询的结果为单个值）。
• 列子查询（子查询结果为一列）。
• 行子查询（子查询结果为一行）。
• 表子查询（子查询结果为多行多列）。

根据子查询的位置，分为：where 之后， from 之后， select 之后。

标量子查询：查询的 where 条件为单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

# 查询研发部的员工信息
# 先查询研发部的部门id
select id from dept where name = '研发部';

# 查询对应员工信息
select * from emp where dept_id = 1;

# 以上两句可以利用嵌套select合并
select * from emp where dept_id = (select id from dept where name = '研发部');

# 查询在test2入职之后入职的员工
select * from emp where emp_date > (select emp_date from emp where name = 'test2') order by emp_date;

列子查询：查询的 where 条件为一列，常用操作符为in、not in、any、some、all。

其中：

操作符	描述
in	在指定的集合范围之内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与 any 等同，二者可以互用
all	子查询返回列表的所有值都必须满足

# 查询研发部和美术部的所有员工信息
# 先查询两个部门的id
select id from dept where name = '研发部' || name = '美术部';

# 根据部门id查询员工信息
select * from emp where dept_id in(1, 3);

# 二者合并有
select * from emp where dept_id in (select id from dept where name = '研发部' || name = '美术部');

# 查询比市场部所有人工资都高的员工信息
# 先查询市场部的部门id
select id from dept where name = '市场部';

# 查询市场部的员工的工资
select salary from emp where dept_id = 4;

# 查询工资比市场部员工最高工资还高的员工信息
select * from emp where salary > all(select salary from emp where dept_id = 4);
# 也可以用max实现

行子查询：查询的 where 条件是一行（可以是多列）。

# 查询test4的薪资及直属领导相同的员工信息
# 查询test4的薪资及直属领导
select salary, manager_id from emp where name = 'test4';

# 查询与test4薪资及直属领导相同的员工信息
select * from emp where salary = 2500 && manager_id = 3;

# 可以使用类似于集合的方式编写上述语句
select * from emp where (salary, manager_id) = (2500, 3);

# 利用select置换
select * from emp where (salary, manager_id) = (select salary, manager_id from emp where name = 'test4');

表子查询：查询的 where 条件是多行多列。

# 查询与test4,test6的职位和薪资相同的员工信息
# 查询与二者相同的职位和薪资
select dept_id, salary from emp where name = 'test4' || name = 'test6';

# 查询与二者信息相同的员工
select * from emp where salary in(2500, 4000) && dept_id in(3, 4);

# 可以利用类似于集合的方式编写上述语句
select * from emp where (dept_id, salary) in(select dept_id, salary from emp where name = 'test4' || name = 'test6');

# 查询入职时间在2023-03-22之后的员工的部门信息
# 查询在2023-03-22之后入职的员工信息
select * from emp where emp_date > '2023-03-22';

# 查询其对应的部门信息
select * from (select * from emp where emp_date > '2023-03-22') e left join dept d on e.dept_id = d.id;

窗口函数

聚合函数将同一个分组内的多行数据汇总成单个结果，窗口函数则保留了所有的原始数据。窗口函数一般用在处理排名问题（每个部门的薪资排名）和 TOPN 问题（查每个部门的排名第一）中。

窗口函数语法：

window_function([expression]) over (
    partition by...
    order by...
)

其中 window_function 是窗口函数的名称，expression 是可选的分析对象（字段名或者表达式），over 子句包含分区（partition by）、排序（order by）以及窗口大小（frame_clause）3个选项。

创建数据分区

例如，以下语句按照不同部门分别统计员工的月薪合计（类似于 group by）：

select
	emp_name,
	salary,
	dept_id,
	sum(salary) over (
    	partition by dept_id
    )
from emp;

分区内排序

排序选项通常用于数据的分类排名，例如以下语句用于分析员工在部门内的月薪排名：

select
	emp_name,
	salary,
	dept_id,
	sum(salary) over (
    	partition by dept_id
        order by salary desc
    )
from emp;

序号函数（常用）

方法	说明
row_number()	顺序排序，最朴素的排序方式，例如：1、2、3
rank()	并列排序，会跳过重复的序号，例如：1、1、3
dense_rank()	并列排序，不会跳过重复的序号，例如：1、1、2

如下便是利用窗口函数中的 rank() 函数对数据库的数据按照 salary 进行排序并且跳过重复序号。

select  *,
    rank() over(
        partition by dept_id
        order by salary
    )
from emp;

窗口函数还有很多其他特定函数，这里不一一列举。

事务

事务简介

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

举例：A向B转1000块。

操作集合为:

• 查询A的账户是否有1000块。
• 如果有，A的账户的余额减去1000。
• 之后B的账户余额加1000。

如果其中有一步抛出异常，则需要整组操作判为失败。否则可能会出现A的账户余额减去1000后B的账户余额没有改变的情况。

MySQL 默认下的事物是自动提交的，也就是说，当执行一条 DML 语句，MySQL 会立即隐式的提交事务。

事务操作

# 查看 / 设置事务提交方式
select @@autocommit;
set @@autocommit = 0;

# 提交事务
commit;

# 回滚事务
rollback;

示例：

# 转账操作
# 1. 查询张三账户的余额
select * from account where name = '张三';

# 2. 将张三账户的余额 -1000
update account set money = money - 1000 where name = '张三';
# 假设这里抛出异常了，会导致下方语句无法执行

# 3. 将李四账户的余额 +1000
update account set money = money + 1000 where name = '李四';

# 查看事务提交方式 1 -> 自动提交 0 -> 手动提交
select @@autocommit;
set @@autocommit = 0;


# 修复数据
update account set money = 2000 where name = '张三' or name = '李四';

# 转账操作
# 1. 查询张三账户的余额
select * from account where name = '张三';

# 2. 将张三账户的余额 -1000
update account set money = money - 1000 where name = '张三';
# 假设这里抛出异常了，会导致下方语句无法执行

# 3. 将李四账户的余额 +1000
update account set money = money + 1000 where name = '李四';

# 设置@@autocommit = 0之后，执行完上述转账操作后，数据并不会马上更改
# 此时需要执行commit提交事务，数据才会被更改

# 只有执行了commit，数据才会被更改
commit;

# 如果抛出异常，可以用rollback回滚事务
rollback;

当设置事务提交方式之后，只有当执行 commit 语句之后数据才会被真正更改！

还可以使用指令来对事务进行操作：

# 开启事务
start transaction 或 begin;

# 提交事务
commit;

# 回滚事务
rollback;

# 开启事务
start transaction;

# 转账操作
# 1. 查询张三账户的余额
select * from account where name = '张三';

# 2. 将张三账户的余额 -1000
update account set money = money - 1000 where name = '张三';
# 假设这里抛出异常了，会导致下方语句无法执行

# 3. 将李四账户的余额 +1000
update account set money = money + 1000 where name = '李四';

# 提交事务
commit;

# 回滚事务
rollback;

事务四大特性ACID

• 原子性（Atomictiy）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，都必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库的数据的改变就是永久的。

并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommited	√	√	√
Read commited	×	√	√
Repeatable Read（MySQL 默认）	×	×	√
Serializable	×	×	×

查看事务隔离级别：select @@transaction_isolation;

设置事务隔离级别：set [session | global] transaction isolation level {read uncommitted | read commited | repeatable read | serializable}

进阶

存储引擎

MySQL 体系结构

• 连接层：进行客户端连接以及完成认证处理、授权等操作。
• 服务层：SQL 接口、查询解析器、查询优化器、缓存等均在服务层实现。
• 引擎层：可插拔存储引擎，控制 MySQL 数据的存储和提取方式。（索引是在存储引擎层实现的，InnoDB 是 MySQL 5.5版本之后的默认引擎）
• 存储层：磁盘中存储着数据库当中的数据，包括了日志、文件等。

存储引擎简介

存储引擎是 MySQL 数据库特有的，是存储数据、建立索引、更新 / 查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

在建表时，指定存储引擎：

CREATE TABLE 表名(
#	...
) ENGINE = INNODB

# 查询建表语句 默认选择InnoDB引擎
show create table account;

# 查询当前数据库支持的引擎
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 引擎是一种兼顾高可靠性和高性能的通用存储引擎。其特点为：

• DML 操作遵循 ACID 模型，支持事务。
• 行级锁，提高并发访问性能。
• 支持外键约束，保证数据的完整性和正确性。

上述几个加粗关键字也是 InnoDB 与其他引擎的区别。

InnoDB 引擎的每张表都会对应一个表空间文件（扩展名为 ibd），存储该表的表结构（frm、sdi）、数据和索引。参数：innodb_file_per_tabel。

InnoDB 的逻辑存储结构（从大到小）：

1. 表空间（TableSpace）。
2. 段（Segment）。
3. 区（Extent，大小 1M）。
4. 页（Page，大小 16K）。
5. 行（Row）。

其中，行还包括了最后一次操作事务的 id（Trx id）、指针（Roll pointer）以及字段（col）。

其他存储引擎

MyISAM

是早期的 MySQL 引擎，特点：

• 不支持事务，不支持外键。
• 支持表锁，不支持行锁。
• 访问速度快。

涉及到的文件有三个，扩展名分别为：sdi（存放表结构信息）、myd（存放数据）、myi（存放索引）。

Memory

Memory 引擎的数据存储在磁盘中，受到硬件问题，只能将这些表作为临时表或者缓存使用。特点：

• 内存存放。
• 支持 Hash 索引（默认）。

文件扩展名为 sdi，只存放表结构数据，表中具体数据存放在磁盘中。

存储引擎选择

InnoDB：是 Mysql 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的（例如日志、评论相关数据可以使用，目前被 MangoDB 取代）。

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性（目前被 Redis 取代）。

索引

索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法，这种数据结构就是索引。

• 优点：帮助我们提高查找数据的效率，降低 IO 成本。通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗成本。
• 缺点：索引也是要占用磁盘空间的，且索引会降低更新表的速度，当进行插入、删除等操作的时候，需要去维护结构的更新，效率会降低。

索引结构

索引结构	说明
B+ Tree 索引	最常见的索引类型，大部分引擎都支持
Hash 索引	使用哈希表实现，只有精确匹配索引列的查询才有效（=，in），不支持范围查询（between、<、>、…）和排序操作
R-tree 空间索引	MyISAM 的特殊索引类型，只要用于地理空间数据类型，使用较少
Full-text 全文索引	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene，Solr，ES

索引	InnoDB	MyISAM	Memory
B+ Tree	支持	支持	支持
Hash	不支持	不支持	支持
R-tree	不支持	支持	不支持
Full-text	5.6 版本后支持	支持	不支持

B-Tree 和 B+Tree

B 树和 B+ 树可以看印度大佬的视频学习。

一个度数为 n 的B树，每个节点有 n 个指针（对应有 n 个子节点），有 n-1 个key，对应 n-1 个数据。当节点度数满了之后，再次插入遵循中间节点裂变的规则进行调整，即靠中间的数据向上分裂（可能发生与上层节点合并的情况），两侧数据挂两边（可能出现数据挂在上层节点的情况）。

B+ 树中，所有元素都会出现在叶子节点，上侧的非叶子节点只是起到索引作用，最下方的叶子节点形成了一个单向链表。即向上裂变的时候生成一个新的数据裂变上去，保证叶子节点和索引都有该数据。

MySQL 中的 B+ 树使用双向链表维护叶子节点，提高查找效率。

B 树和 B+ 树的节点均存放在页中，相对于 B 树来讲，B+ 树的节点空间利用率更高（只有叶子节点有数据），故 B+ 树可以容纳更多数据。对于 B+ 树，3层高的树大概可以存储2000万条记录。

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中某数据列中的数据重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext
联合索引	一个节点中的key有多个，按照顺序排列	要遵循最左前缀法则

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

• 聚集索引（Clustered Index）：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据。每张表必须有，且只有一个。
• 二级索引（Secondary Index）：将数据存储与索引分开存储，索引结构的叶子节点关联的是对应主键。每张表中可以存在多个。

从二级索引上选取出来的是主键值，选取到主键值之后会再次从聚集索引里面对比，选出最终的行数据。这种查询，称为“回表查询”。

聚集索引的选取规则：

1. 如果存在主键，主键索引就是聚集索引。
2. 如果没有主键，则使用第一个唯一索引作为聚集索引。
3. 如果表没有主键，也没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

索引语法

创建索引：

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);

查看索引：

SHOW INDEX FROM table_name;

删除索引：

DROP INDEX index_name ON table_name;

示例：

# 查看表索引
show index from te_user;

# 为name字段创建常规索引
create index idx_user_name on te_user(name);

# 为phone字段创建唯一索引
create unique index idx_user_phone on te_user(phone);

# 为剩余字段创建联合索引
create index idx_user_age_address_date on te_user(age, address, date);

# 删除索引
drop index idx_user_age_address_date on te_user;

SQL 性能分析

通过下述语句可以查看当前数据库的CRUD使用频次：

SHOW GLOBAL STATUS LIKE 'Com_______';

如果发现一个数据库中查询语句的执行频率相当高，则我们有必要为这个数据库进行索引优化。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志。

# 查看慢日志查询是否开启
show variables like 'slow_query_log';

# 设置慢查询日志的默认时间
set global long_query_time = 1;

profile 详情

show profies 能够在做 SQL 优化的时候帮助我们了解时间都耗费在哪里。

# 查询当前数据库是否支持profiles
select @@have_profiling;

# 查看是否开启profile
select @@profiling;

# 开启profiling
set profiling = 1;

# 查看所有sql语句耗时情况
show profiles;

# 查看指定query_id的sql语句各个阶段的耗时情况
show profile for query 77;

# 查看指定query_id的sql语句各个阶段的cpu耗时情况
show profile cpu for query  77;

explain 执行计划

在任意 select 语句前加上 explain 关键字可以进行执行计划的查看，例如：

explain select * from te_user;

执行计划查询出来后，每一列的对应意义：

• id：表示 select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序（id 相同，执行顺序从上到下，id 不同，值越大，越先执行）。
• select_type：表明当前 sql 语句的查询类型。
• type：表示连接类型，性能由好到差为 NULL、system、const、eq_ref、ref、range、index、all。当访问系统表时会出现 system、当使用主键或唯一键查询时会出现 const、当使用非唯一性索引进行查询时会出现 ref、当使用全表扫描时会出现 all。
• possible_key：显示可能应用在这张表上的索引，一个或多个。
• key：显示实际用到的索引，如果没有展示 NULL。
• key_len：表示索引中使用的字节数，是可能的最大长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
• rows：是一个估计值，表示要执行查询的行数。
• filtered：表示返回结果的行数占需要读取行数的百分比，越大越好。
• extra：表示额外的信息。

使用规则

最左前缀法则：如果索引了多列，要遵循最左索引法则。指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳过某一列，索引将部分失效（后面的字段索引失效）。

范围查询：联合索引中，出现范围查询，范围查询右侧的列将失效。

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

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

模糊查询：尾部模糊查询不会失效，头部模糊查询则会导致索引失效。

or 连接的条件：如果其中一个条件没有使用索引，则会导致全部索引失效。

数据分布影响：如果 MySQL 评估觉得走索引更慢，那 MySQL 会进行全表扫描。

SQL 提示：是优化数据库的一种手段，就是在数据库中加入人为提示来做到优化操作的目的，例子如下：

# 建议sql使用索引，sql不一定接受你的建议
explain select * from te_user use index (idx_user_age_date_address) where age = 18;

# 提示sql禁用索引
explain select * from te_user ignore index (idx_user_age_date_address) where age = 18;

# 强制sql使用索引
explain select * from te_user force index (idx_user_age_date_address) where age = 18;

覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中可以找到，避免回表查询）。

前缀索引：当字段类型为字符串时，有时需要索引很长的字符串，这会让索引变得非常大，查询时很浪费磁盘 IO，影响效率。此时可以只提取字符串的一部分前缀建立索引，可以大大节省磁盘空间，从而提高索引效率。前缀索引语法：

# 提取字符串前n个字符建立索引
CREATE INDEX idx_name ON table_name(COLUMN);

# 可以根据选择性来决定前缀的长度
# 选择性是指不重复的索引值和数据表的记录总数的比值，索引选择性越高则查询效率越高(区分度高)
# 唯一索引的选择性是1，是最好的索引选择性，性能也是最好的

# 根据下述公式求取索引的选择性
select count(distinct 字段名) / count(*) from 表名;
select count(distinct substring(字段名, 起始, 终止)) / count(*) from 表名;

# 增加前缀索引
create index idx_user_address_3 on te_user(address(3));

单列索引和联合索引：在业务场景中，如果存在多个查询条件，考虑使用联合索引而非单列索引，考虑到最左前缀法则，我们可以把较常用的字段放左侧。

设计原则

1. 对于数据量较大（超过100多万的数据），且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOTNULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

SQL 优化

主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT，index organized table）。B+ 树中的节点存放在“页”里，页可以为空，也可以填充一半，也可以全部填充。每个页包含了 2-N 行数据（如果一行数据过大，会行溢出），根据主键顺序排列。

如果主键是乱序插入，会扰乱原来已排序好的页，MySQL 会通过新建页并调整原来已经存放在页中的数据来达到维护主键有序的效果，这便是页分裂。

当删除一行记录时，实际上并没有直接进行物理删除，而是将数据标记成可以被覆盖的状态，允许这个数据被其他数据声明使用。当删除的数据达到一定阈值（merge_threshold，可以自己设置，默认为页的50%）后，MySQL 会查看相邻的两页是否有合并的可能，如果有，则合并相邻两页以节省空间，此之谓页合并。

故最终，我们可以得出主键的设计原则：

1. 满足业务要求的情况下，尽量降低主键的长度，降低占用的磁盘空间和节省搜索时的磁盘 IO。
2. 插入数据时尽量选择顺序插入，选择使用自增主键。
3. 尽量不要使用 UUID 做主键或者时其他自然主键，如身份证号，节省主键长度并避免出现页分裂的情况。
4. 在业务操作时尽量避免对主键进行修改，因为修改主键会牵动索引结构，修改成本高。

insert 优化

对于小批量数据的插入，可以优化的地方如下：

1. 尽量批量插入数据，减少与数据库建立连接进行传输的次数，并且尽量保证一次插入的数据量在1000条以下，如果数据量过大，建议使用多次批量插入。
2. 使用手动提交事务，在默认的自动提交事务前提下，insert 语句插入数据前后需要开启和提交事务，比较耗时，使用手动提交可以避免耗时操作。
3. 按照主键顺序插入，乱序插入的效率比顺序插入要低。

对于大批量数据的插入，使用 insert 语句性能降低，这个时候建议采用 MySQL 数据库提供的 Load 指令进行插入，将本地数据加载到数据库当中。操作如下：

# 客户端连接服务器时，加上参数 --local -infile
mysql --local-infile -u root -p

# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 查看默认开关是否开启
select @@local_infile;

# 执行load指令将准备好的数据加载到表结构中
load data 
	local infile 文件路径
	into table 表名
	fields terminated by 字段分隔符
	lines terminated by 行分隔符;

# 假设本地test.sql文件中格式为：12,testName12,20,2024-01-27,湖南长沙,13829978811
# 对于本地.sql文件的数据插入命令如下
load data 
	local infile 'D:/HNU/test.sql'
	into table te_user
	fields terminated by ','
	lines terminated by '\n';

order by 优化

MySQL 有两种排序方式，为：

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。故我们在优化 order by 时尽量优化为 using index 排序方式。

可以在创建索引的时候指定索引的排序方式：

create index idx_name on table_name(字段名1 asc, 字段名2 desc);

order by 的优化原则：

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2. 尽量使用覆盖索引。
3. 多字段排序，一个升序一个降序，此时要注意联合索引在创建时的规则。
4. 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256K）。

group by 优化

在分组操作的时候，建立适当的索引使得分组时可以利用索引以提高效率。分组时索引的使用也需要满足最左前缀法则。

limit 优化

limit 在大数据量的分页时越往后所需时间越长，因为 limit 底层默认走的是全表扫描。官方给出的优化方案是通过覆盖索引和子查询的方式提高 limit 的分页效率。

# 先拿到id
select id from te_user order by id limit 10, 10;

# 再查询id对应的行数据，mysql不支持in后跟limit，所以这里使用多表查询
select
    t.*
from
    te_user t,
    (
        select id from te_user order by  id limit 10, 10
    ) tmp
where
    t.id = tmp.id

上述方案使用了多表查询对 limit 语句进行优化。实际上，如果 limit 语句使用了 where 子句，可以在 where 子句所涉及到的字段添加索引，可以有效提高查询效率。

count 优化

MyISAM 引擎把一个表的总行数存在磁盘上，因此执行 count(*) 的时候会直接返回一个数，效率很高（但是前提是没有 where 条件，即查询的是总数）。

针对 InnoDB 引擎的 count 函数，我们没有一个非常好的优化方案，只能自己计数。

count是一个聚合函数，对于返回的结果，一行一行地判断，如果参数不是 NULL，累计值就 +1，否则不加，最后返回累计值。count的用法有以下几种：

• count(*)：求取表中的总记录数。InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接进行按行累加。
• count(主键)：求取主键个数。InnoDB 会遍历整张表，把每一行的主键 id 拿出来返回给服务层。服务层拿到主键后，直接进行累加（因为主键不为 NULL，所以直接加）。
• count(字段)：求取值不为 NULL 的该字段个数。与上述不同的是，如果该字段没有 not null 约束，则还需多一步判断是否为 NULL 的操作。如果有not null约束，则与上述相同。
• count(1)：查询出来的记录都会放一个1进去，最后在服务层进行累加。（实际上你可以传递任何数字进去）。

所以，相对来讲，如果要记录总数，count(*)的效率是最高的（count(1)的效率与其一致）。

update 优化

MySQL 的事务是行级锁。意味着对于索引，上的是行锁，当我们根据具有索引的字段更新本行数据的时候，如果并发更新其他行的数据，是可以成功更新的。但是，对于非索引数据，MySQL 上的是表锁，意味着当我们根据没有索引的字段更新本行数据的时候，如果并发更新其他行的数据，是无法成功更新的，因为此时整个表都锁住了。

故我们使用 update 的时候，需要根据索引字段进行更新，否则就会出现行锁升级为表锁的情况，锁住整张表，降低并发性能。值得一提的是，InnoDB 引擎的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

视图

视图（View）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗地讲，视图不保存查询结果，只保存查询的 SQL 逻辑。所以我们在创建视图的时候，主要的工作就落在创建这条 SQL 查询语句上。也可以理解为，每次进行查询工作，都需要编写查询代码进行查询，而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。

基本语法

# 创建语法(与修改语法一致，修改时or replace要加)
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];

# 查询语法
# 查看创建视图的sql语句
SHOW CREATE VIEW 视图名称;
# 查看视图数据
SELECT * FROM 视图名称;

# 另一种修改语法
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];

# 删除视图
DROP VIEW [IF EXISTS] 视图名称;

示例：

# 创建视图
create view user_v1 as select id, name from te_user where id <= 10;

# 查询视图
show create view user_v1;

# 查询视图数据
select * from user_v1;

# 修改视图
# create or replace view user_v1 as select id, name from te_user where id >= 10;
alter view user_v1 as select id, name from te_user where id >= 10;

# 删除视图
drop view user_v1;

检查选项

当使用 with cascaded check option 子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL 提供了两个选项：cascaded和 local，默认值为 cascaded。

# 在视图中插入数据会变成在表中插入数据，并且不会检查条件
insert into user_v1(id, name) values(16, 'test');

# 为避免插入的数据在视图中查不到，可以使用检查选项
create view user_v2 as select id, name from te_user where id <= 10 with cascaded check option;

select * from user_v2;

# 当插入的数据与视图的sql逻辑有误时，会阻止插入
insert into user_v2(id, name) values(16, 'test');

一但某一个视图有cascaded，则只要涉及到这个视图的级联，就会启用检查判定，而 local只判定自身。

比如，v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 cascaded，但是 v1 视图创建时未指定检查选项。 则在执行检查时，不仅会检查 v2，还会级联检查v2的关联视图 v1。又如果 v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 local ，但是 v1 视图创建时未指定检查选项。则在执行检查时，知会检查 v2，不会检查 v2 的关联视图 v1。

更新及作用

要使得视图可以更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数。
• DISTINCT。
• GROUP BY。
• HAVING。
• UNION 或 UNION ALL。

视图的作用如下：

1. 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2. 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
3. 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

多次使用 sql 语句访问 MySQL 服务器会形成多次请求，消耗性能。我们可以将这多条 sql 语句封装在一个集合里，减少访问请求次数，提升性能。

存储过程是事先经过编译并存储在数据库中的一段 sql 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库S sql 语言层面的代码封装与重用。

基本语法

# 创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	sql语句
END;

# 调用
CALL 名称([参数]);

# 查看指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'XXX';
# 查看某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

# 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;

# 在命令行中使用delimiter声明结束字符
delimiter $$
# 创建语句则写为
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	sql语句
END$$
# 改回分号
delimiter ;

示例如下：

# 创建存储过程
create procedure p1()
begin
    select count(*) from te_user;
end;

# 调用存储过程
call p1();

# 查看存储过程
show create procedure p1;

# 删除存储过程
drop procedure p1;

系统变量

系统变量由 MySQL 服务器提供，不是用户定义的，属于服务层面。分为全局变量（Global）、会话变量（Session）。

会话变量的修改只会影响到当前会话本身，而全局变量在修改之后会影响到所有会话。但是，二者的修改在 MySQL 服务器重启之后均会失效，如果想永久修改某个系统变量，需要去修改 MySQL 的配置文件。

语法如下：

# 查看所有系统变量，可进行模糊匹配，没加级别默认为session级别
SHOW [SESSION | GLOBAL] VARIABLES [LIKE...];

# 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;

# 设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL] 系统变量名 = 值;

示例：

# 查看系统变量
show variables;

# 查看和事务提交相关的变量
show global variables like 'auto%';

# 查看指定系统变量
select @@autocommit;

# 设置系统变量
set session autocommit = 0;

用户自定义变量

是用户根据自己需求自定义的变量，用户变量不用提前声明，在用的时候直接用@变量名使用就可以，其作用域为当前连接。

语法如下：

# 赋值
SET @var_name = expr[, @var_name = expr]...;
SET @var_name := expr[, @var_name := expr]...;
SELECT @var_name := expr[, @var_name := expr]...;
SELECT 字段名 INTO @var_name FROM 表名;

# 使用
SELECT @var_name;

示例：

# 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender = '男', @myhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from te_user;

# 使用
select @myname, @myage, @mygender, @myhobby, @mycolor, @mycount;

局部变量

是根据需要定义的在局部生效的变量，访问之前，需要 declare 声明。可以用作存储过程内的局部变量和输入参数，局部变量的范围在其内声明的 begin...end 块。

语法如下：

# 声明
DECLARE 变量名 变量类型[DEFAULT...];

# 赋值
SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名;

示例：

# 创建存储过程
create procedure p2()
begin
    declare user_count int default 0; # 声明局部变量
    set user_count := 100;  # 局部变量赋值
    # 下述也可以给局部变量赋值
    select count(*) into user_count from te_user;
    select user_count;
end;

# 调用存储过程
call p2();

if 判断

语法如下：

IF 条件1 THEN
ELSEIF 条件2 THEN
ELSE
END IF;

示例：

# 根据定义的分数score变量，判定当前分数对应的分数等级
create procedure p3()
begin
    declare score int default 0; # 定义局部变量
    declare result varchar(10);
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;
end;

# 调用存储过程
call p3();

参数

类型	含义	备注
IN	作为输入	默认
OUT	作为返回值
INOUT	既可以作为输入，也可以作为返回值

语法如下：

CREATE PROCEDURE 存储过程名称([IN / OUT / INOUT 参数名 参数类型])
BEGIN
	sql语句
END;

示例：

# 判定分数等级，并返回
create procedure p4(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;
# 调用p4，使用自定义变量接收返回值
call p4(60, @result);
select @result;



# 将传入的200分制的分数换算成100分制的分数
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;
# 调用p5
set @score := 200;
call p5(@score);
select @score;

case

语法如下：

# 语法1
CASE case_value
	WHEN when_value1 THEN statement_list1
	[WHEN when_value2 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

# 语法2
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

示例：

# 根据传入的月份，判定所属的季节
create procedure p6(in month int, out season char(1))
begin
    case
        when month >= 1 && month <= 3 then
            set season := '春';
        when month >= 4 && month <= 6 then
            set season := '夏';
        when month >= 7 && month <= 9 then
            set season := '秋';
        when month >= 10 && month <= 12 then
            set season := '冬';
        else
            set season := '无';
    end case;
end;


call p6(2, @season);
select @season;

while

当满足条件时执行循环，语法如下：

# 先判定条件，如果条件为true，则执行逻辑，否则不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

示例：

# 从1累加到n
create procedure p7(in input int)
begin
    declare output int default 0;
    while input > 0 do
        set output := output + input;
        set input := input - 1;
    end while;
    select output;
end;

call p7(3);

repeat

当满足条件时退出循环，语法如下：

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL 逻辑...
	UNTIL 条件
END REPEAT;

示例：

# 计算1到n的值
create procedure p8(in input int)
begin
    declare output int default 0;
    repeat
        set output := output + input;
        set input := input - 1;
    until input = 0
    end repeat;
    select output;
end;

call p8(3);

loop

loop 实现简单的循环，如果不在 sql 逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop 可以配合以下两个语句使用：

• leave：配合循环使用，退出循环。
• iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

语法如下：

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

LEAVE label;	# 退出指定标记的循环体
ITERATE label;	# 直接进入下一次循环

示例：

# 从1加到n
create procedure p9(in input int)
begin
    declare output int default 0;
    sum: loop # sum是loop的标签
        set output := output + input;
        set input := input - 1;
        if input <= 0 then
            leave sum;
        end if;
    end loop;
    select output;
end;

call p9(10);

cursor

游标（cursor）是用来存储查询结果集的数据类型。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE。注意要先声明普通变量，再声明游标。

语法如下：

# 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句; 

# 打开游标
OPEN 游标名称;

# 获取游标记录
FETCH 游标名称 INTO 变量[, 变量];

# 关闭游标
CLOSE 游标名称;

示例：

# 查询te_user中年龄小于传递进来的user_age的用户信息，然后插入到新表中
create procedure p11(in user_age int)
begin
    # 先声明普通变量
    declare username varchar(20);
    declare useraddress varchar(20);

    # 声明游标存储查询的结果集
    declare u_cursor cursor for
        select name, address from te_user where age <= user_age;

    # 创建新表
    create table tmp_user (
        id int primary key auto_increment,
        name varchar(20) not null,
        address varchar(20) not null
    ) comment '临时用户表';

    # 打开游标
    open u_cursor;

    # 获取游标记录，需要循环遍历，这里暂时写成死循环，要解决这个问题需要用到handler
    while true do
        # 从游标当中获取数据，按照顺序来
        fetch u_cursor into username, useraddress;
        # 数据插入新表中
        insert into tmp_user values (null, username, useraddress);
    end while;

    # 关闭游标
    close u_cursor;
end;

handler

条件处理程序（Handler）可以用来定义再流程控制结构执行过程中遇到问题时相应的处理步骤，具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement;

handler_action
	CONTINUE:继续执行当前程序
	EXIT:终止执行当前程序
condition_value
	SQLSTATE sqlstate_value:状态码
	SQLWARNING:所有以01开头的SQLSTATE代码的简写
	NOT FOUND:所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码简写

示例：

# 查询te_user中年龄小于传递进来的user_age的用户信息，然后插入到新表中
create procedure p11(in user_age int)
begin
    # 先声明普通变量
    declare username varchar(20);
    declare useraddress varchar(20);

    # 声明游标存储查询的结果集
    declare u_cursor cursor for
        select name, address from te_user where age <= user_age;

    # 声明完游标之后声明条件处理程序
    # 状态码满足02000时触发handler将游标关掉
    declare exit handler for sqlstate '02000' close u_cursor;

    # 创建新表
    create table tmp_user (
        id int primary key auto_increment,
        name varchar(20) not null,
        address varchar(20) not null
    ) comment '临时用户表';

    # 打开游标
    open u_cursor;

    # 获取游标记录，需要循环遍历
    while true do
        # 从游标当中获取数据，按照顺序来
        fetch u_cursor into username, useraddress;
        # 数据插入新表中
        insert into tmp_user values (null, username, useraddress);
    end while;

    # 关闭游标
    close u_cursor;
end;

call p11(19);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型。存储函数能做的，存储过程也能做，且存储函数局限性更大（必须有返回值），所以存储函数的使用频率比存储过程要低。语法如下：

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

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

示例：

# 存储函数完成从1到n的累加，参数不需要指定类型，强制为in类型
create function 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);

触发器

触发器是与表有关的数据库对象，指在 insert / update / delete 之前或之后，触发并执行触发器中定义的 SQL 语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发（影响多少行就触发多少次），不支持语句级触发（不管一次影响多少行，最后只触发一次）。

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

语法如下：

# 创建触发器
CREATE TRIGGER trigger_name
BEFORE / AFTER INSERT / UPDATE / DELETE
ON tbl_name FOR EACH ROW # 行级触发器
BEGIN
	trigger_stmt;
END

# 查看触发器
SHOW TRIGGERS;

# 删除
DROP TRIGGER [schema_name.]trigger_name;

INSERT 型触发器：

# 通过触发器记录te_user表的数据变更日志，将变更日志插入到user_logs表中
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(id)
)engine=innodb default charset=utf8;


# 定义触发器
create trigger te_user_insert_trigger
    after insert
    on te_user for each row
begin
    # 往日志表中插入数据
    insert into
        user_logs (id, operation, operate_time, operate_id, operate_params)
    values
        (
         null,
         'insert',
         now(),
         new.id,
         concat('插入的数据内容为: id=', new.id, ' name=', new.name, ' age=', new.age, '...')
        );
end;

# 使用触发器
insert into te_user values(16, 'testName16', 20, now(), '湖南长沙', 17);

# 查看触发器
show triggers;

# 删除触发器
drop trigger te_user_insert_trigger;

UPDATE 型触发器：

# 修改型触发器
create trigger te_user_update_trigger
    after update
    on te_user for each row
begin
    # 往日志表中插入数据
    insert into
        user_logs (id, operation, operate_time, operate_id, operate_params)
    values
        (
         null,
         'update',
         now(),
         new.id,
         concat('更新前: id=', old.id, ' name=', old.name, '更新后: id=', new.id, ' name=', new.name, '...')
        );
end;

# 修改数据
update te_user set address = '湖南长沙' where id = 13;

DELETE 型触发器：

# 删除数据的触发器
create trigger te_user_delete_trigger
    after delete
    on te_user for each row
begin
    # 往日志中插入数据
    insert into
        user_logs (id, operation, operate_time, operate_id, operate_params)
    values
        (
         null,
         'delete',
         now(),
         old.id,
         concat('删除的数据: id=', old.id, ' name=', old.name, '...')
        );
end;

# 删除数据
delete from te_user where id = 16;

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、IO）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

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

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性和一致性。

全局锁进行数据备份：

# 加全局锁，上锁后其他客户端只能读不能写，如果有更新操作，会阻塞
flush tables with read lock;

# 在windows命令行中备份数据，因为这条语句不是mysql命令
mysqldump -u用户名 -p密码 要备份的数据库 > 以.sql文件形式存放的路径

# 释放锁
unlock tables;

数据库中加全局锁，是一个比较重的操作，存在以下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在lnnoDB引擎中，我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份：

# 不加全局锁的数据备份
mysqldump --single-transaction -u用户名 -p密码 要备份的数据库 > 以.sql文件形式存放的路径

表级锁

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

表锁分三类：

1. 表锁。
2. 元数据锁（meta data lock，MDL）。
3. 意向锁。

表锁

表锁分为两类：

1. 表共享读锁（read lock）。读锁只能读，不可写，换句话讲读锁会阻塞写但不会阻塞读。
2. 表独占写锁（write lock）。写锁对于当前客户端来讲，能读能写，但是对于别的客户端来讲，不能读也不能写。

语法：

# 加锁
lock tables 表名... read / write

# 释放锁
unlock tables / 客户端断开连接;

总的来说，读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

MDL 加锁过程是系统自动控制的，我们无需显式调用，在访问一张表的时候会自动加上，MDL 锁主要作用是维护表元数据（元数据可以理解为表结构）的一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。

可以理解为如果一张表存在未提交的事务，我们不能去修改这张表的表结构，为了避免 SQL 语句的冲突，保证读写的准确性。

在 MySQL 5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁（共享）；当对表结构进行变更操作的时候，加 MDL 写锁（排他）。

对应 SQL	锁类型	说明
lock tables xxx read / write	shared_read_only / shared_no_read_write
select、select ... lock in share mode	shared_read（本会话只能读不能写，其他会话可以读也可以写）	与 shared_read、shared_write 兼容，与 exclusive 互斥
insert、update、delete、select ... for update	shared_write（所有会话都可以读，但只有本会话可以写）	与 shared_read、shared_write 兼容，与 exclusive 互斥
alter table...	exclusive（本会话可以读也可以写，但是别的会话不能读也不能写）	与其他的 MDL 都互斥

查看元数据锁：

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

意向锁

注意到一个场景：当我们利用update语句对表的数据进行操作时，这个时候系统会自动给表加上一个元数据锁，使得所有会话都可以读，但是只有本会话可以写。假设这个时候有另外一个客户端对这个表执行lock tables xxx read / write操作，欲给这个表上一个表锁，这时会发生锁的冲突。为了避免锁的冲突，客户端需要一行一行检查表的行数据以确定是否有元数据锁，这会使得性能下降。

为了避免 MDL 在执行时，加的行锁与表锁冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表的检查。

意向锁分有：

1. 意向共享锁（IS）：与表锁读锁（read）兼容，与表锁写锁（write）互斥。
2. 意向排他锁（IX）：与表锁读锁和写锁都互斥，意向锁之间不会互斥。

查看意向锁及行锁的加锁情况：

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

对应 SQL	锁类型
select	不会加意向锁
select ... lock in share mode	IS
select ... for update	IX
insert、update、delete	IX

行级锁

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

对于行级锁，主要分为以下三类：

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

行锁

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

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

即：SS兼容，SX与XX是互斥的。

SQL	行锁类型	说明
insert	排他锁	自动加锁
update	排他锁	自动加锁
delete	排他锁	自动加锁
select	不加任何锁
select ... lock in share mode	共享锁	需要手动在 select 后面加上 lock in share mode
select ... for update	排他锁	需要手动在 select 后面加上 for update

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

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

间隙锁和临键锁

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

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

InnoDB 引擎

逻辑存储结构

之前写过，逻辑存储结构从大到小分为：表空间、段、区、页、行。接下来，我们便来详细了解一下上述五个方面的存储结构的特点：

1. 表空间：ibd 文件，一个数据库实例可以对应多个表空间，用于存储记录，索引等数据。
2. 段：分为数据段、索引段、回滚段，InnoDB 是索引组织表，数据段就是 B+ 树的叶子节点，索引段即为非叶子节点。段用来管理多个区。
3. 区：表空间的单元结构，大小固定为1M。一个区当中默认包含64个连续的页。
4. 页：是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB 引擎每次从磁盘申请4-5个区。
5. 行：InnoDB 存储引擎当中的数据。当中有两个隐藏字段：一个是 Trx_id，记录着最后一次事务操作的 id；另一个是 Roll_pointer，相当于一个指针，通过这个指针，可以找到增删改之前的数据是什么。

引擎架构

内存结构

Buffer Pool：缓冲池是主内存的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行 CRUD 操作时，先操作缓冲池中的数据（如果没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘 IO，加快处理速度。

缓冲池以 Page 页为单位，底层采用链表管理。根据状态，Page 分为三种类型：

1. free page：空闲 page，未被使用。
2. clean page：被使用的 page，数据没有被修改过。
3. dirty page：脏页，被使用 page，数据被修改过，页中数据与磁盘的数据不一致。

Change Buffer：更改缓冲区（针对于非唯一二级索引页），在执行 DML 语句时，如果这些数据 Page 没有在 Buffer Pool 时，不会直接操作磁盘，而会将数据变更存在 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到 Buffer Pool，再将合并后的数据刷新到磁盘中。

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入。那么每一次的操作，就可能影响不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘 IO。有了 Change Buffer 后，可以有效减少磁盘 IO，提高效率。

Adaptive Hash Index：自适应 Hash 索引，用于优化对 Buffer Pool 数据的查询。InnoDB 引擎会监控表上各索引页的查询，如果观察到 Hash 索引可以提高速度，则建立 Hash 索引，称之为自适应 Hash 索引，无需人工干预。（参数：adaptive_hash_index）

Log Buffer：日志缓冲区，用来保存要写入磁盘中的 log 日志数据（redo log、undo log），默认大小为16MB，日志缓冲区中的内容会定期刷新到磁盘中。如果需要更新、插入或删除多行的事务，增加日志缓冲区的大小可以节省磁盘 IO。（缓冲区大小：innodb_log_buffer_size，日志刷新到磁盘时机：innodb_flush_log_at_trx_commit）

磁盘结构

System Tablespace：系统表空间，Change Buffer 存放在此。如果表示在系统表空间而不是每个表文件或者通用表空间中创建的，它也可能包含表和索引数据。（在 MySQL 5.x 版本中还包含 InnoDB 数据字典、unlog 等，参数：innodb_data_file_path）

Doublewrite Buffer Files：双写缓冲区，InnoDB 将数据页从 Buffer Pool 写入到磁盘之前会将数据写入双写缓冲区当中，进行数据备份。便于系统异常时恢复数据。（文件扩展名为dblwr）

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

File-Per-Table Tablespaces：每张表具有独立的表空间，存放在这个区域当中。（参数：innodb_file_per_table）

General Tablespaces：通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

# 创建通用表空间
CREATE TABLESPACE xxx ADD
DATAFILE 'file_name'
ENGINE = engine_name;

# 将表存放入表空间中
CREATE TABLE xxx ... TABLESPACE ts_name;

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

Temporary Tablespaces：临时表空间，InnoDB 使用会话临时表空间和全局临时表空间来存储用户的临时表等数据。

后台线程

后台线程的作用是把 InnoDB 缓冲池中的数据在合适的时候刷新到磁盘文件当中。

Master Thread：核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo 页的回收。

IO Thread：在 InnoDB 引擎中大量使用了 AIO（异步非阻塞 IO） 来处理 IO 请求，这样可以极大提高数据库的性能，而 IO Thread 主要负责这些 IO 请求的回收。

线程类型	默认个数	职责
Read thread	4	负责读操作
Write thread	4	负责写操作
Log thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区内容刷新到磁盘

使用show engine innodb status;查看上述状态信息。

Purge Thread：主要用于回收事务已经提交的 undo log，在事务提交之后，undo log 可能不用了，就用它来回收。

Page Cleaner Thread：协助 Master Thread 刷新脏页数据到磁盘中，它可以减轻 Master Thread 的负担，减少阻塞。

事务原理

事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。特点（ACID）：

• 原子性（Atomictiy）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，都必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库的数据的改变就是永久的。

其中，原子性、一致性、持久性由 redo log 和 undo log 来保证；隔离性由锁机制和 MVCC（多版本并发控制）来实现的。

redo log

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

客户端发器请求操作 MySQL 服务器，先操作 Buffer Pool（如果要操作的数据不存在这里，则调用后台线程从磁盘中读取数据）。这个时候，缓冲区的数据变更了，但是磁盘的数据没有变更，出现脏页。如果脏页在往磁盘中进行刷新时出错，事务已经提交了，但是脏页的数据并没有刷新到磁盘中，事务的持久性就无法得到保障。

而 redo log 就是用来解决上述问题的。redo log 会将增删改的信息存放在 redo log buffer 中，记录数据页的物理变化。当事务进行提交时，redo log buffer 中的日志会刷新到磁盘中形成日志文件 redo log（Write-Ahead Logging，WAL，预写日志），持久化地保存数据变化。在进行脏页刷新时如果出错，就可以利用 redo log 进行数据的恢复。

undo log

回滚日志，存放于引擎的磁盘结构，用于解决事务的原子性。该日志文件会记录数据被修改前的信息，作用包括两个：提供回滚和 MVCC。

与 redo log 记录物理日志不一样，undo log 记录的是逻辑日志。可以认为当 delete 一条记录时，undo log 会记录一条对于的 insert 记录，反之亦然。当 update 一条记录时，它记录一条反向的 update 记录。当执行 rollback 时，就可以读取 undo log 当中用于恢复数据的 sql 记录进行数据的恢复。

undo log 的销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除 undo log，因为这个日志还会涉及到 MVCC。

undo log 的存储：undo log 采用段的方式进行存储，存放在前面介绍的回滚段中，内部包含1024个 undo log segment。

MVCC

基本概念

当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加共享或者排他锁的操作可以认为是当前读。

快照读：读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。简单的select就是快照读。

• Read Committed：每次 select，都生成一个快照读。
• Repeatable Read：开启事务后第一个 select 语句才是快照读的地方。
• Serializable：快照读会退化为当前读。

MVCC：Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供了一个非阻塞读功能。MVCC 的具体实现，还需要依赖于数据库记录中的三个隐藏式字段，undo log 日志、readView。

隐藏字段

记录中的隐藏字段：

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或者最后一次修改该记录的事务ID
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，才会生成本字段

undo log 版本链

前面介绍过，undo log 是回滚日志，在 insert、update、delete 的时候产生的便于数据回滚的日志。当 insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除。而 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读的时候也需要，不会立即被删除。

undo log 版本链指的是不同事务或者相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本链表，链表的头部是最新的记录，链表尾部是最早的记录。

ReadView

ReadView（读视图）是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交）的 id。

其中有四个核心字段：

1. m_ids：当前活跃的事务 ID 集合。
2. min_trx_id：最小活跃事务 ID。
3. max_trx_id：预分配事务 ID，当前最大事务 ID + 1（因为事务 ID 是自增的）
4. creator_trx_id：ReadView 创建者的事务 ID。

在 undo log 中有一个trx_id字段，表示当前事务的 ID。版本链数据访问规则：

1. trx_id == creator_trx_id：可以访问该版本，说明数据是当前这个事务更改的。
2. trx_id < min_trx_id：可以访问该版本，说明数据已经提交了。
3. trx_id > max_trx_id：不可以访问该版本，说明该事务是在 ReadView 生成后才开启。
4. min_trx_id <= trx_id <= max_trx_id：如果trx_id不在m_ids之中，说明数据已经提交，可以访问该版本。

不同的隔离级别，生成 ReadView 的时机不同：

1. READ COMMITTED（读已提交）：在事务中每一次执行快照读时生成 ReadView。
2. REPEATABLE READ（可重复读）：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView。

MySQL 管理

MySQL 安装后，自带四个数据库：

数据库	含义
mysql	存储 MySQL 服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema	提供了访问数据库元数据（指数据库本身的各种数据）的各种表和视图，包含数据库、表、字段类型及访问权限等
performance_schema	为 MySQL 服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys	包含了一系列方便 DBA 和开发人员利用 performance_schema 性能数据库进行性能调优和诊断的视图

常用工具

mysql

该 mysql 不是指 mysql 服务，而是指 mysql 的客户端工具，语法如下：

# 语法
mysql [options] [database]
# 选项
-u, --user=name	# 指定用户名
-p, --password[=name] # 指定密码
-h, --host=name # 指定服务器IP或域名
-P, --post=port # 指定连接接口
-e, --execute=name # 执行SQL语句并退出

# -e 可以在MySQL客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便
# 示例
mysql -uroot -p123456 db01 -e"select * from stu";

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态，创建并删除数据等。

# 通过帮助文档查看选项
mysqladmin --help

# 示例
mysqladmin -uroot -p123456 drop 'test01';
mysqladmin -uroot -p123456 version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到 mysqlbinlog 日志管理工具。

# 语法
mysqlbinlog [options] log-files1 log-files2 ...

# 选项
-d, --database=name # 指定数据库名称，只列出指定的数据库相关操作
-o, --offset=# # 忽略掉日志中的前n行命令
-r, --result-file=name # 将输出的文本格式日志输出到指定文件
-s, --short-form # 显示简单格式，省略掉一些信息
--start-datetime=date1 --stop-datetime=date2 # 指定日期间隔内的所有日志
--start-position=pos1 --stop-position=pos2 # 指定位置间隔内的所有日志

mysqlshow

mysqlshow 是客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

# 语法
mysqlshow [options] [db_name [table_name [col_name]]]

# 选项
--count # 显示数据库及表的统计信息(数据库、表均不可以指定)
-i	# 显示指定数据库或者指定表的状态信息

# 示例
# 查询每个数据库的表的数量及表中的记录信息
mysqlshow -uroot -p1234 --count
# 查询test库中每个表的字段数、及行数
mysqlshow -uroot -p1234 test --count
# 查询test库中book表的详细情况
mysqlshow -uroot -p1234 test book --count

mysqldump

mysqldump 客户端工具是用来备份数据库或在不同数据库之间进行数据迁移，备份内容包含创建表，及插入表的 SQL 语句。

# 语法
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A

# 连接选项
-u, --user=name	# 指定用户名
-p, --password[=name] # 指定密码
-h, --host=name # 指定服务器IP或域名
-P, --post=port # 指定连接接口

# 输出选项
--add-drop-database # 在每个数据库创建语句前加上drop database语句
--add-drop-table # 在每个表创建语句前加上 drop table 语句,默认开启;不开启 (--skip-add-drop-table)
-n, --no-create-db # 不包含数据库的创建语句
-t, --no-create-info # 不包含数据表的创建语句
-d --no-data # 不包含数据
-T, --tab=name # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

# 数据备份示例
mysqldump -uroot -p123456 库名 > 文件路径
mysqldump -uroot -p1234 -T mysql信任的文件路径 要备份的库名 表名

mysqlimport / source

mysqlimport 是客户端数据导入工具，用来导入 mysqldump 加 -T 之后导出的文本文件：

# 语法
mysqlimport [options] db_name textfile1 [textfile2...]

# 示例
mysqlimport -uroot -p1234 test/tmp/city.txt

# 导入sql文件
source /root/xxx.sql

运维

日志

错误日志

错误日志是MySQL中最重要的日志之一，它记录了当 mysql 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录/var/log，默认的日志文件名为mysql.log。查看日志位置：

show variables like '%log_error%'

二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言） 语句和 DML（数据操作语言） 语句，但不包括数据查询（SELECT、SHOW）语句。它保持了所有数据更改的事件顺序，当需要将数据库恢复至特定时间点时，可以使用二进制日志。

值得注意的是：MySQL 的二进制日志是用于记录数据修改操作，便于复制和数据恢复使用，而 redo log 和 undo log 是 InnoDB 存储引擎内部用来保证事务安全和支持高并发访问的机制。它们各自有着不同的角色和功能，要做出区分。

作用：

1. 灾难时的数据恢复。
2. MySQL 的主从复制，在 MySQL8 版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%'

MySQL 服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式	含义
statement	基于 sql 语句的日志记录，记录的是 sql 语句，对数据进行修改的 sql 都会记录在日志文件中
row	基于行的日志记录，记录的是每一行的数据变更（默认）
mixed	混合了 statement 和 row 两种格式，默认采用 statement，在某些特殊情况下会自动切换为 row 进行记录

show variables like '%binlog_format%'

由于二进制日志是以二进制的方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法为：

# 语法
mysqlbinlog [options] log-files1 log-files2 ...

# 选项
-d # 指定数据库名称，只列出指定的数据库相关操作
-o # 忽略掉日志中的前n行命令
-v # 执行事件（数据变更）重构为SQL语句
-w # 执行事件（数据变更）重构为SQL语句，并输出注释信息

对于比较繁忙的系统，每天产生的日志是很多的，长时间不清除会很占用磁盘空间。可以通过以下几种方式清理日志：

指令	含义
reset master	删除全部 binlog 日志
purge master logs to 'binlog.***'	删除 *** 编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'	删除日志为yyyy-mm-dd hh24:mi:ss之前产生的所有日志

也可以在 mysql 的配置文件中配置二进制日志过期时间（默认30天）：

show variables like '%binlog_expire_logs_seconds%'

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包括查询数据的 SQL 语句。默认情况下，查询日志是未开启的。

查看查询日志开关：

show variables like '%general%'

修改 MySQL 的配置文件，添加以下内容开启查询日志：

# 该选项用来开启查询日志 0关1开
general_log = 1
# 设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file = mysql_query.log

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有 sql 语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以到微秒。

# 慢查询日志
show_query_log = 1;
# 执行时间参数
long_query_time = 2;

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements更改此行为log_queries_not_using_indexs，如下：

# 记录执行较慢的管理语句
log_slow_admin_statements = 1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

主从复制

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL 支持一台主库（Master）同时向多台从库（Slave）进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 主从复制有以下三个优点：

1. 主库出现问题，可以快速切换从库提供服务。
2. 实现读写分离，降低主库的访问压力。
3. 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

主库会将 DDL 和 DML 操作写入 binlog 二进制日志中。而从库涉及到两组线程：一组线程叫 IOthread，该线程发起请求连接主库，然后读取主库的 binlog 日志，返回后写回到从库自身的 Relay log 中；另一组线程叫 SQLthread，负责读取 Relay log 日志中的数据，将 Relay log 日志中记录的数据变化反应到自身的数据变化，从而保证主从的数据一致性。

所以，主从复制的原理可以分为三步：

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
2. 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 RelayLog。
3. slave 重做中继日志中的事件，将改变反映它自己的数据。

搭建

需要准备两台服务器作为主库和从库，开始之前，主从库都需要先进行下述操作：

# 开放指定的3306端口号
firewall-cmd --zone=public --add-port=3306/tcp=permanent
firewall-cmd -reload

# 关闭服务器的防火墙和防火墙的开机自启
systemctl stop firewall
systemctl disable firewallds

# 开放端口和关闭防火墙二选一

准备好两台服务器之后，在上述两台服务器中分别安装好 MySQL，并完成基础的初始化准备工作。

主库配置

修改配置文件/etc/my.cnf：

# mysql服务ID，保证整个集群环境中唯一，取值范围：1－2^32-1，默认为1
server-id = 1 
# 是否只读，1代表只读,0 代表读写
read-only = 0
# 忽略的数据，指不需要同步的数据库
#binlog-ignore-db=mysql
# 指定同步的数据库
#binlog-do-db=db01

接下来重启主库的 MySQL 服务器：systemctl restart mysqld。

重启完毕后，登录 mysql，创建远程连接的账号，并授予主从复制的权限：

# 创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
# 为itcast'@'%'用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

最后通过指令，查看二进制日志坐标：

show master status

字段含义说明：

1. file：从哪个日志文件开始推送日志文件。
2. position：从哪个位置开始推送日志。
3. binlog_ignore_db：指定不需要同步的数据库。

从库配置

修改配置文件/etc/my.cnf：

# mysql服务ID，保证整个集群环境中唯一，取值范围：1－2^32-1，和主库不一样即可
server-id = 2
# 是否只读代表只读，0代表读写
read-only = 1
# 超级管理员只读
#super-read-only = 1

接下来重启从库的 MySQL 服务器：systemctl restart mysqld。

登录 mysql，在从库里设置主库配置（xxx参数是在主库配置的时候可以查看到的）：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='xxx.xxx', SOURCE_USER='xxx', SOURCE_PASSWORD='xxx', SOURCE_LOG_FILE='xxx', SOURCE_LOG_POS=xxx;

上述是 8.0.23 中的语法，如果 mysql 是 8.0.23 以前的版本，执行如下 sql：

CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='xxx', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=xxx;

然后开启同步操作：

start replica; # 8.0.22之后
start slave; # 8.0.22之前

最后查看主从同步状态，如果 io_running 和 sql_running 都是 yes 的话，则连接成功：

show replica status; # 8.0.22之后
show slave status; # 8.0.22之前

分库分表

问题分析：大量的数据存放在一个数据库中时，容易引起磁盘空间不足，一个服务器的内存不可能无限扩展下去，这就容易导致数据库的访问压力增大。故使用单数据库进行数据存储，存在以下性能瓶颈：

1. IO 瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘 IO，效率较低。请求数据太多，带宽不够，网络 IO 瓶颈。
2. CPU 瓶颈：排序、分组、连接查询、聚合统计等 SQL 会耗费大量的 CPU 资源，请求数太多，CPU 出现瓶颈。

分库分表的核心就是将数据分散存储，使得单一数据库 / 表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分方式

垂直拆分

垂直分库指按照库当中表的种类来分库，将不同的表分开，拆分到不同的库中。特点：

1. 每个库的表结构都不一样。
2. 每个库的数据也不一样。
3. 并且所有库的并集是全量数据。

垂直分表指按照表当中的字段来分表，将一个表的字段拆分到多个表中去。特点：

1. 每个表的结构都不一样。
2. 每个表的数据也不一样，一般通过一列（主键 / 外键）关联。
3. 所有表的数据并集是全量数据。

水平拆分

水平分库指将一个库的数据直接拆到多个库中。特点：

1. 每个库的表结构一致。
2. 每个库的数据不一致。
3. 所有库的并集是全量数据。

水平分表指将一张表当中的数据分散存储在多个库中。特点：

1. 每个表的表结构一致。
2. 每个表的数据都不一样。
3. 所有表的并集是全量数据。

实现技术

实现分库分表的技术有：

1. shardingJDBC：基于 AOP 原理，在应用程序中对本地执行的 SQL 进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持 java 语言，性能较高，可以理解为增强版的 JDBC 驱动，使得程序员不需要关注到底分了多少库和多少表，实际操作就好像在操作一个库一个表一样。（官网戳我）
2. MyCat：基于 java 语言编写的数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。可以像使用 mysql 一样来使用 mycat，对于开发人员来说根本感觉不到 mycat 的存在，底层是伪装协议。（下载戳我）

shardingJDBC

未完待续。