MYSQLクエリの効率化のためのSQL文の最適化テクニック

MySQLデータベースの実行効率はプログラムの実行速度に大きな影響を及ぼし,効率的な処理でデータベースを最適化することは非常に有用である.特に大量のデータを処理する必要がある場合.
1.MySQLクエリーキャッシュの最適化
MySQLサーバでクエリーを行い、キャッシュを有効にします.データベース・エンジンをバックグラウンドでこっそり処理することは、パフォーマンスを向上させる最も効果的な方法の一つです.同じクエリーが複数回実行されると、結果がキャッシュから抽出される場合、かなり速くなります.しかし、主な問題は、多くのプログラマーが無視するほど隠されやすいことです.一部の処理タスクでは、実際にはクエリー・キャッシュの動作を阻止できます.

// query cache does NOT work

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 
// query cache works! 
$today = date("Y-m-d"); 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'"); 
// query cache does NOT work 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 
// query cache works! 
$today = date("Y-m-d"); 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2.EXPLAINでSELECT検索をより明確にする
EXPLAINキーワードを使用するのは、MySQLがどのようなクエリー操作を行っているかを知るためのもう一つのMySQL最適化テクニックです.これにより、ボトルネックを発見し、クエリーやテーブル構造に問題があるかを示すことができます.
EXPLAINクエリの結果は、インデックスが参照されているか、テーブルがどのようにスキャンされ、ソートされているかなどを示すことができます.
SELECTクエリー(比較的複雑なものが望ましい、joins方式付き)を実現し、キーワードの説明を追加します.ここでphpMyAdminを使用して、表の結果を教えてくれます.たとえば、joinsを実行しているときにインデックスにカラムを追加するのを忘れている場合、EXPLAINは問題の所在を見つけるのに役立ちます.
3.LIMIT 1による一意行の取得
時々、テーブルを検索すると、自分が1行しか見ていないことを知っています.あなたは非常にユニークな記録に行くかもしれません.あるいは、ちょうど存在する記録の数をチェックしただけで、彼らはあなたのWHERE句を満たしています.
この場合、LIMIT 1を追加すると、クエリーがより効率的になります.これにより、データベース・エンジンは、テーブル全体やインデックスをスキャンするのではなく、1だけがスキャンを停止することを発見します.

// do I have any users from Alabama? 
// what NOT to do: 
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'"); 
if (mysql_num_rows($r) > 0) { 
 // ... 
}  
// much better: 
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); 
if (mysql_num_rows($r) > 0) { 
 // ... 
}

4.インデックス内の検索フィールド
インデックスは、プライマリ・キーまたはユニーク・キーだけではありません.テーブル内のカラムを検索する場合は、インデックスを常に指す必要があります.
5.接続のインデックスが同じタイプであることを保証
アプリケーションに複数の接続クエリーが含まれている場合は、リンクされたカラムが両方のテーブルにインデックスされていることを確認する必要があります.これは、MySQLが内部結合操作を最適化する方法に影響します.
また、追加するカラムは、同じタイプでなければなりません.たとえば、DECIMAL列を追加し、別のテーブルのint列を追加すると、MySQLでは少なくとも1つの指標を使用できません.文字コードが同じ文字列タイプでなければならない場合でも.

// looking for companies in my state 
$r = mysql_query("SELECT company_name FROM users 
 LEFT JOIN companies ON (users.state = companies.state) 
 WHERE users.id = $user_id"); 
// both state columns should be indexed 

// and they both should be the same type and character encoding 

// or MySQL might do full table scans

6.BY RAND()コマンドは使用しない
これは多くの初心者プログラマーが落ちる罠です.あなたは知らず知らずのうちに恐ろしい穏やかさを作ったかもしれません.このトラップは、BY RAND()コマンドを使用している間に作成されます.
本当にランダムに結果を表示する必要がある場合は、より良い方法がたくさんあります.確かにこれはより多くのコードを書く必要がありますが、パフォーマンスのボトルネックを避けることができます.問題は、MySQLがテーブル内の独立したローごとにBY RAND()コマンドを実行し(プロセッサの処理能力を消費する)、ローを1つだけ返す可能性があることです.

// what NOT to do: 
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); 
// much better: 
$r = mysql_query("SELECT count(*) FROM user"); 
$d = mysql_fetch_row($r); 
$rand = mt_rand(0,$d[0] - 1); 
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7.できるだけSELECT*命令を避ける
テーブルから多くのデータを読み込むと、クエリーが遅くなります.ディスクの操作時間を増やしたのか、データベース・サーバとWEBサーバが独立している場合なのか.データがサーバ間で伝送される必要がないため、非常に長いネットワーク遅延が発生します.常に必要な列を指定するのは、非常に良い習慣です.

// not preferred 
$r = mysql_query("SELECT * FROM user WHERE user_id = 1"); 
$d = mysql_fetch_assoc($r); 
echo "Welcome {$d['username']}"; 
// better: 
$r = mysql_query("SELECT username FROM user WHERE user_id = 1"); 
$d = mysql_fetch_assoc($r); 
echo "Welcome {$d['username']}"; 
// the differences are more significant with bigger result sets

8.PROCEDURE ANALYSE()からのアドバイス
PROCEDURE ANALYSE()は、MySQLのカラム構造分析とテーブルの実際のデータをアドバイスします.もしあなたのテーブルに実際のデータがすでに存在しているならば、あなたの重大な意思決定のためにサービスすることができます.
9.用意された文
用意された文は、パフォーマンスの最適化とセキュリティの両方から役立ちます.
準備された文は、バインドされた変数をフィルタリングするデフォルトで、SQL注入攻撃を防止するためにアプリケーションに有効な保護を与えることができます.もちろん手動でフィルタリングすることもできますが、多くのプログラマーの忘れっぽい性格のため、効果が得られません.

// create a prepared statement 
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { 
 // bind parameters 
 $stmt->bind_param("s", $state); 
 // execute 
 $stmt->execute(); 
 // bind result variables 
 $stmt->bind_result($username);  
 // fetch value 
 $stmt->fetch(); 
 printf("%s is from %s
", $username, $state);  
 $stmt->close(); 
}

10.IPアドレスを符号なし整数として記憶する
多くのプログラマは、VARCHAR(15)を作成する際に、IPアドレスを整数形式で格納できることに気づかなかった.INTタイプがある場合は、4バイトのスペースしか占めません.これは固定サイズの領域です.IPアドレスが32ビットunsigned integerを使用するため、操作する列がUNSIGNED INTタイプであることを確認する必要があります.1. $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id"; MYSQLの検索文はまだたくさんありますが、今日はこの10種類についてお話しします.