explain 各字段含义:#

• id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下（多表查询）；id不同，值越大越先执行（子查询））

• select_type：表示 select 的类型，常见取值有 SIMPLE（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含了子查询）等

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

  • NULL：查询不访问任何表时出现，一般不会出现

  • system：当访问系统表或表中仅有一行记录时出现

  • const：使用 primary key 或者 unique 索引进行精确匹配，且只匹配到一行记录时会出现

  • eq_ref：在连接查询里使用 primary key 或者 unique 索引进行连接，且对于每个来自前面表的记录，在当前表中都能通过索引找到唯一匹配的记录时出现

  • ref：使用非唯一性索引查询时会出现

  • range：使用索引进行范围查询时出现

  • index：查询需要扫描整个索引树来获取数据时出现（不一定扫描全表）

  • all：扫描全表数据时出现

• 从优到劣：NULL> system > const > eq_ref > ref > range > index > all

• possible_key：可能应用在这张表上的索引，一个或多个

• Key：实际使用的索引，如果为 NULL，则没有使用索引

• Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

• rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的

• filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

2.1二叉树#

B+Tree是常用的索引
那么为什么存储引用多数都使用B+Tree这个数据结构呢?
继续往下看

接下来用几个案例来证明为什么要使用B+Tree

• 二叉树的缺点可以用红黑树来解决：
  

  

  
  红黑树也存在大数据量情况下，层级较深，检索速度慢的问题。

2.2B-Tree（多路平衡查找树）#

以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）：

• 每一个节点都存储数据

演示网站：

https://www.cs.usfca.edu/~galles/visualization/BTree.html

2.3B+Tree#

• 所有的数据都会出现在叶子节点

• 叶子节点形成一个单向链表

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能：

2.4Hash#

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

• 如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决

• Hash索引只能用于对等比较（=、in），不支持范围查询（between、>、<、…）

• 无法利用索引完成排序操作

• 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的。

2.5思考#

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

1. 相对于二叉树，层级更少，搜索效率高

2. 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

3. 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

3.1 分类#

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

3.2 演示图#

3.3 聚集索引选取规则#

• 如果存在主键，主键索引就是聚集索引

• 如果不存在主键，将使用第一个唯一(unique)索引作为聚集索引

• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

3.4 思考#

（1）以下 SQL 语句，哪个执行效率高？为什么？

1
select * from user where id = 10;
2
select * from user where name = 'Arm';
3
-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

（2）InnoDB 主键索引的 B+Tree 高度为多少？

答：假设一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键假设为bigint，占用字节数为8. 可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字节数，n 表示当前节点存储的key的数量，(n + 1) 表示指针数量（比key多一个）。算出n约为1170。

如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736； 如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856。

另外，如果有成千上万的数据，那么就要考虑分表。

5.1 最左前缀法则#

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

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

• 比较特殊的一种情况是如果直接跳跃第一列，那么第一列后面的索引都会失效，即此时联合索引完全失效

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

• 字段的位置可以任意，只要缺少复合索引某个字段，后面的字段索引全部失效

5.2 索引失效情况#

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

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

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

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

5. 如果 MySQL 评估「走索引比全表扫描还慢」，就不用索引。比如查询条件能命中表里大部分行时，走索引还得一行为一次回表，代价反而比直接全表扫更大

5.3 SQL 提示#

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

使用索引：

1
select * from 表名 use index(索引名) where 查询条件;

不使用哪个索引：

1
select * from 表名 ignore index(索引名) where 查询条件;

必须使用哪个索引：

1
select * from 表名 force index(索引名) where 查询条件;

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

5.4 覆盖索引#

即查询使用了索引，并且需要返回的列，在该索引中已经全部能找到

尽量使用覆盖索引，减少select *的书写

5.5 前缀索引#

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

1
create index 索引名 on 表名(列名(n));

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

求选择性公式（截取长度可以任取，不断运行，直到合适为止）：

