InnoDB#

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎

MyISAM#

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

文件：

• xxx.sdi: 存储表结构信息
• xxx.MYD: 存储数据
• xxx.MYI: 存储索引

Memory#

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

• 存放在内存中，速度快
• hash索引（默认）

文件：

• xxx.sdi: 存储表结构信息

面试题#

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

• 相对于二叉树，层级更少，搜索效率高
• 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

查看执行频次#

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：

SHOW GLOBAL STATUS LIKE 'Com_______'; 或者 SHOW SESSION STATUS LIKE 'Com_______';

慢查询日志#

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

1
-- 开启慢查询日志开关
2
slow_query_log=1
3
-- 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
4
long_query_time=2
5
-- 更改后记得重启MySQL服务，日志文件位置：/var/lib/mysql/localhost-slow.log
6

7
-- 查看慢查询日志开关状态：
8
show variables like 'slow_query_log';

最左前缀法则#

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

索引失效情况#

在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';

字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号

模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。

用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL 提示#

是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1
使用索引：
2
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
3
不使用哪个索引：
4
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
5
必须使用哪个索引：
6
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

覆盖索引&回表查询#

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。

explain 中 extra 字段含义：

using index condition：查找使用了索引，但是需要回表查询数据

using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如select id, name from xxx where name=‘xxx’;，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如select id, name, gender from xxx where name=‘xxx’;

所以尽量不要用select *，容易出现回表查询，降低效率，除非有联合索引包含了所有字段

前缀索引#

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：create index idx_xxxx on table_name(columnn(n));

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

求选择性公式：

1
select count(distinct email) / count(*) from tb_user;
2
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

show index 里面的sub_part可以看到接取的长度

单列索引&联合索引#

单列索引：即一个索引只包含单个列

联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：

explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';

这句只会用到phone索引字段

注意事项

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

普通插入：#

• 采用批量插入（一次插入的数据不建议超过1000条）
• 手动提交事务
• 主键顺序插入

大批量插入：#

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。

1
-- 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
2
mysql --local-infile -u root -p
3
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
4
set global local_infile = 1;
5
select @@local_infile;
6
-- 执行load指令将准备好的数据，加载到表结构中
7
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

介绍#

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

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。（只保存了这条sql语句）

语法#

创建视图：

CREATE [OR REPLACE] VIEW 视图名称(列名列表) AS SELECT语句[WITH[CASCADED|LOCAL] CHECK OPTION]

查询视图：

查看创建视图语句：SHOW CRETE VIEW 视图名称;

查看视图数据：查看视图数据:SELECT*FROM 视图名称…;

修改视图：

方式一：

