MySQLクエリを最適化する価値があるかどうかを調べる方法

私はよく同じ質問をされます。最適化が必要なクエリをどのように見つけることができますか。 結局、たとえば、pt-query-digestレポートを見ると、遅いクエリやシステムに大きな負荷を引き起こすクエリを簡単に見つけることができますが、このクエリを高速化する機会があるかどうかをどのように理解できますか？ クエリを最適化する方法は多数あるため、この質問に対する完全な回答には、包括的な分析が必ず必要です。 ただし、適用できる非常に便利なメトリックが1つあります。クエリによって返される行の数と完了した行の関係です。

例があるとしましょう：

# Time: 120911 17:09:44 # User@Host: root[root] @ localhost [] # Thread_id: 64914 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F03 use sbtest; SET timestamp=1347397784; select * from sbtest where pad='abc'; 

この場合のクエリは（一致するものがないため）0行を返しましたが、このために1,000万行を処理する必要がありました。 どのシナリオが望ましいでしょうか？ 要求が最終的に返される行と同じ数の行を通過した場合。 この場合、インデックスをテーブルに配置すると、スロークエリログに次のエントリが記録され、すべてのスロークエリが該当します。

 # Time: 120911 17:18:05 # User@Host: root[root] @ localhost [] # Thread_id: 65005 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 0.000323 Lock_time: 0.000095 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F14 SET timestamp=1347398285; select * from sbtest where pad='abc'; 

Rows_examined = 0の値は、Rows_sentと一致し、リクエストが非常に適切に最適化されていることを意味します。 この場合、データベースへのアクセスがまったく発生しないと思った場合は、間違っていることに注意してください。 インデックスはスキャンされますが、MySQLパーツによる処理のためにトップに戻されて返された行のみがカウントされるため、Rows_examinedの値はゼロのままです。
すべてが非常に単純に思えますが、これは速すぎる結論です。 このような数学は、集計関数/グループ化なしのクエリでのみ機能し、さらに1つのテーブルを通過するクエリでのみ機能します。 しかし、複数のテーブルに影響するクエリはどうでしょうか？

 # Time: 120911 17:25:22 # User@Host: root[root] @ localhost [] # Thread_id: 65098 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 0.000234 Lock_time: 0.000063 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F1D SET timestamp=1347398722; select * from sbtest a,sbtest b where a.id=5 and b.id=ak; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=ak; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | a | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.00 sec) 

この場合、実際には2つのテーブルを結合しますが、テーブルへのアクセスのタイプが「定数」に設定されているため、MySQLは2つのテーブルへのアクセスを考慮しません。 「実際の」アクセスの場合、出力は次のようになります。

 # Time: 120911 17:28:12 # User@Host: root[root] @ localhost [] # Thread_id: 65099 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 0.000273 Lock_time: 0.000052 Rows_sent: 1 Rows_examined: 2 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F23 SET timestamp=1347398892; select * from sbtest a,sbtest b where ak=2 and b.id=a.id; +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+ | 1 | SIMPLE | a | ref | PRIMARY,k | k | 4 | const | 1 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sbtest.a.id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+ 2 rows in set (0.00 sec) 

この場合、行セットごとに2行の分析行があります。これは、このクエリで2つの（論理）テーブルが使用されているためです。 リクエストにグループがある場合、このルールは機能しません。

 # Time: 120911 17:31:48 # User@Host: root[root] @ localhost [] # Thread_id: 65144 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2 # Bytes_sent: 75 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; 

このクエリは2行のみを返しますが、1000万件を通過します。結果をグループ化するにはすべての行を通過する必要があるため、このクエリを簡単に最適化することはできません。
この場合、group byおよび集約関数をリクエストから削除することを検討するかもしれません。 その後、リクエストは“select * from sbtest”に“select * from sbtest” 、1000万行すべてが返されるため、単純な最適化方法はありません。
この方法は、明確な「はい」または「いいえ」の答えを提供するために作成されたものではありませんが、最終的にどのような最適化を達成できるかを大いに助けます。 1000行のインデックスを使用して10を返すクエリがあるとします。たとえば、結合インデックスを追加することで、通過する行数を100倍減らすことができます。

それでは、簡潔に-クエリを最適化する価値があるかどうかをどのようにしてすばやく見つけることができますか？
-group by、distinct、aggregate関数を削除した後にクエリが返す行数を確認する（A）
-渡された行の数を、結合内のテーブルの数で割ったものを取ります（B）
-BがA以下の場合、リクエストは「完璧」です
-B / Aが10以上の場合。 このリクエストは最適化の最も価値のある候補の1つです。

これは簡単な方法であり、平均値だけでなく境界値も報告するため、pt-query-digestと一緒に安全に使用できます。

元の記事： こちら