1
select count(distinct 列名) / count(*) from 表名;
2
select count(distinct substring(列名, 1, 截取长度)) / count(*) from 表名;

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

5.6 不满足最左前缀法则仍可能触发复合索引的情况#

只是可能触发，优化器可能仍选择全表扫描。

5.7 单列索引和联合索引#

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

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

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

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

5.8 设计原则#

1. 针对于数据量较大，且查询比较频繁的表建立索引

2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率

7. 如果索引列不能存储NULL值，请在创建表时使用not NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

1.1 普通插入#

1. 采用批量插入（一次插入的数据不建议超过1000条）

2. 手动提交事务

3. 主键顺序插入

1.2 大批量插入#

如果一次性需要插入大批量数据，使用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';

• 在Windows的cmd命令行中进行load导入数据时，/root/sql1.log路径应使用绝对路径，格式为C:\\path\\to\\file.log（注意转义反斜杠）或 C:/path/to/file.log（使用正斜杠），而且需要根据不同文件的默认换行符使用合适的换行符替换\n

2.1 页分裂#

页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列

2.1.1 主键顺序插入#

当主键是顺序递增时，InnoDB会将新记录插入到当前页的末尾。如果当前页已满或剩余空间不足以插入这条记录，则分配一个新页并维护一个双向指针建立两个页的联系，然后继续插入，依次类推，直到插入完毕

按照顺序插入1,2,3,4,5,6,7,8,9,10,11,12,13,14

2.1.2 主键乱序插入#

当主键无序时，InnoDB需要找到合适的插入位置进行插入。如果目标页已满或剩余空间不足以插入这条记录，会触发页分裂，即从当前页中间处分裂成两个子页并维护双向指针建立页之间的联系，然后继续插入，依次类推，直到插入完毕

插入50

2.2 页合并#

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并（将要合并的页合并到被删除元素的页中）以优化空间使用。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定

2.3 主键设计原则#

• 满足业务需求的情况下，尽量降低主键的长度

• 插入数据时，尽量选择顺序插入，选择使用 auto_increment 自增主键

• 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号

• 业务操作时，避免对主键的修改

6.1 count的几种用法#

• 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值

• 用法：count(*)、count(主键)、count(字段)、count(1)

  • count(主键)跟count(*)一样，因为主键不能为空

  • count(字段)只计算字段值不为NULL的行

  • count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count(*)一样，也可用其他非0数字代替1

  • count(null)返回0

6.2 各种用法的性能#

• count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）

• count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加

• count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加

• count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序：count(字段) < count(主键) < count(1) < count(*)，所以尽量使用 count(*)

1.1 语法#

创建视图

1
create [or replace] view 视图名称(列名列表) as select语句[with[cascaded|local] check option]

查看创建视图语句

1
show create view 视图名称;

查看视图数据

1
select * from 视图名称…;

查看数据中中所有视图

1
show full tables in 数据库名 where Table_type = 'VIEW';

修改视图

1
create [or replace] view 视图名称(列名列表) as select语句 [with [cascaded|local] check option];  或
2
alter view 视图名称(列名列表) as select语句 [with [cascaded|local] check option];

删除视图

1
drop view [if exists] 视图名称[,视图名称];

1.2 检查选项#

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

视图的插入、删除、更新语句和基本表的语句一致，当执行增删改操作时，实际改变的是基本表中的数据，但是不建议通过视图更改基本表。

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

cascaded：在对视图进行更删改操作时，会核查该视图及所有直接或间接依赖的视图创建语句中的条件，只有当所有条件都满足才能够操作成功，例如：

• create view v1 as select id,name from student where id<=20;因为没有指定with check option子句，插入时不会核查条件id<=20，直接插入成功

• create view v2 as select id,name from v1 where id>10 with cascaded check option;插入时会核查v2中的条件id>10和v1中的条件id<=20，只有全部满足时才会插入成功，相当于给v1添加了with cascaded check option子句

