MySQL 最適化のアイデアを共有する

1. 全体的な最適化のアイデア

まず、クエリ数、接続数、その他のデータを観察するスクリプトを構築し、環境上の理由と内部 SQL 実行の理由を特定します。特定の理由に従って特定の処理を実行します。

推奨: 「mysql ビデオ チュートリアル 」

2. ビルド スクリプトの観察ステータス

mysqladmin -uroot -p  ext \G

このコマンドは、現在のクエリ数などの情報を取得し、定期的にポーリングして結果をテキストにリダイレクトし、結果をグラフに処理します。

3. 解決策

1. クエリが定期的に表示されるのが遅い場合は、キャッシュ雪崩の問題を検討してください。

この問題では、キャッシュの有効期限が同じ時刻に期限切れにならないように、キャッシュの有効期限をできるだけ離散的に設定するか、午前 0 時までに集中して設定するだけで済みます。

2. 非正規クエリが遅い場合は、設計が最適化されていないことを考慮してください

処理方法:

a: プロファイリングを有効にしてクエリ操作を記録し、ステートメントの実行を取得します詳細

show variables like '%profiling%';
set profiling=on;
select count(*) from user; 
show profiles;
show profile for query 1;
>>>
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000060 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000049 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000192 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000014 |
| preparing                      | 0.000017 |
| executing                      | 0.001111 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000015 |
| closing tables                 | 0.000011 |
| freeing items                  | 0.000085 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+

b: Explain を使用して、ステートメントの実行、インデックスの使用状況、スキャン範囲などを表示します。

mysql> explain select count(*) from goods \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: index   
possible_keys: NULL
          key: gid
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index

c: 関連する最適化手法

テーブルの最適化と列タイプの選択

列選択の原則:

1: フィールド タイプの優先順位 integer > date, time > char,varchar > blob

理由: 整数型、時間演算が高速でスペースの節約

char/varchar はソート時に文字セットと校正セットの変換を考慮する必要があり、遅い

blob は使用できませんメモリ一時テーブル

2: 十分な量を使用し、寛大にしないでください (smallint、varchar(N) など)

理由: 大きなフィールドはメモリを無駄にし、速度に影響します

varchar(10) を使用し、varchar( 300) は同じ内容を保存しますが、テーブルをクエリする場合、varchar(300) はより多くのメモリを必要とします

3: NULL の使用は避けてください

理由: NULL はインデックス作成に適さないため、特殊バイトを使用してマークします。

ディスク上で占有されるスペースは実際にはさらに大きくなります

インデックス最適化戦略

1. インデックスの種類

1.1 B ツリー インデックス (ソートされた高速検索構造)

注: Myisam、innodb では、B ツリー インデックスがデフォルトで使用されます

1.2ハッシュ インデックス

メモリ テーブルでは、デフォルトはハッシュ インデックスであり、ハッシュの理論的なクエリ時間レビュー次数は O(1)

質問: ハッシュ インデックスは非常に効率的であるため、なぜそうしないのですかこれを使って？

ハッシュ関数の計算結果はランダムです。データがディスク上に配置されている場合、例として主キーを ID として、ID が増加するにつれて、その ID に対応する行がランダムに配置されます。ディスク上に配置されます。

b. 範囲クエリを最適化できない

c. プレフィックス インデックスを使用できない (たとえば、B ツリーでは、フィールド列の値が「helloworld」で、インデックス クエリがxx=hello/xx =helloworld はインデックス (左側のプレフィックス インデックス) を使用できますが、ハッシュ インデックスは使用できません。hash(hello) と hash(helloworld) に関連性がないためです。

d. 並べ替えも最適化できません

e. 行を返す必要があり、インデックスを通じてデータの場所を取得し、データをテーブルに返す必要があります。

2.b-tree インデックスに関するよくある誤解

2.1 where 条件でよく使用される列にインデックスを追加する

例: where cat_id=3 andprice>100; //3 番目の列をクエリします。 、100 元以上 製品

は正しくありません。cat_id とprice の両方がインデックス化されています。実際、使用できるインデックスは 1 つだけであり、それらはすべて独立したインデックスです。

2.2 複数の列にインデックスを作成した後は、どの列がクエリされてもインデックスは機能します。

2.2複数列のインデックス インデックス作成後、どの列がクエリされるかに関係なく、インデックスが役割を果たします。

正解: 複数列インデックスが機能するには、インデックスが左側のプレフィックス要件 (階層化) を満たす必要があります。例:

语句 索引是否发挥作用
where a=3 是
where a=3 and b=5 是
where a=3 and b=5 and c=4 是
where b=3 or where c=4 否
where a=3 and c=4 a列能发挥索引作用，c列不能
where a=3 and b>10 and c=7 a,b能发挥索引作用，c列不能

高パフォーマンスのインデックス戦略

1. innodb の場合、ノード配下にデータファイルがあるとノードの分割が遅くなりますので、innodbの主キーは整数型を使用するようにして、増加する整数型にしてください。

2. インデックスの長さは、インデックス ファイルのサイズに直接影響し、追加、削除、変更の速度に影響し、間接的にクエリの速度 (より多くのメモリを消費する) に影響します。

3. 列の値について、左から右に部分を切り取ってインデックスを構築します。

a. 切り捨てが短いほど、繰り返しの度合いが高くなり、区別が小さくなり、インデックス効果が悪くなります

b. 切り捨てが長いほど、識別は改善されますが、インデックス ファイルが大きくなる 速度に影響する

したがって、パフォーマンスを最大化するために長さのバランス ポイントを見つけてみてください。一般的な方法: インデックスの区別をテストするために異なる長さをインターセプトします。

識別テスト:

select count(distinct left(word, 1)) / count(*) from table;

テストが完了したら、テストから得られた最適な長さに基づいてインデックスを作成できます

alter table table_name add index word(word(4));

理想的なインデックス

1. よくあるクエリ

2. 識別性が高い

3. 長さが小さい

4. 一般的なクエリ フィールドをカバーするように努める

以上がMySQL 最適化のアイデアを共有するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。