MySQL总结

一、为什么要学习数据库

数据库的好处
1.持久化数据到本地
2.可以实现结构化查询，方便管理

二、数据库的相关概念

​ 1、DB：数据库，保存一组有组织的数据的容器
​ 2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
​ 3、SQL:结构化查询语言，用于和DBMS通信的语言

三、数据库存储数据的特点

​ 1、将数据放到表中，表再放到库中
​ 2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
​ 3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
​ 4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
​ 5、表中的数据是按行存储的，每一行类似于java中的“对象”。

四、初始MySQL

MySQL产品的介绍

MySQL产品的安装

MySQL服务的启动和停止

​ 方式一：计算机——右击管理——服务
​ 方式二：通过管理员身份运行
​ net start 服务名（启动服务）
​ net stop 服务名（停止服务）

MySQL服务的登录和退出

​ 方式一：通过mysql自带的客户端
​ 只限于root用户

​ 方式二：通过windows自带的客户端
​ 登录：
​ mysql 【-h主机名 -P端口号 】-u用户名 -p密码

​ 退出：
​ exit或ctrl+C

MySQL的常见命令和语法规范

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表

show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

​	列名 列类型,
​	列名 列类型，
​	。。。

);
6.查看表结构
desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

MySQL的语法规范
	1.不区分大小写,但建议关键字大写，表名、列名小写
	2.每条命令最好用分号结尾
	3.每条命令根据需要，可以进行缩进 或换行
	4.注释
		单行注释：#注释文字
		单行注释：-- 注释文字
		多行注释：/* 注释文字  */
	
	

五、DQL语言的学习

SQL的语言分类

DQL（Data Query Language）：数据查询语言
		select 
	DML(Data Manipulate Language):数据操作语言
		insert 、update、delete
	DDL（Data Define Languge）：数据定义语言
		create、drop、alter
	TCL（Transaction Control Language）：事务控制语言
		commit、rollback

SQL的常见命令

show databases； 查看所有的数据库
use 库名； 打开指定 的库
show tables ; 显示库中的所有表
show tables from 库名;显示指定库中的所有表
create table 表名(
	字段名 字段类型,	
	字段名 字段类型
); 创建表

desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据

进阶1：基础查询

语法：
	SELECT 要查询的东西
	【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

进阶2：条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
	语法：
	select 
		要查询的字段|表达式|常量值|函数
	from 
		表
	where 
		条件 ;

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：

	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
示例：last_name like 'a%'

进阶3：排序查询

语法：
select
	要查询的东西
from
	表
where 
	条件

order by 排序的字段|表达式|函数|别名 【asc|desc】

​

进阶4：常见函数

一、单行函数

	1、字符函数
​		concat拼接
​		substr截取子串
​		upper转换成大写
​		lower转换成小写
​		trim去前后指定的空格和字符
​		ltrim去左边空格
​		rtrim去右边空格
​		replace替换
​		lpad左填充
​		rpad右填充
​		instr返回子串第一次出现的索引
​		length 获取字节个数
​		
	2、数学函数
		round 四舍五入
		rand 随机数
		floor向下取整
		ceil向上取整
		mod取余
		truncate截断
	3、日期函数
		now当前系统日期+时间
		curdate当前系统日期
		curtime当前系统时间
		str_to_date 将字符转换成日期
		date_format将日期转换成字符
	4、流程控制函数
		if 处理双分支
		case语句 处理多分支
			情况1：处理等值判断
			情况2：处理条件判断
	5、其他函数
		version版本
		database当前库
		user当前连接用户

​

二、分组函数

	sum 求和
	max 最大值
	min 最小值
	avg 平均值
	count 计数

	特点：
	1、以上五个分组函数都忽略null值，除了count(*)
	2、sum和avg一般用于处理数值型
		max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
	4、count的参数可以支持：
		字段、*、常量值，一般放1

	   建议使用 count(*)

进阶5：分组查询

语法：
	select 查询的字段，分组函数
	from 表
	group by 分组的字段

特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段！！！！
3、分组筛选
			针对的表		位置					关键字
分组前筛选：	原始表			group by的前面			where
分组后筛选：	分组后的结果集		group by的后面		 having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

进阶6：多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

一、传统模式下的连接 ：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

三、自连接

案例：查询员工名和直接上级的名称

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m 
ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

进阶7：子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
	结果集只有一行
	一般搭配单行操作符使用：> < = <> >= <= 
	非法使用子查询的情况：
	a、子查询的结果为一组值
	b、子查询的结果为空
	
② 多行子查询
	结果集有多行
	一般搭配多行操作符使用：any、all、in、not in
	in： 属于子查询结果中的任意一个就行
	any和all往往可以用其他查询代替

案例：

1.标量子查询★

案例1：谁的工资比 Abel 高?

①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'

);  

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143

);

2.列子查询（多行子查询）★

案例1：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

①查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

3、行子查询（结果集一行多列或多行多列）

案例：查询员工编号最小并且工资最高的员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

二、select后面

	/*
	仅仅支持标量子查询
	*/

案例：查询每个部门的员工个数

案例：查询每个部门的员工个数

SELECT d.*,(

	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

三、exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)
结果：
1或0
*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

进阶8：分页查询

应用场景：

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法：

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

特点：

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

进阶9：联合查询

引入：
union 联合、合并

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

六、DML语言的学习

插入语句

语法：
	insert into 表名(字段名，...)
	values(值1，...);

特点：

1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

修改语句

修改单表语法：
update 表名 set 字段=新值,字段=新值
【where 条件】
修改多表语法：
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件

删除语句

方式1：delete语句

单表的删除： ★
	delete from 表名 【where 筛选条件】

