Mysql高级篇（面试必备）

隔离级别	脏读	不可重复读	幻读	丢失更新	性能	实现机制
读未提交	❌	❌	❌	❌	最高	无锁或仅写锁
读已提交	✅	❌	❌	❌	较高	MVCC快照（每次读最新提交版本）
可重复读	✅	✅	InnoDB✅	✅	中等	MVCC快照 + 间隙锁/临键锁
串行化	✅	✅	✅	✅	最低	所有操作加锁，串行执行

Read uncommitted(读未提交)

- 机制：允许读取未提交的数据，无锁或仅用排他锁（写操作）。

- - 写操作加的锁是 排他锁（X锁），它仅阻止其他事务修改同一数据，但 不阻止读操作。
  - 读操作不需要加锁，因此可以直接读取未提交的数据，即使这些数据可能被回滚（造成脏读问题）

- 问题：所有并发问题均可能发生。
- 适用场景：对数据一致性要求极低，如统计近似值。

READ COMMITTED(读已提交)

- 机制：

- - 锁机制：写操作加排他锁，读操作无锁，但每次读取最新已提交数据。
  - MVCC（多版本并发控制）：每次查询生成快照，仅返回已提交的数据。

- 解决问题：避免脏读（通过MVCC解决的脏读问题，锁机制不参与防止脏读）
- 遗留问题：不可重复读、幻读。
- 适用场景：多数OLTP系统（如Oracle默认级别）。

REPEATABLE READ(可重复读)

- 机制：

- - MVCC：事务首次读取时生成快照，后续读操作基于此快照。
  - 锁机制（InnoDB特有）：使用间隙锁（Gap Locks）和临键锁（Next-Key Locks）锁定范围，防止其他事务插入新数据。

- 解决问题：脏读、不可重复读。
- 遗留问题：理论上有幻读，但InnoDB通过锁机制实际避免。
- MySQL默认级别：InnoDB引擎在此级别下有效防止幻读。
- InnoDB的特殊优化：

- - 在可重复读级别下，通过间隙锁和临键锁实际避免幻读。
  - 示例：事务A查询age > 20时，锁定(20, +∞)的索引间隙，阻止事务B插入age=25的记录。

SERIALIZABLE(可串行化)：最高的隔离级别，强制事务串行执行，避免上述所有问题，但会严重影响性能。

- 定义：所有事务串行执行，完全禁止并发。
- 解决的问题：脏读、不可重复读、幻读。
- 实现机制：

- - 所有读操作隐式转换为SELECT ... FOR SHARE（加共享锁）。
  - 写操作加排他锁，读写操作互相阻塞。

- 缺点：性能极低（高锁竞争，并发度差）。
- 适用场景：强一致性需求（如银行核心交易），谨慎使用。

MySQL的实现细节（InnoDB引擎）

可重复读的幻读处理：

- 通过间隙锁锁定索引范围，禁止其他事务插入新数据。
- 例如：SELECT * FROM users WHERE age > 20 会锁定20以上的索引间隙，阻止插入age=25的记录。

MVCC工作原理：

- 每行数据包含隐藏字段（创建版本号、删除版本号），事务根据版本号访问对应快照。
- 读操作无需加锁，写操作（INSERT/UPDATE/DELETE）仍使用锁保证一致性。

如何选择隔离级别？

优先可重复读：MySQL默认，平衡一致性与性能。
高并发场景：读已提交减少锁竞争（如Web应用）。
强一致性需求：串行化（谨慎使用，可能引发死锁）。

通过合理选择隔离级别，开发者可在数据一致性和系统性能之间找到最佳平衡。

注意：事务隔离级别越高，数据越安全，但是性能越低。

Mysql体系结构：

存储引擎：

Innodb存储引擎

特点：

DML操作遵循acid模型，支持事务；

行级锁，提高并发访问性能；

支持外键 foreign key约束，保证数据的完整性和正确性；

文件：

xxxibd;xxx代表的是表名，innodb引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（fm,sdi),数据和索引

参数：innodb file_per table

逻辑存储结构

MyISAM存储引擎

特色：不支持事务，外键

支持表锁，不支持行锁。访问速度快。

myisam是mysql早期的默认存储引擎。

特点：不支持事务，不支持外键。支持表锁，不支持行锁。访问速度快

文件：

xxx.sdi:存储表结构信息

xxx.MDY:存储数据

