Record access types

提供：MySQL Practice Wiki

EXPLAINのtypeフィールドに表示される項目は、テーブルに対してどのようにアクセスを行うかを示す。簡単にいうとテーブルスキャンなのかインデックスが使われてるのか、範囲検索なのか等価比較による検索なのか・・・といったことが、typeフィールドを見ることで判断できるわけである。

目次 1 const 2 system 3 ALL 4 index 5 eq_ref 6 ref 7 ref_or_null 8 range 9 fulltext 10 index_merge 11 unique_subquery 12 index_subquery

const

PRIMARY KEYやUNIQUE KEYによるルックアップ（等価比較）が行われた時に用いられるタイプ。PRIMARY KEYやUNIQUE KEYには重複したレコードがないため、これらのキーに対しする等価比較を条件に検索をかけると、1行または0行の結果が返ってくることになる。この場合、オプティマイザは検索結果を最初に取得して、定数（constant）のように見なす。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM Country WHERE Code='JPN'; +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | Country | const | PRIMARY | PRIMARY | 3 | const | 1 | | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) </highlightSyntax>

system

テーブルに一行しかないとき、かつ行数を正確にカウントしているストレージエンジン（MyISAM、MEMORYなど）を利用している時に利用される特殊なアクセスタイプである。constと似ているが、こちらは別に主キーがなくても構わない。 <highlightSyntax language="mysql"> mysql> CREATE TEMPORARY TABLE systest SELECT * FROM Country LIMIT 1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE systest ENGINE MyISAM; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM systest; +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | systest | system | NULL | NULL | NULL | NULL | 1 | | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) </highlightSyntax> ストレージエンジンをInnoDBに変更すると、typeはconstではなくALLになってしまう。 <highlightSyntax language="mysql"> mysql> ALTER TABLE systest ENGINE InnoDB; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM systest; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | systest | ALL | NULL | NULL | NULL | NULL | 1 | | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.01 sec) </highlightSyntax>

ALL

フルテーブルスキャンが発生していることを示す。WHERE句で指定されている検索条件において、利用出来るインデックスが存在しない場合などにこのタイプになってしまう。

index

フルインデックススキャン。名前からすると「効率的にインデックスを使ってるのか？」と思ってしまいがちだが、インデックスを全部なめる必要があるのでWHERE句の条件の指定が悪いとき、かつインデックスがはられたカラムだけを結果として返す必要がある場合などに用いられる。フルテーブルスキャン（ALL）よりは軽い処理である。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT Code FROM Country; +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | Country | index | NULL | PRIMARY | 3 | NULL | 239 | Using index | +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) </highlightSyntax>

eq_ref

JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。JOINに用いられる条件、つまりもう一方のテーブルのカラムはrefフィールドに表示される。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM Country, City WHERE Country.Code = City.CountryCode ORDER BY City.Population DESC; +----+-------------+---------+--------+---------------+---------+---------+------------------------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+---------+---------+------------------------+------+----------------+ | 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using filesort | | 1 | SIMPLE | Country | eq_ref | PRIMARY | PRIMARY | 3 | world.City.CountryCode | 1 | | +----+-------------+---------+--------+---------------+---------+---------+------------------------+------+----------------+ 2 rows in set (0.00 sec) </highlightSyntax>

ref

ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。JOINでも単一のSELECTでもインデックスがユニークでない場合はrefになる。

単一のSELECTの場合。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX (Name); Query OK, 239 rows affected (0.46 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE NAME = 'Japan'; +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | Country | ref | Name | Name | 52 | const | 1 | Using where | +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) </highlightSyntax> JOINの場合 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (CountryCode); Query OK, 4079 rows affected (0.48 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM Country,City WHERE Country.Code = City.CountryCode AND Continent = 'Asia'; +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------------+ | 1 | SIMPLE | Country | ALL | PRIMARY | NULL | NULL | NULL | 239 | Using where | | 1 | SIMPLE | City | ref | CountryCode | CountryCode | 3 | world.Country.Code | 18 | | +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------------+ 2 rows in set (0.01 sec) </highlightSyntax>

ref_or_null

refと似ているが、OR条件でIS NULLが指定されている時にref_or_nullになる。以下のサンプルを見れば一目瞭然である。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX (Capital); Query OK, 239 rows affected (0.08 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE Capital = 5 OR Capital IS NULL; +----+-------------+---------+-------------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | Country | ref_or_null | Capital | Capital | 5 | const | 7 | Using where | +----+-------------+---------+-------------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) </highlightSyntax>

range

インデックスを用いた範囲検索。（ただしBツリーインデックスやMySQL ClusterのOrdered Indexのように順序がついたインデックスでないと使用不可） <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX (IndepYear); Query OK, 239 rows affected (0.42 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT Name FROM Country WHERE IndepYear BETWEEN 1800 AND 1900; +----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | Country | range | IndepYear | IndepYear | 3 | NULL | 17 | Using where | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.00 sec) </highlightSyntax>

fulltext

読んで字のごとく！！フルテキストインデックスを利用した検索。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD FULLTEXT INDEX (Name); Query OK, 239 rows affected (0.12 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE MATCH(Name) AGAINST ('Kingdom'); +----+-------------+---------+----------+---------------+--------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+----------+---------------+--------+---------+------+------+-------------+ | 1 | SIMPLE | Country | fulltext | Name_2 | Name_2 | 0 | | 1 | Using where | +----+-------------+---------+----------+---------------+--------+---------+------+------+-------------+ 1 row in set (0.00 sec) </highlightSyntax>

index_merge

2種類のインデックスを個別にOR条件で使用したときに利用されるアクセスタイプ。それぞれの検索条件はrefでもrangeでも良い。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX (Continent); Query OK, 239 rows affected (0.12 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Country ADD INDEX (GNP); Query OK, 239 rows affected (0.46 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE Population > 200000000 OR Continent = 'Atrantica'; +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | Country | index_merge | Population,Continent | Population,Continent | 4,1 | NULL | 11 | Using sort_union(Population,Continent); Using where | +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) </highlightSyntax>

unique_subquery

DEPENDENT SUBQUERYにおいてPRIMARY KEYまたはUNIQUEインデックスを用いてサブクエリが評価されるアクセスタイプ。この場合、外部クエリはさておきサブクエリは高速である。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM Country WHERE Capital IN (SELECT ID FROM City WHERE Name LIKE 'Q%'); +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where | | 2 | DEPENDENT SUBQUERY | City | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec) </highlightSyntax>

index_subquery

unique_subqueryと似ているが、サブクエリで利用されるインデックスがユニークでない場合に利用される。 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (CountryCode); Query OK, 4079 rows affected (0.55 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM City WHERE Population > 10000000); +----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+ | 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where | | 2 | DEPENDENT SUBQUERY | City | index_subquery | CountryCode | CountryCode | 3 | func | 18 | Using where | +----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+ 2 rows in set (0.00 sec) </highlightSyntax>