MySQL
1. 索引
1.1 索引的概述
索引（index）是帮助MySQL高效获取数据的数据结构（有序）
1.2 索引的优缺点
-
优点
- 提高数据检索效率,降低磁盘IO的成本
- 通过索引列对数据进行排序, 降低数据排序的成本,降低CPU的消耗
-
缺点
- 索引是需要存储的,增加存储的成本,可忽略不计
- 降低更新表的效率,因为增删改查比较少,而且目前大部分使用读写分离,所以利大于弊
1.3 结构
1.3.1 B-tree
B-Tree又叫做B树，和平衡二叉树不同的地方在于B树是多叉树(平衡多路查找树)，Oracle和MongoDB的索引技术就是基于B树的数据结构，B树也可以看作是对查找树的一种扩展。
-
B-树，全称是 Balanced Tree，是一种多路平衡查找树。
-
一个节点包括多个key (数量看业务)，具有M阶的B树，每个节点最多有M-1个Key。
-
节点的key元素个数就是指这个节点能够存储几个数据。
-
每个节点最多有m个子节点，最少有M/2个子节点，其中M>2。
-
数据集合分布在整个树里面，叶子节点和非叶子节点都存储数据；类似在整个树里面做一次二分查找。
-
B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data）。
- 每个节点拥有最多的子节点，子节点的个数一般称为阶。
- 阶：m阶是代表每个节点最多有m个分支（子树）。
- 树的度：这棵树里面节点最大的度。
- 节点的度：当前节点有几个子节点。
扩展
-
简单二叉树
- 普通的二叉树，很难构成现实的应用场景
- 顺序插入, 形成链表, 大大降低查询效率
- 大数据情况下, 层级较深, 大大降低查询效率
-
红黑树
- 平衡二叉树, 通过变色、左旋和右旋避免形成链表
- 大数据情况下, 层级较深, 大大降低查询效率
最大度数
一个根节点最多能应有子节点的个数
以一个最大度数为5的Btree为例,一个节点(4个key, 5个指针),通过key的范围进行创建树,如果超过4个key,中间元素向上分裂
数据结构演示网站: 链接
面试题
-
为什么InnoDB使用B+Tree作为索引结构?
- 1.相对于二叉树,层级少,查询效率高
- 2.对于B-Tree,叶子结点和非叶子节点都会存储data,二每一页的大小是有限制的,导致键值减少, 指针减少,要同样保存大量数据,增加树的深度,导致性能降低
- 3.相对Hash,支持排序和范围查询
1.3.2 Hash
采用一定的hash算法,将键值对换算成新的hash值, 映射到对应的槽位中, 并存储到Hash表中
特点
- 只适用于精确查询(=,in), 不适合范围查询(between,>,<…)
- 无法利用索引进行排序操作
- 查询效率高, 通常只索引一次即可, 效率通常高于B+Tree,反之效率低
1.3.3 索引的分类
- 主键索引
- 唯一索引
- 常规索引
- 快速定位特定数据
- 全文索引
- 使用的是倒排索引, 是根据关键字查询记录, 而不是在记录中查询关键字
1.3.4 InnoDb中索引的存储形式
-
聚集索引
就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据
-
存在主键,将主键作为聚集索引
-
不存在主键,将第一个唯一unique作为聚集索引
-
如果以上两者都没有,InnoDB,生成一个rowid作为聚集索引
-
特点
- 必须有,而且只能有一个
-
-
二级索引
数据和索引分开存储,叶子结点存储的是主键
- 特点
- 可以存在多个
- 特点
-
回表查询
先通过二级索引查询到主键值, 在通过主键值通过聚集索引查询行数据
1.4 语法
创建索引
create [unique | fulltext | spatial] index index名称 on 表名(字段名...)
注: spatial为空间索引
删除索引
drop index 索引名称 on 表名
查看索引
show index from 表名称
1.5 性能分析
1.5.1 查看执行频次
SHOW GLOBAL STATUS LIKE 'Com_______';
1.5.2 慢日志查询
SHOW VARIABLES LIKE 'slow_query_log';
1.5.3 profiles详情(了解)
作用: 帮助我们查询sql语句时间都消耗在了哪里
判断数据库是否支持
SELECT @@have_profiling;
查询是否开启
SELECT @@profiling;
-- 开启
SET profiling = 1;
语法
SHOW PROFILES;
SHOW PROFILE [cpu] FOR QUERY 17;
1.5.4 explain执行计划
语法
desc|explain SQL语句
参数详解
-
id: select查询的序列号,表示子语句或者查询表的执行顺序(id相同, 从上往下执行;id不同,越大越先执行)
-
select_type: 表示select的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、 UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
-
type: 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
-
NULL: 基本上不会优化到这个层级
-
const: 主键或者唯一性索引
-
ref: 非主键或者非唯一性索引
-
index: 虽然会用到索引,但是也会全部遍历索引
-
all: 全表扫描
-
-
possible_keys: 显示可能应用在这张表上的索引，一个或多个
-
key: 实际使用的索引，如果为NULL，则没有使用索引
-
key_len: 表示索引中使用的字节数，该值为索引字段的最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
-
ref: 显示使用哪个列或常数与key一起从表中选择行
-
rows: MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
-
filtered: 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
1.6 索引使用规则
1.6.1 最左前缀法则
在联合索引中,要遵循最左前缀法则,指查询条件中,包含索引中从左往右的列,哪列缺失,索引即从哪列部分失效, 与where条件中存在的位置无关
1.6.2 范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
explain select * from tb_user where profession=‘软件工程’ and age>30 and status=‘0’;
status索引会失效
- 业务条件允许的条件下, 可使用>=或者<=避规
- 将索引列放到范围查询的左侧
1.6.3 索引失效
- 在where条件中使用聚合函数, 索引失效
- 字符串不加单引号, 索引失效
- like语句中, 模糊尾部, 索引不会失效; 模糊头部, 索引失效
- or连接的条件, or左右都有索引才会使用索引
- 如果MySQL评估,使用索引比走全表扫描还慢,则不适用索引
面试题
IS NULL和IS NOT NULL走不走索引?
不固定,是否走索引是根据表中的数据分布而决定的
1.6.4 SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些认为的提示来达到优化操作的目的
-
use index
explain select * from tb_user use index(idx_user_pro) where profession='软件工程'; -
ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程'; -
force index
explain select * from tb_user force index(idx_user_pro) where profession='软件工程';
1.6.5 覆盖索引&回表查询
尽量使用覆盖索引(查询使用了索引,并且返回的列,在该索引中已经能够全部找到), 避免使用select *
如果查询字段没有在索引中找到, 需要根据 唯一键 回表查询
执行计划Extra
面试题
username和password建立联合索引, 避免回表查询
1.6.6 前缀索引
当索引列类型是verchar,text大文本类型时,如果创建常规索引,索引会很大,浪费磁盘IO;可以针对文本的前缀字符进行索引,大大节约索引空间,提高索引效率
1.6.7 单列索引和联合索引的选择
实际业务场景中, 如果查询条件为多个, 建议使用联合索引
设计原则
- 针对于 数据量较大，且查询比较频繁的表建立索引。
- 针对于常作为查询条件 (where)、排序 (order by)、分组 (group by)操作的字段建立索引。
- 尽量选择 区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立 前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个2索引最有效地用于查询。
1.6.8 总结
1、前导模糊查询不能使用索引， 如name like ‘%涛’
2、Union、in、or可以命中索引，建议使用in。
3、负条件查询不能使用索引，可以优化为in查询，其中负条件有！=、<>、not in、not exists、not like等
4、联合索引最左前缀原则，又叫最左侧查询，如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。
5、建立联合查询时，区分度最高的字段在最左边
6、如果建立了（a，b）联合索引，就不必再单独建立a索引。同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引
7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置
8、范围列可以用到索引，但是范围列后面的列无法用到索引。索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between等。
9、把计算放到业务层而不是数据库层。在字段上计算不能命中索引，
10、强制类型转换会全表扫描，如果phone字段是varcher类型，则下面的SQL不能命中索引。Select * fromuser where phone=13800001234
11、更新十分频繁、数据区分度不高的字段上不宜建立索引。更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（*）来计算。
12、利用覆盖索引来进行查询操作，避免回表。被查询的列，数据能从索引中取得，而不是通过定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。
13、建立索引的列不能为null，使用not null约束及默认值
14、利用延迟关联或者子查询优化超多分页场景，
MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页进行SQL改写。
15、业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
16、超过三个表最好不要用join，需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。
17、如果明确知道查询结果只要一条，limit 1能够提高效率，比如验证登录的时候。
18、Select语句务必指明字段名称
19、如果排序字段没有用到索引，就尽量少排序
20、尽量用union all 代替 union。Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟，当然，使用union all的前提条件是两个结果集没有重复数据。
2. SQL优化
2.1 插入数据
2.1.1 insert优化
批量插入
insert into values(1...),(2...),(3...)
批量插入单次建议数据量在500-1000,如果更多,分批进行批量插入
2.1.1.1 手动提交事务
2.1.1.2 主键顺序插入
示例：
主键乱序插入：8,7,16,9,10,14,13,11,12,15
主键顺序插入：7,8,9,10,11,12,13,14,15,16
2.1.1.3 大批量数据(百万)插入(load)
sql文件加载
2.2 update优化
where使用索引字段进行更新, 否则行锁会升级为表锁, 从使并发性能降低
2.3 主键优化
2.3.1 页分裂
tip: 主键顺序插入,避免页分页造成效率降低
2.3.2 页合并
2.3.3 设计原则
- 业务允许的情况下, 尽量降低主键的长度
- 业务允许的情况下, 尽量使用主键自增长
- 避免对主键的修改
如果需要分布式ID
1. 使用redis自增的方式获取id(个人比较喜欢, 不过需要确保redis高可用)
2. 使用雪花算法生成id
2.4 order by优化
- using filesort
- using index(效率高)
原则- 默认创建索引为升序排序
- 如果需要根据两个字段进行排序, 两个都是升序或者降序,直接走索引排序(useing index),否则全表扫描排序(using filesort)
- 创建索引指定排序
- create index index_name on table_name(c1 asc, c2 desc)
2.5 group by优化
- 尽量索引进行分组
- 在联合索引的情况下, 尽量满足最左前缀法则,where+group by字段在联合索引中也是满足最左前缀法则的
2.6 limit优化
- 但是上面的方式优化有限, 随着数量的增加,很越来越慢, 实际业务中使用分表的方式优化
- 当多条件进行查询是, 先查询出id, 再根据id查询出具体的数据
2.7 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. 锁
3.1 锁的分类标准
锁可以根据 加锁机制、锁粒度、兼容性以及锁模式来区分
3.2 按锁的粒度划分
3.2.1 全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是
-- 加锁
FLUSH TABLES WITH READ LOCK;
-- 解锁
UNLOCK TABLES;
当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是：做全库逻辑备份 。
3.2.2 表级锁
MySQL 里面表级别的锁有两种：一种是表锁，比如自增锁; 一种是元数据锁（meta data lock，MDL)。
表锁是MySQL中最大粒度的锁定机制，会锁定整张表，可以很好的避免死锁，是 MySQL 中最大颗粒度的锁定机制。表锁由 MySQL Server 实现，一般在执行 DDL 语句时会对整个表进行加锁，比如说ALTER TABLE等操作。在执行 DML 语句时，也可以通过LOCK TABLES显式指定对某个表进行加锁。
-
写锁（Exclusive Lock）：写锁会阻止其他会话对表进行读取或写入操作
LOCK TABLES table_name WRITE; -
读锁（Shared Lock）：读锁允许多个会话同时读取表，但阻止其他会话进行写入操作。
LOCK TABLES table_name READ; -
写锁等待（Write Lock Wait）：如果一个会话尝试获取写锁，但该表已被其他会话以读或写模式锁定，则该会话将等待，直到所有锁都被释放
LOCK TABLES table_name WRITE, another_table_name WRITE...; -
解锁
UNLOCK TABLES;
3.2.3 页级锁
页级锁是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。页级锁的颗粒度介于行级锁与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也是介于上面二者之间。另外，页级锁和行级锁一样，会发生死锁。页级锁主要应用于 BDB 存储引擎。
3.2.4 行级锁
MySQL 的行锁是在引擎层由各个引擎自己实现的， 且锁定颗粒度在 MySQL 中是最小的，比如 MyISAM 引擎就不支持行锁，行级锁主要应用于 InnoDB 存储引擎。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到并发度，只针对操作的当前行进行加锁，所以行级锁发生锁定资源争用的概率也最小。比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新
MySQL中3种锁的特性可大致归纳如下：
3.3 按锁的兼容性划分
3.3.1 共享锁
-- 加锁方式：共享锁
SELECT...LOCK IN SHARE MODE
3.3.2 排它锁
-- 加锁方式：排它锁
SELECT...FOR UPDATE
InnoDB实现标准的行级锁定，其中有两种类型的锁， 共享锁（S）和排它锁（X）, 排它锁也称为独占锁。
共享锁允许持有锁，读取行的事务。
排它锁不允许持有锁，更新或删除行的事务。
3.4 按锁的模式划分
3.4.1 记录锁
记录锁(Record Locks)属于为行锁，表示对某一行记录加锁
-- id 列为主键列或唯一索引列
SELECT * FROM test WHERE id = 10 FOR UPDATE;
id为10的行记录会被锁住，可以防止其他会话插入，更新或删除行。
记录锁总是锁定索引记录（SELECT和UPDATE都会加锁），即使表没有定义索引。对于这种情况， InnoDB创建一个隐藏的聚集索引并使用该索引进行记录锁定。但因为可能会扫描全表，那么该锁也就会退化为表锁。
注意：
- id列必须为唯一索引或主键列，否则上述语句加的锁会变成临键锁。
- 查询语句必须为精准匹配=，不能>、<、like等，否则也会退化成临键锁。
3.4.2 间隙锁
间隙锁(Gap Locks)是对索引（非唯一索引）记录之间的间隙，锁定一个区间：加在两个索引之间，或者加在第一个索引之前，或者加在最后一个索引之后的间隙。
注意！间隙锁锁住的是一个区间，而不仅仅是这个区间中目前仅存在的数据行
SELECT * FROM test WHERE id BETWEEN 10 and 15 FOR UPDATE;
例如上面的语句，那（10，15）整个区间的记录行都会被锁住，即id为11，12，13，14数据行的插入操作都会被阻塞，但是10和15两条记录行并不会被锁住。对于唯一索引，如果使用等值查询，那么间隙锁会退化为行锁。
-- 身份证号，唯一索引
SELECT * FROM user WHERE identity_id = 100;
这里还值得注意的是，间隙锁只阻止其他事务插入到间隙中，并不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用，即不同的事务可以在间隙上持有冲突的锁。 例如，事务 A 可以在间隙上持有共享间隙锁（间隙 S 锁），而事务 B 在同一间隙上持有排他间隙锁（间隙 X 锁）。允许冲突间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁
总结：
- 间隙锁锁区间的索引，使用唯一索引搜索唯一行不需要间隙锁定。
- 在READ COMMITTED（RC）隔离级别下，不会使用gap lock，在Repeatable Read（RR）级别及以上（Serializable）才会使用它。
- 间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。
3.4.3 临键锁
临键锁(Next-Key)简单理解是 “记录锁+间隙锁” 的组合，但Next-Key lock与record lock加锁的粒度一样，都是加在一条索引记录上的。一个next-key lock=对应的索引记录的record lock+该索引前面的间隙的gap lock，通过临键锁可以解决幻读的问题。
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，在这种情况下，InnoDB使用临键锁进行搜索和索引扫描，以防止幻像行，比如select … in share mode或者select … for update语句。但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。
记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13和20。那么，临键锁的范围如下，左开右闭：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
唯一索引等值查询：
- 当查询的记录是存在的，next-key lock 会退化成「记录锁」。
- 当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
非唯一索引等值查询：
- 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
- 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于：
- 唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
- 非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁
3.4.4 意向锁
意向锁（Intention Locks）是表级锁，指示事务稍后需要(或想要，表明锁的意向)对表中的行使用哪种类型的锁（共享锁或独占锁），即用来标识该表上面有数据被锁住（或即将被锁）。
意向锁有两种类型：
- 意向共享锁（IS）：一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。
- 意向排它锁（IX）：一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。
意图锁定协议如下：
- 在事务可以获取表中行的共享锁之前，它必须首先获取表上的IS锁或更强的锁。
- 在事务获得表中行的排他锁之前，它必须首先获得表的IX锁。
表级锁类型兼容性总：
如果请求事务与现有锁兼容，则向请求事务授予锁，但如果与现有锁冲突，则不会。事务一直等到冲突的现有锁被释放。如果锁定请求与现有锁定发生冲突并且由于会导致死锁而无法授予 ，则会发生错误。
除了全表请求（例如，LOCK TABLES … WRITE）之外，意向锁锁不会阻止任何内容。意向锁的主要目的是表明有人正在锁定一行，或者打算锁定表中的一行。
这里说一下意向锁存在的目的：可以快速判断该表是否存在行锁。
比如，事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁。那么此时事务T2要进行LOCK TABLE … WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突，不授予锁。
再比如，事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。
3.4.5 插入意向锁
插入意向锁(Insert Intention Locks)是在插入一条记录行前，由 INSERT 操作产生的一种特别的间隙锁
该锁用以表示插入意向，当多个事务在同一区间插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占（排他）锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。
总结来说，插入意向锁的特性可以分成两部分：
- 插入意向锁是一种特殊的间隙锁，如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。
- 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。
需要强调的是，虽然插入意向锁中含有“意向锁”三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。
3.4.6 自增锁
自增锁(auto-inc Locks)是一种特殊的表级锁，主要用于事务中插入自增字段（AUTO_INCREAMENT），也就是我们最常用的自增主键id。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待插入到该表中，以便第一个事务插入的行接收连续的主键值。
可以通过配置项 innodb_autoinc_lock_mode调整自增锁算法：传统模式（还没有锁模式这个概念时，InnoDB 的自增锁运行的模式）、连续模式（MySQl 8.0以前的默认模式）以及交叉模式。
3.5 按加锁机制划分
3.5.1 悲观锁
现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。
悲观锁(Pessimistic Locking)，悲观锁是指在数据处理过程，使数据处于锁定状态，一般使用数据库的锁机制实现。
-- 开启事务
START TRANSACTION;
-- 实现步骤首先要开启事务, 然后对记录进行加锁
SELECT * FROM test WHERE id = 10 FOR UPDATE;
-- 事务提交
COMMIT;
注: 在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住
3.5.2 乐观锁
乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。接下来我们看一下乐观锁在数据表和缓存中的实现。
放个被用烂了的图:
实现乐观锁的方法
可以通过使用版本号或时间戳字段实现乐观锁。基本的实现原理是在更新数据时，检查数据的版本号或时间戳是否与当前事务开始之前获取的值一致，如果不一致，则表示数据已被其他事务修改，需要进行相应处理。
举个栗子🌰：
-- 创建表并添加版本号字段
CREATE TABLE my_table (
id INT PRIMARY KEY,
data VARCHAR(100),
version INT DEFAULT 0
);
-- 插入初始数据
INSERT INTO my_table (id, data) VALUES (1, 'Initial data');
-- 使用CAS方式更新数据
UPDATE my_table SET data = 'Updated data', version = version + 1 WHERE id = 1 AND version = <previous_version>;
<previous_version>应替换为上一次获取数据时的版本号
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
