typora-copy-images-to: imgs

面试:
1)事务的4大特性:
   隔离性
   持久性
   原子性
   一致性
2)事务并发下出现的问题
  脏读
  不可重复读
  幻读
3)Mysql数据库事务隔离级别?
  读未提交  脏读 不可重复读 幻读
  读已提交  不可重复读 幻读
  可重复读  幻读
  串行化
4)索引类型
  主键索引 primary key 提高查询效率 非空+唯一运输
  唯一索引 unique 唯一
  普通索引 index
  联合索引
  全文索引
  hash索引
  空间索引
5)创建索引的原则?
  1.字段内容的辨识度越高越好;
  2.经常需要查询的条件下对应的字段;
  3.表连接查询(inner join on / left join on /right join on )
  4.order by 字段 配合使用
  5.空间原则
6)视图
  语法:create view 视图名称 AS select_sql;
  视图与表的区别:
     视图:表结构+视图sql底层逻辑(不保存数据本身);
     表:表结构+数据

1. 第一章 存储过程和函数(理解)

1.1. 1 存储过程和函数概述

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

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

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

1604319977408

1.2. 2 创建存储过程

语法:

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

说明:

1.参数列表包含3部分

        参数模式    参数名        参数类型
举例:   IN         stuname     varchar(20)

参数模式:

IN:该参数可以作为输入,也就是该参数需要调用者传入值
OUT:该参数可以作为输出,也就是该参数可以作为返回值
INOUT:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值

2.如果存储过程体仅仅只有一句话,begin end可以省略

3.自定义结束符

delimiter 结束标记
举例:
delimiter $
说明:这种声明只是针对当前窗口有效,属于临时性的,下次重新连接mysql无效。

在mysql中分号表示一条sql的结束,但是在存储过程中,分号表示的sql的分隔符,那么如果在存储过程中使用分号数据库就会认为存储过程已经结束,所以我们需要自定义存储过程的结束标识;

数据准备:

