02-MySQL之高级篇【常用函数、事务、索引、视图、触发器、存储过程、体系结构、存储引擎、锁】

`① 能够理解Mysql常用函数

`② 能够理解事务
 事务命令
 事务4大特性
 事务并发问题

`③ 能够理解索引
索引分类
创建索引原则

`④ 能够理解视图

`⑤ 了解触发器

一、MySQL常用函数

字符串函数

数学函数

日期函数

高级函数

二、MySQL事务

事务概述

**事务：**指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败

**事务作用：**保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

MySQL中有两种事务提交的方式：

1. 手动提交事务：先开启，再提交；

2. 自动提交事务：即执行一条sql语句提交一次事务 (MySQL默认事务提交机制)

事务的提交

• 手动提交事务

-- 案例
# 创建账号表
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
# 初始化数据
insert into account values (null,'小明',1000);
insert into account values (null,'大白',1000);

-- 需求1: a给b转账100元,演示提交事务
-- 1.1 手动开启事务
start transaction;
-- 1.2 a扣款100
update account set money=money-100 where name='小明';
-- 1.3 b收款100
update account set money=money+100 where name='大白';
-- 1.4 事务提交 / 回滚
commit; /  rollback;

• 自动提交事务

  MySQL默认的事务提交方式：自动提交
   每一条DML(增删改)语句都是一个单独的事务
   都会自动开启一个事务
   执行完毕会自动提交事务；

1. 通过以下命令可以查看当前autocommit模式：
show variables like '%commit%';

2. 设置自动提交的参数为OFF:
-- 0:OFF     1:ON
set autocommit = 0;

事务的回滚

• 直接回滚到事务开始执行之前

• 回滚到事务执行中的某个位置

  • 设置回滚点: savepoint    回滚点名字
    回到回滚点: rollback to  回滚点名字
    

  • -- 2. 开启事务
    start transaction;
    -- 3. 让a用户先减100块
    update account set money=money-100 where name='小明';
    -- 4. 设置回滚点：savepoint a; 
    savepoint a;
    -- 5. 然后让a用户再次减100块
    update account set money=money-100 where name='小明';
    -- 6. 回到回滚点：rollback to a
    rollback to a;
    -- 7. 最后commit提交事务
    commit;
    

事务的原理

① 客户端连接mysql服务,服务端会对这个用户进行安全校验,通过后会创建一个临时的事务日志文件;

② 当用户直接向数据库插入数据时(因为mysql默认事务自动提交),数据会直接刷入数据库;

③ 当用户调用start transaction命令手动开启事务时,事务相关的数据首先会写入这个临时的事务日志文件, 然后当

用户调用commit命令时,会将事务的数据刷入的数据库;

④ 当用户进行事务的回滚时,会直接清空这个临时的事务日志文件;

⑤ 当一个事务尚未提交,但是连接中断时,自动清空这个临时的事务文件;

事务ACID特性

数据库的事务具备ACID四大特性：

 Atomicity（原子性）

 Consistensy（一致性）

 Isolation（隔离性）

 Durability（持久性）

# 1. 说一下事务的4大特性
`隔离性: 
多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离
[事务在并发下,事务彼此隔离,不受影响]
`原子性:
原子性是指事务包装的一组sql(一组业务逻辑)是一个不可分割的工作单位,要么都发生,要么都不发生;
[事务是一个不可分割的整体,要么都发生,要么都不发生]
`持久性:
[事务一旦提交,对数据库数据的影响是永久性的,那么数据库重启,数据也不会丢失]
`一致性:
首先一致性是基于原子性的,强调的是一个业务逻辑的概念,（事务前后数据的完整性必须保持一致）
(比如转账业务,双方转账前总金额与转账后总金额要一致)
 
 
# 2. 事务相关的指令：
`手动开启事务
    • Start transaction
    • begin
`提交事务
	• commit
`回滚事务
	• Rollback
`设置事务回滚点
	• Savepoint 回滚点名称
`回滚到回滚点
	• Rollback to 回滚点

事务并发问题

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题

事务在并发中存在的三个问题

脏读

• 一个事务读取了另一个事务未提交的数据;

不可重复读

• 一个事务在相同条件下前后读取的内容不同 (强调:发生在其他事务进行update操作时);

幻读(虚读)