• create view v3 as select id,name from v2 where id<=15;由于v3没有指定with check option子句，不会核查条件id<=15，但是由于v2指定了with cascaded check option子句，所以会核查v2的条件id>10和v1的条件id<=20;

local：在对视图进行更删改操作时，会核查该视图及所有直接或间接依赖的且使用with check option子句的视图创建语句中的条件，只有当所有条件都满足才能够操作成功，例如：

• create view v4 as select id,name from student where id<=20;因为没有指定with check option子句，插入时不会核查条件id<=20，直接插入成功

• create view v5 as select id,name from v4 where id>10 with local check option;插入时会核查v5中的条件id>10，因为v4中没有使用with check option子句，所以不会核查v4中的条件id<=20，也就是说，不会给v4添加with local check option子句

• create view v6 as select id,name from v5 where id<=15;因为没有指定with check option子句，插入时不会核查条件id<=15，由于v5使用了with local check option子句，所以会核查v5中的条件id>10，又因为v4中没有使用with check option子句，所以不会核查v4中的条件id<=20，也就是说，仅仅核查有with local check option子句的视图中的条件

1.3 更新及作用#

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系，即视图中的每一行必须直接对应基础表中的唯一一行，不能有多行映射到视图中的同一行，也不能有视图中的一行映射到基础表中的多行

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

1. 聚合函数或窗口函数SUM()、min()、max()、count()等

2. distinct

3. group by

4. having

5. union 或者 union all

1.4 视图的作用#

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

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

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

2.1 系统变量#

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(global)、会话变量(session)

查看系统变量

1
show [session|global] variables ;    --查看所有系统变量
2
show [session|global] variables like '...';    --可以通过like模糊匹配方式查找变量
3
select @@[session.|global.]系统变量名;    --查看指定变量的值

设置系统变量

1
set [session|global] 系统变量名 = 值;    --设置全局/会话变量
2
set @@global.系统变量名 = 值, @@session.系统变量名 = 值;    --同时设置全局系统变量和会话系统变量

• 如果没有指定 session / global，默认 session，会话变量

• myesql 服务器重启之后，所设置的全局参数会失效，要想不失效，需要更改/etc/my.cnf 中的配置。

2.2 用户定义变量#

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

赋值

1
set @var_name = expr [,@var_name = expr]...;    --定义单个或多个变量
2
set @var_name := expr [,@var_name := expr]...;
3

4
select @var_name := expr [,@var_name = expr]...;
5
select 字段名 into @var_name from 表名;    --将查询结果赋值给变量@var_name

使用

1
select @var_name;

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

2.3 局部变量#

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

声明

1
declare 变量名 变量类型 [default 默认值];

• 变量类型就是数据库字段类型：int、bigint、char、varchar、date、TIME等

赋值

1
set 变量名=值;
2

3
set 变量名:=值;
4

5
select 字段名 into 变量名 from 表名 ...;    --将查询结果赋值给局部变量

6.1 while#

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句

语法

1
--先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
2
while 循环条件 do
3
  SOL逻辑...
4
end while;

案例

1
--计算从1累加到 n 的值
2
create procedure p7(in n int)
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
  select total;
12
end;
13
call p7( n: 100);

6.2 repeat#

repeat是有条件的循环控制语句,当满足条件的时候退出循环

与 while 区别：

1. 先进行循环一次再判断。相当于 c 语言中的 do while();

2. 满足条件则退出

语法

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

案例

1
--计算从1累加到 n 的值
2
create procedure p8(in n int)
3
begin
4
  declare total int default 0;
5

6
  repeat
7
    set total := total + n;
8
    set n := n - 1;
9
  until n <= 0
10
  end repeat;
11

12
  select total;
13
end;
14

15
call p8( n: 100);

6.3 loop#

loop 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合以下两个语句使用

1. leave：配合循环使用，退出循环（类似break）

2. iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环（类似continue）

语句

1
--label：loop循环的名字
2
[begin label:] loop
3
  SQL逻辑
4
end loop [end label];
5

