Mysql/indexの貼り方 - 変更履歴

Admin: /* index検証データ */

2025-05-07T03:47:55Z

‎index検証データ

Admin: /* ちなみにindex,uniqueキー削除方法はこちら */

2025-02-25T09:48:53Z

‎ちなみにindex,uniqueキー削除方法はこちら

Admin: /* 強制的にsqlにindexを適用 */

2017-07-04T14:38:43Z

‎強制的にsqlにindexを適用

Admin: /* index削除方法 */

2017-07-04T14:38:32Z

‎index削除方法

Admin: /* index検証データ */

2017-04-22T01:45:23Z

‎index検証データ

Admin: /* サンプルphp */

2016-08-13T18:29:08Z

‎サンプルphp

2016年3月14日 (月) 03:50にAdminによる

2016-03-14T03:50:08Z

2016年3月14日 (月) 03:49にAdminによる

2016-03-14T03:49:38Z

Admin: /* index検証データ */

2016-01-08T23:44:37Z

‎index検証データ

127.0.0.1: ページの作成:「==indexキーの基本知識== #MySQLのindexは１つのクエリーに対して１つしか効きません。 ==select時にインデックスを追加しなければ...」

2015-05-19T18:08:38Z

ページの作成:「==indexキーの基本知識== #MySQLのindexは１つのクエリーに対して１つしか効きません。 ==select時にインデックスを追加しなければ...」

新規ページ

==indexキーの基本知識==
#MySQLのindexは１つのクエリーに対して１つしか効きません。

==select時にインデックスを追加しなければならないかを確認==
EXPLAIN構文
http://dev.mysql.com/doc/refman/4.1/ja/explain.html

==index検証データ==
create table areas(ken varchar(20),si varchar(20));
create index idx_areas on areas(ken,si);
create unique index uniq_areas on areas(ken,si);
insert into areas values('tokyo', 'sinjuku');

==サンプルデータ==
*県:47レコード
*市:470000レコード

==実行速度==
mysql> select * from areas where ken = 'tokyo10'; --ok
10000 rows in set (0.02 sec)
mysql> select * from areas where si='sinjuku999968669'; --ng
1 row in set (0.30 sec)
mysql> select * from areas where si='sinjuku999968669' and ken ='tokyo10'; --ok
1 row in set (0.00 sec)
mysql> select * from areas where ken = 'tokyo10' and si='sinjuku999968669'; --ok
1 row in set (0.00 sec)

==explain結果==
mysql> explain select * from areas where ken = 'tokyo10';
+----+-------------+-------+------+---------------------+------------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+------------+---------+-------+-------+--------------------------+
| 1 | SIMPLE | areas | ref | uniq_areas,idx_area | uniq_areas | 23 | const | 21038 | Using where; Using index |
+----+-------------+-------+------+---------------------+------------+---------+-------+-------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select * from areas where si='sinjuku999968669';
+----+-------------+-------+-------+---------------+------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | areas | index | NULL | uniq_areas | 46 | NULL | 466365 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from areas where si='sinjuku999968669' and ken ='tokyo10';
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
| 1 | SIMPLE | areas | const | uniq_areas,idx_area | uniq_areas | 46 | const,const | 1 | Using index |
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from areas where ken = 'tokyo10' and si='sinjuku999968669';
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
| 1 | SIMPLE | areas | const | uniq_areas,idx_area | uniq_areas | 46 | const,const | 1 | Using index |
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+

--unique keyを削除後に再度explain
alter table areas drop index uniq_areas;
mysql> explain select * from areas where si='sinjuku999968669' and ken ='tokyo10';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | areas | ref | idx_area | idx_area | 46 | const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from areas where ken = 'tokyo10' and si='sinjuku999968669';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | areas | ref | idx_area | idx_area | 46 | const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+

==explain typeについて==
*const: primary,uniqueによるルックアップが行われた。最速
*eq_ref: primary,uniqueによるルックアップ（joinあり時）に表示。
*ref: indexによるルックアップが行われた
*index: フルインデックススキャンが行われた。遅い
*ALL: indexなど全く使われてない。激遅

index,ALLがチューニング対象

==indexを使っていてもtypeがALLになる場合==
whereで指定した結果データの半分以上がひっかかった場合

==カーディナリティについて==
カーディナリティが低いカラムにはindexをつけない。（つけると性能が落ちる

カーディナリティとは値に対する種類の数で性別などは２種類しかないので、
カーディナリティが低いとされる。

==参考URL==
*http://ameblo.jp/wataru420/entry-10839087804.html
*http://d.hatena.ne.jp/kiyo560808/20101117/1289952549
*http://www.hi-ho.ne.jp/tsumiki/doc_1.html
*http://nippondanji.blogspot.com/2009/03/mysqlexplain.html

==サンプルphp==
require_once 'Zend/Db.php';
$db = Zend_Db::factory('Pdo_Mysql', array(
'host' => 'localhost',
'username' => '*****',
'password' => '******',
'dbname' => 'test',
'charset' => 'utf8'
));
for ($i = 1;$i <= 47; $i++) {
print $i."\n";
for ($j = 1; $j <= 10000; $j++) {
try {
$sql = "insert into areas values('tokyo" . $i . "' , 'sinjuku" . rand() . "');";
$rows = $db->query($sql);
} catch (Exception $e) {
}
}
}

@@ 行7: / 行7: @@
 ==index検証データ==
-  create table areas(ken varchar(20),si varchar(20));
+  CREATE INDEX `areas`(`ken` varchar(20),`si` varchar(20));
-  create index idx_areas on areas(ken,si);
+  CREATE INDEX `idx_areas` on `areas`(`ken`,`si`);
-  create unique index uniq_areas on areas(ken,si);
+  CREATE UNIQUE INDEX `uniq_areas` on areas(`ken`,`si`);
-  insert into areas values('tokyo', 'sinjuku');
+  INSERT INTO `areas` VALUES('tokyo', 'sinjuku');
 ==参考show table==

@@ 行19: / 行19: @@
    KEY `idx_areas` (`ken`,`si`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 ==ちなみにindex,uniqueキー削除方法はこちら==

←前の版		2017年7月4日 (火) 14:38時点における版
行140:		行140:

	==強制的にsqlにindexを適用==		==強制的にsqlにindexを適用==
−	SELECT * FROM t1 FORCE INDEX idx_key1 ~~WHERE~~ WHERE key1='hoge';	+	SELECT * FROM t1 FORCE INDEX idx_key1 WHERE key1='hoge';

←前の版		2017年7月4日 (火) 14:38時点における版
行138:		行138:
	==index削除方法==		==index削除方法==
	ALTER TABLE テーブル名 DROP INDEX インデックス名;		ALTER TABLE テーブル名 DROP INDEX インデックス名;
		+
		+	==強制的にsqlにindexを適用==
		+	SELECT * FROM t1 FORCE INDEX idx_key1 WHERE WHERE key1='hoge';

←前の版		2016年8月13日 (土) 18:29時点における版
行127:		行127:
	}		}
	}		}
		+
		+	==index削除方法==
		+	ALTER TABLE テーブル名 DROP INDEX インデックス名;

@@ 行106: / 行106: @@
 *http://d.hatena.ne.jp/kiyo560808/20101117/1289952549
 *http://www.hi-ho.ne.jp/tsumiki/doc_1.html
-*http://nippondanji.blogspot.com/2009/03/mysqlexplain.html
+*http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
 ==サンプルphp==

@@ 行89: / 行89: @@
 ==indexを使っていてもtypeがALLになる場合==
 whereで指定した結果データの半分以上がひっかかった場合
 ==カーディナリティについて==