• 一个事务在相同的条件下前后读取的行记录数不一致 (强调在其他事务发生insert,delte操作时)

事务隔离级别

MySQL数据库规范规定的4种隔离级别

分析

• 并发的三个问题中，最严重的就是脏读（读取了错误数据），这个问题一定要避免

• 关于不可重复读和虚读其实并不是逻辑上的错误，而是数据的时效性问题，所以这种问题并不属于很严重的错误

• 如果对于数据的时效性要求不是很高的情况下，可以接受不可重复读和虚读的情况发生

安全和性能对比

• 安全: 串行化 > 可重复读 > 读已提交 > 读未提交

• 性能: 串行化 < 可重复读 < 读已提交 < 读未提交

1) 查询隔离级别
show variables like '%isolation%';
-- 或
select @@tx_isolation;
2) 设置事务隔离级别
set global transaction isolation level 隔离级别;
-- 如:
set global transaction isolation level read uncommitted;
## 注意：客户端需要重新连接，刷新事务隔离级别
select @@tx_isolation; -- READ-UNCOMMITTED

3) 操作流程
-- 1. A事务、B事务：
start transaction;
-- 2. A事务 
update account set money = money-500 where name='小明';
update account set money = money+500 where name='大白';
-- A事务未提交（转换到B事务执行）

-- 3. B事务
select * from account;    -- 查询的结果会不相同 （脏数据）

4) 如何解决脏读的问题?
需要将全局的隔离级别进行提升为：read committed
1) 提高隔离级别 (读已提交)
set global transaction isolation level read committed; 
2) 断开重新连接数据库
select @@tx_isolation;

三、MYSQL索引

MySQL性能 (理解)

提高操作数据库的性能，有如下两种方式：

硬优化：就是软优化之后性能还很低，只能采取硬优化，最后的步骤了，就是公司花钱购买服务器，在硬件上进行优化。

软优化：在操作和设计数据库方面上进行优化（重点）(表结构和sql语句)

1.执行次数比较多的语句分类

​ 查询密集型

​ 修改密集型

2.查询累计插入和返回数据条数，即查看当前数据库属于查询密集型还是修改密集型

– 查询累计插入和返回数据条数

show global status like ‘Innodb_rows%’;

索引的引入

Mysql官方对索引的定义：索引（index）是帮助Mysql高效获取数据的数据结构

索引的本质：索引就是数据结构

索引作用：提高查询效率

• 索引分类

` 主键索引（主键约束）
主键约束(唯一+非空)+提高查询效率

` 唯一索引   
唯一约束+提高查询效率

` 普通索引
仅提高查询效率

` 组合索引（联合索引）
多个字段组成索引 [联合主键索引、联合唯一索引、联合普通索引] 

` 全文索引 TEXT BIGTEXY
Mysql全文索引使用较少。基本针对文档类数据会选择solr、es等文档搜索类数据库

` hash索引
根据key-value等值查询效率非常高，但是不适合范围查询

• 无索引查询千万条数据案例

-- 1. 准备表
CREATE TABLE user(
id INT,
    username VARCHAR(32),
    password VARCHAR(32),
    sex VARCHAR(6),
    email VARCHAR(50)
);
-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
-- 可以将下面的存储过程理解为java中的一个方法，插入千万条数据之后，在调用存储过程
CREATE PROCEDURE auto_insert()
BEGIN
     DECLARE i INT DEFAULT 1;
     START TRANSACTION; -- 开启事务
WHILE(i<=10000000)DO
	INSERT INTO user 
	VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@163.com'));
	SET i=i+1;
END WHILE;
COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号
-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;
-- 4. 调用存储过程
CALL auto_insert();


# 说明:上述sql大概运行很久;

• 创建索引语句

-- 创建普通索引
create index 索引名 on 表名(字段);
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

• 添加索引再次进行测试千万数据

-- 给千万级表创建索引： （测试索引的性能）
-- 说明: 千万数据下添加索引非常耗时,大概需要几分钟; 

-- 添加索引后再次查询执行效率明显提升！
select * from user where id = 22; -- 无索引字段耗时：5.422s
select * from user where username = jack22; -- 有索引字段耗时几乎为0.00s

• 修改制定索引语句

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段); --默认索引名：primary
-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名
-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名