CREATE [OR REPLACE]VIEW 视图名称(列名列表)AS SELECT语句[WITH[CASCADEDLLOCAL] CHECK OPTION

方式二：

ALTER VEW 视图名称(列名列表)AS SELECT语句[WITH[CASCADED|LOCAL]CHECK OPTION]

删除视图：

DROP VIEW [IF EXISTS]视图名称[,视图名称]

例子#

1
- 创建视图
2
create or replace view stu_v_1 as select id, name from student where id <= 10;
3

4
-- 查询视图
5
show create view stu_v_1;
6
select * from stu_v_1;
7

8
-- 修改视图
9
create or replace view stu_v_1 as select id, name, no from student where id <= 10;
10
alter view stu_v_1 as select id, name from student where id <= 10;
11

12
-- 删除视图
13
drop view if exists stu_v_1;/*  */

检查选项#

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。

为了确定检查的范围，mysql 提供了两个选项 和 LOCAL，默认值为CASCADED。

cascaded：在对创建时含有该字段的视图，插入数据时，该视图依赖的视图都会加上检查，需要所有条件都满足才能够插入成功。

local：在对创建时含有该字段的视图，插入数据时，对于该视图依赖的视图中含有检查语句的条件进行检查判断。

更新以及作用#

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数SUM()、MIN()、MAX()、COUNT()等
• DISTINCT
• GROUP BY
• HAVINGA
• UNION 或者 UNION ALL

作用#

简单

• 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

安全

• 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

数据独立

• 视图可帮助用户屏蔽真实表结构变化带来的影响。

例子

1
-- 1.为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。
2
create view tb user view as select id,name,profession, age,gender,status,createtime from tb_user;
3
select *from tb user view;
4

5
-- 2.查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。
6
create view tb_stu_course_view
7
select s.name student_name, s.no student_no, c.name course_name
8
from student s, stuent_course sc, course c
9
where s.id = sc.studentid and sc.courseid = c.id;
10

11
-- 以后每次只需要进行查询视图即可
12
select * from tb_stu_course_view;

介绍#

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

存储过程思想上很简单，就是数据库 SQL语言层面的代码封装与重用。

基本语法#

1
-- 存储过程基本语法
2
-- 创建
3
create procedure p1()
4
begin
5
  select count(*)from student;
6
end;
7

8
-- 调用
9
call p1();
10

11
-- 查看
12
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
13
-- 查询数据库的存储过程及状态信息
14
show create procedure p1;
15
-- 查询某个存储过程的定义
16

17
-- 删除
18
drop procedure if exists p1;

变量#

1
-- 变量：系统变量
2
-- 查看系统变量
3
show session variables;
4
show session variables like 'auto%';
5
show glabal variables like 'auto%';
6
select @@global.autocommit;
7

8
-- 设置系统变量
9
set session autocommit = 1;
10
insert intto course(id, name) values (6, 'ES');
11
set global auto commit = 0;

1
-- 变量：用户变量
2
-- 赋值
3
set @myname = 'itcast';
4
set @myage := 10;
5

6
select @mycolor := 'red';
7
select count(*) into @mycount from tb_user;
8

9
-- 使用
10
select @myname, @myage, @mycolor, @mycount;
11

12
select @abc; -- 输出为NULL

if判断#

语法：

1
IF 条件1 THEN
2
        ...
3
ELSEIF 条件2 THEN -- 可选
4
        ...
5
ELSE              -- 可选
6
        ...
7
END IF;

参数（in,out,inout）#

IN为输入参数，OUT为输出参数，INOUT既是输入，也是输出

语法：

1
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型 ])
2
BEGIN
3
    -- SQL语句
4
END :

案例：

1
-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)
2
-- score >= 85分，等级为优秀。
3
-- score >= 60分 且 score < 85分，等级为及格
4
-- score < 60分，等级为不及格。
5
create procedure p3(in score int, out result varchar(10))
6
begin
7
  if score >= 85 then
8
    set result :='优秀';
9
  elseif score >= 60 then
10
    set result :='及格';
11
  else
12
    set result :='不及格';
13
  end if;
14
  select result;
15
end;
16

17
-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回分数 --> inout
18
create procedure p5(inout score double)
19
begin
20
  set score := score * 0.5;
21
end;
22

23
set @score = 198;
24
call p5(score);
25
select @score;

case#

语法

1
CASE case value
2
  WHEN when_value1 THEN statement_list1
3
  [WHEN when_value2 THEN statement_list2]...
4
  [ELSE statement_list ]
5
END CASE;

1
CASE
2
  WHEN search_conditionl THEN statement_list1
3
  WHEN search_condition2 THEN statement_list2...
4
  [ELSE statement_list]
5
END CASE;

案例

1
-- case
2
-- 根据传入的月份，判定月份所属的季节(要求采用case结构)
3
-- 1-3月份，为第一季度
4
-- 4-6月份，为第二季度
5
-- 7-9月份，为第三季度
6
-- 10-12月份，为第四季度
7

8
create procedure p6(in month int)
9
begin
10
  declare result varchar(10);
11
  case
12
    when month >= 1 and month <= 3 then
13
      set result := '第一季度';
14
    when month >= 4 and month <= 6 then
15
      set result := '第二季度';
16
    when month >= 7 and month <= 9 then
17
      set result := ' 第三季度';
18
    when month >= 10 and month <= 12 then
19
      set result := '第四季度';
20
    else
21
      set result := '非法参数';
22
  end case;
23

24
  select concat('你输入的月份为：', month, '，所属季度为：', result);
25
end;

循环#

1
#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
2
WHILE 条件 DO
3
  SQL逻辑...
4
END WHILE;

1
-- while计算从1累加到 n 的值，n 为传入的参数值。
2
-- A.定义局部变量，记录累加之后的值;
3
-- B.每循环一次，就会对 n 进行减1，如果 n 减到0，则退出循环
4

5
create procedure p7(in n int)
6
begin
7
  declare total int default 0;
8

9
  while n>0 do
10
    set total := total + n
11
    set n:=n-1;
12
  end while;
13

14
  select total;
15
end;
16
call p7( n: 100);

1
-- 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
2
REPEAT
3
  SQL逻辑.
4
  UNTIL 条件
