共有が高く評価されました: 本番環境に沿った MySQL 最適化のアイデア-mysql チュートリアル-php.cn

まえがき

この記事を書く出発点は、仕事でデータの問題に対処する中で私が蓄積した経験を記録することです。インデックスを最適化する場合、スロークエリ、Explain、およびその他の関連機能について一定の理解が必要であるなど、その他の背景知識を提供する必要があります。たとえば、Elasticsearch を導入するには、データ同期の問題を解決したり、Elasticsearch の知識を学習したりする必要があります。記事が長いため、すべてのポイントを網羅することは不可能ですが、ビデオチュートリアルのように詳細に説明されているため、私の限られた知識といくつかの一般的なポイントに基づいて要約することしかできません。とはいえ、記事がかなり長くなってしまいましたので、気になる点がございましたら、Baidu/Google などで詳細をご確認ください。

この記事は非常に長いので、興味があれば最後まで読んでいただければ幸いです。数十分を無駄にしないでください。 [推奨学習: "mysql ビデオチュートリアル "]

考え方の視点

データベーステクノロジは、手動管理段階とファイルシステム段階を経てきました。これまでのところ、データベースシステムの段階です。

ソフトウェアシステムが存在しなかった初期の頃、特定のビジネスの現実世界の運営は、手作業による会計処理と口頭での契約という手動の管理段階を通じても実現できました。この形式は長い間存在し、比較的非効率な計画です。次の段階では、コンピューター技術の発展に伴い、手作業による会計処理を Excel テーブルに置き換えたファイルシステム段階があり、生産性がある程度向上しました。シンプル操作で高効率なデータベースシステムであるソフトウェアシステム段階では、生産性が再び向上し、現実世界の具体的な問題がデータに抽象化され、データの流れと変化によって現実世界のビジネスが表現されます。ソフトウェアシステムでは、データストレージは通常、リレーショナルデータベースと複数の非リレーショナルデータベースで構成されます。

データベースはシステムビジネスと密接に関連しています。そのため、プロダクトマネージャーはビジネスを設計する際に、データストレージとクエリのプロセスを理解する必要があります。設計の開始時に、ビジネスの変更がどのような影響を与えるかを明確にします。データベースに存在するかどうか、および新しいテクノロジースタックを参照する必要があるかどうかを確認します。例えば、プロダクトマネージャーが設計した業務は、単一テーブル数が数百万にも及ぶ複数のMySQLテーブルのデータを統計分析・集計するというもので、MySQLの複数テーブルクエリをそのまま使用すると、確実にクエリの低速化が発生し、msyqlエラーの原因となります。この場合の解決策は、製品側で妥協するか、テクノロジースタックを変更することです。

システムアーキテクチャとデータベースソリューションでは、会社のチームの能力により適したものを選択する必要があります。システムの初期段階では、紙幣機能を備えた単純なデータベースの最適化が最もコスト効率が高くなります。解決策はありますが、mysql データベースの紙幣機能となるとどうすることもできず、重要な機能に焦点を当てたソフトウェアサービスを導入することが最も費用対効果の高いソリューションになります。問題が発生したときに適切なソリューションを選択するのは、あなたの価値観を反映します。

貧しい少年は金持ちの少女と恋に落ちます。短期的な甘さは現実の階級的不平等に匹敵するものではありません。ハッピーエンドは貧しい少年の空想と瓊瑶先生のテレビシリーズの中にのみ存在します。

限られたコストでデータストレージのパフォーマンスを向上させる方法が、この記事の中心的なアイデアです。

予備知識

皆さんも日々の業務で以下の内容に触れる機会が多いと思いますので、簡単にまとめておきます。

リレーショナルデータベース