-- 创建学生表
CREATE TABLE student3(
    id INT PRIMARY KEY    AUTO_INCREMENT, -- 主键索引
    name VARCHAR(32),
    telephone VARCHAR(11) UNIQUE, -- 唯一索引
    sex VARCHAR(5),
    birthday DATE,
    INDEX(name) -- 普通索引
);

• 查看可删除索引

-- 查看索引
show index from 表名; 

-- 删除索引
-- 方式1: 直接删除
drop index 索引名 on 表名;
-- 方式2: 修改表时删除
alter table 表名 drop index 索引名;

索引的数据结构 [了解]

• 分析二叉查找树

• 分析二叉查找树和红黑树

• 引入 BTree树

我们知道二叉查找树查询的时间复杂度是O（logN），查找速度最快和比较次数最少，既然性能已经如此优秀，但为什么实现索引使用B-Tree而不是二叉查找树，关键因素是磁盘IO的次数。

数据库索引是存储在磁盘上，当表中的数据量比较大时，索引的大小也跟着增长，达到几个G甚至更多。当我们利用索引进行查询的时候，不可能把索引全部加载到内存中，只能逐一加载每个磁盘页，这里的磁盘页就对应索引树的节点。

从结构树看,树的高度越高，那么查询叶子节点时I/O次数也就越多，造成I/O瓶颈。

• 索引使用B+Tree树

B+Tree好处

1）B+Tree的非叶子节点只存储索引和指针域，不保存数据，这样降低树的高度,也就意味着减少了数据查询所需的io次数

2. 叶子节点按照索引排好序，更好的支持范围查找，速度会很快

3）mysql将根节点都加载到内存中，每张表有一个根节点，大小是****16KB.那么这样的好处，按照上述如果是千万条数据，

那么只有2次磁盘IO.这就是为什么我们加完索引之后能瞬间查到数据的原因

MySQL中的B+Tree
-- 查看mysql索引节点大小 -- 16kb
show global status like 'innodb_page_size'; 

索引优缺点及创建原则

• 索引优缺点

【提高数据检索的效率，降低数据库的IO成本】

【降低数据排序的成本，降低CPU的消耗】（索引底层就是排序，通过索引列对数据进行排序）

【在海量数据前提下创建索引成本高】

【索引会额外占用磁盘空间】

【维护成本高】

• 索引创建的原则

1.字段的辨识度越高越好

2.where关键字后的字段适合添加索引；

3.多表join关联查询时，on关键字后边的字段维护索引；

4.在order by关键字后边适合添加索引，避免cup大量排序计算；

5.空间原则索引的字段类型占用的空间越小越好；(比如int 占4个字节，date占3个字节，那么相比较而言，使用int类型构建索引，占用空间会更大一些)

四、MySQL视图

视图应用与创建

• 概述

视图（View）是一种虚拟存在的表，行和列的数据来源于定义视图的查询中使用的表，并且是在

使用视图时动态生成的;

通俗的讲，视图就是一条SELECT语句执行后返回的结果集

• 应用场景及好处

 场景:

1)多场景使用相同且非常复杂的查询sql;

2)在一些数据敏感,涉及到安全的场景;

3)网络访问密集的场景(减少网络io开销)；

 好处:

1)提高sql的复用性;

2)提高数据的安全性和数据库性能;

3)减少网络io开销

• 创建视图语法格式:

create view 视图名 as select语句;

视图和表的区别

视图修改

-- 视图存在则修改视图，不存在则创建视图
create or replace view 视图名 as select语句;

-- alter修改视图
alter view 视图名 as select查询语句;

查看及删除视图

-- 查看视图指令与table指令格式一致
show tables;
desc country_view;
show create table country_view;

-- 删除视图
drop view 视图名;

五、MySQL触发器

触发器概述

• 触发器基于事件驱动触发：触发器主要是通过事件进行触发而被执行的,我们可以在修改数据的前后绑定事件,触发执行定义的SQL;

• 触发器是基于表，需要与表进行绑定：触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集

• 触发器通过OLD和NEW获取监听数据：使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。OLD表示操作前的对象，NEW 表示操作之后的对象。

触发器分类

触发器使用

create trigger trigger_name
before/after insert/update/delete
on tbl_name
for each row  -- 行级触发器
trigger_stmt ;
/*
说明：
 1）before/after    			位置称为触发时机，一个触发器只能选择一个
 2）insert/update/delete		位置称为触发事件，一个触发器只能选择一个
 3）for each row				称为行级触发器，触发器绑定实质是表中的所有行，因此当每一行发生指定改
变的时候，就会触发触发器。
*/

