1、概念
-
在应用开发的初期，由于数据量较小，开发人员更重视功能上的实现，随着应用系统上线后，数据量急剧增长，很多性能问题逐渐显现，对使用的影响也越来越大，此时这些问题语句就称为整个系统的性能瓶颈，此时必须对其进行优化
-
常用的优化方式
– 从设计上
– 从查询上
– 从索引上
– 从存储上
2、查看sql执行频率
-
mysql客户端连接成功后，通过show[session|global] status命令可以查看服务器状态信息，通过状态信息可以查看当前数据库的主要操作类型
show session status like 'Com_______'; --查看当前会话统计结果show global status like 'Com_______'； --查看自数据库上次启动至今的统计结果show status like 'innoDB_rows_%'; --查看针对innoDB引擎的统计结果参数 含义 Com_select 执行select操作的次数，一次查询只累加1 Com_insert 执行insert操作的次数，批量插入的操作，只累加1次 Com_update 执行update的次数 Com_delete 执行delete操作的次数 innodb_rows_read select查询返回的行数 innodb_rows_inserted insert操作插入的行数 innodb_rows_update update操作更新的行数 innodb_rows_deleted deleted操作删除的行数 Connections 试图连接mysql服务器的次数 uptime 服务器工作时间 slow_queries 慢查询的次数
3、定位低效率sql语句
有两种方式可以定位执行效率较低的SQL语句
-
慢查询日志
show variables like '%slow_query_log%'; -- 查看慢查询日志配置信息set global slow_query_log = 1; -- 开启慢查询日志show variables like 'long_query_time%'; -- 查看慢查询日志的最低阈值时间set global long_query_time = 4; --修改最低阈值时间 -
show processlist：此命令查看当前mysql正在在进行的线程，包括线程状态、是否锁表等，可以实时地查看sql执行情况，同时对一些锁表操作进行优化
show processlist;
其中： -
id：用户登录mysql时系统分配的connection_id，可以使用函数connection_id()查看；
-
user：显示当前用户，如果不是root，这个命令就只显示权限范围内的sql语句；
-
host：显示本语句是从哪个ip的哪个端口上发来的，可以用来跟踪出现问题语句的用户；
-
db：显示这个进程目前连接的是哪个数据库；
-
command：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等；
-
time：显示这个状态持续的时间，单位秒；
-
state：显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。以一个sql查询语句为例，可能需要经过copingy to tmp table、sorting result、sending data等状态才可以完成
-
info：显示这个sql语句，是判断问题语句的一个重要依据
4、explain分析执行计划
查询到效率低的sql语句后，可以通过explain命令获取mysql是如何执行select语句的信息，包括在select语句执行过程中表时如何连接的、已经连接的顺序。
–准备示例数据
create database demo_explain;
use demo_explain;create table user(user_id int primary key auto_increment,user_name varchar(8)
)charset = utf8;
insert into user values(null, '小乔'),(null, '张非'),(null, '曹操');create table role(role_id int primary key auto_increment,role_name varchar(8)
)charset = utf8;
insert into role values(null, '女神'),(null, '屌丝'),(null, '老板');create table user_role(uid int,rid int
)charset = utf8;
alter table user_role add foreign key(uid) references user(user_id);
alter table user_role add foreign key(rid) references role(role_id);
insert into user_role values(1,1),(1,2),(2,2),(3,3);create table privilege(p_id int primary key auto_increment,P_name varchar(8)
)charset = utf8;
insert into privilege(null, '玩跑车'),(null, '挖矿'),(null, '写代码');explain select * from user where user_id = 1;
字段解释如下表：
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，是一组数字，表示的是执行select子句或者操作表的顺序 |
| select_type | select的类型，常有simple（简单，即不适用表连接或子查询），primary（主查询，即外层查询），union（union中第二个或后面的查询语句，subquery（子查询中的第一个select）等 |
| table | 输出结果集的表，即本次查询用到的表 |
| type | 表示表的连接类型，性能由好至差的连接类型顺序为：system–>const–>eq_ref–>ref–>ref_or_null–>index_merge–>index_subquery–>range–>index–>all |
| possible_keys | 查询时可能使用的索引 |
| key | 实际使用的索引 |
| key_len | 索引字段的长度 |
| rows | 扫描行的数量 |
| extra | 执行情况的说明和描述 |
-
explain之id
1、id相同加载表的顺序是从上到下；
explain select * from user as u, user_role as ur, role as r where u.user_id = ur.u_id and ur.r_id = role_id;
2、id不相同时，id值越大优先级越高，越先被执行；
explain select * from role where role_id = (select r_id from user_role where u_id = (select user_id from user where user_name = '张非'));
3、id同时存在相同和不同时，相同的id可以认为是一组，从上往下顺序执行；在不同组中，id值越大，该组优先级越高，越先执行。
explain select * from role as r, (select * from user_role as ur where ur,u_id = (select user_id from user where user_name = '张非')) t1 where r.r_id = t1.r_id
-
explain之select_type：表示select的类型，常见的取值如下表
select_type 含义 simply 简单的select语句，语句中不包含子查询或union primary 查询中如包含认可复杂子句，主查询（最外层查询）标记为该标识 subquery 在select或where列表中包含子查询 derived 在from列表中包含的子查询，被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表中 union 如第二个select出现在union之后，则标记为union;如union包含在from子句的子查询中，外层select将被标记为：derived union result 从union表获得结果的select -
type显示的是访问类型，是较重要的一个指标，可取值为：
type 含义 null mysql不访问任何表，索引，直接返回结果 system 系统表，少量数据，通常不需要进行磁盘i/o；5,7以上的版本变更成了all，即使只有一条记录 const 命中主键（primary key）或唯一（unique）索引，被连接的部分是一个常量（const）值 eq_ref 对于前表的每一行，后表只有一行扫描：1、jion查询；2、命中主键（primary key）或非空唯一（unique not null）索引；3、等值连接 ref 非唯一索引扫描，返回匹配某个单独值的所有行，对于前表的每一行(row)，后表可能有多于一行的数据被扫描。 range 只检索给定返回的行，使用一个索引来选择行，where之后出现between，<，>，in等操作 index 需要扫描索引上的全部数据 all 全表扫描，此时id上无索引 -
explain之extra：其他的额外的信息在该列显示
extra 含义 using filesort Mysql会对数据使用一个外部索引排序，而不是按照表内的所有顺序进行读取，称为‘文件排序’，效率低 using temporary 需要建立临时表（temporary）来暂存中间结果，常见与order by 和group by，效率低 using index sql需要返回的所有列数据均在一颗索引树上，避免访问表的数据行，效率较高
