「Mysql/indexの貼り方」の版間の差分

2016年1月9日 (土) 08:44時点における版

indexキーの基本知識

1. 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');

ちなみに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で指定した結果データの半分以上がひっかかった場合

カーディナリティについて

カーディナリティが低いカラムには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) {
        }
    }
}