MySQL

DDL(数据定义语言)

数据库操作


# 查询所有数据库
show databases;

# 查询当前数据库
select database();

# 创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
# 创建一个itheima数据库，并且指定字符集 
create database itheima default charset utf8mb4;

# 删除数据库
drop database [if exists] 数据库名;

# 切换数据库
use 数据库名;

表操作


# 查询当前数据库所有表
show tables;

# 查看指定表结构
desc 表名;

# 查询指定表的建表语句
show create table 表名;

# 创建表结构
CREATE TABLE 表名(
  字段1 字段1类型[COMMENT 字段1注释],
  字段2 字段2类型[COMMENT 字段2注释],
  字段3 字段3类型[COMMENT 字段3注释],
  ......
  字段n 字段n类型[COMMENT 字段n注释]
) [COMMENT 表注释];
# 创建一张表
 create table tb_user(
   id int comment '编号',
   name varchar(50) comment '姓名',
   age int comment '年龄',
   gender varchar(1) comment '性别'
 )comment '用户表';

# 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
# 为emp表增加一个新的字段“昵称”为nickname，类型为varchar(20)
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
# 将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickename username varchar(30) COMMENT '昵称';

# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 将emp表的字段username删除
ALTER TABLE emp DROP username;

# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
# 将emp表的表名修改为 employee
ALTER TABLE emp RENAME TO employee;

# 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表, 并重新创建表
TRUNCATE TABLE 表名;

数值类型

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT或INTEGER	4 bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 bytes	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
FLOAT	4 bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

字符串类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

char与varchar都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关。而varchar是变长字符串，指定的长度为最大占用长度。相对来说，char的性能会更高些

日期时间类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值,时间戳

DML(数据操作语言)

添加数据（INSERT）


# 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
# 给employee表所有的字段添加数据；
insert into employee(id, workno, name, gender, age, idcard, entrydate) 
values(1, '1', 'Itcast', '男', 10, '123456789012345678', '2000-01-01');

# 给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);
# 插入数据到employee表
insert into employee values(2,'2','张无忌','男',18,'123456789012345670','2005-01-01');

# 批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...)  
VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
# 批量插入数据到employee表
insert into employee 
values(3,'3','韦一笑','男',38,'123456789012345670','2005-01-01'),(4,'4','赵敏','女',18,'123456789012345670','2005-01-01');

注意事项:


- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小，应该在字段的规定范围内

修改数据（UPDATE）


# 修改数据
UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ....[WHERE 条件];
# 修改employee表中id为1的数据，将name修改为itheima
UPDATE employee SET name = itheima where id = 1;
# 修改employee表中id为1的数据, 将name修改为小昭, gender修改为女
UPDATE employee SET name = "小昭", gender = "女" where id = 1;

注意事项

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

删除数据（DELETE）


# 删除数据
DELETE FROM 表名 [WHERE 条件];
# 删除gender为女的员工
delete from employee where gender = '女';

注意事项:


- DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
- DELETE语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

`DQL`(数据查询语言)


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

所有的查询语句都是从FROM开始执行，执行过程中，每个步骤都生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，最后一个步骤产生的虚拟表就是输出结果

基本查询（不带任何条件）


# 查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名;
SELECT * FROM 表名;

# 字段设置别名
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名;
SELECT 字段1 [别名1], 字段2 [别名2] ... FROM 表名;

# 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

# 查询指定字段 name, workno, age并返回
select name, workno, age from emp;
# 查询返回所有字段
select * from emp
# 查询所有员工的工作地址,起别名
select workaddress '工作地址' from emp;
# 查询公司员工的上班地址有哪些(不要重复)
select DISTINCT workaddress '工作地址' from emp;

条件查询（`where`）

比较运算符& 逻辑运算符

比较运算符	功能	逻辑运算符	功能
>	大于	AND 或 &&	并且 (多个条件同时成立)
>=	大于等于	OR 或
<	小于	NOT 或 !	非 , 不是
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围之内(含最小、最大值)
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配 _匹配单个字符, %匹配任意个字符
IS NULL	是NULL


# 基本语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;

