MySQLのINDEXと複合INDEXとINDEXなしの時間を計測 - nicepaper @ ウィキ

MySQLのINDEXの張り方について記述していきたいと思います。 MySQLにてテーブル作成する場合、主キー、カラムとその型の記述は不可欠ですが、検索を高速化するためにINDEXを追加して張ることがあると思います。そのINDEXの張り方においてどれくらい効果があるのかを見ていきたいと思います。7月26日記事

目次

---

テーブル設計

id(INT型),col1(VARCHAR型),col2(INT型),col3(INT型)にします。 テーブルはこんな感じ

CREATE TABLE なんちゃら_index_table(
id INT NOT NULL PRIMARY KEY auto_increment,
col1 VARCHAR(255)  DEFAULT NULL,
col2 INT DEFAULT NULL,
col3 INT DEFAULT NULL
);

この「なんちゃら」部分を色々と変えていきたいと思います。

INDEXを一つも張らない。

CREATE TABLE no_index_table(
id INT NOT NULL PRIMARY KEY auto_increment,
col1 VARCHAR(255)  DEFAULT NULL,
col2 INT DEFAULT NULL,
col3 INT DEFAULT NULL
);

INDEXを一つずつ張る。

CREATE TABLE single_index_table(
id INT NOT NULL PRIMARY KEY auto_increment,
col1 VARCHAR(255)  DEFAULT NULL,
col2 INT DEFAULT NULL,
col3 INT DEFAULT NULL,
INDEX idx_col2(col2), 
INDEX idx_col3(col3) 
);

INDEXを複合して張る。

multindex_table
CREATE TABLE multi_index_table(
id INT NOT NULL PRIMARY KEY auto_increment,
col1 VARCHAR(255)  DEFAULT NULL,
col2 INT DEFAULT NULL,
col3 INT DEFAULT NULL,
INDEX multi(col2,col3)
);

データ挿入

以下のプログラムを走らせ、INSERTするテーブル先を変えていきました。

require('config.php');

$dsn = 'mysql:dbname='.DB_NAME.';host='.DB_HOST.';port='.DB_PORT.'';
$user = DB_USER;
$password = DB_PASSWORD;

try{
	$dbh = new PDO($dsn, $user, $password);	
}catch (PDOException $e){
	print('Connection failed:'.$e->getMessage());
	die();
}

for($i = 1; $i < 10001; $i++){
	$sql = "INSERT INTO no_index_table ( col1,col2,col3) values ";
	
	if($i % 7 == 0 && $i % 100 == 0){//700おき	
	$sql .= "('col1_".$i."','350','700')";
	}elseif($i %  7 == 0 ){//7おき
	$sql .= "('col1_".$i."','350','7')";
	}elseif($i % 100 == 0 ){//100おき
	$sql .= "('col1_".$i."','350','100')";	
	}else{//それ以外
	$sql .= "('col1_".$i."','".$i."','".$i."')";
	}
	$stmt = $dbh->query($sql);
	$sql = '';
}

※上部分はno_index_tableですが、これをsingle_index_table,multi_index_tableにそれぞれ書き換え1万件分のデータを入れました。

計測

それぞれのテーブルに対して実行してみてその時間を計測します。クエリは

SELECT * FROM なんちゃら_index_table WHERE col2 = '350' AND col3 = '700';

です。

	no	single	multi
1	0.0038	0.0019	0.0003
2	0.0039	0.0009	0.0004
3	0.0039	0.001	0.0011
4	0.0042	0.0017	0.0006
5	0.0039	0.0019	0.0004
6	0.004	0.0021	0.0005
7	0.0043	0.001	0.0004
8	0.0068	0.0009	0.0005
9	0.0039	0.0009	0.0005
10	0.0041	0.001	0.0004
平均	0.00428	0.00133	0.00051

まとめ

データが1万件ではさほどクリティカルな差はでないが、やはり複合INDEXを張った方が早いということになる。 INDEXをそれぞれ張ったとしても最初のカラム（今回の場合ではcol2）にしか効かない。 データがあまりにも多い場合はやはり複合INDEXを使うようにしていきましょう。 データが少ない場合はあまり変わりません。

以上