首页 > 数据库 >MySQL知识点归纳

MySQL知识点归纳

时间：2023-10-05 18:55:38浏览次数：45  
标签：知识点 事务 log 归纳 查询 索引 book MySQL 主键

1. 索引

  索引按照物理实现方式，可以分为两种：聚簇（聚集）和非聚簇（非聚集）索引。也把非聚集索引称为二级索引或者辅助索引。

1.1 聚簇索引

  聚簇索引不是一种单独的索引类型，而是一种数据存储方式，即索引的存储方式（所有的用户记录都存储在了叶子节点），所以聚簇索引：索引即数据，数据即索引。主要针对主键。

1.2 二级索引（辅助索引、非聚簇索引）

1.3 B+树的存储能力如何？为何说一般查找行记录最多只需要1~3次I/O?

1.4 为什么B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

1.5 Hash索引与B+树索引的区别

1.6 innoDB的数据页

页是磁盘和内存之间交互的基本单位，默认是16kb

1.7 索引的分类

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

1.8 索引的设计原则

1.8.1 适合创建索引的情况

  1. 字段的数值有唯一性的限制。
  2. 频繁作为WHERE查询条件的字段。
  3. 经常GROUP BY和ORDER BY的列。
    添加索引的顺序要先GROUP BY再ORDER BY。
  4. UPDATE、DELETE的WHERE条件列。
  5. DISTINCT去重的字段创建索引。
  6. 多表JOIN连接操作时，创建索引注意事项。
    （1）连接的表尽量不要超过三张，因为每增加一张表就相当于增加了一次嵌套循环。
    （2）对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。
    （3）对用于连接的字段创建索引，且该字段在多张表中的类型必须一致。
  7. 使用列的类型小的创建索引。
    此处的类型大小指的是该类型表示的数据范围的大小，即占据字节的数量大小。
  8. 使用字符串的前缀。
    无法支持索引排序，只能使用文件排序。
  9. 区分度高（散列性高）的列适合作为索引。
    如性别列只有男、女，即便有几千万的数据，这个列的基数也是2，散列性不高，不建议创建索引。
  10. 使用最频繁的列放到联合索引的左侧。
  11. 在多个字段都要创建索引的情况下，联合索引优于单列索引。

注意：建议单表索引数量不超过6个。

1.8.2 不适合创建索引的情况

  1. 在where中使用不到的字段，不要设置索引。
  2. 数据量小的表最好不要使用索引。
  3. 有大量重复数据的列上不要建立索引。
  4. 避免对经常更新的表创建过多的索引。
    （1）频繁更新的字段不建议建立索引。
    （2）避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。
  5. 不建议用无序的值作为索引。
  6. 删除不再使用或者很少使用的索引。
  7. 不要定义冗余或重复的索引。

1.9 创建索引

1.9.1 create table

1.9.1.1 隐式的方式创建索引
  • 在声明有主键约束、唯一性约束、外键约束的字段上，会自动添加相关索引。
CREATE DATABASE dbtest2;

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE emp(
demp_id int PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) #外键
);
1.9.1.2 显式的方式创建索引
  1. 创建普通的索引
CREATE TABLE book(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
#在建表字段后面进行索引声明
index idx_name(book_name)
);
  1. 通过命令查看索引
  • 方式一
    SHOW CREATE TABLE book;
  • 方式二
    SHOW INDEX FROM book;
  1. 创建唯一索引