-- 创建员工表，员工信息
create table emp(
id int primary key auto_increment,
name varchar(30) not null,
age int,
salary int
);
insert into emp(id,name,age,salary) values(null, '金毛狮王',40,2500);
insert into emp(id,name,age,salary) values(null, '蝙蝠王',38,3100);
-- 创建一张日志表,存放日志信息
create table emp_logs(
id int(11) primary key auto_increment,
operation varchar(20) not null comment '操作类型, insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_id int(11) not null comment '操作表的ID,emp表数据的id',
operate_params varchar(500) comment '操作参数,插入emp中的数据'
)engine=innodb default charset=utf8;

1:创建 insert 型触发器，完成插入数据时的日志记录
3:创建 update 型触发器，完成更新数据时的日志记录
2:创建 delete 行的触发器 , 完成删除数据时的日志记录
create trigger update_tri 
after update
on emp
for each row
insert into emp_logs values
(null,'update',now(),old.id,
concat('操作之前数据:',old.id,old.name,old.age,old.salary,'操作之
后数据:',
new.id,new.name,new.age,new.salary));

总结

掌握：
1.索引的创建语法；
1）方式1：
create index  索引名称 on  表（字段）；
create unique index 索引名称 on  表（字段，字段1，….）
2) 方式2：
alter table 表名 add primary key(字段)；
alter table 表名 add index(字段);
alter table 表名 add unique(字段);
2）方式3：创建表时指定索引
create table 表名（
id int primary key,
name varchar(20) ,
tele varchar(11) unique
index(name)
）；

2.事务
    1）事务的4大特性
    原子性，一致性，持久性，隔离性
    3）事务并发的3个问题
    脏读，不可重复读，幻读
    3）事务隔离级别
        读未提交 ：脏读，不可重复读，幻读
        读已提交：不可重复读，幻读
        可重复读（rr）:幻读
        串行化
3.索引
    1.索引分类
    1）主键索引：唯一+非空+提交查询效率，primary key
    2）唯一索引：唯一+提交查询效率 unique
    3）普通索引:提高查询效率 index
    4) 联合索引：由多个字段共同组成的索引
        主键联合索引
        唯一联合索引
        普通联合索引
        
    2.索引的创建原则
        1.字段的辨识度越高越好；
        2. where关键字后的字段适合添加索引；
        3.多表join关联查询时，on关键字后边的字段维护索引；
        4.在order by关键字后边适合添加索引，避免cup大量排序计算；
        5.空间原则: 索引的字段类型占用的空间越小越好；
   
    3.索引结构( 了解)
    B+Tree
    特点：非叶子节点只存储索引和指针域，进一步降低树高度；
    叶子节点与节点之间存在双向指针，提高数据库的区间访问能力；
    根节点提前加载到内存中，避免返回加载，提高查询效率
    
4.视图
语法：create  view 视图名称 as select语句； 

5.触发器      

六、存储过程

问题引出：

问题1：相同的业务在不同的模块中实现细节可能会有差别，代码复用性差，且后期维护性也差；

问题2：相同结构的SQL反复发送，不仅增大网络I/O开销；

问题3：相同结构的SQL反复发送，使得数据库反复编译执行，带来数据库性能开销；

1.存储过程和函数类似于java中的方法，将一段代码封装起来，然后使用的时候直接调用即可。

2.mysql中的存储过程和函数是 一组预先编译并存储在数据库中的SQL 语句的集合，我们可以通过调用

存储过程和函数来执行一组SQL语句的集合。

3.好处:提高代码的重用性,简化操作,减少编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程创建

• -- 格式 
  delimiter $ CREATE procedure 存储过程名(参数列表)
  begin
  	-- 存储过程体(一组合法的sql语句集合)
  end$
  

• 参数模式 	 参数名 	参数类型 
  举例： 
  IN   		stuname  	varchar(20) 12
  
  IN:该参数可以作为输入，也就是该参数需要调用者传入值 
  OUT:该参数可以作为输出，也就是该参数可以作为返回值 
  INOUT:该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
  

