mysql 학습에서 select 쿼리문은 어떻게 실행되나요?

이 기사는 커넥터, 분석기, 최적화 프로그램 및 실행 프로그램을 포함하여 MySQL의 선택 문 실행에 대한 관련 지식을 제공합니다.

관계형 데이터베이스로서 mysql은 중국에서 가장 널리 사용되어야 합니다. 아마도 귀사에서는 Oracle, PG 등을 사용하고 있겠지만, 저희 회사 등 대부분의 인터넷 기업에서는 Mysql을 가장 많이 사용하고 있으며 그 중요성은 자명합니다.

테이블에서 select *를 실행하면 데이터베이스 하단에서 정확히 어떤 일이 발생하나요? 그러면 데이터를 어떻게 얻나요?

이제 두 개의 열만 있는 사용자 테이블이 있고, 한 열에는 자체 증가하는 ID가 있고, 한 열에는 varchar 유형의 이름이 있다고 가정해 보겠습니다. 테이블 생성문은 다음과 같습니다.

CREATE TABLE IF NOT EXISTS `user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

문제는 다음 구문의 실행 과정입니다.

select * from user where id = 1;

01 mysql 아키텍처 개요

이 문제를 이해하려면 mysql의 내부 아키텍처를 알아야 합니다. 이를 위해 아래와 같이 mysql 아키텍처 다이어그램(sql 쿼리문의 실행 과정으로도 이해할 수 있음)을 그렸습니다.

먼저 msql은 서버 계층과 서버 계층, 두 부분으로 나뉩니다. 스토리지 엔진 계층. 서버 계층에는 커넥터, 쿼리 캐시, 최적화 프로그램 및 실행기의 네 가지 기능 모듈이 포함됩니다. 이 계층은 내장 함수, 저장 프로시저, 트리거, 뷰 등과 같은 mysql의 모든 핵심 작업을 담당합니다.

스토리지 엔진 계층은 데이터 액세스를 담당합니다. mysql에서는 스토리지 엔진이 선택 사항입니다. 일반적인 엔진으로는 InnoDB, MyISAM, Memory 등이 있습니다. 가장 일반적으로 사용되는 엔진은 InnoDB입니다. 이는 현재 기본 스토리지 엔진이기도 합니다(mysql 버전 5.5.5부터). 위의 테이블 생성 명령문이 InnoDB 엔진을 지정한 것을 볼 수 있습니다. 물론 지정하지 않으면 기본값으로 설정됩니다.

스토리지 엔진은 선택 사항이므로 mysql의 모든 스토리지 엔진은 실제로 서버 계층을 공유합니다. 다시 본론으로 돌아가서, 지방이 적은 문제를 해결하기 위해 이 그림의 과정을 활용해 보겠습니다.

1.1 커넥터

먼저 데이터베이스가 sql을 실행하려면 먼저 데이터베이스에 연결되어 있어야 합니다. 이 작업 부분은 커넥터에 의해 수행됩니다. 계정 비밀번호 확인, 권한 획득, 연결 수 관리 및 최종적으로 클라이언트와의 연결 설정을 담당합니다. mysql 링크 데이터베이스는 다음과 같이 작성됩니다.

mysql -h 127.0.0.1 -P 3306 -u root -p
# 127.0.0.1 : ip 3306 : 端口 root : 用户名

명령을 실행한 후 비밀번호를 입력해야 하며, 물론 -p를 따를 수도 있습니다. 하지만 비밀번호 유출의 위험이 있으므로 권장하지 않습니다.

명령어를 입력하면 커넥터는 계정 이름과 비밀번호를 기반으로 신원을 인증합니다. 두 가지 상황이 발생합니다:

• 계정 또는 비밀번호가 올바르지 않으며 서버에서 "ERROR 1045 (28000): 사용자 'root'@'127.0.0.1'에 대한 액세스가 거부되었습니다(비밀번호 사용: YES)" 오류를 반환합니다. , 연결을 종료하세요.

• 인증이 통과된 후 커넥터는 권한 테이블로 이동하여 권한을 확인합니다. 나중에 어떤 권한을 갖게 될지는 이때 읽은 권한에 따라 판단됩니다.

참고로 이때 발견된 권한에 대해 이야기하고 있습니다. 관리자 계정을 사용하여 현재 사용자의 권한을 수정하더라도 현재 연결된 사용자는 영향을 받지 않습니다. 새 권한을 적용하려면 mysql을 다시 시작해야 합니다.

1.1.1 연결 상태 확인

연결이 완료되었습니다. 이후 아무것도 하지 않으면 연결이 유휴 상태가 됩니다. show processlist 명령을 사용하면 아래와 같이 mysql의 연결 정보를 볼 수 있습니다. show processlist 작업을 수행하는 연결을 제외하고는 모든 데이터베이스 연결이 절전 상태입니다.

1.1.2 제어 연결

클라이언트가 오랫동안 작동하지 않으면 이 연결이 자동으로 끊어집니다. 이 시간의 기본값은 8시간이며 wait_timeout 매개변수에 의해 제어됩니다. 연결을 끊은 후에도 계속 작동하면 "쿼리 중 MySQL 서버에 대한 연결이 끊어졌습니다"라는 오류가 표시됩니다. 이때 요청을 실행하려면 다시 연결해야 합니다.

데이터베이스에는 긴 연결과 짧은 연결이 있습니다. 긴 연결: 연결에 성공한 후에도 계속 요청이 있으면 항상 동일한 연결이 사용됩니다. 짧은 연결: 여러 요청을 실행한 후 연결이 끊어지고 다음에 다시 설정해야 합니다.

연결 설정은 시간이 많이 걸리는 작업이므로 긴 연결을 사용하는 것이 좋습니다. 하지만 문제가 있습니다. 긴 연결을 계속 연결하면 너무 많은 메모리를 차지하며 시스템에서 강제로 샌딩됩니다. 이로 인해 MySQL이 비정상적으로 다시 시작됩니다. 어떻게 해결하나요? 두 가지 방법:

• 긴 연결을 정기적으로 끊습니다. 특정 시간을 사용하거나, 메모리를 많이 차지하는 작업을 수행한 후 연결이 끊어진 것으로 프로그램에서 판단합니다. 필요한 경우 다시 연결하십시오.

• mySQL 5.7 이상에서는 메모리를 많이 차지하는 작업을 실행할 때마다 mysql_reset_connection을 실행하여 리소스를 다시 연결할 수 있습니다. 이때는 다시 연결하거나 권한 인증을 다시 수행할 필요가 없습니다. 상태는 생성 시 이전 상태로 복원됩니다.

1.2 查询缓存

连接建立以后可以执行 select 语句了。这就会来到第二步：查询缓存。

查询缓存中存储的数据是 key-value 的形式，key 是查询语句，value 是查询的结果。逻辑是这样的：先看看查询缓存有没该语句对应的 value？有则直接取出返回客户端，无则继续到数据库执行语句。查出结果后会放一份到缓存中，再返回客户端。

你可能发现缓存真的香，但是并不建议使用查询缓存，因为有弊端。查询缓存的失效非常频繁，只有某个表有更新。它马上失效了，对于经常更新的表来说，命中缓存的概率极低。它仅仅适用于那些不经常更新的表。

而 MySQL 似乎也考虑到这点了。提供了 query_cache_type 参数，把它设置为 DEMAND 就不再适用韩村。而对于要使用缓存的语句则可用 SQL_CACHE 显示指定，像这样：

select SQL_CACHE * from user where id = 1;

PS：MySQL 8.0 及以上版本把查询缓存删掉了，之后再也没有这块功能了。

1.3 分析器

如果没有命中缓存就进入分析器，这里就是对 sql 进行分析。分析器会做词法分析。你输入的 sql 是啥，由啥组成，MySQL 都需要知道它们代表什么。

首先根据 "select" 识别出这是查询语句。字符串"user"识别成"表名 user"、字符串"id"识别成"列名id"。

之后进行语法分析，它会根据输入的语句分析是不是符合 MySQL 的语法。具体表现就是 select、where、from 等关键字少了个字母，明显不符合 MySQL 语法，这次就会报个语法错误的异常：它一般会提示错误行数，关注"use near"后面即可。

1.4 优化器

过了分析器，就来到了优化器。MySQL 是个聪明的仔，再执行之前会自己优化下客户端传过来的语句，看看那种执行起来不那么占内存、快一点。比如下面的 sql 语句：

select * from user u inner join role r on u.id = r.user_id where u.name = "狗哥" and r.id = 666

它可以先从 user 表拿出 name = "狗哥" 记录的 ID 值再跟 role 表内连接查询，再判断 role 表里面 id 的值是否 = 666

也可以反过来：先从 role 表拿出 id = 666 记录的 ID 值再跟 user 表内连接查询，在判断 user 表里面的 name 值是否 = "狗哥"。

两种方案的执行结果是一样的，但是效率不一样、占用的资源也就不一样。优化器就是在选择执行的方案。它优化的是索引应该用哪个？多表联查应该先查哪个表？怎么连接等等。

1.5 执行器

分析器知道了做啥、优化器知道了应该怎么做。接下来就交给执行器去执行了。

开始执行，判断是否有相应的权限。比如该账户对 user 表没权限就返回无权限的错误，如下所示：

select * from user where id = 1;
ERROR 1142 (42000): SELECT command denied to user 'nasus'@'localhost' for table 'user'

PS：如果命中缓存没走到执行器这里，那么在返回查询结果时做权限验证。

回到正题，如果有权限，继续打开表执行。执行器会根据表定义的引擎去使用对应接口。比如我们上面的 sql 语句执行流程是这样的：

• 走 id 索引、调用 InnoDB 引擎取"满足条件的第一行"接口，再循环调用"满足条件的下一行"接口（这些接口都是存储引擎定义好的），直到表中不再有满足条件的行。执行器就将上述遍历得到的行组成结果集返回给客户端。

• 对于 id 不是索引的表，执行器只能调用"取表记录的第一行"接口，再判断 id 是否 = 1。如果不是则跳过，是则存在结果集中；再调存储引擎接口取"下一行"，重复判断逻辑，直到表的最后一行。

至此，整个 SQL 的执行流程完毕，

推荐学习：mysql视频教程

위 내용은 mysql 학습에서 select 쿼리문은 어떻게 실행되나요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!