深度解锁MySQL

最新推荐文章于 2024-06-22 15:26:37 发布
最新推荐文章于 2024-06-22 15:26:37 发布
阅读量145 收藏
点赞数
分类专栏： Mysql Oracle MongoDB数据库 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45044097/article/details/116999471
版权

文章目录

1.sql查询语句的执行

select * from T where ID=10；
MySQL 的基本架构示意图：

MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

  1. 连接器：负责跟客户端建立连接、获取权限、维持和管理连接
    连接命令：mysql -h$ip -P$port -u$user -p 然后输入密码。
  2. 查询缓存：MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。（建议不使用查询缓存，因为只要对一个表更新，这个表上所有的查询缓存都会被清空，除非是系统配置表，不经常更新的表可以做查询缓存）对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定：mysql> select SQL_CACHE * from T where ID=10；，MySQL 8.0 版本直接将查询缓存的整块功能删掉了。
  3. 分析器：“词法分析”，“语法分析”。
  4. 优化器：在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
  5. 执行器：执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
    比如例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：
    1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
    2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
    3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

2.sql更新语句的执行

mysql> create table T(ID int primary key, c int);
将 ID=2 这一行的值加 1：
mysql> update T set c=c+1 where ID=2;
连接器连接数据库 -> 更新时查询缓存失效 -> 分析器会分析更新语句 -> 优化器决定使用ID这个索引 -> 执行器负责执行

更新流程涉及两个重要日志模块：redo log（重做日志），binlog（归档日志）

2.1 redo log

WAL技术：Write-Ahead Logging，关键点就是先写日志，再写磁盘。
具体：一条记录需要更新，InnoDB引擎在适当的时候吧操作记录更新到磁盘（更新往往在系统比较空闲的时候）。
redo log时固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共可以记录4GB的操作，从头开始写，写到末尾就又回到开头循环写：

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

2.2 binlog

Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

两种日志的不同：

redo logbinlog
InnoDB引擎特有MySQL的Server层实现的，所有引擎都可以使用
物理日志，记录的是“在某个数据页上做了什么修改”逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1”
循环写的，空间固定会用完可以追加写入，“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

执行器和InnoDB引擎在执行这个简单的update语句时内部流程：
1.执行器先找到ID=2这一行，ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2.执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态，然后告知执行器执行完成了，随时可以提交事务。
4.执行器生成这个操作的binlog，并把binlog写入磁盘。
5.执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

update执行流程图，浅色框InnoDB内部执行，深色框表示再执行器中执行的。

“两阶段提交”：为了让两份日志之间的逻辑一致。

3.MySQL 事务支持

隔离性与隔离级别：
ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

隔离得越严实，效率就会越低，SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

  1. 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。
  2. 读提交：一个事务提交之后，它做的变更才会被其他事务看到。
  3. 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  4. 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为：
mysql> create table T(c int) engine=InnoDB;
insert into T( c ) values(1);

事务A事务B
启动事务，查询得到值1启动事务
”“查询得到值1
”“将1改成2
查询得到值V1
”“提交事务B
查询得到值V2
提交事务A
查询得到值V3

5.25更新：
“读未提交”， 则 V1 的值就是 2，V2、V3 也都是 2。
“读提交”，则 V1 是 1，V2、V3 的值是2。
“可重复读”， V1、V2 是 1，V3 是 2。
“串行化”，从 A 的角度看V1、V2 值是 1，V3 的值是 2。

Oracle 数据库的默认隔离级别是“读提交”

尽量不使用长事务：长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务启动方式：
1.显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2.set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

4.索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型

  1. 哈希表：键-值存储数据的结构，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。问题：多个key值会出现同一个值的情况，拉出一个链表来解决。适用于只有等值查询的场景。
  2. 有序数组：适合等值查询和范围查询的场景，只适用于静态存储引擎。
  3. 搜索树：时间复杂度是 O(log(N))，二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。因为索引不止存在内存中，还要写到磁盘上。N 叉树在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

InnoDB的索引模型
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

索引类型分为主键索引（聚簇索引 clustered index）和非主键索引（二级索引 secondary index）。
（普通索引、唯一索引、主键索引、组合索引、全文索引）
主键索引的叶子节点存的是整行数据，非主键索引的叶子节点内容是主键的值。