5
END REPEAT;

1
-- while计算从1累加到 n 的值，n 为传入的参数值。
2
-- A.定义局部变量，记录累加之后的值;
3
-- B.每循环一次，就会对 n 进行减1，如果 n 减到0，则退出循环
4

5
create procedure p8(innint)
6
begin
7
  declare total int default 0;
8

9
  repeat
10
    set total := total + n;
11
    set n := n - 1;
12
  until n <= 0
13
  end repeat;
14

15
  select total;
16
end;
17

18
call p8( n: 10);
19
call p8( n: 100);

1
[begin label:] LOOP
2
  SQL逻辑..
3
END LOOP [end label];
4

5
LEAVE label;  -- 退出指定标记的循环体
6
ITERATE label;-- 直接进入下一次循环

1
-- loop 计算从1到n之间的偶数累加的值，n为传入的参数值。
2
-- A.定义局部变量，记录累加之后的值;
3
-- B.每循环一次，就会劝进行-1，如果n减到0，则退出循环。------> leave xx
4
-- C.如果当次累加的数据是奇数，则直接进入下一次循坏。-------> iterate xx
5

6
create procedure p10(in n int)
7
begin
8
  declare total int defatult 0;
9

10
  sum: loop
11
    if n <= 10 then
12
      leave sum;
13
    end if;
14

15
    if n %2 = 1 then
16
      set n := n - 1;
17
      iterate sum;
18
    end if;
19

20
    set total := total + n;
21
    set n := n - 1;
22
  end loop sum;
23

24
  select total;
25
end;

游标(cursor)#

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

通俗点讲：类似于 c 语言中的结构体，java 中的实体类。

语法：

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：

OPEN 游标名称;

获取游标记录：

FETCH 游标名称 INTO 变量[,变量];

关闭游标：

CLOSE 游标名称;

案例：

1
-- 游标
2
-- 根据传入的参数uage，来查询用户表tb_user 中， 所有的用户年龄小于uage的用户姓名(name)和专业（profession），
3
-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
4
-- 逻辑:
5
-- A.声明游标，存储查询结果集-
6
-- B.准备:创建表结构
7
-- C.开启游标-
8
-- D.获取游标中的记录
9
-- E.插入数据到新表中-
10
-- F.关闭游标
11

12
create procedure p11(in uage int)
13
begin
14
  declare uname varchar(100);
15
  declare upro varchar(100);
16
  declare u_cursor cursor for select name, profession from tb_user where age <= uage;
17

18
  drop table if exists tb_user_pro;
19
  create table if not exists tb_user_pro(
20
    id int primary key auto_increment,
21
    name varchar(100),
22
    profession varchar(100)
23
  );
24

25
  open u_cursor;
26
  while true do
27
    fetch u_cursor into uname,upro;
28
    insert into tb_user_pro values(null, uname, upro);
29
  end while;
30
  close u_cursor;
31
end;

条件处理程序#

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

语法：

1
DECLARE handler action HANDLERFOR condition value l, condition value.... statement;
2
handler action
3
  CONTINUE: 继续执行当前程序
4
  EXIT: 终止执行当前程序
5
condition value
6
  SQLSTATE sqlstate_value:状态码，如 02000
7
  SQLWARNING:所有以01开头的SQLSTATE代码的简写
8
  NOT FOUND:所有以02开头的SOLSTATE代码的简写
9
  SOLEXCEPTION:所有没有被SOLWARNING 或 NOT FOUND捕获的SOLSTATE代码的简写

案例：

1
create procedure p11(in uage int)
2
begin
3
  declare uname varchar(100);
4
  declare upro varchar(100);
5
  declare u_cursor cursor for select name, profession from tb_user where age <= uage;
6

7
  -- 监控到02000的状态码后，关闭游标后执行exit退出操作。
8
  declare exit handler for not found close u_cursor;
9

10
  drop table if exists tb_user_pro;
11
  create table if not exists tb_user_pro(
12
    id int primary key auto_increment,
13
    name varchar(100),
14
    profession varchar(100)
15
  );
16

17
  open u_cursor;
18
  while true do
19
    fetch u_cursor into uname,upro;
20
    insert into tb_user_pro values(null, uname, upro);
21
  end while;
22
  close u_cursor;
23
end;

介绍#

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

语法#

1
CREATE TRIGGER trigger name
2
BEFORE/AFTER INSERT/UPDATE/DELETE
3
ON tbl name FOR EACH ROW --行级触发器BEGIN
4
  trigger_stmt;