多表的删除：
	delete 别名1，别名2
	from 表1 别名1，表2 别名2
	where 连接条件
	and 筛选条件;

方式2：truncate语句

truncate table 表名

两种方式的区别【面试题】

1.truncate不能加where条件，而delete可以加where条件
2.truncate的效率高一丢丢
3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
	delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
4.truncate删除不能回滚，delete删除可以回滚

七、DDL语言的学习

库和表的管理

库的管理：

一、创建库
create database 库名
二、删除库
drop database 库名

表的管理：

1.创建表

CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME
);
DESC studentinfo;

2.修改表 alter

语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;

①修改字段名
ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

②修改表名
ALTER TABLE stuinfo RENAME [TO]  studentinfo;

③修改字段类型和列级约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;

⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;

​

3.删除表

DROP TABLE [IF EXISTS] studentinfo;

4.复制表

1.仅仅复制表的结构
	CREATE TABLE 新表名 LIKE 旧表名;

2.复制表的结构+数据
	CREATE TABLE 新表名 
	SELECT * FROM 旧表名;

3.只复制部分数据
	CREATE TABLE 新表名
	SELECT id,au_name
	FROM 旧表名 
	WHERE nation='中国';
	
4.仅仅复制某些字段
	CREATE TABLE 新表名 
	SELECT id,au_name
	FROM 旧表名
	WHERE 0;

常见类型

整型：	  Tinyint 1个字节
		Smallint 2个字节
		Mediumint 3个字节
		Int、Integer 4个字节
		Bigint   8个字节
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

小数：
	分类：
	1.浮点型
	float(M,D)
	double(M,D)
	2.定点型
	dec(M，D)
	decimal(M,D)
	
	特点：
	
	①
	M：整数部位+小数部位
	D：小数部位
	如果超过范围，则插入临界值
	
	②
	M和D都可以省略
	如果是decimal，则M默认为10，D默认为0
	如果是float和double，则会根据插入的数值的精度来决定精度
	
	③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

字符型：
特点：
		写法			M的意思							特点			空间的耗费		效率
char	char(M)		最大的字符数，可以省略，默认为1	固定长度的字符		比较耗费		   高
varchar varchar(M)	最大的字符数，不可以省略		可变长度的字符			比较节省		  低


日期型：
分类：
	date只保存日期
	time 只保存时间
	year只保存年
	
	datetime保存日期+时间
	timestamp保存日期+时间
特点：
			字节		范围		              时区等的影响
datetime    8		1000——9999	                  不受
timestamp	4	    1970-2038	                    受

​

常见约束

NOT NULL
DEFAULT
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY
分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空
	比如姓名、学号等
	DEFAULT:默认，用于保证该字段有默认值
	比如性别
	PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
	比如学号、员工编号等
	UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
	比如座位号
	CHECK:检查约束【mysql中不支持】
	比如年龄、性别
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值
	比如学生表的专业编号，员工表的部门编号，员工表的工种编号

​

添加约束的时机：
		1.创建表时
		2.修改表时
		
约束的添加分类：
	列级约束：
			六大约束语法上都支持，但外键约束没有效果
	表级约束：
			除了非空、默认，其他的都支持

​

主键和唯一的大对比：

		保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
	主键	√		    ×				至多有1个           √，但不推荐
	唯一	√			√				可以有多个          √，但不推荐
外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一个key（一般是主键或唯一）
	4、插入数据时，先插入主表，再插入从表
		删除数据时，先删除从表，再删除主表

​

查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

2.添加表级约束

/*
语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

二、修改表时添加约束

1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;	*/
	1.添加非空约束
	ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
	2.添加默认约束
	ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键
	①列级约束
	ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
	②表级约束
	ALTER TABLE stuinfo ADD PRIMARY KEY(id);
	
4.添加唯一
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

​

三、修改表时删除约束

1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

标识列

/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
	特点：
	1、标识列必须和主键搭配吗？不一定，但要求是一个key
	2、一个表可以有几个标识列？至多一个！
	3、标识列的类型只能是数值型
	4、标识列可以通过 SET auto_increment_increment=3;设置步长
		可以通过 手动插入值，设置起始值

​	
​	*/

八、TCL语言的学习

事务和事务处理

数据库事务

含义:

​ 通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态

特点:

（ACID）
	原子性：要么都执行，要么都回滚
	一致性：保证数据的状态操作前和操作后保持一致
	隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
	持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

相关步骤：

1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务

事务的分类：

一、隐式事务，没有明显的开启和结束事务的标志
比如
insert、update、delete语句本身就是一个事务

二、显式事务，具有明显的开启和结束事务的标志
		1、开启事务
			取消自动提交事务的功能

		2、编写事务的一组逻辑操作单元（多条sql语句）
			insert
			update
			delete
	
		3、提交事务或回滚事务

使用到的关键字：

set autocommit=0;
start transaction;
commit;
rollback;

savepoint  断点
commit to 断点
rollback to 断点

事务的隔离级别:

事务并发问题如何发生？

当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些？

脏读：一个事务读取到了另外一个事务未提交的数据
不可重复读：同一个事务中，多次读取到的数据不一致（因为中间有进程修改了值并且提交）
幻读：事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读
	如果事务A 按一定条件搜索 期间事务B 删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条。这种情况归为 不可重复读

如何避免事务的并发问题？

通过设置事务的隔离级别
1、READ UNCOMMITTED 读未提交
2、READ COMMITTED 读已提交 可以避免脏读
3、REPEATABLE READ 可重复读 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE 可串行化 可以避免脏读、不可重复读和幻读

	事务的隔离级别：
				  脏读		不可重复读	幻读
