English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
データベース最適化には多くの話題があります。データ量に応じて、2つの段階に分けられます：単機データベースとデータベースの分離やテーブルの分離。前者は一般的に500Wまたは10G未満のデータでは問題ありませんが、この値を超える場合には、データベースの分離やテーブルの分離を考慮する必要があります。また、大企業の面接では、通常、単機データベースから始め、分離やテーブルの分離に至るまで一貫して質問されます。多くのデータベース最適化の問題が含まれます。この記事では、単機データベースの最適化の一部の実践を説明しようと試みます。データベースはMySQLに基づいており、不合理な点があれば、ご指摘ください。
1、テーブル構造の最適化
アプリケーションを開始する際には、データベースのテーブル構造の設計は、特にユーザー数が増えた後のパフォーマンスに影響を与えます。したがって、テーブル構造の最適化は非常に重要なステップです。
1.1、文字セット
一般的にはUTFを選択するのが望ましいです。-8、中間保存時にはGBKがUTFよりも-8使用するストレージスペースは少ないですが、UTF-8各国の言語に対応していますが、このようなストレージスペースのために拡張性を犠牲にする必要はありません。実際には、後でGBKからUTFに変換する必要がある場合、-8大きな犠牲が必要で、データ移行が必要であり、ストレージスペースはお金をかけてハードディスクを拡張することで解決できます。
1.2、主キー
MySQLのInnoDBを使用する場合、InnoDBの下層のストレージモデルはB+木は、主キーをクラスタインデックスとして使用し、挿入データを葉のノードとして使用し、主キーを通じて葉のノードを素早く見つけることができます。したがって、テーブルの設計では、主キーを追加する必要があり、さらに自增が望ましいです。自增主キーは、データを主キーの順序に挿入するための下層のB+木の葉のノードでは、順序に基づいているため、既存の他のデータを移動する必要がほとんどなく、挿入効率が非常に高いです。もし主キーが自動増分でない場合、主キーの値は近似的にランダムで、その場合、Bを確保するために大量のデータを移動する必要があります。+木の特性は、不必要なコストを増やします。
1.3、フィールド
1.3.1、インデックスが作成されたフィールドはnot null制約を追加し、デフォルト値を設定する必要があります。
1.3.2、float、doubleを使用して小数を保存することは避け、精度の損失を防ぐためにdecimalを使用することをお勧めします。
1.3.3、Textを使用することは避けます。/blobを使用して大量のデータを保存します。大テキストの読み書きは大きなI/Oコストがかかり、MySQLのキャッシュを占有し、高并发ではデータベースのスループットが大幅に低下するため、大テキストデータは専用のファイルストレージシステムに保存することをお勧めします。MySQLでは、ファイルのアクセス先の相対パスのみを保存します。例えば、ブログ記事はファイルに保存され、MySQLではファイルの相対パスのみを保存します。
1.3.4、varchar型の長さは8Kを使用します。
1.3.5、時間型はDatetimeを使用することをお勧めします。timestampを使用することは避け、Datetimeは8バイト、一方、timestampは4バイト、しかし後者は空であることを保証し、時区に敏感です。
1.3.6、テーブルにgmt_createとgmt_modifiedの2つのフィールドを追加することをお勧めします。これらのフィールドが作成された理由は、問題を簡単に調べるためです。
1.4、インデックスの作成
1.4.1、この段階ではビジネスに詳しくないため、盲目的にインデックスを追加することは避け、必ず使用されるフィールドにのみ通常のインデックスを追加してください。
1.4.2、innodb単列インデックスの長さは767bytes、もしそれを超える場合は、前で使用されます255bytesとしてプレフィックスインデックス
1.4.3、innodb組み合わせインデックスの各列インデックス長さは767bytes、合計で3072bytes
2、SQL最適化
一般的にはSQLは以下の通りです：基本的なCRUD（増加、削除、更新、検索）、ページングクエリ、範囲クエリ、模糊検索、マルチテーブル結合
2.1、基本的なクエリ
一般的なクエリはインデックスを通じて実行する必要があります。インデックスがない場合はクエリを修正し、インデックスがあるフィールドを追加してください。このビジネスシーンではこのフィールドを使用できない場合は、このクエリの呼び出し回数が大きいかどうかを見てください。もし大きければ、例えば毎日呼び出される10W+、これには新しい索引が必要です。もし小さければ、例えば毎日呼び出される100+、その場合、そのままにすることを検討してください。また、select * できるだけ少なく使用し、必要なフィールドがある場合はSQL文に追加し、不要なフィールドは調べないでください。I/Oとメモリ空間。
2.2、効率的なページング
limit m,nの本質はまずlimit m+行数n、次にm行目からn行を取得します。これにより、limitでページを遅くに越えるとmが大きくなり、パフォーマンスが低下します。例えば
select * from A limit 100000,10、このSQL文のパフォーマンスは非常に悪いです。以下のバージョンに変更することをお勧めします:
select id,name,age from A where id >=(select id from A limit 100000,1) limit 10
2.3、範囲クエリ
範囲クエリにはbetween、大きい、小さいおよびinが含まれます。MySQLのinクエリの条件には数の制限があり、数が少ない場合は索引クエリが通過できますが、数が多い場合は全テーブルスキャンになります。また、between、大きい、小さいなどのクエリは索引を通過しませんので、索引を通過するクエリ条件の後に配置することをお勧めします。
2.4、模糊クエリlike
like %name%のような文は索引を通過しません。全テーブルスキャンに相当し、データ量が少ない場合には大きな問題はありませんが、データ量が多い場合にはパフォーマンスが大幅に低下します。データ量が多い場合には、このような模糊検索の代わりに検索エンジンを使用することをお勧めし、必要でない場合は、索引を通過できる条件を模糊クエリの前に追加することをお勧めします。
2.5、多テーブルジョイン
サブクエリとジョインは、複数のテーブル間からデータを取得できるですが、サブクエリのパフォーマンスは悪いため、サブクエリをジョインに変更することをお勧めします。MySQLのジョインはNested Loop Joinアルゴリズムを使用しており、前のテーブルの結果セットを使用して次のテーブルにクエリを行います。例えば、前のテーブルの結果セットは100件のデータ、次のテーブルには10Wデータがある場合、以下の100*10Wのデータセットから最終結果セットをフィルタリングして取得するため、小さな結果セットのテーブルを使用して大テーブルとジョインし、ジョインのフィールドに索引を設定することをお勧めします。索引を設定できない場合は、十分な大きさのジョインバッファサイズを設定する必要があります。もし以上のテクニックでもジョインによるパフォーマンス低下の問題を解決できない場合は、ジョインは使用しない方が良いでしょう。一度のジョインクエリを2回の簡単なクエリに分割します。また、多テーブルジョインは3テーブルを超えないようにし、3テーブルを超えると一般的にパフォーマンスが悪くなるため、SQLを分割することをお勧めします。
3、データベース接続プールの最適化
データベース接続プールは本質的にキャッシュであり、高並行処理に耐える手段です。データベース接続プールの最適化は、パラメータの最適化が主であり、一般的にはDBCP接続プールを使用し、具体的なパラメータは以下の通りです：
3.1 initialSize
初期接続数は、ここでの初期はgetConnectionの最初の呼び出しを指し、アプリケーションの起動時とは異なります。初期値は、並行量の過去の平均値に設定できます
3.2、minIdle
最小で保持する空き接続数です。DBCPはバックグラウンドで空き接続を回収するスレッドを開始し、このスレッドが空き接続を回収する際には、minIdle個の接続数を保持します。一般的には5、並行量が非常に小さい場合には、1.
3.3、maxIdle
最大で保持する空き接続数は、ビジネスの並行ピークに設定されます。例えば、並行ピークが20、それでは、ピークが過ぎたらすぐにリソースが回収されないため、少し時間が経って再びピークが来たら、接続プールはこれらの空き接続を再利用して、接続の作成と閉じる頻度を減らすことができます。
3.4、maxActive
最大アクティブ接続数、受け入れ可能な並行度の極限に基づいて設定します。例えば、単一インスタンスの並行度の極限が受け入れ可能な場合100、その場合、maxActiveを100秒後、同時に100つのリクエストをサービスする、余分なリクエストは最大待機時間後は捨てられます。この値は設定する必要があります。これは悪意のある並行攻撃を防ぎ、データベースを保護します。
3.5、maxWait
接続を取得する最大待機時間、短めに設定することをお勧めします。例えば3秒、これによりリクエストが迅速に失敗するようにします。なぜなら、リクエストが接続を取得する間にスレッドは解放されないため、単一のインスタンスのスレッドの並行処理量は限られています。この時間が長すぎると、例えばオンラインで推奨されるように60秒、その場合、このスレッドはこの60秒以内では解放できないため、このようなリクエストが増えると、アプリケーションの利用可能なスレッドが少なくなり、サービスが利用できなくなることがあります。
3.6、minEvictableIdleTimeMillis
リンクがアイドル状態であれば、リサイクルされない時間の長さ、デフォルト30分。
3.7、validationQuery
リンクが有効かどうかを確認するためのSQL文を設定します。一般的にはシンプルなSQL文です。設定することをお勧めします。
3.8、testOnBorrow
リンクを申請する際にリンクをチェックする、この機能は有効にすることをお勧めしません。パフォーマンスに非常に悪い影響を与えます。
3.9、testOnReturn
リンクを返却する際にリンクをチェックする、この機能は有効にすることをお勧めしません。パフォーマンスに非常に悪い影響を与えます。
3.10、testWhileIdle
有効にすると、バックグラウンドのリンククリアスレッドは定期的にアイドル接続に対してvalidateObjectを実行し、接続が無効であればクリアします。パフォーマンスに影響を与えず、有効にすることをお勧めします。
3.11、numTestsPerEvictionRun
一度にチェックするリンクの数を表します。maxActiveと同じ値に設定することをお勧めします。これにより、一度にすべてのリンクを効果的にチェックできます。
3.12、リソースプールの予熱
リソースプールについては、アプリケーションを起動した際に予熱することをお勧めします。外部からのアクセスが提供される前に簡単なSQLクエリを実行し、リソースプールに必要な接続数を満たします。
4、インデックス最適化
データ量が一定の程度に達すると、SQLの最適化でパフォーマンスを向上させることはできません。その場合、大規模な対策を講じる必要があります：インデックスです。インデックスは3階層ありますが、一般的にはこれら3階層を理解するだけで十分です。また、インデックスを構築するフィールドについては、その選択性を考慮する必要があります。
4.1、一級インデックス
WHEREの後の条件にインデックスを構築すると、単一の列には通常のインデックスを構築し、複数の列には組み合わせインデックスを構築します。組み合わせインデックスでは最左前缀原則に注意してください。
4.2、二級インデックス
もしORDER BYやGROUP BYで使用されるフィールドがある場合、そのフィールドにインデックスを構築することを検討することができます。これにより、インデックスがもともと並び順を持っているため、ORDER BYおよびGROUP BYで引き起こされるソートを避け、パフォーマンスを向上させることができます。
4.3、三階層インデックス
上記の方法が効果がない場合は、クエリするフィールドにもインデックスを追加して、これにより、インデックスコVERAGEが形成されます。これにより、I/O オペレーション、なぜなら MySQL がデータをクエリする際には、まず主キーインデックスを検索し、その後、主キーインデックスに基づいて普通インデックスを検索し、最後に普通インデックスに基づいて対応するレコードを検索するからです。必要なレコードが普通インデックスにすべて含まれている場合、第三ステップは必要ありません。もちろん、このようなインデックス作成方法は非常に極端であり、一般的なシナリオには適していません。
4.4、インデックスの選択性
インデックスを作成する際には、選択性の高いフィールドに重点を置いてください。選択性が高いとは、このフィールドを通じて検索されるデータ量が少ないことを言います。例えば、名前で人々の情報を検索すると、検索されるデータ量は一般的に少なく、性別で検索すると、データベースの半分のデータが検索される可能性があります。したがって、名前は選択性の高いフィールドであり、性別は選択性の低いフィールドです。
5、歴史データアーカイブ
データ量が1年で増加500W条の時点で、インデックスも無力化されます。この時の一般的なアプローチは、データが急激に増加していない場合でも、データが徐々に増加している場合には、複雑な技術手段の分庁分表を考慮せずに、歴史データのアーカイブを行うことです。例えば、ライフサイクルが終了した歴史データに対して、6ヶ月前のデータをアーカイブします。クワーズのスケジュールタスクを使用して、夜中に定期的に6ヶ月前のデータを抽出し、リモートのhbaseサーバーに保存します。もちろん、必要に応じて歴史データのクエリインターフェースも提供する必要があります。
これはmysql 単機データベースの最適化に関する情報の整理です。今後も関連する情報を追加していく予定です。皆様のこのサイトへのサポートに感謝します。
声明：この記事の内容はインターネットから取得しており、著作権者に帰属します。インターネットユーザーが自発的に貢献し、自己でアップロードしたものであり、このサイトは所有権を持ちません。また、人工的な編集もなく、関連する法的責任も負いません。著作権侵害を疑う内容がある場合は、メールを送信して：notice#oldtoolbag.com（メールを送信する際には、#を@に置き換えてください。報告を行い、関連する証拠を提供してください。一旦確認がついたら、このサイトは即座に侵害を疑われるコンテンツを削除します。）