MySQL笔记-从mysql基础到mysql高级、InnoDB、mysql优化等

目录

1.分类

2.DDL

1.数据库

2.表

3.DML

添加数据

修改数据

删除数据

4.DQL

1.基本查询

2.条件查询where

3.聚合函数

4.分组查询group by

5.排序查询 order by

6.分页查询 limit

5.DCL

6.函数、约束、多表、事务

1.函数

2.约束

3.多表查询

4.事务

7.MySQL体系结构

8.存储引擎

9.索引

1.定义

2.聚集索引与二级索引

3.回表查询

4.索引语法

5.性能

10.SQL优化

1.insert优化：

2.主键优化

3.order by优化

4.group by，limit，count，update优化

11.视图

12.存储过程

1.变量

2.if

3.参数

4.case

5.循环

6.游标

13.存储函数

14.触发器

14.锁

1.全局锁

2.表级锁

3.行级锁

15.InnoDB引擎

1.逻辑存储结构 ：

2.架构

3.后台线程

4.事务原理

5.MVCC

RR可重复读隔离级别下提取过程

MySQL系统数据库

1.分类

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

2.DDL

1.数据库

查询所有数据库 ：show database;

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

创建：create database (if not exists) 数据库名 [default charset 字符集] [collate 排序规则];

删除：drop database「if exists] 数据库名

使用：use 数据库名

2.表

查询所有表：show tables;

查询表结构：desc 表名;

查询指定表建表语句：show create table 表名

创建表：create table 表名( 字段 字段类型[comment 字段注释]，·······)[comment 表注释]

create table users (
  id int auto_increment primary key,
  username varchar(50) not null,
  email varchar(100) not null,
  birthdate date comment '生日',
  is_active boolean default true
);

删除表：drop table[if exists] 表名;

添加字段：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]；

修改数据类型：alter table 表名 modify 字段名 新数据类型 (长度)

修改字段名和字段类型：alter table change 旧字段名 新字段名 类型(长度) [ comment 注释] [约束]

修改表名：alter table 表名 rename to 新名;

3.DML

添加数据

给指定字段添加数据：使用 INSERT INTO 语句可以向表中指定的字段添加数据，插入数据时，指定的字段顺序需要与值的顺序一一对应，字符串和日期型数据应包含在引号中。

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

给全部字段添加数据：若要给表中的全部字段添加数据，可以省略字段名

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

批量添加数据:批量添加数据时，可以在 VALUES 子句中提供多组值，每组值用逗号分隔。

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

修改数据

使用 UPDATE 语句可以修改表中的数据：如果没有提供 WHERE 条件，会更新整张表的所有数据。

update 表名 set 字段名1=值1, 字段名2=值2, ... [where 条件];

删除数据

从表中删除数据：where同上，用于删除整行数据，而不是单独的字段值。若要清除某个字段的值，应使用 UPDATE 语句将该字段的值设置为 NULL 或默认值。

delete from 表名 [where 条件];

4.DQL

1.基本查询

select 字段列表 from 表名列表 where 条件列表 
group by 分组字段列表 having 分组后条件列表 
order by 字段 排序方式 limit 分页参数

2.条件查询where

< > >= <= = <> !=
between and
in
like 占位符
is null
and &&
or ||
not !

3.聚合函数

count max min avg sum

select 聚合函数(字段列表) from 表名

4.分组查询group by

select product_id, sum(amount) as total_sales
from sales
group by product_id;
--返回每个产品的销售总额。GROUP BY 子句按 product_id 列对结果进行分组，SUM(amount) 聚合函数计算每组中 amount 列的总和。

where与having：WHERE 用于过滤数据行， HAVING 用于过滤分组后的结果。通常先使用 WHERE 子句过滤数据，然后再使用 HAVING 子句对分组结果进行进一步过滤。

5.排序查询 order by

asc升 desc降

如果多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

6.分页查询 limit

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

SELECT * FROM users
LIMIT 10 OFFSET 9;

--LIMIT 10 表示查询应该返回的记录数，这里是10条。
--OFFSET 9 表示从第10条记录开始返回，这里是第10条，所以实际返回的是第11到第20条记录。