-- 1个国家有多个城市,一个城市只属于一个国家
-- 国家和城市是:1对多
CREATE TABLE country (
    country_id int(11) PRIMARY KEY AUTO_INCREMENT,
    country_name varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE city (
    city_id int(11) PRIMARY KEY AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int(11) NOT NULL,
    constraint ref_country_fk foreign key(country_id) references country(country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country values(1,'China');
insert into country values(2,'America');
insert into country values(3,'Japan');
insert into country values(4,'UK');


insert into city values(1,'西安',1);
insert into city values(2,'NewYork',2);
insert into city values(3,'北京',1);
insert into city values(4,'上海',1);

需求:创建名称为pro_test1存储过程,完成查询之前创建的country表中的行记录数功能

-- 定义结束符
delimiter $
-- 定义存储过程
create procedure pro_test1()
begin
  select count(*) from country;
end$

1.3. 3 调用存储过程

-- 调用格式
call 存储过程名(实参列表)$
-- 如果存储过程没有入参和出参,那么括号可以省略

需求:调用上述创建好的存储过程pro_test1

call pro_test1();

1.4. 4 查看存储过程

方式1:通过mysql.proc数据库根据db字段查看

select * from mysql.proc where db='day04_1';

1604367113740

方式2:查看创建某个存储过程的语句信息

-- 语法格式:
show create procedure 存储过程的名称;
show create procedure pro_test1;

1.5. 5 删除存储过程

-- 语法
drop procedure 存储过程名$

需求:删除pro_test1存储过程

drop procedure pro_test1;

1.6. 6 存储过程语法

1.6.1. 6.1 变量

1.declare声明变量

通过 declare 可以定义一个局部变量,该变量的作用范围只能在 BEGIN…END 块中;

-- 语法格式
  declare 变量名1,变量名2,...  变量的数据类型 [default 默认值]

注意事项:在声明存储过程时,变量避免使用sql关键字,否则报错;

需求:定义变量num默认值是5,并查询默认值

create procedure pro_test2()
begin
  -- 生命num变量
  declare num int default 5; 
  select num;
end$

call pro_test2;

1604367459780

2.set赋值变量

直接赋值使用 SET,可以赋常量或者赋表达式

-- 语法
  SET 变量名1 = 表达式1,变量名2=表达式2,...

需求:定义变量num默认值是0,设置num值+10 ,并查询

delimiter $
create procedure pro_test3()
begin
  declare num int default 0;
  set num=num+10;
  select num;

end$

call pro_test3();

1604367643430

3.select ... into 赋值

select  .... into 变量  from  表名;
-- 将查询结果赋值给变量

需求:定义变量num,没有默认值,通过select ... into设置num值为city表的行记录数 ,并查询

delimiter $
create procedure pro_test4()
begin
  declare num int;
  select count(*) into num from city;
  select num;
end$

call pro_test4;

1604367845372

1.6.2. 6.2 if条件判断

-- if条件后面为真执行对应then后面的语句,不满足就执行后面的elseif,如果elseif条件也不满足则继续向下执行判断,如果都不满足条件就执行else后面的语句

if 条件1 then 语句1;
[elseif 条件2 then 语句2;]
...
[else 语句n;]
end if;

需求:

根据定义的体重变量,判定当前体重的所属的身材类型
    170 及以上 ----------> 身材肥胖
    150 - 170 ---------> 标准身材
    150 以下 ----------> 身材偏瘦
delimiter $
create procedure pro_test5()
begin
  declare weight int default 160;
  declare descr varchar(20);
  if weight >=170 then
     set descr='身材肥胖';
  elseif weight<170 and weight >=150  then
     set descr='标准身材';  
  else
     set descr='身材偏瘦';
  end if;
  select descr;
end$


call pro_test5;

1604368413006

1.6.3. 6.3 传递参数

语法:

create procedure procedure_name([in/out/inout]  参数名  参数类型)
...

IN :  该参数可以作为输入,也就是需要调用方传入值 , 默认
OUT:  该参数作为输出,也就是该参数可以作为返回值
INOUT: 既可以作为输入参数,也可以作为输出参数

IN 输入

IN :  该参数可以作为输入,也就是需要调用方传入值 , 默认。

需求:根据传入的体重变量,判定当前体重的所属的身材类型

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

call pro_test6(200);

1604368624648

OUT 输出

OUT:  该参数作为输出,也就是该参数可以作为返回值

声明回话变量:

-- 语法格式
@变量名
-- 这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的

需求:根据传入的体重变量,获取当前体重的所属的身材类型

delimiter $
create procedure pro_test7(in weight int,out descr varchar(20))
begin
  if weight >=170 then
     set descr='身材肥胖';
  elseif weight<170 and weight >=150  then
     set descr='标准身材';  
  else
     set descr='身材偏瘦';
  end if;
  select descr;
end$

call pro_test7(90,@description);

select @description;

INOUT输入输出

INOUT: 既可以作为输入参数,也可以作为输出参数

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

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

需求:创建带inout模式参数的存储过程,传入a和b两个值,最终将a和b都乘以2并返回。

delimiter $
create procedure pro_test8(inout a int,inout b int)
begin
  set a=a*2;
  set b=b*2;
  select concat('a:',a,',b:',b);
end$
set @a=10;
set @b=50;

call pro_test8(@a,@b);

select concat('a:',@a,',b:',@b);

1604369414582

小结:

-- 声明变量
declare 变量名称 类型 default 值;
-- 修改变量
set  变量名称=值;
select .... into 变量名称 from 表明;
-- if判断
if 条件 then 
  [ sql逻辑;
elseif 条件  then 
   sql逻辑;
.....
 else
   sql逻辑;]
end if
-- 传参类型
  in 入参
  out 出参 @变量名称 | set @变量名称=值;
  inout:出入参

1.6.4. 6.4 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 pro_test9(in mon int)
begin
  declare season varchar(10);
  case
     when mon >=1 and mon <=3 then
        set season='第一季度';
     when mon >=4 and mon <=6 then
        set season='第二季度';  
     when mon >=7 and mon <=9 then
        set season='第三季度';       
     when mon >=10 and mon <=12 then
        set season='第四季度';
      else
        select '您输入的参数有误!'; 
  end case;
    select season;
end$

call pro_test9(10);
call pro_test9(100);

1.6.5. 6.5 mysql存储过程中的三种循环

6.5.1 while循环

语法:

-- 如果循环条件为true,则一直执行do后面的sql语句,如果循环条件为false,则结束while循环
while 循环条件 do

    sql语句

end while;

需求:计算从1加到n的值

​ 思路:定义一个自动加1的变量,如果变量小于等于n,则继续执行,否则退出;

delimiter $
create procedure pro_test10(in num int)
begin
  declare i int default 1;
  declare total int default 0;
  while i<=num do
      set total=total+i; -- 1+2+3+...num
      set i=i+1;
  end while;
  select total;
end$

call pro_test10(5);-- 15
call pro_test10(100);-- 5050

1604371472920

6.5.2 repeat结构

while 是满足条件才执行,repeat 是满足条件就退出循环。

-- 如果不满足until后面的循环条件则执行循环体代码,直到满足循环条件就结束循环体代码
repeat
    -- 循环体
    sql语句
    until  循环条件 -- 注意:循环条件后面不能加分号,不满足语法规则
end repeat;

需求:计算从1加到n

delimiter $
create procedure pro_test11(in num int)
begin
  declare i int default 1;
  declare total int default 0;

  repeat
      set total=total+i; -- 1+2+3+...num
      set i=i+1;
     until i > num
  end repeat;  
  select total;
end$

call pro_test11(5);-- 15
call pro_test11(100);-- 5050

6.5.3 loop语句

loop 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 leave 语句实现;

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

注意:如果不在 sql语句中增加退出循环的语句,那么 loop 语句可以用来实现简单的死循环。

6.5.4 leave语句(结束loop循环)

leave用来从标注的流程中退出,通常和loop循环一起使用。

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

需求:使用loop和leave实现数字累加

delimiter $
create procedure pro_test12(in num int)
begin
  declare  i int default 1;
  declare total int default 0;
  c:loop
    set total=total+i;
    set i=i+1;   
    if i > num then
      leave c;
    end if;  
  end loop c;
  select total;
end$

call pro_test12(5);-- 15
call pro_test12(100);-- 5050

小结:

1)while
while 循环条件 do
  循环逻辑;
end while; 

2)repeat
repeat
  循环逻辑;
  until 条件(满足条件就退出)
end repeat;

3)loop + leave
循环标记:loop
  循环逻辑;
  if 条件 then
    leave 循环标记;
   end if; 
