heyke
Mysql原理

MySQL知识点梳理

Mysql

Mysql的定义:

一.mysql是什么？

mysql是RDBM数据库（关系型数据库），应用于OLTP场景（联机事务处理——适用日常事务处理）

1.存储引擎：

InnoDB引擎：提供ACID四大事务特性，提供行锁和外键约束，目的是处理大容量的数据库系统， MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎，具有crash-safe （崩溃恢复）

存储形式：Frm表文件（mysql8.0之后就没了）、Idb数据和索引存储文件

MyISAM引擎：mysql原本默认引擎，不支持事务特性，也没有行级锁和外键，最小锁粒度为表锁

存储形式：.frm表文件、.MYD数据文件（MYData）、.MYI索引文件（MYIndex)

Memory引擎：所有数据都在内存中，数据处理快，但是不安全

BlackHole存储引擎：BDB支持事务，而且支持mvcc的行级锁，主要用于日志记录或同步归档，这个存储引擎除非有特别目的，否则不适合使用
MyISAM innoDB Memory
事务 不支持 支持 不支持
外键 不支持 支持 不支持
锁 表锁
(加锁速度快，但是不适合并发写）		 表锁/行锁
(加锁速度慢，但是适合并发写）		 表锁
缓存 缓存索引 缓存索引和数据（保证数据安全）
全文索引 支持 不支持
索引实现 非聚簇索引 聚簇索引

一般需要事务就使用InnoDB引擎

//查看当前数据库的默认引擎
show VARIABLES like 'default_storage_engine'

2.mysql数据库的三范式：

消除冗余也是范式的要求，解决部分依赖和传递依赖，本质就是尽可能减少冗余数据。

3.mysql语言定义类型

DML：数据操作语言，包括（select,insert,update,delete)

DDL：数据定义语言，包括（Create，alter，drop,rename,truncate,comment)

DCL：数据控制语言，包括（grant,revoke)

二.事务是什么？

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

#事务的创建
/*
隐式的事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id=1;
显式事务：事物具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;
*/
#演示事务的使用步骤

#开启事务
SET autocommit=0;
START(begin) TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';
#结束事务
COMMIT;
SELECT * FROM account;

//查询全局/会话事务隔离机制
show global/session variables like '%isolation%';
//设置全局/会话事务隔离机制
set global/session transaction isolation level read committed;

1.ACID四大事务特性：

A（Atomicity） 原子性：操作成功or失败

C（Consistency）一致性：数据执行前后要保持一致性

I（Isolation） 隔离性：事物之间隔离，互不影响

D（Durability) 持久性：数据持久化，不会丢失

2.并发事务出现的问题

1.脏读：一个事务读取到了另一个事务还未提交的数据

2.脏写：两个事务同时修改一个数据，事务一回滚，数据变为null，则另一个事务修改的数据也没了

3.不可重复读：一个事务先后执行读取一个数据的操作，前后读取到的内容不一致（ 解决问题）

4.幻读：一个事务查询数据时，未查到相应数据，进行插入数据时又发现数据已经存在了（前提：已经解决了不可重复读（RR隔离级别）

解决幻读问题：

MySQL只解决了快照读语义下的幻读

RR隔离级别当前读语义下需要加上(next-key lock)临键锁 select …… where for update（delete），直接不允许修改，间隙锁之间不互斥，但是间隙锁和插入语句互斥，会发生死锁

在RC隔离级别下，临键锁会失效，用分布式锁，保证先读再写并发问题

4.事务隔离级别：
隔离级别（√代表问题存在） 脏读 不可重复读 幻读 MVCC
Read uncommitted 未提交读 √ √ √
Read committed 读已提交 × √ √ 每次生成一个readview
Repeatable Read(默认) 可重复读 × × √ 只生成一次readview
Serializable 串行化
（一个事务完成后才执行下一个事务）		 × × ×

三.日志体系

缓冲池（buffer pool）：

主内存中的一个区域，用来缓存磁盘上的真实数据，执行增删改查操作时先操作缓冲池中的数据，如果缓冲池中没有就从磁盘中加载并缓存，操作完成后再刷新到磁盘中，减少了磁盘IO；

用来缓存写操作的内存，就是Change Buffer

Change Buffer默认占Buffer Pool的25%，最大设置占用50%：

buffer pool中除了缓存数据页，还有索引页和undo页、插入缓存、自适应哈希索引、锁信息等；

数据页（page)：InnoDB存储引擎的最小单元，每页的默认大小为16kb，页内存储的是行数据

