目的
不適切な操作によってTencentDB for MySQLが使用できなくなることを防ぐため、TencentDB for MySQLの管理とメンテナンスを標準化します。
SQLを合理的に記述して、TencentDB for MySQLの最適なパフォーマンスを引き出すよう、データベース開発者に指示します。
権限管理基準
TencentDB for MySQLの安定性とセキュリティを考慮し、TencentDB for MySQLはsuper、shutdown、file権限を制限しています。TencentDB for MySQLでsetセンテンスを実行すると、次のエラーが表示される場合があります。
ソリューション：関連パラメータの変更をsetする必要がある場合は、コンソールのインスタンス管理ページのデータベース管理>パラメータ設定機能を使用して完了することができます。
必要に応じて権限を付与し、一般的なアプリケーションプログラムはDML（SELECT、UPDATE、INSERT、DELETE）権限のみを付与します。
権限付与の対象を最小限にするという原則により、一般的なアプリケーションプログラムアクセスユーザーにはデータベースレベルに応じて権限を付与します。
権限を付与されたユーザーがアクセスする場合は、特定のIPまたはIPセグメントへのアクセスのみが許可され、コンソールでセキュリティグループを設定することによってアクセスを制限することができます。セキュリティグループの設定は、コンソールが提示する基準に従って操作する必要があります。パブリックネットワークアクセスでセキュリティグループを設定する場合は、関連するすべてのegress IP を必ずオープンにしてください。
管理アカウントと開発アカウントを分離します。
日常的な操作の基準
注意事項
データベースインスタンスのセキュリティを向上させるため、脆弱なパスワードの使用を禁止します。
イントラネット接続ログインでは、client側のクラウドサーバCVMとクラウドデータベースMySQLが同じアカウントの同じ地域および同じVPCのマシンであることを確認する必要があります。
コンソールからダウンロードしたbinlogログをローカルで解析する必要がある場合は、クライアントのMySQLバージョンがTencentDB for MySQLのインスタンスバージョンと一致していることを確認する必要があります。一致していない場合は、解析時に文字化けが発生する恐れがあります。バージョン3.4以降のmysqlbinlogを使用することをお勧めします。
コンソールでプライベートネットワークを介してCVMのコールドバックアップファイルをダウンロードする場合は、urlを引用符で囲んでください。この操作を行わないと、404エラーが表示されます。
推奨事項
業務ピーク時にonline ddl操作を極力行わないようにしてください。利用可能なツールについては、pt-online-schema-changeをご参照ください。
業務ピーク時にデータのバッチ操作を行うことは極力避けてください。業務オフピーク時がバッチ操作に最適です。
単独のインスタンスで複数の業務を実行することを避けてください。カップリングレベルが高すぎると、業務間で相互に影響を及ぼし合うリスクがあります。
誤操作によるデータ紛失のリスクを低減するため、トランザクションの自動送信をオフにし、オンライン操作でのbegin;を習慣付けることをお勧めします。誤操作については、TencentDB for MySQLのロールバック機能を使用することもできます (現在、5日以内の任意の時点のロールバックをサポートしています)。関連のテーブルにクロスデータベースやクロステーブルのロジックが含まれない場合は、高速ロールバックまたは超高速ロールバックを使用してデータをより迅速に回復することができます。ロールバックによって新しく生成されたデータベーステーブル名のデフォルトはデータベーステーブル名_bakです。
プロモーション活動を行う業務などについては、事前にリソースを見積もり、インスタンスの最適化を行ってください。需要量が大きい場合は、適宜、対応するサービスマネージャーにご連絡ください。
データベーステーブル設計仕様
注意事項
TencentDB for MySQL 5.6以降のバージョンは、MyISAMおよびMemoryエンジンをサポートしていません。Memoryエンジンのニーズがある場合は、TencentDB for RedisおよびMemcachedを使用することをお勧めします。自作データベースをTencentDB for MySQLに移行すると、MyISAMエンジンはInnoDBエンジンに自動的に変換されます。
自動インクリメント列のあるテーブルについては、自動インクリメント列に少なくとも1つの個別のインデックス、または自動インクリメント列で始まる1つの複合インデックスが必要です。
row_formatは非fixedを保証する必要があります。
各テーブルにはプライマリキーが必要です。適切な列をプライマリキーとして選択できなくとも、無意味な列をプライマリキーとして追加する必要があります。MySQL第1パラダイム標準InnoDBセカンダリインデックスのリーフノードは、プライマリキー値を格納することができます。インデックスが占有するディスク領域を削減し、効率を向上させるため、自動インクリメント列をプライマリキーとして使用することをお勧めします。binlog_formatがrowに設定されている場合、プライマリキーのないデータの一括削除は深刻なマスター/スレーブ遅延を引き起こす恐れがあります。
フィールドは極力NOT NULLと定義し、デフォルト値を追加します。NULLはSQL開発に多くの問題をもたらし、インデックスを作成できなくなります。NULLを計算する場合は、IS NULLとIS NOT NULLのみを判断に使用することができます。
推奨事項
業務シナリオ分析とデータアクセス (データベースの読み書きQPS、TPS、Storageキャパシティなどを含みます) の見積もりを通じて、データベースの使用リソースを合理的に計画し、コンソールBasic Cloud Monitorインターフェースで、TencentDB for MySQLインスタンスの各種監視を設定することもできます。
データベース構築の原則は、同種の業務のテーブルを1つのデータベースに格納し、異なる業務のテーブルに同じデータベースを共用することをできる限り避け、プログラムによるデータベース間の関連付け操作を極力実行しないようにすることです。この操作は後続の高速ロールバックにも一定の影響を与える恐れがあります。
文字化けのリスクを低減するため、文字セットにはutf8mb4を統一して使用します。一部の複雑な漢字とemojiの表情を正しく表示するためにはutf8mb4を使用する必要があります。文字セットの変更は、変更後に作成されたテーブルでのみ有効です。したがってTencentDB for MySQLを新規購入し、インスタンスを初期化する際にutf8mb4を選択することをお勧めします。
少数のフィールドについては、decimal型の使用をお勧めします。floatやdoubleは精度が充分ではなく、特に金銭に関わる業務ではdecimalを使用する必要があります。
サイズの大きなテキスト、バイナリーデータ、画像、ファイルなどをtext/blob を使用してデータベースに保存することを極力避けてください。これらのデータはローカルディスクファイルとして保存し、データベースにはインデックス情報のみを保存してください。
外部キーを極力使用しないようにしてください。アプリケーション層に外部キーのロジックを実装することをお勧めします。外部キーとカスケード更新は同時実行性の高いシナリオには不向きです。インサートのパフォーマンスが低下し、大量に同時実行する場合にデッドロックが発生します。
業務ロジックとデータストレージ間のカップリングレベルを低下させます。データベースは主にデータを格納し、業務ロジックは可能な限りアプリケーション層を介して実装されます。ストアドプロシージャ、トリガー、関数、event、ビューなどの高度な機能の使用を最小限に抑えます。これらの機能は移植性、拡張性に劣ることから、インスタンスにこれらのオブジェクトが存在する場合は、移行アカウントとdefinerの不一致による移行の失敗を避けるため、デフォルトでdefinerを設定しないことをお勧めします。
短期的な業務は大きな規模に至らないことから、パーティションテーブルの使用を禁止することをお勧めします。パーティションテーブルは、主にアーカイブ管理に使用され、宅配便業界やeコマース業界のオーダーシートとして多く使用されます。業務における80%以上のクエリーがパーティションフィールドを使用しない限り、パーティションテーブルはパフォーマンスを向上させません。
読み取り負荷が高く、かつ整合性要件が低い(秒レベルの遅延でデータを受信する) 業務シナリオでは、ライブラリから読み取り専用インスタンスを購入して、読み取り/書き込み分離ポリシーを実装することをお勧めします。
インデックス設計仕様
注意事項
頻繁に更新され、識別度の高くない列にインデックスを作成することは禁止されています。レコードの更新によってB+ツリーを変更することができます。頻繁に更新されるフィールドにインデックスを作成すると、データベースのパフォーマンスが大幅に低下します。
複合インデックスを作成する場合は、識別度が最も高い列をインデックスの左端に配置します。例えば、select xxx where a = x and b = x;では、aとbを同時に使用してインデックスを作成し、aの識別度の方が高い場合は、idx_ab(a,b)のように作成します。不等号と等号の混合判定条件がある場合は、等号条件の列を前方に配置します。例えばwhere a xxx and b = xxxでは、aの識別度の方が高い場合でも、インデックスaを使用できないため、bをインデックスの最前列に配置する必要があります。
推奨事項
1つのテーブルのインデックス数が5を超えず、1つのインデックスのフィールド数が5を超えないことをお勧めします。数が多すぎるとフィルタリングの役割を果たせず、インデックスがスペースを占有し、管理するためのリソースも消費します。
業務においてSQLフィルタリングが最も多く、cardinality値が高い列を選択してインデックスを作成します。業務SQLが使用しない列にインデックスを作成しても無意味です。フィールドの一意性が高いほどcardinality値が高いことを意味し、インデックスのフィルタリング効果も高くなります。一般的なインデックス列のcardinalityレコード数は10%未満で、これが性別フィールドなどの効率の低いインデックスであると認識できます。
varcharフィールドにインデックスを作成する場合は、列全体で直接インデックスを作成するのではなく、インデックスの長さを指定することをお勧めします。通常、varchar列は長いため、特定の長さを指定してインデックスを作成することで、識別度を充分に高めることができます。列全体のインデックスを作成する必要はありません。列全体のインデックスを作成すると重くなり、インデックスのメンテナンスコストが増大します。 count(distinct left(列名, インデックスの長さ))/count(*)を使用して、インデックスの識別度を確認することができます。
冗長なインデックスを回避します。2つのインデックス(a, b) (a)が同時に存在する場合は、(a)が冗長なインデックスredundant indexに該当します。 クエリーのフィルタ条件がa列である場合は、(a, b)インデックスで足り、(a)インデックスを個別に作成する必要はありません。
カバリングインデックスを合理的に利用してIOオーバーヘッドを削減します。InnoDBではセカンダリインデックスのリーフノードは自身のキー値とプライマリキー値のみを格納します。SQLクエリーがインデックス列またはプライマリキーでない場合、このインデックスは最初に対応するプライマリキーを検出し、次にプライマリキーに従って、検出したい列を検出します。これがテーブルのリターンであり、追加のIOオーバーヘッドが発生します。この時、この問題を解決するために、カバリングインデックスを利用することができます。例えば、select a,b from xxx where a = xxxで、aがプライマリキーでない場合、aとb2つの列の複合インデックスを作成すれば、テーブルのリターンが行われません。
SQL作成仕様
注意事項
UPDATE、DELETE操作にはLIMITを使用せず、WHEREを使用して正確に整合させる必要があります。LIMITがランダムである場合は、この操作によってデータエラーが発生する恐れがあります。
INSERT INTO t_xxx VALUES(xxx)の使用は禁止されており、テーブル構造の変更によるデータエラーを回避するため、挿入された列の属性を明示的に指定する必要があります。
SQLセンテンスにおいて最も一般的なインデックスエラーを引き起こす状況に注意を払う必要があります。
例えば、インデックスaのタイプがvarcharであり、SQLセンテンスがwhere a = 1、 varcharをint に変更するなど、タイプを黙示的に変換する場合。
例えば、関数を使用して日付列をフォーマット処理するなど、インデックス列に対して数学的計算と関数等の操作を実行する場合。
join列の文字セットが統一されていない場合。
例えば、インデックスが(a,b)であり 、SQLセンテンスがorder by a b desclikeであるなど、複数の列の並べ替え順序が一致しない場合。
あいまい検索を使用する際、文字タイプxxx%の形式で一部のインデックスを作成できるが、他の状況ではいずれもインデックスを作成できない場合。
否定的なクエリー(not、!=、not inなど) が使用される場合。
推奨事項
必要に応じてリクエストし、select *を拒否して、次の問題を回避します。
インデックスのカバリング、テーブルのリターン操作、 I/Oの追加ができない。
メモリ負荷の増加、大量のコールドデータのinnodb_buffer_pool_sizeへの流入、クエリーヒット率の低下。
ネットワーク伝送のオーバーヘッドの増加。
大規模トランザクションを極力避け、大規模トランザクションを小さなトランザクションに分割し、マスター/スレーブの遅延を回避します。
業務コード内のトランザクションを速やかに行い、不要なロック待機を回避します。
複数テーブルではjoinの使用を少なくし、大きなテーブルではjoinを禁止します。2つのテーブルのjoinは小さなテーブルをドライバーテーブルとする必要があり、join列は文字セットが一致し、かつすべてにインデックスが作成されている必要があります。
LIMITページングの最適化、LIMIT 80000、10といった操作は、80010件のレコードを取り出してから、後の10件のレコードを返すため、データベースの負荷が極めて高くなります。したがって、例えばSELECT * FROM test WHERE id >= ( SELECT sql_no_cache id FROM test order by id LIMIT 80000,1 ) LIMIT 10 ;のように、ページングの前に最初のレコードの位置を確認することをお勧めします。
マルチレベルのサブクエリーが埋め込まれたSQLセンテンスを回避します。MySQL 5.5より前のクエリーオプティマイザはinをexistsに変更するため、インデックスを失効させる恐れがあり、外部テーブルが非常に大きい場合はパフォーマンスが低下します。
説明：
上述の状況を完全に回避することは困難であり、推奨されるソリューションは、これらの条件を主要なフィルタリング条件としないことです。インデックスを作成する際の主要なフィルタリング条件に従えば、大きな問題はありません。
モニタリングでフルテーブルスキャンの量がかなり多いことが判明しました。コンソールパラメータでlog_queries_not_using_indexesを設定し、しばらくすると低速ログファイル解析をダウンロードすることができますが、低速ログの急増を避けるため、長時間開いたままにしないでください。
業務のオンライン化前に、必要なSQL監査を実行します。日常の運用保守では、対象を絞った最適化を実施するため、スロークエリーログを定期的にダウンロードする必要があります。