MySQL がクエリ速度を最適化する方法を学ぶ

前の章では、高パフォーマンスの MySQL に不可欠な、最適化されたデータ型を選択する方法とインデックスを効率的に使用する方法を紹介しました。しかし、これだけでは十分ではなく、クエリの合理的な設計も必要です。クエリの記述が適切でないと、テーブル構造がどれほど合理的でインデックスが適切であっても、高いパフォーマンスは達成できません。

MySQL のパフォーマンスの最適化に関しては、クエリの最適化が最適化のソースであり、システムが高速かどうかを最もよく反映することもできます。この章と次の章では、クエリのパフォーマンスの最適化に焦点を当て、MySQL が実際にクエリをどのように実行するのか、どこが遅いのか、そしてどのように高速化するのかをより深く理解するのに役立ついくつかのクエリ最適化テクニックを紹介します。高効率と非効率の理由を説明します。これは、クエリ SQL ステートメントをより適切に最適化するのに役立ちます。

関連する学習の推奨事項: mysql ビデオ チュートリアル

この章は、明確に理解できるように「クエリ速度が非常に遅い理由」から始まります。これにより、クエリをより適切に最適化し、クエリが遅くなる可能性がある場所を知ることができるため、一歩先を行くことができます。

1. どこが遅くなっていますか?

クエリ速度の実際の尺度は応答時間です。クエリをタスクと考えると、クエリは一連のサブタスクで構成され、それぞれに一定の時間がかかります。クエリを最適化する場合、実際にはそのサブタスクを最適化する必要があるため、それらのサブタスクの一部を削除し、サブタスクの実行回数を減らすか、サブタスクの実行を高速化します。

MySQL がクエリを実行するとき、どのようなサブタスクがあり、どのサブタスクに最も時間がかかりますか?これには、いくつかのツールまたはメソッド (実行プランなど) を使用してクエリを分析し、遅さの原因を突き止めて発見する必要があります。

一般的に、クエリのライフ サイクルは次の順序で大まかに見ることができます。 クライアントからサーバーに送信され、サーバー上で解析され、実行計画が生成され、実行され、結果が返されます。クライアント 。そのうちの「実行」は、ライフサイクル全体の中で最も重要な段階と考えることができます。これには、データを取得するためのストレージ エンジンへの多数の呼び出しと、呼び出し後の並べ替えやグループ化などのデータ処理が含まれます。

これらのタスクを完了するとき、クエリは、ネットワーク、CPU 計算、統計情報と実行計画の生成、ロック待機およびその他の操作、特に基盤となるストレージからのデータの取得など、さまざまな段階でさまざまな場所で時間を費やす必要があります。エンジン これらの呼び出しにはメモリ操作や CPU 操作が必要で、多数のコンテキスト スイッチやシステム コールも生成される場合があります。

上記の操作では、多くの時間がかかり、何度も繰り返される操作や、実行が非常に遅い操作など、不必要な追加操作がいくつか発生します。これは、クエリが実際に遅くなる可能性がある場所です。クエリを最適化する目的は、これらの操作にかかる時間を削減および排除することです。

上記の分析を通じて、クエリ プロセスを全体的に理解し、クエリのどこに問題がある可能性があるかを明確に把握できます。これにより、最終的にクエリ全体が遅くなり、実際のクエリ最適化の方向性が示されます。

つまり、クエリの最適化は次の 2 つの観点からアプローチできます。

• サブクエリの数を減らす
• 追加の繰り返し操作を減らす

クエリのパフォーマンスが低下する一般的な理由は、アクセスされるデータが多すぎることです。データ量が少ないときは、クエリ速度はかなり良好ですが、データ量が増加すると、クエリ速度が劇的に変化し、人々を混乱させ、エクスペリエンスを非常に低下させます。クエリの最適化については、次の側面から確認できます。

• #不要なデータがクエリされているかどうか
• 追加のレコードがスキャンされているかどうか

2. 不要なデータがクエリされるかどうか

実際のクエリでは、実際に必要なデータがクエリされることが多く、その冗長なデータはアプリケーションによって破棄されます。これは MySQL にとって追加のオーバーヘッドであり、アプリケーション サーバーの CPU およびメモリ リソースも消費します。

いくつかの典型的なケースは次のとおりです:

1. 不要なレコードのクエリ