6
leave label;  -- 退出指定标记的循环体
7
iterate label;  -- 直接进入下一次循环

案例

1
--计算从1到n之间的偶数累加的值
2
create procedure p10(in n int)
3
begin
4
  declare total int default 0;
5

6
  sum: loop
7
    if n <= 1 then
8
      leave sum;
9
    end if;
10

11
    if n %2 = 1 then
12
      set n := n - 1;
13
      iterate sum;
14
    end if;
15

16
    set total := total + n;
17
    set n := n - 1;
18
  end loop sum;
19

20
  select total;
21
end;

1.1 语句#

使用全局锁

1
flush tables with read lock;

拷贝数据库

1
mysqldump [--single-transaction] -uroot -p[123456] itcast > itcast.sql

• 不属于MySQL命令，需要退出数据库管理系统后在命令行执行

• 只适用于支持 可重复读隔离级别的事务 的存储引擎

• -u后面跟用户名，如root用户，但要保证用户有足够权限拷贝数据库

• -p后面可以跟用户密码，也可以不跟，不跟命令执行后需要手动输入密码，建议不加密码

• itcast代表要拷贝的数据库，只写数据库名就可以了

• itcast.sql可以写绝对路径，但要保证路径存在，格式是D:/backup/itcast.sql或D:\\backup\\itcast.sql

释放全局锁

1
unlock tables;

1.2 特点#

数据库中加全局锁，是一个比较重的操作，存在以下问题:

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟（该结构会在后续主从复制讲解）

解决方法：

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份，通过加上这个参数，确保了在备份开始时创建一个一致性的快照，通过启动一个新的事务来实现这一点（该事务的隔离级别是Repeatable Read级别），从而确保在备份数据库时可以对数据库数据进行操作。

2.1 表锁#

对于表锁，分为两类：

1. 表共享读锁（read lock）：当前客户端和其他客户端都能读，当前客户端不能写，其他客户端写被阻塞

2. 表独占写锁（write lock）：当前客户端可以读和写，其他客户端的读和写会被阻塞

加锁

1
lock tables 表名... read|write;

释放锁

1
unlock tables;

• 用户端断开与数据库的连接会自动释放锁，如事务结束事务内加的锁全部释放

2.2 元数据锁（MDL）#

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

• 元数据：描述数据库对象结构的信息，而不是实际的数据内容，是描述数据库对象的结构和属性的信息，如表结构、视图定义等

元数据锁是为了避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查时，加MDL读锁（共享）；当对表结构进行变更时，加MDL写锁（排他）

• SHARED_READ和SHARED_WRITE是兼容的，即可以同时存在，但是这两个锁和EXCLYSIVE是互斥的，即EXCLYSIVE和他们不能同时存在，会发生阻塞

查看元数据锁

1
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

2.3 意向锁#

当线程A对基本表某一行加行锁后，如果线程B要对基本表加表锁，那么MySQL会检查基本表的每一行，判断是否有其他线程加的锁，如果有，会判断表锁和行锁是否冲突，如果不冲突会直接加锁，如果冲突会被阻塞，直到其他线程全部释放锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

意向锁分为两类：

• 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排他锁（write）互斥

• 意向排他锁（IX）：与表锁共享锁（read）和排他锁（write）都互斥，意向锁之间不会互斥

意向锁的好处：

1. 如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢

2. 那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录

查看锁及元数据锁的加锁情况

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

添加意向共享锁

1
select ... lock in share mode;

• 执行语句后，MySQL会先在表上加上意向共享锁，然后对读取的记录加共享锁，也就是说会加两个锁

添加意向独占锁

1
insert、 update、 delete、 select ... for update

• 在执行增删改语句后会自动加上意向独占锁，不需要手动指定

• 执行语句后，MySQL会先在表上加上意向独占锁，然后对读取的记录加独占锁，也就是说会加两个锁

2.4 AUTO-INC锁#

