Mysql/indexの貼り方
提供: 初心者エンジニアの簡易メモ
目次
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');
参考show table
CREATE TABLE `areas` ( `ken` varchar(20) DEFAULT NULL, `si` varchar(20) DEFAULT NULL, UNIQUE KEY `uniq_areas` (`ken`,`si`), KEY `idx_areas` (`ken`,`si`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ちなみにindex,uniqueキー削除方法はこちら
alter table テーブル名 drop index インデックス名
サンプルデータ
- 県: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で指定した結果データの半分以上がひっかかった場合
explainのpossible_keysについて
index候補としてあげられたキー
explainのkeyについて
実際に選択されたindexキー
カーディナリティについて
カーディナリティが低いカラムには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.jp/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) {
}
}
}
index削除方法
ALTER TABLE テーブル名 DROP INDEX インデックス名;
強制的にsqlにindexを適用
SELECT * FROM t1 FORCE INDEX idx_key1 WHERE key1='hoge';
