MySQL パフォーマンス チューニング クエリの最適化

この記事では、mysql に関する関連知識を提供し、主にクエリの最適化やその他のコンテンツを含むパフォーマンスの最適化に関する関連事項を紹介します。一緒に見てみましょう。皆様のお役に立てれば幸いです。 。 役立つ。

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

高速クエリを作成する前に、本当に重要なのは応答時間、および SQL ステートメント全体の実行中に各ステップにかかる時間を知る必要があります。どのステップが実行効率を低下させる重要なステップであるかを知る必要があります。これを行うには、SQL ステートメントのライフ サイクルを知る必要があります。クエリを実行し、最適化します。アプリケーションのシナリオが異なれば、最適化方法も異なります。特定の状況を一般化したり分析したりしないでください。

1. クエリが遅い理由

1. ネットワーク

2. CPU

3. IO

4. コンテキストの切り替え

5. システムコール

##6. 統計情報の生成

##7. ロック待ち時間

##2. データアクセスの最適化

##1.クエリのパフォーマンスが低下する主な原因は、アクセスされるデータが多すぎることです。クエリによっては必然的に大量のデータをフィルタリングする必要があります。アクセスされるデータの量を減らすことで最適化できます。

(1) アプリケーションが適切かどうかを確認します。必要な量を超える大量のデータを取得している

#(2) mysql サーバー層が必要な量を超える大量のデータ行を分析しているかどうかを確認します

2。データベースから不要なデータが要求される

(1) 不要なレコードをクエリする (mysql は必要なデータのみを返すと誤解することがよくあります。実際、mysql は最初にすべての結果を返し、その後計算を実行します。日常の開発では習慣として、最初にクエリに select ステートメントを使用することがよくあります。多数の結果が得られ、最初の N 行を取得した後に結果セットを閉じます。最適化方法は、クエリの後に制限を追加することです)

(2 ) 複数のテーブルが関連付けられている場合はすべての列を返します (select * fromactor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';select actress.* from Actor...;)

(3) 常にすべての列を取り出す (会社のエンタープライズ要件では、select * の使用は禁止されています。この方法は開発を簡素化できますが、クエリのパフォーマンスに影響を与えるため、使用しないようにしてください) )

(4) 同じデータを繰り返しクエリする (同じクエリを繰り返し実行し、毎回まったく同じデータを返す必要がある場合。そのため、このようなアプリケーション シナリオに基づいて、この部分をキャッシュすることができます)

3. 実行プロセスの最適化

1. クエリ キャッシュ

クエリ ステートメントを解析する前に、クエリ キャッシュがオンになっている場合、mysql は最初にクエリがクエリ キャッシュ内のデータにヒットするかどうかをチェックします。クエリがクエリ キャッシュにヒットした場合は、結果を返す前にユーザーのアクセス許可がチェックされます。パーミッションに問題がある場合、mysql はすべての段階をスキップし、結果をキャッシュから直接取得してクライアントに返します

2. クエリ最適化処理

#Mysql は、キャッシュをクエリした後、SQL の解析、前処理、および SQL 実行プランの最適化の手順を実行します。これらの手順でエラーが発生した場合、クエリが終了する可能性があります。

(1) 文法パーサーと前処理

mysql は、キーワードを通じて SQL ステートメントを解析し、解析ツリーを生成します。mysql パーサーは、mysql 文法規則を使用してクエリを検証および解析します。間違ったキーワードが使用されているかどうか、順序が正しいかどうかなどを確認します。プリプロセッサはさらに、テーブル名と列名が存在するかどうか、あいまいさがないかどうかなど、解析ツリーが正当であるかどうかを確認し、権限なども確認します。 .

( 2) クエリ オプティマイザー

構文ツリーに問題がない場合、オプティマイザーはそれを実行プランに変換します。クエリ ステートメントでは多くの実行メソッドを使用でき、対応する結果は次のようになります。ただし、実行方法が異なれば効率も異なります。オプティマイザの主な目的は、最も効果的な実行プランを選択することです。

MySQL はコストベースのオプティマイザーを使用しており、最適化中に特定のクエリ プランを使用してクエリのコストを予測し、コストが最小のものを選択しようとします。

a. select count(*) from film_actor; show status like 'last_query_cost';

このクエリ ステートメントでは、対応するデータを見つけるために約 1104 データ ページが必要であることがわかります。

(a) 各テーブルまたはインデックスのページ数

(b) インデックスのカーディナリティ

(c) インデックスとデータ行の長さ

(d) インデックスの分布

b. 多くの場合、mysql は次の理由で間違った実行プランを選択します:

(a ) 不正確な統計情報(InnoDB は、mvcc アーキテクチャのため、データ テーブル内の行数に関する正確な統計情報を維持できません)

(b) 実行計画のコスト見積もりは、実際の実行コストと等しくありません (実行計画により多くのページを読み取る必要がある場合でも、これらのページが順番に読み取られる場合、またはこれらのページが読み取られる場合、そのコストは小さくなる場合があります)すでにメモリ内にある場合、そのアクセス コストは非常に小さくなります。MySQL レベルでは、どのページがメモリ内にあり、どのページがディスク上にあるかがわからないため、クエリの実行中にどれだけの IO 時間が必要になるかを知ることは不可能です。)

(c) mysql の最適値は、あなたが考えているものと異なる可能性があります (mysql の最適化はコスト モデルの最適化に基づいていますが、それが最速の最適化ではない可能性があります)

(d ) mysql同時に実行される他のクエリは考慮されません

(e) mysql は制御下にない運用コスト (ストアド プロシージャまたはユーザー定義関数の実行コスト) を考慮しません

c. 最適化サーバーの最適化戦略

(a) 静的最適化 (解析ツリーを直接分析し、最適化を完了します)