• 1）创建存储过程
  delimiter $
      create procedure 存储过程名称(参数列表)
      begin
      sql逻辑;
  end$
  2) 调用
  call 存储过程名称(参数);
  3) 查看存储过程信息
  show create procedure 存储过程名称;
  4) 删除
  drop procedure 存储过程名称;
  

存储过程变量声明与赋值

• declare

  -- declare 可定义一个局部变量,该变量的作用范围只能在 BEGIN…END 块中;
  declare  变量名  变量的数据类型   [default 默认值] 
  

• set

  set 变量名 = 值

• select … into

  select … into 变量 from 表名;

delimiter$
create procedure pro_test2
begin
     -- 声明变量
     declare num int default 1;
     -- 查看 num 的值
     select num;
     -- 修改 num 变量
     set num = num + 10;
     select num;
 end$

-- 调用存储过程
call pro_test2;

delimiter $
create procedure pro_test2
begin
	declare num int;
	-- 查询表中的记录，赋值num 给变量
	select count(*) into num from city;
	select num;
end$

call pro_test3

存储过程if条件判断

if 条件1 then 语句1
elseif 条件2 then 语句2
else 语句3
end if;

delimiter$
create procedure pto_test4(int weight int)
begin
	declare descr varchar(10);
	if weight >= 170 then
		set descr = '身体肥胖';
	elseif weight > 150 then
		set descr = '标准身材';
	else
		set descr = '身材偏瘦';
	end if;
	
	select descr;
end$

-- 调用
call pro_test4(120);

存储过程传递参数

• @变量名

-- 语法格式
@变量名 
-- 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表当前与数据库的会话域下共有属性
-- 当存储过程内部对其修改时，外部就可以查看到变化

• out 该参数作为输出，也就是该参数可以作为返回值；

delimiter$
create procedure pto_test4(int weight int, out descr varchar(10))
begin
	-- declare descr varchar(10);
	if weight >= 170 then
		set descr = '身体肥胖';
	elseif weight > 150 then
		set descr = '标准身材';
	else
		set descr = '身材偏瘦';
	end if;
	
	select descr;
end$

-- 调用
-- call pro_test4(120);
call pro_test5(120, @description);

select @description;

• inout 既可以作为输入参数，也可以作为输出参数

声明自定义用户变量并初始化格式：

set @变量名=值$ 
举例： set @m=10$

delimiter $
create procedure proc_test6(inout a int, inout b int)
begin 
	set a = a *2;
	set b = b *2;
end$

--  外部设置会话变量的初始值
set @a = 10;
set @b = 20;
call proc_test6(@a,@b);
-- 查看返回值
select @a,@b;

存储过程case结构

 方式一 :
-- 拿case后面的值和when后面的值依次比较，相等就执行then后面的语句，如果都不相等，就执行else后面的语句
CASE 值
WHEN 值1 THEN 语句1;
 [WHEN 值2 THEN 语句2] ...
 [ELSE 语句3]
END CASE;

 方式二 :
-- 说明：WHEN后面的任意一个 条件表达式 是true，就执行then后面的语句，如果都是false，那么执行else后面的语句，多个when只会执行一个
CASE
 WHEN 条件表达式1 THEN 语句1;
 [WHEN 条件表达式2 THEN 语句2;] ...
 [ELSE 语句3]
END CASE;

delimiter $
create procedure proc_test7(in m int, out desc varchar(20))
begin
	case
		when m>=1 and m<=3 then
		 	set descr = '第一季度';
		when m>=4 and m<=6 then
		 	set descr = '第二季度';
		.....
		else set season='您输入的月份有错误!';
	end case;
end$

call proc_test6(3,@descr);

select @descr;

存储过程三种循环

• while do 循环

  • while 循环条件 do
     循环体
    end while;
    

  • delimiter $
    create procedure pro_test8(in num int)
    bengin
    	-- 声明变量
    	declare total int default 0;
    	declare n int default 1;
    	-- while 循环
    	while n < num do
    		-- 循环体 计算
    		set total = total + n;
    		set n = n +1;
    	end while;
    	-- 查看total
    	select total；
    end$
    
    -- 调用
    call proc_test8(15)
    

• repeat until 循环

  • repeat
    	循环体
    	-- 直到什么结束
    	until 循环条件 -- 注意：循环条件后面不能加分号，不满足语法规则
    end repeat;
    

