MySQL에 대한 나의 이해 중 하나: 인프라
- coldplay.xixi앞으로
- 2020-10-20 17:03:331948검색
오늘MySQL Tutorial 칼럼에서는 제가 알고 있는 기본 아키텍처를 소개하겠습니다.
진지한 CRUD 엔지니어로서 데이터베이스와의 상호 작용은 추가, 삭제, 수정 및 쿼리의 일일 반복, 기록 데이터 처리, SQL 성능 최적화 등과 같은 일상 작업에서 큰 부분을 차지합니다. 프로젝트 데이터의 양이 늘어남에 따라 프로젝트 진행을 따라잡기 위해 묻어두었던 깊은 구덩이들이 서서히 그 위력을 드러내고 있으며, 이로 인해 기본적인 CRUD에만 머물지 않고 MySQL을 종합적이고 심층적으로 배우게 됩니다. .
MySQL 시리즈의 첫 번째 기사에서는 MySQL의 인프라와 서버 계층의 bin 로그, InnoDB 고유의 redo 로그 등 각 구성 요소의 기능을 주로 소개합니다.
1. MySQL 아키텍처 소개
DB-Engines에서 발표한 가장 인기 있는 데이터베이스 관리 시스템 순위에 따르면 MySQL은 단연 2위를 차지하고 있습니다.
가장 널리 사용되는 관계형 데이터베이스 관리 시스템 중 하나인 MySQL은 C/S 아키텍처, 즉 클라이언트 및 서버 아키텍처를 사용합니다. 예를 들어 개발자가 Navicat을 사용하여 MySQL에 연결하는 경우 전자는 클라이언트이고 후자는 서버입니다.
동시에 MySQL은 단일 프로세스, 다중 스레드 데이터베이스이기도 합니다. 이는 이해하기 쉽습니다. 실행 중인 MySQL 인스턴스는 "단일 프로세스"이며 이 프로세스에는 메인 스레드 마스터 스레드, IO 스레드와 같은 많은 스레드가 있습니다. > 등, 이러한 스레드는 다양한 작업을 처리하는 데 사용됩니다. Master Thread，IO Thread 等，这些线程被用于处理不同的任务。
2. MySQL 组成部分
前面说到 MySQL 采用的是C/S架构，用户通过客户端连接到 MySQL 服务器，然后提交 SQL 语句到服务器，然后服务器就会把执行结果返回给客服端。
在这一小节的内容中，我们主要关注 MySQL 服务端的逻辑组成，先来看一张图。
从上图可以看到，与客户端的交互中，MySQL 的服务端分别经过了连接器、查询缓存、分析器、优化器、执行器和存储引擎这几部分。
下面就以一条简单的查询语句来描述 MySQL 服务端的各组成部分及它们所起的作用。
2.1 连接器
在客户端提交查询语句之前，需要与服务端建立连接。所以最先来到的是连接器，连接器的作用就是负责与客户端建立、管理连接，同时查询用户的权限。
需要注意的是：
- 连接器只获取用户的权限，并不做校验，校验是在查询缓存或执行器才进行。
- 一旦建立连接同时获取用户的权限之后，只有建立新的连接才会刷新用户权限。
- 对于长时间没有发送请求的客户端，连接器会自动断开连接。这里的「长时间」是由 wait_timeout 参数来决定的，它的默认值为8小时。
2.2 查询缓存
在经过连接器的建立连接、获取用户权限之后，接下来用户可以提交查询语句了。
最先经过的是查询缓存部分，由它的名字也能够猜到，查询缓存的作用就是查询 MySQL 是否执行过客户端提交的查询语句，如果这条 SQL 之前执行过，并且用户对该表有执行该语句的权限，就会直接返回之前执行的结果。
所以在某些时候，多次执行一句 SQL 并不能得到它的平均执行时间，因为查询缓存的关系，后面的执行时间往往比第一次执行要短。
如果你不想使用缓存，可以在每次查询后都用 update 语句更新表，当然这是非常麻烦并且憨的方法。MySQL也提供了相应的配置项—— query_cache_type，你可以在 my.cnf 文件中将 query_cache_type 设置为0以关闭查询缓存。
需要注意的是：
- 查询缓存部分是以
key-value形式进行存储的，key 为查询语句，value 是查询结果。 - 当对数据表进行更新时，关于这张表的所有查询缓存都会失效，所以一般来说查询缓存的命中率是很低的。
- 在
MySQL 8.02. MySQL 구성 요소
🎜🎜위 그림에서 볼 수 있듯이 클라이언트와의 상호 작용에서 MySQL 서버는 각각 커넥터, 쿼리 캐시, 분석기, 최적화 프로그램, 실행기 및 저장소를 통과합니다. 엔진의 이러한 부분. 🎜🎜다음은 MySQL 서버의 다양한 구성 요소와 해당 기능을 설명하는 간단한 쿼리문입니다. 🎜2.1 커넥터🎜🎜클라이언트가 쿼리문을 제출하기 전에 서버와의 연결을 설정해야 합니다. 따라서 가장 먼저 나오는 것은 커넥터입니다. 커넥터의 기능은 클라이언트와의 연결을 설정 및 관리하고 사용자의 권한을 쿼리하는 것입니다. 🎜🎜주의해야 할 점: 🎜
- 커넥터는 사용자의 권한만 획득하며 확인을 수행하지 않습니다. 확인은 캐시나 실행 프로그램을 쿼리할 때만 수행됩니다. 🎜
- 연결이 설정되고 사용자 권한을 획득한 후에는 새 연결이 설정될 때만 사용자 권한이 새로 고쳐집니다. 🎜
- 오랫동안 요청을 보내지 않은 클라이언트의 경우 커넥터가 자동으로 연결 해제됩니다. 여기서 "긴 시간"은 wait_timeout 매개변수에 의해 결정되며 기본값은 8시간입니다. 🎜🎜
2.2 쿼리 캐시🎜🎜커넥터를 통해 연결을 설정하고 사용자 권한을 얻은 후 사용자는 쿼리문을 제출할 수 있습니다. 🎜🎜가장 먼저 살펴봐야 할 부분은 쿼리 캐시 부분입니다. 이름에서 알 수 있듯이 쿼리 캐시의 기능은 🎜클라이언트가 제출한 쿼리 문이 실행되었는지 여부를 쿼리하는 것입니다🎜. 이전에 실행되었고 사용자가 테이블에 명령문을 실행할 권한이 있는 경우 이전 실행 결과를 직접 반환합니다. 🎜🎜그래서 어떤 시점에서는 SQL 문을 여러 번 실행하면 쿼리 캐시로 인해 후속 실행 시간이 첫 번째 실행 시간보다 짧은 경우가 많습니다. 🎜🎜캐싱을 사용하지 않으려면 업데이트 문을 사용하여 각 쿼리 후에 테이블을 업데이트할 수 있습니다. 물론 이는 매우 번거롭고 어리석은 방법입니다. MySQL은 해당 구성 항목인 query_cache_type도 제공합니다. my.cnf 파일에서 query_cache_type을 0으로 설정하여 쿼리 캐시를 끌 수 있습니다. . 🎜🎜주의해야 할 점: 🎜
- 쿼리 캐시 부분은
키-값 형식으로 저장됩니다. 여기서 키는 쿼리 문이고 값은 쿼리 결과입니다. 🎜
- 데이터 테이블이 업데이트되면 해당 테이블에 대한 모든 쿼리 캐시가 무효화되므로 일반적으로 쿼리 캐시의 적중률이 매우 낮습니다. 🎜
-
MySQL 8.0 버전에서는 쿼리 캐시 기능이 제거되었습니다. 🎜🎜🎜2.3 분석기🎜🎜제가 사용하는 MySQL 버전은 5.7.21이므로 클라이언트가 제출한 쿼리 문은 쿼리 캐시로 이동하며 적중이 없으면 계속해서 분석기로 내려갑니다. 🎜분석기는 제출된 명령문에 대해 어휘 분석(명령문 구문 분석) 및 구문 분석(명령문이 MySQL의 문법 규칙을 준수하는지 확인)을 수행하므로 분석기의 역할은 SQL 문을 구문 분석하고 적법성을 확인하는 것입니다.
다음 사항에 유의해야 합니다.
- SQL 문의 유효성을 확인할 때 MySQL은 처음에 MySQL 문법 규칙을 준수하지 않는 오류만 표시하고 SQL 문의 모든 문법 오류를 표시하지 않습니다. 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复制代码
2.4 Optimizer
SQL 문의 적법성을 확인한 후 MySQL은 사용자가 제출한 문의 목적이 무엇인지 이미 알고 있지만 실제로 실행되기 전에 여전히 매우 "형이상학적인" 최적화 프로그램을 거쳐야 합니다.
옵티마이저의 기능은 SQL 문에 대한 최적의 실행 계획을 생성하는 것입니다.
옵티마이저를 "형이상학적"이라고 말하는 이유는 SQL 문을 최적화하는 과정에서 사용자가 예상치 못한 실행 계획(인덱스 선택, 다중 테이블 연관 연결 순서, 암시적 함수 변환, 등.) . 물론, 최적화 프로그램이 때때로 "잘못된 인덱스를 선택"하는 경우가 있는데, 이는 데이터 볼륨 및 인덱스 통계와 같은 요소와 관련이 있습니다.
주의할 점:
- 프로덕션 환경에서 SQL을 최적화해야 하는 경우 프로덕션 환경과 동일한 데이터 볼륨으로 로컬에서 테이블을 복원한 후 실행 계획에 따라 최적화해 보세요.
- 쿼리문을 작성할 때 인덱스의 가장 왼쪽 일치 원칙을 고려해야 합니다(가장 왼쪽 일치 원칙은 인덱스 장에서 논의됩니다).
MySQL 옵티마이저의 워크플로우에 대해 다음 블로그를 읽을 수 있습니다. 이것이 MySQL 옵티마이저가 원래 작동하는 방식입니다.
MySQL 실행 계획도 숙달해야 하는 기술입니다. 이 블로그는 매우 상세하고 가치가 있습니다. explain 실행 계획을 읽을 수 없습니다. 이력서에 SQL 최적화에 익숙하다고 쓰지 않는 것이 좋습니다.
2.5 Executor
최적화 프로그램이 MySQL이 최적이라고 간주하는 실행 계획을 생성한 후에는 마침내 나타납니다. Executor의 역할은 물론 SQL 문을 실행하는 것입니다.
단, 실행 전 사용자에게 테이블에 대한 쿼리 권한이 있는지 확인하기 위해 권한 확인이 먼저 이루어져야 합니다. 그런 다음 테이블에 정의된 엔진 유형에 따라 해당 엔진이 제공하는 인터페이스를 사용하여 테이블에 대해 조건부 쿼리를 수행하고 마지막으로 조건을 충족하는 테이블의 모든 데이터 행을 결과 세트로 클라이언트에 반환하므로 전체 SQL의 실행이 끝났음을 의미합니다.
실행자가 SQL 문을 실행하기 전에 사용자에게 테이블에 대한 작업 권한이 있는지 확인하기 전에
- 이 확인된다는 점에 유의해야 합니다.
2.6 스토리지 엔진
MySQL은 InnoDB, MyISAM, Memory 등과 같은 다양한 스토리지 엔진을 지원합니다.
2.6.1 InnoDB
InnoDB는 오늘날 가장 일반적으로 사용되는 MySQL 스토리지 엔진이자 MySQL 5.5 이후의 기본 스토리지 엔진이기도 합니다.
InnoDB는 트랜잭션, MVCC(다중 버전 동시성 제어), 외래 키, 행 수준 잠금 및 자동 증가 열을 지원합니다. 그러나 InnoDB는 전체 텍스트 인덱싱을 지원하지 않으며 더 많은 데이터 공간을 차지합니다.
2.6.2 MyISAM
MyISAM은 MySQL 5.1 및 이전 버전의 기본 스토리지 엔진으로, 전체 텍스트 인덱싱, 압축, 공간 함수 및 테이블 수준 잠금을 지원합니다.
MyISAM의 데이터는 컴팩트한 형식으로 저장되므로 공간을 덜 차지합니다. 삽입 및 쿼리 속도가 높지만 MyISAM은 트랜잭션을 지원하지 않으며 충돌 후 안전하게 복구할 수 없습니다.
2.6.3 메모리
메모리의 모든 데이터는 메모리에 저장되므로 디스크 I/O가 필요하지 않으므로 MyISAM 및 InnoDB보다 속도가 훨씬 빠릅니다. 하지만 데이터베이스를 종료하거나 재시작하면 메모리 엔진 데이터는 사라집니다.
메모리는 해시 인덱스를 지원하지만 테이블 수준 잠금을 사용하기 때문에 동시 쓰기 성능이 상대적으로 낮습니다.
MySQL의 임시 테이블은 일반적으로 메모리 테이블에 저장된다는 점을 언급할 가치가 있습니다. 중간 테이블의 데이터 양이 너무 크거나 BLOB 유형 또는 TEXT 유형의 필드를 포함하는 경우 MyISAM 테이블이 사용됩니다.
스토리지 엔진에 관해서는 제가 상대적으로 접한 적이 거의 없기 때문에 "MySQL Technology Insider: InnoDB Storage Engine"을 읽고 여기서는 간단히 언급하겠습니다.
3. 로그 모듈
위에서 언급한 실행 프로세스는 주로 쿼리 문을 설명합니다. 업데이트 문인 경우 MySQL 로그 모듈도 포함됩니다.
클라이언트에서 실행기로의 논리적 쿼리 문과 업데이트 문은 실행기 계층에 도달할 때 업데이트 문이 MySQL 로그 모듈과 상호 작용한다는 점을 제외하면 동일합니다. 다른.
3.1 物理日志 redo log
3.1.1 redo log 中记录的内容
对于 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。
3.1.2 WAL
MySQL 的更新持久化逻辑运用到了 WAL(Write-Ahead Logging，写前日志记录) 的思想：先写日志，再写磁盘。
需要注意的是这里的写日志也是写到磁盘中，但由于日志是顺序写入的，所以速度很快。而如果没有 redo log，直接更新磁盘中的数据，那么首先需要找到那条记录，然后再把新的值更新进入，由于查询和读写I/O，就相对会慢一些。
最后，当 InnoDB 引擎空闲的时候，它会去执行 redo log 中的逻辑，将数据持久化到磁盘中。
3.1.3 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 指针写入日志文件。
3.1.4 redo log 的作用
我们已经知道，redo log 中记录的是数据页的物理修改，所以 redo log 能够保证在数据库发生异常重启时，记录尚未写入磁盘，但是在重启后可以通过 redo log 来“redo”，从而不会发生记录丢失的情况，保证了事务的持久性。
这一能力也被称作 crash-safe。
3.2 归档日志 bin log
前面说到 redo log 是 InnoDB 特有的日志，而 bin log 则是属于 MySQL Server 层的日志，在默认的 Statement Level 下它记录的是更新语句的原始逻辑，即 SQL 本身。
另外需要注意的是：
- bin log 的日志文件大小并不固定，它是“追加写入”的模式，写完一个文件后会切换到下一个文件写入。
- bin log 没有 crash-safe 的能力。
- bin log 是在事务最终提交前写入的，而 redo log 是在事务执行中不断写入的。
3.2.1 bin log 的作用
与 redo log 不同的是，bin log 常用于恢复数据，比如说主从复制，从节点根据父节点的 bin log 来进行数据同步，实现主从同步。
3.3 两阶段提交
为了让 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 로그에 전체 기록이 있어도 자동으로 커밋됩니다. 다시 시작한 후에는 돌아오지 않습니다.
4. 요약
이 글에서는 주로 MySQL의 인프라와 각 구성 요소의 기능을 소개합니다. 마지막으로 MySQL Server 계층의 bin 로그와 InnoDB 고유의 redo 로그를 소개합니다.
5. 과거를 복습하고 새로운 것을 배웁니다
다음 질문은 "과거를 복습하고 새로운 것을 배우면 교사가 될 수 있다"는 말처럼 이 글에 설명된 내용에 대해 질문하고 지식을 통합하는 것입니다.
- 쿼리문에 해당 필드가 존재하지 않거나, 필드가 모호하거나, 키워드 철자가 틀리면 어떤 부분에서 오류가 발생하나요?
- 사용자에게 테이블에 대한 쿼리 권한이 없으면 어떤 부분에서 오류가 보고되나요?
- MySQL의 쿼리 캐시가 유효하지 않은 이유는 무엇입니까?
- Select 쿼리 문은 어떻게 실행되나요?
- MySQL에서 일반적으로 사용되는 스토리지 엔진은 무엇입니까?
- MySQL의 로깅 모듈은 무엇입니까? 그들은 어떤 역할을 합니까?
- 리두 로그가 가득 차면 어떻게 해야 하나요?
- 리두 로그의 2단계 제출을 어떻게 이해하나요?
리두 로그와 빈 로그의 차이점은 무엇인가요?
더 많은 관련 무료 학습 권장 사항: mysql 튜토리얼(동영상)
2.2 쿼리 캐시🎜🎜커넥터를 통해 연결을 설정하고 사용자 권한을 얻은 후 사용자는 쿼리문을 제출할 수 있습니다. 🎜🎜가장 먼저 살펴봐야 할 부분은 쿼리 캐시 부분입니다. 이름에서 알 수 있듯이 쿼리 캐시의 기능은 🎜클라이언트가 제출한 쿼리 문이 실행되었는지 여부를 쿼리하는 것입니다🎜. 이전에 실행되었고 사용자가 테이블에 명령문을 실행할 권한이 있는 경우 이전 실행 결과를 직접 반환합니다. 🎜🎜그래서 어떤 시점에서는 SQL 문을 여러 번 실행하면 쿼리 캐시로 인해 후속 실행 시간이 첫 번째 실행 시간보다 짧은 경우가 많습니다. 🎜🎜캐싱을 사용하지 않으려면 업데이트 문을 사용하여 각 쿼리 후에 테이블을 업데이트할 수 있습니다. 물론 이는 매우 번거롭고 어리석은 방법입니다. MySQL은 해당 구성 항목인 query_cache_type도 제공합니다. my.cnf 파일에서 query_cache_type을 0으로 설정하여 쿼리 캐시를 끌 수 있습니다. . 🎜🎜주의해야 할 점: 🎜
- 쿼리 캐시 부분은
키-값 형식으로 저장됩니다. 여기서 키는 쿼리 문이고 값은 쿼리 결과입니다. 🎜
- 데이터 테이블이 업데이트되면 해당 테이블에 대한 모든 쿼리 캐시가 무효화되므로 일반적으로 쿼리 캐시의 적중률이 매우 낮습니다. 🎜
-
MySQL 8.0 버전에서는 쿼리 캐시 기능이 제거되었습니다. 🎜🎜🎜2.3 분석기🎜🎜제가 사용하는 MySQL 버전은 5.7.21이므로 클라이언트가 제출한 쿼리 문은 쿼리 캐시로 이동하며 적중이 없으면 계속해서 분석기로 내려갑니다. 🎜분석기는 제출된 명령문에 대해 어휘 분석(명령문 구문 분석) 및 구문 분석(명령문이 MySQL의 문법 규칙을 준수하는지 확인)을 수행하므로 분석기의 역할은 SQL 문을 구문 분석하고 적법성을 확인하는 것입니다.
다음 사항에 유의해야 합니다.
- SQL 문의 유효성을 확인할 때 MySQL은 처음에 MySQL 문법 규칙을 준수하지 않는 오류만 표시하고 SQL 문의 모든 문법 오류를 표시하지 않습니다. 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复制代码
2.4 Optimizer
SQL 문의 적법성을 확인한 후 MySQL은 사용자가 제출한 문의 목적이 무엇인지 이미 알고 있지만 실제로 실행되기 전에 여전히 매우 "형이상학적인" 최적화 프로그램을 거쳐야 합니다.
옵티마이저의 기능은 SQL 문에 대한 최적의 실행 계획을 생성하는 것입니다.
옵티마이저를 "형이상학적"이라고 말하는 이유는 SQL 문을 최적화하는 과정에서 사용자가 예상치 못한 실행 계획(인덱스 선택, 다중 테이블 연관 연결 순서, 암시적 함수 변환, 등.) . 물론, 최적화 프로그램이 때때로 "잘못된 인덱스를 선택"하는 경우가 있는데, 이는 데이터 볼륨 및 인덱스 통계와 같은 요소와 관련이 있습니다.
주의할 점:
- 프로덕션 환경에서 SQL을 최적화해야 하는 경우 프로덕션 환경과 동일한 데이터 볼륨으로 로컬에서 테이블을 복원한 후 실행 계획에 따라 최적화해 보세요.
- 쿼리문을 작성할 때 인덱스의 가장 왼쪽 일치 원칙을 고려해야 합니다(가장 왼쪽 일치 원칙은 인덱스 장에서 논의됩니다).
MySQL 옵티마이저의 워크플로우에 대해 다음 블로그를 읽을 수 있습니다. 이것이 MySQL 옵티마이저가 원래 작동하는 방식입니다.
MySQL 실행 계획도 숙달해야 하는 기술입니다. 이 블로그는 매우 상세하고 가치가 있습니다. explain 실행 계획을 읽을 수 없습니다. 이력서에 SQL 최적화에 익숙하다고 쓰지 않는 것이 좋습니다.
2.5 Executor
최적화 프로그램이 MySQL이 최적이라고 간주하는 실행 계획을 생성한 후에는 마침내 나타납니다. Executor의 역할은 물론 SQL 문을 실행하는 것입니다.
단, 실행 전 사용자에게 테이블에 대한 쿼리 권한이 있는지 확인하기 위해 권한 확인이 먼저 이루어져야 합니다. 그런 다음 테이블에 정의된 엔진 유형에 따라 해당 엔진이 제공하는 인터페이스를 사용하여 테이블에 대해 조건부 쿼리를 수행하고 마지막으로 조건을 충족하는 테이블의 모든 데이터 행을 결과 세트로 클라이언트에 반환하므로 전체 SQL의 실행이 끝났음을 의미합니다.
실행자가 SQL 문을 실행하기 전에 사용자에게 테이블에 대한 작업 권한이 있는지 확인하기 전에
- 이 확인된다는 점에 유의해야 합니다.
2.6 스토리지 엔진
MySQL은 InnoDB, MyISAM, Memory 등과 같은 다양한 스토리지 엔진을 지원합니다.
2.6.1 InnoDB
InnoDB는 오늘날 가장 일반적으로 사용되는 MySQL 스토리지 엔진이자 MySQL 5.5 이후의 기본 스토리지 엔진이기도 합니다.
InnoDB는 트랜잭션, MVCC(다중 버전 동시성 제어), 외래 키, 행 수준 잠금 및 자동 증가 열을 지원합니다. 그러나 InnoDB는 전체 텍스트 인덱싱을 지원하지 않으며 더 많은 데이터 공간을 차지합니다.
2.6.2 MyISAM
MyISAM은 MySQL 5.1 및 이전 버전의 기본 스토리지 엔진으로, 전체 텍스트 인덱싱, 압축, 공간 함수 및 테이블 수준 잠금을 지원합니다.
MyISAM의 데이터는 컴팩트한 형식으로 저장되므로 공간을 덜 차지합니다. 삽입 및 쿼리 속도가 높지만 MyISAM은 트랜잭션을 지원하지 않으며 충돌 후 안전하게 복구할 수 없습니다.
2.6.3 메모리
메모리의 모든 데이터는 메모리에 저장되므로 디스크 I/O가 필요하지 않으므로 MyISAM 및 InnoDB보다 속도가 훨씬 빠릅니다. 하지만 데이터베이스를 종료하거나 재시작하면 메모리 엔진 데이터는 사라집니다.
메모리는 해시 인덱스를 지원하지만 테이블 수준 잠금을 사용하기 때문에 동시 쓰기 성능이 상대적으로 낮습니다.
MySQL의 임시 테이블은 일반적으로 메모리 테이블에 저장된다는 점을 언급할 가치가 있습니다. 중간 테이블의 데이터 양이 너무 크거나 BLOB 유형 또는 TEXT 유형의 필드를 포함하는 경우 MyISAM 테이블이 사용됩니다.
스토리지 엔진에 관해서는 제가 상대적으로 접한 적이 거의 없기 때문에 "MySQL Technology Insider: InnoDB Storage Engine"을 읽고 여기서는 간단히 언급하겠습니다.
3. 로그 모듈
위에서 언급한 실행 프로세스는 주로 쿼리 문을 설명합니다. 업데이트 문인 경우 MySQL 로그 모듈도 포함됩니다.
클라이언트에서 실행기로의 논리적 쿼리 문과 업데이트 문은 실행기 계층에 도달할 때 업데이트 문이 MySQL 로그 모듈과 상호 작용한다는 점을 제외하면 동일합니다. 다른.
3.1 物理日志 redo log
3.1.1 redo log 中记录的内容
对于 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。
3.1.2 WAL
MySQL 的更新持久化逻辑运用到了 WAL(Write-Ahead Logging，写前日志记录) 的思想：先写日志，再写磁盘。
需要注意的是这里的写日志也是写到磁盘中，但由于日志是顺序写入的，所以速度很快。而如果没有 redo log，直接更新磁盘中的数据，那么首先需要找到那条记录，然后再把新的值更新进入，由于查询和读写I/O，就相对会慢一些。
最后，当 InnoDB 引擎空闲的时候，它会去执行 redo log 中的逻辑，将数据持久化到磁盘中。
3.1.3 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 指针写入日志文件。
3.1.4 redo log 的作用
我们已经知道，redo log 中记录的是数据页的物理修改，所以 redo log 能够保证在数据库发生异常重启时，记录尚未写入磁盘，但是在重启后可以通过 redo log 来“redo”，从而不会发生记录丢失的情况，保证了事务的持久性。
这一能力也被称作 crash-safe。
3.2 归档日志 bin log
前面说到 redo log 是 InnoDB 特有的日志，而 bin log 则是属于 MySQL Server 层的日志，在默认的 Statement Level 下它记录的是更新语句的原始逻辑，即 SQL 本身。
另外需要注意的是：
- bin log 的日志文件大小并不固定，它是“追加写入”的模式，写完一个文件后会切换到下一个文件写入。
- bin log 没有 crash-safe 的能力。
- bin log 是在事务最终提交前写入的，而 redo log 是在事务执行中不断写入的。
3.2.1 bin log 的作用
与 redo log 不同的是，bin log 常用于恢复数据，比如说主从复制，从节点根据父节点的 bin log 来进行数据同步，实现主从同步。
3.3 两阶段提交
为了让 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 로그에 전체 기록이 있어도 자동으로 커밋됩니다. 다시 시작한 후에는 돌아오지 않습니다.
4. 요약
이 글에서는 주로 MySQL의 인프라와 각 구성 요소의 기능을 소개합니다. 마지막으로 MySQL Server 계층의 bin 로그와 InnoDB 고유의 redo 로그를 소개합니다.
5. 과거를 복습하고 새로운 것을 배웁니다
다음 질문은 "과거를 복습하고 새로운 것을 배우면 교사가 될 수 있다"는 말처럼 이 글에 설명된 내용에 대해 질문하고 지식을 통합하는 것입니다.
- 쿼리문에 해당 필드가 존재하지 않거나, 필드가 모호하거나, 키워드 철자가 틀리면 어떤 부분에서 오류가 발생하나요?
- 사용자에게 테이블에 대한 쿼리 권한이 없으면 어떤 부분에서 오류가 보고되나요?
- MySQL의 쿼리 캐시가 유효하지 않은 이유는 무엇입니까?
- Select 쿼리 문은 어떻게 실행되나요?
- MySQL에서 일반적으로 사용되는 스토리지 엔진은 무엇입니까?
- MySQL의 로깅 모듈은 무엇입니까? 그들은 어떤 역할을 합니까?
- 리두 로그가 가득 차면 어떻게 해야 하나요?
- 리두 로그의 2단계 제출을 어떻게 이해하나요?
리두 로그와 빈 로그의 차이점은 무엇인가요?
더 많은 관련 무료 학습 권장 사항: mysql 튜토리얼(동영상)
키-값 형식으로 저장됩니다. 여기서 키는 쿼리 문이고 값은 쿼리 결과입니다. 🎜MySQL 8.0 버전에서는 쿼리 캐시 기능이 제거되었습니다. 🎜🎜🎜2.3 분석기🎜🎜제가 사용하는 MySQL 버전은 5.7.21이므로 클라이언트가 제출한 쿼리 문은 쿼리 캐시로 이동하며 적중이 없으면 계속해서 분석기로 내려갑니다. 🎜분석기는 제출된 명령문에 대해 어휘 분석(명령문 구문 분석) 및 구문 분석(명령문이 MySQL의 문법 규칙을 준수하는지 확인)을 수행하므로 분석기의 역할은 SQL 문을 구문 분석하고 적법성을 확인하는 것입니다.
다음 사항에 유의해야 합니다.
- SQL 문의 유효성을 확인할 때 MySQL은 처음에 MySQL 문법 규칙을 준수하지 않는 오류만 표시하고 SQL 문의 모든 문법 오류를 표시하지 않습니다. 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复制代码
2.4 Optimizer
SQL 문의 적법성을 확인한 후 MySQL은 사용자가 제출한 문의 목적이 무엇인지 이미 알고 있지만 실제로 실행되기 전에 여전히 매우 "형이상학적인" 최적화 프로그램을 거쳐야 합니다.
옵티마이저의 기능은 SQL 문에 대한 최적의 실행 계획을 생성하는 것입니다.
옵티마이저를 "형이상학적"이라고 말하는 이유는 SQL 문을 최적화하는 과정에서 사용자가 예상치 못한 실행 계획(인덱스 선택, 다중 테이블 연관 연결 순서, 암시적 함수 변환, 등.) . 물론, 최적화 프로그램이 때때로 "잘못된 인덱스를 선택"하는 경우가 있는데, 이는 데이터 볼륨 및 인덱스 통계와 같은 요소와 관련이 있습니다.
주의할 점:
- 프로덕션 환경에서 SQL을 최적화해야 하는 경우 프로덕션 환경과 동일한 데이터 볼륨으로 로컬에서 테이블을 복원한 후 실행 계획에 따라 최적화해 보세요.
- 쿼리문을 작성할 때 인덱스의 가장 왼쪽 일치 원칙을 고려해야 합니다(가장 왼쪽 일치 원칙은 인덱스 장에서 논의됩니다).
MySQL 옵티마이저의 워크플로우에 대해 다음 블로그를 읽을 수 있습니다. 이것이 MySQL 옵티마이저가 원래 작동하는 방식입니다.
MySQL 실행 계획도 숙달해야 하는 기술입니다. 이 블로그는 매우 상세하고 가치가 있습니다. explain 실행 계획을 읽을 수 없습니다. 이력서에 SQL 최적화에 익숙하다고 쓰지 않는 것이 좋습니다.
2.5 Executor
최적화 프로그램이 MySQL이 최적이라고 간주하는 실행 계획을 생성한 후에는 마침내 나타납니다. Executor의 역할은 물론 SQL 문을 실행하는 것입니다.
단, 실행 전 사용자에게 테이블에 대한 쿼리 권한이 있는지 확인하기 위해 권한 확인이 먼저 이루어져야 합니다. 그런 다음 테이블에 정의된 엔진 유형에 따라 해당 엔진이 제공하는 인터페이스를 사용하여 테이블에 대해 조건부 쿼리를 수행하고 마지막으로 조건을 충족하는 테이블의 모든 데이터 행을 결과 세트로 클라이언트에 반환하므로 전체 SQL의 실행이 끝났음을 의미합니다.
실행자가 SQL 문을 실행하기 전에 사용자에게 테이블에 대한 작업 권한이 있는지 확인하기 전에
- 이 확인된다는 점에 유의해야 합니다.
2.6 스토리지 엔진
MySQL은 InnoDB, MyISAM, Memory 등과 같은 다양한 스토리지 엔진을 지원합니다.
2.6.1 InnoDB
InnoDB는 오늘날 가장 일반적으로 사용되는 MySQL 스토리지 엔진이자 MySQL 5.5 이후의 기본 스토리지 엔진이기도 합니다.
InnoDB는 트랜잭션, MVCC(다중 버전 동시성 제어), 외래 키, 행 수준 잠금 및 자동 증가 열을 지원합니다. 그러나 InnoDB는 전체 텍스트 인덱싱을 지원하지 않으며 더 많은 데이터 공간을 차지합니다.
2.6.2 MyISAM
MyISAM은 MySQL 5.1 및 이전 버전의 기본 스토리지 엔진으로, 전체 텍스트 인덱싱, 압축, 공간 함수 및 테이블 수준 잠금을 지원합니다.
MyISAM의 데이터는 컴팩트한 형식으로 저장되므로 공간을 덜 차지합니다. 삽입 및 쿼리 속도가 높지만 MyISAM은 트랜잭션을 지원하지 않으며 충돌 후 안전하게 복구할 수 없습니다.
2.6.3 메모리
메모리의 모든 데이터는 메모리에 저장되므로 디스크 I/O가 필요하지 않으므로 MyISAM 및 InnoDB보다 속도가 훨씬 빠릅니다. 하지만 데이터베이스를 종료하거나 재시작하면 메모리 엔진 데이터는 사라집니다.
메모리는 해시 인덱스를 지원하지만 테이블 수준 잠금을 사용하기 때문에 동시 쓰기 성능이 상대적으로 낮습니다.
MySQL의 임시 테이블은 일반적으로 메모리 테이블에 저장된다는 점을 언급할 가치가 있습니다. 중간 테이블의 데이터 양이 너무 크거나 BLOB 유형 또는 TEXT 유형의 필드를 포함하는 경우 MyISAM 테이블이 사용됩니다.
스토리지 엔진에 관해서는 제가 상대적으로 접한 적이 거의 없기 때문에 "MySQL Technology Insider: InnoDB Storage Engine"을 읽고 여기서는 간단히 언급하겠습니다.
3. 로그 모듈
위에서 언급한 실행 프로세스는 주로 쿼리 문을 설명합니다. 업데이트 문인 경우 MySQL 로그 모듈도 포함됩니다.
클라이언트에서 실행기로의 논리적 쿼리 문과 업데이트 문은 실행기 계층에 도달할 때 업데이트 문이 MySQL 로그 모듈과 상호 작용한다는 점을 제외하면 동일합니다. 다른.
3.1 物理日志 redo log
3.1.1 redo log 中记录的内容
对于 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。
3.1.2 WAL
MySQL 的更新持久化逻辑运用到了 WAL(Write-Ahead Logging，写前日志记录) 的思想：先写日志，再写磁盘。
需要注意的是这里的写日志也是写到磁盘中，但由于日志是顺序写入的，所以速度很快。而如果没有 redo log，直接更新磁盘中的数据，那么首先需要找到那条记录，然后再把新的值更新进入，由于查询和读写I/O，就相对会慢一些。
最后，当 InnoDB 引擎空闲的时候，它会去执行 redo log 中的逻辑，将数据持久化到磁盘中。
3.1.3 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 指针写入日志文件。
3.1.4 redo log 的作用
我们已经知道，redo log 中记录的是数据页的物理修改，所以 redo log 能够保证在数据库发生异常重启时，记录尚未写入磁盘，但是在重启后可以通过 redo log 来“redo”，从而不会发生记录丢失的情况，保证了事务的持久性。
这一能力也被称作 crash-safe。
3.2 归档日志 bin log
前面说到 redo log 是 InnoDB 特有的日志，而 bin log 则是属于 MySQL Server 层的日志，在默认的 Statement Level 下它记录的是更新语句的原始逻辑，即 SQL 本身。
另外需要注意的是：
- bin log 的日志文件大小并不固定，它是“追加写入”的模式，写完一个文件后会切换到下一个文件写入。
- bin log 没有 crash-safe 的能力。
- bin log 是在事务最终提交前写入的，而 redo log 是在事务执行中不断写入的。
3.2.1 bin log 的作用
与 redo log 不同的是，bin log 常用于恢复数据，比如说主从复制，从节点根据父节点的 bin log 来进行数据同步，实现主从同步。
3.3 两阶段提交
为了让 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 로그에 전체 기록이 있어도 자동으로 커밋됩니다. 다시 시작한 후에는 돌아오지 않습니다.
4. 요약
이 글에서는 주로 MySQL의 인프라와 각 구성 요소의 기능을 소개합니다. 마지막으로 MySQL Server 계층의 bin 로그와 InnoDB 고유의 redo 로그를 소개합니다.
5. 과거를 복습하고 새로운 것을 배웁니다
다음 질문은 "과거를 복습하고 새로운 것을 배우면 교사가 될 수 있다"는 말처럼 이 글에 설명된 내용에 대해 질문하고 지식을 통합하는 것입니다.
- 쿼리문에 해당 필드가 존재하지 않거나, 필드가 모호하거나, 키워드 철자가 틀리면 어떤 부분에서 오류가 발생하나요?
- 사용자에게 테이블에 대한 쿼리 권한이 없으면 어떤 부분에서 오류가 보고되나요?
- MySQL의 쿼리 캐시가 유효하지 않은 이유는 무엇입니까?
- Select 쿼리 문은 어떻게 실행되나요?
- MySQL에서 일반적으로 사용되는 스토리지 엔진은 무엇입니까?
- MySQL의 로깅 모듈은 무엇입니까? 그들은 어떤 역할을 합니까?
- 리두 로그가 가득 차면 어떻게 해야 하나요?
- 리두 로그의 2단계 제출을 어떻게 이해하나요?
리두 로그와 빈 로그의 차이점은 무엇인가요?
더 많은 관련 무료 학습 권장 사항: mysql 튜토리얼(동영상)
위 내용은 MySQL에 대한 나의 이해 중 하나: 인프라의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!