오늘MySQL Tutorial 칼럼에서는 제가 알고 있는 기본 아키텍처를 소개하겠습니다.
진지한 CRUD 엔지니어로서 데이터베이스와의 상호 작용은 추가, 삭제, 수정 및 쿼리의 일일 반복, 기록 데이터 처리, SQL 성능 최적화 등과 같은 일상 작업에서 큰 부분을 차지합니다. 프로젝트 데이터의 양이 늘어남에 따라 프로젝트 진행을 따라잡기 위해 묻어두었던 깊은 구덩이들이 서서히 그 위력을 드러내고 있으며, 이로 인해 기본적인 CRUD에만 머물지 않고 MySQL을 종합적이고 심층적으로 배우게 됩니다. .
MySQL 시리즈의 첫 번째 기사에서는 MySQL의 인프라와 서버 계층의 bin 로그, InnoDB 고유의 redo 로그 등 각 구성 요소의 기능을 주로 소개합니다.
DB-Engines에서 발표한 가장 인기 있는 데이터베이스 관리 시스템 순위에 따르면 MySQL은 단연 2위를 차지하고 있습니다.
가장 널리 사용되는 관계형 데이터베이스 관리 시스템 중 하나인 MySQL은 C/S 아키텍처, 즉 클라이언트 및 서버 아키텍처를 사용합니다. 예를 들어 개발자가 Navicat을 사용하여 MySQL에 연결하는 경우 전자는 클라이언트이고 후자는 서버입니다.
동시에 MySQL은 단일 프로세스, 다중 스레드 데이터베이스이기도 합니다. 이는 이해하기 쉽습니다. 실행 중인 MySQL 인스턴스는 "단일 프로세스"이며 이 프로세스에는 메인 스레드 마스터 스레드
, IO 스레드
와 같은 많은 스레드가 있습니다. > 등, 이러한 스레드는 다양한 작업을 처리하는 데 사용됩니다. Master Thread
,IO Thread
等,这些线程被用于处理不同的任务。
前面说到 MySQL 采用的是C/S架构,用户通过客户端连接到 MySQL 服务器,然后提交 SQL 语句到服务器,然后服务器就会把执行结果返回给客服端。
在这一小节的内容中,我们主要关注 MySQL 服务端的逻辑组成,先来看一张图。
从上图可以看到,与客户端的交互中,MySQL 的服务端分别经过了连接器、查询缓存、分析器、优化器、执行器和存储引擎这几部分。
下面就以一条简单的查询语句来描述 MySQL 服务端的各组成部分及它们所起的作用。
在客户端提交查询语句之前,需要与服务端建立连接。所以最先来到的是连接器,连接器的作用就是负责与客户端建立、管理连接,同时查询用户的权限。
需要注意的是:
在经过连接器的建立连接、获取用户权限之后,接下来用户可以提交查询语句了。
最先经过的是查询缓存部分,由它的名字也能够猜到,查询缓存的作用就是查询 MySQL 是否执行过客户端提交的查询语句,如果这条 SQL 之前执行过,并且用户对该表有执行该语句的权限,就会直接返回之前执行的结果。
所以在某些时候,多次执行一句 SQL 并不能得到它的平均执行时间,因为查询缓存的关系,后面的执行时间往往比第一次执行要短。
如果你不想使用缓存,可以在每次查询后都用 update 语句更新表,当然这是非常麻烦并且憨的方法。MySQL也提供了相应的配置项—— query_cache_type
,你可以在 my.cnf
文件中将 query_cache_type
设置为0以关闭查询缓存。
需要注意的是:
key-value
形式进行存储的,key 为查询语句,value 是查询结果。MySQL 8.0
query_cache_type
도 제공합니다. my.cnf
파일에서 query_cache_type
을 0으로 설정하여 쿼리 캐시를 끌 수 있습니다. . 🎜🎜주의해야 할 점: 🎜키-값
형식으로 저장됩니다. 여기서 키는 쿼리 문이고 값은 쿼리 결과입니다. 🎜MySQL 8.0
버전에서는 쿼리 캐시 기능이 제거되었습니다. 🎜🎜🎜2.3 분석기🎜🎜제가 사용하는 MySQL 버전은 5.7.21이므로 클라이언트가 제출한 쿼리 문은 쿼리 캐시로 이동하며 적중이 없으면 계속해서 분석기로 내려갑니다. 🎜분석기는 제출된 명령문에 대해 어휘 분석(명령문 구문 분석) 및 구문 분석(명령문이 MySQL의 문법 규칙을 준수하는지 확인)을 수행하므로 분석기의 역할은 SQL 문을 구문 분석하고 적법성을 확인하는 것입니다.
다음 사항에 유의해야 합니다.
예:
select * form user_info limit 1;复制代码
위의 SQL 문장에는 두 가지 오류가 있습니다. 첫 번째는 from의 철자 오류이고, 두 번째는 실행 후 user_info 테이블이 존재하지 않는다는 것입니다. 아래는 SQL을 3회 실행한 결과 정보입니다.
第一次的执行信息: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form user_info limit 1' at line 1, Time: 0.000000s 修改为from后第二次的执行信息:1146 - Table 'windfall.user_info' doesn't exist, Time: 0.000000s 修改为 user 表后第三次的执行信息: OK, Time: 0.000000s复制代码
SQL 문의 적법성을 확인한 후 MySQL은 사용자가 제출한 문의 목적이 무엇인지 이미 알고 있지만 실제로 실행되기 전에 여전히 매우 "형이상학적인" 최적화 프로그램을 거쳐야 합니다.
옵티마이저의 기능은 SQL 문에 대한 최적의 실행 계획을 생성하는 것입니다.
옵티마이저를 "형이상학적"이라고 말하는 이유는 SQL 문을 최적화하는 과정에서 사용자가 예상치 못한 실행 계획(인덱스 선택, 다중 테이블 연관 연결 순서, 암시적 함수 변환, 등.) . 물론, 최적화 프로그램이 때때로 "잘못된 인덱스를 선택"하는 경우가 있는데, 이는 데이터 볼륨 및 인덱스 통계와 같은 요소와 관련이 있습니다.
주의할 점:
MySQL 옵티마이저의 워크플로우에 대해 다음 블로그를 읽을 수 있습니다. 이것이 MySQL 옵티마이저가 원래 작동하는 방식입니다.
MySQL 실행 계획도 숙달해야 하는 기술입니다. 이 블로그는 매우 상세하고 가치가 있습니다. explain 실행 계획을 읽을 수 없습니다. 이력서에 SQL 최적화에 익숙하다고 쓰지 않는 것이 좋습니다.
최적화 프로그램이 MySQL이 최적이라고 간주하는 실행 계획을 생성한 후에는 마침내 나타납니다. Executor의 역할은 물론 SQL 문을 실행하는 것입니다.
단, 실행 전 사용자에게 테이블에 대한 쿼리 권한이 있는지 확인하기 위해 권한 확인이 먼저 이루어져야 합니다. 그런 다음 테이블에 정의된 엔진 유형에 따라 해당 엔진이 제공하는 인터페이스를 사용하여 테이블에 대해 조건부 쿼리를 수행하고 마지막으로 조건을 충족하는 테이블의 모든 데이터 행을 결과 세트로 클라이언트에 반환하므로 전체 SQL의 실행이 끝났음을 의미합니다.
실행자가 SQL 문을 실행하기 전에 사용자에게 테이블에 대한 작업 권한이 있는지 확인하기 전에
MySQL은 InnoDB, MyISAM, Memory 등과 같은 다양한 스토리지 엔진을 지원합니다.
InnoDB는 오늘날 가장 일반적으로 사용되는 MySQL 스토리지 엔진이자 MySQL 5.5 이후의 기본 스토리지 엔진이기도 합니다.
InnoDB는 트랜잭션, MVCC(다중 버전 동시성 제어), 외래 키, 행 수준 잠금 및 자동 증가 열을 지원합니다. 그러나 InnoDB는 전체 텍스트 인덱싱을 지원하지 않으며 더 많은 데이터 공간을 차지합니다.
MyISAM은 MySQL 5.1 및 이전 버전의 기본 스토리지 엔진으로, 전체 텍스트 인덱싱, 압축, 공간 함수 및 테이블 수준 잠금을 지원합니다.
MyISAM의 데이터는 컴팩트한 형식으로 저장되므로 공간을 덜 차지합니다. 삽입 및 쿼리 속도가 높지만 MyISAM은 트랜잭션을 지원하지 않으며 충돌 후 안전하게 복구할 수 없습니다.
메모리의 모든 데이터는 메모리에 저장되므로 디스크 I/O가 필요하지 않으므로 MyISAM 및 InnoDB보다 속도가 훨씬 빠릅니다. 하지만 데이터베이스를 종료하거나 재시작하면 메모리 엔진 데이터는 사라집니다.
메모리는 해시 인덱스를 지원하지만 테이블 수준 잠금을 사용하기 때문에 동시 쓰기 성능이 상대적으로 낮습니다.
MySQL의 임시 테이블은 일반적으로 메모리 테이블에 저장된다는 점을 언급할 가치가 있습니다. 중간 테이블의 데이터 양이 너무 크거나 BLOB 유형 또는 TEXT 유형의 필드를 포함하는 경우 MyISAM 테이블이 사용됩니다.
스토리지 엔진에 관해서는 제가 상대적으로 접한 적이 거의 없기 때문에 "MySQL Technology Insider: InnoDB Storage Engine"을 읽고 여기서는 간단히 언급하겠습니다.
위에서 언급한 실행 프로세스는 주로 쿼리 문을 설명합니다. 업데이트 문인 경우 MySQL 로그 모듈도 포함됩니다.
클라이언트에서 실행기로의 논리적 쿼리 문과 업데이트 문은 실행기 계층에 도달할 때 업데이트 문이 MySQL 로그 모듈과 상호 작용한다는 점을 제외하면 동일합니다. 다른.
对于 InnoDB 存储引擎来说,它有一个特有的日志模块——物理日志(重做日志)redo log
,它是 InnoDB 存储引擎的日志,它所记录的是数据页的物理修改。
举个例子,现在有一张 user 表,有一条主键 id=1,age=18 的数据,然后用户提交了下面这条 SQL,执行器准备执行。
update user set age=age+1 where id=1;复制代码
对于这条 SQL,在 redo log 中记录的内容大致是:将 user 表中主键 id=1 行的 age 字段值修改为19
。
MySQL 的更新持久化逻辑运用到了 WAL(Write-Ahead Logging,写前日志记录) 的思想:先写日志,再写磁盘。
需要注意的是这里的写日志也是写到磁盘中,但由于日志是顺序写入的,所以速度很快。而如果没有 redo log,直接更新磁盘中的数据,那么首先需要找到那条记录,然后再把新的值更新进入,由于查询和读写I/O,就相对会慢一些。
最后,当 InnoDB 引擎空闲的时候,它会去执行 redo log 中的逻辑,将数据持久化到磁盘中。
redo log 日志文件大小是固定的,我把它理解为一个MySQL에 대한 나의 이해 중 하나: 인프라,链表的每个节点都可以存放日志,在这个链表中有两个指针:write(黑) 和 read(白)。
最开始这两个指针都指向同一个节点,且节点日志元素都为空,表示此时 redo log 为空。当用户开始提交更新语句,write 节点开始往前移动,假设移动到3的位置。而此时的情况就是 redo log 中有1-3这三个日志元素需要被持久化到磁盘中,当 InnoDB 空闲时,read 指针往前移动,就代表着将 redo log 持久化到磁盘。
但这里有一种特殊情况,就是 InnoDB 一直没有空闲,write 指针一直在写入日志,直到它写到5的位置,再往前写又回到了最开始1的位置(也就是上图的位置,但不同的是链表节点中都存在日志数据)。
此时发现1的位置已经有日志数据了,同时 read 指针也在。那么这时候 write 指针就会暂停写入,InnoDB 引擎开始催动 read 指针移动,把 redo log 清空掉一部分之后再让 write 指针写入日志文件。
我们已经知道,redo log 中记录的是数据页的物理修改,所以 redo log 能够保证在数据库发生异常重启时,记录尚未写入磁盘,但是在重启后可以通过 redo log 来“redo”,从而不会发生记录丢失的情况,保证了事务的持久性。
这一能力也被称作 crash-safe。
前面说到 redo log 是 InnoDB 特有的日志,而 bin log 则是属于 MySQL Server 层的日志,在默认的 Statement Level 下它记录的是更新语句的原始逻辑,即 SQL 本身。
另外需要注意的是:
与 redo log 不同的是,bin log 常用于恢复数据,比如说主从复制,从节点根据父节点的 bin log 来进行数据同步,实现主从同步。
为了让 redo log 和 bin log 的状态保持一致,MySQL 使用两阶段提交的方式来写入 redo log 日志。
在执行器调用 InnoDB 引擎的接口将写入更新数据时,InnoDB 引擎会将本次更新记录到 redo log 中,同时将 redo log 的状态标记为 prepare,表示可以提交事务。
随后执行器生成本次操作的 bin log 数据,并写入 bin log 的日志文件中。
最后执行器调用 InnoDB 的提交事务接口,存储引擎把刚写入的 redo log 记录状态修改为 commit,本次更新结束。
在这个过程中有三个步骤 add redo log and mark as prepare
-> add bin log
-> commit
,即:
두 번째 단계, 즉 빈 로그를 작성하기 전에 시스템이 충돌하거나 다시 시작되면 데이터가 없습니다. 시작 후 bin 로그에서 redo 로그의 레코드는 이 업데이트 문을 실행하기 전에 롤백됩니다.
세 번째 단계 이전, 즉 제출 전 시스템이 충돌하거나 다시 시작하면 커밋이 없지만 redo 로그에 준비로 기록되고 bin 로그에 전체 기록이 있어도 자동으로 커밋됩니다. 다시 시작한 후에는 돌아오지 않습니다.
이 글에서는 주로 MySQL의 인프라와 각 구성 요소의 기능을 소개합니다. 마지막으로 MySQL Server 계층의 bin 로그와 InnoDB 고유의 redo 로그를 소개합니다.
다음 질문은 "과거를 복습하고 새로운 것을 배우면 교사가 될 수 있다"는 말처럼 이 글에 설명된 내용에 대해 질문하고 지식을 통합하는 것입니다.
리두 로그와 빈 로그의 차이점은 무엇인가요?
더 많은 관련 무료 학습 권장 사항: mysql 튜토리얼(동영상)
위 내용은 MySQL에 대한 나의 이해 중 하나: 인프라의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!