end loop 循环标记;

1.6.6. 6.6 游标/光标(了解)

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

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

-- 语法格式
declare 游标名 cursor for 查询数据的结果集的sql语句 ;  -- 声明游标,执行完查询的sql语句之后将结果集放到游标中了
open 游标名 ; -- 打开游标
fetch 游标名  INTO 变量名,变量名 ... ; -- 获取游标中的数据并赋给指定变量,执行一次fetch获取游标中的一行数据,然后游标中的指针就会向下移动一次
close 游标名; -- 关闭游标

注意:Mysql中游标只适用于存储过程以及函数。

初始化数据:

create table emp(
    id int(11) not null auto_increment ,
    name varchar(50) not null comment '姓名',
    age int(11) comment '年龄',
    salary int(11) comment '薪水',
    primary key(id)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) 
values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),
(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

需求1:创建存储过程,查询emp中所有的数据存储到游标中,并取出游标中的数据

-- 需求1:创建存储过程,查询emp中所有的数据存储到游标中,并取出游标中的数据
delimiter $
create procedure pro_test13()
begin
 -- 生命emp字段变量
 declare id int;
 declare name varchar(20);
 declare age int;
 declare salary int;
 -- 生命游标
 declare emp_result cursor for select * from emp;
 -- 打开游标
 open emp_result;
 -- fetch .. into ... 获取数据
 fetch emp_result into id,name,age,salary;
 select concat(id,name,age,salary);
 fetch emp_result into id,name,age,salary;
 select concat(id,name,age,salary);
 fetch emp_result into id,name,age,salary;
 select concat(id,name,age,salary);
 fetch emp_result into id,name,age,salary;
 select concat(id,name,age,salary);
 -- 关闭游标
 close emp_result;
end$

call pro_test13();

1604373922003

循环实现方案:

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

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

需求2:使用出错处理器+循环实现;

delimiter $
create procedure pro_test14()
begin
-- flag为1表示有数据,0表示没有数据
 declare flag int default 1;
 -- 生命emp字段变量
 declare id int;
 declare name varchar(20);
 declare age int;
 declare salary int;
 -- 生命游标
 declare emp_result cursor for select * from emp;
 -- 说明生命退出处理器,一旦出现not found找不到数据,
 -- 那么就会将flag=0,表示游标中没有数据
 declare exit handler for not found set flag=0;
 -- 打开游标
 open emp_result;
 while flag=1 do
    fetch emp_result into id,name,age,salary;
    select concat(id,name,age,salary);
 end while;
 -- 关闭游标
 close emp_result;
end$

call pro_test14();

需求3:使用循环实现(先声明查询游标,然后再查询记录条数,根据记录条数条件循环即可)

自行实现;

1.7. 7 存储函数(了解)

函数 : 是一个有返回值的过程 ; 过程 : 是一个没有返回值的函数 ;

本身存储过程是没有返回值的函数,是一个过程,没有结果。上述所说的返回值其实不是真正返回值,只是我们传递一个变量到存储过程中,在存储过程中修改了该变量值,然后在调用存储过程的位置在获取修改后的变量值。

如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数,即便是存储过程使用了out类型的参数,其本质也不是真的有了返回值,而是在存储过程内部给out类型参数赋值,在执行完毕后,我们直接拿到输出类型参数的值。

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

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

-- 调用存储函数
select 存储函数名(实参)$

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

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

需求: 定义一个存储函数,在city表中根据传入的国家id,计算满足条件的总记录数

select count(*) from city where country_id=1

delimiter $
create function fn1(cid int)
-- 指定返回的数据类型
returns int
begin
  declare result int default 0;
  -- 查询赋值
  select count(*) into result from city where country_id=cid;
  -- 返回数据
  return result;
end$

select fn1(10);-- 3

总结:

-- 存储过程语法
delimiter $
create procedure p_name([in/out/inout] v1 类型,...)
begin
  declare v2 类型 default 值;
  set v2 =值;
  select ... into v2 from...
  if 条件 then
    sql逻辑;
  elseif 条件 then
    sql逻辑;
  ....
  else
    sql逻辑;
  end if;

 -- 多选一
  case
    when 条件 then
     sql逻辑
    when 条件 then
      sql逻辑;
    else
      sql逻辑;
  end case;
 -- 3种循环 
 -- 满足条件就运行
 while 条件 do
   sql逻辑;
 end while;
 -- 满足条件就退出
 repeat
  sql逻辑
  until 条件
 end repeat;

 c:loop
  sql逻辑;
  if 条件 then
    leave c;
  end if; 
 end loop c;

 -- 游标
 declare cusor_name  cursor for select_sql;
 declare exit handler for not found set v3=1open cusor_name;
 fetch cusor_name into v1,v2,...;
 close cusor_name;

end$

call p_name();

-- 函数
-- 语法格式
delimiter $
create function fn_name(v1 类型,....)
retruns 类型
begin
  sql逻辑;
  return 值;
end$

select  fn_name(参数);

1.8. 8. 存储过程练习

数据准备:

--  创建表的sql
CREATE TABLE account(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    money FLOAT
);
INSERT INTO account VALUES(NULL,"tom",1000),(NULL,"rose",1000);

需求:

将转账的业务,写入存储过程,调用存储过程实现转账的业务
存储过程四个参数:
    IN:
        fromSub 从哪位用户转账
        toSub 转给的用户
        m 转账金额
    OUT:flag 标记,1表示转账成功  0表示转账失败

说明:该案例需要使用到ROW_COUNT()函数,该函数表示返回前一个SQL进行UPDATE,DELETE,INSERT操作所影响的行数

delimiter $
create procedure transfer(in fromSub varchar(20), 
in toSub varchar(20),in m float,out flag int)
begin
 declare l1 int ;
 declare l2 int;
 -- 开启事务
 start transaction;
 -- 转出金额
 update account set money=money-m where name=fromSub; 
 select     row_count() into l1;
 -- 转入金额
 update account set money=money+m where name=toSub; 
 select row_count() into l2;
 -- 判断
 if l1 >0 and l2 >0 then
   -- 提交事务
   commit;
   -- 设置flag值为1表示成功
   set flag=1;
 else
   -- 事务回滚
   rollback;
   set flag=0;  
 end if;  
 select    concat('l1:',l1,'----l2:',l2);
end$

call transfer('tom','rose',100,@flag);

select @flag;-- 1

1604384018984

2. 第二章 Mysql体系结构(了解)

MySQL Server由以下组成:

  • Connection Pool : 连接池组件。连接池: 管理、缓冲用户的连接,线程处理等需要缓存的需求。
  • Management Services & Utilities : 管理服务和工具组件。系统管理和控制工具,例如备份恢复、Mysql复制、集群等
  • SQL Interface : SQL 接口组件。接受用户的SQL命令,并且返回用户需要查询的结果。
  • Parser : 查询分析器组件。SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
  • Optimizer : 优化器组件。SQL语句在查询之前会使用查询优化器对查询进行优化
  • Caches & Buffers : 缓冲池组件。如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据
  • Pluggable Storage Engines : 存储引擎。存储引擎说白了就是如何管理操作数据(存储数据、如何更新、查询数据等)的一种方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)
  • File System : 文件系统。存储的文件系统不仅包含数据,还包括索引、错误日志和慢查询日志等