# 查询年龄等于 88 的员工
SELECT name FROM emp where age = 88;
# 查询年龄小于等于 20 的员工信息
SELECT * from emp where age <= 20;
# 查询没有身份证号的员工信息
SELECT * from emp where idcard is null;
# 查询有身份证号的员工信息
SELECT * from emp where idcard is not null;
# 查询年龄不等于 88 的员工信息
SELECT * from emp where age != 88;
# 查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from emp where age <= 20 and age >= 15;
# 查询性别为女且年龄小于25岁的员工信息
select * from emp where age < 25 and gender = '女';
# 查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40;
# 查询姓名为两个字的员工信息
select * from emp where name like '__';
# 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';

聚合函数（`count`、`max`、`min`、`avg`、`sum`）

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

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


# 语法
SELECT 聚合函数(字段列表) FROM 表名;

# 统计该企业员工数量
select count(*) from emp;
# 统计该企业员工的平均年龄
select avg(age) from emp;
# 统计该企业员工的最大年龄
select max(age) from emp;
# 统计该企业员工的最小年龄
select min(age) from emp;
# 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = "西安";

分组查询（`group by`）

where与having的区别


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

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义执行顺序: where > 聚合函数 > having支持多字段分组, 具体语法为 : group by columnA, columnB


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

# 根据性别分组,统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender;
# 根据性别分组,统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;
# 查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select address, count(*) from emp where age < 45 group by address having count(*) >= 3;
# 统计各个工作地址上班的男性及女性员工的数量
select gender,workaddress,count(*) from emp group by gender,workaddress;

排序查询（`order by`）

排序方式


- ASC: 升序(默认值)
- DESC: 降序

如果是升序, 可以不指定排序方式<font>ASC</font>; 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序;


# 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

# 根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
# 根据入职时间, 对员工进行降序排序
select * from emp order by entrydate desc;
# 根据年龄对公司的员工进行升序排序, 年龄相同, 再按照入职时间进行降序排序
select * from emp order by age asc, entrydate desc;

分页查询（`limit`）

起始索引从0开始，起始索引 = （查询页码 – 1）* 每页显示记录数分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10


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

# 查询第1页员工数据, 每页展示10条记录
select * from emp limit 0,10
# 查询第2页员工数据, 每页展示10条记录
select * from emp limit 10,20;

DCL(数据控制语言)

管理用户

查询结果

其中Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可远程访问的。User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。


# 查询用户
select * from mysql.user;

# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

# 删除用户
DROP USER '用户名'@'主机名';

权限控制

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

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


# 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;

# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

字符串函数

函数	功能
CONCAT(S1, S2, …, Sn)	字符串拼接，将S1，S2，… Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串


# 字符串拼接
select concat('Hello','MySQL');

# 全部转小写
select lower('Hello');

# 全部转大写
select upper('Hello');

# 左填充
select lpad('01', 5, '-');
# 右填充
select rpad('01', 5, '-');

# 去除空格
select trim(' Hello  MySQL ');

# 截取子字符串
select substring('Hello MySQL',1,5);

# 企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。
 update emp set workno = lpad(workno, 5, '0');

数值函数

函数	功能
ceil(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数


# ceil：向上取整
select ceil(1.1);

# floor：向下取整
select floor(1.9);

# mod：取模
select mod(7,4);

# rand：获取随机数
select rand();

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

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1 和结束时间date2之间的天数


# 当前日期
select curdate();

# 当前时间
select curtime();

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

# 当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());

# 增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR);

# 获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');

# 查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by 
entrydays desc;

流程函数

函数	功能
IF(value, t , f)	如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果expr的值等于val1，返回 res1，…否则返回default默认值


# if
select if(false, 'Ok', 'Error');

# ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

# case when then else end
# 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
 select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else 
     '二线城市' end ) as '工作地址'
 from emp;

约束

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

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY


CREATE TABLE tb_user(
  id int AUTO_INCREMENT PRIMARY KEY  COMMENT  'ID唯一标识',
  name varchar(10) NOT NULL UNIQUE  COMMENT  '姓名' ,
  age int check (age > 0 && age <= 120)  COMMENT  '年龄' ,
  status char(1) default  '1'  COMMENT  '状态',
  gender char(1)  COMMENT  '性别'
);