CREATE TABLE book1(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_com(comment)

  1. 主键索引
    通过定义主键约束的方式自动定义主键索引。
    可以通过删除主键约束的方式删除主键索引（声明有AUTO_INCREMENT的字段必须是主键或者是UNIQUE）。
    alter table book1 drop primary key;

  2. 创建单列索引

CREATE TABLE book2(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX idx_bname(baname)
  1. 创建联合索引
CREATE TABLE book3(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_info(book_id,book_name,info)

联合索引的声明的顺序对生成的索引的顺序是固定的，影响索引是否起作用（最左前缀原则）。

  1. 使用EXPLAIN关键字查看SQL语句是否触发索引
    explain select book_id,book_name from book3 where book_id = 1001 and book_name='mysql';

  2. 创建全文索引
    全文索引（FULLTEXT）只能在CHAR、 VARCHAR、 TEXT类型列上创建，对整个列进行，不支持局部（前缀）索引。

CREATE TABLE test4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
#声明全文索引
FULLTEXT INDEX futxt_idx_info(info(50))
) ENGINE=MyISAM;
  1. 创建空间索引
    对于空间的类型的字段必须是非空的。
CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

1.9.2 在已经创建的表上创建索引

1.9.2.1 创建索引

  1. 通过ALTER TABLE ... ADD ...
ALTER TABLE book5 ADD INDEX idx_cmt(comment);
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);
  1. 通过CREATE INDEX ... ON ...
CREATE INDEX idx_cmt ON book6(comment);
CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);

1.9.2.2 删除索引

添加了auto_increment约束字段的唯一索引不能被删除

  • 方式一：alter table ... drop index ...
    ALTER TABLE book5 DROP INDEX idx_cmt;

  • 方式二：drop index ... on ...
    DROP INDEX uk_idx_bname ON book5;

注意：当删除某个字段，该字段建立的索引会被一起删除，联合索引会删除对应的索引列。

2. 性能分析工具

2.1 查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。
SHOW STATUS语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
一些常用的性能参数如下：

  • Connections：连接MySQL服务器的次数。
  • Uptime：MySQL服务器的上线时间。
  • Slow_queries：慢查询的次数。
  • Innodb_rows_read：Select查询返回的行数。
  • Innodb_rows_inserted：执行INSERT操作插入的行数。
  • Innodb_rows_updated：执行UPDATE操作更新的行数。
  • Innodb_rows_deleted：执行DELETE操作删除的行数。
  • Com_select：查询操作的次数。
  • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
  • Com_update：更新操作的次数。
  • Com_delete：删除操作的次数。

2.2 统计SQL的查询成本：last_query_cost

查看最后一条查询语句的成本，value表示该语句查询出的数据页页数。以下的表示数据来自约21个数据页。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value      |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

2.3 定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，具体指运行超过long_query_time值的SQL，long_query_time的默认值为10，即10秒以上（不含10秒）。

2.3.1 开启慢查询日志参数

set global slow_query_log='ON';
  • 开启慢查询后，文件保存在/var/lib/mysql/[主机名].log。
  • 可以使用show variables like '%slow_query_log%'语句查看。

以上的方式是临时的设置，当MySQL服务重启后，设置就会失效。
以下的方式相较于命令行的方式可以看作是永久设置的方式。