xxx.MYI:存储素引

MeMory存储引擎

特点：表数据存放在内存中（访问速度快），hash索引。

文件：

xxx.sdi：存放表结构信息

存储引擎选择

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

myisam:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性，并发性要求不是很高，那么选择这个存储引擎是非常合适的。

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

索引

索引概述

优势

1.提高数据检索的效率，降低数据库的 IO 成本

2.通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗

劣势

1.索引列也是要占用空间的

2.索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert，update，delete时，效率降低。

索引结构

mysql的索引是在存储引擎层实现的。

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

哈希

思考题：

索引分类

选择唯一索引还是常规索引取决于具体的需求。如果需要确保列中的值是唯一的，那么应该使用唯一索引。如果唯一性不是必须的，而查询性能是主要考虑因素，那么常规索引可能是更好的选择。在设计数据库时，应该根据数据的实际使用情况来决定使用哪种类型的索引。

1. 主键索引

定义

主键索引是唯一标识表中每一行数据的索引，每个表有且仅有一个主键索引。

特点

唯一性：主键列的值必须唯一且不允许 NULL 值。
聚簇索引（InnoDB 引擎）：数据行按主键顺序存储，索引即数据文件本身。
自动创建：定义主键时会自动创建主键索引。

创建语法

-- 建表时定义主键
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

-- 修改表结构添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

适用场景

唯一标识数据行（如用户 ID、订单号）。
需要快速通过唯一键查询数据的场景。

2. 唯一索引

定义

唯一索引确保索引列的值唯一，但允许 NULL 值（MySQL 中唯一索引允许存在多个 NULL 值）。

特点

唯一性：索引列的值必须唯一，但允许存在一个 NULL 值（具体行为取决于数据库实现）。
非聚簇索引：索引结构与数据行分离，需回表查询数据。
可多个：一个表可以有多个唯一索引。

创建语法

-- 建表时定义唯一索引
CREATE TABLE users (
  email VARCHAR(100) UNIQUE
);
-- 修改表结构添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

适用场景

需要保证某列的唯一性（如用户邮箱、手机号）。
避免重复数据的业务约束。

3. 常规索引

定义

常规索引是最基础的索引类型，仅用于加速查询，不保证唯一性。

特点

允许重复值：索引列可以有重复值和 NULL 值。
非聚簇索引：索引与数据行分离，需回表查询数据。
前缀索引：支持对字符串列的前 N 个字符创建索引以节省空间。

创建语法

-- 建表时定义常规索引
CREATE TABLE products (
  name VARCHAR(100),
  INDEX idx_name (name)
);

-- 修改表结构添加常规索引
ALTER TABLE products ADD INDEX idx_name (name(10));  -- 前缀索引（前10个字符）

适用场景

频繁作为查询条件的列（如商品名称、订单时间）。
需要加速 WHERE、JOIN 或 ORDER BY 操作的列。

4. 全文索引

定义

全文索引用于对文本内容进行高效的关键词搜索，支持自然语言查询。

特点

文本搜索优化：支持 MATCH() AGAINST() 语法，替代低效的 LIKE 模糊查询。
分词处理：对文本内容进行分词并建立倒排索引。
引擎支持：InnoDB（5.6+）和 MyISAM 均支持全文索引。
中文支持：需配合分词插件（如 ngram）处理中文。

创建语法

-- 建表时定义全文索引
CREATE TABLE articles (
  content TEXT,
  FULLTEXT INDEX idx_content (content)
) ENGINE=InnoDB;

-- 修改表结构添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

使用示例

-- 自然语言搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

适用场景

大文本字段的关键词搜索（如文章内容、商品描述）。
替代低效的 LIKE '%keyword%' 查询。

索引语法

创建索引
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
查看索引
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;

索引--SQL性能分析

1.sql执行频率
mysql客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下接令，可以查看当前数据库的 insert,update,delete,select的访问频次：
show global status like 'com_______';

对于以查询为主的数据库：进行SQL优化

2.慢查询日志

通过慢查询日志定位查询效率较低的查询SQL语句，并对其进行优化。

通过慢查询日志只能找到执行耗时超过了我们预设的时间(如以上的2秒)的sql语句，如果有些sql语句的业务很简单，但执行时间也达到了1.9几秒(相对来说也是耗时较长，执行性能较低)。我们也需要对这类sql进行优化。