• loop leave循环

  • -- 语法 
    循环标记:loop 
        -- 循环体 
        sql语句 
    end loop 循环标记;
    

  • leave语句

    • -- 语法格式 
      -- c表示给循环体取一个名称,名称随便定义(sql关键字除外)
      c:loop 
          sql语句 
          if 条件判断 then 
              -- 条件判断 
              leave c; 
              -- 满足条件则退出当前循环 
          end if; 
      end loop c;
      

  • delimiter $ 
    create procedure pro_test14(in num int) 
    begin
        declare total int default 0; 
        declare record int default 1; 
        aa:loop 
            set total=total+record; 
            set record=record+1; 
            if record > num then 
            	leave aa; 
            end if; 
        end loop 
        aa; select total; 
    end$ 
    
    call pro_test14(100);-- 5050
    

存储过程游标

• 1.游标(光标)是用来存储查询结果集的 , 在存储过程和函数中可以使用光标对结果集进行循环的处理;

  2.游标的使用包括游标的declare(声明)、open(打开)、fetch(获取游标中的数据)和 close(关闭);

• -- 声明游标
  declare 游标名 cursor for  查询数据的结果集的sql语句
  -- 打开游标
  open 游标名
  -- 获取游标
  fetch 游标名 INTO 变量名,变量名 ... ; -- 获取游标中的数据并赋给指定变量，执行一次
  -- 关闭游标
  close 游标名
  

• 句柄（出错处理器）

  • 循环实现方案:

    mysql提供一种思想就是句柄(出错处理器)，当找不到使用句柄(出错处理器)方式可以实现，要求声明句柄(出错处理器)和声明游标必须放在一起，并且声明句柄(出错处理器)放在声明游标下面：

  • -- 创建游标 
    declare emp_result cursor for select * from emp; -- 声明句柄 
    -- declare 表示声明 exit 退出 handler 处理器 set 变量 = 初始化值 设置值 
    declare exit handler for not found set 变量 = 初始化值;
    

存储函数

函数 ： 是一个有返回值的过程 ；

过程 ： 是一个没有返回值的函数 ；

本身存储过程是没有返回值的函数，是一个过程，没有结果。上述所说的返回值其实不是真正返回值，

只是我们传递一个变量到存储过程中，在存储过程中修改了该变量值，然后在调用存储过程的位置在获

取修改后的变量值。

如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数，即便是存储过程使用了out类型

的参数，其本质也不是真的有了返回值，而是在存储过程内部给out类型参数赋值，在执行完毕后，我

们直接拿到输出类型参数的值。

所以存储过程可以替代存储函数。

• -- 创建存储函数
  create function 存储函数名(参数名 参数类型) returns 返回值的数据类型
  begin 
      ... 
      return xx; 
  end$ 
  
  -- 调用存储函数 
  select 存储函数名(实参)$
  

• show variables like '%func%';-- 查看参数 
  set global log_bin_trust_function_creators=1;-- 设置参数
  

• **注意:**mysql默认不开启函数功能,需要进行参数设置

七、MySQL体系结构

MySQL Server由以下组成

Connection Pool : 连接池组件。连接池： 管理、缓冲用户的连接，线程处理等需要缓存的需求。

Management Services & Utilities : 管理服务和工具组件。系统管理和控制工具，例如备份恢复、Mysql复制、集群等

SQL Interface : SQL 接口组件。接受用户的SQL命令，并且返回用户需要查询的结果。

Parser : 查询分析器组件。SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)

Optimizer : 优化器组件。SQL语句在查询之前会使用查询优化器对查询进行优化(决定sql是否走索引,以及走什么索引)

Caches & Buffffers : 缓冲池组件。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

Pluggable Storage Engines : 存储引擎。存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存

储引擎也可以称为表类型（即存储和操作此表的类型）

File System : 文件系统。存储的文件系统不仅包含数据，还包括索引、错误日志和慢查询日志等

1.connectons连接器：不同语言不同客户端连接mysql服务 2.connection pool:连接池，线程复用，连接数限制，内存检查等 3.sql interface:sql接口层，主要用于接收客户端发送过来的sql； 4.parser:sql解析器，解析sql，检查sql语法，以及sql权限等； 5.Optimizer:优化器，优化sql，决定执行时用那些索引，以及使用的顺序； 
6.engines:引擎层，mysql采用插拔式的设计方式，用户可以根据自身的需求，选择不同的存储引擎； 存储引擎作用：1）存储数据 2）维护索引

