(ROOT)Mysql总结

关于索引查询顺序io的问题

【mysql进阶-彩蛋篇】深入理解顺序io和随机io(全网最详细篇)-CSDN博客

页的上层结构

 其中区是物理连续的64个页，为的是io顺序访问

详见：

MySQL --- 读书笔记 --- InnoDB数据结构 - huang1993 - 博客园 (cnblogs.com)

mysql中的索引在磁盘中如何存储的 mysql索引存储结构深入理解_mob6454cc6d3e23的技术博客_51CTO博客

下面这个说的也有道理 

读《高性能MySQL》笔记---索引_索引怎么把随机io变成顺序io-CSDN博客

存储引擎

存储引擎功能表
功能    MyISAM    Memory    InnoDB    Archive    NDB
B树索引    是    是    是    否    否
备份/时间点恢复    是    是    是    是    是
集群数据库支持    否    否    否    否    是
聚集索引    否    否    是    否    否
压缩数据    是    否    是    是    否
资料快取    否    否    是    否    是
加密数据    是    是    是    是    是
外键支持    否    否    是    否    是
全文搜索索引    是    否    是    否    否
地理空间数据类型支持    是    否    是    是    是
地理空间索引支持    是    否    是    否    否
哈希索引    否    是    否    否    是
索引缓存    是    否    是    否    是
锁定粒度    表    表    行    行    行
MVCC    否    否    是    否    否
复制支持    是    限量    是    是    是
储存限制    256TB    内存    64TB    否    384EB
T树索引    否    否    否    否    是
交易次数    否    否    是    否    是
更新数据字典的统计信息    是    是    是    是    是

mysql查询语句执行流程

MySQL查询语句执行过程 - 残城碎梦 - 博客园 (cnblogs.com)

查询请求的执行流程

众所周知在MySQL数据库应用中查询请求是使用最多的，假设我们输入下面的SQL，通过客户端请求MySQL服务器，会得到一个包含user的结果集。但是，其中MySQL的处理过程我们并不了解，那么下面就让我们一起看看在查询请求前后MySQL服务端发生了些什么吧。

select * from user where userId=1

如上图所示，整张图由三部分组成，从上到下分别是客户端(紫色)、MySQL Server层(绿色)、MySQL存储引擎层(黄色)。

• 客户端，主要负责与MySQL Server层建立连接，发送查询请求以及接受响应的结果集。
• MySQL Server层，主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能，例如平时使用最多的存储过程、触发器、视图都在这一层中。还有一个通用的日志模块 bin log。
• MySQL 存储引擎层，主要负责数据的存储和提取。其支持多个存储引擎，例如：InnoDB、MyISAM等。常用的有InnoDB，它从MySQL 5.5.5版本开始成为了MySQL的默认存储引擎，重要的是InnoDB 引擎包含了自带的日志模块 redo log。

上面介绍了MySQL的组件结构，那么这里将其处理SQL语句的流程简单梳理一遍，之后再对每个组件逐一进行介绍。

1. 连接器：当客户端登陆MySQL的时候，对身份认证和权限判断。
2. 查询缓存：执行查询语句的时候，会先查询缓存(MySQL 8.0 版本后移除)。
3. 分析器：假设在没有命中查询缓存的情况下，SQL请求就会来到分析器。分析器负责明确SQL要完成的功能，以及检查SQL的语法是否正确。
4. 优化器：为SQL提供优化执行的方案。
5. 执行器：将语句分发到对应的存储引擎执行，并返回数据。

优化

 数据类型的优化

• 更小的通常更好

  • 可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。

    尽量使用满足需求的最小数据类型

  • 1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串。1、最大长度：255    2、会自动删除末尾的空格。3、检索效率、写效率 会比varchar高，以空间换时间

    • 1、存储长度波动不大的数据，如：md5摘要

    • 2、存储短字符串、经常更新的字符串

  • 2、varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性。varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。

    • 应用：1、存储长度波动较大的数据，如：文章，有的会很短有的会很长   2、 字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度    3、适合保存多字节字符，如：汉字，特殊字符等

  • 3、text不设置长度，当不知道属性的最大长度时，适合用text；

    MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。

    两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

  • 按照查询速度：char>varchar>text

  • datetime（8byte）和timestamp(4byte),date(3byte)

    • 1、不要使用字符串类型来存储日期时间数据

      2、日期时间类型通常比字符串占用的存储空间小

      3、日期时间类型在进行查找过滤时可以利用日期来进行比对

      4、日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算

      5、使用int存储日期时间不如使用timestamp类型

  • 使用枚举代替字符串类型

  • ip:

    select inet_aton('1.1.1.1')

    select inet_ntoa(16843009)

