MYSQL笔记

最新推荐文章于 2024-07-25 16:09:50 发布

qq_45667934

最新推荐文章于 2024-07-25 16:09:50 发布

阅读量220

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_45667934/article/details/125814974

版权

一、MySQL索引

MySQL

1.什么是索引

# 1.什么是索引
- 官方定义: 一种帮助mysql提高查询效率的数据结构
- 索引的优点:
		1、大大加快数据查询速度
- 索引的缺点:
		1、维护索引需要耗费数据库资源
		2、索引需要占用磁盘空间
		3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

2.索引分类

# 2.索引分类

- 1. 从逻辑功能上分：普通索引、唯一索引、主键索引、全文索引
- 2. 从物理实现方式分： 聚簇索引、非聚簇索引
- 3. 作用字段个数分：单列索引、联合索引


- 1.主键索引
		设定为主键后数据库会自动建立索引，innodb为聚簇索引

- 2.单列（值）索引
		即一个索引只包含单个列，一个表可以有多个单列索引 

- 3.唯一索引   
		索引列的值必须唯一，但允许有空值

- 4.多列（复合、联合）索引
		即一个索引包含多个列
		
- 5.普通索引
		没有任何限制

- 6.Full Text 全文索引 (My5.7版本之前 只能由于MYISAM引擎)
		全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、			TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

3.索引的基本操作

1.主键索引 自动创建

--建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;

2.单列索引(普通索引|单值索引)
--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name));  
	'注意:随表一起建立的索引索引名同列名一致'
	  
--建表后创建
create index nameindex on t_user(name);

--删除索引
drop index 索引名 on 表名

3.唯一索引
--建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
 
--建表后创建
 create unique index nameindex on t_user(name);

4.多列（复合、联合）索引
---建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后创建
 create index nameageindex on t_user(name,age);

5.普通索引
---建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后创建
 create index nameageindex on t_user(name);

4.索引的底层原理

1.思考
---建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;

# 5.为什么上面数据明明没有按顺序插入,为什么查询时却是有顺序呢?
- 原因是:mysql底层为主键自动创建索引,一定创建索引会进行排序
- 也就是mysql底层真正存储是这样的
- 为什么要排序呢?因为排序之后在查询就相对比较快了 如查询 id=3的我只需要按照顺序找到3就行啦(如果没有排序大海捞针,全靠运气😸!！)

# 6.为了进一步提高效率mysql索引又进行了优化
-  就是基于页的形式进行管理索引
-  如 查询id=4的 直接先比较页 先去页目录中找,再去 数据目录中找