リレーショナルデータベースは、2 次元のテーブルとそれらの間の関係で構成されるデータ組織であり、トランザクションデータの一貫性を提供します。データの永続化などの機能は、ソフトウェアシステムのコアストレージサービスです。これらは、開発や面接中に最も頻繁に接触するデータベースです。一部の小規模なアウトソーシングプロジェクトでは、1 つの MySQL ですべてのビジネスニーズを満たすのに十分です。私たちがよく触れるものですが、実は裏技がたくさんあるので、次の章で詳しく解説していきます。
利点:

トランザクション
永続性
比較的一般的な SQL 言語

問題

ハードディスク I/O の要件は非常に高いです
大量のデータの集計クエリの効率は低いです
インデックスのミス
左端インデックスの一致原理により不一致が発生する全文検索に適している
#水平展開によるさまざまな問題への対応が難しい

非リレーショナルタイプデータベース - NoSqlMySQL データベースは、リレーショナルデータストレージソフトウェアとして、利点と明らかな欠点があるため、ソフトウェアシステムのデータ量が増加し続けると、ソフトウェアシステムの拡張とビジネスの複雑さは増大し続けるため、MySQL データベースの機能強化だけですべての問題を解決することは期待できず、代わりに、他のストレージソフトウェアの導入やさまざまなタイプの NoSQL の使用がソフトウェアシステムの拡張の問題を解決する必要があります。データ量とビジネスの複雑さの増大。

リレーショナルデータベースは、リレーショナルデータベースをさまざまなシナリオに最適化したものであり、何らかの NoSQL を導入すればすべてがうまくいくというわけではなく、市場にある NoSQL の種類と適用の難しさを十分に理解する必要があり、適切なシナリオで適切なストレージを選択するには、ソフトウェアが最適です。

Key-Value タイプビジネスでは、特定のテーブルの内容が頻繁にクエリされますが、クエリ結果のほとんどは変更されないため、Key-Value ストレージソフトウェア、主に Memcached と Redis が登場し、システム内のキャッシュモジュールで広く使用されています。 Redis は Memcached よりも多くのデータ構造と永続性を備えており、KV 型 NoSQL の中で最も広く使用されています。

検索タイプ全文検索のシナリオでは、MySQLB ツリーインデックスのクエリ最適化では、類似クエリはインデックスにヒットできず、類似キーワードクエリはすべて 1 つです。 time フルテーブルスキャンは、数万のデータを持つテーブルでサポートできますが、データが最後にあるときにクエリが遅くなります。ビジネスコードが適切に記述されておらず、トランザクション内で Like クエリが呼び出された場合、読み取りロックが発生します。が発生します。 ElasticSearch は、転置インデックスを核として、全文検索のシナリオに完璧に対応できると同時に、大規模なデータを非常によくサポートし、ドキュメントとエコロジーも非常に優れています。タイプ。

ドキュメントタイプドキュメントタイプ NoSql は、半構造化データをドキュメントとして保存する NoSql のタイプを指します。ドキュメントタイプ NoSql は通常、データを JSON または XML 形式で保存します。 , したがって、ドキュメント型 NoSql にはスキーマがありません。スキーマ機能がないため、自由にデータを保存したり読み込んだりすることができます。したがって、ドキュメント型 NoSql の登場により、リレーショナルデータベースのテーブル構造の拡張の不便さの問題が解決されます。著者は、

列式を一度も使用したことがありません。特定の規模の企業の場合、ビジネスにはリアルタイムで柔軟なデータの要約が含まれることが多く、これはビジネスには適していません。このような業務は事前に計算するという解決策を使って問題を解決しましょう事前に計算して集計するという解決策で業務を書くことができても、集計データの数が増えてくると、最終的に集計データを蓄積する作業が難しくなってしまいます。徐々に非常に遅くなります。カラムベースの NoSQL は、このシナリオの産物です。これは、ビッグデータ時代の最も代表的なテクノロジの 1 つです。最も一般的なのは HBase ですが、HBase のアプリケーションは非常に重く、多くの場合、完全なアプリケーションが必要です。実行する Hadoop エコシステムのセット著者の会社である Alibaba Cloud の AnalyticDB (MySql クエリステートメントと互換性のあるカラムストレージソフトウェア) が使用されています。サマリー列ストレージソフトウェアの強力なクエリ機能は、さまざまなリアルタイムで柔軟なデータサマリーサービスをサポートするのに十分です。

