MySQL インデックスがソートに与える影響の分析例

この記事では、例を使用して、MySQL インデックスがソートに与える影響を説明します。ご参考までに、詳細は以下の通りです。

インデックスはクエリ速度を向上させるだけでなく、ソート速度も向上させます。order by の後のステートメントでインデックスを使用すると、ソート速度が向上します。

テスト

1. テストテーブルを作成する: t15 テーブル

テーブル `t15` を作成します (
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 `cat_id` int(10) unsigned NOT NULL DEFAULT '0',
 `price` 小数点(10,2) NOT NULL デフォルト '0.00',
 `name` char(5) NOT NULL DEFAULT ''
 主キー (`id`)、
) エンジン=InnoDB デフォルト文字セット=utf8

2. 10,000行のデータを挿入する

<?php
$db = 'テスト';
ローカルホストに接続します。
mysql_query('use ' . $db , $conn);
mysql_query('名前をutf8に設定', $conn);
($i=1;$i<=10000;$i++) の場合 {
  1. 10 進数で表されたcat_idの値。
  価格 = ランド(1,50000);
  $name = substr(str_shuffle('abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ234565789'),0,5);
  $sql = sprintf("t15 値に挿入 (%d,%d,%f,'%s')",$i,$cat_id,$price,$name);
  mysql_query($sql, $conn);
}

3. ショッピングモールのウェブサイトでは通常、価格が特定のカテゴリごとに並べ替えられます。これをシミュレートして、同じ SQL ステートメントを実行してみましょう。

cat_id=1 の場合、t15 から name、cat_id、price を選択し、price で並べ替えます。

（１）最初にインデックスを追加しない

クエリ分析から、Extra でUsing filesortが使用されていることがわかります。これは、ファイルのソートが必要であることを示しています。

（２）（cat_id, price）列にインデックスを追加する

テーブル t15 を変更し、インデックス cp(cat_id,price) を追加します。

ここでのクエリ分析では、Extra は Using filesort を使用していません。つまり、インデックスを追加してからクエリを実行する SQL では、外部ソートは使用されず、インデックスソートが使用されます。インデックス自体がソートされるため、追加の order by は必要ありません。

4. 観測クエリ時間

Query_ID 1 のステートメントはインデックスを追加せずに実行され、Query_ID 3 のステートメントはインデックスを追加した後に実行されます。一方の実行時間は 0.013 秒で、もう一方の実行時間は 0.005 秒です。インデックスありの方が明らかに高速です。どこが高速かを見てみましょう。

Query_ID 1 の SQL ステートメントの詳細な時間のかかるグラフ:

Query_ID 3 の SQL ステートメントの詳細な時間消費図:

明らかに、Query_ID1 のSorting resultには 0.012 秒かかりますが、Query_ID2 のSorting resultには 0.000004 秒しかかかりません。このSorting resultがソート時間です。

結論：インデックスはソートの速度に一定の影響を与えます。そのため、実際の開発では、実際の状況に基づいてインデックスを策定し、ソートフィールドを可能な限りインデックスに追加する必要があります。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、および「MySQL データベース ロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。