MySQL

https://blog.csdn.net/m0_45432976/article/details/129795378

1).连接层

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

2).服务层

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

3).引擎层

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

4).存储层

数据存储层，主要是将数据(如:redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1 存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

1. 建表是指定存储索引

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

2. 查询当前数据库支持的存储引擎

show ENGINES;

1.1 InnoDB

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

2. 特点 DML操作遵循ACID模型，支持事务； 行级锁，提高并发访问性能； 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

3. 文件 xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

4. 逻辑存储结构

• 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
• 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
• 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
• 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

1.2 MyISAM

1. 介绍 MyISAM是MySQL早期的默认存储引擎。
2. 特点 不支持事务，不支持外键 支持表锁，不支持行锁 访问速度快
3. 文件 xxx.sdi：存储表结构信息**（Serialized Dictionary Information）** xxx.MYD: 存储数据 xxx.MYI: 存储索引

1.3Memory

1. 介绍 Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
2. 特点 内存存放 hash索引（默认）
3. 文件 xxx.sdi：存储表结构信息 存储在内存中，只有sdi表结构信息

1.4 区别及特点

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持(5.6版本之后)	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

面试题

• InnoDB引擎与MyISAM引擎的区别 ?

  • InnoDB引擎, 支持事务, 而MyISAM不支持。

  • InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。

  • InnoDB引擎, 支持外键, 而MyISAM是不支持的。

  • 主要是上述三点区别，当然也可以从索引结构、存储限制等方面，更加深入的回答，具体参考如下官方文档：

  • https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

  • https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

1.5 存储引擎的选择

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

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

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

• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。 （redis）

2 索引⭐⭐

2.1 索引的概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优缺点⭐

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

2.2 索引的结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

不同的存储引擎对于索引结构的支持情况。

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

• 二叉树：顺序插入时形成链表

• 红黑树（自平衡二叉树）：数据量大时，层级太深

• b树：B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

  • 特点：
    • 具有中间元素向上分裂的特点
    • 在B树中，非叶子节点和叶子节点都会存放数据。

  

• b+树：所有的元素都是叶子节点，叶子节点形成双向链表（树的高度更低）,非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。（结点存储在页中。逻辑空间结构：表段区页行）

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

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

特点

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

面试题：为什么InnoDB存储引擎选择使用B+tree索引结构?⭐

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

2.3索引分类

1. 索引的分类 在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

聚集索引&二级索引 而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:⭐

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

具体过程如下:

1. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
2. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。 最终拿到这一行的数据，直接返回即可。
3. 回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

思考题

思考题： 以下两条SQL语句，那个执行效率高? 为什么? A. select * from user where id = 10 ; B. select * from user where name = ‘Arm’ ; 备注: id为主键，name字段创建的有索引； 解答： A 语句的执行性能要高于B 语句。 因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

思考题： InnoDB主键索引的B+tree高度为多高呢?

假设: 一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。 高度为2： n * 8 + (n + 1) * 6 = 161024 , 算出n约为 1170 1171 16 = 18736 也就是说，如果树的高度为2，则可以存储 18000 多条记录。 高度为3： 1171 * 1171 * 16 = 21939856 也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

2.4 索引的语法

1. 创建索引的语法 如果不写UNIQUE或FULLTEXT默认常规索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

1. 查看索引

SHOW INDEX FROM table_name ;

1. 删除索引

DROP INDEX index_name ON table_name ;

案例展示

1. 创建相关的表
2. 数据准备好了之后，接下来，我们就来完成如下需求：

• 查看索引

show INDEX from tb_user

• name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。（常规索引）

CREATE index idx_user_name on tb_user (name);

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

CREATE UNIQUE INDEX idx_user_phone on tb_user(phone);

• 为profession、age、status创建联合索引。（创建索引关联三个自动）

CREATE index idx_user_pro_age_sta on tb_user(profession,age,status);

• 为email建立合适的索引来提升查询效率。（常规索引）

CREATE INDEX idx_email on tb_user(email);
DROP INDEX idx_email on tb_user;

2.5 Sql性能分析⭐

1. sql执行频率 MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

• Com_insert: 插入次数
• Com_select: 查询次数
• Com_update: 更新次数

2. 慢查询日志 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

show VARIABLES like 'slow_query_log'

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

开启MySQL慢日志查询开关

slow_query_log=1

设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志

long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log 然后，再次查看开关情况，慢查询日志就已经打开了。

3. profile详情 show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

SELECT @@have_profiling ;

查看支持profile操作

SELECT @@profiling ;

可以看到，当前MySQL是支持 profile操作的，且开关是打开的。如果关闭了，我们可以通过set语句在session/global级别开启profiling：

SET profiling = 1;

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：⭐

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

4. . explain⭐⭐ EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

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(全表扫描) 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
Extra	Using index condition有部分条件无法使用索引， Using index：表示查询可以直接使用索引来获取数据，而不需要访问数据表。 Using where：表示查询需要使用索引来过滤数据

2.6 索引的规则⭐⭐

2.6.1. 最左前缀法则 (opens new window)

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

==如果跳跃某一列，索引将会部分失效(后面的字段索引失效)==。⭐

以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引。

https://blog.csdn.net/m0_45432976/article/details/129795378

2.6.2 范围查询⭐⭐

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为38，就说明范围查询右边的status字段是没有走索引的。

当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为42，就说明所有的字段都是走索引的。 **所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <**⭐

2.6.3 索引失效情况⭐⭐

1. 索引列运算 不要在索引列上进行运算操作， 索引将失效

• 当根据phone字段进行等值匹配查询时, 索引生效。

• 当根据phone字段进行函数运算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

2. 字符串不加引号⭐

   字符串类型字段使用时，不加引号，索引将失效。

3. 模糊查询 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

4. or连接条件⭐ 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。(or连接了无索引字段，那么要全表查询，都全表了索引自然没用了)

5. 数据分布影响 如果MySQL评估使用索引比全表更慢，则不使用索引。 就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

2.6.4 SQL提示

关于profession的索引有两个，如果此时针对profession进行查询，会使用哪个索引？此时就可以借助于 MySQL的SQL提示来完成 SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

EXPLAIN select * from tb_user use index (ids_profession) where profession='软件工程'

ignore index ： 忽略指定的索引。

EXPLAIN select * from tb_user IGNORE index (ids_profession) where profession='软件工程'

force index ： 强制使用索引

EXPLAIN select * from tb_user force index (ids_profession) where profession='软件工程'

2.6.5 覆盖索引

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？

覆盖索引是指查询使用了索引，并且需要返回的列，在该==索引中已经全部能够找到== 。

• 解释

  1. 表的结构示例图

  2. 执行SQL select * from tb_user where id = 2;

     根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

  3. 执行SQL：selet id,name from tb_user where name = 'Arm';

     虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。

  4. 执行SQL：selet id,name,gender from tb_user where name = 'Arm';

     由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

思考题：

• 一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:

  select id,username,password from tb_user where username =‘itcast’;

• 答案: 针对于 username, password建立联合索引,

  ​ sql为: create index idx_user_name_pass on tb_user(username,password);

  ​ 这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。

2.6.6 前缀索引

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

语法

create index idx_xxxx on table_name(column(n)) ;

• 前缀长度 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
• 前缀索引的查询流程(回表后需要再比较以下email是否完成匹配，否则需要继续顺序查找)

2.6.7 单列索引与联合索引

• 单列索引：即一个索引只包含单个列。
• 联合索引：即一个索引包含了多个列

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

可以用use index()建议mysql使用联合索引覆盖索引

联合索引的B+树

2.6.8 索引设计原则⭐⭐

1. 针对于数据量较大（一百多万），且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引（需要遵循最左前缀法则），减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

总结

1.索引概述

索引是高效获取数据的数据结构；

2.索引结构

B+Tree Hash

3.索引分类

主键索引、唯一索引、常规索引、全文索引 聚集索引、二级索引

4.索引语法

create [unique index xxx on xxx(xxx); show index from xxxx drop index xxx on xxxx;

3.sql优化⭐

3.1. 插入数据

3.1.1 insert插入

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

优化方案一

• 批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

优化方案二

• 手动控制事务

start transaction;
    insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
    insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
    insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

优化方案三

• 主键顺序插入，性能要高于乱序插入。

3.1.2 大批量插入数据⭐

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql --local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

3.2 主键优化⭐

主键顺序插入的性能是要高于乱序插入的。 在具体运用中改主键又该如何设计

3.2.1 数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

​ 行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

​ 在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存 储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

3.2.2 页分裂

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

A. 主键顺序插入效果

1. 从磁盘中申请页， 主键顺序插入
2. 第一个页没有满，继续往第一页插入
3. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
4. 当第二页写满了，再往第三页写入

B. 主键乱序插入效果

1. 加入1#,2#页都已经写满了，存放了如图所示的数据

2. 此时再插入id为50的记录，我们来看看会发生什么现象

   会再次开启一个页，写入新的页中吗？

• 页分裂

  • 不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

  • 但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。

  • 但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。

  • 移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。

  • 上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

3.2.3页合并

• 目前表中已有数据的索引结构(叶子节点)如下：

• 当我们对已有数据进行删除时，具体的效果如下: 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

• 当我们继续删除2#的数据记录

• 当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

• 删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

• 这个里面所发生的合并页的这个现象，就称之为 “页合并”。

• 知识小贴士：

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

3.2.4索引设计原则

• 满足业务需求的情况下，尽量降低主键的长度。(二级索引叶子结点可以存储更多行)
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
• 业务操作时，避免对主键的修改。

3.3 order by优化⭐⭐

MySQL的排序，有两种方式：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

#根据age,phone:进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc,phone desc;//Using filesort
#创建索引
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);
#根据age,phone进行降序一个升序，一个降序
公
explain select id,age,phone from tb_user order by age asc,phone desc;//Using index

order by优化原则:⭐

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。否则会再磁盘进行排序

3.4 group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

• 在分组操作时，可以通过索引来提高效率。
• 分组操作时，索引的使用也是满足最左前缀法则的。

索引结构中pro全局有序而age局部有序：

1. age的值可以直接通过index获得不回表 so:Using index；

2. 获得的age全局无，需要在临时表重排以分组，so:Using temp

3.5 limit优化⭐⭐

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

• 优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询(有的版本不支持子查询limit，可以用表连接)形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

3.6 count优化⭐⭐

3.6.1 概述

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。 InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。 如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

3.6.2 count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。 用法：count（*）、count（主键）、count（字段）、count（数字）

count用法	含义
count(主键)	InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段)	没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(数字)	InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*)	InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