如何定位这类sql呢？使用 profile详情 工具

3.profile详情：

以上三种方法都是通过执行时间判断sql语句的执行性能，都是只能粗略地进行判断，并不能真正地判定SQL语句的性能。

还得借助第四种手段判断：

4.explain执行计划（最为重要）
explain执行计划各字段含义：
id：
select查询的序列号，表示查询中执行select子句或者是操作表的顾序（d相同，执行顺序从上到下；id不同，值越大，超先执行）
select_type：
表示select的类型，常见的取值有simple(简单表，即不使用表连接或者子查询）,primary(主查询，即外层的查询）
union(union 中的第二个或者后面的查询语句）,subquery (select/where之后包含了子查询）等
type：
表示连接类型，性能由好到差的连接类型为null.system,const.eqjref,ref,range,index,all.
possible key：
显示可能应用在这张表上的索引，一个或多个。

key：
实际使用的索引，如果为null,则没有使用索引。
key_len：
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越短越好。
rows
mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered
表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

Extra

展示额外信息的字段

索引--使用规则

1.最左前缀法则
如果索引了多列（联合索引）,要遵守最左前缀法则。最左前统法则指的是查询从索引的最左列开始，并且不能跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）.

2.范围查询
联合索引中，出现范围查询（ > , < ）范围查询右侧的列索引失效

例如： user有联合索引index_user_pro_age_sta
explain select from tb user where profession = "软件工程" and age>30 and status = '0'; (status的索引失效)
explain select from tb user where profession = "软件工程"and age>=30 and status = '0'; (status的索引不失效)

8.SQL提示

9.覆盖索引&回表查询

10.前缀索引

11.单列&联合索引

索引--设计原则

SQL优化

插入数据优化

1.insert优化

1.尽量批量插入(多次与数据库建立连接，断开连接是很耗时的)，如果数据量实在是太大，可多次批量插入。

2.手动提交事务

start transaction;
插入SQL语句;
commit;

3.主键顺序插入

先引入页的概念：

页中存放着许多行数据，且大小固定为16K。页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-n行数据（如果一行数据过大，会行溢出）,根据主键排列。由此可得：

主键顺序插入：效率较高。
主键乱序插入会导致页分裂，效率较低。

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

操作如下：
#客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u root -p
#设置全局参数locallinfile为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';
主键顺序插入性能高于乱序插入

主键优化

1.数据组织方式

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

2.页分裂

主键乱序插入会导致 页分裂。

3.页合并

当删除一行记录时，实际上记录并没有被物理删除。只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到merge_threshold（默认为页的50%），innodb会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
注：merge threshold：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

重点：主键设计原则
满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。
尽量不要使用uuid做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

Order By 优化

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

1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

2.尽量使用覆盖索引。

3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（asc/desc).

4.如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer.size(默认256k).

Group By优化

在分组操作时，可以通过索引来提高效率。

分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

覆盖索引 + 子查询的方式优化

count优化

比如：(借用Redis)在Redis中维护一个key-value字段，数据库每加一条行数据就在Redis中计数加一。用到时直接去Redis中取得。

count的几种用法：

Update优化

更新语句的条件字段要有索引，并且该索引不能失效，否则行级锁会升级为表锁，并发性能大大降低。

更新的条件最好就是主键（因为主键一定有索引）。

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

存储对象

视图

介绍及语法

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

创建
create [OR REPLACE] view 视图名称（列名列表)] as select语句 [with[ cascaded | local ] check option]
查询
查看创建视图语句：show create view 视图名称;
查看视图数据：select * from 视图名称 ...;
修改
方式一：create [or replace] view 视图名称 (列名列表)] as select语句 [with[cascaded | local] check option]
方式二：alter view 视图名称 [列名列表] as select语句 [with[cascaded | local] check option]
删除
drop view[if exists] 视图名称 [视图名称] ...

检查选项（ cascaded(级联) ，local）

当使用with check option子句创建视图时，mysql会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。这样，可以保证视图上的DML操作不会创建出视图条件之外的行。

mysql允许基于另一个视图创建视图，他还会检查依赖视图中的规则，以保持一致性。为了确定检查的范围，mysql提供了两个选项；cascaded和local，默认值为cascaded

举例：

create view v1 as select id,name from student where id <= 20

