mysqlにindexを貼るとどのくらい変わるのか実験

index_test.md

mysqlのインデックスでどのくらい変わるのか実験

仕事ではなんのきなしにインデックス張るけど
実際どのくらい変わるものなのかを見たことが無いので
勉強がてらその変化・効果を計測。

準備：とりあえず1千万ちょいレコードを挿入しておく

流したクエリたち

CREATE TABLE `test` (
  id INT(11) NOT NULL AUTO_INCREMENT,
  string VARCHAR(64), -- 8桁のランダムな半角英数字が入る
  created_date TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `test` (`string`) VALUES (“hoge”)
 
ALTER TABLE `test` ADD INDEX index_id(id)
ALTER TABLE `test` DROP INDEX index_id

ALTER TABLE `test` ADD INDEX index_string(string)
ALTER TABLE `test` DROP INDEX index_string

インデックスなし

/////////////////////////////////////////
// before - no index
/////////////////////////////////////////
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 13101011 |
+----------+
1 row in set (6.89 sec)

mysql> select count(*) from test where string like "abc%" ;
+----------+
| count(*) |
+----------+
|     1738 |
+----------+
1 row in set (6.67 sec)

idにだけインデックス

/////////////////////////////////////////
// after - index_id
/////////////////////////////////////////
mysql> ALTER TABLE `test` ADD INDEX index_id(id);
Query OK, 0 rows affected (41.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 13101011 |
+----------+
1 row in set (3.41 sec)    <- ※半分くらいになった

mysql> select count(*) from test where string like "abc%" ;
+----------+
| count(*) |
+----------+
|     1738 |
+----------+
1 row in set (7.40 sec)    <- ※遅くなった

idとstringにインデックス

/////////////////////////////////////////
// after - index_id, index_string
/////////////////////////////////////////
mysql> ALTER TABLE `test` ADD INDEX index_string(string);
Query OK, 0 rows affected (1 min 35.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 13101011 |
+----------+
1 row in set (4.03 sec)    <- ※idだけの時より遅い

mysql> select count(*) from test where string like "abc%" ;
+----------+
| count(*) |
+----------+
|     1738 |
+----------+
1 row in set (0.04 sec)    <- ※爆速

stringにだけインデックス

/////////////////////////////////////////
// after - index_string
/////////////////////////////////////////
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 13101011 |
+----------+
1 row in set (4.20 sec)    <- ※id,stringのときと変わらない

mysql> select count(*) from test where string like "abc%" ;
+----------+
| count(*) |
+----------+
|     1738 |
+----------+
1 row in set (0.02 sec)    <- ※さらに爆速

総括

• 値に幅がある stringカラム にインデックスを貼るとやはりかなり効果的
• id,string 両方にインデックスを張るのは最速ではない
  　　　-> どこにでも貼ればいいという訳ではない
• 実行するSQLがどういうものかでインデックスの張り方を考える必要性

test.php

<?php
/**
 * MySQLにinsertをひたすらするクラス
 */
class Test 
{
	/**
	 * 実行
	 */
	public static function run()
	{
		$maxRow = 10000000; //insert する件数
		$interval = 10000; //進捗出力件数の間隔

		//DB接続
		$pdo = new PDO("mysql:dbname=db_test;host=localhost", "root", "");

		//処理
		for($i=0 ; $i<$maxRow; $i++){
			$str = Test::makeRandStr(8);
			$st = $pdo->prepare("INSERT INTO `test` (`string`) VALUES (?)");
  			$st->execute(array($str));

			if (!$st) {
			    die('faild to insert data '.$i.' : '.mysql_error()."\n");
			} else if($i%$interval == 0) {
				echo "inserted no ".$i."\n";
			}
		}
		echo "completed!!! \n";

		//DB切断
		$pdo = null;
	}

	/**
	 * ランダム文字列生成 (英数字)
	 * $length: 生成する文字数
	 * @see http://qiita.com/TetsuTaka/items/bb020642e75458217b8a
	 */
	public static function makeRandStr($length = 8) {
	    static $chars;
	    if (!$chars) {
	        $chars = array_flip(array_merge(
	            range('a', 'z'), range('A', 'Z'), range('0', '9')
	        ));
	    }
	    $str = '';
	    for ($i = 0; $i < $length; ++$i) {
	        $str .= array_rand($chars);
	    }
	    return $str;
	}

}

Test::run();

?>