MySql(28)------使用explain分析低效sql的执行情况

使用explain命令可以获取MySql执行SELECT语句的信息。

eg:

建表和插入数据：

CREATE TABLE t_test1(  f_userId INT(11) NOT NULL PRIMARY KEY,  f_userName VARCHAR(225) NOT NULL);INSERT INTO t_test1 VALUES (1,'one'),(2,'two'),(3,'three'),(4,'four');

使用explain分析查询：

mysql> explain select count(*) from t_test1;+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | t_test1 | index | NULL          | PRIMARY | 4       | NULL |    4 | Using index |+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+1 row in set

对查询结果相关说明:

select_type： 表示select的类型，主要包括simple(简单表，也就是不使用表连接和子查询)，

primary(主键表),union(union查询sql集合的union后的第二个或往后的查询语句),subquery(子查询中的第一个select)等等

table: 查询的表

type: 访问类型

注意包括以下访问类型：

all---->index---->range---->ref---->const,system---->null

不同的访问类型，查询的效率不一样，从左到右，效率由低到高。

******type = all,进行全表扫描

建表，插入数据：

CREATE TABLE t_test2(  f_userId INT(11) NOT NULL ,  f_userName VARCHAR(225) NOT NULL);INSERT INTO t_test2 VALUES (1,'one'),(2,'two'),(3,'three'),(4,'four');

explain分析：

mysql> explain select * from t_test2 where f_userId > 2;+----+-------------+---------+------+---------------+------+---------+------+------+-------------+| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+---------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | t_test2 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |+----+-------------+---------+------+---------------+------+---------+------+------+-------------+1 row in set

表中没有主键，也没有索引，查询时进行全表扫描。

******type=index,索引全扫描

在刚才的t1表上新建一个索引：

CREATE UNIQUE INDEX idx_userName ON t_test1(f_userName);

explain分析：

mysql> explain select * from t_test1 where f_userId > 2;+----+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+| id | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |+----+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+|  1 | SIMPLE      | t_test1 | index | PRIMARY       | idx_userName | 227     | NULL |    4 | Using where; Using index |+----+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+1 row in set

因为有索引，MySql按照整个索引查询匹配的行。

******type=range,索引范围扫描

创建表，插入数据，在f_age上创建唯一索引：

CREATE TABLE t_test3(  f_userId INT(11) NOT NULL ,  f_userName VARCHAR(225) NOT NULL,  f_age INT(11) NOT NULL);INSERT INTO t_test3 VALUES (1,'one',22),(2,'two',23),(3,'three',24),(4,'four',25);CREATE UNIQUE INDEX idx_age ON t_test3(f_age);

explain分析：

mysql> explain select * from t_test3 where f_age > 22;+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | t_test3 | range | idx_age       | idx_age | 4       | NULL |    3 | Using where |+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+1 row in set

索引范围扫描是在索引上使用>,>=,<,<=,between等表示范围的语句进行索引+范围的扫描表。
******type=ref,使用非唯一索引扫描或者唯一索引的前缀扫描

创建表，插入数据，创建非唯一索引

CREATE TABLE t_test4(  f_userId INT(11) NOT NULL ,  f_userName VARCHAR(225) NOT NULL,  f_age INT(11) NOT NULL);INSERT INTO t_test4 VALUES (1,'one',22),(2,'two',23),(3,'three',24),(4,'four',25);CREATE INDEX idx_age ON t_test4(f_age);

explain分析：

mysql> explain select * from t_test4 where f_age = 24;+----+-------------+---------+------+---------------+---------+---------+-------+------+-------+| id | select_type | table   | type | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+---------+------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | t_test4 | ref  | idx_age       | idx_age | 4       | const |    1 |       |+----+-------------+---------+------+---------------+---------+---------+-------+------+-------+1 row in set

如果将等号改成>等比较符号，将变成索引范围扫描。

******type=const/system,单行匹配，根据主键或唯一索引查询

创建表，插入数据，创建唯一索引

CREATE TABLE t_test5(  f_userId INT(11) NOT NULL ,  f_userName VARCHAR(225) NOT NULL,  f_age INT(11) NOT NULL);INSERT INTO t_test5 VALUES (1,'one',22),(2,'two',23),(3,'three',24),(4,'four',25);CREATE UNIQUE INDEX idx_age ON t_test5(f_age);

explain分析：

mysql> explain select * from t_test5 where f_age = 24;+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | t_test5 | const | idx_age       | idx_age | 4       | const |    1 |       |+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+1 row in set

*******type=NULL，MySql不用访问表或索引就能直接查询出结果

mysql> explain select 12+20 from dual;+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+1 row in set

type除了以上类型外，还有eq_ref等类型