mysql的优化

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需要返回的所有列数据均在一颗索引树上，避免访问表的数据行，效率较高