MYSQL クエリの効率を向上させる 10 の SQL ステートメント最適化テクニック

MySQL データベースの実行効率はプログラムの実行速度に大きな影響を与えます。データベースの効率的な処理と最適化は非常に役立ちます。特に大量のデータを処理する必要がある場合。

1. MySQLクエリキャッシュを最適化する

MySQL サーバーでクエリを実行するときに、高速クエリ キャッシュを有効にすることができます。データベース エンジンをバックグラウンドで静かに動作させることは、パフォーマンスを向上させる最も効果的な方法の 1 つです。同じクエリを複数回実行する場合、結果をキャッシュから取得すると非常に高速になります。
しかし、主な問題は、それが非常に簡単に隠されてしまうため、ほとんどのプログラマーがそれを無視してしまうことです。一部の処理タスクでは、クエリ キャッシュが動作しないようにすることができます。

// クエリキャッシュは機能しません

$r = mysql_query("username FROM user WHERE signup_date >= CURDATE() を選択してください"); 
// クエリ キャッシュが機能します。 
$today = date("Ymd"); 
$r = mysql_query("username FROM user WHERE signup_date >= '$today'"); 
// クエリキャッシュは機能しません 
$r = mysql_query("username FROM user WHERE signup_date >= CURDATE() を選択してください"); 
// クエリ キャッシュが機能します。 
$today = date("Ymd"); 
$r = mysql_query("username FROM user WHERE signup_date >= '$today'");

2. EXPLAINを使用してSELECTクエリをより明確にする

EXPLAIN キーワードの使用は、MySQL がどのようなクエリ操作を実行しているかを確認できるもう 1 つの MySQL 最適化のヒントです。これにより、ボトルネックを見つけ、クエリまたはテーブル構造に問題がある場所を表示できます。

EXPLAIN クエリの結果から、どのインデックスが参照されているか、テーブルがどのようにスキャンされ、ソートされているかなどがわかります。

SELECT クエリ (結合を含むより複雑なクエリが望ましい) を実装し、キーワードの説明を追加します。ここで phpMyAdmin を使用すると、テーブルに結果が表示されます。たとえば、結合を実行するときにインデックスに列を追加するのを忘れた場合、EXPLAIN を使用すると問題を見つけるのに役立ちます。

3. LIMIT 1を使用して、唯一の行を取得します。

テーブルをクエリするときに、1 行だけを確認すればよいことが分かる場合があります。非常にユニークなレコードを探している場合もあれば、WHERE 句を満たすレコードがいくつ存在するかをチェックしている場合もあります。

この場合、 LIMIT 1を追加するとクエリの効率が向上します。この方法では、データベース エンジンはテーブルまたはインデックス全体をスキャンするのではなく、1 つだけ見つけたらスキャンを停止します。

// アラバマ州のユーザーはいますか? 
// してはいけないこと: 
$r = mysql_query("SELECT * FROM user WHERE state = 'アラバマ'"); 
mysql_num_rows($r) > 0 の場合 
 // ... 
}  
// はるかに良い: 
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); 
mysql_num_rows($r) > 0 の場合 
 // ... 
}

4. インデックス内の検索フィールド

インデックスは主キーや一意キーだけではありません。テーブル内の任意の列を検索する場合は、常にインデックスを指定する必要があります。

5. 結合するインデックスが同じタイプであることを確認する

アプリケーションに複数の結合クエリが含まれている場合は、結合する列が両方のテーブルでインデックス付けされていることを確認する必要があります。これは、MySQL が内部結合操作を最適化する方法に影響します。

さらに、追加される列は同じタイプである必要があります。たとえば、DECIMAL 列を結合し、同時に別のテーブルの int 列を結合すると、MySQL は少なくとも 1 つのインデックスを使用できなくなります。文字エンコーディングも文字列型と同じである必要があります。