修改my.cnf文件，[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，重启MySQL服务器。

[mysqld]
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/xxx.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

2.3.2 查看慢查询数目

查询慢查询数目语句：

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

分析语句：

show status like 'slow_queries';

2.3.3 慢查询分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的帮助信息

mysqldumpslow --help

image
mysqldumpslow 命令的具体参数如下：

  • -a: 不将数字抽象成N，字符串抽象成S
  • -s: 是表示按照何种方式排序：
      • c: 访问次数
    • l: 锁定时间
    • r: 返回记录
    • t: 查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间 （默认方式）
    • ac:平均查询次数
  • -t: 即为返回前面多少条的数据；
  • -g: 后边搭配一个正则匹配模式，大小写不敏感的；
    举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：
mysqldumpslow -s t -t 5 /var/lib/mysql/xxxx-slow.log

工作常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | more

2.4查看SQL执行成本：show profile

show variables like 'profiling';

image
通过设置 profiling='ON' 来开启 show profile：

set profiling = 'ON';

image
然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

show profiles;

image
你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：

show profile;

image

show profile cpu,block io for query 2;

image

show profile的常用查询参数：
① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。

2.5 分析查询语句：EXPLAIN

2.5.1 基本语法

EXPLAIN SELECT select_options 
#或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

3. 索引优化与查询优化

3.1 索引失效

1. 不符合最左前缀原则索引失效
2. 计算、函数、类型转换（自动或手动）导致索引失效
3. 范围条件右边的列索引失效
4. 不等于（!=或<>）索引失效
5. is null可以使用索引，is not null索引失效
6. like以通配符%开头索引失效
7. or关键字前后存在非索引的列，索引失效

3.2 关联查询优化

3.3 子查询优化

3.4 排序优化

3.5 覆盖索引

3.6 索引下推

4 数据库设计规范

4.1 范式

4.1.1 第一范式（1st NF）

具有原子性，不能再拆分。

4.1.2 第二范式（2nd NF）

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录都是可唯一标识的。而且所有字段都必须完全依赖主键，不能只依赖主键的一部分。
若是只依赖主键的一部分，则可以继续拆分。

  • 确保每列都和主键完全依赖。
  • 1NF（第一范式）告诉我每个字段属性是需要原子性的，而2NF告诉我们一张表就是一个独立的对象，一张表只表达一个意思。
  • 第二范式（2NF）要求实体的属性完全依赖主键关键字，如果存在不完全依赖，那么这个属性和关键字的这一部分应该分离出来形成一个新的实体，新实体与元实体之间是一对多的关系。

4.1.2 第三范式（3rd NF）

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，即要求数据表中的所有非主键字段不能依赖于其他非主键字段。

  • 第二范式是主键可分；第三范式是非主键依赖于其他非主键，则再以某个被依赖的非主键为主键而分。
  • 确保每列都和主键列直接相关，而不是间接相关。
  • 符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：每个非（主）键属性依赖于键，依赖于整个键，并且除了键别无他物。

4.1.3 小结

  • 范式的优点：有助于消除数据库的中的数据冗余，第三范式被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

  • 范式的缺点：可能降低查询效率。因为范式等级越高，设计出来的数据表越多、越精细，数据的冗余度就越低，进行数据查询就要关联多张表，对数据库开销增大，可能使一些索引策略无效。

4.2 反范式化

不遵循范式的原则，一般只是不遵循第二和第三范式。

4.3 巴斯范式（BCNF）

主属性对于候选键是部分依赖关系，导致插入某些属性字段的时候导致其他主键为空值，导致插入的情况数据异常。巴斯范式在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

4.4 数据表的设计原则

1. 数据表的个数越少越好
2. 数据表中的字段个数越少越好
3. 数据表联合主键的字段个数越少越好
4. 使用主键和外键越多越好
  • 外键指的是描述外键关系的逻辑

5 数据库事务

事务是一组逻辑操作单元，使数据从一种状态变换到另一种状态。

5.1 事务的ACID

  • 原子性
    指事务是一组不可分割的工作单位，要么全部提交，要么全部失败回滚。

  • 一致性
    事务在执行前后，数据从一个合法状态变换到另一个合法状态。

  • 隔离性
    一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性
    一个事务一旦被提交，它对数据库中数据的改变是永久性的。

5.2 事务的状态

  • 活动的
  • 部分提交的
  • 失败的
  • 提交的
  • 中止的

5.3 显式事务与隐式事务

1. 显式事务如何开启？

使用关键字 satrt transaction 或 begin。
satrt transaction 后面可以跟：read only / read write(默认) / with consistent snapshot

2. 隐式事务

  • show variables like 'autocommit'; 默认是ON，开启
    当DML操作的时候，受隐式事务影响，当使用显式关键字的时候，会自动不用隐式提交。
    或者set autocommit = false。

3. 事务的其他示例

  • 保存点 savepoint
    savepoint s1; #事务操作的过程中设置保存点
    roballk s1; #回滚到保存点

4. 事务的隔离级别

1. 并发的四种问题

  • 脏写（Dirty Write）：
    对于两个事务 Session A、Session B，如果事务Session A修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写

  • 脏读（Dirty Read）：
    对于两个事务 Session A、Session B，Session A读取了已经被 Session B更新但还没有被提交的字段。之后若 Session B回滚，Session A读取的内容就是临时且无效的。
    读到不一样的未提交的记录。

  • 不可重复读（Non-Repeatable Read）：
    对于两个事务Session A、Session B，Session A读取了一个字段，然后 Session B更新了该字段。 之后Session A再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。
    读到不一样的已提交的记录。

  • 幻读（Phantom）：
    对于两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插入了一些新的行。 之后, 如果 Session A再次读取同一个表, 就会多出几行。那就意味着发生了幻读。
    读到了多出来的已提交的记录。

脏写 > 脏读 > 不可重复读 > 幻读

2. 四种隔离级别

四种隔离级别都解决了脏写的问题，只在脏读、不可重复读、幻读之中进行取舍

  • 读未提交 READ UNCOMMITTED：只解决了脏写问题

  • 读已提交 READ COMMITTED：解决了脏读问题

  • 可重复读 REPEATABLE READ：解决了脏读问题，且在另一个事务更改数据后，仍然读到未修改时的数据

  • 可串行化 SERIALIZABLE：解决以上所有的问题

5. MySQL事务日志

事务有四种特性：原子性、隔离性、一致性和持久性。

  • 事务的隔离性由锁机制实现。
  • 二事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证。
    • REDO LOG称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
    • UNDO LOG称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

有的DBA或许会认为undo是redo的逆过程，其实不然，redo和undo都可以视为是一种恢复操作，但是

  • redo log 是存储引擎层（innodb）生成的日志，记录的是物理级别上的页操作，比如页号xxx、偏移量yyy写入了'zzz'数据，主要为了保证数据的可靠性；

  • undo log 是存储引擎层（innodb）生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了insert语句操作，那么undo log就记录一条与之相反的delete操作。主要用于事务的回滚（undo log记录的是每个修改操作的逆操作）和一致性非锁定读（undo log回滚行记录到某种特定的版本--MVCC，即多版本并发控制）。

5.1 redo日志

innoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才能访问，所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（checkPoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

5.1.1 REDO日志的好处、特点

1. 好处

  • redo日志降低了刷盘频率
  • redo日志占用的空间非常小

2. 特点

  • redo日志是顺序写入磁盘的
  • 事务执行过程中，redo log不断记录

redo log的刷盘策略

5.2 undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个undo log。

5.2.1 undo log的理解

事务需要保证 原子性 ，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：

  • 情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。

  • 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。

以上情况出现，我们需要把数据改回原先的样子，这个过程称之为回滚，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合原子性要求。

5.2.2 undo log的作用

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

6. 锁

为解决数据库并发操作的问题。

  • 方案一：读操作利用多版本兵法控制（MVCC），写操作加锁。

  • 方案二：读操作和写操作都加锁。

6.1 锁的不同角度分类

6.1.1 类型划分

  • 读锁：也称为共享锁，英文用S表示。

  • 写锁：也称为排他锁（独占锁），英文用X表示。

1. 锁定读

  • 对读取的记录加S锁
SELECT ... LOCK IN SHARE MODE;
# 或
SELECT ... FOR SHARE; #(8.0新增语法)
  • 对读取的记录加X锁：
SELECT ... FOR UPDATE;
  • S锁和S锁兼容，即SS锁兼容。XS、XX锁互斥。

MySQL8.0新特性：

在5.7及之前的版本，SELECT ... FOR UPDATE，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。在8.0版本中，SELECT ... FOR UPDATE, SELECT ... FOR SHARE 添加NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。

  • NOWAIT：如果查询的行已经加锁，会立即报错返回

  • SKIP LOCKED：如果查询的行已经加锁，只返回结果中不包含被锁定的行

2. 写操作

  • DELETE：对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，再执行delete mark操作。
  • UPDATE：在对一条记录做UPDATE操作时分为三种情况：
    • 情况1：未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化。则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原纪录的位置进行修改操作。
    • 情况2：未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化。则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。新插入的记录由INSERT操作提供的隐式锁进行保护。
    • 情况3：修改该记录的键值，则相当于在原纪录上做DELECT操作之后再来一次INSERT操作。
  • INSERT：一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

6.1.2 粒度划分

表级锁、页级锁、行锁。

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取、检查、释放锁等动作)。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity)”的概念。