表里的主键通常都会设置成自增的，这是通过对主键字段声明 auto_increment 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 auto_increment 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 auto_increment 修饰的字段的值是连续递增的。

但是，AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 auto_increment 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

• 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；

• 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。

• 当 innodb_autoinc_lock_mode = 1：

  • 普通 insert 语句，自增锁在申请之后就马上释放；

  • 类似 insert ... select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。

3.1 Record Lock（行锁）#

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

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

   1. 当前客户端和其他客户端都能读，当前客户端不能写，其他客户端写被阻塞

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

   1. 当前客户端可以读和写，其他客户端的读和写会被阻塞

行锁类型：

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

1. 通过唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁 （注意是两者都要满足,才会优化）

2. InnoDB的行锁是针对于索引加的锁，如果不通过索引检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

查看意向锁及行锁的加锁情况

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

3.2 Gap Lock（间隙锁）和Next-Key Lock（临键锁）#

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

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

   1. 会在应存在的位置（间隙）插入一个间隙锁，其他事务访问这个间隙时会被阻塞

2. 索引上的等值查询(非唯一性索引)：向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁

   1. 如果值存在，会把查询的节点加行锁，并且在查询的节点前后两个间隙都添加间隙锁

   2. 如果值不存在，会在应存在的位置（间隙）插入一个间隙锁

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

   1. 对查询范围内的所有行加行锁，对查询范围内的所有间隙加间隙锁

4. 索引上的范围查询(非唯一索引)：向左遍历时第一个范围不满足查询范围，向右遍历时最后一个范围不满足查询范围

   1. 对查询范围内的所有行加行锁，对查询范围内的所有间隙以及起始点的前一个间隙和终止点的后一个间隙加间隙锁

2.1 内存架构#

• adaptive_hash_index：控制是否启用自适应哈希索引，ON表示开启，OFF表示关闭，默认值是ON；具体操作参考系统变量。

2.2 磁盘结构#

• innodb_data_file_path：用于定义InnoDB的系统表空间（System Tablespace）的文件路径、大小和属性。
  

  

• innodb_file_per_table：控制InnoDB是否为每个表创建独立的表空间文件，ON表示每个表都有自己的表空间文件，OFF表示所有表的数据和索引存储在系统表空间中，默认值是ON。

通用表空间：将多个表的数据存储在一个共享的文件中，方便管理和维护。

创建通用表空间文件

1
create tablespace tablespace_name
2
add datafile 'file_name.ibd'
3
[file_block_size = value]
4
[engine [=] InnoDB];
5

6
tablespace_name：通用表空间的名称
7
file_name.ibd：表空间文件的路径和名称，要确保指定的文件路径是MySQL可访问的，并且有足够的权限
8
file_block_size：可选参数，指定表空间的文件块大小（通常与表的页大小一致），必须与表的页大小一致（例如16K）
9
engine：指定存储引擎，默认为 InnoDB。

创建表时指定表空间

1
create table ...[tablespace tablespace_name];
2

3
tablespace_name：指定表存储的通用表空间名称

将现有表移动到通用表空间

1
alter table table_name tablespace tablespace_name;

删除通用表空间

1
drop tablespace tablespace_name;

2.3 后台线程#

3.1 redo log#

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

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

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

问题：数据为什么要通过redolog写入ibd表空间文件，而不是直接从Buffer Pool直接刷新到磁盘ibd文件？

答：Buffer Pool 刷盘是随机写：数据页在磁盘上的位置是分散的、随机的，每次刷盘都需要寻址，性能较低。Redo Log 是顺序写：每次写入都是追加到日志文件的末尾，性能非常高。

3.2 undo log#

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

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

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

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

3.3 MVCC#

3.3.2 记录中的隐藏字段#

每一张创建的表都有两个或三个隐藏字段：DB_TRX_ID、DB_ROOL_PRT、DB_ROW_ID（表没有主键时存在）

3.3.3 undo log#

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

当insert的时候，产生的undoloq日志只在回滚时需要，在事务提交后，可被立即删除。