WAL(Write Ahead Log)预写日志，mysql的写操作不是立刻写到磁盘上，而是先写日志，在合适的时间再写到磁盘上，是顺序写，因为不用考虑位置问题，速度比随机写快，用来保证原子性和持久性，缓存是异步提交的

1.redo log

重做日志（前滚日志；物理日志），记录事务提交时数据页和undo页的物理修改，用来保证事物的持久性

日志文件分为：重做日志缓冲（redolog buffer）默认大小16MB（可以使用innodb_log_Buffer_size 参数动态的调整大小）

和重做日志文件（redolog file），在发生错误时，可以用于数据恢复

1.1 Redo log的刷盘时机：

  • Mysql关闭时

  • Redo log buffer的写入量大于redolog buffer内存空间的一半时，会触发落盘

  • InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘

  • 事务提交时

1.2 redo log文件写满了怎么办？

Innodb引擎有1个重做日志文件组（redo log Group），包含两个redolog文件，重做日志文件组以循环写的方式工作。

Innodb用write pos指向redolog准备写入位置，checkpoint表示要擦除的位置

当write pos，追上了checkpoint，表示redo log文件写满了，这时候就不能再执行新的更新操作了，mysql会阻塞，需要停下来将Buffer pool中的脏页数据刷新到磁盘，刷新成功那么redolog的旧数据就没有用了，进行擦除，擦除后checkpoint会向后移，腾出空间插入新数据

一次checkpoint过程就是，刷新数据脏页，标记redolog可以覆盖的记录

2.undo log

回滚日志： 用于记录数据被修改前的信息，作用包含提供回滚和MVCC（多版本并发控制），是逻辑日志

例如：当执行delete语句时，undo log会记录一条insert记录，反之亦然，如果是update，就会记录修改前的数据

1.回滚日志：存储老版本数据

2.版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer（回滚指针）形成一个链表

Undo log 保证了事物的一致性和原子性

3.MVCC

Multi-Version Concurrency Control（多版本并发控制），主要是为了提高数据库的并发性能，指维护一个多个版本，使读写操作没有冲突，是快照读的实现

实现通过隐式字段、undo log日志、ReadView，实现了mysql的隔离性（为了解决不可重复读问题）

3.1.隐式字段：

  • DB_TRX_ID：最近修改的事务ID，记录插入这条记录或最后一次修改该记录的事务ID

  • DB_ROLL_PTR：回滚指针，记录这条记录的上一个版本的地址，配合undo log，指向上一个版本

  • DB_ROW_ID：隐藏主键，表中有主键时隐藏该字段，没有时自动生成

3.2.ReadView（读视图）：

是快照读Sql执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交）id

  • 当前读：每次读取的都是当前最新的数据，但是读的时候不允许写，写的时候也不允许读，就是给数据加了一把锁。（悲观锁）

    • Select lock in share mode (共享锁）

    • Slelect for update (排他锁）

    • Update 、insert、delete（排他锁）

  • 快照读：读写不冲突，每次读取的是快照数据，

四个核心字段

  • m_ids ： 当前活跃的事务ID集合

  • min_trx_id：最小活跃事务ID

  • max_trx_id：预分配事务ID，当前最大事务ID+1(事务ID是自增的）

  • creator_trx_id：ReadView创建者的事务ID

规则：

1.当前事务ID<最小事务ID，表示事务已经提交，可以读取

2.当前事务ID>最大事务ID，说明是在ReadView生成后才开启的事务，一般读取不到，根据事务隔离级别而定

readview在不同的隔离级别生成时机不一样

在Read Committed 中，每次执行事务是都生成一个readview，保证每次读取的都是最新的数据

在Reaptable Read 中，只在第一次执行事务时生成readview，有可能读取到的数据不是最新的

3.当前事务肯定读

4.当前事务在最小和最大ID之间，就要看是否在活跃事务ID集合中，在的话就不读取

4.bin log

主从复制的核心就是二进制日志（binlog)，默认情况下可以成为逻辑日志，以追加写的形式写入，写满一个文件就创建一个新文件，保存全量的日志用于整个数据库的恢复，由Server层实现，所有引擎都可以使用，主要用于备份恢复，主从复制，有三种形式

  • statement（默认格式）：

    • 每一条修改数据的 SQL 都会被记录到 binlog 中

    • 问题：记录动态函数问题，会导致主从库不一致

  • row（记录行）：

    • 记录行数据最终被修改成什么样了

    • 问题：记录的数据太长了，导致bin log文件过大

  • mixed：

    • 包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和STATEMENT 模式；

二进制日志（binlog)记录了所有的DDL和DML语句，不包括查询语句

在事务提交时，执行器将binlog cache 完整的写入binlog文件（在文件系统的page cache），并清空binlog cache

4.1主从复制操作：

1.Master主库在事务提交时，把数据变更信息记录在二进制日志（binlog）中

2.slave从库读取主库的二进制日志文件（binlog），写入到从库的中继日志Relay log

3.slave从库重做中继日志中的事件，将改变反映到它的数据

一般一个主库跟2~3个从库，因为从库数量的增加，I/O线程会增多，log dump也会增多，消耗主库的资源，还要受带宽的影响

5.两阶段提交

两阶段提交其实是分布式事务一致性协议，可以保证多个逻辑操作的一致性

事务提交后，redolog和binlog都要持久化到磁盘中，但是是两个独立的逻辑，可能会出现半成功的状态，就会导致两份日志之间的逻辑不一致，会出现主从库数据不一致的现象

两阶段提交的第一阶段 （prepare阶段）：将XID（XA事务）写入redo log，写rodo-log 并将其标记为prepare状态，然后将redo log持久化到磁盘（innodb_flush_log_at_trx_commit =1)