对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细;其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁。

1. 表锁

该锁会锁定整张表，它是MySQL中最基本的锁策略，并不依赖于存储引擎，并且表锁是开销最少的策略。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁的问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

① 表级别的S锁、X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名：Metadata Locks，简称MDL）结构来实现的。

一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。比如，在系统变量autocommit=0，innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表t 的S锁或者X锁可以这么写：

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

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

② 意向锁 （intention lock）

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

1、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。

2、意向锁是一种不与行级锁冲突的表级锁，这一点非常重要。

3、表明“某个事务正在某些行持有了锁或该事务准备去持有锁”

意向锁分为两种：

  • 意向共享锁（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;

即：意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在数据表的对应意向锁。

③DML锁
更改表结构时施加到表上的锁

2. 行锁

  1. 记录锁
  2. 间隙锁
  3. 临键锁（inonDB默认，即可重复读默认的锁）
  4. 插入意向锁
  • 行级锁都有可能发生死锁

3. 页锁

介于行锁和表锁之间，也会出现死锁。

注意：每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

6.1.3 对锁的态度划分

1. 悲观锁

  • select ... for update; 是MySQL中的悲观锁。

2. 乐观锁

不采用数据库自身的锁机制，而是通过程序来实现。

  1. 乐观锁的版本号机制

  2. 乐观锁的时间戳机制