read uncommitted：√				√		√
read committed：  ×				√		√
repeatable read： ×				×		√
serializable	  ×             ×       ×

​

mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed

设置隔离级别：
set session|global  transaction isolation level 隔离级别名;

查看隔离级别：
select @@tx_isolation;

九、视图的讲解

含义：

​ 理解成一张虚拟的表

视图和表的区别：

	使用方式	占用物理空间

视图	完全相同	不占用，仅仅保存的是sql逻辑

表	完全相同	占用

视图的好处：

1、sql语句提高重用性，效率高
2、和表实现了分离，提高了安全性

视图的创建

语法：
	CREATE VIEW  视图名
	AS
	查询语句;

例如：1.查询姓名中包含a字符的员工名、部门名和工种信息
①创建
CREATE VIEW myv1
AS

SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id  = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;

②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

​

视图的增删改查

1、查看视图的数据 ★
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';

2、插入视图的数据
INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);

3、修改视图的数据
UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';

4、删除视图的数据
DELETE FROM my_v4;

​

某些视图不能更新

包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
	常量视图
	Select中包含子查询
	join
	from一个不能更新的视图
	where子句的子查询引用了from子句中的表

视图逻辑的更新

方式一：
	CREATE OR REPLACE VIEW test_v7
	AS
	SELECT last_name FROM employees
	WHERE employee_id>100;
	
方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;

SELECT * FROM test_v7;

视图的删除

DROP VIEW test_v1,test_v2,test_v3;

视图结构的查看

DESC test_v7;
	SHOW CREATE VIEW test_v7;

十、变量

一、全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

查看所有全局变量
SHOW GLOBAL VARIABLES;
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
查看指定的系统变量的值
SELECT @@global.autocommit;
为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

二、会话变量

作用域：针对于当前会话（连接）有效

查看所有会话变量
SHOW SESSION VARIABLES;
查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

自定义变量

一、用户变量

声明并初始化：

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

赋值：

方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;


方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用：

select @变量名;

二、局部变量

声明：

declare 变量名 类型 【default 值】;

赋值：

方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;


方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用：

select 变量名

二者的区别：

			作用域				定义位置			语法
用户变量	当前会话		会话的任何地方			加@符号，不用指定类型  
局部变量	定义它的BEGIN END中 	BEGIN END的第一句话	一般不用加@,需要指定类型

十一、存储过程和函数

存储过程

含义：一组经过预先编译的sql语句的集合

好处：

1、提高了sql语句的重用性，减少了开发程序员的压力
2、提高了效率
3、减少了传输次数

分类：

1、无返回无参
2、仅仅带in类型，无返回有参
3、仅仅带out类型，有返回无参
4、既带in又带out，有返回有参
5、带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个

创建存储过程

语法：

create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
	存储过程体

end

类似于方法：

修饰符 返回类型 方法名(参数类型 参数名,...){

	方法体;
}

注意：

1、需要设置新的结束标记
delimiter 新的结束标记
示例：
delimiter $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
BEGIN
	sql语句1;
	sql语句2;

END $

2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

3、参数前面的符号的意思
in:该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出

调用存储过程

​

call 存储过程名(实参列表)

案例：

1.创建带in模式参数的存储过程
案例1：创建存储过程实现 根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
DELIMITER $
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;
END $

调用
CALL myp2('柳岩')$

​
​
​

案例2 ：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用
END $

#调用
CALL myp3('张飞','8888')$

三、删除存储过程

语法：drop procedure 存储过程名
		DROP PROCEDURE p1;

四、查看存储过程的信息

	DESC myp2;×//错误
	SHOW CREATE PROCEDURE  myp2;

函数

创建函数

学过的函数：LENGTH、SUBSTR、CONCAT等
语法：

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体

END

调用函数

SELECT 函数名（实参列表）

案例

案例1：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')$

函数和存储过程的区别

		关键字		调用语法	返回值			应用场景
函数		FUNCTION	SELECT 函数()	只能是一个		一般用于查询结果为一个值并返回时，当有返回值而														且仅仅一个
存储过程	PROCEDURE	CALL 存储过程()	可以有0个或多个		一般用于更新

十二、流程控制结构

一、if函数

语法：if(条件，值1，值2)

​ 特点：可以用在任何位置

二、case语句

语法：

情况一：类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句，需要加分号) 
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

情况二：类似于多重if
case 
when 条件1 then 结果1或语句1(如果是语句，需要加分号) 
when 条件2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

特点：

​ 可以用在任何位置

三、if elseif语句

语法：

if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;

特点：

​ 只能用在begin end中！！！！！！！！！！！！！！！

三者比较：

					应用场合
	if函数			简单双分支
	case结构		等值判断 的多分支
	if结构		区间判断 的多分支

循环

语法：

【标签：】WHILE 循环条件  DO
	循环体
END WHILE 【标签】;

特点：

只能放在BEGIN END里面

如果要搭配leave跳转语句，需要使用标签，否则可以不用标签

leave类似于java中的break语句，跳出所在循环！！！

经典案例

/*一、已知表stringcontent
	其中字段：
	id 自增长
	content varchar(20)
向该表插入指定个数的，随机的字符串
*/
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
	
);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT;#代表初始索引
	DECLARE len INT;#代表截取的字符长度
	WHILE i<=insertcount DO
		SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
		SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）
		INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i=i+1;
	END WHILE;

END $

CALL test_randstr_insert(10)$

十三、数据库三大范式

  1NF:字段不可再分,原子性。  
  2NF:满足第二范式( 2NF )必须先满足第一范式( 1NF )。 一个表只能说明一个事物。非主键属性必须完全依赖于主键属性。  
  3NF:满足第三范式( 3NF ) 必须先满足第二范式( 2NF ) 。每 列都与主键有直接关系,不存在传递依赖。 任何非主属性不依赖于其它 非主属性。