ケース 2021 年をノードとして考えると、ほとんどのシステムは初期段階で次の計画を立てて開始します。

共有が高く評価されました: 本番環境に沿った MySQL 最適化のアイデア #ハードウェアのアップグレードによってもたらされるメリットは、時間の経過とともに減少します。時間と人員が限られている場合、これが最速の最適化ソリューションです。ソフトウェアの最適化によってもたらされるメリットは将来的にはさらに大きくなりますが、必要な技術人材のレベルも将来的には高くなるため、時間と人員が許せば、最も費用対効果の高い最適化ソリューションとなります。ハードウェアとソフトウェアの最適化は相互に排他的ではなく、必要に応じて、両方を同時に MYSQL パフォーマンスの上限に近づけることができます。

共有が高く評価されました: 本番環境に沿った MySQL 最適化のアイデア

ハード最適化 - 資金能力

改善ディスク I/O については、SSD ディスクを使用してみてください (品質の向上)
- メモリを増やし、クエリキャッシュスペースを増やします
- CPU コアの数を増やし、実行スレッドを増やします
自己構築の mysql をサービスプロバイダーの mysql service に置き換えます
- 組み込みの読み取りと書き込みの分離機能を有効にする
- サービスプロバイダーの mysql サービスは、クラウドネイティブの分散データベースに置き換えられます
- 組み込みの読み取りと書き込みの分離機能を有効にする
- 組み込みのサブテーブルを有効にするfunction

ソフト最適化 - クエリ - OLTP

OLTP は主に、次のような特定の種類のビジネスイベントの発生を記録するために使用されます。ユーザーの行動として、その行動が発生すると、システムはユーザーがいつ、どこで何かをしたかを記録し、そのようなデータの行 (または複数の行) が追加、削除、変更の形でデータベース内で更新されます。これには次のことが必要です。高いリアルタイムパフォーマンス、強力な安定性、データがタイムリーに正常に更新されることを保証する一般的なビジネスシステムはすべて OLTP に属し、使用されるデータベースは MySlq、Oracle などのトランザクションデータベースです。 OLTP の場合、クエリ速度とサービスの安定性の向上が最適化の核心です

共有が高く評価されました: 本番環境に沿った MySQL 最適化のアイデア

