const>eq_ref>ref>range>index>all。數(shù)據(jù)準(zhǔn)備:CREATE TABLE `t_blog..."/>
您好,登錄后才能下訂單哦!
explain的type列表示該條查詢的掃描范圍,一共有七種,效果由上到下排列:
system>const>eq_ref>ref>range>index>all。
數(shù)據(jù)準(zhǔn)備:
CREATE TABLE `t_blog` ( `id` int(11) NOT NULL auto_increment, `title` varchar(50) default NULL, `typeId` int(11) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `title_index` (`title`), KEY `type_index` (`typeId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `t_type` ( `id` int(11) NOT NULL auto_increment, `name` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1、system
該表中只有一行記錄,這種情況在日常開(kāi)發(fā)中很少見(jiàn),不多贅述;
2、const
表示通過(guò)索引一次就找到了結(jié)果,用于掃描主鍵和唯一索引,例如:
mysql> explain select * from t_blog where id = 1; +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | t_blog | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set
在where子句中,id為主鍵且值為一個(gè)常數(shù),在id索引中只有一條數(shù)據(jù)與之對(duì)應(yīng)。
3、eq_ref
通過(guò)主鍵和唯一索引,只有一條數(shù)據(jù)與之匹配,例如:
mysql> explain select b.* from t_blog b left join t_type t on b.typeId = t.id; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 7 | | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ 2 rows in set
eq_ref和const都表示在唯一索引或主鍵的作用下,只找到一行與之匹配的數(shù)據(jù)。const表示按主鍵和唯一索引讀取,eq_ref通常體現(xiàn)在連表上,按連表的主鍵和唯一索引讀取。
4、ref
非唯一索引掃描,有多個(gè)行與之匹配
mysql> explain select * from t_blog where typeId = 4; +----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | t_blog | ref | type_index | type_index | 5 | const | 1 | Using where | +----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+ 1 row in set
typeId是表的普通索引,即非唯一索引,與eq_ref最大的區(qū)別在于ref表示非唯一索引掃描。
5、range
表示范圍,使用索引選擇行,使用了 > < in beteen等
mysql> EXPLAIN select * from t_blog where id>2; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t_blog | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set
6、index
遍歷索引樹(shù),讀全表
mysql> EXPLAIN select id from t_blog; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t_blog | index | NULL | PRIMARY | 4 | NULL | 7 | Using index | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set
只查詢id,所以只遍歷索引文件即可,不需要從硬盤中讀取,比all快。
7、all
讀全表,不使用任何索引,從硬盤中讀數(shù)據(jù),最慢
mysql> explain select * from t_blog; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_blog | ALL | NULL | NULL | NULL | NULL | 7 | | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set
*在一般的開(kāi)發(fā)過(guò)程中,達(dá)到ref即可
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。