Mysql の最適化に関する 15 の問題を選択してまとめました

この記事では、mysql に関する関連知識を提供し、開発プロセス中の SQL ステートメントのトラブルシューティング方法や運用環境のトラブルシューティング方法など、主に SQL の最適化に関連する問題を紹介します。 SQLの問題など、皆さんのお役に立てれば幸いです。

推奨学習: mysql ビデオ チュートリアル

開発プロセス中に SQL のトラブルシューティングを行うにはどうすればよいですか?

トラブルシューティングのアイデア

ほとんどのプログラマにとって、開発プロセス中の SQL のトラブルシューティングは基本的に空白です。しかし、業界の関与に伴い、開発プロセスにはますます多くの注意と専門性が払われており、その 1 つは、運用中に SQL の問題が露呈するのを避けるために、開発プロセス中に可能な限り SQL の問題を解決することです。では、開発プロセス中にプログラムの SQL トラブルシューティングを簡単に実行するにはどうすればよいでしょうか?

目標は、引き続き Mysql のスロー ログを使用して次のことを実現することです。

• まず、開発プロセス中に、データベースのスロー クエリを有効にする必要もあります。 Mysql

  SET GLOBAL slow_query_log='on';

• 次に、遅い SQL の最小時間を設定します

  注: ここでの時間単位は s 秒ですが、6 秒あります。小数点以下の桁数があるため、微妙な時間の強さを表現できます。一般に、単一テーブルの SQL 実行時間は 20 ミリ秒以内であることが望ましいです。逆に、開発プロセスでは、実行する SQL ステートメントが 20 ミリ秒を超える場合は、料金を支払う必要があると理解されています。それに注意してください。

  SET GLOBAL long_query_time=0.02;

• 操作の便宜のため、遅い SQL をファイルではなくテーブルに記録できます

  SET GLOBAL log_output='TABLE';

• 最後に、記録された遅い SQL は、mysql.slow_log テーブルを通じてクエリできます。

全員向けに開発されたツールを使用する

Brother Yong 著 このソフトウェアには、ワンクリックで上記の機能を素早く実装できるグラフィカル インターフェイスも用意されています。

実稼働環境で SQL の問題をトラブルシューティングするにはどうすればよいですか?

トラブルシューティングのアイデア

生成された SQL の問題のトラブルシューティングは比較的複雑ですが、全体的なアイデアは、遅い SQL をトラブルシューティングすることです。具体的なアイデアは次のとおりです。

• 最初にデータベース Mysql の遅いクエリを有効にします

  SET GLOBAL slow_query_log='on';

• 2 番目に遅い SQL の最小時間を設定します

  SET GLOBAL long_query_time=0.02;

• 通常は遅い SQL を入れますファイル内

  #

  SET GLOBAL log_output='FILE';

• スロー SQL ログ ファイルをローカルにダウンロードします

• 最後にデータベース Mysql

  ## のスロー クエリを閉じます

  # 重要な注意事項: 低速 SQL は、実稼働環境で使用する場合にのみ開き、ロギングがビジネス パフォーマンスに影響を与えるのを避けるために、使用後は閉じることをお勧めします。

  SET GLOBAL slow_query_log='off';

SQL をチューニングするにはどうすればよいですか?

SQL チューニングでは、複数の側面からの知識が統合され、テーブル構造とテーブルインデックスの 2 つの側面から最適化するのが一般的です。

テーブル構造の最適化

1. フィールド クラスと長さの合理的な使用

理解するための例: tinyint(1) で保存された単なる性別フィールドint(1) ストレージは 1 バイトを占有し、int(1) ストレージは 4 バイトを占有します。レコードが 100 万件ある場合、int に格納されたテーブル ファイルは、tinyint に格納されたテーブル ファイルよりも約 2.8M 大きくなります。そのため、格納された int 型を読み取る場合、テーブルファイルは大きく、読み取り速度は tinyint の読み取りよりも遅くなります。これは実際、

フィールド タイプの長さを合理的に使用する必要がある理由の本質です。つまり、読み取りパフォーマンスを提供するために保存されたファイルのサイズを削減するためです。 もちろん、280 万は全体の状況に影響しないので無視してもよいという友人もいるかもしれません。ヨン兄弟は、この考えに何か付け加えたいと思います: テーブルに 10 個のフィールドがあり、システムに合計 30 個のテーブルがあると仮定します。その後、追加のファイル サイズを見てみましょう。 (2.8Mx10x30=840M、Thunder Super を使用して 840M をダウンロードするには数秒かかります。この時間はコンピュータでは非常に遅いと考えられます...)

#2. 冗長設計の合理的な使用

# 2.1. 冗長設計の背景 - 一時テーブル