• 简单就好
• 尽量避免null

合理使用范式和反范式（适当数据冗余）

主键的选择推荐使用代理主键

字符集的选择

• 2.如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。

• 1.纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。

• 3.MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

存储引擎的选择

• innodb对比myisam支持事务，行锁，外键，适合大量insert，delete，update

适当拆分（当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。）

索引优化：查看执行计划

• 分类：主键索引 唯一索引 普通索引 全文索引 组合索引
• 技术：覆盖索引 最左匹配  索引下推
• 优化细节：

  • 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

  • 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询

  • 使用前缀索引（计算完整列的选择性：select count(distinct left(city,3))/count(*) as sel）

  • 使用索引扫描来排序（只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序 ）

  • union all,in,or都能够使用索引，但是推荐使用in（其他的会每个条件都回表）

  • 范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

  • 强制类型转换会全表扫描

  • 更新十分频繁，数据区分度（完整列的选择性）不高的字段上不宜建立索引

  • 创建索引的列，不允许为null，可能会得到不符合预期的结果（mysql中null本身占一字节，查询时null参与的拼接字符串等最终返回null，如concat(null,"lalala")返回null）

  • 当需要进行表连接的时候，最好不要超过三张表，因为需要join关联的字段，数据类型必须一致

  • 能使用limit的时候尽量使用limit

  • 单表索引建议控制在5个以内

  • 单索引字段数不允许超过5个（组合索引）

  • 索引监控

    • show status like 'Handler_read%';

      • Handler_read_first：读取索引第一个条目的次数

      • Handler_read_next：通过索引读取下一条数据的次数

      • Handler_read_rnd_next：从数据节点读取下一条数据的次数

      • Handler_read_key：通过index获取数据的次数

      • Handler_read_last：读取索引最后一个条目的次数

      • Handler_read_prev：通过索引读取上一条数据的次数

      • Handler_read_rnd：从固定位置读取数据的次数

优化数据访问（是否向数据库请求了不需要的数据）

join

• 算法：

  • simple nested-loop join驱动表中一个个取去查询匹配表

  • index nested-loop join在匹配表有索引时会用，以减少查询次数。查询时查询到匹配表索引查到对应才会回表查询，如果非驱动表关联键只有主键就不用回表效率极高。

  • block nested-loop join如果有索引，会用上面第二种方式join，如果没有且开启优化器管理配置的optimizer_switch的设置block_nested_loop为on就用这种。join_buffer将驱动表join相关所有列缓存{可以简单理解为hashmap之类的}，批量与匹配表匹配，减少非驱动表访问频率。n个join会产生n-1个joinbuffer

• （1）Join Buffer会缓存所有参与查询的列而不是只有Join的列。

  （2）可以通过调整join_buffer_size缓存大小

  （3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。

  （4）使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。show variables like '%optimizer_switch%'

• left join左表一定是驱动表吗？-腾讯云开发者社区-腾讯云 (tencent.com)，left join如果有where语句将null列的行过滤掉，mysql会优化将left join转为inner join并将有索引的作为匹配表（如果有索引的是left join左边的，此时就从驱动表变成了匹配表）

排序

• 双次排序：第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。

  这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高

  两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

• 单次排序：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

• 需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

• 无论如何排序都是一个成本很高的操作，所以从性能的角度出发，应该尽可能避免排序或者尽可能避免对大量数据进行排序。

  推荐使用利用索引进行排序，但是当不能使用索引的时候，mysql就需要自己进行排序，如果数据量小则再内存中进行，如果数据量大就需要使用磁盘，mysql中称之为filesort。

  如果需要排序的数据量小于排序缓冲区(show variables like '%sort_buffer_size%';),mysql使用内存进行快速排序操作，如果内存不够排序，那么mysql就会先将树分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放再磁盘上，然后将各个排好序的块进行合并，最后返回排序结果

优化limit分页

• MySQL中Limit性能优化的方案_limit优化-CSDN博客

• 对where的条件加索引（如果where条件适合加索引）

• 优化此类查询的最简单的办法就是尽可能地使用覆盖索引（子查询），而不是查询所有的列

优化union

• 除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高