此时没有检查选项，因此对这个v1视图进行增删改操作就不会检查后面的where条件，也就是说，你插了id>20的数据到基表里也可以，但是再查询视图，这个刚插入的数据在视图中不可见

create view v2 as select id, name from v1 where id >= 10 with cascaded check option ;

又创建个视图，其基于v1，并且加了cascaded检查选项，所以在操作v2视图时他会检查插入等操作的数据是否会满足where后面的条件（这里的where条件是指自己v2后的where条件以及v1视图的where条件的交集，不管v1有没有检查选项）满足则可以插入

注意：

1.被依赖的视图v2如果有cascaded/local的检查条件，那么依赖他的视图v3不管加不加检查选项在操作数据时都会遵循v2的条件。但如果v3本身没有加检查选项的话，那操作数据时不会遵循v3自己的条件。

2.被依赖的视图v1如果没有检查选项，依赖他的视图v2有cascaded检查选项，那么在依赖他的视图v2中进行数据操作，不仅看v2的条件还需看v1条件是否满足

3.被依赖的视图v1如果没有检查选项，依赖他的视图v2有local检查选项，那么在依赖他的视图v2中进行数据操作，看v2的条件是否满足即可，不用管v1条件，若v1有检查选项，那么v2和v1的条件都应满足

视图的更新条件

要使视圈可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新；
1.聚合函数或窗口函数（sum(),min(),max(),count()等）
2. distinct
3. group by
4. having
5. UNION 或者 UNION ALL

例如：

--创建视图，使用聚合函数
create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);//插入不了，会报错。

视图的作用

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

存储过程

介绍&特点&基本语法

语法结构

系统变量

用户自定义变量

局部变量

if判断，参数（in，out，inout），case，循环结构（while，repeat，loop），游标：cursor，条件处理程序：handler

存储函数

触发器

介绍

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

行级触发器：执行一条语句，语句影响了几行，触发器就触发了几次。

语句级触发器：执行一条语句，不管语句影响了几行，触发器只触发一次。

语法

锁

1.概述

2.分类

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

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

3.全局锁

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

演示：

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

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

2.如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog)，会导致主从延迟。

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

mysqldump  --single-transaction  -uroot  -p1234  itcast  >  itcast.sql

这个本质上就是：Innodb存储引擎的底层通过快照读来实现。

4.表级锁

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

1.表锁

1.表共享读锁（read lock)

如图：本客户端和其它客户端都只可读不可写。

2. 表独占写锁（write lock)

如图：本客户端可读可写，其它客户端不可读不可写。

3.语法：
1.加锁：lock tables 表名... read/write。
2. 释放锁：unlock tables / 客户端断开连接。

2.元数据锁

元数据：其实就是表结构

元数据锁的作用：维护表结构的数据一致性

表中有未提交的事务时，不可对表结构进行更改。为了避免DML与DDL冲突，保证读写的正确性。

3.意向锁

作用：意向锁解决了在Innodb存储引擎中加行锁和表锁的冲突问题。

加锁情况：

意向共享锁（IS): 由语句 select ... lock in share mode添加
意向排他锁（IX):由insert,update,delete,select ... for update 添加

兼容情况：

1.意向共享锁（IS): 与表锁共享锁（read)兼容，与表锁排它锁（write)互斥。
2.意向排他锁（IX): 与表锁共享锁（read)及排它锁（write)都互斥。意向锁之间不会互斥。

5.行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在innodb存储引擎中。
Innodb的行数据是基于索引组织(存储)的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

对于行级锁，主要分为以下三类

行锁（record lock)：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
间隙锁（gap lock)：锁定索引记录间隙（不含该记录）,确保索引记录间隙不变。防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
临键锁（next-key lock)：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙GAP。在RR隔离级别下支持。

1.行锁

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

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

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

各类SQL语句执行时的加锁情况：

行锁-演示：

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

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

2.间隙锁&临键锁

1.间隙锁/临键锁-演示

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

1.索引上的等值查询（唯一索引）（注：这里主键索引也算一种唯一索引），给不存在的记录加锁时，优化为间隙锁。

如：

对以上表进行以下操作时

，

属于是给不存在的记录（id == 5 的纪录）加锁，此时锁会优化为间隙锁（锁住 id为3到8之间的间隙，不包括id为3和8的记录），即此时如果另一个事务执行一个insert语句，加入一行id为7的行数据，事务将被阻塞，无法加锁成功。