5
END;

1
-- 插入数据触发器
2
create trigger tb_user_insert_trigger
3
  after insert on tb_user for each row
4
  begin
5
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
6
  (null, 'insert', now(), new.id, concat('插入的数据内容为：id=', new.id, ',name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));
7
end;
8

9
-- 查看
10
show triggers;
11

12
-- 删除
13
drop trigger tb_user_insert_trigger;
14

15
-- 插入数据tb_user
16
insert into tb_user(id, name, phtone, email, profession, age, gender, status, createtime) values(25, '二皇子', '1880901212', 'erhuangzi@163.com', '软件工程', 23, '1', '1'1, now());
17

18
-- 修改数据触发器
19
create trigger tb_user_update_trigger
20
  after update on tb_user for each row
21
  begin
22
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
23
  (null, 'update', now(), new.id,
24
   concat('更新之前的数据：id=', old.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
25
    '更新之后的数据：id=', new.id, ',name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));
26
end;
27

28
update tb_user set age = 32 where id = 23;
29
update tb_user set age = 32 where id <= 5; -- 触发器为行级触发器，所以更改几行数据则出发几次，该语句出发5次
30

31
-- 删除数据触发器
32
create trigger tb_user_delete_trigger
33
  after delete on tb_user for each row
34
  begin
35
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
36
  (null, 'insert', now(), old.id,
37
   concat('删除之前的数据：id=', new.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
38
end;
39

40
delete from tb_user where id = 26;

语法：#

1
CREATE FUNCTION 存储函数名称([ 参数列表 ])
2
RETURNS type [characteristic ...]
3
BEGIN
4
  -- SQL语句
5
  RETURN ...;
6
END ;
7

8
-- characteristic说明:
9
-- · DETERMINISTIC:相同的输入参数总是产生相同的结果
10
-- · NO SQL:不包含 SQL语句。
11
-- · READS SOL DATA:包含读取数据的语句，但不包含写入数据的语句,

案例：#

1
create function fun1(n int)
2
returns int deterministic
3
begin
4
  declare total int default 0;
5

6
  while n > 0 do
7
    set total := total + n;
8
    set n := n - 1;
9
  end while;
10

11
  return total;
12
end;

介绍#

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

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

基本语法#

使用全局锁：flush tables with read lock

释放全局锁：unlock tables

演示图

未加全局锁

加了全局锁

介绍#

每次操作锁住整张表。锁定粒度大，发生锁的冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为一下三类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

1
//表级别的共享锁，也就是读锁；
2
//允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作。
3
lock tebles t_student read;
4

5
//表级锁的独占锁，也是写锁；
6
//允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）。
7
lock tables t_stuent write;

介绍#

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

1. 行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
2. 间隙锁(GapLock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
3. 临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

Record Lock(行锁)#

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分。

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

1. 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

行锁类型：

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。

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

查看表锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

GapLock(间隙锁)#

Next-Key Lock(临键锁)#

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

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁 。
2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock退化为间隙锁。
3. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

内存架构#

磁盘架构：#

专用表空间（File-Per-Table Tablespace）

后台线程#

InnoDB的整个体系结构为：

当业务操作的时候直接操作的是内存缓冲区，如果缓冲区当中没有数据，则会从磁盘中加载到缓冲区，增删改查都是在缓冲区的，后台线程以一定的速率刷新到磁盘。

redo log:#

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

Buffer Pool在产生脏页数据的时候，会先将数据存储到 redo log buffer 再存储到 redo log 中进行磁盘持久化存储，在内存出现异常（比如突然断电）时，通过redo log中持久化的数据进行回滚。过程如下图：

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举?

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

undo log:#

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

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

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

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。

这段可能非常难以理解，我们来看一个例子：假如我开启了一个事务，就一个update语句，首先在undolog中记录这个操作并且将数据页变成脏页，更新里面的记录，假如这个操作失败了就根据undolog回滚脏页，成功了就先写入缓冲池中的redolog,然后将缓冲池中的redolog存到磁盘中，后续后台线程会负责将这个更新真正写入到磁盘的数据文件中。假设从缓冲池写入磁盘的redolog失败，那么就相当于事务提交失败，数据就不会更新，缓冲池中的数据根据undolog回滚

MVCC#

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

Mysql#

mysqladmin#

mysqlbinlog#

mysqlshow#

mysqldump#

mysqlimport/source#