3.7 update优化⭐

innoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

update优化
update student set no = '2000100100' where id = 1#根据主键更新行锁
update student set no = '2000100105' where name = '韦一笑'#如果没有索引，会升级为表锁，如果有name索引则为行锁

总结⭐

1. 插入数据 insert批量插入、手动控制事务、主键顺序插入 大批量插入：load data local infile
2. 主键优化 主键长度尽量短、顺序插入 、使用AUTO INCREMENT UUID
3. order by优化 using index:直接通过索引返回数据，性能高 using filesort:需要将返回的结果在排序缓冲区排序
4. group by优化 索引，多字段分组满足最左前缀法则
5. imit优化⭐ 覆盖索引+子查询
6. count优化 性能：count(字段)<count(主键id)<count(1)≈count(*)
7. update优化⭐ 尽量根据主键/索引字段进行数据更新

4.视图⭐

4.1 视图

4.1.1 介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。 通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

4.1.2 语法

1. 创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]
-- 举例
CREATE or REPLACE view stu_v_1 as select id,name from student where id<10 

2. 查询

--查看创建视图语句：
SHOW CREATE VIEW 视图名称;
-- 查看视图数据：
SELECT * FROM 视图名称 ...... ;
show CREATE view stu_v_1
select * from stu_v_1