select * from user where age > 30 and id >100;-- 小结果集驱动大结果
-- 采样算法
说明:假如表中有age索引和id索引;
     age>30 过滤到的数据10w
     id>100 过滤到数据100w
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)维护索引

3. 第三章 存储引擎

3.1. 1 存储引擎概述

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

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

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

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

1.1 查看mysql支持的存储引擎命令:

show engines;

1604385624686

1.2 查看Mysql数据库默认的存储引擎 , 指令

show variables like '%storage_engine%';

1604385686616

3.2. 2 存储引擎特性

特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 64TB 没有
事务安全 支持
锁机制 行锁(适合高并发) 表锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持(5.6版本后) 支持
集群索引 支持
数据索引 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 N/A
内存使用 中等
批量插入速度
支持外键 支持

我们将重点介绍最长使用的两种存储引擎: InnoDB、MyISAM , 另外几种 了解即可。

小结:

innodb:支持事务,支持行锁(表锁),支持外键,b+tree
myisam:不支持事务,不支持行锁,不支持外键,支持表锁,b+tree

3.2.1. 2.1 InnoDB(聚集索引/聚簇索引)

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

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

数据准备:

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

1604386254931

因为在innodb存储引擎下,表中的索引和数据存储到一块,所以称为聚簇索引;

