MySQL学習エンジンの詳細な説明、説明、権限

エンジン

導入

Innodbエンジン

Innodb エンジンは、データベース ACID トランザクションをサポートし、SQL 標準の 4 つの分離レベルを実装します。このエンジンは、行レベルのロックと外部キー制約も提供します。大容量のデータベースシステムを処理するように設計されています。これは、実際には MySQL バックエンドに基づく完全なデータベースシステムです。MySQL の実行中、Innodb はメモリ内にバッファプールを確立し、データとインデックスをバッファリングします。ただし、このエンジンは FULLTEXT インデックスをサポートしておらず、テーブル内の行数を保存しません。SELECT COUNT(*) FROM TABLE を実行する場合、テーブル全体をスキャンする必要があります。もちろん、データベーストランザクションが必要な場合、このエンジンが最初の選択肢となります。ロックの粒度が小さいため、書き込み操作でテーブル全体がロックされることはなく、同時実行性が高い場合は Innodb エンジンを使用すると効率が向上します。ただし、行レベルのロックの使用は絶対的なものではありません。MySQL が SQL ステートメントの実行時にスキャンする範囲を決定できない場合、InnoDB テーブルもテーブル全体をロックします。

MyIASMエンジン

MyIASM は MySQL のデフォルトエンジンですが、データベーストランザクション、行レベルのロック、外部キーをサポートしていません。そのため、データの INSERT (挿入) または UPDATE (更新) を実行する場合、書き込み操作でテーブル全体をロックする必要があり、効率が低下します。ただし、Innodb とは異なり、MyIASM はテーブル内の行数を保存するため、SELECT COUNT(*) FROM TABLE を実行する場合は、テーブル全体をスキャンせずに保存された値を直接読み取るだけで済みます。テーブルの読み取り操作が書き込み操作よりはるかに多く、データベーストランザクションのサポートを必要としない場合は、MyIASM も適切な選択肢です。

主な違い

1. MyIASMは非トランザクションセーフですが、InnoDBはトランザクションセーフです。

2. MyIASMロックの粒度はテーブルレベルですが、InnoDBは行レベルのロックをサポートしています。

3. MyIASMはフルテキストインデックスをサポートしていますが、InnoDBはフルテキストインデックスをサポートしていません。

4. MyIASMはInnoDBよりも比較的シンプルで効率的です。小規模なアプリケーションではMyIASMの使用を検討できます。

5. MyIASMテーブルはファイル形式で保存されるため、クロスプラットフォームでの使用に便利です。

アプリケーションシナリオ

1. MyIASM は非トランザクションテーブルを管理し、高速な保存と取得、および全文検索機能を提供します。アプリケーションで多数の選択操作を実行する場合は、MyIASM を選択する必要があります。

2. InnoDBはトランザクション処理に使用され、ACIDトランザクションのサポートなどの機能を備えています。アプリケーションで大量の挿入および更新操作を実行する場合は、InnoDBを選択する必要があります。

MySQLの説明

日常業務では、実行に時間のかかる SQL ステートメントを記録するために、スロークエリを実行することがあります。これらの SQL ステートメントを見つけても、作業が完了したわけではありません。SQL ステートメントがインデックスを使用しているかどうか、および完全なテーブルスキャンが実行されているかどうかを確認するために、explain コマンドを使用してこれらの SQL ステートメントの実行プランを表示することがよくあります。これは、explain コマンドで表示できます。したがって、MySQL のコストベースのオプティマイザについて深く理解でき、また、オプティマイザが考慮する可能性のあるアクセス戦略や、SQL ステートメントを実行するときにオプティマイザが採用すると予想される戦略に関する多くの詳細も取得できます。 (QEP: sql は実行プランクエリ実行プランを生成します)

mysql> explain select * from servers;
+----+-------------+---------+-------+---------------+-------+-------+------+------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------+-------+---------------+-------+-------+------+------+
| 1 | SIMPLE | サーバー | すべて | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+-------+---------------+-------+-------+------+------+
セット内の行数 (0.03 秒)

expain によって出力される情報には、id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra の 10 列があります。これらのフィールドの表示例を次に示します。

1. id

私の理解では、それは SQL が実行される順序です。SQL は大きいものから小さいものの順に実行されます。

1. IDが同じ場合、実行順序は上から下になります

2. サブクエリの場合はID番号が増加します。ID値が大きいほど優先度が高くなり、早く実行されます。

3. IDが同じ場合はグループとみなして上から下へ実行します。すべてのグループで、ID値が大きいほど優先度が高くなり、早く実行されます。

2. 選択タイプ

クエリ内の各選択句のタイプを示します

（１）SIMPLE（UNIONやサブクエリなどを使用しない単純なSELECT）

