facebook twitter hatena line google mixi email

Mysql/indexの貼り方

提供: 初心者エンジニアの簡易メモ
移動: 案内検索

indexキーの基本知識

  1. MySQLのindexは1つのクエリーに対して1つしか効きません。

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をつけない。(つけると性能が落ちる

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

参考URL

サンプル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';