MySQLをシンプルに学ぶ

序文

データベースは常に私の弱点でした。自分の経験 (python+sqlalchemy) を組み合わせて、記録を作成します。私は ORM を使用することを好みます。SQL のスペルは苦痛であると常に感じています (主に SQL が得意ではないため)。また、以前のメンテナンスプロジェクトで、エンコードの問題、浮動小数点精度の低下など、データベースの落とし穴に遭遇したため、将来同じ落とし穴を繰り返さないようにしています。

第1章: ヘルプの使用

mysql組み込みヘルプコマンドを使用する

msyql> ? data types
mysql> ? int
mysql> ? create table

第2章: テーブルタイプの選択(ストレージエンジン)

最も一般的に使用される 2 つのエンジンは次のとおりです。

1. Myisam は MySQL のデフォルトのストレージエンジンです。create で新しいテーブルを作成し、新しいテーブルのストレージエンジンを指定しない場合は、デフォルトで Myisam が使用されます。各 MyISAM はディスク上の 3 つのファイルに保存されます。ファイル名はテーブル名と同じで、拡張子は .frm (テーブル定義を保存)、.MYD (MYData、データを保存)、.MYI (MYIndex、インデックスを保存) です。データファイルとインデックスファイルを異なるディレクトリに配置することで、IO を均等に分散し、速度を向上できます。

2. InnoDB ストレージエンジンは、コミット、ロールバック、クラッシュ回復機能を備えたトランザクションセキュリティを提供します。ただし、MyISAM ストレージエンジンと比較すると、InnoDB は書き込み効率が低く、データとインデックスを保持するためにより多くのディスク領域を占有します。

共通環境:

1. MyISAM: デフォルトのMySQLプラグインストレージエンジン。Web、データウェアハウス、その他のアプリケーション環境で最も一般的に使用されているストレージエンジンの1つです。

2. InnoDB: ACID トランザクションサポートを含む多くの機能を備え、トランザクション処理アプリケーションに使用されます。

第3章: 適切なデータ型の選択

まず、適切なストレージエンジンを選択し、指定されたストレージエンジンに基づいて適切なデータ型を決定します。

MyISAM: 可変長データ列ではなく、固定長データ列を使用するのが最適です。
InnoDB: varchar が推奨されます

注意すべきデータの種類:

1. char と varchar: 保存方法と取得方法が異なり、最大長や末尾のスペースが保持されるかどうかも異なります。 char の長さは固定です。長さが足りない場合は、スペースが埋め込まれます。取得時に PAD_CHAR_TO_FULL_LENGTH が設定されていない場合、末尾のスペースはデフォルトで削除されます。
varchar 可変長文字列。末尾のスペースは取得中に保持されます。クエリでは大文字と小文字が区別されないことに注意してください。大文字と小文字を区別するために sqlalchemy を使用する場合は、 func.binary関数を使用しないでください。

2. テキストとblob: text 、大きな「穴」が残ります。OPTIMIZE TABLE 関数を使用して、このようなテーブルを定期的にデフラグすることをお勧めします。大きな BLOB 値またはテキスト値を取得しないでください。テキスト列と BLOB 列を別々のテーブルに分離します。

3. 浮動小数点数 float と固定小数点数 10 進数:

いくつかの点に注意してください:

1. 浮動小数点数はより広いデータ範囲を表現できますが、エラーの問題があります。

2. 通貨など、精度が重要な問題の場合は、固定小数点ストレージを使用する必要があります。以前のプロジェクトでいくつか問題に遭遇し、結局ズームインとズームアウトで解決しなければならなくなり、それは見苦しいものでした。

3. プログラミング中に浮動小数点数に遭遇した場合は、エラーの問題に注意し、浮動小数点数の比較を避けるようにしてください (浮動小数点数を比較するには、差が特定の精度未満である必要があります)。Python 3.5 では、次のように比較できます: float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

4. 浮動小数点数におけるいくつかの特殊な値の処理に注意してください。

第4章: 文字セット

最初に適切な文字セットを選択することが重要です。そうしないと、後で変更するのに非常にコストがかかります。文字セットは Python 2 における長年の課題であり、多くの初心者を混乱させています。以前私が管理していたプロジェクトでは、msyql のデフォルトの latin1 文字セットを使用していたため、文字列を書き込むたびに手動で utf8 にエンコードする必要がありました。最近、python3.5+flask を使用してプロジェクトを実行し、utf8 を直接使用しましたが、エンコードの問題は再び発生しませんでした。

utf8 を使用してデータベースを作成します。CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
sqlalchemy 接続 URL は、mysql://root:[email protected]:3306/my_db?charset=utf8 を使用します。文字化けしたコードについては心配しないでください。

第5章: インデックスの設計と使用

すべての MySQL 列タイプはインデックス化できます。関連する列にインデックスを使用することが、選択操作のパフォーマンスを向上させる最善の方法です。インデックス設計の原則:

1. 検索対象のインデックス列は、必ずしも選択される列ではありません。インデックス作成に最も適した列は、select キーワードに続く select リストに表示される列ではなく、where 句に表示される列、または join 句で指定される列です。