6.1.4 按加锁的方式划分

  1. 隐式锁

  2. 显式锁

6.1.5 其他锁

1. 全局锁

2. 死锁

1. 产生死锁的必要条件
  1. 两个或两个以上的事务
  2. 每个事务都已经持有锁并且申请新的锁
  3. 锁资源同时只能被同一个事务持有或者不兼容
  4. 事务之间因为持有锁和申请锁导致彼此循环等待
2. 如何处理死锁

  • 方式一：等待超时

  • 方式二：使用死锁检测进行死锁处理

3. 如何避免死锁

  1. 合理设计索引，使业务 SOL 尽可能通过索引定位更少的行，减少锁竞争。

  2. 调整业务逻辑 SQL 执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面

  3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也更小。

  4. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select ...for update 语句，如果是在事务里运行了 start transaction 或设置了autocommit 等于0，那么就会锁定所查找到的记录。

  5. 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

隐式锁的判断：

  • 聚簇索引判断锁的内存结构，判断当前记录的trx_id是否是当前活跃的事务，是的话给当前事务创建一个锁结构，并且is_waiting=false；然后给自己页创建一个锁结构，is_waiting=true。

  • 二级索引会到页结构的PAGE_MAX_TRX_ID字段中判断该字段保存的是否小于当前活跃事务的最新id，如果是则代表该事务已经提交，则不存在隐式锁，反之则隐式锁存在，需要回表去到聚簇索引中进行判断。

6.1.6 锁的内存结构

  • 锁的监控
mysql> show status like 'innodb_row_lock%';

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量；

  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；（等待总时长）

  • Innodb_row_lock_time_avg：每次等待所花平均时间；（等待平均时长）

  • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数；（等待总数）

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

  • 不加锁的select属于快照读，加锁即为当前读

  • DML操作的时候都属于当前读

7.1 MVCC原理之ReadTiew

MVCC的实现依赖于：隐藏字段、ReadView、undo log。

  • 读已提交的隔离级别，每次查询都要获取一次都要生成一次readview；而可重复读的隔离级别，只在第一次select的时候获取一次readview，后面的select会复用这个readview。