八、存储引擎

存储引擎介绍

存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方式 ;

存储引擎是基于表的，而不是基于库的,所以一个库中不同的表可以指定不同的存储引擎;

注意：实际开发中，不建议在同一个库下的不同的表选择不同的存储引擎；

或者说在mysql中同一个库下,不同的表可以选择不同的存储引擎,但是不建议,因为在表关联查询的时候,会出现一个难以解决的问题;

-- 查看mysql支持的存储引擎命令:
show engines;
-- 查看Mysql数据库默认的存储引擎 ， 指令
show variables like '%storage_engine%';

`Innodb特点:
    支持外键
    支持事务
    支持行锁(支持表锁)
    
`Myisam特点:
    支持表锁(不支持行锁)
    不支持事务
    不支持外键

聚集索引和非聚集索引

1) InnoDB聚集索引/聚簇索引）

MySQL5.5版本之后默认存储引擎;

特点:支持事务,支持外键;

-- 创建 innodb存储引擎表
CREATE TABLE tab_innodb
( 
    id INT, 
    name VARCHAR(32) 
)ENGINE = INNODB;
-- 这里不指定ENGINE = INNODB默认也是INNODB 12345

.frm 存储表的结果信息
.ibd 存储索引+数据

2) MyISAM（非聚集索引/非聚簇索引）

MySQL5.5版本之前默认存储引擎;

不支持事务,不支持外键;

-- 创建 myisam存储引擎表 
CREATE TABLE tab_myisam
( 
    id INT, 
    name VARCHAR(32) 
)ENGINE=MYISAM;


.frm 存储表结果信息
.MYD 存储数据
.MYI 存储索引信息

存储引擎的选择

`InnoDB : 
是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由
于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

`MyISAM ： 
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

innodb:对事务要求比较高,或者需要一些外键支持的业务场景,可以使用,同时业务中修改操作如果比 较频繁,也可使用innodb

myisam:适合以读为主的应用,同时对事务要求不是太高的场景;

在日常开发中,如果没有特殊的要求,那么一般会优先使用innodb存储引擎;

Innodb与MyiSam比较

`1.Innodb与MyiSam存储引擎区别？
 Innodb特点:支持外键,支持事务,支持行锁(支持表锁)
 Myisam特点:支持表锁(不支持行锁),不支持事务,不支持外键;

`2.Innodb与MyiSam使用场景？
 Innodb:项目中强调事务一致性,外键关联,支持写并发(行锁);
 Myisam:如果项目中有大量查询操作,然后写操作非常少,同时不用关心事务,外键等特性,可以考虑Myisqm引擎;

九、锁

锁的介绍

1.锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）;

2.在数据库中，除传统的计算机CPU、I/O 等资源争用以外，数据也是一种供许多用户共享的资源,所以也存储在并发问题,所以如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题

锁的分类

从对数据操作的粒度分 ：

表锁：操作时，会锁定整个表。 类似于java中HashTable

行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

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

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

mysql锁

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用；

行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用;

存储引擎  表级锁  	行级锁 		页面锁(了解)
MyISAM   支持 	不支持 		不支持
InnoDB   支持 	支持(默认) 	   不支持

MyISAM 表锁

加读锁 : lock table 表名 read; 
加写锁 : lock table 表名 write；

myisam下读锁会阻塞写，但是不会阻塞读。

而写锁，则既会阻塞读，又会阻塞写。

• MyISAM读锁演示

# session1
-- 1.添加锁
lock table tab_myisam read;

-- 2.自己查询表
select * from tab_myisam; 

-- 5.释放锁
unlock tables;

# session2

-- 3.另一个线程查询表
select * from tab_myisam; 

-- 4. 进行写操作 (阻塞)
update tab_myisam set name = 'mysql大成' where id = 2;

MyiSAM读锁:读操作对其他读操作是共享的,但是对于当前会话下其他表的读操作,是不允许的,同时对其他所有的写操作是阻塞的;

• MyISAM写锁演示

lock table 表名 write;

# session1
-- 1.添加锁
lock table tab_myisam write;

-- 2.自己查询表
select * from tab_myisam; 

-- 5.释放锁
unlock tables;