推荐使用用户自定义变量

• update t1 set lastupdated = now() where id = 1 and @now:=now();

  select @now;

分区表

• 
   CREATE TABLE `list_c` ( 
   `c1` int(11) DEFAULT NULL, 
   `c2` int(11) DEFAULT NULL, 
   `c3` char(20) DEFAULT NULL 
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  /*!50500 PARTITION BY RANGE COLUMNS(c1,c3) 
  (PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB, 
   PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */ 
     
   CREATE TABLE `list_c` ( 
   `c1` int(11) DEFAULT NULL, 
   `c2` int(11) DEFAULT NULL, 
   `c3` char(20) DEFAULT NULL 
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  /*!50500 PARTITION BY LIST COLUMNS(c3) 
  (PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB, 
   PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */ 
     

MySQL通过索引优化-这里可能有你不知道的索引优化细节（一）-腾讯云开发者社区-腾讯云 (tencent.com)

是否用到索引

查询条件	组合索引是否发挥作用
where a=3	是，只使用了a
where a=3 and b=5	是，使用了a,b
where a=3 and b=5 and c=6	是，使用了a,b,c
where a=3 and b=5	是，使用了a,b
where b=3 or c=5	否
where a=3 and c=6	是，只使用了a
where a=3 and b<10 and c=7	是，使用了a,b
where a=3 and b like '%xxoo%' and c=7	是，只是用了a

like %索引失效问题 

验证用例：name创建索引，主键为id

 

like xxx%

 EXPLAIN SELECT * FROM users WHERE `name` LIKE '001%';

索引不会失效

like %xxx%

EXPLAIN SELECT * FROM users WHERE `name` LIKE '%001%'

未使用索引

EXPLAIN SELECT  `name` FROM users WHERE `name` LIKE '%001%';

使用索引

EXPLAIN SELECT id , `name` FROM users WHERE `name` LIKE '%001%';

使用索引

EXPLAIN SELECT id , `name` , gender FROM users WHERE `name` LIKE '%001%';

未使用索引

LIKE查询以%开头使用了索引的原因就是使用了索引覆盖（依然是在索引中全扫描。。。）。
针对二级索引MySQL提供了一个优化技术。即从辅助索引中就可以得到查询的记录，就不需要回表再根据聚集索引查询一次完整记录。使用索引覆盖的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作，但是前提是要查询的所有列必须都加了索引。

LIKE查询以%开头会导致全索引扫描或者全表扫描，如果没有索引覆盖的话，查询到的数据会回表，多了一次IO操作，当MySQL预估全表扫描或全索引扫描的时间比走索引花费的时间更少时，就不会走索引。有了索引覆盖就不需要回表了，减少了IO操作，花费的时间更少，所以就使用了索引。

总结

LIKE查询 以%开头不一定会让索引失效。如果查询的结果中只包含主键和索引字段则会使用索引，反之则不会。
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/qq_39654841/article/details/126116260

连接查询优化

在sql优化中，永远应是以小表驱动大表。

查询过程驱动表不会用到索引，被驱动表会用到；

EXPLAIN语句分析出来的第一行的表即是驱动表

EXPLAIN select * from A a left join B b on a.code=b.code

如果只给A表的code建立索引

select A in/exist B;

当B表数据集小于A表数据集时，用in优于exists。

select *from tb_emp_bigdata A where A.deptno in (select B.deptno from tb_dept_bigdata B)
1

当A表数据集小于B表数据集时，用exists优于in。

select *from tb_dept_bigdata A where A.deptno in(select B.deptno from tb_emp_bigdata B);
1

结论：

• 右连接以右表为驱动表，需左表创建索引；左连接以左表为驱动表，需右表创建索引；
• 当使用inner join时，数据库会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;
• 在执行效率上，那个作为主表关系不大，执行效率差距不大 ;
• 驱动表索引没有生效，被驱动表索引有效 ;
• in后面跟的是小表，exists后面跟的是大表。如果两个表相差不大in和exist效率差不多；

索引下推

用例：

CREATE TABLE `user2` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `username` (`username`(191),`age`)

)

ENGINE=InnoDB

AUTO_INCREMENT=100001

DEFAULT CHARSET=utf8mb4

COLLATE=utf8mb4_unicode_ci;

# 关键字 KEY 用于指定要创建索引的列名。如果需要创建多列索引，则可以在 KEY 后面跟上多个列名，使用逗号进行分隔即可。

 MySQL 5.7开始出现索引下推。用例：

select * from user2 where username like 'j%' and age=99;

看一下执行计划，看到Extra一列里 Using index condition，这就是用到了索引下推。

图解

1. MySQL 的 server 层首先调用存储引擎定位到第一个以 j 开头的 username。
2. 找到记录后，存储引擎并不急着回表，而是继续判断这条记录的 age 是否等于 99，如果 age=99，再去回表，如果 age 不等于 99，就不去回表了，直接继续读取下一条记录。
3. 存储引擎将读取到的数据行返回给 server 层，此时如果还有其他非索引的查询条件，server 层再去继续过滤，在我们上面的案例中，此时没有其他查询条件了。假设 server 层还有其他的过滤条件，并且这个过滤条件把刚刚查到的记录过滤掉了，那么就会通过记录的 next_record 属性读取下一条记录，然后重复第二步。

5.7前

5.7以后

索引下推使用条件

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

• 只能用于InnoDB和 MyISAM存储引擎及其分区表；

• 对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

索引null值问题

准备

REATE TABLE a_achievement  ( 
    s_id       int(11) NULL,
    Math       decimal(4,1) NULL,
    English    decimal(4,1) NULL,
    Chinese    float NULL 
    );

CREATE TABLE a_achievement_t  ( 
    s_id       int(11) not NULL,
    Math       decimal(4,1)not NULL,
    English    decimal(4,1)not NULL,
    Chinese    float not NULL 
    );

insert into a_achievement(s_id,Math,English,Chinese)
values(1,100,95,88),(2,98,92,100),(3,99,100,80),(4,100,95,99),(5,90,96,100);

insert into a_achievement_t(s_id,Math,English,Chinese)
values(1,100,95,88),(2,98,92,100),(3,99,100,80),(4,100,95,99),(5,90,96,100);

create index s_id_index on a_achievement(s_id);
create index s_id_index on a_achievement_t(s_id);

 测试

select all is not null

explain select * from a_achievement where s_id is not null 

explain select * from a_achievement_t where s_id is not null 

 select all is null

explain select * from a_achievement where s_id is null 

 explain select * from a_achievement_t where s_id is null 

select s_id is null

explain select s_id  from a_achievement where s_id is null 

 is null的少所以走了ref查询（ref：使用了非唯一性索引进行数据的查找）

explain select s_id  from a_achievement_t where s_id is null 

select s_id is not null

explain select s_id  from a_achievement where s_id is not null 

 not null的多所以走了range（range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() ）

explain select s_id  from a_achievement_t where s_id is not null 

 索引覆盖了所以走了index（index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序）

结论：

select all不会走索引

not null字段查询null 会提示impossible WHERE

其他见测试文字描述

mysql事务

mysql锁

mysql锁结构

锁所在的事务信息

一个锁结构对应一个事务，那么这里就存储着锁对应的事务信息。它其实只是一个指针，可以通过它获取到内存中关于该事务的更多信息，比如：事务id是多少。

索引信息

对于行级锁来说，这里记录的就是加锁的记录属于哪个索引。

表锁/行锁信息

(1)、对于表锁，主要是来记录对哪张表进行的加锁操作以及其他的信息。
(2)、对于行锁，内容包括3部分：
Space ID：记录所在的表空间ID。
Page Number：记录所在的页号。
n_bits：一条记录对应一个bit

type_mode

它是由32个bit组成的，分别为：lock_mode、lock_type、lock_wait和rec_lock_type，如下图所示：

锁分类

按属性分类（共享锁和排他锁）

共享锁：又称读锁，简称 S 锁。当事务对数据加上读锁后，其他事务只能对该数据加读锁，不能加写锁。即：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;

共享锁加锁方法： select …lock in share mode

排他锁，又称为写锁，简称 X 锁，当事务对数据加上排他锁后，其他事务无法对该数据进行查询或者修改，即：

• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

MySQL InnoDB引擎默认 update，delete，insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型。

排他锁加锁方式：update，delete，insert ，select …for update

按粒度分类（全、表、页、行）

全局锁

对整个 MySQL 数据库实例加锁，加锁期间，对数据库的任何增删改操作都无法执行。（数据备份之类的操作）

#1.加锁全局锁，锁住全部表，其他客户端只能读不能改或写，更新或者插入命令进入阻塞状态
flush tables with red lock; 
#2.将数据库数据备份到sql文件中，期间数据库支持查询操作。-u后面跟用户名，-p后面跟密码
mysqldump -uroot -p1234 itcast>itcast.sql
#3.备份完毕释放锁.
unlock tables;   

不加锁备份（基于快照）

#通过快照读来实现的，也就是备份时的数据，备份过程中的提交的事务时是看不到的，也不会备份进去。
#在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

single-transaction 实际上通过做了下面两个操作 ：
① 在开始的时候把该当前窗口的事务隔离级别设置成 repeatable read ；
② 然后启动一个事务（执行 begin ），备份结束的时候结束该事务（执行 commit ）

Flush tables with read lock (FTWRL)

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

加表锁：lock tables tablename read/write; unlock tables

元数据锁

给表加锁的时候或者执行SQL，系统会自动加上元数据锁，元数据指的就是表结构，如果某张表存在未提交的事务，那么不能修改这张表的结构，也就是为了避免DML语句和DDL语句的冲突，保证读写的正确性。mysql5.5中引入了元数据锁

元数据锁分为共享读锁，共享写锁和互斥锁，共享锁之间可以兼容，共享锁和互斥锁不兼容
执行select语句加上共享读锁（shared_read)
执行update，insert，delete语句加上的是共享写锁(shared_write)
执行alter语句加上的是互斥锁(exclusive)
所以就保证了对表中数据修改时，表的结构不能发生变化。保证数据的完整性和一致性