// 私の州で企業を探しています 
$r = mysql_query("usersからcompany_nameを選択 
 LEFT JOIN companies ON (users.state = companies.state) 
 ここで、users.id = $user_id"); 
// 両方の状態列にインデックスを付ける必要がある 

// 両方とも同じ型と文字エンコーディングである必要があります 

// またはMySQLがテーブル全体をスキャンする可能性がある

6. BY RAND() コマンドを使わない

これは多くの初心者プログラマーが陥る罠です。気づかないうちに、ひどい静けさを作り出しているかもしれません。このトラップは、BY RAND() コマンドを使用するときに作成されます。

本当に結果をランダム化する必要がある場合は、それを実行するよりよい方法があります。確かに、これにはより多くのコードを記述する必要がありますが、パフォーマンスのボトルネックを回避できます。問題は、MySQL がテーブル内の各行に対して BY RAND() コマンドを実行し (プロセッサのパワーを消費する)、1 行だけを返す可能性があることです。

// してはいけないこと: 
$r = mysql_query("ユーザー名をuserから選択し、ORDER BY RAND() LIMIT 1を実行"); 
// はるかに良い: 
$r = mysql_query("count(*) を user から選択します"); 
mysql_fetch_row() メソッドは、行を 1 つだけフェッチします。 
$rand = mt_rand(0,$d[0] - 1); 
$r = mysql_query("ユーザー名をuser LIMIT $rand, 1から選択");

7. SELECT *コマンドを避ける

テーブルから読み取るデータが増えるほど、クエリの速度は遅くなります。特にデータベース サーバーと Web サーバーが分離されている場合、ディスク操作に必要な時間が増加します。データが不必要にサーバー間を移動するため、非常に長いネットワーク遅延が発生します。必要な列を常に指定するのは非常に良い習慣です。

// 好ましくない 
$r = mysql_query("SELECT * FROM user WHERE user_id = 1"); 
mysql_fetch_assoc は、次のコードで定義されます。 
echo "ようこそ {$d['username']}"; 
// より良い： 
$r = mysql_query("user_id = 1 である user から username を選択"); 
mysql_fetch_assoc は、次のコードで定義されます。 
echo "ようこそ {$d['username']}"; 
// 結果セットが大きいほど、違いは顕著になります

8. PROCEDURE ANALYSE()から提案を得る

PROCEDURE ANALYSE() を使用すると、MySQL はテーブル内の列構造と実際のデータを分析して、いくつかの提案を行うことができます。テーブルに実際のデータがすでにある場合は、重要な決定を下すのに役立ちます。

9. 準備された声明

準備されたステートメントは、パフォーマンスの最適化とセキュリティの面で役立ちます。

準備されたステートメントは、すでにバインドされている変数をデフォルトでフィルター処理し、アプリケーションを SQL インジェクション攻撃から効果的に保護します。もちろん手動でフィルタリングすることもできますが、ほとんどのプログラマーは忘れっぽいため、望ましい効果を得るのは困難です。

// 準備されたステートメントを作成する 
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { 
 // バインドパラメータ 
 $stmt->bind_param("s", $state); 
 // 実行する 
 $stmt->execute(); 
 // 結果変数をバインドする 
 $stmt->bind_result($username);  
 // 値を取得する 
 $stmt->fetch(); 
 printf("%s は %s からのものです\n", $username, $state);  
 $stmt->close(); 
}

10. IPアドレスを符号なし整数として保存する

多くのプログラマーは、IP アドレスを整数として保存できることに気付かずに VARCHAR(15) を作成します。 INT 型の場合、占有するスペースは 4 バイトのみで、固定サイズのフィールドになります。
IP アドレスは 32 ビットの符号なし整数を使用するため、操作する列が UNSIGNED INT 型であることを確認する必要があります。
1. $r = "ユーザーを更新します。SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
MYSQLクエリステートメントは数多くあります。今日はこれら10個についてお話します。