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)