# session2

-- 3.另一个线程查询表 (阻塞)  锁释放 自动完成读操作
select * from tab_myisam; 

-- 4. 进行写操作 (阻塞)
update tab_myisam set name = 'mysql大成' where id = 2;

• 查看锁的争用情况

-- 命令
show open tables;
-- 指令解释： 
In_use: 表示当前表被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。 
Name_locked：表名称是否被锁定。名称锁定 用于对表进行重命名等操作。

查看表的锁定情况:
-- 指令
show status like 'Table_locks%';

参数说明:
Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。 

Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此 值高说明存在着较为严重的表级锁争用情况。

InnoDB行锁

1) 行锁介绍

1.行锁特点 ：InnoDB 存储引擎默认方式，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

2.InnoDB 与 MyISAM 的最大不同有两点：一是InnoDB 支持事务；二是 InnoDB 采用了行级锁。行级锁也是和事务有关的。

2) InnoDB的行锁模式

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

共享锁（S -share）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁（X-exclusion）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，但是获取排他锁的当前的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显式给记录集加共享锁或排他锁（sqlyog下会报语法错误） :

共享锁（ S）: SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 

排他锁（X) : SELECT * FROM table_name WHERE ... FOR UPDATE 

说明：一个事务在进行查询中，不想让其他事务修改当前查询的数据，可以使用for update;

注意：

innodb的行锁依赖于事务，一旦一个事务获取了行锁，在这个事务未提交之前，

其他事务如果操作的时相同的行，那么需要阻塞等待获取锁的事务提交后释放行锁，才能解除阻塞；

innodb行锁升级为表锁

在更新或者删除等写操作条件不走索引或者索引失效时，会导致全表扫描，进而导致表锁；
那么导致索引失效的情况有哪些呢?

 1）在索引字段下使用函数，导致索引失效；
select * from user where name=substr('xxxx',1,5);-- 可以走索引
select * from user where substr(name,1,5)='xxxx';-- 索引失效

2）在索引字段下进行类型转换；
select * from user where name=700;

3）在索引字段下使用数学运算；
select * from user where id=100+1;-- 可以走索引
select * from user where id[+-*/]1=100;-- 索引失效

 4）在索引字段频繁进行写操作；

innodb间隙锁

当我们用范围条件并请求共享或排他锁时，

InnoDB不仅会给符合条件的已有数据进行加锁，而且也会对于键值在条件范围内但并不存在的记录加锁，这种锁机制就是所谓的间隙锁;

`举例说明:
数据库存在数据的主键是：1 3 4 6 9

如果条件是：id < 10
这里id缺少的是2 5 7 8 。他们就称为间隙。
InnoDB也会对这些间隙进行加锁，这种锁机制就是所谓的间隙锁

innodb行锁争用

-- 指令
show status like 'innodb_row_lock%';

/*说明：
Innodb_row_lock_current_waits: 		当前正在等待锁定的数量
Innodb_row_lock_time: 				从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:			每次等待所花平均时长
Innodb_row_lock_time_max:			从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits: 				系统启动后到现在总共等待的次数
*/

总结

理解：
# 1、存储过程
①看懂存储过程的创建语句格式；
②内部比如if,case,while  do  set declare，理解功能；
了解：
         1）存储过程中游标；
         2）存储函数；function
         3)  mysql结构体系；
         
# 2、一个sql从客户端发送到mysql server 服务端，都经历了哪些组件？
客户端先与服务端建立连接（connectors,connection pool）,
接下来客户端发送select sql，服务端SQL interface sql接口接收，
交给parser解析器解析sql的语法是否正确，是否有执行的权限，
通过后，交给optimizer查询优化器对sql进行优化处理（1.查询是否走索引 2 要走那个索引），
最终调用存储引擎完成数据的查询工作；

# 3、innodb:
1)行锁:首先行锁是基于事务,同时行锁对其他同一行的写操作阻塞,但是不阻塞读;
2)表锁:
出现的原因：
1)更新或者删除的条件下的字段没有添加索引;
2)索引失效导致表锁
3)索引失效原因有哪些?
        1.字段类型装换
        2.索引字段使用函数
        3.索引字段进行数学运算
        4.索引字段进行高频的写操作等
3)间隙锁:
一个事务中进行范围的写操作,那么其他事务就不能再这个范围内写数据;