当update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

那么何时删除？

• 当所有依赖于该undo log的快照读取操作结束后，undo log才会被删除。这意味着如果有一个事务正在进行快照读取，并且依赖于某个undo log，那么这个undo log会一直保留直到该事务结束。

undo log版本链：

3.3.4 readview#

ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id

ReadView中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	ReadView创建者的事务ID

• trx_id：访问那行数据的更改事务的id

READ COMMITTED

在不可重复读下的readview生成时机

以事务5的第一条查询为例，判断过程是：

1. 记录一次 ReadView
2. 拿当前记录的 DB_TRX_ID=4 按版本链规则判断 → 4 在 m_ids 中（不可见）
3. 沿版本链找到下一条 DB_TRX_ID=3 → 3 仍在 m_ids 中（不可见）
4. 再找下一条 DB_TRX_ID=2 → 满足第 2 条规则（可见）
5. 于是读到 0x00002 指向的记录（id: 30, age: 3, name: A30）

事务5的第二条查询语句重新记录一次ReadView读视图，然后根据新的ReadView读视图重新判断，直到找到满足版本链数据访问规则的一条版本记录为止，所以两次查询结果不一定一致

REPEATBLE READ

在可重复读下的访问规则的readview生成时机

查询过程和READ COMMITTED相同，只是事务5的第二条查询语句不会重新生成ReadView读视图，会复用第一条查询语句的ReadView读视图，所以两次查询的结果一致

总结

到这可以看出这个访问规则的逻辑其实很简单
不可重复读：每次都会生成新的readview，而且是读取最近已经提交的数据，所以这样就会造成不可重复
可重复读：第一次查询会生成readview，后续都是复用第一次的，所以这就是为什么可以重复读

MVCC 一句话总结

RC（读已提交）：每次 select 都重新生成 ReadView → 可能读到别人新提交的数据 → 不可重复读。

RR（可重复读）：只在第一次 select 生成 ReadView，后续复用 → 两次查询结果一致。 MVCC = 隐藏字段（trx_id、roll_pointer）+ undo log 版本链 + ReadView，三者配合实现快照读不加锁。

2.1 mysql#

该mysql不是指mysql服务，而是指mysql的客户端工具

1
mysql [options] [database]
2

3
options选项:
4
-u[username]|--user=username : 指定用户名，如-uroot
5
-p[password]|--password[=password] : 指定用户密码，如-p123456
6
-h[host]|--host=host : 指定服务器IP或域名，如-h127.0.0.1
7
-P[port]|--port=port : 指定连接端口（P为大写），如-P3306
8
-e[execute]|execute=execute : 在客户端执行SQL语句并退出（无需进入mysql系统），但前面要跟上操作的数据库名，SQL用双引号包裹，如mysql -uroot -p123456 mysql -e"select * from user"

2.2 mysqladmin#

mysqladmin是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前运行状态，创建并删除数据库等

1
## 通过帮助文档查看选项：
2
mysqladmin --help
3

4
示例选项:
5
create dbname : 创建指定数据库，如mysqladmin -uroot -p123456 create text
6
drop dbname : 删除指定数据库，如mysqladmin -uroot -p123456 drop text
7
ping : 检查MySQL服务器是否正在运行，如mysqladmin -uroot -p123456 ping
8
status : 查看服务器状态，如mysqladmin -uroot -p123456 status
9
shutdown : 关闭MySQL服务器，如mysqladmin -uroot -p123456 shutdown

2.3 mysqlbinlog#

由于服务器生成的二进制文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具（需要管理员身份）

1
mysqlbinlog [options] log-files1 log-files2 ...
2

3
mysqlbinlog log-file
4

