我要评论一、准备环境
1、新增用户表，方便后续测试使用
create table `t_login_user` ( `id` int (11) not null auto_increment, `dateint` int (11) not null comment '年月份', `user_id` varchar (50) not null comment '用户id', `login_name` varchar (50) default null comment '登录名', `password` varchar (255) default null comment '密码', `nick_name` varchar (255) default null comment '用户名称', `fail_count` int (11) not null default '0' comment '当前连续失败次数', `max_fail_count` int (11) not null default '6' comment '允许的连续最大失败次数', `is_locked` char (1) not null default 'n' comment '是否被锁定', primary key (`id`), unique key `unique_user_id` (`user_id`), key `idx_login_name` (`login_name`), key `dateint_login_name` (`dateint`, `login_name`) ) engine = innodb auto_increment = 19 default charset = utf8 comment = '登录用户';
2、数据表索引结构
二、explain执行计划中的列解释
1、id列
id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。
id越大执行优先级越高，id相同则从上往下执行，id为null最后执行。
explain select * from t_login_user where id = ( select id from t_login_user where user_id = ( select user_id from t_login_user where nick_name = '张三') );
案例分析：
如上有3个select查询那么explain结果中有3个id，sql得执行顺序是先执行id为3的select（nick_name为张三作为条件查到user_id）,在执行id为2的select（拿到上一步user_id作为条件查到id）,最后在执行id为1的select (哪到上一步id作为条件查到数据)。
2、select type列
select type表示对应查询的类型，是简单还是复杂的查询
select type的值有：
- 1、simple：简单查询。查询不包含子查询和union 2、primary：复杂查询中最外层的select
- 3、subquery：包含在select中的子查询（不在from子句中）
- 4、derived：包含在from子句中的子查询。mysql会将结果存放在一个临时表中，也称为派生表
- 5、union：在union关键字随后的selelct
案例演示：
1、simple：简单查询
2、primary：复杂查询中最外层的select 3、subquery：包含在select中的子查询（不在from子句中）
4、derived：包含在from子句中的子查询。mysql会将结果存放在一个临时表中，也称为派生表
5、union：在union关键字随后的selelct
3、table列
这一列表示explain的一行正在访问哪个表。当from子句中有子查询时，table列是格式，表示当前查询依赖id=n的查询，于是先执行id=n的查询。
当有union时，union result的table列的值为<union 1,2>，1和2表示参与union的select行id
4、partitions
partitions查询涉及到的分区
5、type列
这一列表示关联类型或访问类型，即mysql决定如何查找表中的行，查找数据行对应的大概范围。
依次从最优到最差的分别为：system>const>eq_ref>ref>range>index>all一般来说，得保证查询达到range级别，最好达到ref。
1、null：mysql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需在执行时访问表。
2、system
访问类型最高的,属于const类型的特例,表只有一条记录行(系统表)
3、const
表示通过索引一次就能找到, const 用于比较primary或者unique(值是唯一的)。
因为只匹配一条数据,所以很快。
如果将主键置于where 子句中,mysql就能将该查询转为一个常量
4、eq_ref：eq_ref用于联合表的查询。
primay key或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录
如下面sql，对于前表t_login_user表中的每一行（row），对应后dept 表只有一行被扫描
explain select a.id from t_login_user a left join dept b using(id) -- using(id)等价于是 on a.id = b.id
5、ref：相比eq_ref，不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
简单select查询，name是普通索引（非主键索引或唯一索引）
explain select id from t_login_user where login_name= "张三"
6、range：范围扫描通常出现在in(), between,>,<,>=等操作中。
使用一个索引来检索给定范围的行。
explain select id from t_login_user where id > 1
7、index：扫描全表索引，通常比all快一些
8、all：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。
这种情况下需要增加索引来进行优化。
6、possible_keys列
mysql 能在该表中可能使用的索引，显示的是索引的名称，多个索引用逗号隔开。
在explain时可能会出现possible_keys有索引key值，而key显示为null的情况，这种情况是因为表中的数据不多，mysql认为索引对此查询帮助不大，选择了全表扫描。
如果该列为null，则没有相关的索引
7、key列
mysql决定实际用到的索引，显示的是索引的名称，多个索引用逗号隔开，如果没有，则为null
8、key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。
ken_len计算规则如下：
字符串
- char(n)：n字节长度
- varchar(n)：n字节存储字符串长度，如果是utf-8， 则长度是3n+2
数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
时间类型
- date：3字节
- timestamp：4字节
- datetime：8字节
如果字段允许为null，需要1字节记录是否为null
索引最大长度是768字节，当字符串过长时，mysql会做一个类似做前缀索引的处理，将前半部分的字符串提取出来做索引。
9、ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有： const(常量)，字段名、表名等。
一般是查询条件或关联条件中等号右边的值，如果是常量那么ref列是const，非常量的话ref列就是字段名。
条件等号右边是常量 ref列就显示const，如下
条件等号右边是非常量 ref列就显示字段名或者表名
10、row列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集的行数。
11、filtered
filtered表示返回结果的行数占需读取行数的百分比，filtered列的值依赖于统计信息。
12、extra列
这一列是额外信息。
- using index：使用覆盖索引（查询的字段是索引字段）
- using where：使用where语句来处理结果，查询的列未被索引覆盖
- using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般要进行优化，首先要想到是索引优化。
- using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
- select tables optimized away：使用某些聚合函数（比如：max、min）来访问存在索引的某个字段
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论