1. mysql体系结构

1） 连接层

主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2） 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3） 引擎层 [存储引擎]

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4）存储层

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

mysql体系结构:

1. 连接层：
   2. 服务层: 
   3. 存储引擎： 
   4. 存储层: mysql数据全都存在在文件上。

2. 存储引擎

2.1 存储引擎概述

​ 和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。

​ 存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是==基于表的==，而不是基于库的。

​ Oracle，SqlServer 等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。  

 查看Mysql数据库默认的存储引擎 ， 指令 ：

 show variables like '%storage_engine%' ； 

2.2.1 InnoDB

​ InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点 ：

事务控制

create table goods_innodb(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

create table goods_MyISAM(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- innodb存储引擎---支持事务
-- 手动开启事务
start transaction;
-- 添加成功但查看表确没有该条数据---这是因为事务还未提交
insert into goods_innodb(id,name) values (null,'Meta20');
-- 事务提交
commit;

-- myisam存储引擎---不支持事务
-- 开启事务，但是对myisam存储引擎的SQL语句并不起作用
start transaction;
insert into goods_MyISAM (id,name) values (null,'vivoX20');
commit;

 测试，发现在InnoDB中是存在事务的 ；

外键约束

​ MySQL支持外键的存储引擎==只有InnoDB==， 在创建外键的时候， 要求父表必须有对应的索引 ， 子表在创建外键的时候， 也会自动的创建对应的索引。

​ 下面两张表中 ， country_innodb是父表 ， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id 。

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DELETE RESTRICT  -- restrict 限制在字表有关联记录的情况下，父表不能更新
-- UPDATE CASCADE		-- cascade表示在更新或者删除是，字表对应的数据也会被删除或更新
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

在创建索引时， 可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

RESTRICT和NO ACTION相同， 是指限制在子表有关联记录的情况下， 父表不能更新；

CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；

SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。

针对上面创建的两个表， 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候， 如果子表有对应记录， 则不允许删除， 主表在更新记录的时候， 如果子表有对应记录， 则子表对应更新 。

表中数据如下图所示 ：

删除country_id为1 的country数据：  

-- 因为delete加了restrict ，限制了父表的更新
delete from country_innodb where country_id = 1;

 更新主表country表的字段 country_id :

-- update 加了cascade 所以在其更新时相应的字表的外键列的数据也会被改变
update country_innodb set country_id=33 where country_id =1;

更新后， 子表的数据信息为 ：

存储方式 /var/lib/mysql/demo01

InnoDB 存储表和索引有以下两种方式 ：

①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

2.2.2 MyISAM

​ MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点：

不支持事务

通过测试，我们发现，在MyISAM存储引擎中，是没有事务控制的 ；  

create table country_myisam(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;


create table city_myisam(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_myisam(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into country_myisam values(null,'China'),(null,'America'),(null,'Japan');
insert into city_myisam values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);



-- myisam 存储引擎的表---不支持事务和外键，所以在进行删除和修改时指定的操作都不生效
delete from country_myisam where country_id = 33;
update country_myisam set country_id=33 where country_id =1;

文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

.frm (存储表定义)；

.MYD(MYData , 存储数据)；

.MYI(MYIndex , 存储索引)；

myisam

总结: 

innodb: 支持事务和外键，而且表结构存在一个文件中，索引和数据存储在idb文件中

myisam: 不支持事务和外键，它的表结构存在在frm中，索引和数据分别存在在myi和myd文件中

2.2.3 存储引擎选择

2. 存储过程。

2.1 存储过程和函数概述

​ 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

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

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

2.2 创建存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
	-- SQL语句
end ;

示例 ：

create procedure p1()
begin
	select 'nihao子非鱼';
end;
-- 调用存储过程--查看select的数据
call p1 ;


-- 使用delimiter
delimiter $

create procedure pro_test1()
begin
	select 'Hello Mysql' ;
end$

delimiter ;

 知识小贴士

DELIMITER

​ 该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

2.3 调用存储过程

call procedure_name() ;	

2.4 查看存储过程

-- 查询mysqlgj数据库中的所有的存储过程
select name from mysql.proc where db='mysqlgj';

-- 查询存储过程的状态信息
show procedure status;

-- 查询某个存储过程的定义
show create procedure test.mysqlgj \G;

2.5 删除存储过程

DROP PROCEDURE  [IF EXISTS] sp_name ；

示例:

-- 删除存储过程
drop procedure if exists p2;

2.6 语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

2.6.1 变量

• DECLARE

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

DECLARE var_name[,...] type [DEFAULT value]

示例:

-- 存储过程是可以编程的，可以使用变量、表达式、控制结构、
create PROCEDURE p3()
begin
-- 通过delclare定义一个局部变量并设置初始值
	declare num int default 5;
-- 查看并进行变量运算
	select num+10;
end;
-- 调用存储过程--查看select的值
call p3();

• SET

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

  SET var_name = expr [, var_name = expr] ...

 示例:

create procedure p4()
begin
-- 通过declare定义一个局部变量--不赋值
	declare name varchar(255);
-- 通过set直接给变量赋值	
	set name='mysql';
	select name;
	
end;

call p4();

 也可以通过select ... into 方式进行赋值操作 :

create procedure p5()
begin
	declare countnum int;
-- 通过select into 方式赋值----count(*):数量,city_innodb:表名--查询city_innodb表数据的数量并赋值给countnum	
	select count(*) into countnum from city_innodb;
	select countnum;
end;

call p5();

2.6.2 if条件判断

语法结构 :

if search_condition then statement_list

	[elseif search_condition then statement_list] ...
	
	[else statement_list]
	
end if;

示例:

根据定义的身高变量，判定当前身高的所属的身材类型 

    180 及以上 ----------> 身材高挑

    170 - 180  ---------> 标准身材

    170 以下  ----------> 一般身材

-- if条件判断

create procedure p6()
begin
-- 创建身高并赋予初始值
	declare height int default 177;
-- 创建描述对象	
	declare describle varchar(255);
-- 使用if条件判断---使用then set 给变量赋值
	if height>180 then set describle='height tiao';
	elseif height>170 and height<=180 then set describle='zhongzheng';
-- 最后的else可以直接使用set赋值	
	else set describle='small';
-- 结束if语句	
	end if;
-- 查询变量	
	select describle;
end;

call p6();

 调用结果为 :

2.6.3 传递参数

语法格式 :

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

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

IN - 输入

示例 :

根据定义的身高变量，判定当前身高的所属的身材类型 

-- 传参
-- 创建时定义参数和参数类型
create procedure p7(in height int)
begin
-- 创建描述对象	
	declare ds varchar(255);
-- 使用if条件判断---使用then set 给变量赋值	
	if height>180 then set ds='gaotiao';
	elseif height>170 and height<=180 then set ds='zhongzheng';
	else set ds='small';
-- 结束if语句		
	end if;
-- 查询变量	
	select ds;
end;

-- 调用存储过程并赋值
call p7(177);

OUT-输出

示例:

根据传入的身高变量，获取当前身高的所属的身材类型  

-- 传参+返回值
-- 定义参数和参数类型---使用out输出参数
create procedure p8(in ht int,out ds varchar(255))
begin
	if ht>180 then set ds='gao';
	elseif ht>170 and ht<=180 then set ds='zhong';
	else set ds='small';
	end if;
end;

-- 调用存储过程并赋值---@ds调用输出参数
call p8(166,@ds);
-- 查看调用的输出参数
select @ds;

小知识

@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做 系统变量

2.6.4 case结构

switch ()

case

case

default:

 语法结构 :

方式一 : 

CASE case_value

  WHEN when_value THEN statement_list
  
  [WHEN when_value THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

----传递一个int类型的数字 如果为1 则输出星期一  
方式二 : 

CASE

  WHEN search_condition THEN statement_list
  
  [WHEN search_condition THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

需求:  

给定一个月份, 然后计算出所在的季度 

-- switch..case 
create PROCEDURE p9(month int)
begin 
	declare jijie varchar(255);
-- 开启case	
	case
-- 条件编辑	
		when month>=1 and month<=3 then set jijie='spring';
		when month>=4 and month<=6 then set jijie='summer';
		when month>=7 and month<=9 then set jijie='qiutian';
		when month>=10 and month<12 then set jijie='winter';
-- 关闭case		
	end case;
-- 查询变量	
	select jijie;
end;

call p9(5);

2.6.5 while循环

while(条件){

}

语法结构:

while search_condition do

	statement_list
	
end while; --别忘记分号

示例 :

计算从1加到n的数的和值   

-- while 循环
-- 定义变量n
create procedure p10(n int)
begin
-- 定义变量sum并
	declare sum int default 0;
	declare num int default 1;
-- 开始while循环，表达式为true则继续执行，false跳出循环	
	while num<=n do
		set sum=sum+n;
		set n=n-1;
-- 结束循环		
	end while;
	select sum;
end;
-- 计算1-100的总和
call p10(100);

2.6.6 repeat结构

do{}while()

有条件的循环控制语句, 当满足条件的时候退出循环 。while 是满足条件才执行，repeat 是满足条件就退出循环。

语法结构 :

REPEAT

  statement_list

  UNTIL search_condition  --不要加分号

END REPEAT;

示例 :

计算从1加到n的值 

-- 使用repeat(do...while)进行循环
create procedure p11(n int)
begin
	declare sum int default 0;
	repeat 
		set sum=sum+n;
		set n = n-1;
-- until 后面不要分号
		until n=0
-- 结束repeat(do..while)循环--		
	end repeat;
	
	select sum;
	
end;

call p11(100);

2.6.7 游标/光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明游标：

DECLARE cursor_name CURSOR FOR select_statement ; -- select语句

OPEN 游标：

OPEN cursor_name ;

FETCH 游标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 游标：

CLOSE cursor_name ;

示例 :

初始化脚本:

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,'yellow',55,3800),(null,'white',60,4000),(null,'green',38,2800),(null,'blue',42,1800);

create PROCEDURE p12()
begin
	DECLARE n varchar(254);
	DECLARE s int;
	declare has_data int default 1;
	-- 声明游标
	declare my cursor for select name,salary from emp;
	declare exit handler for not found set has_data=0;
	create table if not exists tb_my(
		id int primary key auto_increment,
		name varchar(255),
		salary int
	);
	-- 开启游标
	open my;
	
	while has_data=1 do
	-- 取出游标的数据
		fetch my into n,s;
			insert into tb_my(name,salary) values (n,s);
	end while;
	
	close my;
	
end;

call p12();

通过循环结构 , 获取游标中的数据 : 

-- 游标---返回值
create procedure p13()
begin
		declare id int(11);
		declare name varchar(50);
		declare age int(11);
		declare salary int(11);
		declare has_data int default 1;
		
		declare emp_result cursor for select * from emp;
-- 若没有数据返回，程序继续，并将变量has_data设为0
		declare exit handler for not found set has_data=0;
	-- 开启游标	
		open emp_result;
		
		repeat 
	-- 取出游标的数据
-- 游标select的字段数需要与fetch into的变量数一致	
			fetch emp_result into id,name,age,salary;
			select concat('id:',id,',name:',name,',age:',age,',salary:',salary);
-- until 后不跟分号			
			until has_data=0
		end repeat;
-- 关闭游标	
		close emp_result;
	end;

call p13();

2.7 存储函数 ---存储过程 函数：函数有返回值。

语法结构:

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
	...
END;

案例 :

定义一个存储函数, 请求满足条件的总记录数 ;

-- 存储函数
create function count_city(countryId int)
-- 开启返回值
returns int 
begin
	declare cnum int;
-- 根据条件查询city_innodb表指定country_id的数据有几个	
	select count(*) into cnum from city_innodb where country_id = countryId;
-- 返回数据	
	return cnum;
end;


select count_city(1);
select count_city(2);

3. 索引

3.1 索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的==数据结构==（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的==示意图==所示 :

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

3.2 索引优势劣势

3.3 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

Innodb myisam

• B+TREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。

• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。

• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

• Full-text （全文索引 倒排索引--ES） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

3.3.0 二叉树

3.3.1 B-TREE 结构

动态演示Data Structure Visualization

漫画：什么是B-树？ - 知乎

3.3.2 B+Tree结构

漫画：什么是B+树？ - 知乎

3.3.4 数据库中B+tree的结构0

3.4 索引分类

 例如:

3.5 索引的语法

例子:

create table student(
   id int primary key,
     name varchar(20),
     phone varchar(11),
     email varchar(50),
     profession varchar(30),
     age int,
     gender TINYINT,
     status TINYINT,
     createtime datetime
);

需求:

1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

3. 为profession、age、status创建联合索引。

4. 为email建立合适的索引来提升查询效率。

-- 创建普通索引
create INDEX idx_name on student (name);
-- 创建唯一索引
create unique INDEX idx__phone on student (phone);
-- 创建联合索引
create INDEX idx_profession_age_status on student (profession,age,status);
-- 创建全局索引
create FULLTEXT INDEX idx_email on student (email);

3.6 性能分析

3.7 索引使用规则

-- 最左前缀法则--对于联合索引，查询时必须有最左边即第一个索引列存在(查询从索引的最左列开始，即创建索引时的第一个)，否则索引不会生效
explain select * from student where profession='软件工程' and age='31' and status='0'; 
explain select * from student where profession='软件工程' and age='31'; 
explain select * from student where profession='软件工程'; 
explain select * from student where age='31' and status='0'; 
explain select * from student where status='0'; 
explain select * from student where profession='软件工程' and status='0'; 

-- 范围查询--如果出现逻辑运算符，索引将会失效
explain select * from student where profession='软件工程' and age>'30' and status='0';
explain select * from student where profession='软件工程' and age<'30' and status='0';

-- 索引列运算--索引上进行运算操作索引将失效
explain select * from student where substring(phone,10,2)='15'

-- 字符串不加引号，索引会失效
explain select * from student where phone= '17799990005';
explain select * from student where phone= 17799990005;

-- 模糊查询--尾部模糊索引不会失效，但如果是头部模糊匹配，索引会失效
explain select * from student where profession like '软件%'
explain select * from student where profession like '%软件%'
explain select * from student where profession like '%工%'

-- or 连接条件
-- 如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到
explain select * from student where id=10 or age=23;
explain select * from student where id=10 or name='露娜';

-- 数据分布影响--r如果mysql苹果使用索引比全表查询更慢，则不使用索引
select * from student where phone>='1799990005';
select * from student where phone>='1799990015';

-- 覆盖索引
explain select id,profession from student; 
explain select id,profession,age from student; 
explain select id,profession,age,status from student; 
explain select id,profession,age,status,phone from student; 

注意: 根据id查询为什么没有执行覆盖索引。

4. 锁

4.1 Mysql锁问题

4.1.1 锁概述

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

在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

4.1.2 锁分类

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。MyISAM 5.6 后InnoDB支持

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

从对数据操作的类型分：

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

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

4.1.3 Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

 MySQL这2种锁的特性可大致归纳如下 ：

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM和InnoDB

1. 事务，外键，文件存储，锁。

4.2 MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

4.2.1 如何加表锁

MyISAM 在执行查询语句（SELECT）前，会==自动给涉及的所有表加读锁==，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。 但是我们为了给你能演示出效果人为加锁。

显示加表锁语法：

加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；

是否锁:   unlock tables;

4.2.2 读锁案例

 准备环境

create database demo_03 default charset=utf8mb4;

use demo_03;

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,'田伯光');

客户端 一 ：

1）获得tb_book 表的读锁----添加表的读锁

lock table tb_book read;

2） 执行查询操作

select * from tb_book;

可以正常执行 ， 查询出数据。

客户端 二 ：

3） 执行查询操作

select * from tb_book;

客户端 一 ：

4）查询未锁定的表

select name from tb_user;

 因为当前用户已经对别的表上锁了 只有解锁后才可以对其他的表进行读操作。

#释放锁
unlock tables;

客户端 二 ：

5）查询未锁定的表

select name from tb_user;

可以正常查询出未锁定的表；

客户端 一 ：

6） 执行写操作

update tb_book set name='java' where id=1;

执行写操作， 直接报错 ， 由于当前tb_book 获得的是 读锁， 不能执行更新操作。

mysql默认会为每一条命令添加锁，所以写会自动添加锁，而此时我们手动给读操作添加了锁，并且还未释放锁，所以写操作无法执行

客户端 二 ：

7） 执行插入操作

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 inesrt 语句 ， 立即执行 ；

4.2.3 写锁案例

写锁(排他锁): 不允许其他会话进行任意操作

客户端 一 :

1）获得tb_book 表的写锁---为表添加写锁

lock table tb_book write ;

 2）执行查询操作

select * from tb_book ;

查询操作执行成功；

3）执行更新操作

update tb_book set name='java编程思想' where id=1;

更新操作执行成功 ；

客户端 二 :

4）执行查询操作

select * from tb_book ;

当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 select 语句 ， 立即执行 ；  

 总结

4.3 InnoDB 行锁 支持事务

4.3.1 行锁介绍

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

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

4.3.2 背景知识

1. 什么事务？
       事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

 并发事务处理带来的问题

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

备注 ： √ 代表可能出现 ， × 代表不会出现 。

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：

show variables like 'tx_isolation';

4.3.3 InnoDB 的行锁模式

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

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

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

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

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

可以通过以下语句显示给记录集加共享锁或排他锁 。

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

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

4.3.4 案例准备工作 innodb 支持事务 行级锁

create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(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);

4.3.5 行锁基本演示 ----支持事务