スロークエリ
- スロークエリログを通じて効率の問題を伴う SQL を発見する
問題 SQL のトラブルシューティングの方向
- インデックス設計に問題があります
- SQL ステートメントに問題があります
- データベースのインデックス選択が間違っています
- 単一テーブルが大きいです
- #インデックスヒット状況の確認(要点)
##mysqlオプティマイザー
オプティマイザがインデックスを選択すると、インデックスのカーディナリティが参照されます。
- カーディナリティは MySQL によって自動的に維持および推定されるため、正確ではない可能性があります
- インデックスがヒットしない場合または、間違ったインデックスが使用されている場合は、オプティマイザステップに問題があります
- analyze でインデックス情報を再カウントし、ベース番号を再計算できます
- ##強制インデックス
- カバードインデックス - 最も理想的なヒットインデックス
- データクエリの場合複数のインデックスを使用しています。カバーインデックスではありません
- SQL ステートメントを最適化するか、結合インデックスを最適化できます。カバーインデックスを使用します
- #count() 関数
count(non-indexed field) - カバリングインデックスは使用できません。理論的には最も遅いです。
- count(1) - インデックスツリーのみをスキャンします。データ行を解析するプロセスはなく、理論的には高速ですが、それでも1 が null かどうかを判断します
- count(*) - MySQL は特に count(*) 関数を最適化し、インデックスツリー内のデータ数を直接返すようにします。最適
- ORDER BY
- 追加の並べ替えを最小限に抑え、where 条件を指定します
- インデックスカバレッジでは、中間結果セットの生成をスキップし、クエリ結果を直接出力できます
- ORDER フィールドにはインデックスが付けられ、WHERE 条件および出力と一致している必要があります。内容は次のとおりです。すべて同じインデックス内にある
- - ページングクエリ
- インデックスプッシュダウン
- KEY
store_id_guide_id
store_id)
- guide_id) BTREEEselect * from table where store_id in (1,2) and guide_id = 3;MySQL5.6 より前では、次のことが必要です。インデックスを使用して (1,2) の store_id をクエリし、すべてのテーブルを追加して film_id = 3
- # を直接使用します。
- ##緩いインデックススキャン
- KEY
(
,
- ) BTREE を使用select film_id from table where guide_id = 3 MySQL8.0 の新機能緩やかなインデックススキャンは「左手の原則」を破り、主要な兄弟を失う問題を解決できます
- 結合インデックスの効率よりも効率が低くなります
- 関数操作
- インデックスフィールドに対して関数操作を実行すると、オプティマイザはインデックスを放棄します。
この状況には、時間関数、文字列から数値への変換、文字エンコードの変換が含まれる可能性があります
mysql 関数を置き換えるためにサーバー側ロジックの使用を最適化します
- #単一テーブルのサイズが大きすぎます
- mysql をアップグレードすると、異なる mysql ソフトウェアがサポートできる単一テーブルのサイズが異なります。私の現在の経験に基づくと、単一テーブルの場合にヒットインデックスをクエリするのは問題ありません。 Alibaba Cloudのテーブルのpolardbクラスタバージョンは2億(高優先)
- データ決済 - パイプラインデータなどの決済が可能ある時点での決済により最新の値を取得し、決済されたフローをバックアップテーブル（中優先度）
- テーブル分割を実装するコード - PHP や GO のほとんどのフレームワーク ORM で分割した後、特定のルールに従って 1 つのテーブルを複数のテーブルに分割しますフレームワーク ORM に特定の変更を加える必要があります。 JAVA にはネイティブサポートがあります。プロジェクトの初期段階で検討することをお勧めします。後ほど難しくなります (優先度が低くなります)
- ソフト最適化 - 書き込み更新削除

ロック

共有が高く評価されました: 本番環境に沿った MySQL 最適化のアイデア

粒度に従って、MySQL ロックは次のように分割できます。グローバルロック、テーブルレベルのロック、行ロック

##グローバルロック
##fromgoogle/baidu
- ##テーブルレベルのロックは、テーブルロック (データロック) とメタデータロックに分けられます
- テーブルロック
- 自google/baidu
  - - ##行ロックはデータ行をロックし、共有ロックと排他ロックに分けられます
  - Google/baidu
- - パラメータ構成
    - innodb_lock_wait_timeout パラメータを調整します
      - デフォルトは 50 秒です。つまり、50 秒待機してもロックが取得されない場合、現在のステートメントがレポートします。エラー
      - 待機時間が長くなった場合は、このパラメータを適切に短くすることができます
    - アクティブなデッドロック検出: innodb_deadlock_detect
      - コストの低いトランザクションをロールバックします。デッドロックが見つかりました
      - デフォルトで有効になります
  - 必要ない場合はトランザクションを開かないでください
  - 配置してみてくださいロックされた行の数を減らすために、トランザクションの外でクエリを実行します。
  - トランザクション時間が長すぎるため、トランザクション内で http リクエストをトリガーしないでください。
検索ビジネス
Elasticsearchの転置インデックスは全文検索に適していますが、データ構造の柔軟性に欠けます。
#データ同期
- ビジネスコードがデータを変更すると、同時に Elasticsearch に同期されます
  - Canel サブスクリプションの mysql ログが同期をトリガーします
  Elasticsearch-index
