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

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個についてお話します。

以下もご興味があるかもしれません:
  • MySQL で SQL クエリを最適化するための 30 の一般的な方法について簡単に説明します。
  • MySQL は SQL ステートメントの最新のレコードをクエリします (最適化)
  • MySQL SQL 文を最適化するための 10 のヒント
  • MySQL SQL ステートメント分析とクエリ最適化の詳細な説明
  • MySQL テーブルの読み取り、書き込み、インデックス作成、その他の操作の SQL ステートメントの効率最適化の問題を分析します。
  • MySQL SQL文を最適化するためのヒント
  • MySQL の最適化: 高品質の SQL 文を書く方法
  • MySQL を最適化するための 19 の一般的かつ効果的な方法 (推奨!)

<<:  スワイパープラグインを使用して Vue でカルーセルを実装する例

>>:  JS ES6 変数分割代入の詳細な説明

推薦する

Vue lazyload 画像遅延読み込み例の詳細な説明

ドキュメント: https://github.com/hilongjw/vue-lazyload 1...

Linuxの一般的なコマンドでLinuxのmoreコマンドを使用する方法

more は、最もよく使用されるツールの 1 つです。最も一般的な使用方法は、出力コンテンツを表示し...

MySQL の最初のインストールが成功した後にパスワードを初期化する手順

ファイルをディレクトリに解凍しますこれは解凍後のディレクトリですmy.iniファイルを入力しますダブ...

Vue.jsはカレンダー機能を実装します

この記事では、カレンダー機能を実装するためのVue.jsの具体的なコードを例として紹介します。具体的...

Linuxでpyファイルを直接実行する方法

1. まずファイルを作成します(ファイルを配置するディレクトリにcdします) myTest.py を...

VMWARE で Centos8 仮想マシンをコピーすることによって発生する IP 損失の問題の解決策

VMwareでcentos8サービスをインストールしてコピーすると、次の問題が発生します。 コピー前...

HTML フォームタグチュートリアル (2):

このチュートリアルでは、ウェブデザインにおけるFORMフォームタグのさまざまな属性の応用を紹介します...

JavaScript を使用したコマンドライン アプリケーションの構築

目次1. ノードをインストールする2. Commander.jsをインストールする3. JavaSc...

three.js を使って立体的な矢印線を描く詳細な手順

需要: この需要は緊急に必要です!地下鉄のシーンでは、脱出経路を示す矢印を描かなければなりません。こ...

Mysql の varchar 型に関する注意点

varchar の保存ルール4.0 未満のバージョンでは、varchar(20) は 20 バイトを...

MySQLインデックスの使用に関するヒントと注意事項

1. インデックスの役割一般的なアプリケーション システムでは、読み取りと書き込みの比率は約 10:...

Linux userdel コマンドの使用法

1. コマンドの紹介userdel (ユーザー削除) コマンドは、ユーザー アカウントと関連ファイル...

Vue のデータ応答性に関する詳細な理解

目次1. ES 構文のゲッターとセッター2. ES構文でのdefineProperty 3. Vue...

CSS3は、変換変形とイベントを組み合わせて扇形のナビゲーションを完成させます。

この場合、transition という単語を間違って書いたため、午後中ずっとそれに取り組みました。本...

JavaScript における正規表現の実際的な応用の詳細な説明

実際の業務では、JavaScript の正規表現が依然として頻繁に使用されます。したがって、この部分...