Mysql 内には特別で軽量な一時テーブルがあり、Mysql によって自動的に作成および削除されます。一時テーブルは主に SQL の実行中に、特定の操作の中間結果を保存するために使用されます。このプロセスは MySQL によって自動的に完了し、ユーザーが手動で介入することはできません。また、この内部テーブルはユーザーには表示されません。

内部一時テーブルは SQL ステートメントの最適化プロセスにおいて非常に重要であり、MySQL の多くの操作は最適化操作のために内部一時テーブルに依存します。ただし、内部一時テーブルを使用するには、テーブルの作成と中間データへのアクセスにコストがかかるため、

SQL ステートメントを作成するときは一時テーブル

の使用を避ける必要があります。

それでは、これらのシナリオでは、Mysql は内部的に一時テーブルを使用するのでしょうか?

複数テーブル関連クエリ (JOIN) では、order by または group by で使用される列は最初のテーブルの列ではありません
グループ化された列がインデックス列ではない場合

• distinct と group by は一緒に使用されます

• distinct キーワードは order by ステートメントで使用されます

• group by カラムはインデックスカラムですが、データ量が多すぎます

2.2. 内部一時テーブルが使用されているかどうかを確認するにはどうすればよいですか?

Explain キーワードまたはツールのファンクション ボタンを使用して、SQL の実行プロセスを表示します。結果の [追加] 列に一時キーワードの使用が表示される場合は、SQL ステートメントが実行時に一時テーブルを使用することを意味します。実行中。

以下に示すように、ロール テーブルとロール グループ ロール グループには多対 1 の関係があります。関連するクエリを実行するとき、一時テーブルを使用してロール グループの ID で並べ替えられます (図 1 を参照)ソートがロールの ID を使用して行われる場合、一時テーブルは使用されません (図 2 を参照)。

2.3. 内部一時テーブルを使用しない問題を解決するにはどうすればよいですか?

この問題には 2 つの解決策があります。1 つは一時テーブルの使用を回避するように SQL ステートメントを調整すること、もう 1 つはテーブルに冗長に格納することです。 たとえば、2.2 の図 1 の例では、role_group の ID で並べ替える必要がある場合、role テーブルの group_id で並べ替えることができます。この列は、role_group の id 列の値になります。冗長的に格納されるテーブル。

3. サブデータベースとテーブルサブデータベースの合理的な使用方法

サブデータベースとサブテーブルは大量の最適化のためだけに使用されるわけではありませんですが、垂直テーブルのサブテーブルも使用できますので、SQLチューニングの下で​​使用してください。 (垂直サブテーブルと水平サブテーブルについてはここでは説明しません。ご興味がございましたら、プライベート メッセージをお送りください。)

例: 記事テーブルの一般的なデザインには、「」という大きなフィールドは含まれません。記事の内容。

記事コンテンツの大規模なフィールドは別のテーブルに配置されます

なぜ記事をテーブルに配置する必要があるのですか上記を使用してください。フィールドを 1 つのテーブルに結合せずに設計する場合はどうですか?

まず数学の問題を計算してみましょう。記事のサイズが 1M で、記事の内容が 824KB、残りのフィールドが 200KB であると仮定します。このような記事は合計 100 万件あります。

• オプション 1、テーブルをストレージに使用する場合、テーブルのサイズは 100W*1M=100WM

• オプション 2、垂直テーブル ストレージを使用する場合テーブルは 200KBx100W、コンテンツ テーブルは 824KBx100W

フロントエンドに記事リストと記事詳細の 2 ページがあり、関連するコンテンツを直接クエリする必要があります。

• 計画 1、記事リストと記事の詳細は 100WM データからクエリされます

• 計画 2、記事リストは 200KBx100W からクエリされ、記事の詳細は 824KBx100W からクエリされます。 クエリは 200KBx100W からクエリされます (現在、200KBx100W からクエリも必要になる場合があります)

これを言っても、誰もがそうすべきだと思います。彼らの心の中には明確な答えがあるのです！ テーブルの垂直分割により、さまざまなビジネス シナリオでさまざまな量のデータをクエリできます。多くの場合、このデータ量はテーブルの合計データ量よりも少なく、固定された大量または少量のデータからクエリを実行するよりも柔軟で効率的です。 。

テーブル インデックスの最適化

1. インデックス列を合理的に追加する

ほとんどの人のインデックスに対する理解は、「インデックスによってクエリを高速化できる。「速度」」というものです。ただし、Yong 兄弟は、この文の後半を追加したいと考えています。「インデックスを使用するとクエリが高速化されますが、データの挿入や変更の速度も遅くなります。」。

テーブルに 5 つのインデックスがある場合、インデックスを単純にテーブルとみなすと、1 つのテーブルと 6 つのインデックス テーブル = 6 つのテーブルに相当し、この 6 つのテーブルはいつ動作しますか?計算してみましょう:

• 挿入操作、データ挿入後、インデックス データを 5 つのインデックス テーブルに挿入する必要があります。

