MySQLのカバリングIndex が効いているかを実験してみる

ダミーのテーブルを作成する

CREATE TABLE item (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  description VARCHAR(30),
  price INT UNSIGNED,
  created_at DATETIME
);

Indexをはる

CREATE INDEX price_index on item( price )

INSERT INTO item () VALUES ();
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;

UPDATE item SET
  name = CONCAT('商品', id),
  description = SUBSTRING(MD5(RAND()), 1, 30),
  price = CEIL(RAND() * 10000),
  created_at = ADDTIME(CONCAT_WS(' ','2014-01-01' + INTERVAL RAND() * 180 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));

結果

mysql> select * from item limit 1;
+----+---------+--------------------------------+-------+---------------------+
| id | name    | description                    | price | created_at          |
+----+---------+--------------------------------+-------+---------------------+
|  1 | 商品1   | 06689c82aa651e7e631cba28a78bc8 |  4066 | 2014-05-31 22:42:23 |
+----+---------+--------------------------------+-------+---------------------+

mysql> show indexes from item\G;
*************************** 1. row ***************************
        Table: item
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2097099
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: item
   Non_unique: 1
     Key_name: price_index
 Seq_in_index: 1
  Column_name: price
    Collation: A
  Cardinality: 2097099
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.01 sec)

mysql> select * from item where price = 112 limit 10000;
215 rows in set (0.07 sec)

mysql> select name from item where price = 114 limit 10000;
197 rows in set (0.04 sec)

mysql> select description from item where price = 115 limit 10000;
213 rows in set (0.04 sec)

mysql> select price from item where price = 113 limit 10000;
202 rows in set (0.00 sec)

price にカバリングインデックスが効いているため、 インデックスを張っていない他のカラム(name とdescription)に比べて早くなっている。 price の場合は index が使われていることがわかる。

mysql> explain select price from item where price = 116 limit 10;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | item  | ref  | price_index   | price_index | 5       | const |  202 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql>
mysql> explain select name from item where price = 116 limit 10;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | item  | ref  | price_index   | price_index | 5       | const |  202 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)