5
options选项:
6
-d dbname|--database=dbname : 指定数据库名称，只列出指定的数据库相关操作，如mysqlbinlog -d mysql binlog.000001
7
-o n|--offset=n : 忽略日志中的前n行数据，如mysqlbinlog -o 10 binlog.000001
8
-r filename|--result-file=filename : 将输出的文本日志格式写到目标文件filename中,filename可以用绝对路径，如
9
mysqlbinlog -s binlog.000001 -r "F:\login.000001"
10
-s|--short-form : 让输出内容更简洁，只输出必要的信息，如mysqlbinlog -s mysql binlog.000001
11
--start-datetime : 读取指定开始时间之后的事件，如mysqlbinlog --start-datetime="2025-03-18 10:00:00" binlog.000001
12
--stop-datetime : 读取指定结束时间之前的时间，如mysqlbinlog --stop-datetime="2025-03-18 12:00:00" binlog.000001
13
--start-position : 指定从二进制日志的哪个位置开始读取事件,如mysqlbinlog --start-position=100 binlog.000001
14
--stop-position : 指定读取二进制日志时的结束位置，如mysqlbinlog --stop-position=200 binlog.000001

• 在使用mysqlbinlog之前，需要先定位到binlog.000001所在文件夹，否则后面的使用要用到绝对路径或相对路径

2.4 mysqlshow#

客户端对象查找工具，用来很快地查看存在哪些数据库、数据库中的表、表中的列和或者索引

1
mysqlshow [options] [db_name [table_name [col_name]]]
2

3
options选项:
4
--count : 显示数据库及表的统计信息（数据库、表均可以不指定）
5
-i : 显示指定数据库或者指定表的状态信息
6

7
示例:
8
## 查询每个数据库的表的数量及表中记录的数量
9
mysqlshow -uroot -p123456 --count
10
## 查询mysql库中每个表中的字段数及行数
11
mysqlshow -uroot -p123456 mysql --count
12
## 查询mysql库中user表的详细信息
13
mysqlshow -uroot -p123456 mysql user --count

2.5 mysqldump#

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表、及插入表的SQL语句

1
## 目标文件filename.sql可以用绝对路径或相对路径
2
mysqldump [options] db_name[tables] > filename.sql                ##备份指定数据库中的部分或全部表到目标文件
3
mysqldump [options] --database|-B db1 [db2 db3 ...] > filename.sql                ##备份多个指定的数据库到目标文件
4
mysqldump [options] --all-databases|-A > filename.sql                ##备份 MySQL 服务器上的所有数据库到目标文件
5

6
options连接选项:
7
-u[username]|--user=username : 指定用户名
8
-p[password]|--password[=password] : 指定用户密码，如-p123456
9
-h[host]|--host=host : 指定服务器IP或域名，如-h127.0.0.1
10
-P[port]|--port=port : 指定连接端口（P为大写），如-P3306
11

12
options输出选项:
13
--add-drop-database : 在每个数据库创建语句前加上drop database语句
14
--add-drop-table : 在每个表创建语句前加上drop table语句，默认开启，不开启（--skip-add-drop-table）
15
-n|--no-create-db : 不包含数据库的创建语句
16
-t|--no-create-info : 不包含数据表的创建语句
17
-d|--no-data : 不包含数据
18
-T filename|--tab[=filename] : 自动生成两个文件，一个.sql文件，创建表结构的语句，一个.txt文件，数据文件,例如：
19
## 拷贝mysql数据库下的user表
20
mysqldump -u root -p --tab=/path/to/export/directory mysql user 或
21
mysqldump -u root -p -T /path/to/export/directory mysql user

-T filename|—tab[=filename]参数：

• 生成的两个文件文件名和表名一致

• filename指文件存放路径，可以是绝对路径，也可以是相对路径，如果当前已定位到指定路径，可以不指定filename

• -T后必须跟filename，而—tab后可以不跟filename

2.6 mysqlimport/source#

mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件

1
mysqlimport [options] db_name textfile1 [textfile2 ...]
2

3
示例：mysqlimport -uroot -p text /tmp/city.txt

如果需要导入sql文件，可以使用mysql中的source命令

1
## filename.sql可以使用路径
2
source filename.sql;