3. 修改

   --方式一：
   CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ]
   -- 方式二：
   ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] 
   
   alter view stu_v_1 as select id,name,no from student where id<10
   

4. 删除视图

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

4.1.3 检查选项⭐

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。

1. CASCADED 级联。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

2. LOCAL 本地。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

4.1.4 视图的更新⭐

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

• DISTINCT

• GROUP BY

• HAVING

• UNION 或者 UNION ALL

示例演示:

• 上述的视图中，就只有一个单行单列的数据，如果我们对这个视图进行更新或插入的，将会报错。

create view stu_v_count as select count(*) from student; 1
insert into stu_v_count values(10);

4.1.5 视图作用⭐⭐

1. 简单 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2. 安全 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据（select指定的列和行）
3. 数据独立 视图可帮助用户屏蔽真实表结构变化带来的影响。（基表修改字段名，可以用as来屏蔽）

4.2 存储过程

4.2.1 介绍

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

特点:

• 封装，复用:

​ 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。

• 可以接收参数，也可以返回数据

  再存储过程中，可以传递参数，也可以接收返回值。

• 减少网络交互，效率提升

  如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了

4.2.2 基本语法

1. 创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;

create PROCEDURE p1()
BEGIN
select count(*) from student;
END

2. 调用

CALL 名称 ([ 参数 ]); 

call p1()

3. 查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

select * from information_schema.ROUTINES where routine_schema = 'study_mysql'
show CREATE PROCEDURE p1;

4. 删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

4.2.3 变量⭐

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

4.2.3.1 系统变量(@@)

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

1. 查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

2. 设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

注意: mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。 A. 全局变量(GLOBAL): 全局变量针对于所有的会话。 B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

4.2.3.2 用户定义变量(@)

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

1. 赋值

• 方式一

SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;

set @myname = "smile";

赋值时，可以使用 = ，也可以使用 := 。推荐使用 :=，因为判断也是使用=。

• 方式二:

SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;

• 方式二:

SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名

2. 使用

select @myname 

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

4.2.3.3 局部变量(declare)

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

1. 声明 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

   DECLARE 变量名 变量类型 [DEFAULT ... ] ; 
   

2. 赋值

   SET 变量名 = 值 ;
   SET 变量名 := 值 ;
   SELECT 字段名 INTO 变量名 FROM 表名 ... ;
   

示例

CREATE PROCEDURE p2 () BEGIN
	DECLARE stu_count INT DEFAULT 1;
	SELECT COUNT(*) INTO stu_count FROM student;
	SELECT stu_count;
END;

4.2.4 if

1. 介绍 if 用于做条件判断，具体的语法结构为： 在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

2.4.5 参数⭐

1. 介绍 参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下： 用法：

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

• 案例一

根据传入参数score，判定当前分数对应的分数等级，并返回。