これはよくある間違いで、MySQL が返すものだけを返すと誤解することがよくあります。データ、実際には、MySQL は最初に結果セット全体を返し、次に計算を実行します。

開発者は、習慣的に SELECT ステートメントを使用して多数の結果をクエリし、次にアプリケーション クエリまたはフロントエンド表示レイヤーを使用して、前の N 行のデータを取得します。たとえば、ニュースの 100 レコードをクエリします。最初の 10 項目がページに表示されます。

最も効果的な解決策は、必要なだけレコードをクエリすることです。通常、LIMIT はクエリの後に追加されます (ページング クエリ)。

2. 複数のテーブルを相関させる場合はすべての列を返す

映画「Academy Dinosaur」に出演したすべての俳優をクエリしたい場合は、次のように実行しないでください。方法 クエリ:

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

これにより、3 つのテーブルのすべてのデータ列が返されます。実際の要件は、アクター情報をクエリすることです。正しい書き方は次のとおりです:

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. 总是查询出全部列

每次看到select *的时候一定要用异样的目光来审视它，是不是真的需要返回全部数据列？

在大部分情况下，是不需要的。 select *会导致进行全表扫描，会让优化器无法完成索引扫描这类优化，过多的列还会为服务器带来额外的I/O、内存和CPU的消耗。 即使真的需要查询出全部列，应该逐个罗列出全部列而不是*。

4. 重复查询相同的数据

如果你不太留意，很容易出现这样的错误： 不断地重复执行相同的查询，然后每次都返回完全相同的数据。

例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复来查询这个数据。 比较好处理方法是，在初次查询的时候将这个数据缓存起来，后续使用时直接从缓存中取出。

三、是否扫描了额外的记录

确 定查询只查询了需要的数据以后，接下来应该看看查询过程中是否扫描了过多的数据。 对于MySQL，最简单衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

没有哪个指标能够完全来衡量查询的开销，但它们能够大致反映MySQL内部执行查询时需要访问多少数据，并可以大概推算出查询运行的实际。 这三个指标都会记录到MySQL的慢日志中，所以 检查慢日志记录是找出扫描行数过多查询的办法 。

慢查询： 用于记录在MySQL中响应时间超过阈值(long_query_time,默认10s)的语句，并会将慢查询记录到慢日志中。 可通过变量slow_query_long来开启慢查询，默认是关闭状态，可以将慢日志记录到表slow_log或文件中，以供检查分析。

1. 响应时间

响应时间是两个部分之和： 服务时间和排队时间。 服务时间是指数据库处理这个查询真正花费了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待I/O操作，也可能是等待 行 锁等等。

在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。 诸如存储引擎的锁（表锁，行锁），高并发资源竞争，硬件响应等诸多因素都会影响响应时间，所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。

2. 扫描的行数和返回的行数

在分析查询时，查看该查询扫描的行数是非常有帮助的，在此之上也能够分析是否扫描了额外的记录。

对于找出那些糟糕查询，这个指标可能还不够完美，因为并不是所有行的访问代价都是相同的。 较短的行的访问速度相当快，内存中的行也比磁盘中的行的访问速度要快的多。

理想的情况下，扫描的行数和返回的行数应该是相同的。 但实际上这种美事并不多，例如在做一个关联查询的时候，扫描的行数和对返回的行数的比率通常都很小，一般在1:1和10：1之间，不过有时候这个值也可能非常大。

3. 扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。 这些访问方式可能需要访问很多行才能返回一条结果，也有些访问方式可能无需扫描就能返回结果。

在执行计划EXPLAIN语句中的type列反映了访问类型。 访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引，常数索引等。 这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，这也是我们之前讨论索引的问题。 现在应该明白为什么索引对于查询优化如此重要了。 索引让MySQL以最高效，扫描行数最少的方式找到需要的记录 。

如果发现查询扫描了大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：

• インデックス カバレッジ スキャンを使用して、必要な列をすべてインデックスに追加します。これにより、ストレージ エンジンは、対応する行を取得するためにテーブルに戻ることなく結果を返すことができます。
  
• テーブル構造を最適化します。たとえば、別の集計テーブルを使用してクエリを完了します。
• MySQL オプティマイザーがより最適化された方法でクエリを実行できるように、複雑なクエリを書き換えます。

関連する推奨事項: プログラミング ビデオ コース

以上がMySQL がクエリ速度を最適化する方法を学ぶの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。