mysqlの学習においてselectクエリ文はどのように実行されるのでしょうか？

この記事では、コネクタ、アナライザー、オプティマイザー、エグゼキューターなど、mysql での選択ステートメントの実行に関する関連知識を提供します。皆様のお役に立てれば幸いです。

# リレーショナル データベースとして、mysql は中国で最も広く使用されているはずです。もしかしたらあなたの会社では Oracle や PG などを使用しているかもしれませんが、当社のようなほとんどのインターネット企業は Mysql を最もよく使用しており、その重要性は自明のことです。

select * from table を実行すると、データベースの下部で正確に何が起こっているのでしょうか?では、どうやってデータを取得するのでしょうか?

ここで、2 つの列だけを持つユーザー テーブルがあるとします。1 つの列には自動的にインクリメントされる ID があり、もう 1 つの列には 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サーバー層とストレージエンジン層に分かれています。サーバー層には、コネクタ、クエリ キャッシュ、オプティマイザ、エグゼキュータの 4 つの機能モジュールが含まれています。この層は、組み込み関数、ストアド プロシージャ、トリガー、ビューなど、mysql のすべてのコア作業を担当します。

ストレージ エンジン層はデータ アクセスを担当します。 mysql ではストレージ エンジンはオプションであることに注意してください。一般的なものには、InnoDB、MyISAM、メモリなどが含まれます。最も一般的に使用されるのは 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 を続けることもできます。ただし、パスワード漏洩の危険性があるためお勧めできません。

コマンドを入力すると、コネクタはアカウント名とパスワードに基づいて ID を認証します。 2 つの状況が考えられます:

• アカウントまたはパスワードが正しくない場合、サーバーは「エラー 1045 (28000): ユーザー 'root'@'127.0.0.1 のアクセスが拒否されました」を返します。 (パスワードを使用: YES)」エラーが発生した場合は、接続を終了します。

• 検証が成功すると、コネクタは権限テーブルにアクセスして権限を調べます。後でどのような権限を持っているかは、このときに読み取られた権限によって判断されます。

注、ここで話しているのは現時点で見つかった権限についてです。管理者アカウントを使用して現在のユーザーの権限を変更しても、現在接続しているユーザーには影響しません。新しい権限を有効にするには、mysql を再起動する必要があります。

1.1.1 接続状態の確認

接続が完了すると、何も操作しないとアイドル状態になります。以下に示すように、show processlist; コマンドを使用して mysql の接続情報を表示できます。show processlist 操作を実行する接続を除き、すべてのデータベース接続がスリープ状態になっています。

#1.1.2 接続の制御

• 長い接続を定期的に切断します。特定の時間を使用するか、大量のメモリを消費する操作を実行した後に接続が切断されたとプログラムが判断します。必要に応じて再接続します。

• 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 中国語 Web サイトの他の関連記事を参照してください。