起始索引(从0开始)= (查询页码 - 1) * 每页显示记录数

分页查询是数据库特有的功能，不同的数据库有不同的实现方式

如果查询的是第一页数据，起始索引可以省略

5.DCL

--1. 查询用户
use mysql;
select * from user;
--2. 创建用户
create user '用户名'@'主机名' identified by '密码';
​
--3. 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
​
--4. 删除用户
drop user '用户名'@'主机名';
​
-- 查询权限
show grants for '用户名'@'主机名';
​
-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
​
-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

6.函数、约束、多表、事务

1.函数

字符串函数

--concat(S1,S2,...Sn)字符串拼接，将S1，S2，….Sn拼接成一个字符串
select concat('Hello','hi') --Hellohi
--lower(str)将字符串str全部转为小写
--upper(str)将字符串str全部转为大写
select upper('Hello') --HELLO
--lpad(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
select lpad('01',5,'*') --***01
--rpad(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
select rpad('01',5,'*') --01***
--trim(str)去掉字符串头部和尾部的空格
select trim(' Hello hi') --Hello hi
--substring(str,start len)返回从字符串str从start位置起的len个长度的字符串
select substring('hello hi',1,5) --hello 

数值函数

--ceil(x)向上取整
--floor(x)向下取整
select ceil(1.3) --2
--mod(x,y)返回x/y的余数
select mod(7,4) --3
--rand()返回0~1内的随机数
--round(x,y)求参数x的四舍五入的值，保留y位小数

日期函数

-- curdate() 返回当前日期
select curdate(); --2024-1-1
-- curtime() 返回当前时间
-- now() 返回当前日期和时间
-- year(date) 获取指定date的年份
select YEAR(now) --2024
-- month(date) 获取指定date的月份
-- day(date) 获取指定date的日期
-- date_add(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(),INTERVAL 7 DAY) --当前时间往后推7天
-- datediff(date1, date2) 返回起始时间date1和结束时间date2之间的天数
selece datediff('2024-1-1','2024-1-3') -- -2

流程函数

-- if(value, t, f)
-- 如果value为真，则返回t，否则返回f
select if (false ,'OK','Error') --Error
​
-- ifnull(value1, value2)
-- 如果value1不为空，返回value1，否则返回value2
select ifnull('0k','Default'); --Ok
select ifnull('','Default'); --结果为空
select ifnull(null,'Default'); --Default
​
-- case when [val1] then [res1] ... else [default] end
-- 如果val1为真，返回res1，否则返回default默认值
​
-- case [expr] when [val1] then [res1] ... else [default] end
-- 如果expr的值等于val1，返回res1，否则返回default默认值
select name, (case address when '北京' then '一线城市' else '其他' end) as 地址
from user;

2.约束

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

create table user(
    id int primary key auto_increment comment'主键',
    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"性别'
)comment'用户表";

3.多表查询

内连接：相当于查询A、B交集部分数据

隐式内连接：
select u.name,d.name from user u,dept d where u.dept_id = d.id

显示内连接：
select u.name,d.name from user u inner join dept d on u.dept_id = d.id

外连接：

左外连接:查询左表所有数据，以及两张表交集部分数据(outer 可省略)
select u.name,d.name from user u left outer join dept d on u.dept_id = d.id

右外连接:查询右表所有数据，以及两张表交集部分数据
select u.name,d.name from user u right outer join dept d on u.dept_id = d.id

自连接：

--内连接
select a.name,b.name from emp a ,emp b where a.mangerid = b.id
--外连接
select a.name,b.name from emp a left join emp b on a.mangerid = b.id

联合查询：把多次查询的结果合并起来，形成一个新的查询结果集。

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

select * from emp where salsry < 5000
union on
select * from emp where age < 50

子查询：sql语句中嵌套select语句

标量子查询(子查询结果为单个值)

select * from emp where dept_id = 
(select id from dept where name = '销售部')

列子查询(子查询结果为一列) ：

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

select * from emp where dept_id in
(select id from dept where name = '销售部' or name = '财务部')
​
--高于所有dept_id 为3的人
select * from emp where salary > all 
(select salary from emp where dept_id = 3)

行子查询(子查询结果为一行)

--查询与返回数据行相同的行
select * from emp where (salary ,managerid) = 
(select salary,managerid) from emp where name = '张三'

表子查询(子查询结果为多行多列)

--只要满足表中的一个数据就可以查出来
select * from emp where (salary ,managerid) in
(select salary,managerid) from emp where name = '张三' or name = '李四'

4.事务

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

--查看/设置事务提交方式
SELECT @@autocommit; --1：自动提交 0：手动提交
SET @@autocommit=0;
--开始事务
start transaction; / begin;
--提交事务
commit;
--回滚事务
rollback;

四大特性：原子性、隔离性、持久性、异质性

并发事务问题：

脏读：一个事务读到另外一个事务还没有提交的数据。

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

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

事务隔离级别：

1. Read uncommitted（读未提交）：

   • 在这个隔离级别下，一个事务可以读取到另一个未提交事务的数据变更。即一个事务可以“看到”另一个事务正在进行的修改，但可能还没有提交。

   • 并发性能最高，因为读取操作不会被锁定。

   • 可能导致数据不一致，因为一个事务可能会看到另一个事务的中间状态。

2. Read committed（读已提交）：

   • 在这个隔离级别下，一个事务只能读取到另一个已经提交的事务的数据变更。即一个事务只能“看到”另一个事务已经完成并提交的数据变更。

   • 避免了脏读，即不会读取到未提交的数据。

   • 可能会发生不可重复读，即同一个事务在多次读取同一数据时，可能会看到不同的数据。

3. Repeatable Read（可重复读）：

   • 在这个隔离级别下，一个事务在开始后读取到的数据不会被其他事务修改，直到该事务结束。即一个事务可以多次读取同一数据，而不会看到其他事务对数据的修改。

   • 避免了脏读和不可重复读，数据一致性较高。

   • 可能会发生幻读，即一个事务在两次查询之间，其他事务插入的数据会使其查询结果不一致。

   • 这是MySQL的默认隔离级别。

4. Serializable（串行化）：

   • 在这个隔离级别下，所有事务都被处理为按顺序执行，就像它们在一个单一的线程中一样。所有事务都是依次执行，没有并发，就像它们在排队一样。

   • 避免了脏读、不可重复读和幻读，数据一致性最高。

   • 性能最差，因为事务之间没有并发，需要一个接一个地执行。

隔离级别	赃读	不可重复读	幻读
Read uncommitted 读未提交	存在	存在	存在
Read committed 读已提交		存在	存在
Rapeatable Read(默认) 可重复读			存在
Serializable 串行化

--查看事务隔离级别
SELECT @@TRANSACTION ISOLATION;
--设置事务隔离级别
SET [ SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

7.MySQL体系结构

连接层：处理客户端与MySQL服务器之间的连接

服务层：包括查询解析器、查询优化器、缓存管理器、事务管理器等组件

引擎层 ：负责数据的存储和提取，MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等

存储层：实际存储数据的地方，可以是本地硬盘、网络存储等

（MySQL的体系结构就像是一个高效的餐厅，连接层负责接待顾客，服务层负责处理订单，引擎层负责存储和准备食材，而存储层则是存放食材的地方。）

8.存储引擎

创建表时指定存储引擎：
create table name(...)engine=innodb[表注释]

查看数据库支持的存储引擎：
show engines

常见存储引擎：

innodb：支持事务、行级锁、外键约束 （逻辑存储结构：表空间-段-区-页-行）

MyISAM ：不支持事物，不支持外键，支持表锁不支持行锁，访问速度快（读取、插入方便）被MongoDB代替

Memory：存储在内存中，hash索引（做临时表或缓存表）被redis代替

9.索引

1.定义

MySQL中的索引是一种数据结构，用于快速查找表中的数据。

索引类型	特点	用途
B+树索引	支持范围查询、排序查询和精确匹配查询，自平衡树结构，适用于大量数据	适用于InnoDB存储引擎，支持事务处理和外键约束
哈希索引	基于哈希表，支持快速精确匹配查询	适用于UNIQUE和PRIMARY KEY约束
全文索引	用于文本搜索，可以对文本数据进行索引	适用于全文搜索应用
空间索引	用于地理空间数据，支持空间查询操作	适用于地理信息系统（GIS）应用

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

相对于二叉树，层级更少，搜索效率高 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

在InnoDB存储引擎中，索引的分类是根据其存储形式和与数据行的关联方式来区分的。以下是聚集索引和二级索引的详细介绍：

2.聚集索引与二级索引

聚集索引（Clustered Index）的叶子节点包含实际的数据行，数据存储与索引是放在一起的。

• 必须有一个聚集索引，且每个表只能有一个聚集索引。

• 聚集索引通常建立在主键或唯一索引上。

• 如果没有合适选择，innoDB自动生成rowid作为隐藏聚集索引

二级索引（Secondary Index）叶子节点不包含行数据，而是包含一个指向聚集索引（通常是主键）的指针。

• 可以存在多个二级索引。

• 二级索引可以建立在任何列上。

3.回表查询

在查询过程中多次访问同一表，例如通过二级索引找到要获取的行数据的主键，再通过聚集索引找到该行对应的所有数据

4.索引语法

创建索引 unique：唯一
create [unique | fulltext] index 索引名 on 表名(字段项...);s

查看索引
sqlshow index from table_name;sql

删除索引
drop index index_name on table_name;

5.性能

--查看执行频次 ：
show global statuslike 'com_______'
--查看慢查询日志：
show variables like 'slow_query_log'
--查看每一条SQL的耗时基本情况
show profiles;
--查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
--查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
--#查看sql语句执行计划
explain/desc +sql语句

执行计划部分字段含义：

1.type：连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。

2.possible_key:可能用到的索引

3.Key：实际用到的索引

4.rows：估计要查询的行数

5.extra：额外信息

索引规则：

1.最左前缀法则：联合索引查询从最左列开始，不能跳过索引中的列，否则后面字段索引失效

（MySQL 会按照指定的列顺序创建索引，这个道理也适用于列中的每个字符）

2.范围查询：联合索引出现范围查询(>,<)时，范围查询右侧的列索引失效

3.索引会失效情况：在索引列上进行运算操作、字符串类型不加引号、尾部模糊查询、用or连接了没有索引的字段、索引扫描比全表扫描慢

--尾部模糊匹配生效
explain select * from user where phone like '199$';
--截取phone运算，不生效
explain select * from user where substring(phone,10,2)='15'
--就算name有索引，age没有那就没法用
explain select * from user where name='aa' or age=2
--SQL提示：use(推荐使用)/ignore(别用)/force(得用) index
explain select * from user use index(idx_user_name) where name='aa'

4.多用覆盖索引：需返回的列就在索引中，不用回表查询

5.字符串长，用前缀索引：截取字符串一部分前缀，可节约索引空间

6.多用联合索引：多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询（避免回表）。

7.用唯一索引、not null 约束都会提升效率

10.SQL优化

1.insert优化：

批量插入、手动提交事物、主键顺序插入

start transaction;
insert into user values(1,"ww"),(2,"cc");
insert into user values(3,"ew"),(4,"tc");
commit;

大批量插入用load指令

--local-infile 是一个客户端连接参数，它允许客户端以二进制格式从本地文件系统读取数据，并将其作为查询的一部分发送到服务器。通常用于将数据导入数据库中

--客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
--设置全局参数local infile为1，开启从本地加载文件导入数据的开关
set global local infile=1;
--执行load指令将准备好的数据，加载到表结构中
load data local infile '目录/文件名.sql' into table 表名 fields terminated by '字段分隔符' lines terminated by '行分隔符';

2.主键优化

索引组织表：表数据跟据主键顺序组织存放

页分裂：一个页默认大小为16k，若一个页装不下则写入下一个页，若主键顺序存放则没有那么多破事直接写就好，若主键乱序插入，则还要重新组织顺序，即页分裂

页合并：页中删除的记录达到阈值(默认50%)时寻找相邻页合并

主键设计原则：降低主键长度，顺序插入，使用自增主键，（尽量别用IUUID做主键 eg:身份证号），减少修改

3.order by优化

user index:通过有序索引顺序扫描直接返回有序数据

using filesort:数据还要在缓冲区重新排序

explain select id,age,phone from tb user order by age asc , phone desc;
create index idx user age phone ad on tb user(age asc ,phone desc);

大批量排序可增大排序缓冲区大小

4.group by，limit，count，update优化

#分组也符合最左前缀法则：以下语句使用索引idx_user_name_age
select age count(*) from user where name='aa' group by age;
​
#limit：覆盖索引+子查询
select * from user limit 900000,10;#排查900000条只取后10条
#使用多表查询/子查询性能较高
select s.* from user s ,(select id from user limit 900000,10) a where s.if=a.id
​
#count优化：自己计数，InnoDB要自己累加
#count效率：(字段) <(主键)<(1) ~=(*) 
select count(1) from user # */1: 遍历表，不取值累加
​
#update:多用索引，少表锁
update user set name='a' where id=1 #跟据索引，加的行锁
update user set name='a' where name='w' #name无索引，加的表锁

11.视图

视图(View)是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。保存查询的SQL逻辑，不保存查询结果

--创建
create [or replace] view 视图名称 as + select语句 [with local check option]
--查询
show create view 视图名称 #查询创建视图时的语句
select * from 视图名称#查询视图中的数据
--改
create or replace view 视图名 as SQL语句
alter view  视图名 as SQL语句
--删
drop view from 视图名称[,视图名称]
--添加视图中的数据=>插入到视图对应的基表中
insert into  视图名 values(字段...)

视图检查：添加了with check option后，可检查增删改的数据是否符合视图定义

with local check option ：

--local作用的基表如果没有加上checkoption那么就不会校验
create or replace view stu_v_3 as select id,name from stu_v_2 where id <= 15 with local check option

with cascaded check option ：

--cascade：就算基表没有加with check option也会校验是否符合条件 
create or replace view stu_v_3 as select id,name from stu_v_2 where id <= 15 with cascaded check option

视图更新：若视图中的行与表中的行一一对应，则视图可以更新，不能更新的情况例如group by等

作用：简化sql操作；授权到特定行列，安全；数据独立屏蔽基表变化的影响，数据独立

--为了保证数据库装的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏敲手机号和邮箱两个字段
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tbh_user select * from tb_user_view;
--2.查询每个学生所选修的课程(三张表联査)，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。
select s.name, s.no , c.name from student s, student course sc , course c where s.id = sc.studentid and sc.courseid = c.id,create view tb_stu_course_view
select * from tb_stu_course_view;

12.存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，减少数据在数据库和应用服务器之间的传输，是数据库 SOL语言层面的代码封装与重用。

 --创建
create procedure 存储过程名([参数列表])
begin; 
sql语句
end;
 --调用
call 存储过程名 结束符
 --查看
select * from　INFORMATION SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xx';--査询指定数据库的存储过程及状态信息
show create procedure 存储过程名
 --删除
drop procedure [if exists] 存储过程名
在命令行执行创建存储过程的sql时，要通过delimiter设置sql语句的结束符，
因为一旦见到分号会认为存储过程已经结束，导致存储过程创建不合法

--将结束符由默认的;改为$$
delimmiter $$
--创建存储过程：
create procedure p1()
begin
    select * from user
end$$
--调用
call p1()$$

1.变量

系统变量（MySQL服务器用于配置和控制服务器行为的变量）：

1.全局变量global

2.会话变量session

--查看系统变量
show [session |global] variables
--可以通过like模糊匹配方式查找变量
show [session|global] variables like '......' 
--查看指定变量的值
select @@[session|global] 系统变量名
--设置系统变量
set [session|global]系统变量名=值
set @@[session|global]系统变量名 =值

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。 mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/mny.cnf 中配置

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

--赋值
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 ;

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

3.局部变量

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

create procedure p2()
begin
    --声明
    declare stu_count int default 0;
    --赋值例1
    set stu_count = 1; 
    --赋值例2
    select count(*) into stu_count from student;
    select stu_count;
end;
call p2()

2.if

create procedure p1()
begin
    declare score int default 12;
    declare result varchar(10);
    if score >=80 then
        set result := 'perfect';
    elseif score >= 60 then
        set result := 'good';
    else 
        set result :='bad'
    end if;
    select result;
end;

3.参数

in：该类参数作为输入，也就是需要调用时传入值 out：该类参数作为输出，也就是该参数可以作为返回值 inout：既可以作为输入参数，也可以作为输出参数

create procedure p1(in score int , out result vachar(10))
begin
    if score >=80 then
        set result := 'perfect';
    elseif score >= 60 then
        set result := 'good';
    else 
        set result :='bad'
    end if;
end;
​
call p1(34,@result);
select @result;
​
create procedure p2(inout score int)
begin
    set score :=score+1;
end;
set @score = 12;
call p2(@score);

4.case

create procedure p3(in month int)
begin
    declare result varchar(10);
    case 
        when month >= 1 and month <= 3 then
            set result := 'first';
        when month >= 4 and month <= 5 then
            set result := 'second';     
        else
            set result := 'defalt'
    end case;
    select concat(month,':',result);
end;

5.循环

while n>0 do --满足条件继续循环
    set total := tatal +n;
    set n := n-1;
end while;
repeat --先执行一次，满足条件退出循环
    set n:n-1;
until n<=0
end repeat;
sum:loop 
--死循环，配合leave label（退出循环）、itrate lable(下一次循环)
--计算从1累加到n的值
    if n<=0 then
        leave sum;
    end if;
    set total :=total + n;
    set n := n-1;
    
end loop sum;
select total;

6.游标

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

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

--声明游标，存査询结果集
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
--声明条件处理器，用于退出循环:满足sql状态码为02000时退出操作关闭游标
declare exit handler for SQLSTATE '02000' close u_cursor;
​
--开启游标
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;

13.存储函数

--从1到n累加
create function fun1(n int)
return int deterministic  --deterministic 为 characteristic
begin
    declare total int default 0;
    while n>0 do
        set total := total +n;
        set n := n-1;
    end while;
    return total;
end;
--characteristic：
--deterministic：相同的输入参数总是产生相同的结果
--no sql :不包含 SQL 语句。
--reads sql data 包含读取数据的语句，但不包含写入数据的语句,

14.触发器

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

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

现在触发器还只支持行级触发，不支持语句级触发。

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

--记录user表的变更日志，插入logs表中
--插入数据的触发器
create trigger user_insert_trigger 
    after insert on user for each row
begin
    insert into logs (id,operation,time,operate_id) values
    (null,'insert',now() , new.id)
end;
--查看触发器日志
show triggers;
--删除触发器
drop trigger user_insert_trigger ;

14.锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

1.全局锁

锁定数据库中所有表，加锁后整个实例处于只读状态，后续的DML写语句、DDL语句、已更新操作的事物提交语句将被阻塞

用做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

flush table with read lock;
mysqldump -uroot -p1234 itcast > itcast.sql
/*-uroot:用户名 -p1234:密码*/
unlock tables

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

mysqldump --single-transaction -uroot -p123456 itcast >itcast.sql

2.表级锁

1.表锁

--1.表共享锁(read lock)
--2.表独占写锁(write lock)
--读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写
lock tables 表名 read/write;
unlock tables /客户端断开连接

2.元数据锁

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

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

3.意向锁

使得表锁不用检查每行数据是否加锁，事物对行加行锁再对表加意向锁，分为：

意向共享锁(IS):由语句 select...lock in share mode添加。 意向排他锁(IX):由insert、update、delete、select... for update 添加。

3.行级锁

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

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

InnoDB实现了两种锁：共享锁S、排他锁X

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

2.间隙锁(GapLock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

3.临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

间隙锁/临键锁 默认情况下，InnODB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-kev锁进行搜索和索引扫描，以防止幻读

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁

2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。

3. 索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止。

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

15.InnoDB引擎

1.逻辑存储结构 ：

1.表空间：(ibd文件)一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

2.段：分为数据段(Leafnodesegment)、索引段(Non-leafnode segment)、回滚段(Rolbacksegment)，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。

3.区：(extend)表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

4.页：(page)是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

行：InnoDB 存储引擎数据是按行进行存放的。 Trx_id:每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。 Rol.pointer:每次对菜条引记录进行改动时，都会把旧的版本写入到und0日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

2.架构

内存结构（缓冲池）和磁盘结构（数据文件）通过数据的读取和写入操作相互关联。
内存结构中的数据页是从磁盘上的数据文件加载到缓冲池中的，当缓冲池中的数据被修改时，修改操作会先记录在日志缓冲区，然后通过重做日志文件和撤销日志文件写入磁盘。
缓冲池中的数据页会定期从磁盘刷新，以保持数据的一致性和可靠性。
总的来说，内存结构用于快速访问和修改数据，而磁盘结构用于持久化存储数据。

1.内存结构：

BufferPool:缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

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

free page:空闲page，未被使用。 clean page:被使用page，数据没有被修改过。 dirty page:脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

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

缓冲池就像一个快速访问的内存，用来存储经常用到的数据页，数据在缓冲池中修改后，会定期写回到硬盘上。
更改缓冲区用来存储不在缓冲池中的数据页的变更，修改操作会先记录在更改缓冲区，等到数据被读取时再写回缓冲池和硬盘。
缓冲池主要用于缓存磁盘上的数据页，而更改缓冲区主要用于缓存对非唯一索引页的修改操作，以减少对磁盘的直接写操作。

Adaptive Hash Index:自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。无需人工干预，是系统根据情况自动完成。

参数:adaptiv_ hash_index

Log Buffer:日志缓冲区，用来保存要写入到磁盘中的log日志数据(redolog、undolog)，默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 1/0。 参数: innodb log buffer size:缓冲区大小 innodb flush log at trx commit:日志刷新到磁盘时机

2.磁盘结构

System Tablespace:系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)（参数:innodb datafile_path）

File-Per-Table Tablespaces:每个表的文件表空间包含单个InnoDB表的数据引，并存储在文件系统上的单个数据文件中。 参数:innodb_file_per_table

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

Undo Tablespaces:撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间(初始大小16M)，用于存储undolog日志。 Temporary Tablespaces:InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

Doublewrite Buffer Files:双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。 Redo Log:重做日志，是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redolog),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复R使用。以循环方式写入重做日志文件。

3.后台线程

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

2. lO Thread 在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。

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

 3.Purge Thread 主要用于回收事务已经提交了的undolog，在事务提交之后，undolog可能不用了，就用它来回收

4.Page Cleaner Thread 协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

4.事务原理

事物特性

原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败：undo log 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态： redo log+undo log

持久性(Durability):事务旦提交或回滚，它对数据库中的数据的改变就是永久的： redo log

隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行：锁、MVCC、readView

redo.log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redolog buffer)以及重做日志文件(redolog file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

undolog

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

undo log和redolog记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚。

Undolog销毁:undolog在事务执行时产生，事务提交时，并不会立即删除undolog，因为这些日志可能还用于MVCC。

Undoloq存储:undolog采用段的方式进行管理和记录，存放在前面介绍的 rollback seqment 回滚段中，内部包含1024个undologsegment.

5.MVCC

当前读： 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select .. lock in share mode(共享锁)，select.... for update、update、insert、delete(排他锁)都是一种当前读。

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

Read Committed:每次select，都生成一个快照读。

Repeatable Read:开启事务后第一个select语句才是快照读的地方

Serializable:快照读会退化为当前读。

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

实现原理：

创建表后InnoDB会给表添加隐藏字段，具体如下：

DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。 DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undolog，指向上一个版本隐藏主键

DB_ROW_ID：如果表结构没有指定主键，将会生成该隐藏字段。

--查看表空间文件
ibd2sdi 表名.ibd

undo log 回滚日志

在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undolog日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undolog日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

undo log 版本链

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

ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。 ReadView中包含了四个核心字段:

m_ids：当前活跃的事务ID集合

min_trx_id：最小活跃事务ID

max_trx_id：预分配事务ID，当前最大事务ID+1(因为事务ID是自增的)

creator_trx_id：ReadView创建者的事务ID

RC读已提交隔离级别下提取过程

RR可重复读隔离级别下提取过程

16.MySQL系统数据库

MySQL数据库安装成功后自带以下四个数据库

mysql：存储MySQL服务器正常运行所需要的各种信息(时区、主从、用户、权限等)

information schema：提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等

performance schema：为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数

sys：进行性能调优和诊断的视图

个人笔记，跟据视频黑马程序员 MySQL数据库入门到精通