两阶段提交的第二阶段（commit阶段）：将XID（XA事务）写入binlog,然后将binlog持久化到磁盘(sync_binlog=1的作用),接着将redolog标记为commit状态，再将redo log写入文件系统的page cache中

不论哪个时刻出现问题Mysql都会顺序扫描redo log文件，此时redo log都处于prepare状态，然后去查看binlog是否存在此XID。

如果在时刻A出现意外：

比对binlog的XID不存在，则说明binlog没刷盘，回滚事务即可

如果在时刻B出现意外：

比对binlog的XID存在，则说明redolog和binlog已经刷盘，则提交事务

目的是在出现意外时（mysql宕机了），可以根据redo log日志（准备阶段或commit阶段）来恢复bufferpool或写入磁盘，保证了原子性和一致性，保证主从库数据一致

两阶段提交也会产生磁盘I/O次数高，锁竞争激烈现象，通过组提交来解决

四.索引是什么？

优点：索引是一种提高查询效率的数据结构，提高检索效率降低IO成本（不需要全表扫描）,降低cpu数据排序成本

缺点：但是索引也占用磁盘空间，附加在数据库表的字段上，利用了一种空间换时间的概念，但是索引也不能创建过多，占用空间会过大，提高了读的效率，但是写的操作时会变得困难，而且查询前会对索引的成本进行计算，过多的索引计算会消耗性能。

创建原则

1. 数据量较大，且查询比较频繁的表

2. 常作为查询条件、排序、分组的字段

3. 字段内容区分度高（唯一的列）

4. 内容较长，使用前缀索引

5. 尽量联合索引

6. 要控制索引的数量

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

1.索引的数据结构：

1.1 B+树索引：

矮胖，树的高度能够大大降低，磁盘读写代价更低，查询效率更稳定

能很好的支持单点查询，范围查询，有序性查询（运用了二分法）叶子节点是双向链表；

为什么一般B+树不超过3层？

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1000个键值，注意指的的非叶子节点。

我们再看叶子节点，这里假设一行记录的数据大小为 1KB（页大小是16KB），那么高度为3的B+Tree可以存放 1000 * 1000 * 16 = 16000000条记录。

可以看出一个高度为3的B+tree 就能存千万条记录， 所以B+Tree最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

1.2. hash索引：

（一般不使用，适用于单行查询，效率比B+树高）

2.索引的分类：

2.1.普通索引（二级索引）

普通索引（index）是最基本的索引，没有任何限制，索引列的长度最大为255字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节）