标签：知识点,事务,log,归纳,查询,索引,book,MySQL,主键
From： https://www.cnblogs.com/iforevering/p/17737143.html

相关文章

  • mysql命令行执行sql文件
    方法一：mysql命令，直接在服务器上执行mysql–u用户名–p密码–D数据库<【sql脚本文件路径全名】例如：mysql-uroot-proot-Dtest</tmp/test.sql方法二：source命令，登录mysqlclient执行source【sql脚本文件路径全名】例如：source/tmp/test.sql......
  • mysql
    在命令提示符中先输入cdC:\ProgramFiles\MySQL\MySQLServer8.0\bin打开目录,然后输入mysql-hlocalhost-uroot-p登录数据库，再输入数据库密码6km，登录成功查看信息输入status命令可以查看MySQL的版本信息 ......
  • 有关于Mysql的简单问题及示例（增删改查 一对一 多对多 左外连接 右外链接）
    Mysql1、请自行设计表并针对该表练习最基本的增删改查且写出示例代码建立表格class其中有属性nameidgenderinterest表格建立完成向表中插入数据插入数据完成尝试删除表中id=101的数据删除数据成功尝试修改表中id为102的数据修改成功2、请问什么是一对多？请自......
  • MySQL 的 InnoDB 存储引擎简介
    MySQL是世界上最流行的开源关系型数据库管理系统之一，而其中的存储引擎则是其关键组成部分之一。InnoDB存储引擎在MySQL中扮演了重要角色，提供了许多高级功能和性能优化，适用于各种应用程序和工作负载。本文将深入介绍InnoDB存储引擎的各个方面，以帮助您更好地理解它的特性和优......
  • 【知识点】如何找到正确的算法？
    算法思路一、多组查询·考虑如何利用已知信息避免重复查询。·考虑各种预处理，例如前缀和。二、规模减小·考虑树、链等三、以小见大·考虑特殊情况，并考虑以此为基础继续转移四、模拟优化·考虑高维复杂度算法，并考虑尽可能优化五、题面信息·数据规模\[n≥10......
  • MySQL-8.0.18的搭建
    一、安装mysql-8.0.18(1)下载安装包#下载前先卸载mariadb的一些东西yumremove-ymariadb*#然后下载安装包mkdir/usr/local/soft&&cd/usr/local/softwget-chttps://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz(2)......
  • 【知识点】如何找到正确的算法？
    #算法思路**一、多组查询**·考虑如何利用已知信息避免重复查询。·考虑各种预处理，例如前缀和。------------**二、规模减小**·考虑树、链等------------**三、以小见大**·考虑特殊情况，并考虑以此为基础继续转移------------**四、模拟优化**·考虑高维复杂度......
  • MySql的安装配置
    首先简单概述分为几个步骤：一、下载Mysql二、安装Mysql三、验证Mysql安装是否成功四、 配置环境变量五、验证配置环境变量是否成功一、下载Mysql要在Windows或Mac上安装MySQL，首先从MySQL官方网站下载最新的MySQLCommunityServer版本：官网:https://www.mysql.com/1.首......
  • 关于Async、Await的一些知识点
    在ASP.NETCore中，当一个HTTP请求到达服务器时，它会被分配给线程池中的一个线程来处理。该线程会执行相应的Controller方法。如果这个方法是一个异步方法并且使用了await关键字，那么在await的代码执行完毕之前，这个线程会被释放回线程池，可以用来处理其他的HTTP请求。当await的代码执......
  • MySQL详细解析之Clone插件
    文档课题：MySQL详细解析之Clone插件.系统：rhel7.3数据库：MySQL8.0.271、理论知识从MySQL8.0.17开始引入Clone插件，克隆插件允许从本地或远程MySQLServer克隆数据.克隆的数据存储在InnoDB中的schema（database）、table（表）、tablespaces（表空间）和datadictionarymetadata（数据字典元数......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99