(b) 動的最適化 (動的最適化はクエリのコンテキストに関連しており、 (クエリのコンテキストにも関係する場合があります) インデックスに対応する値と行数は関連しています)

(c) MySQL はクエリを静的に 1 回最適化するだけで済みますが、動的最適化ではクエリを最適化する必要があります。実行されるたびに再評価される

d、オプティマイザの最適化タイプ

(a) 関連テーブルの順序を再定義します（データテーブルの関連付けは、常に指定された順序で実行されるわけではありません） (b) 外部結合を内部結合に変換します。内部結合は外部結合よりも効率的です。

(c) 同等の変換ルールを使用して、mysql はいくつかの同等の変更を使用してユニオンの計画式

(d) count()、min()、max() を最適化します (インデックスとカラムを null にできるかどうか)通常、mysql がこのタイプの式を最適化するのに役立ちます。たとえば、特定の列値の最小値を見つけるには、インデックスの左端のレコードをクエリするだけでよく、全文スキャンや比較は必要ありません)

(e) 推定して定数式に変換すると、mysql が定数時間に変換できる式を検出した場合、その式は常に定数として扱われます。 (explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1)

(f) インデックス内の列にすべての列が含まれる場合のインデックス カバレッジ スキャン。クエリ列で使用する必要がある場合は、カバリング インデックス

を使用できます。 (g) サブクエリの最適化 (mysql は場合によってはサブクエリをより効率的な形式に変換できるため、データに対する複数のクエリを何度も削減できます。 Access は、たとえば、頻繁にクエリされるデータをキャッシュに入れます。)

(h) 等価伝播 (2 つのカラムの値が等価で関連付けられている場合、mysql は 1 つのカラムの where 条件を置くことができます)別の列に渡される列:

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

film_id フィールドは、ここでは等価性の関連付けに使用されます。film_id 列は、film テーブルだけでなく、film_actor テーブルにも適用できます

explain select film.film_id from film inner join film_actor using(film_id
 ) where film.film_id > 500 and film_actor.film_id > 500;）

e, 関連クエリ

Mysql の関連クエリは非常に重要ですが、実際、関連クエリを実行する戦略は比較的単純です。mysql は、あらゆる関係に対してネストされたループ関連の操作を実行します。つまり、mysql が最初に実行されます。テーブル内の 1 つのデータをループアウトし、次のテーブルにネストして一致する行を見つけます。一致する行がすべてのテーブルで見つかるまで順番に続行されます。その後、各テーブルの一致する行に基づいて、必要な列を返します。 MySQL は最後に関連付けられたテーブルで一致する行を見つけようとします 一致する行をすべて見つけた後、最後の関連テーブルでそれ以上の行が見つからない場合、mysql は前のレベルの関連テーブルに戻り、さらに一致するレコードが見つかるかどうかを確認します。全体的な考え方は次のとおりですが、実際の実行プロセスには多くのバリエーションがあることに注意してください:

f、ソートの最適化

いずれにせよ、ソートは非常にコストのかかる操作です。パフォーマンスの観点から、ソートを回避するか、大量のデータのソートを可能な限り回避することは可能です。

ソートにはインデックスを使用することをお勧めしますが、インデックスを使用できない場合は、 MySQL を使用する場合、MySQL 自体がソートする必要があります。データ量が少ない場合は、メモリ上で行われます。データ量が多い場合は、ディスクを使用する必要があります。これは、mysql でファイルソートと呼ばれます。ソートされるデータの量がソート バッファー ('%sort_buffer_size%'; のような変数を表示) より少ない場合、mysql は迅速なソートのためにメモリを使用します。十分なメモリがない場合は、ソートし、mysql は最初にツリーをブロックに分割します。 、クイック ソートを使用して各独立したブロックをソートし、各ブロックのソート結果をディスクに保存し、ソートされたブロックをマージし、最後にソートに戻ります。その結果、ソート アルゴリズムは次のようになります:

(a ) 2 回の送信ソート

1 回目のデータ読み取りでは、ソートが必要なフィールドを読み取ってソートし、2 回目では必要に応じてソート結果に応じたデータ行を読み取ります。

この方法は比較的非効率的です。その理由は、データがソートされているため、2 回目にデータを読み取るときに、すべてのレコードを読み取る必要があるためです。このとき、より多くのランダム IO が使用され、読み取りコストが増加します。データは大きくなります

2 回の送信の利点は、ソート中に保存するデータをできるだけ少なくして、ソート操作でソート バッファにできるだけ多くの行を収容できるようになることです。

(b) 1 回の送信ソート

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

（c）如何选择

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

四、优化特定类型的查询

1、优化count()查询

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数。

（1）总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的。

（2）使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

（3）更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

2、优化关联查询

（1）确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引。

（2）确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

3、优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替

4、优化limit分页

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列。

select film_id,description from film order by title limit 50,5;
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

5、优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。

6、推荐使用用户自定义变量

用户自定义变量是一个容易被遗忘的mysql特性，但是如果能够用好，在某些场景下可以写出非常高效的查询语句，在查询中混合使用过程化和关系话逻辑的时候，自定义变量会非常有用。
用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。

（1）自定义变量的使用

set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;

（2）自定义变量的限制

a、无法使用查询缓存

b、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句

c、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信

d、不能显式地声明自定义变量地类型

e、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行

f、赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号。

g、使用未定义变量不会产生任何语法错误。

（3）自定义变量的使用案例

a、优化排名语句

在给一个变量赋值的同时使用这个变量

select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

b、避免重新查询刚刚更新的数据

当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

update t1 set  lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

c、确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段

（a）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

（b）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的 。

（c）解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

推荐学习：mysql视频教程

以上がMySQL パフォーマンス チューニング クエリの最適化の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。