2. 一意のインデックスを使用します。インデックスは、一意の値を持つ列では適切に機能しますが、複数の重複値を持つ列では適切に機能しません。

3. 短いインデックスを使用します。文字列列にインデックスを付ける場合は、可能な限りプレフィックスの長さを指定する必要があります。

4. 一番左のプレフィックスを使用します。 n 列のインデックスを作成すると、実際には MySQL が使用できる n 個のインデックスが作成されます。複数列インデックスは、インデックス内の最も左の列セットを使用して行を一致させることができるため、複数のインデックスとして機能することができます。このような列セットは、最も左のプレフィックスになります。

5. インデックスを過剰に作成しないでください。インデックスはディスク領域を浪費し、書き込みパフォーマンスを低下させます。

6. 列に対して実行される比較の種類を考慮します。

第6章: ロック機構とトランザクション制御

InnoDB エンジンは行レベルのロックを提供し、共有ロックと排他ロックの 2 つのロックモードと 4 つの異なる分離レベルをサポートします。 MySQL は、AUTOCOMIT、START TRANSACTIONS、COMMIT、ROLLBACK などのステートメントを通じてローカルトランザクションをサポートします。

第7章: SQLにおけるセキュリティ問題

SQL インジェクション: 一部のデータベースの外部インターフェースを利用して、実際のデータベース操作言語 (sql) にユーザーデータを挿入し、データベースやオペレーティングシステムに侵入する目的を達成します。主な原因は、プログラムがユーザーによるデータ入力を厳密にフィルタリングせず、不正なデータベースクエリステートメントが実行されてしまうことです。予防策:

prepareStatement = Bind-variable 、連結されたSQLは使用しないでください
アプリケーションが提供する変換関数の使用
カスタム関数検証（フォーム検証など）

第8章: SQLモードと関連する問題

デフォルトの MySQL 実行モードを変更します。たとえば、厳密モードで挿入または更新が正しくない場合、MySQL はエラーを出して操作を中止します。 set session sql_mode='STRICT_TRANS_TABLES' 。 sql_mode を設定するには、アプリケーション担当者がさまざまな長所と短所を比較検討し、適切な選択を行う必要があります。

第9章: 一般的なSQLスキル

最大値/最小値を含む行を取得します: MAX([DISTINCE] expr), MIN([DISTINCE] expr)
rand()/rand(n)を使用してランダムな行を抽出する方法
統計を行うには、 group by句with rollup句を使用します。
統計にbit group functionsを使用する

第10章: 注意が必要なその他の問題

データベース名とテーブル名の大文字と小文字の区別: プラットフォームやシステムによって大文字と小文字の区別が異なります。常に小文字の名前を使用することをお勧めします。
外部キーを使用する際の注意点: MySQL の InnoDB は、外部キーワード制約のチェックをサポートしています。

第11章: SQLの最適化

SQL を最適化するための一般的な手順:

1. show status および application characteristics を使用して、さまざまな SQL ステートメントの実行頻度と、さまざまな SQL ステートメントのおおよその実行比率を把握します。たとえば、InnoDB パラメータ Innode_rows_read は返された行数を照会し、Innodb_rows_inserted は挿入によって挿入された行数を実行し、Innodb_rows_updated は更新された行数を実行します。いくつかのパラメータもあります: MySQL サーバーへの接続試行の Connections、サーバーの稼働時間 Uptime、低速クエリの数 Slow_queries。

2. 実行効率が低い SQL ステートメントを見つけます。方法は 2 つあります。1 つは、スロークエリログを使用して実行効率の低いステートメントを見つける方法です。--log-slow-queries[=file_name] オプションを指定して起動すると、mysqld は実行時間が long_query_time 秒を超えるすべての SQL ステートメントを含むログファイルを書き込みます。もう 1 つは、show processlist を使用して、スレッドの状態、テーブルがロックされているかどうかなど、MySQL の現在のスレッドを表示することです。SQL 実行状態をリアルタイムで表示し、一部のテーブルロック操作を最適化できます。

3. EXPLAIN を使用して非効率的な SQL の実行プランを分析します。 EXPLAIN を使用すると、インデックスを使用してレコードを検索する SELECT を高速化するために、テーブルにインデックスを付ける必要があるタイミングがわかります。以下は、EXPLAIN の実行後に取得される結果の説明です。

select_type: 選択タイプ
テーブル: 出力結果セットのテーブル
type: テーブルの接続タイプを示します。テーブルに行が 1 つしかなく、type の値が system の場合、これが最適な接続タイプです。select 操作でテーブルを接続するためにインデックスが使用される場合、type の値は ref です。select テーブル接続でインデックスが使用されない場合は、type の値が ALL になることが多く、テーブルが完全にスキャンされていることを示します。この場合、インデックスを作成してテーブル接続の効率を向上させることを検討する必要があります。
possible_keys: クエリ時に使用できるインデックス列を示します。
キー: 使用するインデックスを示します
key_len: インデックスの長さ
行: スキャン範囲
追加: 実装に関する注記と説明

4. 問題を特定し、対応する最適化対策を講じます。