页级锁

MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。BDB 引擎支持页级锁

行级锁

行级锁是 MySQL 粒度最细的锁，发生锁冲突概率最低，但是加锁慢，开销大，容易死锁

Innodb并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

MySQL 中只有 InnoDB 引擎支持行锁，其他不支持

MySQL 中，行级锁并不是之间锁记录，而是锁的索引 。

普通的 select 语句是不会对记录加锁的，因为它属于快照读，是通过MVCC（多版本并发控制） 实现的。

MySQL 在执行select …lock in share mode（s）； update，delete，insert ，select …for update(x)语句时会自动加上行锁

按算法分类（行级锁Record Lock Gap Lock Next-Key Lock）

Record Lock

称为记录锁，锁住的是一条记录（行锁）。而且记录锁是有 S 锁和 X 锁之分的

Gap Lock

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻读而提出的。

Next-Key Lock

称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。前开后闭区间

按状态（意向锁）

意向锁就是代替了全局扫描去检查每条记录的行锁情况，也就是表中记录加行锁没有，加的是什么行锁，共享锁还是排它锁。这个意向锁相当于暴露出当前表中记录加行锁的类型，利于直接判断当前表能不能加表锁（排他锁与排它锁不兼容，排它锁和共享锁互斥，意向锁之间都互相兼容）

	意向共享锁(IS)	意向排他锁(IX)