# 7.上面这种索引结构称之为B+树数据结构,那么什么是B+树呢?
- 参考资料: https://www.cnblogs.com/lianzhilei/p/11250589.html

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2_{4层。[mysql](http://lib.csdn.net/base/mysql)的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

5.聚簇索引和非聚簇索引

# 8.聚簇索引和非聚簇索引
- 聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
- 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注意:在innodb中，在聚簇索引之上创建的索引称之为辅助索引，

非聚簇索引: 都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

InnoDB中

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MYISAM

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

# 9.使用聚簇索引的优势
- 问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？
	
- 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

- 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

# 10.聚簇索引需要注意什么?
- 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
- 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

# 11. 为什么主键通常建议使用自增id
- 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

# 12. 什么情况下无法利用索引呢?
- 1. 查询语句中使用LIKE关键字
			在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

- 2.查询语句中使用多列索引
			多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。


- 3.查询语句中使用OR关键字
			查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

6.什么情况下适合用索引

# 1. 字段的数值有唯一性的限制

# 2. 频繁作为 WHERE 查询条件的字段
	设置个普通索引就够了	
# 3. 经常 GROUP BY 和 ORDER BY 的列
# 4. UPDATE、DELETE 的 WHERE 条件列
# 5. DISTINCT 字段需要创建索引
# 6. 多表 JOIN 连接操作时，创建索引注意事项
# 7. 使用列的类型小的创建索引
# 8. 使用字符串前缀创建索引
# 9. 区分度高散列性高的列适合作为索引
# 10. 使用最频繁的列放到联合索引的左侧
# 11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

7.哪些情况不适合创建索引

# 1. 在 where 中使用不到的字段，不要设置索引
	包括 group by 和 order by中   单表不超过 6个 索引
# 2. 数据量小的表最好不要使用索引
	低于 1000 行不用创建索引
# 3. 有大量重复数据的列上不要建立索引
	重复度 10% 以上
# 4. 避免对经常更新的表创建过多的索引
	
# 5. 不建议用无序的值作为索引
	如 身份证、UUID、MD5、hash、无序长字符串
# 6. 删除不再使用或者很少使用的索引
	
# 7. 不要定义冗余或重复的索引
CREATE TABLE person_info( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
name VARCHAR(100) NOT NULL, 
birthday DATE NOT NULL, 
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL, 
PRIMARY KEY (id), 
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), 
KEY idx_name (name(10))          ## 这个就不用创建了
);

二.MySQL优化

有哪些维度可以进行数据库调优

索引失效、没有充分利用索引 ----》建立索引
关联查询太多 join（设计缺陷或不得已的需求） —》 SQL优化
服务器调优及各个参数设置（缓存、线程数） —》调整 my.cnf
数据过多 —》分库分表

SQL优化技术过多，但大体分为两种：物理查询优化和逻辑查询优化

物理查询优化：索引和表链接优化
逻辑查询优化：SQL等价变换提高查询效率

1. 优化步骤

2.索引失效的11种情况

2.1 全职匹配我最爱

2.2 最佳左前缀法则

2.3 主键插入顺序

2.4 计算、函数、类型转换（自动或手动）导致索引失效

2.5 类型转换导致索引失效

2.6 范围条件右边的列索引失效

2.7 不等于（！= 或 <> ）索引失效

2.8 is null 可以使用索引，is not null无法使用索引

2.9 like 以通配符 % 开头索引失效

2.10 OR 前后存在非索引的列，索引失效

2.11 数据库和表的字符集统一使用 utf8mb4

3.关联查询优化

4.子查询优化

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子

查询的执行效率不高。

原因：

① 执行子查询时，内层查询语句要建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

② 临时表不会存在索引，所以查询性能会受到一定的影响。

③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

解决方案：

在MySQL中，可以使用连接JOIN查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

5.排序优化

**问题：**在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

优化建议：

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

6.GROUP BY 优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。
Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

7.分页查询优化

8.优先考虑覆盖索引

9.

三、事务基础知识

1.概述

1.1 存储引擎支持情况

1.2 基本概念

1.3 事务的 ACID 特性

原子性（atomicity）：原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
一致性（consistency）：指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。
1. A有200，给B转300。账户还有 -100，这样不行
2. A有200，B有100，A转给B 100，此时 A + B = 300。即总和保持不变
隔离性（isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。
持久性（durability）：一个事务一旦被提交，它对数据的改变就是永久性的，接下来的其他操作和故障不对其有影响。

1.4 事务的状态

2.如何使用事务

2.1 显式事务

步骤1：START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。

mysql> BEGIN; 
#或者 
mysql> START TRANSACTION;

START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个修饰符：

① READ ONLY ：只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。

② READ WRITE ：读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。

③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

步骤2：一系列事务中的操作（主要是DML，不含DDL）

步骤3：提交事务或中止事务（即回滚事务）

# 提交事务。当提交事务后，对数据库的修改是永久性的。 
mysql> COMMIT;

# 回滚事务。即撤销正在进行的所有没有提交的修改 
mysql> ROLLBACK;

# 将事务回滚到某个保存点。 
mysql> ROLLBACK TO [SAVEPOINT]

2.2 隐式事务

MySQL中有一个系统变量 autocommit ：默认是 ON，代表自动提交

#关闭自动提交
SET autocommit = OFF; 
#或
SET autocommit = 0;

3.事务的隔离级别

3.1 数据并发问题

脏写（ Dirty Write ）：A事务写了一个还没被B提交的数据。A一查：发现数据 name = 原始值
脏读（Dirty Read）：A事务读取了一个还没被 B 提交的数据。A再一查：name = 原始值
不可重复读：重复读同一个数据，发现每次都不一样【个数变少了】【数值变了】
幻读：重复读同一个数据，发现【个数多了】

3.2 SQL中的四种隔离级别

严重性：脏写 > 脏读 > 不可重复读 > 幻读

READ UNCOMMITTED ：读未提交，解决脏写
READ COMMITTED（Oracle默认）：读已提交，解决脏写，脏读问题
REPEATABLE READ （MySQL默认）：可重复读，解决脏写，脏读，可重复读
SERIALIZABLE ：可串行化，解决脏写，脏读，可重复读，幻读

3.3 如何设置隔离级别

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别' 
#其中，隔离级别格式： 
> READ-UNCOMMITTED 
> READ-COMMITTED 
> REPEATABLE-READ 
> SERIALIZABLE

使用 GLOBAL 关键字（在全局范围影响）：
- 当前已经存在的会话无效
- 只对执行完该语句之后产生的会话起作用

SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';

使用 SESSION 关键字（在会话范围影响）：
- 对当前会话的所有后续的事务有效
- 如果在事务之间执行，则对后续的事务有效
- 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';

小结：

数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。

4.事务的常见分类

四、事务日志

1）redo日志

1.流程

2.redo log 刷盘策略

# 注意:
	redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存 （page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。
	针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：
# 设置为0 ：
	表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）
# 设置为1 ：
	表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ） 安全，就用它
# 设置为2 ：
	表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

2）undo 日志

2.1.作用

作用1：回滚数据
作用2：MVCC

2.2.undo log 生命周期

简要生成过程

2.3 undo log 的删除

# 针对于insert undo log
	因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。
# 针对于update undo log 
	该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

2.4 总结

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。
redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程。

五、锁

1）并发问题解决方案

怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢？其实有两种可选的解决方案：
# 方案一：读操作利用多版本并发控制（ MVCC ，下章讲解），写操作进行 加锁 。
普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
	在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了 事务不可以读取到未提交的事务所做的更改 ，也就是避免了脏读现象；
	在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读和幻读的问题。
# 方案二：读、写操作都采用 加锁 的方式。
小结对比发现：
	采用 MVCC 方式的话， 读-写 操作彼此并不冲突， 性能更高 。
	采用 加锁 方式的话， 读-写 操作彼此需要 排队执行 ，影响性能。
一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，但是业务在某些特殊情况
下，要求必须采用 加锁 的方式执行。下面就讲解下MySQL中不同类别的锁。

2）锁的分类

2.1）读锁，写锁

# 对数据的操作类型划分
- 1.读锁/共享锁---》S
		针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
- 2.写锁/排它锁---》X
		当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。
需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。

2.2）表级锁、页级锁、行锁

# 锁的力度
- 1.表锁 ---》 不重要
		1.S锁，X锁  ---》 innodb 不要用			
		2.意向锁（intention lock）
		3.自增锁（AUTO-INC锁）
		4.元数据锁（MDL锁）
- 2.行锁 ---》重要
		1.记录锁（Record Locks ）
		2.间隙锁（Gap Locks） 
		3.临键锁（Next-Key locks）== 记录锁 + 间隙锁
		4.插入意向锁  
- 3.页锁 ---> 不重要

① 表级别—S锁、X锁

LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁。
LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁。

不过尽量避免在使用InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的行锁，关于

InnoDB表级别的 S锁和 X锁大家了解一下就可以了。

② 表级别—意向锁

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向****锁就是其中的一种表锁。

意向锁分为两种：

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。 
SELECT column FROM table ... FOR UPDATE;

从上面的案例可以得到如下结论：

1. InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。

2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与共享锁 / 排他锁互斥。

3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

4. 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

③ 表级别—自增锁

④ 表级别 —元数据锁

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。
MDL 的作用是，保证读写的正确性。
	比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
	因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

⑤行级别—记录锁

只管一行

⑥行级别—间隙锁

	这是解决幻读的一种方案（另一种是MVCC）
	insert··· where id = 8 
	图中id值为8的记录加了gap锁，意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 ，其实就是id列的值(3, 8)这个区间的新记录是不允许立即插入的。
	比如，有另外一个事务再想插入一条id值为4的新记录，它定位到该条新记录的下一条记录的id值为8，而这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间(3, 8)中的新记录才可以被插入。
gap锁的提出仅仅是为了防止插入幻影记录而提出的。

⑦行级别—临键锁

	有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为next-key锁 。
	Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁，
innodb默认的锁就是Next-Key locks。

begin; 
select * from student where id <=8 and id > 3 for update;

⑧行级别—插入意向锁

	我们说一个事务在 插入 一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁 （ next-key锁也包含 gap锁 ），如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。
	但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个 间隙 中 插入 新记录，但是现在在等待。InnoDB就把这种类型的锁命名为 Insert Intention Locks ，官方的类型名称为：LOCK_INSERT_INTENTION ，我们称为 插入意向锁 。	插入意向锁是一种 Gap锁 ，不是意向锁，在insert操作时产生。插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁 。
	事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。
简单记：等待在间隙锁（临键锁）中插入的锁，叫插入意向锁

⑨页锁

	页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。
	页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。
	当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，
	比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

2.3）悲观锁、乐观锁

# 对锁的态度
- 1.悲观锁
		悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。
		悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。
		Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。
-------------------------------------------------------------------------------------------------------------------
- 2.乐观锁
		乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。
		在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。
		适用场景：多读的应用类型，这样可以提高吞吐量。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁begin; 
		select * from student where id <=8 and id > 3 for update;的一种实现方式：CAS实现的。
1. 乐观锁的版本号机制
		在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。
		然后对数据进行更新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。
		此时如果已经有事务对这条数据进行了更改，修改就不会成功。
2. 乐观锁的时间戳机制
		时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。
		你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或者时间戳），从而证明当前拿到的数据是否最新。
-------------------------------------------------------------------------------------------------------------------
# 乐观锁和悲观锁的适用场景：
1. 乐观锁 
		适合 读操作多 的场景，相对来说写的操作比较少。
		它的优点在于 程序实现 ， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁 
		适合 写操作多 的场景，因为写的操作具有 排它性 。
		采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。

①悲观锁

秒杀案例1：

# 第1步：查出商品库存
select quantity from items where id == 1001 for update;
# 第2步：如果库存大于0，根据商品信息产生订单
insert into orders(item_id) values(1001);
# 第3步：修改商品库存，num表示购买数量
update items set quantity = quantity - num where id = 1001;

②乐观锁

秒杀案例2：不好，一条记录，只能一个人成功，会出现大量的失败

# 第1步：查出商品库存
select quantity from items where id == 1001;
# 第2步：如果库存大于0，根据商品信息产生订单
insert into orders(item_id) values(1001);
# 第3步：修改商品库存，num表示购买数量
update items set quantity = quantity - num, version = version + 1 where id = 1001 and version = #{version};

秒杀案例3：好，只有没库存的才会失败

# 第1步：查出商品库存
select quantity from items where id == 1001;
# 第2步：如果库存大于0，根据商品信息产生订单
insert into orders(item_id) values(1001);
# 第3步：修改商品库存，num表示购买数量
update items set quantity = quantity - num where id = 1001 and quantity - num > 0;

2.4）隐式锁、显示锁

# 加锁方式
- 1.隐式锁
		insert into ···· where id = 10，还没提交，别人想操作 id = 10的记录，为了防止这样的事发生，会给一个隐式锁
		在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务，首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)。