基于主键索引和普通索引的查询有什么区别？
如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。基于非主键索引的查询需要多扫描一棵索引树，所以尽量使用主键查询。

索引维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

select * from T where k between 3 and 5

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

SQL 查询语句的执行流程：
1.在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2.再到 ID 索引树查到 ID=300 对应的 R3；
3.在 k 索引树取下一个值 k=5，取得 ID=500；
4.再回到 ID 索引树查到 ID=500 对应的 R4；
5.在 k 索引树取下一个值 k=6，不满足条件，循环结束。

回到主键索引树搜索的过程，我们称为回表。
这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）

覆盖索引
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

索引下推
MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

B树和B+树的区别
在B树中，你可以将键和值存放在内部节点和叶子节点；
但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

5.MySQL的锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

  1. 全局锁：对整个数据库实例加锁。加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。
    官方自带的逻辑备份工具是 mysqldump，使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
    全库只读不使用 set global readonly=true 的方式原因：readonly 的值会被用来做其他逻辑（用来判断一个库是主库还是备库）；在异常处理机制上有差异（FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高）。
  2. 表级锁：一种是表锁，一种是元数据锁（meta data lock，MDL)。表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
  3. 行锁：针对数据表中行记录的锁，MySQL 的行锁是在引擎层由各个引擎自己实现的。 MyISAM 引擎不支持行锁。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

按照效率排序的话，count(字段)< count(主键id) < count(1)~count(*)

