单条语句没有给出优化建议
sql
select * from temp_student where tempstu_code='学号278641' and tempstu_name_cn like '姓名35264%'
ddl
CREATE TABLE temp_student
(
tempstu_id
varchar(32) NOT NULL DEFAULT '' COMMENT '主键',
tempstu_grade_id
varchar(32) NOT NULL DEFAULT '' COMMENT '班级ID',
tempstu_code
varchar(32) NOT NULL DEFAULT '' COMMENT '学号',
tempstu_name_cn
varchar(16) NOT NULL DEFAULT '' COMMENT '姓名',
tempstu_sex
tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别',
tempstu_mobile
varchar(11) NOT NULL DEFAULT '' COMMENT '联系电话',
tempstu_qq
varchar(12) DEFAULT '' COMMENT 'QQ',
tempstu_email
varchar(64) NOT NULL DEFAULT '' COMMENT 'Email',
tempstu_interest
varchar(256) DEFAULT NULL COMMENT '兴趣爱好、特长',
tempstu_info
text NOT NULL COMMENT '个人简历',
tempstu_birth_time
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '出生时间',
tempstu_coming_time
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
tempstu_create_user
varchar(32) DEFAULT NULL COMMENT '创建人',
tempstu_create_time
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
PRIMARY KEY (tempstu_id
),
KEY idx_ctime
(tempstu_create_time
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='学生';
执行计划
[Note] 第1步: 对SQL解析优化之后得到的SQL:select *
AS *
from youren_test
.temp_student
where ((tempstu_code
= '学号278641') and (tempstu_name_cn
like '姓名35264%'))
2017-03-10 17:00:23 8770 [Note] 第2步:开始解析where中的条件:(tempstu_code
= '学号278641')
2017-03-10 17:00:23 8770 [Note] show index from temp_student
2017-03-10 17:00:23 8770 [Note] show table status like 'temp_student'
2017-03-10 17:00:23 8770 [Note] select count(*) from ( select tempstu_code
from temp_student
FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student
where (tempstu_code
= '学号278641')
2017-03-10 17:00:23 8770 [Note] 第3步:表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_code
= '学号278641')的选择度:0
2017-03-10 17:00:23 8770 [Note] 第4步:开始解析where中的条件:(tempstu_name_cn
like '姓名35264%')
2017-03-10 17:00:23 8770 [Note] show index from temp_student
2017-03-10 17:00:23 8770 [Note] show table status like 'temp_student'
2017-03-10 17:00:23 8770 [Note] select count(*) from ( select tempstu_name_cn
from temp_student
FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student
where (tempstu_name_cn
like '姓名35264%')
2017-03-10 17:00:23 8770 [Note] 第5步:表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_name_cn
like '姓名35264%')的选择度:0
2017-03-10 17:00:23 8770 [Note] 第6步:表temp_student 的SQL太逆天,没有优化建议
2017-03-10 17:00:23 8770 [Note] 第7步: SQLAdvisor结束!
[root@db1 sqladvisor]# ./sqladvisor -f sql.cnf -v 1
2017-03-10 17:00:43 9092 [Note] 第1步: 对SQL解析优化之后得到的SQL:select *
AS *
from youren_test
.temp_student
where ((tempstu_code
= '学号278641') and (tempstu_name_cn
like '姓名35264%'))
2017-03-10 17:00:43 9092 [Note] 第2步:开始解析where中的条件:(tempstu_code
= '学号278641')
2017-03-10 17:00:43 9092 [Note] show index from temp_student
2017-03-10 17:00:43 9092 [Note] show table status like 'temp_student'
2017-03-10 17:00:43 9092 [Note] select count(*) from ( select tempstu_code
from temp_student
FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student
where (tempstu_code
= '学号278641')
2017-03-10 17:00:43 9092 [Note] 第3步:表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_code
= '学号278641')的选择度:0
2017-03-10 17:00:43 9092 [Note] 第4步:开始解析where中的条件:(tempstu_name_cn
like '姓名35264%')
2017-03-10 17:00:43 9092 [Note] show index from temp_student
2017-03-10 17:00:43 9092 [Note] show table status like 'temp_student'
2017-03-10 17:00:43 9092 [Note] select count(*) from ( select tempstu_name_cn
from temp_student
FORCE INDEX( PRIMARY ) order by tempstu_id DESC limit 0) temp_student
where (tempstu_name_cn
like '姓名35264%')
2017-03-10 17:00:43 9092 [Note] 第5步:表temp_student的行数:0,limit行数:0,得到where条件中(tempstu_name_cn
like '姓名35264%')的选择度:0
2017-03-10 17:00:43 9092 [Note] 第6步:表temp_student 的SQL太逆天,没有优化建议
2017-03-10 17:00:43 9092 [Note] 第7步: SQLAdvisor结束!