（2）PRIMARY（クエリに複雑なサブパーツが含まれている場合、最も外側の選択はPRIMARYとしてマークされます）

（３）UNION（UNION内の２番目以降のSELECT文）

（4）DEPENDENT UNION（UNION内の2番目以降のSELECT文は外側のクエリに依存する）

（５）連合の結果

（6）SUBQUERY（サブクエリの最初のSELECT）

（7）依存サブクエリ（外部クエリに依存するサブクエリの最初のSELECT）

（８）DERIVED（派生テーブルSELECT、FROM句のサブクエリ）

（9）キャッシュ不可能なサブクエリ（サブクエリの結果はキャッシュできず、外部リンクの最初の行を再評価する必要がある）

3. 表

この行のデータがどのテーブルに関するものかを表示します。実際のテーブル名ではなく、派生x (x は数値で、ステップの実行結果として理解されます) である場合もあります。

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+--------------+------------+---------+-------------------+---------+-------+-------+-------+-------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------+------------+---------+-------------------+---------+-------+-------+-------+-------+
| 1 | PRIMARY | <derived2> | システム | NULL | NULL | NULL | NULL | 1 | |
| 2 | 派生 | <derived3> | システム | NULL | NULL | NULL | NULL | 1 | |
| 3 | 派生 | t1 | const | PRIMARY、idx_t1_id | PRIMARY | 4 | | 1 | |
+----+--------------+------------+---------+-------------------+---------+-------+-------+-------+-------+

タイプ

MySQL がテーブル内の必要な行を見つける方法を示します。これは「アクセスタイプ」とも呼ばれます。

一般的なタイプは、ALL、index、range、ref、eq_ref、const、system、NULL です (左から右、パフォーマンスは最悪から最高)

ALL: フルテーブルスキャン。MySQLはテーブル全体をスキャンして一致する行を検索します。

index: フルインデックススキャン。index と ALL の違いは、インデックスタイプはインデックスツリーのみをトラバースすることです。

範囲: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。

ref: 上記のテーブルの結合一致条件、つまりインデックス列の値を見つけるために使用される列または定数を示します。

eq_ref: ref と似ていますが、使用されるインデックスが一意のインデックスであるという点が異なります。インデックスキー値ごとに、テーブル内で一致するレコードは 1 つだけです。簡単に言えば、複数のテーブルを結合する場合、結合条件として主キーまたは一意のキーを使用します。

const、system: MySQL はクエリの一部を最適化し、それを定数に変換するときに、これらのタイプのアクセスを使用します。 where リストに主キーを入れると、MySQL はクエリを定数に変換できます。system は const 型の特殊なケースです。クエリテーブルに行が 1 つしかない場合は、system を使用します。

NULL: MySQL は最適化中にステートメントを分解し、実行中にテーブルやインデックスにアクセスしません。たとえば、インデックス列から最小値を選択することは、単一のインデックス検索で実行できます。

5. 可能なキー

MySQL がテーブル内のレコードを検索するために使用できるインデックスを示します。クエリに関係するフィールドにインデックスが存在する場合、そのインデックスはリストされますが、クエリでは使用されない可能性があります。

この列は、EXPLAIN 出力に表示されるテーブルの順序とは完全に独立しています。これは、possible_keys 内の一部のキーが、テーブルが生成された順序で実際に使用できないことを意味します。
列が NULL の場合、関連付けられたインデックスはありません。この場合、WHERE 句をチェックして、特定の列またはインデックス作成に適した列を参照しているかどうかを確認することで、クエリのパフォーマンスを向上させることができます。その場合は、適切なインデックスを作成し、EXPLAIN を使用してクエリを再度確認してください。

6. キー

キー列には、MySQL が実際に使用することを決定したキー (インデックス) が表示されます。

インデックスが選択されなかった場合、キーは NULL になります。 MySQL で possible_keys 列のインデックスを強制的に使用または無視するには、クエリで FORCE INDEX、USE INDEX、または IGNORE INDEX を使用します。

7. キーの長さ

インデックスで使用されるバイト数を示します。この列は、クエリで使用されるインデックスの長さを計算するために使用できます (key_len によって表示される値は、インデックスフィールドの最大可能長であり、実際に使用される長さではありません。つまり、key_len はテーブル定義に基づいて計算され、テーブルから取得されるものではありません)。

長さが短いほど、精度を失わずに良くなります。

8. 参照

上記のテーブルの結合一致条件、つまりインデックス列の値を見つけるために使用される列または定数を示します。

9. 行

MySQL がテーブル統計とインデックス選択に基づいて必要なレコードを見つけるために読み取る必要があると見積もる行数を示します。

10. 追加

この列には、MySQL がクエリを解決する方法に関する詳細情報が含まれています。いくつかのケースがあります。