2.索引上的等值查询（非唯一普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock（临键锁）退化为间隙锁。

如：

，

通过普通索引进行等值匹配时的加锁情况：

1.对表加一个意向共享锁（IS）（表级锁）

2.对age等于3的行数据加行锁

3.对age等于3以及age为1到3加临键锁，同时锁住数据和前面的间隙

4.对age为3到7加间隙锁

（因为age是非唯一索引（其两边间隙加的数据的age可能与这个数据的age相同，故两边间隙都要锁住），防止其他事务往间隙加数据，产生幻读现象，所以把间隙都锁住）

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

如：

使用唯一索引进行范围匹配时，加锁情况如下:

1.往id为19的记录加一个行锁

2.id为25以及id为19到25的间隙加一个临键锁

3.id为正无穷以及id为正无穷到25的间隙加一个临键锁

4.对表加一个意向共享锁（IS）（表级锁）

2.注意

间隙锁唯一目的是防止其他事务插入间隙造成幻读现象。
间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

Innodb存储引擎

1.逻辑存储结构

2.架构

1.内存结构

内存结构主要就是缓冲区。

对于MYSQL服务器来说，一般mysql 80% 的内存都会分配给到缓冲区，来提高mysql的执行效率。

1.Buffer Pool

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

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

free page：空闲page，未被使用。
clean page：被使用page，数据没有被修改过。
dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘的数据产生了不一致。（页中数据还没来得及更新到磁盘）

2.Change Buffer

Change Buffer：更改缓冲区（针对于非唯一二级索引页），在执行DML语句(增删改语句)时，如果这些数据 Page 没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更（数据变更的操作）存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
Change Buffer的意义是什么？
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了Change Buffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

3.Adaptive Hash Index

Adaptive Hash Index：自适应hash索引，用于优化对Buffer Pool数据的查询。Innodb存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。
参数：adaptive_hash_index

4.Log Buffer

2.磁盘结构

1.系统表空间

2.每个表的文件表空间（包含表结构，数据和索引），并存储在表空间文件（后缀名为ibd的文件）里

3.通用表空间

4.撤销表空间

5.临时表空间

6.双写缓冲区

7.重做日志

3.后台线程

将Innodb存储引擎内存结构数据刷新到磁盘结构时，会用到后台线程。

3.事务原理（ACID的底层原理）

1.redo log

保证事务的持久性。Redo log日志每隔一段时间会被清理。

Buffer Pool 里的数据持久化到磁盘大部分都是随机磁盘 IO（因为一般进行数据操作时都是操作有多组操作，而这些操作数据页的操作都是随机的），是一些没有顺序的 IO 操作，性能较低，所以要定时持久化同步过去而不是在提交事务的时候就同步过去。而 Redo Log buffer 里面的日志数据同步到磁盘的Redo Log 文件中时是以日志追加的形式同步过去，属于顺序磁盘 IO 操作，性能较高，所以同步Redo Log 数据到磁盘可以在事务提交时立刻进行。

2.undo log

保证事务的原子性。

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和 MVCC

主要作用：

事务执行失败进行回滚时就会依赖undo log进行回滚。
MVCC也会用到undo log。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当dalete一条记录时，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。

4.MVCC（多版本并发控制）

作用：在快照读的时候，通过MVCC来查找对应的历史版本。

一些基本概念

当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