# 创建表设置外键
CREATE TABLE 表名(
  字段名 数据类型,
  ...
  [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
# 修改表设置外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
# 为emp表的dept_id字段添加外键约束,关联dept表的主键id。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
# 删除emp表的外键fk_emp_dept_id
alter table emp drop foreign key fk_emp_dept_id;

多表查询

内连接

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


# 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

# 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

# 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
# 表结构: emp , dept 连接条件: emp.dept_id = dept.id
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
# 查询每一个员工的姓名, 及关联的部门的名称(显式内连接实现)
# 表结构: emp , dept 连接条件: emp.dept_id = dept.id
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

外连接

外连接分为左外连接和右外连接。


- 左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
- 右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而在日常开发使用时，更偏向于左外连接。


# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

# 查询emp表的所有数据, 和对应的部门信息
# 表结构: emp, dept 连接条件: emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

# 查询dept表的所有数据, 和对应的员工信息(右外连接)
# 表结构: emp, dept 连接条件: emp.dept_id = dept.id
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

自连接&联合查询

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。


# 语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

# 查询员工及其所属领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id

# 联合查询 把多次查询的结果合并起来，形成一个新的查询结果集
SELECT 字段列表 FROM 表A ...  
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

# 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
select * from emp where salary < 5000
union all
select * from emp where age > 50;

连接算法

嵌套循环算法（Nested-Loop Join，NLJ）

通过两层循环，用第一张表做Outter Loop，第二张表做Inner Loop，Outter Loop的每一条记录跟Inner Loop的记录作比较，符合条件的就输出。而NLJ又有3种细分的算法：嵌套循环算法又可以分为简单嵌套循环、索引嵌套循环、块嵌套循环。

简单嵌套循环（SNLJ）

SNLJ就是两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出，这也就是让两张表做笛卡尔积，比较次数是R * S，是比较暴力的算法，会比较耗时。

索引嵌套循环（INLJ）

INLJ是在SNLJ的基础上做了优化，通过连接条件确定可用的索引，在Inner Loop中扫描索引而不去扫描数据本身，从而提高Inner Loop的效率。而INLJ也有缺点，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。

块嵌套循环（BNLJ）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后在内存中比较匹配条件是否满足。但内存里可能并不能完全存放的下表中所有的记录。为了减少访问被驱动表的次数，我们可以首先将驱动表的数据批量加载到Join Buffer（连接缓冲），然后当加载被驱动表的记录到内存时，就可以一次性和多条驱动表中的记录做匹配，这样可大大减少被驱动表的扫描次数，这就是BNLJ算法的思想。

在数据库执行JOIN时，它通常会用“嵌套循环”或者“哈希”来匹配两张表的记录

在索引命中的情况下，如果使用嵌套循环，驱动表的每一行要去另一个张表里找匹配的行，驱动表越小，循环次数越少，性能越高。

子查询


# 语法
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

标量子查询：子查询返回的结果是单个值（数字、字符串、日期等）。常用的操作符：= <> > >= < <= 列子查询：子查询返回的结果是一列（可以是多行）。常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

行子查询：子查询返回的结果是一行（可以是多列）。常用的操作符：=、<>、IN、NOT IN表子查询：子查询返回的结果是多行多列。常用的操作符：IN


# 标量子查询
# 查询 "销售部" 部门ID
select * from emp where emp.dept_id = (select id from dept where dept.name='销售部');

# 列子查询
# 查询 "销售部" 和 "市场部" 的所有员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
# 查询比财务部所有人工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));
# 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

# 行子查询
# 查询与"张无忌"的薪资及直属领导相同的员工信息;
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

# 表子查询
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (salary,job) in (select salary,job from emp where name = '鹿杖客' or name = '宋远桥');
# 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

事务

TODO(补充笔记)：[https://blog.csdn.net/kking_edc/article/details/110848986](https://blog.csdn.net/kking_edc/article/details/110848986)

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

默认MySQL的事务是自动提交的，当执行完一条DML语句时，MySQL会立即隐式的提交事务

事务操作


# 第一种开启事务
# 查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit=0;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

# 第二种开启事务
# 开启事务
START TRANSACTION 或 BEGIN;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

事务四大特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
通过undo log(回滚日志)实现
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
通过持久性+原子性+隔离性保证
隔离性（Isolation）：数据库系统提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
通过MVCC(多版本并发控制)或锁机制保证
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
通过redo log(重做日志)来保证

并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据，此时读的数据是脏数据

B读取到了A未提交的数据

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

事务A两次读取同一条记录，但是读取到的数据却是不一样的。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
`Read uncommitted`（未提交读）	√	√	√
`Read committed`（读已提交）	×	√	√
`Repeatable Read`(默认)（可重复读）	×	×	√
`Serializable`（串行化）	×	×	×

串行化是通过加锁的方式来进行隔离读已提交和可重复读是通过Read View实现，区别在于ReadView的时间不同
读已提交在每个语句执行前生成一个Read View可重复读是在启动事务时生成一个Read View，然后整个事务期间都在用这个ReadView，通过这个 Read View就可以在undo log版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的
解决幻读：尽量在开启事务之后，马上执行select ... for update这类锁定读的语句，因为它会对记录加next-key lock，从而避免其他事务插入一条新记录，就避免了幻读的问题


# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

事务日志

缓冲池(buffer pool)：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池中没有数据，则从磁盘中加载并缓存），以一定的频率刷新到磁盘，从而减少磁盘IO，加快处理速度数据页(page)：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB，页中存储的是行数据

由于MySQL事务提交后，是将数据页先存入缓冲池中，然后再从缓冲池将数据页保存在磁盘中。但是在从缓冲池将数据页保存在磁盘的过程中服务器宕机，则会造成数据丢失，违背了事务的持久性

redo log：重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成：
重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。
undo log：回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然当update一条记录时，会记录一条对应相反的update记录。执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

MVCC（多版本并发控制）

MVCC，多版本并发控制。指维护一个数据的多个版本，使读写操作没冲突。MVCC允许多个事务同时更新，删除，在update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除,

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

Read View(读视图)：是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交)id
**当前读：**读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于日常操作，如：select. .. lock in share mode(共享锁)，select... for update、update、insert、delete(排他锁)都是一种当前读。**快照读：**简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed：每次select，都生成一个快照读。Repeatable Read：开启事务后第一个select语句才是快照读的地方。
四个核心字段：
m_ids：当前活跃的事务ID集合，即启动但还未提交的事务min_trx_id：最小ID的活跃事务max_trx_id：预分配事务ID，当前最大事务ID+1(事务ID自增的)，即创建Read View时当前数据库应该给下一个事务的id值，也就是全局事务中最大的事务id值+1creator_trx_id：ReadView创建者的事务ID

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

READ COMMITTED：在事务的每一次执行快照读时生成ReadViewREPEATABLE READ：仅在事务第一次执行快照读时生成ReadView，后续复用该ReadView

尽量不要使用长事务。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。空间只能被 InnoDB 复用，不能直接释放给操作系统。

存储引擎

连接器：建立连接，管理连接、校验用户身份查询缓存：查询语句若命中查询缓存则直接返回，否则继续往下执行【MySQL8.0已删除该模块】解析器：解析SQL查询语句解析词法分析，然后构建语法树，以便后续模块读取表名、字段、语句类型执行SQL
预处理阶段：检查表或字段是否存在；将select *中的*符号拓展为表上的所有列优化阶段：基于查询成本选择成本最小的执行计划执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回客户端


# 建表时指定存储引擎
CREATE TABLE  表名(
  字段1 字段1类型 [COMMENT 字段1注释],
  ......
  字段n 字段n类型 [COMMENT 字段n注释] 
) ENGINE = INNODB [COMMENT 表注释];

# 查询当前数据库支持的存储引擎
show engines;

# 查询建表语句
 show create table account;

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认MySQL存储引擎

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

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引

逻辑存储结构

表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表的空间中可以包含多个Segment段。段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。区 : 区是表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。页 : 页是组成区的最小单元，页是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。行: InnoDB存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段。

MyISAM

MyISAM是MySQL早期的默认存储引擎。

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

xxx.sdi：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

**Memory**

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

内存存放hash索引（默认）

xxx.sdi：存储表结构信息

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	–	–
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	–	–	支持
全文索引	支持	支持	–
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	–	–

索引

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

索引结构

索引结构	描述
`B+Tree`索引	最常见的索引类型，大部分引擎都支持`B+`树索引
`Hash`索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
`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树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针

5阶的B-树，每一个节点最多存储4个key，对应5个指针。一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。在B-树中，非叶子节点和叶子节点都会存放数据

B+Tree

B+Tree是B-Tree的变种，以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图

绿色框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。红色框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的相比于B树，磁盘读写代价更低(树的高度更小，所以读取次数少)，查询效率B+树更加稳定，B+树便于扫库和区间查询

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

InnoDB的B+树指向的是表的ID，而MyISAM的B+树指向的是表行的指针（物理地址）

Hash

哈希索引是采用一定的hash算法，将键值换算成新的hash值，映射到对应的位置，然后存储hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）无法利用索引完成排序操作查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	`PRIMARY`
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	`UNIQUE`
常规索引(普通索引)	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	`FULLTEXT`

数据结构分类：B+tree索引、Hash索引、Full-text索引物理存储分类：聚簇索引(主键索引)、二级索引(辅助索引)字段特性分类：主键索引、唯一索引、普通索引、前缀索引字段个数分类：单列索引、联合索引

聚集索引&二级索引

分类	含义	特点
聚集索引(`Clustered Index`)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(`Secondary Index`)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则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;

# name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
CREATE INDEX idx_user_name ON tb_user(name);

# phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

# 为profession、age、status创建联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

# CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
CREATE INDEX idx_email ON tb_user(email);

最左前缀法则

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


# 创建联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

# 使用索引 满足最左前缀法则 因此全部索引有效
select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

# 使用索引 不满足最左前缀法则 profession后的索引无效
select * from tb_user where profession = '软件工程' and status = '0';

# 使用索引 不满足最左前缀法则 索引无效
select * from tb_user where age = 31 and status = '0';

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。但使用（<=, >=）时，则右侧的索引仍会有效，因此要尽可能使用（<=, >=）。


# 范围查询右边的status字段索引无效
select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

# 范围查询右边的status字段索引有效
select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

索引失效

不能在索引列上进行运算操作，否则索引会失效字符串要加引号，否则索引会失效（类型转化会导致索引失效）模糊查询。仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效范围查询，右边的列不会使用索引用or分割的条件，如果or的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到如果MySQL评估使用索引比全表更慢，则不使用索引


# 根据phone字段进行函数运算操作之后，索引失效
select * from tb_user where substring(phone,10,2) = '15';

# 数据库存在隐式类型转换，索引将失效
explain select * from tb_user where phone = 17799990015;

# 在关键字后面加%，索引生效。在关键字前面加%，索引失效
select * from tb_user where profession like '软件%';
select * from tb_user where profession like '%工程';
select * from tb_user where profession like '%工%';

# 由于age没有索引，所以即使id、phone有索引，索引也会失效。
select * from tb_user where phone = '17799990017' or age = 23;

覆盖索引

尽量使用覆盖索引，减少select *

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。


# 当查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。 若超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据，这个过程就是回表。
select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。


# 创建前缀索引
create index idx_xxxx on table_name(column(n));

# tb_user表的email字段，建立长度为5的前缀索引
create index idx_email_5 on tb_user(email(5));

# 选择前缀索引的长度 结果越接近1则性能越好
select count(distinct substring(email,1,5)) / count(*)  from  tb_user ;

索引设计原则

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

SQL性能分析


# session 是查看当前会话
# global 是查询全局数据
SHOW GLOBAL STATUS LIKE 'Com_______';

Com_delete: 删除次数Com_insert: 插入次数Com_select: 查询次数Com_update: 更新次数

慢查询日志

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

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息


# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

/var/lib/mysql/localhost-slow.log

profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：


# 查询profile状态
SELECT  @@have_profiling;

# 通过set语句在session/global级别开启profiling
SET  profiling = 1;

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

Explain

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。


-- 直接在select语句之前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)
select_type	表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、 SUBQUERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered的值越大越好

SQL优化

表的设计优化

比如设置合适的数值(tinyint int bigint)，要根据实际情况选择比如设置合适的字符串类型(char和varchar)char定长效率高，varchar可变长度，效率稍低

SQL语句优化

SELECT语句务必指明字段名称(避免直接使用select *)SQL语句要避免造成索引失效的写法尽量用union all代替union，因为union会多一次过滤，效率低
union把多路结果拼起来后，数据库还要做一次“把重复行过滤掉”的步骤，这一步通常需要临时表 + 排序（或哈希/唯一索引），属于额外的计算与 I/O；而 UNION ALL 直接把各分支结果顺序拼接，不去重，不需要这一步
避免在where子句中对字段进行表达式操作
在 WHERE 里对“列”做函数/运算（DATE(col), UPPER(col), col*1.1…）会让优化器拿不到该列的有序性，无法据此计算区间边界，只能全表/大范围扫描，再用 Using where/Using index condition 过滤
Join优化，能用inner join就不用left join right join，如必须使用一定要以小表为驱动内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或right join，不会重新调整顺序

主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

定位慢查询

慢查询一般会来自于聚合查询、多表查询、表数据量过大查询、深度分页查询，表象为页面加载过慢，接口压测响应时间过长
方案一：开源工具
调试工具：Arthas运维工具：Prometheus、Skywalking
方案二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:


# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2


- <font>配置完毕之后，通过以下指令重新启动</font>`<font>MySQL</font>`<font>服务器进行测试，并可以查看慢日志文件中所记录的信息</font>`<font>/var/lib/mysql/localhost-slow.log</font>`

分析SQL语句

可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息


# 直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

possible_key：当前sql可能会使用到的索引key：当前sql实际命中的索引key_len：索引占用的大小Extra：额外的优化建议
Using where；Using Index：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据Using index condition：查找使用了索引，但是需要回表查询数据
type：这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
system：查询系统中的表const：根据主键查询eq_ref：主键索引查询或唯一索引查询ref：索引查询range：范围查询index：索引树扫描all：全盘扫描

插入数据

对于插入数据，若需要一次性插入多条数据，要么是一次批量插入数据，要么是手动控制事务，以防自动事务频繁开启。主键顺序插入，性能要高于乱序插入。


# 批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

# 手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

主键优化

表数据都是根据主键顺序组织存放的，而行数据都是存储在聚集索引的叶子节点上

一个页中所存储的行也是有限的（16K），如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。

A. 主键顺序插入

从磁盘中申请页，主键顺序插入

第一个页没有满，继续往第一页插入

当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

B. 主键乱序插入

若1#,2#页都已经写满了，存放了如图所示的数据

插入id为50的记录，会开辟一个新的页3#，但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50，此时，这三个页之间的数据顺序有问题。1#的下一个页，应该是3#， 3#的下一个页是2#。所以，此时，需要重新设置链表指针

页合并

对于已有数据的索引结构(叶子节点)如下

当我们对已有数据进行删除时，具体的效果如下: 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用

当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

索引设计原则

满足业务需求的情况下，尽量降低主键的长度插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键尽量不要使用UUID做主键或者是其他自然主键，如身份证号业务操作时，避免对主键的修改。

语法优化

order by优化

Using filesort: 通过表的索引或全表扫描，读取满足条件数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

Using index的性能高，Using filesort的性能低


# 无索引 执行效率低
select id,age,phone from tb_user order by age;
select id,age,phone from tb_user order by age, phone;

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

# 有索引后执行效率高
select id,age,phone from tb_user order by age;
# 排序时,也需要满足最左前缀法则
# 不满足最左前缀 索引失效
select id,age,phone from tb_user order by phone, age;

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

group by优化

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

limit优化（超大分页）

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。


select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a 
where t.id  =  a.id;

count优化

count用法	含义
count(主键)	InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段)	没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(数字)	InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。
count(*)	InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键id) < count(1) ≈ count()，所以尽量不使用 count()

主从同步

对MySQL搭建主从数据库，主数据库负责写，从数据库负责读

MySQL主从复制的核心就是二进制日志，二进制日志(BINLOG)记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。

复制分成三步:


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

bin_log和redo_log的最终一致性
采用两阶段提交机制
提交过程如下：
1. 写redolog prepare阶段：InnoDB先写入redo log的prepare状态（表示事务准备提交），此时还没正式commit，数据也写入了InnoDB的缓冲池
2. 写binlog：MySQL Server层生成binlog日志并写入磁盘
3. 写redolog commit：InnoDB发现binlog写成功后，写redolog的commit记录，事务正式提交

中间如果发生宕机：
1. 如果只有redo的prepare，没有binlog→ 崩溃恢复时事务会被回滚（因为binlog缺失）
2. 如果redo prepare + binlog都写成功了 → 崩溃恢复时可以完成commit

原因一：MySQL的崩溃恢复逻辑以binlog为“提交依据”

MySQL 崩溃恢复中，Server层会先检查binlog；如果发现binlog中事务存在，但InnoDB的redo log是prepare状态，说明这笔事务是 “提交完成但commit日志还没落盘”；为了保证binlog和InnoDB一致性，MySQL会在恢复时强制补写redo commit，完成该事务的提交。

这种恢复机制称为：基于binlog的补偿式提交机制（commit recovery）

原因二：binlog 和 redo log 写顺序保障了崩溃一致性

MySQL强制要求顺序：必须先写完binlog，InnoDB才能写commit到redo log

也就是说，如果 redo commit 写入成功，一定说明 binlog 成功写入；
而如果宕机时已经有 binlog，即代表是“准提交状态”，MySQL就可以补commit。

宕机恢复时的一致性保障
恢复时InnoDB会：
1. 遍历redolog
2. 发现有prepare但没有commit的记录
2.1 如果对应binlog没有成功写入 → 回滚该事务
2.2 如果binlog存在该事务记录 →redo commit并完成持久化
这保证了最终binlog与redolog的内容保持一致。

分库分表

项目业务数据逐渐增多，或业务发展比较迅速，优化已经解决不了性能问题，存在，IO瓶颈（磁盘IO、网络IO），CPU瓶颈（聚合查询、连接数太多）

拆分策略

垂直分库：以表为依据，根据业务将不同表拆分到不同库中
按业务对数据分级管理、维护、监控、扩展在高并发下，提高磁盘IO和数据量连接数
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
冷热数据分离减少IO过渡争抢，两表互不影响
水平分库：将一个库的数据拆分到多个库中
根据路由规则找到对应的库（取模，按id范围查找）解决了单库大数量，高并发的性能瓶颈问题提高了系统的稳定性和可用性
水平分表：将一个表的数据拆分到多个表中（可以在同个库中）
优化单一表数据量过大而产生的性能问题,避免IO争抢并减少锁表的几率
分库之后的问题
分布式事务一致性问题跨节点关联查询跨节点分页、排序函数主键避重
分库中间件：mycat，sharding-sphere

视图/存储过程/触发器

视图

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

视图只保存了查询的SQL逻辑，不保存查询结果。


# 创建视图
CREATE [OR REPLACE] VIEW 名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION]

# 查询视图创建语句
SHOW CREATE 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] 视图名称 [,视图名称] ...;

# 示例
-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;
-- 查询视图
show create view stu_v_1;
 -- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;
-- 删除视图
drop view if exists stu_v_1;

检查选项

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

CASCADED

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

LOCAL

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

视图更新

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

聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）DISTINCTGROUP BYHAVINGUNION或者UNION ALL

存储过程

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

封装，复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。可以接收参数，也可以返回数据：再存储过程中，可以传递参数，也可以接收返回值。减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，我们只需要网络交互一次可能就可以了

基本语法


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

# 调用存储过程
CALL 名称 ([参数]);

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

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

# 示例
# 创建
create procedure p1()
begin
  select count(*) from student;
end;

# 调用
call p1();

# 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;

# 删除
drop procedure if exists p1;

在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

变量

系统变量

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


# 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;
# 通过LIKE模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......';
# 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;

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

# 示例
# 查看系统变量
show session variables ;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;

# 设置系统变量
set session autocommit = 1;
insert into course(id, name) VALUES (6, 'ES');
set global autocommit = 0;
select @@global.autocommit;

用户定义变量

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


# 赋值
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 tb_user;
# 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;

局部变量

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

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等


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

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

# 示例
# 声明局部变量 - declare
# 赋值
create procedure p2()
begin
  declare stu_count int default 0;
  select count(*) into stu_count from student;
  select stu_count;
end;

call p2();

语法

if

if用于做条件判断，在if条件判断的结构中，ELSE IF结构可以有多个，也可以没有。ELSE结构可以有，也可以没有。


IF 条件1 THEN 
  .....
ELSEIF 条件2 THEN       -- 可选
  .....
ELSE                    -- 可选
  .....
END IF;

# 示例
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;
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;

# 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;

case


# 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 否则就执行 statement_list
CASE case_value
  WHEN when_value1 THEN statement_list1
  [WHEN when_value2 THEN statement_list2] ...
  [ELSE statement_list]
END CASE;

# 含义：当条件search_condition1成立时，执行statement_list1，当条件search_condition2成立时，执行statement_list2， 否则就执行 statement_list
CASE
  WHEN search_condition1 THEN statement_list1
  [WHEN search_condition2 THEN statement_list2] ...
  [ELSE statement_list]
END CASE;

# 示例
create procedure p6(in month int)
begin
  declare result varchar(10);
  case
    when month >= 1 and month <= 3 then
      set result := '第一季度';
    when month >= 4 and month <= 6 then
      set result := '第二季度';
    when month >= 7 and month <= 9 then
      set result := '第三季度';
    when month >= 10 and month <= 12 then
      set result := '第四季度';
    else
      set result := '非法参数';
    end case ;
    select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
 end;
 
 call p6(16);

while

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句


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

# 示例
# A. 定义局部变量, 记录累加之后的值;
# B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
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;

call p7(100);

repeat

repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环


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

# 示例
-- A. 定义局部变量, 记录累加之后的值;-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
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;
 
call p8(10);
call p8(100);

loop

LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用：

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


# A. 定义局部变量, 记录累加之后的值;
# B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
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;
  select total;
end;

call p9(100);

# A. 定义局部变量, 记录累加之后的值;
# B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
# 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
create procedure p10(in n int)
begin
  declare total int default 0;
  sum:loop
    if n<=0 then
      leave sum;
    end if;
    
    if n%2 = 1 then
      set n := n - 1;
      iterate sum;
    end if;

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

call p10(100);

游标和handler

游标(CURSOR)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以用游标对结果集进行循环的处理。

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

handler_action的取值：

HANDLER FORCONTINUE: 继续执行当前程序EXIT: 终止执行当前程序

condition_value 的取值：

SQLSTATE sqlstate_value: 状态码，如 02000SQLWARNING: 所有以01开头的SQLSTATE代码的简写NOT FOUND: 所有以02开头的SQLSTATE代码的简写SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写


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

# 打开游标
OPEN 游标名称;

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

# 关闭游标
CLOSE 游标名称;

# 声明handler
DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement;

# 示例
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin
  declare uname varchar(100);
  declare upro varchar(100);
  declare u_cursor cursor for select name,profession from tb_user where age <= uage;
  
  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;

call p11(30);

触发器

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

触发器类型	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;
# 如果没有指定 schema_name，默认为当前数据库

# 示例
# 插入数据触发器 往tb_user插入数据后触发
create trigger tb_user_insert_trigger after insert on tb_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.nam	e, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

# 修改数据触发器 对tb_user表后触发
create trigger tb_user_update_trigger after update on tb_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, 	', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,' | 		更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.	email, ', profession=', NEW.profession));
end;

# 删除数据触发器 删除tb_user表的数据后触发
create trigger tb_user_delete_trigger after delete on tb_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.nam		e, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

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


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

全局锁

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

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加全局锁之后，其他的DDL、 DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。


# 加全局锁
flush tables with read lock;

# 数据备份
mysqldump -uroot –p1234 itcast > itcast.sql

# 释放锁
unlock tables;

表级锁

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

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


- 表锁</font>
- 元数据锁（meta data lock，MDL）
- 意向锁

表锁

对于表锁，分为两类：


- 表共享读锁（read lock） ：LOCK TABLE tbl READ  
- 表独占写锁（write lock）： ALTER TABLE， LOCK TABLE tbl WRITE


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

# 释放锁
unlock tables;

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

元数据锁

meta data lock , 元数据锁，简写MDL。

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

对应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互斥
insert 、update、 delete、select … for update	EXCLUSIVE	与其他的MDL都互斥

意向锁

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

若没有意向锁，当客户端一对表加了行锁后，客户端二会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低

有了意向锁后，其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况


- 意向共享锁(IS): 由语句select ... lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
- 意向排他锁(IX): 由insert、update、delete、select...for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

行级锁

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

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面间隙Gap。在RR隔离级别下支持。

行锁

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

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

	S(共享锁)	X(排他锁)
S(共享锁)	兼容	冲突
X(排他锁)	冲突	冲突

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT（正常）	不加任何锁
SELECT … LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

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

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

间隙锁&临键锁