- 2.显示锁
		通过特定的语句进行加锁，我们一般称之为显示加锁，例如：
		显示加共享锁：
			select ··· lock in share mode
		显示加排它锁：
			select ··· for update

2.5）全局锁、死锁

# 其他
- 1.全局锁
		全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（DML数据的增删改）、数据定义语句（DDL包括建表、修改表结构等）和更新类事务的提交语句。
		全局锁的典型使用 场景 是：做 全库逻辑备份 。
		全局锁的命令：
			Flush tables with read lock
- 2.死锁
		死锁发生后两种策略：
			一：直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
			二：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on ，表示开启这个逻辑。

六、MVCC

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View

1）隐藏字段、Undo Log 版本链

回顾一下undo日志的版本链，对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。
	trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的 事务id 赋值给trx_id 隐藏列。
	roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，它占用的Undo Log Segment也会被系统回收（也就是该undo日志占用的Undo页面链表要么被重用，要么被释放）。

2）什么是 Read View

1. creator_trx_id ，
		创建这个 Read View 的事务 ID。
说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。 
2. trx_ids ，
		表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表 。 
3. up_limit_id ，
		活跃的事务中最小的事务 ID。 
4. low_limit_id ，
		表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。
注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1， 2，5这三个事务，之后id为5的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是6。