对于我们日常的操作，如：select... lock in share mode(共享锁）, select.... for update,update,insert, delete(排他锁）都是一种当前读。

快照读
简单的select(不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed（读已提交）：每次SELECT，都生成一个快照读。
repeatable Read（可重复读）：开启事务后第一个SELECT语句才是快照读的地方。
Serializable（可串行化）：快照读会退化为当前读。
MVCC
全称Multi-Version Concurency Control（多版本并发控制）。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为mysql突现MVCC提供了一个非阻塞读功能。

MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段，

undo log日志，readView。

1.数据库记录中的三个隐式字段

2.undo log

undo log版本链：

3.readView

ReadView(读视图）是快照读SQL执行时MVCC提取数据的依据，

记录并维护系统当前活跃的事务(未提交的)id。

版本链数据访问规则

举例

1.RC隔离级别下

第一次快照读：

拿各个版本数据（从最新的版本记录开始）的隐藏字段当前事务ID：DB_TRX_ID作为tri_id去套到规则里去分析，

通过以上逐个版本数据以及根据规则的分析可得以上这次的快照读读取的是最近修改事务id为2的这个版本的记录：

可以发现在RC(读已提交)隔离级别下我们查询返回的是已提交的数据。

第二次快照读：

通过以上逐个版本数据以及根据规则的分析可得以上这次的快照读读取的是最近修改事务id为3的这个版本的记录：

可以发现在RC(读已提交)隔离级别下我们查询返回的同样是已提交的数据。

2.RR隔离级别下

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

第一次快照读：查询到的数据和RC隔离级别下第一次快照读的结果相同：

第二次快照读和第一次快照读的结果一样。

4.MVCC总结

MVCC（多版本并发控制）是MySQL数据库管理系统中InnoDB存储引擎的一个特性，它允许多个事务同时对同一数据进行读取和修改，而不会相互干扰，从而提高了数据库的并发性能。

在传统的数据库管理系统中，读取数据时通常需要加锁，以保证数据的一致性。但是，加锁会阻塞其他事务对同一数据的访问，降低并发性能。而MVCC通过保存数据在多个版本的历史状态，允许读取事务访问到数据的历史版本，从而避免了加锁操作，提高了并发性能。

MVCC的工作原理如下：

每个事务都有一个唯一的事务ID。
每行数据都有两个隐藏的列：事务ID（DB_TRX_ID）和回滚指针（DB_ROLL_PTR）。
当事务对数据进行修改时，不会直接覆盖原数据，而是生成一个新的版本，并将新版本的事务ID设置为当前事务ID，同时将原数据的回滚指针指向新版本。
读取数据时，事务会根据自己的事务ID和数据的创建时间、过期时间来判断哪些版本的数据是可见的。
事务读取到的数据是一致的，因为它看到的是数据的一个快照，即事务开始时数据的最新版本。
事务提交时，会将自己的事务ID记录到事务日志中，同时清理过期数据。

MVCC的作用如下：

提高并发性能：多个事务可以同时读取同一数据，不会因为加锁而相互阻塞。
降低死锁概率：由于读取操作不会加锁，因此降低了死锁的发生概率。
保持数据一致性：事务读取到的数据是一致的，因为它看到的是数据的一个快照，不会受到其他事务的影响。
支持事务的隔离级别：MVCC是实现事务隔离级别的一种方式，可以支持不同的事务隔离级别，如读已提交（Read Committed）和可重复读（Repeatable Read）。

需要注意的是，MVCC虽然提高了并发性能，但也有一些限制。例如，MVCC可能导致额外的存储空间开销，因为需要保存多个版本的数据。此外，MVCC对删除操作的处理也比较复杂，因为需要清理过期数据。因此，在实际应用中，需要根据具体情况选择是否使用MVCC。

MYSQL管理

1.系统数据库

2.常用工具

1.mysql客户端工具

2.mysqladmin

3.mysqlbinlog

4.mysqlshow

5.mysqldump

6.mysqlimport/source

总结：

日志

1.错误日志

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

2.二进制日志

binlog.000008 binlog.000009 binlog.000010 binlog.000011等：二进制日志文件

binlog.index：二进制日志索引文件

日志查看：

日志删除：

3.查询日志

记录所有sql语句，包括DDL，增删改查语句(DML，DQL)语句。

4.慢查询日志

主从复制

1.概述

主库出现问题，可以快速切换到从库提供服务。
实现读写分离（主库进行增删改，从库进行查询），降低主库的访问压力。
可以在从库中执行备份，以避免备份期间影响主库服务。（数据备份时需要锁住数据库（加全局锁），让数据库处于只读状态，如果没有主从复制，锁住数据库，业务将会处于停摆状态，有主从复制则在从库中执行备份，不会影响从库的的读数据功能）缺点：锁住从库后，会使得主库数据无法及时同步到从库，导致主从延迟。

主库和从库可能位于同一台服务器上，特别是在测试环境中。但在生产环境中，为了提高可靠性和性能，通常会将主库和从库放置在不同的服务器上，甚至可能在不同的物理位置。

2.原理

3.配置

1.主库配置

2.从库配置

分库分表

1.介绍

问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈；
1.IO瓶颈；热点数据太多，数据库缓存不足，产生大量磁盘IO,效率较低。请求数据太多，带宽不够，网络IO瓶颈。
2.CPU瓶颈：排序，分组，连接查询，聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。