3.2.2. 2.2 MyISAM(非聚集索引/非聚簇索引)

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

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

数据准备:

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

1604386353914

3.3. 3 存储引擎选择

在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

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

小结:

1.存储引擎的选择:
  innodb:对事务要求比较高,或者需要一些外键支持的业务场景,可以使用,同时业务中修改操作如果比较频繁,也可使用innodb:
  myisam:适合以du为主的应用,同时对事务要求不是太高的场景;

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

4. 第四章 锁

4.1. 1 锁概述

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

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

4.2. 2 锁分类

从对数据操作的粒度分 :

  • 表锁:操作时,会锁定整个表。 类似于java中HashTable

  • 行锁:操作时,会锁定当前操作行。

从对数据操作的类型分:

  • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。

  • 写锁(排它锁):当前操作没有完成之前,它会阻断其他线程的写锁和读锁。

4.3. 3 mysql锁

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

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

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

4.4. 4 MyISAM 表锁

MyISAM只支持表级锁;

加表级锁语法:

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

测试数据准备:

-- 创建book表和用户表,两张表无关联关系
CREATE TABLE tb_book (
    id INT(11) auto_increment,
    name VARCHAR(50) DEFAULT NULL,
    publish_time DATE DEFAULT NULL,
    status CHAR(1) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思
想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思
想','2088-08-08','0');

CREATE TABLE tb_user (
    id INT(11) auto_increment,
    name VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

4.4.1. 1.MyISAM读锁演示

演示思路:
ab两个客户端,a对表加读锁,b没有加锁
1.验证ab客户端对加锁表进行读操作时的区别;
2.验证ab客户端对其他表进行读操作时的区别;
3.验证ab客户端对加锁表表进行写操作时的区别;

1604387983704

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

4.4.2. 2.MyISAM写锁演示

lock table 表名 write;

1604388205194

结论:

读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。

4.4.3. 3.查看锁的争用情况

查看表的使用情况:

-- 命令
show  open tables;

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

说明:可通过设置写锁或者读锁查看当前锁的征用情况;

1604388475398

查看表的锁定情况:

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

参数说明:

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

4.5. 5 InnoDB 行锁

4.5.1. 5.1 行锁介绍

1.行锁特点 :InnoDB 存储引擎默认方式,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。 2.InnoDB 与 MyISAM 的最大不同有两点:一是InnoDB 支持事务;二是 InnoDB 采用了行级锁。行级锁也是和事务有关的。

4.5.2. 5.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;

4.5.3. 5.3 行级锁基本演示

测试数据准备:

create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1) -- 0表示女 1表示男
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

操作说明:innodb默认行级锁,可通过在不同事务下操作相同的行进行演示;

1604390013852

小结:

innodb的行锁依赖于事务,一旦一个事务获取了行锁,在这个事务未提交之前,其他事务如果操作的时相同的行,那么需要阻塞等待获取锁的事务提交后释放行锁,才能解除阻塞;

4.5.4. 5.4 行锁升级为表锁

在mysql中使用innodb索引失效的几种情况:

1)在索引字段下使用函数,导致索引失效;

 select * from user where name=substr('xxxx');//可以走索引
 select * from user where substr(name)='xxxx';//索引失效