3）整体流程

了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过MVCC找到它：

首先获取事务自己的版本号，也就是事务 ID；
获取 ReadView；
查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
最后返回符合规则的数据。

在隔离级别为 读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

4）举例说明

4.1 READ COMMITTED隔离级别下

# 使用READ COMMITTED隔离级别的事务 
BEGIN; 
# SELECT1：Transaction 10、20未提交 
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

# Transaction 10 
BEGIN; 
UPDATE student SET name="李四" WHERE id=1; 
UPDATE student SET name="王五" WHERE id=1;

# Transaction 20 
BEGIN; 	
# 更新了一些别的表的记录
UPDATE student SET name="钱七" WHERE id=1; 
UPDATE student SET name="宋八" WHERE id=1;
...

# 使用READ COMMITTED隔离级别的事务 
BEGIN; 

# SELECT1：Transaction 10、20均未提交                   --->  活动的事务表【10，20】
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三' 

# SELECT2：Transaction 10提交，Transaction 20未提交     --->  活动的事务表【20】
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'王五'

4.2) REPEATABLE READ隔离级别下

# 使用REPEATABLE READ隔离级别的事务 
BEGIN; 

# SELECT1：Transaction 10、20均未提交                  --->  活动的事务表【10，20】
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三' 

# SELECT2：Transaction 10提交，Transaction 20未提交    --->  活动的事务表【10，20】
SELECT * FROM student WHERE id = 1; # 得到的列name的值仍为'张三'

4.3）解决幻读

步骤一：事务 A 开始第一次查询数据，查询的 SQL 语句如下。

select * from student where id >= 1;			#事务 A 的第一次查询，能读取到一条数据，id=1。

步骤二：接着事务 B(trx_id=30)，往表 student 中新插入两条数据，并提交事务。

insert into student(id,name) values(2,'李四'); 
insert into student(id,name) values(3,'王五');

步骤三：步骤3：接着事务 A 开启第二次查询，根据可重复读隔离级别的规则，此时事务 A 并不会再重新生成ReadView。

5）总结

这里介绍了 MVCC·在 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行快照读操作时访问记录的版本链的过程。这样使不同事务的读-写、写-读操作并发执行，从而提升系统性能。核心点在于 ReadView 的原理， READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成ReadView的时机不同：

READ COMMITTD 在每一次进行普通SELECT操作前都会生成一个
ReadView REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

七、其他数据库日志

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志 和 慢查询日志 ，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使用这些日志文件，可以查看MySQL内部发生的事情。

**慢查询日志：**记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
**通用查询日志：**记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助
**错误日志：**记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
**二进制日志：**记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
**中继日志：**用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
**数据定义语句日志：**记录数据定义语句执行的元数据操作。