十四、MySQL的架构介绍

1.MySql简介

​ 概述：

2.MySQL逻辑架构介绍

1.Connectors

指的是不同语言中与SQL的交互

2 Management Serveices & Utilities：

系统管理和控制工具

3 Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果
他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果

7 Cache和Buffer： 查询缓存。

他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

8 、存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。
从图2还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
注意：存储引擎是基于表的，而不是数据库。

3.MySQL存储引擎

查看命令：

MyISAM和InnoDB区别：

十五、索引优化分析

1.索引简介

是什么？

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为"排好序的快速查找数据结构"。

结论：数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。

优势：

通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势：

1.实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的。

2.虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3.索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句。

mysql索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

基本语法：

创建：
1.CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
	//如果是CHAR,VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。
2.ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

删除：
DROP INDEX [indexName] ON mytable;

查看：
SHOW INDEX FROM table_name\G

使用Alter命令：

mysql索引结构：

BTree：

B树（Balance Tree）是一种多路平衡查找树，他的**每一个节点最多包含M个孩子，M就是B树的阶。**M的大小取决于磁盘页的大小。

B-树就是B树，中间的横线不是减号，所以不要读成B减树。

B+Tree

一个m阶的B+树具有如下几个特征：

1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.每个父节点的元素都同时存在于子节点中，是子节点中的最大（或最小）元素。

4.根节点的最大元素是整个B+树的最大元素。

5.由于父节点的元素都包含在子节点，因此所有叶子节点包括了全部的元素信息。

6.每个叶子节点都带有指向下一个节点的指针，形成一个有序链表。

B树索引：

B+树索引：

其中MyISAM和InnoDB存储引擎都采用B+树索引，但底层实现方式不同

InnoDB实现方式：其中叶子节点存储数据和键值即是原表中数据（聚集索引）

MyISAM实现方式：其中叶子节点存储键值和数据表中对应数据的物理地址（非聚集索引）

Hash索引：

1.只能使用=或<=>操作符的等式比较

2.优化器不能使用hash索引来加速order by操作

3.mysql不能确定在两个值之间大约有多少行。如果将一个myisam表改为hash索引的memory表，会影响一些查询的执行效率。

4.只能使用整个关键字来搜索一行

hash index是基于哈希表实现的，只有精确匹配索引所有列的查询才会生效。对于每一行数据，存储引擎都会对所有的索引列计算一个hash code,并将有的hash code存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询的条件的字段应该创建索引

3.查询中与其他表关联的字段，外键关系建立索引

4.频繁更新的字段不适合创建索引

5.Where条件里用不到的字段不创建索引

6.单间/组合索引的选择问题，who？（在高并发下倾向创建组合索引）

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

8.查询中统计或者分组字段

哪些情况不要创建索引

1.表记录太少

2.经常增删改的表

3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

01 索引是什么？

索引是一种数据结构，它的出现就是为了提高数据查询的效率，就像一本书的目录。想想一本书几百页，没有目录估计找得够呛的。举个通俗点的例子，我在知乎刷到的，比喻得很妙。

我们从小就用的新华字典，里面的声母查询方式就是聚簇索引。偏旁部首就是二级索引 偏旁部首 + 笔画就是联合索引。

索引本身也是占用磁盘空间的（想想一本书中的目录也是占用页数的，你就知道了），它主要以文件的形式存在于磁盘中。

1.1 索引的优缺点

优点

• 提高查询语句的执行效率，减少 IO 操作的次数
• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 加了索引的列会进行排序（一本书的章节顺序不就是按照目录来排嘛），在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间

缺点

• 索引需要占物理空间
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 当对表中的数据进行增删改查是，索引也要动态的维护，这样就降低了数据的更新效率

1.2 索引的分类

主键索引

一种特殊的唯一索引，不允许有空值。(主键约束 = 唯一索引 + 非空值)

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

普通索引

MySQL 中的加索引类型，没啥限制。允许空值和重复值，纯粹为了提高查询效率而存在。

单列索引

没啥好说的，就是索引的列数量只有一个，每个表可以有多个单列索引。

组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。注意，使用它的时候需要遵守最左匹配原则。多个列作为查询条件时，组合索引在工作中很常用。

全文索引

只能在文本内容，也就是 TEXT、CHAR、VARCHAR 数据类型的列上建全文索引。有人说创建单列索引不就完了吗？考虑一种情况：当这列的内容很长时，用 like 查询就会很慢，这是就适合建全文索引。

前缀索引

还是只能作用于文本内容，也就是 TEXT、CHAR、VARCHAR 数据类型的列上建前缀索引，它可以指定索引列的长度，它是这样写的：

// 在 x_test 的 x_name 列上创建一个长度为 4 的前缀索引
alter table x_test add index(x_name(4));

这个长度是根据实际情况来定的。长了太占用空间，短了不起效果。比如：我有个表的 x_name 的第一个字符几乎都是一样的（假设都是 1），如果创建索引的长度 = 1，执行以下查询的时候就可能比原来更糟。因为数据库里面太多第一个字符 = 1 的列了，所以选的时候尽量选择数据开始有差别的长度。

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

空间索引

MySQL 在 5.7 之后的版本支持了空间索引，而且支持 OpenGIS 几何数据模型。MySQL 在空间索引这方面遵循 OpenGIS 几何数据模型规则。

02 索引的内存模型

实现索引的方式有很多种，这里先介绍下最常见的三种：哈希表、有序数组、二叉树，其中二叉树又分为二叉查找树、平衡二叉树、B 树以及 B+ 树，从而说明为啥 InnDB 选择了 B+ 树？为了方便作图举例我先建个表，建表语句如下：user 有两列，一列是身份证号，还有一列是名称。

