mysqlチューニング--どのいくつかの面から着手します

概要
一般的にmysqlチューニングに関連し、ハードウェア、mysqlシステム構成、テーブル構造最適化、sql文、インデックスのいくつかの面から着手することができます.次に、各方面で私たちが何ができるかを簡単に分析します.sql文とインデックスは私たちが最適化する最も一般的な手段であり、他の文章に記載されています.ここでは主に他の3つの方面を分析します.
ハードウェア
デルのビジネスでよく見られるシーン

データベースデータ量大

毎回アクセスする必要があるデータ量は

より小さい.

クライアントはデータベースと頻繁に対話する

データの離散度が高い

合併度が高い

この場合、強力なcpuを選択し、頻繁にインタラクティブなネットワークデバイスに耐えられる.
もう1つの一般的なビジネスシーンは、レポート統計などの機能に使用されるデータ・ウェアハウスです.特徴は

データ量超大

アクセスあたりのデータ量が大きい

クライアントとデータベースのインタラクション回数が少ない

合併度が低い

そのため、ハードウェアを選択するとcpuに対する要求は比較的低いが、ハードディスクの容量が大きく、レポートの統計計算時間が長く、クラスタ配置を行い、統計タスクを複数のサブタスクに分割して並列統計を行うこともできるかもしれない.
mysqlシステム構成
配置の最適化に対して、実は高性能mysqlという本の中で分析があります.次によくあるものをいくつか見てみましょう.

query_cache_sizeクエリーキャッシュサイズホットスポットデータをキャッシュすると、いくつかのクエリーの効率が向上し、適切なサイズ構成でより多くのデータをキャッシュし、クエリーの効率を向上させることができます.

sort_buffer_sizeソート操作が必要な場合は、ソートのために指定したサイズのメモリを割り当てます.小さすぎるとディスクioが必要になり、パフォーマンスが低下します.しかし、この変数は勝手に大きく設定することはできません.一般的には、プロファイルで小さく設定し、実際に大きなソートスペースが必要な場合は、sqlを実行するときに、以下の文を加えて個別にサイズを設定すればいいです.

set @@session.sort_buffer_size := ；
//    
set @@session.sort_buffer_size := DEFAULT

read_buffer_szieクエリー時にこのサイズのメモリをキャッシュとして割り当て、

を一度に割り当てる

join_buffer_size:テーブル関連バッファは、グローバル値を設定したり、スレッドごとに個別に設定したりすることができます.同様に、関連バッファが小さすぎてディスクioをもたらし、パフォーマンスが低下する可能性があります.

一般的に、構成の変更は常に変更する必要はありません.最適化するには、テーブル構造の最適化とsqlインデックスの最適化を完了してから、構成の最適化を考慮します.構成の変更は慎重にしてください.
テーブル構造の最適化

データ型最適化.これは、ビジネスニーズに応じて適切なデータ型を選択することで、ストレージ領域の使用を大幅に削減し、ディスクの読み書き効率をコミットできることが最も明らかです.

は1つのテーブルの中の不要な列を減らして、mysqlストレージエンジンapiは仕事をする時1つの行列の変換の過程に関連して、多すぎる列はmysqlストレージエンジンの仕事の代価を高めて、cpuの負荷は著しく向上します.

関連付けが多すぎて、「エンティティ-属性-値」の設計モードで、クエリーの際にテーブルを関連付けすぎて、クエリーのパフォーマンスに影響を与える必要があります.関連を著しく減らすことができれば,許容範囲内でテーブル内のカラムを冗長化し,関連を減らすことができる.

物化ビュー、事前に統計して私たちが必要とするホットスポットクエリーをクエリーして、私たちはクエリーの時にビューをクエリーするだけで、背後の実際のクエリーの仕事はビューで完成します.

sql文およびインデックス
インデックスが有効な場合は、次のようになります.

全値が

に正確に一致する

最左接頭辞

に一致する.

一致列接頭辞

整合範囲値

は、ある列(最も左)に正確に一致し、別の列の一致範囲

である.

インデックスのみにアクセスするクエリー(インデックスを上書き)

インデックスのメリット

インデックスは、サーバがスキャンする必要があるデータ量

を大幅に削減することができる.

インデックスは、サーバがソートおよびテンポラリ・テーブル

を回避するのに役立ちます.

インデックスは、ランダムioを順序io

に変更することができる.
≪単一列索引｜Single Column Index｜oem_src≫:共通および取得、分散性が高く、適切な長さの列が望ましい
≪複数カラム・インデックス｜Multi-Column Index｜oem_src≫:適切な結合インデックスを作成します.複数の単一カラム・インデックスを頻繁に取得する場合は、結合インデックスを考慮する必要があります.結合インデックスのカラム順序は、選択性の高いものを優先します.
クラスタインデックス
メリット

データへのアクセスが速く、インデックスツリーリーフノードから直接データを取得します.

カバーインデックススキャンは、ページノードの下のプライマリキー値

を直接使用することができる.

データを集約することで、ディスクio

を削減できます.
欠点

はio密集型アプリケーションの性能を向上させたが、データがすべてメモリにある場合、

の優位性はない.

挿入性能は挿入順序に大きく依存し、プライマリ・キーの順序挿入が最良の方法である.そうしないと、クラスタ・インデックス・ツリーの構造調整コストが高い

.

クラスタインデックスを更新するコストが高い

二次インデックスは、

より大きい可能性があります.

二次インデックス検索には二次

を取得する必要がある.
インデックスの上書き

は二次インデックスにアクセスするだけで、データにアクセスする必要はありません.二次インデックスはクラスタインデックスツリーよりはるかに小さく、メモリに入れやすく、データアクセス量を大幅に削減します.

io密集型の範囲クエリでは、io回数はランダム読み出しよりも

少ない.

explainの場合、extraがusing indexの場合、上書きインデックスが使用されていることを示します.

特定の文の最適化
mysqlには、いくつかの特定の文が、ソケットに従って最適化され、パフォーマンスを向上させることができます.

count文.近似値をビジネス上で使用できるかどうかを見て、できればexplainのrowsやプログラムを使ってcountをキャッシュタイミングリフレッシュすることができます.myisamエンジン統計全テーブルcount(*)は、統計を再スキャンする必要がないため、この値を利用して差分演算を行い、統計にスキャンする行数を減らすことができます.

関連クエリーの最適化.関連付けられた列には必ずインデックスが作成されます.グループbyフィールドとorder byフィールドが2つのテーブルに分散されていないことを確認します.そうしないと、インデックスを使用してソートを完了できません.

groupbyとdistinct最適化.group byとdistinctの場合、order byを明示的に使用してソートシーケンスを指定しないとmysqlはgroup byとdistinct列を使用してソートし、group byとdistinctが順序に関心がない場合はorder by nullを使用してソートを行わず、filesortが発生しないようにすることができます.

limitページングを最適化します.limitページングでは、スキャンオフセットサイズのデータを使用し、部分的にスクリーンショットするだけで、パフォーマンスが非常に消費されます.以下の方法を試してみてください.

は、上書きインデックスサブクエリを使用してページを分割し、条件を満たすプライマリ・キーidをクエリし、元のテーブルに関連付けてすべてのデータをクエリし、上書きインデックス・スキャンはスキャン・ページを大幅に削減します.

は、前のページの最終位置idを記録し、次のページを検索する際に、直接範囲クエリを使用して、このidからデータの検索を開始します.