score >= 85分，等级为优秀。

score >= 60分 且 score < 85分，等级为及格。

score < 60分，等级为不及格。

CREATE PROCEDURE p4(in score int,out result varchar(10))
BEGIN
	if score >= 85 then
		set result = '优秀';
	elseif socre >= 60 then
		set result = '及格';
	else
		set result = '不及格';
	end if;	
end;

call p4(86,@result)
select @result

案例二：将传入的200制分数进行换算，换算成百分制，然后返回

create PROCEDURE p5(INOUT score DOUBLE)
BEGIN
  set score = score * 0.5;
end;

set @score = 97;
call p5(@score);
select @score;

4.2.6 case

1. 介绍 case结构及作用，和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式： 语法1：

--含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 
--否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;

语法2:

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
-- 立时，执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;

4.2.7 while

1. 介绍 while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;

4.2.8 repeat

1. 介绍 repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。具体语法为：

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

4.2.9 loop

1. 介绍

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用：

LEAVE ：配合循环使用，退出循环。

ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];

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

4.2.10 条件处理程序

4.3 存储函数

1. 介绍 存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;

characteristic说明：

• DETERMINISTIC：相同的输入参数总是产生相同的结果
• NO SQL ：不包含 SQL 语句。
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

4.4 触发器

4.4.1 介绍

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

4.4.2 语法⭐

1. 创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;

2. 查看

SHOW TRIGGERS ; 

3. 删除

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。

4.4.3 案例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中, 包含增加,修改 , 删除 ;

• 表结构准备

-- 准备工作：日志表 user_logs
create table user_logs(
    id int(11) not null 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',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;j

1. 插入数据触发器

-- 插入数据时的触发器
CREATE TRIGGER tb_user_insert_trigger
after insert on tb_user for each row
BEGIN
	insert into user_logs (id,operation,operate_time,operate_id,operate_params) values
	(null,'insert',now(),new.id,concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email));
END;

2. 修改数据触发器

