优化sql步骤

1.sql执行效率

mysql客户端连接成功后,通过show [session | global] status命令可以查询服务器状态信息

• session:当前连接(默认)
• global:自数据库上次启动至今

如果我们要查询这个数据库中sql语句的执行状态,可以使用以下sql

-- 查询此次连接
show status like 'Com_______';

-- 查询总共
show global status like 'Com_______';

只针对innodb的表查询

show global status like 'Innodb_rows_%';

通过以上两个sql,我们可以知道哪些语句一直被用到,是查询语句呢还是增删改语句?

1.2 定位低效率执行sql

• 慢查询日志:
  按我们设置的标准,当sql执行时间超过一个值后,就会把执行信息记录到慢查询日志中
• show processlist:
  慢查询日志是在查询结束后才记录,而show processlist则是查看实时的sql语句执行情况

看视频了解

1.3 explain分析执行计划

通过以上步骤查询到效率低的sql语句后,可以用个explain或者desc命令获取mysql如何执行select语句,包括在select语句执行过程中表如何连接和连接的顺序

查询sql语句的执行顺序

EXPLAIN select * from tb_item where id = 1;

环境准备:

create table t_role (
	id varchar(32) not null,
	role_name varchar(255) default null,
	role_code varchar(255) default null,
	description varchar(255) default null,
	role_name varchar(255) default null,
	primary key(id),
	unique key unique_role_name (role_name)
) engine = innodb default charset = utf8;

create table t_user (
	id varchar(32) not null,
	username varchar(45) default null,
	password varchar(96) default null,
	name varchar(45) default null,
	primary key(id),
	unique key unique_user_username (username)
) engine = innodb default charset = utf8;

create table user_role (
	id int(11) not null auto_increment,
	user_id varchar(32) default null,
	role_id varchar(32) default null,
	primary key(id),
	key fk_ur_user_id (user_id),
	key fk_ur_role_id (role_id),
	constraint fk_ur_role_id foreign key(role_id) references t_role(id) on delete no action on update no action,
	constraint fk_ur_user_id foreign key(user_id) references t_user(id) on delete no action on update no action
) engine = innodb default charset = utf8;

insert into t_user values ('1', 'super', '123456', '超级管理员');
insert into t_user values ('2', 'admin', '123456', '系统管理员');
insert into t_user values ('3', 'itcast', '123456', 'test02');
insert into t_user values ('4', 'stu1', '123456', '学生1');
insert into t_user values ('5', 'stu2', '123456', '学生2');
insert into t_user values ('6', 't1', '123456', '老师1');

insert into t_role values ('5', '学生', 'student', '学生');
insert into t_role values ('6', '老师', 'teacher', '老师');
insert into t_role values ('7', '教学管理员', 'teachermanager', '教学管理员');
insert into t_role values ('8', '管理员', 'admin', '管理员');
insert into t_role values ('9', '超级管理员', 'super', '超级管理员');

insert into user_role values (null, '1', '5');
insert into user_role values (null, '1', '6');
insert into user_role values (null, '2', '7');
insert into user_role values (null, '3', '8');
insert into user_role values (null, '4', '7');
insert into user_role values (null, '5', '9');

(1)id
id代表一个序号,指的是操作表的顺序,如果是单表操作,这个id就没有什么用处,而如果是多表操作,通过id可以看到表的执行顺序

id的情况有以下三种:

• id相同,表示加载顺序是从上而下的
  
• id越大,优先级越高,越先被执行
  
• id有相同,也有不同,此时id相同的可以认为是一组,从上往下执行,在所有的组中,依旧先执行id值大的

(2)select_type
表示select的类型,常见的类型如下

• simple: 简单的select查询,查询中不包含子查询和union操作
• primary:在含有子查询的语句中,最外层的查询为该标记
• subquery:子查询
• derived:将子查询的结果放到临时表中
• union:联合查询
• union result:

从上到下,效率越来越低

simple

primary和subquery
在select或where中包含的子查询为subquery,而外层的查询为primary

derived
当子查询在from中

(3)table
展示这一行的数据来源于哪一张表

(4)type (关键)
type表示访问类型,是比较重要的一个指标

• null:mysql不访问任何表、索引,直接返回结果

• system:表中只有一行记录(一般不会出现)

• const:表示通过索引一次就找到了,而且只返回一条记录(一般是查询主键或者唯一索引)
  

• eq_ref:多表关联查询时,使用了主键,且查询出的记录只有一条

• ref:对非唯一索引进行索引,查询的记录有多行

• range:范围查询

• index:遍历了整个索引树
  id字段我们建立了索引,但是此处我们是要获取所有的id,所以会遍历整个索引树
  

• all:遍历全表进行匹配

效率:system > const > eq_ref > ref > range > index > all
我们在优化时一般只要达到ref或range级别即可

(5)possible_keys
可能用到的索引

(6)key
实际用到的索引

如果此处为null,表示没有走索引

(7)key_len
key的长度(索引的长度)
越短越好

(8)rows
扫描的行

(9)extra
其他的额外执行计划信息,在此展示

• using filesort:使用文件排序(数据文件)
  对非索引字段进行排序时
  
  优化方式:对要排序的列设置索引

• using temporary:对临时表
  

• using index:

前面两个如果出现了,我们就要开始考虑优化了

1.4 show profile分析sql

通过have_profiling参数,能够查看mysql是否支持profile


如果结果为0时,表示未开启,可以用个set语句来开启

set profiling = 1; // 开启profiling开关

通过show profiles来查看每个语句的耗时