インデックス作成の問題

インデックスストレージの分類: myisam テーブルのデータファイルとインデックスファイルは自動的に分離され、innodb のデータとインデックスは同じテーブルスペースに配置されます。 MyISAMとInnoDBのインデックスストレージタイプはbtreeです
MySQL がインデックスを使用する方法: インデックスは、列内の特定の値を持つ行をすばやく見つけるために使用されます。クエリでインデックスを使用するための最も重要な条件は、クエリ条件でインデックスキーワードを使用することです。複数列のインデックスの場合、複数列のキーワードの左端のプレフィックスがクエリ条件で使用されている場合にのみ、インデックスを使用できます。それ以外の場合、インデックスは使用できません。
インデックスの使用状況を確認します。Handler_read_key の値は、行がインデックス化された回数を表します。値が低い場合、インデックスは頻繁に使用されていないことを意味します。 Handler_read_rnd_next 値が高いということは、クエリが非効率的に実行されていることを意味し、インデックスを作成して修正する必要があります。 show status like 'Handler_read%';

2つのシンプルで実用的な最適化方法

定期分析テーブル: ANALYZE TABLE、CHECK TABLE、CHECKSUM TABLE
OPTIMIZE テーブルを使用します。

クライアント（コード側）の観点からの最適化

接続のオーバーヘッドを回避するには、データベースへの永続的な接続を使用します。コードでは、一般的に接続プールを使用します
すべてのインタールードで必要なインデックスが使用されていることを確認します。
読み取り/書き込み競合によるテーブルのロックに関連する問題を回避するために、頻繁に更新されるテーブルに対して複雑な選択クエリを実行しないでください。
デフォルト値を活用し、デフォルト値と異なる場合にのみ値を明示的に挿入します。これにより、MySQL が実行する必要がある構文分析の量が削減され、挿入速度が向上します。
読み取りと書き込みの分離によりパフォーマンスが向上
フィールドの自動インクリメントが同時実行性の高い状況での効率に影響しないように、テーブルフィールドでは自動インクリメント変数を使用しないようにしてください。フィールドの自動インクリメントはアプリケーションを通じて実装することをお勧めします。

第12章: データベースオブジェクトの最適化

テーブルデータ型を最適化: PROCEDURE ANALYZE()現在のテーブルタイプに基づいて最適化の提案を行います。実際には、統計情報は実際のアプリケーションの最適化と組み合わせることができます。

分割によるテーブルアクセス効率の向上: ここでの分割は主に MyISAM タイプのテーブルを対象としています。

垂直分割: アプリケーションのアクセス頻度に応じて、テーブル内の頻繁にアクセスされるフィールドとあまりアクセスされないフィールドを 2 つのテーブルに分割します。頻繁にアクセスされるフィールドは、可能な限り固定長にする必要があります。
水平分割: アプリケーションの状況に応じて、データを複数のテーブルに水平に分割したり、パーティショニングによって複数のパーティションに分割したりできます。これにより、MyISAM テーブルの読み取りと更新によって発生するロックの問題を効果的に回避できます。

非正規化: 正規化設計では独立性を重視し、データの冗長性を最小限に抑えます。冗長性が高くなると、占有される物理スペースも大きくなり、データのメンテナンスや一貫性のチェックにも問題が発生します。適切な冗長性により、複数テーブルへのアクセスが削減され、クエリの効率が大幅に向上します。この場合、適切な冗長性による効率の向上を検討できます。

冗長統計テーブルを使用する: 統計分析にcreate temporary tableを使用する

より適切なテーブルタイプを選択します。1. アプリケーションに深刻なロック競合がある場合は、ストレージエンジンを意図的に InnoDB に変更するかどうかを検討してください。行ロックメカニズムにより、ロック競合の発生を効果的に減らすことができます。 2. アプリケーションに多くのクエリ操作があり、トランザクションの整合性に関する厳しい要件がない場合は、Myisam の使用を検討できます。

第13章: ロックの問題

ロックの待機: システム上のテーブルロックの競合を分析するための table_locks_waited および table_locks_immediate ステータス変数。行ロックの競合を分析するには、Innode_row_lock をチェックします。

第14章: MySQLサーバーの最適化

MySQLサーバーの現在のパラメータを表示する

サーバーパラメータのデフォルト値を表示します: mysqld --verbose --help
サーバーパラメータの実際の値を表示するには： shell> mysqladmin variables or mysql> SHOW VARIABLES
サーバーの実行ステータス値を表示します: mysqladmin extended-status or mysql>SHOW STATUS

MySQLのパフォーマンスに影響を与える重要なパラメータ

key_buffer_size: キーキャッシュ
table_cache: データベースで開かれているキャッシュの数
innode_buffer_pool_size: InnoDBデータとインデックスをキャッシュするためのメモリバッファのサイズ
innodb_flush_log_at_trx_commit: 1 に設定することをお勧めします。各トランザクションがコミットされると、ログバッファーがログファイルに書き込まれ、ディスク操作のためにログファイルが更新されます。

第15章: I/Oの問題

ディスクシークはパフォーマンスの大きなボトルネックになります。