2)在索引字段下进行类型转换

3)在索引字段下使用数学运算

如果通过索引条件检索数据,但是索引类型和定义的类型不一致时,存在类型转换,索引失效,最终行锁变为表锁 。那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。

演示说明:

​ 开启两个窗口,同时关闭两窗口事务自动提交功能,窗口1更新条件设置name为int类型,之后窗口2再进行条件id更新操作,查看演示效果;

1604390978670

思路:利用条件查询中的索引字段进行类型转换时导致索引失效,进而导致全表遍历查询,进而导致全表的锁定,在这种情况下,其他事务进行更新操作时,会导致阻塞的情况;

4.5.5. 5.5 间隙锁

​ 当我们用范围条件并请求共享或排他锁时,InnoDB不仅会给符合条件的已有数据进行加锁,而且也会对于键值在条件范围内但并不存在的记录加锁,这种锁机制就是所谓的 间隙锁;

举例说明:

数据库存在数据的主键是:
1
3
4
6
9
如果条件是:id < 10
这里id缺少的是2 5 7 8 。他们就称为间隙,InnoDB也会对这些间隙进行加锁,这种锁机制就是所谓的 间隙锁

需求:

1.窗口1和窗口2关闭事务自动提交功能;
2.窗口1更新id小于4的name为'6666',且不提交事务;
3.窗口2插入id是2的数据.
4.总结查看演示效果

4.5.6. 5.6 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: 系统启动后到现在总共等待的次数※

4.5.7. 5.7 总结

1.mysql中存储过程的语法格式:

delimiter $ -- 定义结束符
create procedure 存储过程的名称 (参数列表)
begin
  sql逻辑
end$
-- 调用
call 存储过程名称(实参);

2.存储引擎:

innodb:支持事务,外键,行级锁,锁的粒度比较细,支持并发大,但是锁的维护成本开销大,可能会出现死锁;

myisam:不支持事务,外键,支持表级锁,锁的并发相对较低,同时不会发生死锁;

results matching ""

    No results matching ""