• 削除操作、データの後に削除するには、5 つのインデックス テーブルのインデックスを削除する必要があります

• 更新操作

  • インデックス列のデータが変更された場合は、最初にデータを変更するには、インデックス テーブルのインデックスも変更する必要があります

  • #インデックス列のデータが変更されない場合、データ テーブルのみが変更されます

#操作の選択

• #クエリ インデックスがヒットした場合は、最初にインデックスをクエリし、次にデータ テーブルをクエリします

  • クエリ インデックスがヒットしない場合は、データ テーブルを直接確認してください。
  #上記の計算により、魔法のように次のことがわかります。
インデックスの数が多いほど、挿入と削除に適しています。更新操作には影響があり、マイナスの影響もあります。

したがって、 は、インデックスの影響がクエリの利点よりも小さいと評価し、盲目的に を追加するのではなく、インデックスを追加する可能性があります。

2、合理的调配复合索引列个数和顺序

复合索引指的是包括有多个列的索引，它能有效的减少表的索引个数，平衡了多个字段需要多个索引直接的性能平衡，但是再使用复合索引的时候，需要注意索引列个数和顺序的问题。

先说列个数的问题，指的是一个复合索引中包括的列字段太多影响性能的问题，主要是对update操作的性能影响，如下红字：

• 如果修改了索引列的数据，则先修改数据，还需要修改索引表中的索引，如果索引列个数越多则修改该索引的概率越大

• 如果没有修改索引列的数据，则只修改数据表

再说复合索引中列顺序的问题，是指索引的最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，这个比较容易理解，就不多做阐述。

那些情况索引会失效？

• 索引无法存储null值，当使用is null或is not nulli时会全表扫描

• like查询以"%"开头

• 对于复合索引，查询条件中没有给出索引中第一列的值时

• mysql内部评估全表扫描比索引快时

• or、!=、<>、in、not in等查询也可能引起索引失效

表设计有那些规范？

建表规约

1. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型为 unsigned tinyint。 说明：任何字段如果为非负数，则必须是 unsigned。

2. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。e.g. 商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，

   避免关联查询

   。冗余字段遵循：

   • 不是频繁修改的字段；

   • 不是 varchar 超长字段，更不能是 text 字段。

索引规约

1. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

2. 页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。 说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3. 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

   • 正例：where a=? and b=? order by c; 索引: a_b_c。

   • 反例：索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

4. 利用延迟关联或者子查询优化超多分页场景。 说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 的行，返回 N 行。当 offset 特别大的时候，效率会非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行 SQL 改写。

5. 建组合索引的时候，区分度最高的在最左边。

6. SQL 性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 consts。

SQL 语句

1. 不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。

2. count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。

3. 当某一列的值全为 NULL 时，count(column) 的返回结果为 0，但 sum(column) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。 可以使用如下方式来避免 sum 的 NPE 问题。

SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;

1. 使用 ISNULL() 来判断是否为 NULL 值。 说明：NULL 与任何值的直接比较都为 NULL。

2. 外部キーとカスケードは使用してはならず、すべての外部キーの概念はアプリケーション層で解決される必要があります。 説明: 学生と成績の関係を例にとりますと、学生テーブルのstudent_idが主キー、成績テーブルのstudent_idが外部キーとなります。 Student テーブルの Student_id が更新されると、成績テーブルの Student_id の更新もトリガーされます。これは cascade update です。外部キーとカスケード更新は、単一マシンでの同時実行性が低い場合には適していますが、分散クラスターや同時性が高いクラスターには適していません。カスケード更新は強力にブロックされ、データベース更新ストームのリスクがあります。外部キーはデータベースの挿入速度に影響します。 。

3. ストアド プロシージャの使用は禁止されています。ストアド プロシージャはデバッグと拡張が難しく、移植性がありません。

4. #in 回避できる場合は操作を回避してください。避けられない場合は、in 以降のコレクション要素数を慎重に評価し、1000 以内に制御する必要があります。

ORM マッピング

1. POJO クラスのブール属性は is を使用して追加できませんが、データベース フィールドは is_ を使用して追加する必要があります。マッピングの resultMap にフィールドと属性が必要です。

2. #sql.xml

   構成パラメータは #{}、#param# を使用します。${} は使用しないでください。この方法は簡単ですSQLインジェクションが発生します。

3. @トランザクション

   トランザクションを乱用しないでください。トランザクションはデータベースの QPS に影響します。さらに、トランザクションが使用される場合は、キャッシュ ロールバック、検索エンジンのロールバック、メッセージ補正、統計補正など、ロールバック ソリューションのさまざまな側面を考慮する必要があります。

推奨学習:

mysql ビデオ チュートリアル

以上がMysql の最適化に関する 15 の問題を選択してまとめましたの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。