莫小夕儿呀
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
深度解锁Webpack系列
03-02 293
1.webpack 是什么？ webpack 是一个现代 JavaScript 应用程序的静态模块打包器，当 webpack 处理应用程序时，会递归构建一个依赖关系图，其中包含应用程序需要的每个模块，然后将这些模块打包成一个或多个 bundle。 2.webpack 的核心概念 entry: 入口 output: 输出 loader: 模块转换器，用于把模块原内容按照需求转换成新内容 插件(plu...
Mysql5.0破解版
07-09
我自己的项目一直都是用这个版本，内附激活码。很稳定。
深度解锁Webpack系列(中)（转载）
yorcentroll的博客
03-10 210
本文转载来自： https://mp.weixin.qq.com/s/t8dW5TlASCAb_Cdowb8isA 本文是第二篇，如果你还没有阅读《4W字长文带你深度解锁Webpack系列(基础篇)》，建议阅读之后，再继续阅读本文。 本文会引入更多的webpack配置，如果文中有任何错误，欢迎在评论区指正，我会尽快修正。webpack优化部分放在了下一篇。 推荐大家参考本文一步一步...
深度解锁Webpack系列(上)（转载）
yorcentroll的博客
03-10 254
本文转载来自：https://mp.weixin.qq.com/s/X9fWN4GbDFOLfOODZlLoVg 1.webpack 是什么？ webpack是一个现代JavaScript应用程序的静态模块打包器，当webpack处理应用程序时，会递归构建一个依赖关系图，其中包含应用程序需要的每个模块，然后将这些模块打包成一个或多个bundle。 2.webpack 的核心概念 ...
MySQL表级锁——技术深度+1
qq_41124175的博客
04-18 1124
SQLType兼容性与SHARED_READ和SHARED_WRITE兼容，与EXCLUSIVE互斥与SHARED_READ和SHARED_WRITE兼容，与EXCLUSIVE互斥DDLEXCLUSIVESHARED_READ_ONLY与SHARED_READ兼容，与SHARED_WRITE互斥；SHARED_NO_READ_WRITE与SHARED_READ_ONLY 和SHARED_WRITE都互斥与所有MDL锁互斥。Lock TypeIS意向共享锁与表读锁兼容，与写锁是排斥的。
MySQL原理深度解剖与应用最佳实践
u011585609的专栏
03-16 388
本文从MySQL的数据结构和设计思路出发剖析了索引、事务、锁等机制的实现原理，并对一些常见的MySQL使用特性进行了原理的佐证，引申出一些实用小技巧，本文零零碎碎耗费了本人两个星期左右时间，总结的很完善了，素材和资料来自互联网，核心论点均来自实际演示demo，文章有点长，可以按章节逐步学习，如需转载请标注出处尊重原创，创作不易，谢谢！-- danyuanblog.com 原文传送门 1 索引详解 1.1 简介 什么是索引 如果把数据库中一张表比作一本书，那索引就可以看做是书的目录，它提供了一种通过索引
mysql
qq_45806972的博客
04-12 932
一、mysql 1.1 numeric数据类型 整数：tinyint smallint bigint int mediumint 小数：float double decimal(p,s) numeric(p,s) 数据范围 数据类型 所占字节 最小范围有符号 最大范围有符号 最小范围无符号 最大范围无符号 tinyint 1 -128 127 0 255 smallint 2 -32768 32767 0 65535 mediumint 3 -8388608 8388607 0
【Mysql系列】Mysql基础篇
热门推荐
檀越的博客
11-14 2万+
💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨。
深入理解MySQL
WN0813的博客
08-06 608
MySQL逻辑框架 最上层服务并不是MySQL所独有，大多基于网络的客户端/服务器的工具或服务都有类似的架构。如连接处理、授权认证、安全等 第二层架构是MySQL核心服务所在曾，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现存储过程、触发器、视图等 第三层包含存储引擎 ，负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎不会去解析
mysql回滚会解锁吗_MySQL死锁检测和回滚
weixin_36309562的博客
02-02 146
”在innodb源代码lock/lock0lock.c文件中,定义了两个常量:/*Restricts the length of search we will do in the waits-forgraph of transactions*/#define LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 1000000/*Restricts the recursion de...
破解Mysql账号密码的方法
07-18
当忘了mysql账号的密码的时候，可以尝试尝试这个方法。
windows 下 mysql破解方法
01-03
windows 下 mysql破解方法 , 只需要修改mysql的配置文件
mysql破解插件
12-13
破解任意版本的mysql工具,破解任意版本的mysql工具,破解任意版本的mysql工具
mysql破解密码脚本
03-30
mysql破解密码脚本
在JPA项目启动时新增MySQL字段
VioletKiss
06-18 995
我用的方案1，如果你也是这种项目，那么我也推荐你用方案1，简单粗暴又方便；方案2是问了chatgpt得出来的答案，感觉不错，但是报错了又不想改；不建议使用 schema.sql 和 data.sql 对数据库结构及数据进行初始化，因为在每一次项目启动，这两个文件都必须执行一次，大大减慢了项目的启动速度，替代方案可以是使用 flyway 进行数据库版本控制。
mysqladmin——MySQL Server管理程序（二）
最新发布
u011565038的博客
06-22 995
mysqladmin是一个命令行工具，用于执行简单的 MySQL 服务器管理任务，如检查服务器的状态、创建和删除数据库、重载权限等。
如何在 MySQL 中创建和使用事务?
lgbingyu00的博客
06-21 942
事务对于MySQL在业务生产系统中的作用非常重要，以上示例主要显示的数据的隔离性。当多条数据同时执行的时候，事务会保证业务执行同时成功或同时失败，这样使用有关联性的业务可以保持业务的一致性。
mysql中生僻汉字的解决办法
codemami的博客
06-20 557
除了字符集之外，还需要选择适当的排序规则（collation）。如果你的数据库或表当前使用的字符集不支持生僻汉字（如latin1或utf8），你可以通过ALTER DATABASE或ALTER TABLE命令来更改它们。在MySQL中处理生僻汉字时，主要关注的是字符集和排序规则（collation）的设置。在进行任何可能更改数据的操作之前（如更改字符集或排序规则），务必备份你的数据库。总之，处理MySQL中生僻汉字问题的关键是选择正确的字符集和排序规则，并确保在存储、检索和显示过程中正确处理字符编码。
mysql 锁表解锁
09-02
要锁定和解锁MySQL表，可以使用以下语句： 1. 锁定表： ``` LOCK TABLES table_name [AS alias] {READ | WRITE} ``` 其中，`table_name` 是要锁定的表名，`alias` 是表的别名，`READ` 或 `WRITE` 指定锁的类型。`...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值