where の使用: 実際にテーブルを読み取らずに、インデックスの情報のみを使用してテーブルから列データが返されます。これは、テーブルに対して要求されたすべての列が同じインデックスの一部である場合に発生します。つまり、MySQL サーバーは、ストレージエンジンが行を取得した後にフィルタリングを実行します。

一時テーブルの使用: MySQL が結果セットを保存するために一時テーブルを使用する必要があることを示します。これは、クエリの並べ替えやグループ化でよく使用されます。

ファイルソートの使用: インデックスを使用して完了できない MySQL のソート操作は、「ファイルソート」と呼ばれます。

結合バッファの使用: この値は、結合条件を取得するときにインデックスが使用されず、中間結果を格納するために結合バッファが必要であることを強調します。この値が表示される場合は、クエリの特定の状況に応じて、パフォーマンスを向上させるためにインデックスを追加する必要がある場合があることに注意してください。

不可能な where: この値は、where 句によって条件に該当する行が生成されないことを強調します。

最適化されたテーブルを選択: この値は、オプティマイザーがインデックスのみを使用して集計関数の結果から 1 行のみを返す可能性があることを意味します。

要約:

• EXPLAINでは、トリガー、ストアドプロシージャ、またはユーザー定義関数がクエリにどのように影響するかについてはわかりません。

• EXPLAINはさまざまなキャッシュを考慮しない

• EXPLAINは、クエリ実行時にMySQLが行う最適化作業を表示しません。

• 一部の統計は推定値であり、正確な値ではありません

• EXPALIN は SELECT 操作のみを説明できます。他の操作は SELECT として書き換えてから、実行プランを確認する必要があります。

MySQL 認証管理

文法：

'user'@'IP address' の権限を表示します。 -- 権限を表示します
database.table への権限を 'user'@'IP address' に付与します。 -- 承認
database.table の権限を 'user'@'IP address' から取り消します。 -- 権限を取り消します

権限パラメータ

すべての権限付与を除くすべての権限
チェック権限のみを選択
選択、挿入クエリ、挿入権限
...
使用方法アクセス権なし
alter テーブルを変更する
変更プロシージャと削除プロシージャを使用してルーチンを変更する
作成テーブルの作成を使用する
ルーチンを作成する作成手順を使用する
一時テーブルを作成する一時テーブルの作成を使用する
ユーザーの作成ユーザーの作成、ユーザーの削除、ユーザー名の変更、すべての権限の取り消しを使用します
ビューの作成ビューの作成を使用する
削除削除を使用する
ドロップドロップテーブルの使用
呼び出しとストアドプロシージャを使用して実行する
ファイルは、出力ファイルを選択して、データを入力ファイルにロードします。
grant と revoke を使用した grant オプション
インデックスインデックスの使用
挿入挿入を使用する
テーブルのロックテーブルのロックの使用
プロセス完全なプロセスリストを表示
選択する選択を使用する
show databases show databasesの使用
表示ビュー表示ビューを使用する
アップデート
フラッシュを使用してリロードする
シャットダウン mysqladmin シャットダウンを使用する（MySQLをシャットダウンする）
super 💙change master、kill、logs、purge、master、set global を使用します。 mysqladminのdebugログインも許可します
レプリケーションクライアントサーバーロケーションアクセス
レプリケーションスレーブレプリケーションスレーブによって使用される

権限について

権限パラメータ

データベースパラメータ

ターゲットデータベースとその内部のその他のデータベースの場合:
データベース名。* データベース内のすべての
データベース名。テーブルはデータベース内のテーブルを指定します。
データベース名.ストアドプロシージャデータベース内のストアドプロシージャを指定します
*.* すべてのデータベース

ユーザーIPパラメータ

ユーザー名@IPアドレスユーザーは変更されたIPアドレスでのみアクセスできますユーザー名@192.168.1.% ユーザーは変更されたIPセグメントでのみアクセスできます (ワイルドカード%は任意を意味します)
ユーザー名@% ユーザーはどのIPアドレスからでもサイトにアクセスできます（デフォルトのIPアドレスは%です）

例

db1.tb1 のすべての権限を TO 'username'@'IP' に付与します。
 
   db1.* に対する 'username'@'IP' の選択権限を付与します。
 
   *.* に対して 'username'@'IP' の select,insert 権限を許可します。
 
   'username'@'IP' からの db1.tb1 の選択を取り消します。

権限の更新

権限をフラッシュし、データをメモリに読み込んで、すぐに有効になるようにします。

パスワードを忘れた

# 認証不要のサーバーを起動する mysqld --skip-grant-tables
 
# クライアント mysql -u root -p
 
# ユーザー名とパスワードを変更します。 update mysql.user set authentication_string=password('666') where user='root';
権限をフラッシュします。
 
パスワードを忘れた

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

以下もご興味があるかもしれません: