使用explain来分析SQL语句实现优化SQL语句
用法：explain sql
作用：用于分析sql语句
mysql> explain select * from quser_1 where loginemail = "quctest2124@163.com"; +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | quser_1 | ref | loginemailindex | loginemailindex | 302 | const | 1 | Using index condition | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ 1 row in set mysql>
0： id表示执 explain 的一个编号(没有实际意义)
1：table 查询的表名
2：select_type查询类型，是单表查询、联合查询还是子查询，可能会出现以下值：
| 查询类型 | 说明 |
| SIMPLE | 简单的 select 查询，不使用 union 以及子查询 |
| PRIMARY | 最外层的 select 查询(使用到主键作为查询条件) |
| UNION UNION | UNION 中的第二个或者随后的 select 查询，不依赖于外部查询的结果集 |
| DEPENDENT UNION | UNION 中的第二个或者随后的 select 查询，依赖于外部查询的结果集 |
| SUBQUERY | 子查询中的第一个 select 查询，不依赖于外部查询的结果集 |
| DEPENDENT SUBQUERY | 子查询中的第一个 select 查询,依赖于外部 查询的结果集 |
| DERIVED | 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里 |
| UNCACHEABLE SUBQUERY | 结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估 |
| UNCACHEABLE UNION UNION | UNION中的第二个或随后的 select 查询,属 于不可缓存的子查询 |
例如使用如下表结构：
CREATE TABLE `quser_1` ( `quserid` int(10) unsigned NOT NULL DEFAULT '0', `username` varchar(64) NOT NULL DEFAULT '', `id` int(10) unsigned NOT NULL DEFAULT '0', `ver` int(10) unsigned NOT NULL DEFAULT '0', `password` varchar(40) NOT NULL DEFAULT '', `randomkey` varchar(30) NOT NULL DEFAULT '', `sealtime` varchar(30) NOT NULL DEFAULT '', `status` tinyint(1) NOT NULL DEFAULT '0', `src` varchar(50) NOT NULL DEFAULT '', `ip` varchar(11) NOT NULL DEFAULT '0', `regtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `tastetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `lastmodifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `loginemail` varchar(100) NOT NULL DEFAULT '', `loginmethod` tinyint(3) unsigned NOT NULL DEFAULT '0', `va` varchar(1000) NOT NULL DEFAULT '' COMMENT 'virtual account info', PRIMARY KEY (`quserid`) KEY_BLOCK_SIZE=1024, UNIQUE KEY `username` (`username`) KEY_BLOCK_SIZE=1024, KEY `id` (`id`) KEY_BLOCK_SIZE=1024, KEY `loginemailindex` (`loginemail`) KEY_BLOCK_SIZE=2048 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
示例1：使用简单查询
mysql> explain select * from quser_1 where loginemail = "quctest2124@163.com"; +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | quser_1 | ref | loginemailindex | loginemailindex | 302 | const | 1 | Using index condition | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ 1 row in set mysql>
type 说明：
| type | 说明 |
| system | 表仅有一行(=系统表)。这是 const 连接类型的一个特例 |
| const | const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System |
| eq_ref | const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System |
| ref | 连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值 |
| ref_or_null | 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找 |
| index_merge | 说明索引合并优化被使用了 |
| unique_subquery | 在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) |
| index_subquery | 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr) |
| range | 只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range |
| index | 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大 |
| all | 最坏的情况,从头到尾全表扫描 |
示例2：type 为 const
mysql> explain select * from quser_1 where quserid = "3000096101"; +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | quser_1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set
示例3： type 为 all (这种是要优化和避免的)
mysql> explain select * from quser_1 where src = "pcw"; +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | quser_1 | ALL | NULL | NULL | NULL | NULL | 1274 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set
示例4： type 为 ref
mysql> explain select * from quser_1 where loginemail = '11223344@qq.com'; +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | quser_1 | ref | loginemailindex | loginemailindex | 302 | const | 1 | Using index condition | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-----------------------+ 1 row in set
prossible_keys:能在该表中使用哪些索引有助于查询
key:实际使用的索引
key_len：索引的长度，在不损失精确性的情况 下,长度越短越好
ref：索引的哪一列被使用了
rows:返回的结果的行数
Extra:其他说明