共享锁(S)	兼容	互斥
排他锁(X)	互斥	互斥

隔离级别

RC隔离级别的行级锁：Record Lock

RR隔离级别的行级锁：Next-Key Lock

加锁（RR）

MySQL InnoDB 存储引擎锁详解与死锁分析 - 知乎 (zhihu.com)

1. 基于主键（聚簇索引）进行等值查询时，如果对应的值存在，则添加标准记录锁Record Lock和前后间隙锁。如果对应的值不存在，则需要在查询id所在的索引间隙添加间隙锁Gap Lock。
2. 基于主键（聚簇索引）进行范围查询时，采用采用Next Key Lock添加行锁。
3. 基于辅助索引进行等值查询时，会先在辅助索引上加锁Next Key Lock，然后在聚簇索引上加锁。聚簇索引上加锁算法采用Record Lock，即只锁记录不锁间隙。
4. 查询不走索引时，会在聚簇索引上加锁，加锁算法采用Next Key Lock，并且会锁定全表范围。（注意是通过Next Key Lock锁定的全表范围，而不是通过表级锁直接锁表）

加锁规律

• InnoDB中默认采用Next Key Lock加锁，Next Key Lock加锁范围前开后闭。
• 行锁都是加在索引上，如果通过聚集索引查询则在聚集索引上加锁，通过辅助索引查询则需要同时在辅助索引和聚集索引上加锁，不走索引则在聚集索引上加锁。
• 查找过程中访问到的索引都会加锁。注意是访问到的索引而不是满足查询条件的索引。
• 索引上的等值查询，没有满足条件的记录时，Next-key lock退化为间隙锁，加锁范围是查询值所在的间隙。
• 通过辅助索引查询并加锁时，需要进行回表查询然后在聚集索引上采用行锁Record Lock加锁。范围查询采用Next Key Lock加锁。