- は、同じフィールドを持つドキュメントのリストで構成されます。mysql のテーブルに似ています。
  - フィールドタイプが設定されると、変更は禁止され、新しいものになります。フィールドは許可されます
  - Specific このメソッドは独自の google/baidu
  Elasticsearch-Document
- es に保存されたユーザーのデータドキュメント - 次のようなものです。 mysql
  - の行はメタデータと Json オブジェクトの構成で構成されます
  - メタデータと Json オブジェクトの詳細は google/baidu
  Elasticsearch-Word によって提供されますセグメンター
- by Google/baidu
  Elasticsearch-Inverted Index (キーポイント)
- fromgoogle/baidu
  - ##Elasticsearch - 集計分析
統計ビジネス - OLAP
OLAP を使用OLTP トランザクション処理シナリオに関連したデータの意思決定分析用です。これはビッグデータ分析で使用されるオフラインデータウェアハウスのアイデアであり、特定のテクノロジスタックではありません。ソリューションが OLAP 分析と処理のアイデアを具体化できる場合、解決策はOLAPです。
初期のデータウェアハウス構築とは、主に、意思決定分析の要件に従って、ERP、CRM、SCM、その他のデータなどのエンタープライズビジネスデータベースをモデル化し、データウェアハウスエンジンに要約することを指します。そのアプリケーションは主にレポート作成です。経営者や事業担当者の意思決定（中長期的な戦略的意思決定）を支援することを目的としています。 IT技術のインターネット化やモビリティ化に伴い、データソースはますます豊富になり、Webサイトのログ、IoTデバイスのデータ、APPの埋め込みデータなど、元のビジネスデータベースを基にした非構造化データが出現します。これは、以前の構造化データよりも数桁大きくなっています。
OLAP が直面するビジネスがどのように変化しても、分析フィールドの決定 -> ビジネスデータをコンピューティングライブラリに同期 -> データクリーニングモデリング -> データベースとの同期データウェアハウス - > 外部への公開
計算元データベースはデータクリーニングに特化して使用されており、データクリーニング中に業務データベースのパフォーマンスへの影響を避けることが目的です。業務やディメンションに応じて計算元データベース内のデータをクリーンアップすることで、データの有用性と再利用性が向上し、最終的なリアルタイムの詳細データが取得され、データウェアハウスに転送され、データウェアハウスが提供する最終的な意思決定の分析データ。
デモプラン

本番プラン

各リンクのソフトウェアは同じ機能を使用できますソフトウェアがチームの最も信頼できるソフトウェア実装ソリューションに置き換えられる場合、そのソリューションは OLAP です。

概要
最適化は、能力を段階的に蓄積し、複数回の繰り返しを行う、現実的なものでなければならず、一夜にして達成できるものではありません。独自の基盤、ビジネスシナリオ、将来の開発の期待に基づいて、複数回の反復を実施します。
反復の原則は、まず単一のソフトウェアサービスのソフト最適化とハード最適化を通じてソフトウェアの効率を向上させることです。将来の開発期待に基づいて、最適化コストが利益よりも低い場合は、成熟したサービスを参照してください。市場にあるソリューションを参照し、そのソリューションに従う組み合わせたイノベーションのために必要に応じて新しいソフトウェアを導入し、ブラインドコピーを避ける有機的な統合を通じてのみ、1 1>2、2 1>3 の効果を達成できる参照されたソフトウェアがボトルネックに遭遇した場合、このプロセスを繰り返します。
お読みいただきありがとうございます。以上が記事の内容です。内容で提案されている最適化ポイントや解決策は、必ずしも最適解であるとは限りません。個人の業務におけるベストプラクティスです。さまざまな意見がありますが、それについて話し合うことは歓迎です。