MySql学习笔记(六):扫描范围
explain的type列表示该条查询的扫描范围,一共有七种,效果由上到下排列:
成都创新互联坚信:善待客户,将会成为终身客户。我们能坚持多年,是因为我们一直可值得信赖。我们从不忽悠初访客户,我们用心做好本职工作,不忘初心,方得始终。十年网站建设经验成都创新互联是成都老牌网站营销服务商,为您提供成都网站建设、成都网站制作、网站设计、H5网站设计、网站制作、品牌网站制作、微信小程序开发服务,给众多知名企业提供过好品质的建站服务。
system>const>eq_ref>ref>range>index>all。
数据准备:
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
该表中只有一行记录,这种情况在日常开发中很少见,不多赘述;
2、const
表示通过索引一次就找到了结果,用于扫描主键和唯一索引,例如:
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为主键且值为一个常数,在id索引中只有一条数据与之对应。
3、eq_ref
通过主键和唯一索引,只有一条数据与之匹配,例如:
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都表示在唯一索引或主键的作用下,只找到一行与之匹配的数据。const表示按主键和唯一索引读取,eq_ref通常体现在连表上,按连表的主键和唯一索引读取。
4、ref
非唯一索引扫描,有多个行与之匹配
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最大的区别在于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
遍历索引树,读全表
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
读全表,不使用任何索引,从硬盘中读数据,最慢
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
*在一般的开发过程中,达到ref即可
文章题目:MySql学习笔记(六):扫描范围
转载注明:http://scyanting.com/article/gpojhe.html