查看加锁信息

--查看加锁信息（MySQL8.0）
SELECT * FROM performance_schema.data_locks;
--查看锁等待（MySQL8.0）
SELECT * FROM performance_schema.data_lock_waits;

--查看元数据锁

select object_type,object_schema, object_name,1ock_type,1ock_duration from performance_schema.metadata_locks ;
-- 查看表锁
show open tables where In_use>0;
-- 查看最近一次死锁信息
show engine innodb status;

lock_mode说明

这里需要重点对 LOCK_MODE 加锁模式进行说明：

LOCK_MODE值    锁类型
IX    意向排他锁
IS    意向共享锁
AUTO_INC    自增主键锁
X    排他临键锁(Next Key) ,既锁记录，也锁间隙
S    共享临键锁(Next Key) ,既锁记录，也锁间隙
X,REC_NOT_GAP    排他标准记录锁(Record）,只锁记录，不锁间隙
S,REC_NOT_GAP    共享标准记录锁(Record) ,只锁记录，不锁间隙
S,GAP    共享间隙锁(GAP),只锁间隙
X,GAP    排他间隙锁(GAP) ,只锁间隙
INSERT_INTENTION    插入意向锁

快照读和当前读(MVCC)

MySQL InnoDB 存储引擎锁详解与死锁分析 - 知乎 (zhihu.com)

• 快照读：读取快照生成时的数据，通常简单的 select 操作会采用快照读；
  • SELECT * FROM apply_item WHERE id=?;
• 当前读：当前时刻已经提交的数据，插入/更新/删除操作会使用当前读；
  • SELECT * FROM apply_item WHERE id=? IN SHARE MODE （S Lock）
  • SELECT * FROM apply_item WHERE id=? FOR UPDATE （以下都为 X Lock）
  • INSERT INTO apply_item VALUES(...)
  • UPDATE apply_item SET ?=? WHERE ?
  • DELETE FROM apply_item WHERE ?
• 注：快照读和当前读实际上包含 Select、Update 和 Insert 等所有语句中的读取逻辑，不光指 Select。

CREATE TABLE `apply_item` (  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `apply_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '服务级申请单id',  
  `valid` int(11) NOT NULL DEFAULT '1' COMMENT '是否有效，1 表示有效，其他数值表示被删除',  
  `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',  
  `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',  
  PRIMARY KEY (`id`),  
  KEY `idx_apply_id` (`apply_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='申请单项';

死锁

MySQL InnoDB 存储引擎锁详解与死锁分析 - 知乎 (zhihu.com)

每个事务执行两条SQL语句产生

两条 SQL 语句分别持有了一把锁，但又分别想要获取另一把锁：

两条 SQL 语句产生的死锁

事务 1 和事务 2 在分别持有了一个锁的情况下试图去获取对方已经持有的锁，导致死锁。

每个事务执行单条SQL语句产生

两个事务锁不同的辅助索引，在自动锁主键索引过程冲突导致死锁

ALTER TABLE apply_item ADD INDEX `idx_update_time` (`update_time`);

假设现在整体的数据 idx_update_time 如下图所示，下面的单条 SQL 语句就可能导致死锁：

事务 1 按照索引 idx_apply_id 对主表进行更新，按照索引中的顺序，它会先对 id=2 的记录加锁，再对 id=4 的记录加锁；事务 2 按照 idx_update_time 中的顺序，先对 id=4 的记录加锁，再对 id=2 的记录加锁。 在这时，如果事务 1 和事务 2 分别持有了 id=2 和 id=4 记录的锁，就产生了情况一相同的循环等待，导致出现死锁。

实际开发过程中遇到的死锁

下面是我在实际开发中遇到的一次死锁对应的死锁日志（使用的隔离级别为可重复读）：

LATEST DETECTED DEADLOCK
------------------------
2023-06-14 12:56:27 0x7f4c7c37f700
*** (1) TRANSACTION:
TRANSACTION 84729416, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 55348210, OS thread handle 139967637870336, query id 1026402036 10.28.75.36 kms_db_iirt update
/*id:fc6be871*//*ip=xx.xx.xx.xx*/insert into apply_item (apply_id)
    values
      (1234
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 381 page no 4 n bits 136 index idx_apply_id of table `db`.`apply_item` trx id 84729416 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 84729418, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 55122082, OS thread handle 139966478284544, query id 1026402040 10.28.75.36 kms_db_iirt update (apply_id)
    values
      (1234
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 381 page no 4 n bits 136 index idx_apply_id of table `db`.`apply_item` trx id 84729418 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 381 page no 4 n bits 136 index idx_apply_id of table `db`.`table_name` trx id 84729418 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

该死锁发生时对应的事务定义如下：

BEGIN;
UPDATE apply_item SET valid=0 WHERE apply_id IN (?, ?, ?...);
INSERT INTO apply_item (`apply_id`) VALUES (?), (?)...;
COMMIT;

由于涉及批量插入，并且死锁发生时问题可能并不只有两个事务（死锁日志中只显示了两个日志，是 MySQL 为了解决死锁找到了导致死锁的最小的事务集合，不代表只有这两个事务被牵扯进了这个死锁），所以我们不用尝试去复现当时的场景，只需要发现问题所在。

该日志说明了以下问题：

1. 事务 1 在执行插入语句时遭遇死锁，此时它正在等待索引 idx_apply_id 的插入 IX 意向锁；
2. 事务 2 此时持有了索引 idx_apply_id 的行锁；
3. 事务 2 此时正在等待索引 idx_apply_id 的插入 IX 意向锁。

可以看到此时两个事务尝试获取 IX Lock 时都被阻塞了，而只有 S Lock 和 X Lock 会阻塞 IX Lock。我们明确对于这个表只通过上面这个事务来进行修改操作，因此只有第一步的 update 操作会在 idx_apply_id 加 X 锁。由于该死锁是在 idx_apply_id 上发生的，一种可能的解决方案是我们可以尝试尽可能地减少事务中对这个索引的竞争，将这个事务改成：

BEGIN;
SELECT id FROM apply_id WHERE apply_id IN (?, ?, ?...);
UPDATE apply_item SET valid=0 WHERE id IN (?, ?, ?);
INSERT INTO apply_item (`apply_id`) VALUES (?), (?)...;
COMMIT;

此时，update 语句只会对主表加锁，解决了 idx_apply_id 上的竞争问题。为了防止我们上面提到的按照主键 ID 更新的顺序不同而导致的死锁问题，我们可以在传入 update 语句之前将主键 ID 进行排序。

可重复读与幻读

美团面试官：MySQL可重复读如何解决幻读问题？ - 知乎 (zhihu.com)

 先了解快照读和当前读（见前）

快照读解决幻读

快照读情况下不会出现幻读

当前读解决幻读

当前读情况下如果是RR可以利用间隙锁防止幻读，如果是RC就不行了

可重复读未解决的幻读

第一种情况，先启动的事务A使用快照读，后启动的事务B插入新的数据行并提交，然后事务A再更新，其后A的查询都能查事务B新增的数据行。

 第二种情况，如果事务一开始没有使用当前读，当其他事务插入数据并提交后再使用当前读就会发生幻读现象。

mysql日志机制

undolog：

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中， 还用Undo Log来实现多版本并发控制(简称：MVCC)

▪ 在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方 称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之 前的状态

▪ 注意：undo log是逻辑日志，可以理解为： – 当delete一条记录时，undo log中会记录一条对应的insert记录 – 当insert一条记录时，undo log中会记录一条对应的delete记录 – 当update一条记录时，它记录一条对应相反的update记录。

binlog：MySQL 在 Server 层的逻辑日志。

Binlog中会记录所有的逻辑，并且采用追加写的方式 ▪ 一般在企业中数据库会有备份系统，可以定期执行备份，备份的 周期可以自己设置 ▪ 恢复数据的过程：

1、找到最近一次的全量备份数据

2、从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时

与redo日志的区别:

– 1、redo是innodb独有的，binlog是所有引擎都可以使用的

– 2、redo是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻 辑日志，记录的是这个语句的原始逻辑

– 3、redo是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前 的日志信息。

redolog：MySQL 在 InndoDB 引擎下的 Engine 层物理日志。

MySQL 的两阶段提交 + WAL技术（Write-Ahead Logging，先写日志再写盘）保证了数据不会丢失。

WAL的优势在于：

1. redo log & binlog 都是按顺序写入磁盘的，比随机写磁盘速度快。
2. 组提交机制，合并fsync。大幅度降低磁盘的IOPS消耗，提高IO性能。

binlog写入流程

bin cache

每个线程都有自己的bin cache。binlog cache 分配了一片内存，大小为 binlog_cache_size，如

果超过这个大小，就会把cache暂存到磁盘的临时文件（tmp files）中。

write

把日志写入文件系统内核的 page cache，page cache是 OS 对磁盘IO的缓存。

适合小文件传输。因为大文件传输 page cache 命中率低，这时不仅没有起到缓存作用，反而增加

一次数据从磁盘 buffer 到 内核 page cache 的开销。

fsync：

将数据持久化到磁盘

fsync 才占磁盘的 IOPS（Input/Output Operations Per Second）。

MySQL 有一个参数：sync_binlog ，用来控制 write 和 fsync 的时机。
可以根据业务场景的需要，来具体调整。

sync_binlog	含义
等于 0	每次事务提交只write，不fsync。（不推荐）
等于 1	每次事务提交不仅write，都会执行 fsync。（这个配置是最安全的，不会丢binlog日志）
等于 N	每次提交事务都write，累积N个事务后，一起fsync。（性能好，但是异常重启会丢N个事务的binlog日志）

redolog写入流程

首先，写入 redo log buffer 。

其次，写入（write）文件系统的 page cache。

最后，持久化（fsync）到磁盘 disk

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数来控制 redo log 写入流程。

innodb_flush_log_at_trx_commit	含义*
= 0	每次事务提交时，redo log 只会留在 redo log buffer。（风险大，等待每秒 write + fsync 到 disk）
= 1	每次事务提交时，都将所有 redo log fsync到磁盘。（最安全）
= 2	每次事务提交时，都将 redo log write 到 page cache。

每秒刷盘机制

InnoDB 有一个后台线程，每隔1秒，就会把 redo log buffer中的日志，调用 write 写到 page cache，然后 fsync 持久化到磁盘。

需要注意的是，事务执行中的 redo log 也是存在于 redo log buffer 的，也会被一起持久化到磁盘。（也就是说，一个还没有提交事务的 redo log，也可能已经被持久化到磁盘了）

强制刷盘

其实，不光每秒刷盘会提前持久化 redo log 到磁盘。

• 当 redo log buffer 到达 innodb_log_buffer_size （缓冲池大小，默认是8MB）一半的时候，会主动触发 write 到文件系统的 page cache。
• 并行事务提交，顺带将其他事务的 redo log buffer 持久化到磁盘。
  举例：
  事务A 执行到一半，写入了部分 redo log 到 buffer 中。
  事务B 完成，进行提交。
  如果innodb_flush_log_at_trx_commit设置为1，代表每次提交都会全部fsync到磁盘。这时候，事务A的 redo log 也有部分已经持久化了。

每秒刷盘和强制刷盘机制使redolog和binlog错峰降低磁盘IO，提高性能

双"1"配置最安全

整体写入流程

 

为什么要交叉执行：

先写redo log后写binlog

假设在redo log写完，binlog还没有写完的时候，MySQL进程 异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复 回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面 就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。 然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这 个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。 

先写binlog后写redo log

如果在binlog写完之后crash，由于redo log还没写，崩溃恢复 以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个 日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值 就是1，与原库的值不同

如何保证数据安全

如果 MySQL 挂在了 1，2，3 阶段。

这时候，不论redo log 还是 binlog 都还没有 fsync 到磁盘。
因此，掉电导致内存丢失，实际也没写入磁盘，数据一致性。

1. 如果 MySQL 挂在了第 4 阶段 fsync binlog。
   即 redo log fsync 成功， binlog fsync 失败。
   MySQL重启后，发现有 redo log 的磁盘数据，没有binlog磁盘数据。
   发现是redo log处于prepare阶段，回滚（删除磁盘里的redo log）。

2. 如果 MySQL 挂在了第 5 阶段 commit。
   即 redo log 和 binlog 都 fsync 成功，但 commit 失败。
   MySQL重启后，发现有完整的binlog和redo log，继续 commit。
   数据成功写入MySQL。

MySQL 基础技术（三）—— MySQL 如何保证数据不丢失？_mysql如何保证数据不丢失_java晴天过后的博客-CSDN博客

MySQL通过索引优化-这里可能有你不知道的索引优化细节（二）-腾讯云开发者社区-腾讯云 (tencent.com)


 

mysql分布式事务方案 mysql 分布式事务_mob6454cc6a469b的技术博客_51CTO博客