CREATE TABLE IF NOT EXISTS `user`(
   `id_card` INT(6) NOT NULL,
   `name` VARCHAR(100) NOT NULL,
   PRIMARY KEY ( `id_card` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1 哈希表

HashMap 相信大家都用过，哈希表就是一种以键值对存储数据的结构。在 MySQL 中 key 用于存储索引列，value 就是某行的数据或者是它的磁盘地址。

用过 HashMap 的你可能知道了，当多个 key 经过哈希函数换算之后会出现同一个值，这种情况下就会 value 值的结构就是个链表。假设现在让你通过身份证号找名字，这时它的哈希表索引结构是这样的：

从上图可知，user2 和 user4 哈希出来的 key 值都是 M，这个时候 value 的值就是个链表。如果你要查 id_card = 66688 的人，步骤是：先将 66688 通过哈希函数算出 M，然后按顺序遍历链表，找到 user2。

你可能注意到了上图中四个 id_card 的值并不是递增的，所以增加新 user 时速度会很快，往后追加就好。但又因为不是有序的，做区间查询的速度就会很慢。

所以，哈希表结构适用于只有等值查询的场景，不适合范围查询。

2.2 有序数组

为了解决区间查询速度慢的问题，有序数组应运而生。它的等值和范围查询都很快。还是上面根据身份号找用户的例子，这时候的索引结构是这样的：

身份证号递增且不重复从而有以上有序数组，这是如果你要查 id_card = 66666 的用户，用二分法就可以啦，复杂度是 O (log (N))。

这数组还支持范围查询，还是用二分查找法，如果你要查区间 [12345,66666] 的用户，只需要二分查找出 id_card 大于等于 12345 且小于 66666 的用户即可。

单看查询效率，有序数组简直完美，但是如果我们要新增数据就很很难受了。假设你要新增 id_card = 12346 的用户，那就只能把后面的数据都往后挪一个位置，成本太高了。

所以有序数组只适用于存储一些不怎么变的数据，比如一些过去的年份数据。

2.3 二叉搜索树

二叉搜索树，也称二叉查找树，或二叉排序树。其定义也比较简单，要么是一颗空树，要么就是具有如下性质的二叉树：每个节点只有两个分叉，左子树所有节点值比右子树小，每个节点的左、右子树也是一个小的二叉树，且没有健值相等的节点。

说概览有点懵，先上个图。一般的二叉搜索树长这样：

之所以设计成二叉有序的结构是因为可以利用二分查找法，它的插入和查找的时间复杂度都是 O (log (N))，但是最坏情况下，它的时间复杂度是 O (n)，原因是在插入和删除的时候树没有保持平衡。比如顺拐的二叉树：

![顺拐的二叉搜索树

所以这种情况下，树的查询时间复杂度都变高，而且也不稳定。

2.4 平衡二叉树

平衡二叉树也叫 AVL 树，它与二叉查找树的区别在于平衡 ，它任意的左右子树之间的高度差不大于 1。我做了个对比，如下图：

平衡二叉树与二叉查找树对比

这样就很开心了，根据平衡二叉树的特点。它的查询时间复杂度是 O (log (N))，当然为了维护平衡它更新的时间复杂度也是 O (log (N))。貌似完美？但是还有问题。

学过数据结构都知道，时间复杂度与树高相关。你想想假设现在有一颗 100 万节点的平衡二叉树，树高 20。一次查询需要访问 20 个数据块。而根据计算机组成原理得知，从磁盘读一个数据快平均需要 10ms 的寻址时间。PS：索引不止存在内存中，还会写到磁盘上，所以优化的核心在于减少磁盘的 IO 次数。

也就是说，对于一个 100 万行的表，如果使用平衡二叉树来存储，单独访问一行可能需要 20 个 10ms 的时间，也就是 0.2s，这很难受了。

此外，平衡二叉树不支持快速的范围查询，范围查询时需要从根节点多次遍历，查询效率真心不高。

所以，大多数的数据库存储也并不使用平衡二叉树。

2.5 B 树

上面分析我们知道了，查询慢是因为树高，要多次访问磁盘。为了让一个查询尽量少触及磁盘。我们可以降低树的高度，既然有二叉。那我们多分几个叉，树的高度不就降低了？所以，这时就用到了 B 树（你心里没点吗？哈哈哈）。

在 MySQL 的 InnoDB 存储引擎一次 IO 会读取的一页（默认一页 16K）的数据量，而二叉树一次 IO 有效数据量只有 16 字节，空间利用率极低。为了最大化利用一次 IO 空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储 1000 个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建 1 百万条数据，树的高度只需要 2 层就可以（1000*1000=1 百万），也就是说只需要 2 次磁盘 IO 就可以查询到数据。磁盘 IO 次数变少了，查询数据的效率也就提高了。

B 树也叫 B- 树，一颗 m 阶（m 表示这棵树最多有多少个分叉）的 B 树。特点是：

• 每个非叶子节点并且非根节点最少有 m/2 个（向上取整），即内部节点的子节点个数最少也有 m/2 个。
• 根节点至少有两个子节点，每个内节点（非叶子节点就是内节点）最多有 m 个分叉。
• B 树的所有节点都存储数据，一个节点包含多个元素，比如健值和数据，节点中的健值从小到大排序。
• 叶子节点都在同一层，高度一致并且它们之间没有指针相连。

3 阶的 B 树结构如下图所示：

B 树索引

• 等值查询

在这样的结构下我们找值等于 48 的数据，还是使用二分查找法。它的查询路径是这样的：数据库 1-> 数据块 3-> 数据块 9。一共经过三次磁盘 IO，而同样数据量情况下，用平衡二叉树存储的树高肯定是更高的。它的 IO 次数显然是更高的。所以说 B 树其实是加快了查询效率。

• 范围查询

不知道大家注意到没有？B 树的叶子节点，并没有指针相连。意味着如果是范围查询，比如我查 41~ 58 的数据。

首先，二分查找法访问：数据块 1-> 数据块 3-> 数据块 9，找到 41；然后再回去从根节点遍历：数据块 1-> 数据块 3-> 数据块 10，找到 58，一共经历了 6 次 IO 查询才算是完成，这样查询的效率就慢了很多。

它还存在以下问题：

\1. 叶子节点无指针相连，所以范围查询增加了磁盘 IO 次数，降低了查询效率。

\2. 如果 data 存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘 IO 次数就会变大。

所以说，B 树还有优化的空间。

2.6 B+ 树

B+ 树其实是从 B 树衍生过来的。它与 B 树有两个区别：

• B+ 树的非叶子节点不存放数据，只存放健值。
• B + 树的叶子节点之间存在双向指针相连，而且是双向有序链表

它的数据结构如下图所示：

B + 树索引

由上图得知，B+ 树的数据都存放在叶子节点上。所以每次查询我们都需要检索到叶子节点才能把数据查出来。有人说了，那这不变慢了吗？B 树不一定要检索到叶子节点呀。

其实不然，因为 B+ 的非叶子节点不再存储数据。所以它可以存更多的索引，也即理论上 B+ 树的树高会比 B 树更低。从这个角度来说，与其为了非叶子结点上能存储值而选择 B 树，倒不如选择 B+ 树，降低树高。

我们通过分析来看看 B+ 树靠不靠谱。

• 等值查询

在这样的结构下我们找值等于 48 的数据，还是使用二分查找法。它的查询路径是这样的：数据块 1-> 数据块 3-> 数据块 9。一共经过三次磁盘 IO，这没毛病。

• 范围查询

比如我查 41~ 49 的数据。首先二分查找访问：数据库 1-> 数据块 3-> 数据块 8。一样经过了三次磁盘 IO，找到 41 缓存到结果集。

但由于叶子节点是个双向有序链表，这个时候只需要往后走。将 49 所在的数据块 9 加载到内存遍历，找到 49，查询结束，只走了 4 次磁盘 IO。

这里可以看出对于范围查询来说，相比于 B 树要走一遍老路，B+ 树就显得高效很多。

所以，B+ 树中等值和范围查询都支持快速查。这样 MySQL 就选择了 B+ 树作为索引的内存模型。

03 MySQL 的索引是如何执行的？

好了，可以作为所索引内存模型的数据结构都分析了一遍。最终 MySQL 还是选择了 B+ 树作为索引内存模型。那 B+ 树在具体的引擎中是怎么发挥作用的呢？一起来看看

3.1 InnDB 索引

首先是 InnDB 索引，篇幅原因，我就聊聊主键索引和普通索引。

3.1.1 主键索引

主键索引又叫聚簇索引，它使用 B+ 树构建，叶子节点存储的是数据表的某一行数据。当表没有创建主键索引是，InnDB 会自动创建一个 ROWID 字段用于构建聚簇索引。规则如下：

1. 在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
2. 如果表没有定义主键，InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引。
3. 如果以上两个都没有，InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引。该 ROWID 字段会在插入新行时自动递增。

多说无益，以下面的 Student 表为例，它的 id 是主键，age 列为普通索引。

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

表数据如下：

主键索引结构图

• 主键索引等值查询 sql：

select * from student where id = 38;

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 38 < 44，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。查询完毕，将数据返回客户端。

流程图：3 次磁盘 IO

流程图

• 主键索引范围查询 sql

select * from student where id between 38 and 44;

前面也介绍说了，B+ 树因为叶子节点有双向指针，范围查询可以直接利用双向有序链表。

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 38 < 44，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。走右边。
• 第四次磁盘 IO：将右边数据块 7 加载到内存，比较 38<44=44。查询完毕，将数据返回客户端。

流程图：一共四次磁盘 IO

流程图

3.1.2 普通索引

• 普通索引等值查询 sql

在 InnDB 中，B+ 树普通索引不存储数据，只存储数据的主键值。比如本表中的 age，它的索引结构就是这样的：

普通索引

执行以下查询语句，它的流程又是怎样的呢？

select * from student where age = 48;

使用普通索引需要检索两次索引。第一次检索普通索引找出 age = 48 得到主键值，再使用主键到主键索引中检索获得数据。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一遍索引树。因此，我们应该尽量使用主键查询。

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 48 < 54，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 28<47<48，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 47<48，48=48。得到主键 38。
• 第四次磁盘 IO：从根节点检索，将根节点加载到内存，比较 38 < 44，走左边。
• 第五次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第六次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。查询完毕，将数据返回客户端。

流程图：一共 6 次磁盘 IO。

流程图

3.1.3 组合索引

如果为每一种查询都设计一个索引，索引是不是太多了？如果我现在要根据学生的姓名去查它的年龄。假设这个需求出现的概览很低，但我们也不能让它走全表扫描吧？

但是为一个不频繁的需求创建一个（姓名）索引是不是有点浪费了？那该咋做呢？我们可以建个（name，age）的联合索引来解决呀。组合索引的结构如下图所示：

组合索引结构

执行以下查询语句，它的流程又是怎样的呢？

select * from student where name = '二狗5' and age = 48;

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 二狗 5 < 二狗 6，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 二狗 2 < 二狗 4 < 二狗 5，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 二狗 4 < 二狗 5，二狗 5 = 二狗 5。得到主键 38。
• 第四次磁盘 IO：从根节点检索，将根节点加载到内存，比较 38 < 44，走左边。
• 第五次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第六次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。查询完毕，将数据返回客户端。

流程图：一共六次磁盘 IO

组合索引执行流程

3.1.4 最左匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列 name 列从左到右递增排列，但是 age 列是无序的，age 列只有在 name 列值相等的情况下小范围内递增有序。

就像上面的查询，B+ 树会先比较 name 列来确定下一步应该搜索的方向，往左还是往右。如果 name 列相同再比较 age 列。但是如果查询条件没有 name 列，B + 树就不知道第一步应该从哪个节点查起，这就是所谓的最左匹配原则。

可以说创建的 idx_name_age (name,age) 索引，相当于创建了 (name)、（name,age）两个索引。、

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql 会一直向右匹配直至遇到范围查询 (>、<、between、like) 就停止匹配。

3.1.5 覆盖索引

覆盖索引是一种很常用的优化手段。因为在上面普通索引的例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么有没有可能经过索引优化，避免回表呢？比如改成这样子：

select age from student where age = 48;

在上面普通索引例子中，如果我只需要 age 字段，那是不是意味着我们查询到普通索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

看下执行计划：

覆盖索引的情况：

覆盖索引

未覆盖索引的情况：

未覆盖索引

3.2 myisam 索引

还是上面那张 student 表，建表语句：

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_age`(`age`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

3.2.1 主键索引

与 InnDB 不同的是 myisam 的数据文件和索引文件是分开存储的。它的叶子节点存的是健值，数据是索引所在行的磁盘地址。它的结构如下：表 student 的索引文件存放在 student.MYI 中，数据文件存储在 student.MYD 中。

myisam 主键索引结构

• 主键索引等值查询

select * from student where id = 38;

它的具体执行流程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 38 < 44，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。得到索引所在行的内存地址。
• 第四次磁盘 IO：根据地址到数据文件 student.MYD 中获取对应的行记录。

流程图：一共 4 次磁盘 IO。

myisam 主键等值查询流程

• 主键索引范围查询

select * from student where id between 38 and 44;

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 38 < 44，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。得到索引所在行的内存地址。
• 第四次磁盘 IO：根据地址到数据文件 student.MYD 中获取主键 38 对应的行记录。
• 第五次磁盘 IO：将右边数据块 7 加载到内存，比较 38<44=44。得到索引所在行的内存地址。
• 第六次磁盘 IO：根据地址到数据文件 student.MYD 中获取主键 44 对应的行记录。

3.2.2 普通索引

在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

3.3 索引的使用技巧

3.3.1 避免回表

上面说了，回表的原因是因为查询结果所需要的数据只在主键索引上有，所以不得不回表。回表必然会影响性能。那怎么避免呢？

使用覆盖索引，举个栗子：还是上面的 student ，它的一条 sql 在业务上很常用：

select id, name, age from student where name = '二狗2';

而 student 表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，并不是使用单一索引，而是使用联合索引（name，age）这样的话再执行这个查询语句就可以根据辅助索引查询到的结果获取当前语句的完整数据。

这样就有效避免了通过回表再获取 age 的数据。喏，这就是一个典型的用覆盖索引的优化策略减少回表的情况。

3.3.2 联合索引的使用

联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。比如上面的 student 表，我就建了 (name,age) 和 age 索引。

联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大。

也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，这种情况下应该使用联合索引。

联合索引的使用

• 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
• 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

3.3.3 索引下推

现在我的表数据是这样的：加了一个 sex 列。

数据表

说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？

我们还是以学生表的联合索引（name，age）为例。如果现在有一个需求：检索出表中 “名字第一个字是二，而且年龄是 38 岁的所有男生”。那么，SQL 语句是这么写的：

select * from student where name like '张%' and age=38 and sex='男';

根据前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到三个满足条件的记录（图中红框数据）。当然，这还不错，总比全表扫描要好。

然后呢？当然是判断其他条件是否满足。

在 MySQL5.6 之前，只能从满足条件的记录 id=18 开始一个个回表。到主键索引上找出数据行，再对比字段

而 MySQL 5.6 引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

它的整个执行的流程图是这样的：

索引下推

InnoDB 在（name，age）索引内部就判断了 age 是否等于 38，对于不等于 38 的记录，直接判断并跳过。在我们的这个例子中，只需要对 id=18 和 id=65 这两条记录回表取数据判断，就只需要回表 2 次，这就是所谓的索引下推。

2.性能分析

MySQL Query Optimizer

MySQL常见瓶颈:

CPU:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候

IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时

服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

Explain

是什么（查看执行计划）

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈

怎么玩

Explain+SQL语句

执行计划包含的信息

各个字段解释

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况
id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

select_type

有哪些：

查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询

1.SIMPLE：简单的select查询，查询中不包含子查询或者UNION

2.PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为

3.SUBQUERY：在SELECT或者WHERE列表中包含了子查询

4.DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。

5.UNION：若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

6.UNION RESULT：从UNION表获取结果的SELECT

table

显示这一行的数据是关于哪张表的

type

显示查询使用了何种类型
从最好到最差依次是：

system>const>eq_ref>ref>range>index>ALL

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很			快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量

eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的		行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中				出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的				某一点，而结束语另一点，不用扫描全部索引

index：Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据		文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

all：FullTable Scan,将遍历全表以找到匹配的行

possible_keys

显示可能应用在这张表中的索引,一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为null则没有使用索引

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

1.Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成排序操作成为“文件排序”

2.Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by

3.USING index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

覆盖索引（Covering Index）：

4.Using where：表面使用了where过滤

5.using join buffer：使用了连接缓存

6.impossible where：where子句的值总是false，不能用来获取任何元组

7.select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
查询执行计划生成的阶段即完成优化。

8.distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作

3.索引优化

索引失效（应该避免）

1.全值匹配我最爱

2.最佳左前缀法则（针对于联合索引）

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

7.is null,is not null 也无法使用索引

8.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

9.字符串不加单引号索引失效

10.少用or,用它连接时会索引失效

11.小总结

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

三、查询截取分析

查询优化

永远小表驱动大表，类似嵌套循环Nested Loop

order by关键字优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果不在索引列上，filesort有两种算法：
mysql就要启动双路排序和单路排序

双路排序
1.MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
2.从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。

单路排序
从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，
因为它把每一行都保存在内存中了。

结论及引申出的问题
由于单路是后出来的，总体而言好过双路
但是用单路有问题

优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

小总结:

GROUP BY关键字优化

groupby实质是先排序后进行分组，遵照索引建的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having,能写在where限定的条件就不要去having限定了。

慢查询日志

是什么

日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息

批量数据脚本

往表里插入1000W数据

1.建表

2.设置参数log_trust_function_createors

3创建函数保证每条数据都不同

​ 随机产生字符串

​ 随机产生部门编号

4.创建存储过程

​ 创建往emp表中插入数据的存储过程

​ 创建往dept表中插入数据的存储过程

5.调用存储过程

Show profiles

是什么：

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤:

1.是否支持，看看当前的SQL版本是否支持

2.开启功能，默认是关闭，使用前需要开启

3.运行SQL

select * from emp group by id%10 limit 150000

4.查看结果，show profiles;

5.诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL 数字号码；

6.日常开发需要注意的结论

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

Creating tmp table 创建临时表

Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

locked

全局查询日志

配置启用

编码启用

永远不要在生产环境开启这个功能。

四、MySQL锁机制

概述

锁的分类

从数据操作的类型（读、写）分：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的颗粒度：

表锁

行锁

表锁（偏读）

特点

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

案例分析

建表SQL

加读锁

加写锁

案例结论

表锁分析

or连接会使行锁升级为表锁

行锁（偏写）

特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁

由于行锁支持事务，复习老知识

事务（Transation）及其ACID属性

并发事务处理带来的问题

脏读:（Dirty Reads）：事务A读到了事务B已修改但尚未提交的数据，还在这个数据基础上做了操作，此时，如果						B事务回滚，A读取的数据无效，不符合一致性要求。
不可重复读（Non-Repeatable Reads）：事务A读取到事务B已经提交的数据,不符合隔离性
幻读（Phantom Reads）:事务A读取到事务B新增的数据

脏读和幻读的区别：脏读是事务B修改了数据，幻读是事务B新增了数据.

事务隔离级别

案例分析

建表SQL

行锁定基本演示

无索引行锁升级为表锁

varchar  不用 ' '  导致系统自动转换类型, 行锁变表锁

间隙锁危害

间隙锁实际上是为了解决幻读的问题

例如A session开启事务但是没有提交,update user set age=1 where id>1 and id<5;

这个时候B session进行插入 insert into user value(2,4); 这个时候会阻塞

面试题：常考如何锁定一行

案例结论

行锁分析

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

MVCC详情：https://www.bilibili.com/video/BV1Lt4y1S7zF?from=search&seid=17514361240759101872

五、主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

三大步骤：

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志时间，binary log events

slave将master的binary log ebents拷贝到它的中继日志（relay log

slave重做中继日志中事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

复制的基本原则

每个slave只有一个master

每个slave只能有一个唯一的服务器ID

每个master可以有多个salve

复制最大问题

延时

分库分表

https://b23.tv/8EpgnH

MVCC

快照读 当前读 事务id 事务管理器

数据库事务实现原理

2. 日志文件

2.1. redo log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

当事务提交之后会把所有修改信息都会存到该日志中。假设有个表叫做tb1(id,username) 现在要插入数据（3，ceshi）

start transaction; 
select balance from bank where name="zhangsan"; 
// 生成 重做日志 balance=600 
update bank set balance = balance - 400; 
// 生成 重做日志 amount=400 
update finance set amount = amount + 400; 
commit;

redo log 有什么作用？

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面图中红色的操作。这样会导致丢部分已提交事务的修改信息！

所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

总结：redo log是用来恢复数据的 用于保障，已提交事务的持久化特性。

2.2. undo log

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

还用上面那两张表

每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log。

undo log 有什么作用？

undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。

总结：undo log是用来回滚数据的用于保障 未提交事务的原子性。

2.3. archive log(binlog)

archive log（归档日志）是Oracle数据库中的概念，它其实是redo log的衍生物。

在Mysql数据库中，归档日志有另外一个名字 binlog（二进制日志 binary log），在mysql主从模式中，从节点就是通过同步主节点的binlog实现主从同步的。

redo log file是LGWR进程从Oracle实例中的redo log buffer写入的，是循环利用的。就是说一个redo log file写满后，才写下一个。归档日志是当数据库运行在归档模式下时，一个redo log file写满后，由ARCn进程将重做日志的内容备份到归档日志文件下，然后这个redo log file才能被下一次使用。

不管数据库是否是归档模式，重做日志是肯定要写的。而只有数据库在归档模式下，重做日志才会备份，形成归档日志。

redo log是循环利用的，但是归档日志不是，它不断接收从redo log中写入的日志备份。因此到一定时间后，会导致数据库存储不够，影响数据库使用。我们一般都会执行一个定时脚本，在规定时间周期后，删掉保留周期前的归档日志文件。