//方法一：创建普通索引
create index name on table_name (column(length));
//方法二：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name (column(length));
//方法三：创建表结构时就创建索引
create table table_name (
id int(3),
index id_index(id
);

2.2.唯一索引

唯一索引（unique）索引列的值必须唯一，允许有空值

//方法一：创建普通索引
create unique index unique_name on table_name (column(length));
//方法二：修改表结构的方式添加索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column(length));
//方法三：创建表结构时就创建索引
create table table_name (
id int(3),
unique index id_index(id
);

2.3.主键索引

主键索引（primary key）是特殊的唯一索引，一个表只能有一个主键，且不能为空，一个表必须有主键，如果不设置，系统就会默认新增一个自增列；

//第一种：在字段外创建
create table info2 (
id int(4) not null,
name varchar(10) not null,
address varchar(50) default '未知',
primary key(id)
);

//第二种：在字段内创建
create table info2 (
id int(4) not null primary key,
name varchar(10) not null,
address varchar(50) default '未知'
);

2.4.组合索引（联合索引）

为了解决复杂的sql场景，分为单列索引和多列索引。

（a,b,c)联合索引

select…… where a = ？ and b = ? and c=?

select…… where b = ？ and a = ?

存在最左匹配原则：联合索引是按照最左边的字段进行排序的，因为有查询优化器，所以查询时最左侧字段的顺序不重要，其他字段全局无序、局部有序，如果查询时不包含最左侧字段则不使用联合索引。

2.5.全文索引

适用于较大的数据集，一般在数据插入后再生成全文索引

//创建全文索引
alter table info2 add fulltext index addr_index(address);  
//查看索引的两种方法
show index from tablename\G;
show keys from tablename; 
//删除索引的两种方式
DROP INDEX 索引名 ON 表名; 
ALTER TABLE 表名 DROP INDEX 索引名;

2.6.聚簇索引

1.当你为一张表创建主键时，也就是定义 PRIMARY KEY 时，此时这张表的聚簇索引就是主键索引。通常情况下，我们应该为一张表设置一个主键，如果没有合适的列作为主键列，我们可以定义一个自动递增的唯一列为主键，并且在插入数据时是自动填充此列。

2. 然而，如果一张表中没有设置主键，那么 InnoDB 会使用第一个唯一索引（unique），且此唯一索引设置了非空约束（not null），我们就使用它作为聚簇索引。

3. 如果一张表既没有主键索引，又没有符合条件的唯一索引，那么 InnoDB 会生成一个名为 GEN_CLUST_INDEX 的隐藏聚簇索引，这个隐藏的索引为 6 字节的长整数类型。

回表

经过一次sql查询不能查询到完整数据，还需要通过主键进行二次查询的现象叫做回表

优化技术

1.索引覆盖

如果一个索引覆盖了要查询的所有字段，就叫做覆盖索引通过一条索引就可以查出完整的数据叫做索引覆盖，避免了回表现象

2.索引下推

在mysql5.6之后才优化出来的功能

即便没按原有设计的索引去走，但是到了存储引擎依然走索引查找，减少一次回表的操作

例:(age,address)联合索引，

select * from tables where age >19 and address = "上海"；

当sql走到age >19此时联合索引失效,

正常情况就会查到age>19的，然后回表通过主键索引查找完整数据返回到server层，由server层判断address是否等于”上海“，等于就发送给客户端，否则跳过，一直重复次操作直到查询全表

但是索引下推可以在索引失效后，继续判断address是否等于上海，如果等于，则回表查询将完整的数据返回给客户端。

3.索引跳跃

Mysql 8.0.13开始支持的index skip scan 也即索引跳跃扫描(跳级查询)，在不符合最左匹配时，依然能使用联合索引进行查询

4.索引合并

mysql5.1之后推出的，可以一次性合并多个索引进行数据扫描，对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。

合并方式分为三种：

union, intersection, 以及它们的组合sort_union(先内部intersect然后在外部union)

5.索引失效

  • 违反最左前缀法则（因为其余字段全局无序，局部有序）

  • 范围查询右边的列，不能使用索引（因为范围查找后，右边的列是全局无序的，他的头结点也是无序的，需要全盘扫描）

  • 不要在索引列上进行运算操作， 索引将失效

  • 字符串不加单引号，造成索引失效。(类型转换)

  • 以%开头的Like模糊查询，索引失效，%放在右侧就可以走索引（因为%在开头代表以1结尾的数据，不走索引）可以使用索引覆盖解决

五.mysql调优

在生产过程中可能出现页面加载过慢的现象，要排除问题首先要定位问题，如果是sql的问题，则需要对sql进行一些优化来解决问题

mysql查询的种类：

https://my.feishu.cn/sync/FuFZdcDo4sR5Lxbf8qMc3yumnch

  • 深度分页查询

1.定位慢查询：

1.1：可以使用开源的工具，例Skywalking

1.2：Mysql自带的慢日志，需要设置两个参数

//开启慢日志查询的功能，1开启，0关闭
slow_query_log = 1
//设置慢查询日志的时间为2秒(默认是10秒)，如果sql时间超过2秒就会被视为慢查询，记录为慢查询日志
long_query_time = 2

2.慢查询优化分析

可以通过explain/desc获取mysql的查询情况

EXPLAIN/DESC select * from `stock_block_rt_info` where id = '1850452111159074816';

查询信息中：
字段 possible_key key key_len type Extra
字段的意义 可能会使用的索引 当前sql命中的索引 索引占用的大小 连接类型 额外的优化建议
字段的作用 可以查看到sql是否有使用索引 判断sql的连接类型 提供其他的优化提示
具体内容 进而判断是否有索引失效的情况 NULL：没有使用表

system：查询系统中的表

const：根据主键查询

eq_ref：主键索引或唯一索引查询

ref：其他索引查询

range：范围查询

index：索引树扫描

all：全盘扫描（需要优化）		 Using where：使用where过滤

Using index：使用了覆盖索引，避免了回表回表

Using index condition：使用了索引，再回表，然后where过滤 （使用了索引下推）

3.show processlist

一般用到 show processlist 或 show full processlist 都是为了查看当前 mysql 是否有压力，都在跑什么语句，当前语句耗时多久了，有没有什么慢 SQL 正在执行之类的，可以看到总共有多少链接数，哪些线程有问题(time是执行秒数，时间长的就应该多注意了)，然后可以把有问题的线程 kill 掉，这样可以临时解决一些突发性的问题kill掉有问题的线程

//查看出有问题的线程号
Show processlist
//杀掉有问题的线程
kill 线程号

4.优化方案

1.建立索引，加快查询效率,语句优化，避免使用select *（会造成回表）

2.存储优化

  • 禁用索引：innodb在insert时会重新建立索引，所以在对数据库进行大量修改时可以先禁用索引

  • 禁用唯一性检查：唯一性校验会影响插入速度

  • 禁用外键检查、关闭自动提交事务

3.数据库优化

  • 尽量设置表结构为not NULL

  • 表结构中只有几个特殊字段的可以改为enum、set类型

  • 单表不要设置太多字段

4.表拆分

  • 数据量过大时可以做分库分表

  • 可以主从复制，读写分离

  • 可以搭建数据库集群

5.优化硬件

六.锁

一.锁的分类：

1.按模式分类：

乐观锁和悲观锁是相对而言的。

  • 乐观锁：

    • 乐观锁假设数据一般情况下不会发生冲突，索引在数据更新时 才去检测数据的冲突，如果冲突了就返回错误信息，让用户决定怎么去做

    • 应用于读多写少的场景，如果出现大量的写操作，写冲突的可能性会增大，业务层不断重试，降低了系统的性能。

    • 实现：通过在表中添加version字段，每次更新操作比对version的值是否一致，如果一致则更新成功，version值自增1；

  • 悲观锁：

    • 悲观锁具有强力的独占和排他性，每次取数据时都认为会被修改，因此在整个数据处理过程中，将数据处于锁定状态。

    • 适用于并发量不大、写入操作比较频繁、数据一致性比较高的场景。

    • 实现：在本次事务进行查询时通过悲观锁锁定行，其他事务要对这行数据进行操作的话要等本次事务提交之后才能执行

2.按锁粒度分类：

  • 全局锁：

    • _Flush tables with read lock；_使整个库处于只读状态，其他所有DDL和DML的线程都会堵塞，会导致业务停滞；

    • 解决方法：Innodb可以通过在RR隔离级别下，mysqldump（全库逻辑备份）使用参数--single-transcation，保证读写正常

  • 表级锁：

    • 表锁：

      • 执行Lock tables t1 read ； 则其他线程写t1都会阻塞，本线程也只能执行读t1操作，知道_unlock tables；_ 释放表锁（一般不使用表级锁，并发性能太低）

    • 元数据锁（MDL）：

      • 在mysql5.5新加入的，只要对进行数据库操作就会自动加上MDL，CRUD操作加的时MDL读锁，改变数据表结构加写锁

      • 在事务提交后才会释放锁，如果有个未提交的长事务执行了select，则MDL读锁一直存在，（读读）锁不冲突，如果出现了一个MDL写锁，则会发生阻塞，且锁操作是一个队列，后续的操作不论读写都会阻塞

      • 解决方法：在修改表结构前查看有无未提交长事务加上MDL读锁，有的话可以先kill该长事务，再对表结构进行改变；

  • 行级锁：

    • 行锁是锁粒度最低的锁，发生锁冲突的概率也最低、并发性最高，但是加锁慢、开销大，容易发生死锁

    • 行级锁锁的是索引，如果sql操作了主键索引，则mysql锁住主键索引，若sql执行了非主键索引，则mysql先锁定该非主键索引，再锁住相关的主键索引。

  • 页级锁：

    • 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁

3.按属性分类：

  • 共享锁：

    • 共享锁（读锁 ；S锁），数据添加了共享锁之后不能对再添加其他锁，只能添加共享锁，不能进行修改操作，也就是不能添加写锁，直到读锁释放

    • 场景：为了支持并发的读取数据而出现的，为了解决不可重复读问题

    • 实现：_select …lock in share mode；_两张关联表，先锁住一张表的相关数据，再对另一张表的数据进行插入或其他修改

  • 排它锁：

    • 排它锁（写锁；X锁）数据添加上写锁之后不可以再添加任何锁了，写锁与其他锁都互斥，只有当前写锁被释放，才能对数据进行读写；

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

    • 场景：解决修改数据时，不允许其他事务对数据进行读写操作，避免了脏读问题；

    • 实现：select …for update；

  • AUTO-INC 锁：

    • 自增锁，实现了不指定主键时，数据库自动自增主键，而且执行完语句后锁会立即释放

    • 实现：在插入数据时，先加一个表级别的AUTO-INC锁，然后为被AUTO_INCREMENT修饰的字段赋递增的值，等插入sql完成时，立刻释放锁

    • 大量插入数据时会阻塞其他的插入数据，所以5.1.22升级了轻量级的锁来实现自增，通过innodb_autoinc_lock_mode = {0(自增锁）,2（轻量级锁）

      innodb_autoinc_lock_mode =1 时：

      • 普通插入，自增锁会在申请后立刻释放

      • 批量插入，还要等语句结束后才释放锁

4.按状态分类：

  • 意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度(表锁与行锁)的锁并存

  • 作用：当事务A有行锁时，Mysql会自动为表添加意向锁，如果其他想申请全表的写锁，只需要判断是否存在意向锁就可以，方便其他事务判断表中是否存在行锁

  • 意向锁不会与行级的共享 / 排它锁互斥

    • 意向共享锁

    • 意向排它锁

5.按算法分类：

  • 记录锁：

    • 记录锁是封锁记录，记录锁也叫行锁

    • 例：select *from goods where **’ id ’ =**1 for update;

    它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

  • 间隙锁（gap lock）：

    • 间隙锁时分唯一索引，它锁定一段索引记录区间，使得不能向这个区间插入数据

    • 例：select* from goods where id between 1 and 10 for update;

    即所有在（1，10）前开后开的区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1和 10 两条记录行并不会被锁住。

  • 临键锁（next-key lock）：

    • 是记录锁和间隙锁的结合，封锁的范围是左开右闭区间包括这条记录和他的间隙区间，是为了解决幻读问题。临键锁是间隙锁和记录锁组合使用的，如果不需要组合使用的话，临键锁就会退化

    • 例：临键锁锁的id范围是（1，9],则id为2~9的都不可以删除和修改

//该命令可以查询执行sql时加了什么锁
select * from performance_schema.data_locks\G;

二.死锁

1.什么是死锁？

两个事务同时插入，插入之前做幂等校验，避免幻读现象，则此时两个事务都进入了等待状态，等待对方释放锁；也就是多个进程争夺资源导致的僵局

死锁的场景：

1.两个不同的where查询顺序导致死锁

业务是投资人随机将投资的金额分给借款人，再通过select for update持续更新借款人的金额，此时AB投资人一起投资，投资人A将钱随机分给了借款人1，2；投资人B随机的将钱分给了2，1；

由于加锁的顺序不一样，此时A事务在等待B事务给2加的锁释放，B上午在等待A事务给1假的锁释放，就出现了等待资源死锁现象

解决：直接将要操作的资源列表一次性锁死，select where id in(...) for update;

在里面的列表值mysq是会自动从小到大排序，加锁也是一条条从小到大加的锁

2.索引合并（index_merge)

本质上就是update xxx where age=xx and status=x这种sql可能会触发mysql的索引合并，多

个事务多个where条件和普通索引的情况，先对普通索引上锁再对主键索引上锁，这里面的数据可能发生重叠和顺序错乱造成死锁。

解决：

一、从代码层面

1.where 查询条件中，只使用uk_accept_id ，将数据查询出来后，在代码层面判断 status 状态是否为1；

2.使用 force index(uk_accept_id) 强制查询语句使用uk_accept_id 索引；

3.where 查询条件后面直接用 id 字段，通过主键去更新(推荐)。

二、从MySQL层面

1.删除 idx_status 索引或者建一个包含这俩列的联合索引；

2.将MySQL优化器的index merge优化关闭。

2.死锁产生的条件

1.互斥：进程之间得到的资源进行排他控制，且一段时间只能为该进程所有

2.请求和保持：进程因请求资源而阻塞时，对以获得的资源保持不放

3.不剥夺条件：进程已获得的资源在未使用完之前，不能剥夺，只能在使用完时由自己释放。

4.环路等待条件：在发生死锁时，必然存在一个进程--资源的环形链。

3.解决死锁

1.检测死锁：利用jstack工具、jConsole查询

2.避免死锁：使用资源有序分配法，破坏环路等待条件

七.sql的执行流程

1.select语句的执行顺序流程：

FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> UNION -> ORDER BY ->LIMIT

1.1.select执行的流程

Mysql的架构：

Server层：负责建立连接、分析、优化、执行sql

连接器：先要与MySQL服务建立连接，运用了TCP的三次握手协议，断开连接就是TCP的四次挥手

查询缓存：连接成功后，如果是select语句则先从缓存中查找缓存数据

解析器：会对sql语句进行词法和语法分析

预处理器：判断表或字段是否存在

优化器：确定sql的执行方案，有多个索引时，优化器会考虑成本问题来决定选择哪个索引

执行器：正式执行sql，执行器与存储引擎交互

存储引擎层：负责进行数据的存储和提取

2.update语句执行顺序流程

FROM -> WHERE -> SET -> UPDATE

2.1.update执行的流程

基础的同select

例：UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

执行器：

1.负责具体执行，调用引擎接口通过主键索引查找判断id=1的数据是否在buffer pool缓存中，如果不在就从磁盘中读取到缓存中，再返回记录给执行器

2.执行器接收记录后，比对更新前后数据是否一致

  • 如果一致就不执行更新操作

  • 不一致就把更新前后的数据都传给Innodb层，让Innodb执行真正的更新操作

开启事务：

1.Innodb更新前需要先记录旧值在undo log中，undo log在写入buffer pool中的undo页面，undo页面修改了也要记录在redo log中

2.开始更新，会先更新内存中的数据，并记录为脏页，然后将脏页写入redo log，此时更新完成，为了减少磁盘I/O，不会立即将脏页写入磁盘，这就是WAL技术。

3.记录语句到对应的bin cache中，并不会直接刷新到硬盘上的binlog文件中，在事务提交后才会统一刷新到硬盘上。

八.分库分表

1.类型

  • 垂直分库：

    • 经过垂直分表后表的查询性能确实得到了一定程度的提升，但数据始终限制在同一台机器内，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘；单台服务器的性能瓶颈通过垂直分表始终得不到突破；

    • 垂直分库是指按照业务将表进行归类，然后把不同类的表分布到不同的数据库上面，而每个库又可以放在不同的服务器上，它的核心理念是-专库专用；

  • 水平分库

    • 水平分库可以看做是水平分表的进一步拆分，是把同一个表的数据按一定规则拆到不同的数据库中，每个库又可以部署到不同的服务器上；

    • 水平分库解决了单库数据量大的问题，突破了服务器物理存储的瓶颈；

  • 垂直分表

    • 垂直分表就是在同一数据库内将一张表按照指定字段分成若干表，每张表仅存储其中一部分字段；

    • 垂直分表拆解了原有的表结构，拆分的表之间一般是一对一的关系；

  • 水平分表

    • 水平分表就是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中，表的结构没有变化；水平分表解决单表数据量大的问题；

    • 优化单一表数据量过大而产生的性能问题;

    • 避免IO争抢并减少锁表的几率;

2.分库工具：

1.ShardingSphere——Sharding-JDBC

2.TDDL

3.Mycat

3.分库分表后的主键唯一性问题？

1.雪花算法：由1bit符号位，41bit时间戳，5bit机器id，5bit服务（机房）id，12bit序列号

一个雪花算法可以在同一毫秒内最多可以生成1024 X 4096 = 4194304个唯一的ID

分库分表的问题：

1.读写操作都需要带着分表字段，这样才能知道具体去哪个库、哪张表中去查询数据。如果不带的话，就得支持全表扫描。

2.不能跨多表进行分页、排序

面试问题：

[模拟面试] 赵家宝 mysql 11.22

1.深分页问题

1.背景和影响

深分页查询时，LIMIT语句的offset值很大，这意味着要扫描大量的索引节点和行数据

1.索引扫描开销：mysql需要扫描更多的索引节点来定位offset对应的行

2.回表操作开销：对于非聚簇索引，每次找到满足条件的索引记录都需要执行一次回表操作，这在大offset值时尤其昂贵。

3.结果集构建开销：即使已经找到了所需的数据，MySQL 仍然需要处理和丢弃之前的offset行

4.长事务和锁竞争：查询的时间很长，增加了锁竞争的可能性

5.死锁风险：锁竞争的可能性增大，死锁的风险就会增多

2.底层原理和优化策略

1.子查询优化策略

子查询优化策略的核心思想是减少回表操作。通过在子查询中找到满足条件的起始ID，然后在主查询中直接从该ID开始检索数据。

底层原理：

  • 子查询在二级索引上执行，快速定位到满足条件的起始点。

  • 主查询使用该起始点在主键索引上直接检索数据，避免了从二级索引到主键索引的多次回表。

2.INNER JOIN 延迟关联策略

延迟关联策略通过先获取满足条件的ID集合，然后与原表进行JOIN操作来获取完整数据。

底层原理：

  • 通过在二级索引上快速找到满足条件的ID集合。

  • 使用INNER JOIN在主键索引上检索这些ID对应的数据，减少了回表次数。

3.使用BETWEEN…AND…策略

使用BETWEEN…AND…来代替LIMIT，直接指定查询的范围。

底层原理：

  • BETWEEN…AND…允许 MySQL 直接定位到查询的起始和结束点。

  • 减少了扫描的行数，提高了查询效率。

4.标签记录法策略

标签记录法通过记录上一次查询的最后一个 ID，下次查询从该 ID 开始。

底层原理：

  • 利用有序索引的特性，从上一次查询的最后一个ID开始，避免从头扫描。

  • 适用于有连续或可排序的字段，如自增主键或时间戳。

2.Mysql多表关联查询优化，

1.小表驱动大表

先对数据量少的表做处理，再去大数据的表中进行筛选，可以减少数据的扫描范围，返回更少的数据，减少交互次数，减少cpu以及内存的开销

2.关联字段加索引

为需要排序的关联字段加上索引

3.防止索引失效

4.where子句过滤

当两个表进行Join操作时，建议在主表的分区限制条件位置使用WHERE子句。具体来说，可以先用子查询过滤数据，然后在主表的WHERE子句中写入这些条件

5.先单表查询，再关联

  • 让缓存的效率更高

  • 将查询分解，执行单个查询可以减少锁的竞争

  • 在应用层进行关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展

  • 查询本身的效率也会提升

  • 可以减少冗余记录的查询

为什么不建议执行超过3表以上的多表关联查询？

  1. 不超过3层是为了效率。

多表连接会产生笛卡尔积，查出的数据呈指数式增长

  1. 更通用 ，更好为了分布式做准备。

MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，简单点说就是要通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。

而具体到算法实现上主要有simple nested loop，block nested loop和index nested loop这三种。而且这三种的效率都没有特别高。

MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，如果有2张表join的话，复杂度最高是O(n2)，3张表则是O(n3)...随着表越多，表中的数据量越多，JOIN的效率会呈指数级下降。

不能用join如何做关联查询

1、在内存中自己做关联 即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联。

2、数据冗余 那就是把一些重要的数据在表中做冗余，这样就可以避免关联查询了。

3.创建索引时会不会加锁？

因为在 MySQL 5.6 之前，创建索引时会锁表，所以，在早期 MySQL 版本中一定要在线上慎用，因为创建索引时会导致其他会话阻塞（select 查询命令除外）。

但这个问题，在 MySQL 5.6.7 版本中得到了改变，因为在 MySQL 5.6.7 中引入了 Online DDL 技术（在线 DDL 技术），它允许在创建索引时，不阻塞其他会话（所有的 DML 操作都可以一起并发执行）。

什么是Online DDL？

Online DDL（Online Data Definition Language，在线数据定义语言）是指在数据库运行期间执行对表结构或其他数据库对象的更改操作，而不需要中断或阻塞其他正在进行的事务和查询。

4.分库分表分片算法：

https://blog.csdn.net/qq_42875345/article/details/132662916

雪花算法

雪花算法，它至少有如下4个优点：

1.系统环境ID不重复

能满足高并发分布式系统环境ID不重复，比如大家熟知的分布式场景下的数据库表的ID生成。

2.生成效率极高

在高并发，以及分布式环境下，除了生成不重复 id，每秒可生成百万个不重复 id，生成效率极高。

3.保证基本有序递增

基于时间戳，可以保证基本有序递增，很多业务场景都有这个需求。

4.不依赖第三方库

不依赖第三方的库，或者中间件，算法简单，在内存中进行。

雪花算法，有一个比较大的缺点：

依赖服务器时间，服务器时钟回拨时可能会生成重复 id。

雪花算法的常见问题和时钟回拨解决方案

https://www.cnblogs.com/shoshana-kong/p/17319231.html

posted on 2026-06-10 17:16  heyke  阅读(7)  评论(0)    收藏  举报

博客园  ©  2004-2026
浙公网安备 33010602011771号 浙ICP备2021040463号-3 Powered By博客园