-- 更新数据时的触发器
CREATE TRIGGER tb_user_update_trigger
after update on tb_user for each row
BEGIN
	insert into user_logs (id,operation,operate_time,operate_id,operate_params) values
	(null,'update',now(),new.id,
     concat('更新之前的数据:id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=',old.email,
            ' | 更新之后的数据:id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email));
END;

3. 删除数据的触发器

-- 删除数据时的触发器
CREATE TRIGGER tb_user_delete_trigger
after delete on tb_user for each row
BEGIN
	insert into user_logs (id,operation,operate_time,operate_id,operate_params) values
	(null,'delete',now(),old.id,concat('删除之前的数据:id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=',old.email));
END;

总结

1. 视图(VIEW) 虚拟存在的表，不保存查询结果，只保存查询的SQL逻辑 简单、安全、数据独立

2. 存储过程(PROCEDURE)

   事先定义并存储在数据库中的一段SQL语句的集合。 减少网络交互，提高帙能、封装重用 变量、if、case、参数(in/out/inout)、while、repeat、loop、cursor、handler

3. 存储函数(FUNCTION) 存储函数是有返回值的存储过程，参数类型只能为IN类型 存储函数可以被存储过程替代

4. 触发器(TRIGGER) 可以在表数据进行INSERT、UPDATE、DELETE之前或之后触发 保证数据完整性、日志记录、数据校验

5.锁

5.1 概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

• 全局锁：锁定数据库中的所有表。
• 表级锁：每次操作锁住整张表。
• 行级锁：每次操作锁住对应的行数据。

5.2 全局锁

5.2.1 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。 其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。 为什么全库逻辑备份，就需要加全就锁呢？

A. 我们一起先来分析一下不加全局锁，可能存在的问题。 假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。

• 在进行数据备份时，先备份了tb_stock库存表。
• 然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）。
• 然后再执行备份 tb_order表的逻辑。
• 业务中执行插入订单日志操作。
• 最后，又备份了tb_orderlog表。

此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。 那如何来规避这种问题呢? 此时就可以借助于MySQL的全局锁来解决。

B. 再来分析一下加了全局锁后的情况

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

5.2.2 语法⭐

1. 加全局锁

flush tables with read lock ;

2. 数据备份

mysqldump [-h 192.168.1.1]远程连接 -uroot –p1234 itcast > itcast.sql

3. 释放锁

unlock tables ;

5.2.3 特点

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

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。⭐

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

5.3 表级锁

5.3.1 介绍

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。 对于表级锁，主要分为以下三类：

1. 表锁
2. 元数据锁（meta data lock，MDL）
3. 意向锁

5.3.2 表锁⭐

对于表锁，分为两类：读写锁

• 表共享读锁（read lock）
• 表独占写锁（write lock）

语法：⭐

• 加锁：lock tables 表名… read/write。
• 释放锁：unlock tables / 客户端断开连接 。

特点:

1. 读锁

左侧客户端，对指定表加了读锁，不会影响右侧客户端的读，但是会阻塞右侧客户端的写，也会阻塞左端客户端的写。

2. 写锁

左侧客户端，对指定表加了写锁，会阻塞右侧客户端的读和写，左侧客户端既能读也能写

结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

5.3.3 元数据锁⭐

• meta data lock , 元数据锁，简写MDL。
• MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。⭐
• 这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
• 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。
• 常见的SQL操作时，所添加的元数据锁：
  • 其中 shared_read和shared_write属于MDL读锁（共享），Exclusive属于MDL写锁

​ 查看系统中的元数据锁：

select object type,object schema,object name,lock type,lock duration from performance schema.metadata locks

5.3.4 意向锁

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

2. 分类

• 意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
• 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

5.4 行级锁

5.4.1 介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

5.4.2 行锁

1. 介绍 InnoDB实现了以下两种类型的行锁：

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

常见的SQL语句，在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT（正常）	不加任何	锁
SELECT … LOCK IN SHAREMODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

5.4.3 间隙锁&临键锁⭐⭐

临键锁 = 间隙锁 + 行锁

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

默认为临键锁，所以以下是优化/退化为间隙锁的情况

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。（更新id为5（不存在），会在这个区间加间隙锁）
• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。（满足查询需要的为临键锁）
• 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止（唯一索引不用加行锁，索引是间隙锁）

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

总结

1. 概述 在并发访问时，解决数据访问的一致性、有效性问题 全局锁、表级锁、行级锁

2. 全局锁 对整个数据库实例加锁，加锁后整个实例就处于只读状态 性能较差，数据逻辑备份时使用

3. 表级锁 操作锁住整张表，锁定粒度大，发生锁冲突的概率高 表锁、元数据锁、意向锁

4. 行级锁 操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低 行锁、间隙锁、临键锁

6.InnoDB引擎

6.1 逻辑存储结构

InnoDB的逻辑存储结构如下图所示

1. 表空间

   表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

2. 段

   段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。

3. 区

   区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

4. 页

   页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

5. 行

   行，InnoDB 存储引擎数据是按行进行存放的。在行中，默认有两个隐藏字段：

• Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。

• Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

6.2 架构

6.2.1 概述

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

6.2.2 内存结构

在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、AdaptiveHash Index、Log Buffer。 接下来介绍一下这四个部分。

1. Buffer Pool

   InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

   在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

   缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

   缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

   • free page：空闲page，未被使用。

   • clean page：被使用page，数据没有被修改过。

   • dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

​ 在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。参数设置： show variables like ‘innodb_buffer_pool_size’;

2. Change Buffer

   Change Buffer，更改缓冲区（针对于==非唯一二级索引页==），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么呢? 先来看一幅图，这个是二级索引的结构图：

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

3. Adaptive Hash Index 自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引

​ 自适应哈希索引，无需人工干预，是系统根据情况自动完成。 ​ 参数： adaptive_hash_index

4. Log Buffer Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。 参数: innodb_log_buffer_size：缓冲区大小 innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个： 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。 0: 每秒将日志写入并刷新到磁盘一次。 2: 日志在每次事务提交时写入并每秒刷新到磁盘一次。

6.2.3 磁盘空间

1. System Tablespace 系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等) 参数：innodb_data_file_path

2. File-Per-Table Tablespaces 如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。 开关参数：innodb_file_per_table ，该参数默认开启。

3. General Tablespaces 通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

• 创建表空间

CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;

• 创建表时指定表空间

CREATE TABLE xxx ... TABLESPACE ts_name;

4. Undo Tablespaces 撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。

5. Temporary Tablespaces InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

6. Doublewrite BufferFiles 双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

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

   以循环方式写入重做日志文件，涉及两个文件：ib_logfile0 和 ib_logfile1

6.2.4 后台线程

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread

1. Master Thread 核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收 。
2. IO Thread 在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。

线程类型	默认个数	职责
Read thread	4	负责读操作
Write thread	4	负责写操作
Log thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区内容刷新到磁盘

我们可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息。

show engine innodb status

3. Purge Thread 主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。
4. Page Cleaner Thread 协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

6.3 事务原理⭐⭐

6.3.1 事务基础⭐⭐

1. 事务 事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
2. 特性ACID 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

那实际上，我们研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性的。

事务的特性以及实现方式

特性	实现方式
原子性	undo log
持久性	redo log
一致性	undo log + redo log
隔离性	锁 + MVCC

而对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而隔离性是通过数据库的锁，加上MVCC来保证的。

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。 该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。 如果没有redolog，可能会存在什么问题的？ 我们一起来分析一下。

我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。 当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。 而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。 而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。

那么，如何解决上述的问题呢？ 在InnoDB中提供了一份日志 redo log，接下来我们再来分析一下，通过redolog如何解决这个问题。

有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redolog buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。 而如果脏页成功刷新到磁盘 或 或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 这种先写日志的方式，称之为 ==WAL（Write-Ahead Logging）==。

redolog如何保证持久性⭐

• Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化，当数据刷盘成功时这两部分文件会进删除

事务的隔离级别⭐⭐

• 读未提交:（Read Uncommitted）。这是最低的隔离级别，允许事务读取其他未提交事务的数据，可能导致脏读、不可重复读和幻读等问题。
• 读已提交:（Read Committed）。事务只能读取已经提交的数据，避免脏读问题，但可能出现不可重复读和幻读。
• 可重复读:（Repeatable Read）。在这个级别下，一个事务在执行过程中多次读取相同的数据时，能够保证数据不会被其他事务修改，避免脏读和不可重复读，但可能出现幻读。
• 串行化:（Serializable）。这是最高的隔离级别，事务会按顺序一个接一个地执行，不允许并发执行，可以避免脏读、不可重复读和幻读等问题，但会降低系统的并发性能。

6.3.3 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。

6.4 MVCC⭐

6.4.1 基本概念⭐

1. 当前读 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select …for update、update、insert、delete(排他锁)都是一种当前读。

2. 快照读 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

   • Read Committed：每次select，都生成一个快照读。

   • Repeatable Read：开启事务后第一个select语句才是快照读的地方。

   • Serializable：快照读会退化为当前读。

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

6.4.2 隐藏字段⭐

6.4.2.1 介绍

我们在查看表结构的时候。 实际上除了自己创建的显示字段，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

测试⭐ 进入服务器中的 /var/lib/mysql/目录，在进入对应的数据库，使用 ibd2sdi stu.ibd即可看到相关的字段信息

6.4.3 undolog

6.4.3.1 介绍

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

6.4.3.2 版本链

有一张表原始数据为：

DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的。 DB_ROLL_PTR ： 由于这条数据是才插入的，没有被更新过，所以该字段值为null。

然后，有四个并发事务同时在访问这张表。

每个事务的修改语句，会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。

最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

6.4.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

而在readview中就规定了版本链数据的访问规则：trx_id 代表当前undolog版本链对应事务ID。

条件	是否可以访问	说明
trx_id ==creator_trx_id	可以访问该版本	成立，说明数据是当前这个事务更改的。
trx_id < min_trx_id	可以访问该版本	成立，说明数据已经提交了。
trx_id > max_trx_id	不可以访问该版本	成立，说明该事务是在 ReadView生成后才开启。
min_trx_id <= trx_id<= max_trx_id	如果trx_id不在m_ids中，是可以访问该版本的	成立，说明数据已经提交。

不同的隔离级别，生成ReadView的时机不同：⭐

• READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

6.4.5 原理分析

6.4.5.1 RC隔离级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

我们就来分析事务5中，两次快照读读取数据，是如何获取数据的? 在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下。

那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，到undolog版本链中匹配数据，最终决定此次快照读返回的数据。

1. 先来看第一次快照读具体的读取过程：

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

• 先匹配当前记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。

• 在匹配第二条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。

• 第三条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。

2. 再来看第二次快照读具体的读取过程

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

• 记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。

• 记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②满足 。终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。

6.4.5.3 RR隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。 那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了

我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。 所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。

6.5MySQL管理

1. mysql Mysql客户端工具，-e执行SQL并退出

2. mysqladmin Mysql管理工具

3. mysqlbinlog 二进制日志查看工具

4. mysqlshow 查看数据库、表、字段的统计信息

5. mysqldump⭐ 数据备份工具

6. mysqlimport/source 数据导入工具

   mysqlimport 导入文本文件

   mysqlsource 导入sql文件

https://www.bilibili.com/video/BV1Kr4y1i7ru?spm_id_from=333.788.videopod.episodes&vd_source=5063386180163934afae66c4e87325ac&p=150

总结

存储引擎

• innoDB引擎和MyISAM引擎的区别
  • 事务、外键、行锁

索引⭐⭐

• 数据结构、索引的分类、索引的语法、索引的规则

SQL优化

• 使用原则、sql优化

视图、存储过程、存储函数、触发器

锁

• 全局锁
• 表级锁
  • 表锁、元数据锁、意向锁
• 行级锁
  • 行锁、间隙锁、临键锁

innoDB引擎

• 存储结构、架构、事务、MVCC

MySQL管理

• MySQL管理工具

运维篇

日志

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：

show variables like '%log_error%'

二进制日志⭐⭐

介绍

二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。

作用：

1. 灾难时的数据恢复；
2. MySQL的主从复制。

在MVSOL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%'

日志格式

MySQL服务器中提供了多种格式来记录二进制记录，具体格式及特点如下：

日志格式	含义
statement	基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
row	基于行的日志记录，记录的是每一行的数据变更。(默认)
mined	混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。

查看参数方式：show variables like '%binlog_format%';

日志查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法：

mysqlbinlog[参数选项]logfilename

参数选项：
    -d    指定数据库名称，只列出指定的数据库相关的操作。
    -o    忽略掉日志中的前n行命令。
    -v    将行事件(数据变更)重构为SOL语句。
    -w    将行事件(数据变更)重构为SQL语句，并输出注释信息

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令	含义
reset master	删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始
purge master logs to ‘binlog.***’	删除 *** 编号之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’	删除日志为”yyyy-mm-dd hh24:mi:ss”之前产生的所有日志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除.

show variables like '%binlog_expire_logs_seconds%'

查询日志⭐

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置一下配置：

修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

#该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启
general_log=1
#设置日志的文件名 ， 如果没有指定，默认的文件名为 host_name.log
general_log_file=mysql_query.log

慢查询日志⭐⭐

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10 秒，最小为0，精度可以到微秒。

#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes，如下所述。

#记录执行较慢的管理语句
log_slow_admin_statements = 1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

主从复制

原理

搭建实现

主库配置

关闭防火墙

从库配置

测试

1、在主库上创建数据库、表，并插入数据

create database db01;
use db01;
create table tb_use(
	id int(11) primary key not null auto_increment,
	name varchar(50) not null,
	sex varchar(1)
)engine=innodb default charset=utf8mb4;
insert into tb_user(id, name, sex) valurs (null, 'Tom', '1'), (null, 'Trigger', '0'), (null, 'Dawn', '1');

2、在从库中查询数据，验证主从是否同步。

主从复制是从当前位置开始同步，如果要从头开始，可以将主库数据导出为sql导入到从库，在指定同步位置为当前位置进行主从复制

总结

1. 概述 将主库的数据变更同步到从库，从而保证主库和从库数据一致。 数据备份、失败迁移，读写分离，降低单库读写压力。
2. 原理 ①.主库会把数据变更记录在二进制日志文件Binlog中。 ②.从库连接主库，读取binlog日志，并写入自身中继日志relaylog。 ③.slave重做中继日志，将改变反映它自己的数据。
3. 搭建 ①.准备服务器 ②.配置主库 ③.配置从库 ④.测试主从复制

分库分表

介绍

• 垂直拆分⭐

• 水平拆分⭐

Mycat概述

Mycat入门

Mycat配置

schema.xml

schema.xml作为MyCat中最重要的配置文件之一,涵盖了MyCat的逻辑库 、逻辑表 、分片规则、分片节点及数据源的配置.

主要包含以下三组标签:

• schema标签
• datanode标签
• datahost标签

rule.xml

Mycat分片

垂直分库

水平分表

Mycat配置

https://blog.csdn.net/qq_46065185/article/details/142005511

分片规则

范围分片

取模分片

一致性hash算法

枚举分片

应用指定算法

固定hash算法

字符串hash解析

按天分片

按自然月分片

Mycat管理及监控

读写分离

介绍

一主一从读写分离

双主双从

测试：

分别在两台主库Master1、Master2上执行DDL、DML语句，查看涉及到的数据库服务器的数据同步情况。

create database db01;
use db01;
create table tb_user(
	id in(11)not null primary key,
	name varchar(50) not null,
	sex varcahr(1)
)engine=innodb default charset=utf8mb4

insert into tb user(id,name,sex) values(l,'Tom','1');
insert into tb user(id,name,sex) values(2,'Trigger','0');
insert into tb user(id,name,sex) values(3,'Dawn','1');
insert into tb user(id,name,sex) values(4,"ack Ma','1');
insertinto tb user(id,name,sex) values(5,'Coco','0');
insert into tb user(id,name,sex) values(6,'erry','1');

双主双从读写分离

测试：

登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。

当主库挂掉一个之后，是否能够自动切换。

数据类型

整型

类型名称	取值范围	大小
TINYINT	-128〜127	1个字节
SMALLINT	-32768〜32767	2个宇节
MEDIUMINT	-8388608〜8388607	3个字节
INT (INTEGHR)	-2147483648〜2147483647	4个字节
BIGINT	-9223372036854775808〜9223372036854775807	8个字节

无符号在数据类型后加 unsigned 关键字。

浮点型

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (M, D)，DEC	压缩的“严格”定点数	M+2 个字节

日期和时间

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

字符串

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

二进制类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

权限一览表

具体权限的作用详见官方文档 (opens new window)

GRANT 和 REVOKE 允许的静态权限

Privilege	Grant Table Column	Context
ALL [PRIVILEGES\] (opens new window)	Synonym for “all privileges”	Server administration
ALTER (opens new window)	Alter_priv	Tables
ALTER ROUTINE (opens new window)	Alter_routine_priv	Stored routines
CREATE (opens new window)	Create_priv	Databases, tables, or indexes
CREATE ROLE (opens new window)	Create_role_priv	Server administration
CREATE ROUTINE (opens new window)	Create_routine_priv	Stored routines
CREATE TABLESPACE (opens new window)	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES (opens new window)	Create_tmp_table_priv	Tables
CREATE USER (opens new window)	Create_user_priv	Server administration
CREATE VIEW (opens new window)	Create_view_priv	Views
DELETE (opens new window)	Delete_priv	Tables
DROP (opens new window)	Drop_priv	Databases, tables, or views
DROP ROLE (opens new window)	Drop_role_priv	Server administration
EVENT (opens new window)	Event_priv	Databases
EXECUTE (opens new window)	Execute_priv	Stored routines
FILE (opens new window)	File_priv	File access on server host
GRANT OPTION (opens new window)	Grant_priv	Databases, tables, or stored routines
INDEX (opens new window)	Index_priv	Tables
INSERT (opens new window)	Insert_priv	Tables or columns
LOCK TABLES (opens new window)	Lock_tables_priv	Databases
PROCESS (opens new window)	Process_priv	Server administration
PROXY (opens new window)	See proxies_priv table	Server administration
REFERENCES (opens new window)	References_priv	Databases or tables
RELOAD (opens new window)	Reload_priv	Server administration
REPLICATION CLIENT (opens new window)	Repl_client_priv	Server administration
REPLICATION SLAVE (opens new window)	Repl_slave_priv	Server administration
SELECT (opens new window)	Select_priv	Tables or columns
SHOW DATABASES (opens new window)	Show_db_priv	Server administration
SHOW VIEW (opens new window)	Show_view_priv	Views
SHUTDOWN (opens new window)	Shutdown_priv	Server administration
SUPER (opens new window)	Super_priv	Server administration
TRIGGER (opens new window)	Trigger_priv	Tables
UPDATE (opens new window)	Update_priv	Tables or columns
USAGE (opens new window)	Synonym for “no privileges”	Server administration

GRANT 和 REVOKE 允许的动态权限

Privilege	Context
APPLICATION_PASSWORD_ADMIN (opens new window)	Dual password administration
AUDIT_ABORT_EXEMPT (opens new window)	Allow queries blocked by audit log filter
AUDIT_ADMIN (opens new window)	Audit log administration
AUTHENTICATION_POLICY_ADMIN (opens new window)	Authentication administration
BACKUP_ADMIN (opens new window)	Backup administration
BINLOG_ADMIN (opens new window)	Backup and Replication administration
BINLOG_ENCRYPTION_ADMIN (opens new window)	Backup and Replication administration
CLONE_ADMIN (opens new window)	Clone administration
CONNECTION_ADMIN (opens new window)	Server administration
ENCRYPTION_KEY_ADMIN (opens new window)	Server administration
FIREWALL_ADMIN (opens new window)	Firewall administration
FIREWALL_EXEMPT (opens new window)	Firewall administration
FIREWALL_USER (opens new window)	Firewall administration
FLUSH_OPTIMIZER_COSTS (opens new window)	Server administration
FLUSH_STATUS (opens new window)	Server administration
FLUSH_TABLES (opens new window)	Server administration
FLUSH_USER_RESOURCES (opens new window)	Server administration
GROUP_REPLICATION_ADMIN (opens new window)	Replication administration
GROUP_REPLICATION_STREAM (opens new window)	Replication administration
INNODB_REDO_LOG_ARCHIVE (opens new window)	Redo log archiving administration
NDB_STORED_USER (opens new window)	NDB Cluster
PASSWORDLESS_USER_ADMIN (opens new window)	Authentication administration
PERSIST_RO_VARIABLES_ADMIN (opens new window)	Server administration
REPLICATION_APPLIER (opens new window)	PRIVILEGE_CHECKS_USER for a replication channel
REPLICATION_SLAVE_ADMIN (opens new window)	Replication administration
RESOURCE_GROUP_ADMIN (opens new window)	Resource group administration
RESOURCE_GROUP_USER (opens new window)	Resource group administration
ROLE_ADMIN (opens new window)	Server administration
SESSION_VARIABLES_ADMIN (opens new window)	Server administration
SET_USER_ID (opens new window)	Server administration
SHOW_ROUTINE (opens new window)	Server administration
SYSTEM_USER (opens new window)	Server administration
SYSTEM_VARIABLES_ADMIN (opens new window)	Server administration
TABLE_ENCRYPTION_ADMIN (opens new window)	Server administration
VERSION_TOKEN_ADMIN (opens new window)	Server administration
XA_RECOVER_ADMIN (opens new window)	Server administration

图形化界面工具

• Workbench(免费): http://dev.mysql.com/downloads/workbench/
• navicat(收费，试用版30天): https://www.navicat.com/en/download/navicat-for-mysql
• Sequel Pro(开源免费，仅支持Mac OS): http://www.sequelpro.com/
• HeidiSQL(免费): http://www.heidisql.com/
• phpMyAdmin(免费): https://www.phpmyadmin.net/
• SQLyog: https://sqlyog.en.softonic.com/

安装

小技巧

1. 在SQL语句之后加上\G会将结果的表格形式转换成行文本形式
2. 查看Mysql数据库占用空间：

SELECT table_schema "Database Name"
     , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;