目录
一.概念
二.查看SQL执行频率
三.定位低效率执行SQL
定位低效率执行SQL—慢查询日志
操作
定位低效率执行SQL—show processlist
四.explain分析执行计划
字段说明
explain中的id
explain中的select_type
explain中的type
explain中的table
explain中的rows
explain中的key
![](https://img-blog.csdnimg.cn/9f4ddb7313344c07a679622b58f27a88.png)
一.概念
在应用的的开发过程中,由于初期数据量小,开发人员写SQL语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化.
MySQL的优化方式有很多,大致我们可以从以下几点来优化MySQL:
- 从设计上优化
- 从查询上优化
- 从索引上优化
- 从存储上优化
二.查看SQL执行频率
MySQL客户端连接成功后,通过show [session/global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。
-- 下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______';-- 查看当前会话统计结果
show global status like 'Com_______';-- 查看自数据库上次启动至今提及结果
show status like 'Innodb_row_%';-- 查看针对Innodb引擎的统计结果
![](https://img-blog.csdnimg.cn/935a9abae88243d6ad9d95710139db86.png)
参数 |
含义 |
Com_select |
执行select操作的次数,一次查询只累加1。 |
Com_insert |
执行INSERT操作的次数,对于批量插入的INSERT 操作,只累加一次。 |
Com_update |
执行UPDATE操作的次数。 |
com_delete |
执行DELETE操作的次数。 |
Innodb_rows_read |
select查询返回的行数。 |
Innodb_rows_inserted |
执行INSERT操作插入的行数。 |
lnnodb_rows_updated |
执行UPDATE操作更新的行数。 |
lnnodb_rows_deleted |
执行DELETE操作删除的行数。 |
Connections |
试图连接MySQL服务器的次数。 |
Uptime |
服务器工作时间。 |
Slow_queries |
慢查询的次数。 |
三.定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的sQL语句。
- 慢查询日志:通过慢查询日志定位那些执行效率较低的SQL语句。
- show processlist:该命令查看当前MysQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看sQL的执行情况,同时对一些锁表操作进行优化。
定位低效率执行SQL—慢查询日志
操作
-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log=1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like 'long_query_time%';
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time=4;
![](https://img-blog.csdnimg.cn/68b7813ad9f5462182f52d9102ee7e61.png)
![](https://img-blog.csdnimg.cn/0f1f40ce44ed4311bfd8d9da1960dfb4.png)
定位低效率执行SQL—show processlist
操作
show processlist;
![](https://img-blog.csdnimg.cn/d4670cefd0e7490c9d0c40f3bde18029.png)
- id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
- user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
- host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
- db列,显示这个进程目前连接的是哪个数据库
- command列,显示当前连接的执行的命令,一般取值为休眠((sleep),查询(query),连接(connect)等
- time列,显示这个状态持续的时间,单位是秒
- state列,显示使用当前连接的sq语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sqi语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
- info列,显示这个sql语句,是判断问题语句的一个重要依据
四.explain分析执行计划
通过以上步骤查询到效率低的SQL语句后,可以通过EXPLAIN命令获取MysQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。
![](https://img-blog.csdnimg.cn/7a7d9e5326c74b01bc3769f2aeaae3d0.png)
链接:https://pan.baidu.com/s/1uaxnHLdWm-f5iY4zNc-n9g
提取码:1234
![](https://img-blog.csdnimg.cn/d1f346f066394620b57833a34dc195a4.png)
use test_optimize;
explain select * from user where uid=1;
explain select * from user where uname='张飞';
![](https://img-blog.csdnimg.cn/2a58d311bba9458ca1ab18a065c1dde0.png)
![](https://img-blog.csdnimg.cn/5498e193b1b84e29bac918daec296fc5.png)
字段说明
字段 |
含义 |
id |
select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。 |
select_type |
表示SELECT的类型,常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION (UNION中的第二个或者后面的查询语句) 、SUBQUERY(子查询中的第一个SELECT)等 |
table |
输出结果集的表 |
type |
表示表的连接类型,性能由好到差的连接类型为( system .--> const ..…> eq_ref …….ref .…. ref_or_nul.---> index_merge ---> index_subquery -----> range ---- index ---…> all ) |
possible_keys |
表示查询时,可能使用的索引 |
key |
表示实际使用的索引 |
key_len |
索引字段的长度 |
rows |
扫描行的数量 |
extra |
执行情况的说明和描述 |
explain中的id
id字段是select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。id情况有三种:
1、id相同表示加载表的顺序是从上到下
2、id不同 id值越大,优先级越高,越先被执行
3、id有相同也有不同,同时存在,id相同的可以认为是一组,从上往下顺序执行;在所有的组中id值越大,优先级越高
-- 1、id相同表示加载表的顺序是从上到下
explain select * from user u,user_role ur ,`role` r where u.uid =ur.uid and ur.rid =r.rid ;
-- 2、id不同 id值越大,优先级越高,越先被执行
explain select * from role where rid=(select rid from user_role ur where uid=(select uid from user where uname='张飞'));
-- 3、id有相同也有不同,同时存在,id相同的可以认为是一组,从上往下顺序执行;在所有的组中id值越大,优先级越高
explain select * from role r,(select * from user_role ur where ur.uid=(select uid from user where uname='张飞')) t where r.rid =t.rid;
![](https://img-blog.csdnimg.cn/d106b1ca0520418e85e437461b8a62df.png)
![](https://img-blog.csdnimg.cn/2cc8bc131b9146389a2def7fc706f0a8.png)
![](https://img-blog.csdnimg.cn/63efdd8a7fb54e47942a646d34763866.png)
explain中的select_type
表示SELECT的类型,常见的取值,如下表所示:
select_type |
含义 |
SIMPLE |
简单的select查询,查询中不包含子查询或者UNION |
PRIMARY |
查询中若包含任何复杂的子查询,最外层查询标记为该标识 |
SUBQUERY |
在SELECT或 WHERE列表中包含了子查询 |
DERIVED |
在FROM列表中包含的子查询,被标记为DERIVED(衍生)MYSQL会递归执行这些子查询,把结果放在临时表中 |
UNION |
若第二个SELECT出现在UNION之后,则标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED |
UNION RESULT |
从UNION表获取结果的SELECT |
![](https://img-blog.csdnimg.cn/6965758ec7c64dd6946863ee062dc3a6.png)
连表查询也是simple
![](https://img-blog.csdnimg.cn/f238c13fb15c4f82a7f64a9f86dc7388.png)
-- derived :在from中包含子查询,被标记为衍生表
explain select * from (select * from user limit 2) t;
![](https://img-blog.csdnimg.cn/be1948df5f8243b2858b27d92cd84953.png)
![](https://img-blog.csdnimg.cn/be781da9e324441dbed47488a9951b09.png)
explain中的type
type显示的是访问类型,是较为重要的一个指标,可取值为:
type |
含义 |
NULL |
MySQL不访问任何表,索引,直接返回结果 |
system |
系统表,少量数据,往往不需要进行磁盘lO;如果是5.7及以上版本的话就不是system了,而是all,即使只有一条记录 |
const |
命中主键(primary key)或者唯一(unique)索引;被连接的部分是一个常量(const)值; |
eq_ref |
对于前表的每一行,后表只有一行被扫描。(1) join查询;(2)命中主键(primary key)或者非空唯一(unique not null)索引;(3)等值连接; |
ref |
非唯一性索引扫描,返回匹配某个单独值的所有行。对于前表的每一行(row),后表可能有多于一行的数据被扫描。 |
range |
只检索给定返回的行,使用一个索引来选择行。where之后出现 between ,< , > , in等操作。 |
index |
需要扫描索引上的全部数据。 |
all |
全表扫描,此时id上无索引 |
结果值从最好到最坏以此是: system > const > eq_ref > ref > range > index >ALL
-- explain
-- all
explain select * from user;
-- null 不访问任何表,任何索引,直接返回结果
explain select now();
-- system 查询系统表,表示直接从内存读取数据,不会从磁盘读取,但是5.7及以上版本不再显示system,直接显示all
explain select * from mysql.tables_priv tp ;
-- const
explain select * from user where uid=2;
explain select * from user where uname='张飞';-- 在没有创建唯一索引之前type为all,创建唯一索引之后为const,创建普通索引为ref
create unique index index_uname on user (uname);-- 创建唯一索引
drop index index_uname on user;-- 删除索引
create index index_uname on user (uname);-- 创建普通索引
系统表指系统自带的表
![](https://img-blog.csdnimg.cn/dc53399bd560485fbc5a3e2d8d74092c.png)
![](https://img-blog.csdnimg.cn/a5e1dc6e9aa5443b9b07f8650b5a4166.png)
![](https://img-blog.csdnimg.cn/502a1c6f21914b57a885dfe1579816b0.png)
![](https://img-blog.csdnimg.cn/cdaf392eb1db4745bd4f6d3a11259c91.png)
![](https://img-blog.csdnimg.cn/755c80cbf47f48eb8f07d92103c85a0e.png)
注:
eq_ref指左表有主键,而且左表的每一行和右表的每一行刚好匹配
![](https://img-blog.csdnimg.cn/f4a0f547f8604147b596ec923bf79810.png)
![](https://img-blog.csdnimg.cn/2410cf2d5314499587057fe63933b562.png)
explain中的table
显示这—步所访问数据库中表名称有时不是真实的表名字,可能是简称,
explain中的rows
扫描行的数量
explain中的key
- possible_keys :显示可能应用在这张表的索引,一个或多个。
- key :实际使用的索引,如果为NULL,则没有使用索引。
- key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。
![](https://img-blog.csdnimg.cn/d815c8246b0f4791b46aeb4235d1736b.png)
![](https://img-blog.csdnimg.cn/8ac46fa44c264050a503c2f4d71859c9.png)
列类型 |
key_len |
备注 |
id int |
key_len = 4+1= 5 |
允许NULL,加1-byte |
id int not null |
key_len = 4 |
不允许NULL |
user char(30) utf8 |
key_len =30*3+1 |
允许NULL |
user varchar(30) notnull utf8 |
key_len =30*3+2 |
动态列类型,加2-bytes |
user varchar(30) utf8 |
key_len =30*3+2+1 |
动态列类型,加2-bytes ;允许NULL,再加1-byte |
detail text(10) utf8 |
key_len =30*3+2+1 |
TEXT列截取部分,被视为动态列类型,加2-bytes ;且允许NULL |
其他的额外的执行计划信息,在该列展示。
extra |
含义 |
using filesort |
说明mysq|会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,称为“文件排序" ,效率低。 |
using temporary |
需要建立临时表(temporary table)来暂存中间结果,常见于order by和group by;效率低 |
using index |
SQL所需要返回的所有列数据均在一棵索引树上,避免访问表的数据行,效率不错。 |
using where |
在查找使用索引的情况下,需要回表去查询所需的数据 |
using index condition |
查找使用了索引,但是需要回表查询数据 |
using index;using where |
查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据 |
![](https://img-blog.csdnimg.cn/34e6eff945bc466689550579cfdc1719.png)
![](https://img-blog.csdnimg.cn/126601723ea4486da634198130053793.png)
![](https://img-blog.csdnimg.cn/172b64d21bb44b9a9cdba6e4d545fb85.png)
![](https://img-blog.csdnimg.cn/4ef98fd5e8e84392aa29784865c82492.png)