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 変数分割代入の詳細な説明

推薦する

css n番目から始まるすべての要素を取得する

具体的なコードは次のとおりです。 <div id="ボックス"> &...

XHTML 入門チュートリアル: XHTML ハイパーリンク

ハイパーリンクはインターネット全体を接続していると言っても過言ではありません。ハイパーリンクは、別の...

InnoDB の主な機能 - 挿入キャッシュ、2 度書き込み、適応ハッシュ インデックスの詳細

InnoDB ストレージ エンジンの主な機能には、挿入バッファ、二重書き込み、適応ハッシュインデック...

Vue2.0/3.0 での provide と inject の使用例

目次1. provide/inject の用途は何ですか? 2. provide/injectの使い...

nginx 設定ファイルパスとリソースファイルパスを表示する方法

nginx 設定ファイルのパスを表示する nginx -t 経由nginx -t コマンドの本来の機...

IE6/7 における a.getAttribute(href,2) 問題の分析と解決

簡単な説明<br />IE6および7では、一般的なaタグ(HTMLで記述され、DOM操作...

CSS3 変換によって子要素の固定位置を絶対位置に変更する方法

この記事では、CSS3 の transform を使用して子要素の固定配置を絶対配置に変更する方法を...

CSS XTHML の記述標準とよくある問題の概要 (ページ最適化)

プロジェクトドキュメントディレクトリDiv+CSS 命名規則 - 4 - Div+css 命名規則 ...

Dockerで新しいイメージを手動で構築する方法

この記事では、Docker で新しいイメージを手動で構築する方法を紹介し、皆さんと共有します。詳細は...

アプレットにおけるwx.getUserProfileインターフェースの具体的な使用

最近、WeChatミニプログラムは、監査ミニプログラムのwx.loginおよびwx.getUserI...

Vue エクスポート Excel 機能の全プロセス記録

目次1. フロントエンドのリーディングプロセス: 2. プラグインの使用と初期化2.1 vue-ad...

Xiaomi公式サイトの登録・ログイン機能を模倣するJavaScript

目次まずページレイアウトを構築する必要がありますJS関数1 JS関数2 JS関数3 JS関数4効果図...

フローチャートとUIフローの違い

UI デザインにおける多くの概念は言葉で言えば似ているように見えるかもしれませんが、実際には大きく異...

Mysqlリモート接続構成を実装する2つの方法

Mysqlリモート接続構成を実装する2つの方法会社で働いていると、誰かのコンピュータに保存されている...

Vueはアップロードコンポーネントを実装します

目次1. はじめに2. アイデアファイルをアップロードする2つの方法3. ライフサイクル4. コード...