当前位置: 首页 > news >正文

MySQL的优化

目录

一.概念

二.查看SQL执行频率

三.定位低效率执行SQL

定位低效率执行SQL—慢查询日志

操作

定位低效率执行SQL—show processlist

四.explain分析执行计划

字段说明

explain中的id

 explain中的select_type

 explain中的type

explain中的table

explain中的rows

explain中的key

explain中的extra


 

一.概念

在应用的的开发过程中,由于初期数据量小,开发人员写SQL语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化.

MySQL的优化方式有很多,大致我们可以从以下几点来优化MySQL:

  1. 从设计上优化
  2. 从查询上优化
  3. 从索引上优化
  4. 从存储上优化

二.查看SQL执行频率

MySQL客户端连接成功后,通过show [session/global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。

-- 下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______';-- 查看当前会话统计结果
show global status like 'Com_______';-- 查看自数据库上次启动至今提及结果show status like 'Innodb_row_%';-- 查看针对Innodb引擎的统计结果

 

参数含义
Com_select执行select操作的次数,一次查询只累加1。
Com_insert执行INSERT操作的次数,对于批量插入的INSERT 操作,只累加一次。
Com_update执行UPDATE操作的次数。
com_delete执行DELETE操作的次数。
Innodb_rows_readselect查询返回的行数。
Innodb_rows_inserted执行INSERT操作插入的行数。
lnnodb_rows_updated执行UPDATE操作更新的行数。
lnnodb_rows_deleted执行DELETE操作删除的行数。
Connections试图连接MySQL服务器的次数。
Uptime服务器工作时间。
Slow_queries慢查询的次数。

三.定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的sQL语句。

  1. 慢查询日志:通过慢查询日志定位那些执行效率较低的SQL语句。
  2. 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;

 

 

定位低效率执行SQL—show processlist

操作

show processlist;

  1.  id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
  2. user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
  3. host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
  4. db列,显示这个进程目前连接的是哪个数据库
  5. command列,显示当前连接的执行的命令,一般取值为休眠((sleep),查询(query),连接(connect)等
  6. time列,显示这个状态持续的时间,单位是秒
  7. state列,显示使用当前连接的sq语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sqi语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
  8. info列,显示这个sql语句,是判断问题语句的一个重要依据

四.explain分析执行计划

通过以上步骤查询到效率低的SQL语句后,可以通过EXPLAIN命令获取MysQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。

链接:https://pan.baidu.com/s/1uaxnHLdWm-f5iY4zNc-n9g 
提取码:1234

 

use test_optimize;explain select * from user where uid=1;explain select * from user where uname='张飞';

 

字段说明

字段含义
idselect查询的序列号,是一组数字,表示的是查询中执行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;

 

 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

 连表查询也是simple

 

-- derived :在from中包含子查询,被标记为衍生表
explain select * from (select * from user limit 2) t;

 

 

 explain中的type

type显示的是访问类型,是较为重要的一个指标,可取值为:

type

含义

NULLMySQL不访问任何表,索引,直接返回结果
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,创建普通索引为refcreate unique index index_uname on user (uname);-- 创建唯一索引
drop index index_uname on user;-- 删除索引create  index index_uname on user (uname);-- 创建普通索引

系统表指系统自带的表

 

 

 

 注:

eq_ref指左表有主键,而且左表的每一行和右表的每一行刚好匹配

 

explain中的table

显示这—步所访问数据库中表名称有时不是真实的表名字,可能是简称,

explain中的rows

扫描行的数量

explain中的key

  1. possible_keys :显示可能应用在这张表的索引,一个或多个。
  2. key :实际使用的索引,如果为NULL,则没有使用索引。
  3. key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。

 

列类型key_len

备注

id intkey_len = 4+1= 5允许NULL,加1-byte
id int not nullkey_len = 4不允许NULL
user char(30) utf8key_len =30*3+1允许NULL
user varchar(30) notnull utf8key_len =30*3+2动态列类型,加2-bytes
user varchar(30) utf8key_len =30*3+2+1动态列类型,加2-bytes ;允许NULL,再加1-byte
detail text(10) utf8key_len =30*3+2+1TEXT列截取部分,被视为动态列类型,加2-bytes ;且允许NULL

explain中的extra

其他的额外的执行计划信息,在该列展示。

extra

含义

using filesort说明mysq|会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,称为“文件排序" ,效率低。
using  temporary需要建立临时表(temporary table)来暂存中间结果,常见于order by和group by;效率低
using  indexSQL所需要返回的所有列数据均在一棵索引树上,避免访问表的数据行,效率不错。

 

 

 

 

 

 

相关文章:

MySQL的优化

目录 一.概念 二.查看SQL执行频率 三.定位低效率执行SQL 定位低效率执行SQL—慢查询日志 操作 定位低效率执行SQL—show processlist 四.explain分析执行计划 字段说明 explain中的id explain中的select_type explain中的type explain中的table explain中的rows ex…...

实现qq群消息接收和发送功能

QQWebsocketClient是什么 实现qq群消息接收和发送功能&#xff0c;基于websocket技术和cqhttp服务开发 一、 效果截图 二、实现思路 使用cqhttp进行socket反向代理&#xff0c;获取qq聊天的所有消息 编写java客户端&#xff0c;连接至cqhttp服务器获取聊天消息 获取聊天消…...

压缩20M文件从30秒到1秒的优化过程

压缩20M文件从30秒到1秒的优化过程 有一个需求需要将前端传过来的10张照片&#xff0c;然后后端进行处理以后压缩成一个压缩包通过网络流传输出去。之前没有接触过用Java压缩文件的&#xff0c;所以就直接上网找了一个例子改了一下用了&#xff0c;改完以后也能使用&#xff0…...

如何选择合适的固态继电器?

如何选择合适的固态继电器&#xff1f; 在选择固态继电器&#xff08;SSR&#xff09;时&#xff0c;应根据实际应用条件和SSR性能参数&#xff0c;特别要考虑到使用中的过流和过压条件以及SSR的负载能力&#xff0c;这有助于实现固态继电器的长寿命和高可靠性。然后&#xff0…...

SAP 忘记SAP系统Client 000的所有账号密码

忘记SAP系统Client 000的所有账号密码。 Solution 在SAP系统DB中删除账号SAP*&#xff0c;SAP系统会自动创建SAP*这个账号&#xff0c;然后初始密码是“PASS”&#xff0c;这样就获得Client 000 SAP*账号。 Step by Step 以Oracle数据库为例&#xff1a; 1.以<SID>ADM账…...

Connext DDS可扩展类型Extensible Types指南

RTI Connext DDS 可扩展类型Extensible Types指南 可扩展类型Extensible TypesConnextDDSv6.1.1版本,包含了对OMG“DDS的可扩展和动态主题类型Extensible andDynamic Topic Types for DDS”规范1.3版的部分支持,该规范来自对象管理组OMG。这种支持,允许系统以更灵活的方式定义…...

Docker简单使用

文章目录1、安装配置2、服务启动3、Docker镜像下载4、Docker启动容器5、容器的常用命令6、Docker进入容器内部7、宿主机与容器交换文件8、查看日志官网地址&#xff1a;1、安装配置 sudo yum install -y yum-utils 设置镜像地址 sudo yum-config-manager \--add-repo \https:…...

A Time Series is Worth 64 Words(PatchTST模型)论文解读

摘要 我们提出了一种高效的基于Transformer设计的模型&#xff0c;用于多变量时间序列预测和自我监督表征学习&#xff08;self-supervised learning&#xff09;。它基于两个关键部分&#xff1a;1、将时间序列分隔成子序列级别的patches&#xff0c;作为Transformer的输入&a…...

微服务学习:SpringCloud+RabbitMQ+Docker+Redis+搜索+分布式

目录 一、高级篇 二、面试篇 实用篇 day05-Elasticsearch01 安装elasticsearch 1.部署单点es 2.部署kibana 一、高级篇 二、面试篇 实用篇 day05-Elasticsearch01 安装elasticsearch 1.部署单点es 1.1.创建网络 因为我们还需要部署kibana容器&#xff0c;因此需要…...

nginx平滑升级

1.平滑升级操作1.1 备份安装目录下的nginxcd /usr/local/nginx/sbin mv nginx nginx.bak1.2 复制objs目录下的nginx到当前sbin目录下cp /opt/software/nginx/nginx-1.20.2/objs/nginx /usr/local/nginx/sbin/1.3 发送信号user2给nginx老版本对应的进程kill -user2 more /usr/lo…...

高可用的“异地多活”架构设计

前言 后台服务可以划分为两类&#xff0c;有状态和无状态。高可用对于无状态的应用来说是比较简单的&#xff0c;无状态的应用&#xff0c;只需要通过 F5 或者任何代理的方式就可以很好的解决。后文描述的主要是针对有状态的服务进行分析。 服务端进行状态维护主要是通过磁盘…...

【面试题】Map和Set

1. Map和Object的区别 形式不同 // Object var obj {key1: hello,key2: 100,key3: {x: 100} } // Map var m new Map([[key1, hello],[key2, 100],[key3, {x: 100}] ])API不同 // Map的API m.set(name, 小明) // 新增 m.delete(key2) // 删除 m.has(key3) // …...

Spring之事务底层源码解析

Spring之事务底层源码解析 1、EnableTransactionManagement工作原理 开启 Spring 事务本质上就是增加了一个 Advisor&#xff0c;当我们使用 EnableTransactionManagement 注解来开启 Spring 事务时&#xff0c;该注解代理的功能就是向 Spring 容器中添加了两个 Bean&#xf…...

【华为OD机试真题 Python】创建二叉树

前言:本专栏将持续更新华为OD机试题目,并进行详细的分析与解答,包含完整的代码实现,希望可以帮助到正在努力的你。关于OD机试流程、面经、面试指导等,如有任何疑问,欢迎联系我,wechat:steven_moda;email:nansun0903@163.com;备注:CSDN。 题目描述 请按下列描达构建…...

RuoYi-Vue-Plus搭建(若依)

项目简介 1.RuoYi-Vue-Plus 是重写 RuoYi-Vue 针对 分布式集群 场景全方位升级(不兼容原框架)2.环境安装参考&#xff1a;https://blog.csdn.net/tongxin_tongmeng/article/details/128167926 JDK 11、MySQL 8、Redis 6.X、Maven 3.8.X、Nodejs > 12、Npm 8.X3.IDEA环境配置…...

uboot和linux内核移植流程简述

一、移植uboot流程 1、从半导体芯片厂下载对应的demo&#xff0c;然后编译测试demo版的uboot 开发板基本都是参考半导体厂商的 dmeo 板&#xff0c;而半导体厂商会在他们自己的开发板上移植好 uboot、linux kernel 和 rootfs 等&#xff0c;最终制作好 BSP包提供给用户。我们可…...

【CS224W】(task2)传统图机器学习和特征工程

note 和CS224W课程对应&#xff0c;将图的基本表示写在task1笔记中了&#xff1b;传统图特征工程&#xff1a;将节点、边、图转为d维emb&#xff0c;将emb送入ML模型训练Traditional ML Pipeline Hand-crafted feature ML model Hand-crafted features for graph data Node-l…...

【算法基础】并查集⭐⭐⭐⭐⭐【思路巧,代码短,面试常考】

并查集,在一些有N个元素的集合应用问题中,我们通常是在开始时让每个元素构成一个单元素的集合,然后按一定顺序将属于同一组的元素所在的集合合并,其间要反复查找一个元素在哪个集合中。这一类问题近几年来反复出现在信息学的国际国内赛题中。其特点是看似并不复杂,但数据量…...

人工智能轨道交通行业周刊-第34期(2023.2.13-2.19)

本期关键词&#xff1a;智慧地铁、枕簧检测选配机器人、智慧工地、接触网检修、工业缺陷检测 1 整理涉及公众号名单 1.1 行业类 RT轨道交通人民铁道世界轨道交通资讯网铁路信号技术交流北京铁路轨道交通网上榜铁路视点ITS World轨道交通联盟VSTR铁路与城市轨道交通RailMetro…...

Retrofit 网络框架源码解析(二)

目录一、Okhttp请求二、Retrofit 请求retrofit是如何封装请求的三、Retrofit的构建过程四、Retrofit构建IxxxService对象的过程&#xff08;Retrofit.create()&#xff09;4.1 动态代理4.2 ServiceMethod4.3 okHttpCall4.4 callAdapter五、Retrofit网络请求操作一、Okhttp请求 …...

SQL Server 2008新特性——更改跟踪

在大型的数据库应用中&#xff0c;经常会遇到部分数据的脱机和多个数据库的合并问题。比如现在有一个全省范围使用的应用程序&#xff0c;每个市都部署了单独的相同的应用程序服务器和数据库服务器&#xff0c;每个月需要将全省所有市的数据全部汇总起来用于出全省的报表&#…...

四六级真题长难句分析与应用

一、基本结构的长难句 基本结构的长难句主要考点&#xff1a;断开和简化 什么是长难句&#xff1f; 其实就是多件事连在了一块&#xff0c;这时候句子就变长、变难了 分析步骤&#xff1a; 第一件事就是要把长难句给断开&#xff0c;把多件事断开成一件一件的事情&#xff0…...

华为OD机试 - 玩牌高手(Python) | 机试题算法+思路 【2023】

最近更新的博客 华为OD机试 - 寻找路径 | 备考思路,刷题要点,答疑 【新解法】 华为OD机试 - 五键键盘 | 备考思路,刷题要点,答疑 【新解法】 华为OD机试 - IPv4 地址转换成整数 | 备考思路,刷题要点,答疑 【新解法】 华为OD机试 - 对称美学 | 备考思路,刷题要点,答疑 …...

【论文阅读】 Few-shot object detection via Feature Reweighting

Few-shot object detection的开山之作之一 ~~ 特征学习器使用来自具有足够样本的基本类的训练数据来 提取 可推广以检测新对象类的meta features。The reweighting module将新类别中的一些support examples转换为全局向量&#xff0c;该全局向量indicates meta features对于检…...

现代卷积神经网络经典架构图

卷积神经网络&#xff08;LeNet&#xff09; LeNet 的简化版深层卷积神经网络&#xff08;AlexNet&#xff09; 从LeNet&#xff08;左&#xff09;到AlexNet&#xff08;右&#xff09;改进&#xff1a; dropOut层 - 不改变期望但是改变方差ReLU层 - 减缓梯度消失MaxPooling数…...

有关eclipse的使用tips

一、alt/键 会产生单词提示&#xff0c;可以提高编程速度。例如不需要辛辛苦苦的打出&#xff1a;System.out.println();整句&#xff0c;只需要在eclipse中输入syso&#xff0c;然后按住ALT/就会出来System.out.println();在alt键/不管用的情况下&#xff0c;可使用以下方法来…...

Mybatis(4)之CRUD

首先是 增 &#xff0c;我们要在数据库中增加一个数据 先来看看我们之前的插入语句 <insert id"insertRole">insert into try(id,name,age) values(3,nuonuo,20)</insert> 请注意&#xff0c;我们这里的 insert 是固定的&#xff0c;但在实际的业务场…...

OSG三维渲染引擎编程学习之五十七:“第五章:OSG场景渲染” 之 “5.15 光照”

目录 第五章 OSG场景渲染 5.15 光照 5.15.1 osg::Light光 5.15.2 osg::LightSource光源 第五章 OSG场景渲染 OSG存在场景树和渲染树,“场景数”的构建在第三章“OSG场景组织”已详细阐明,本章开始深入探讨“渲染树”。 渲染树一棵以状态集(StateSet)和渲染叶(RenderLe…...

[教你传话,表白,写信]

第一步 关注飞鸽传话助手 第二部 点击链接进入 第三步 点击发送,输入内容 第四步 就可以收到了...

物联网在智慧农业中的应用

随看现代科技的不断发展&#xff0c;近年来我国农业的进步是显而易见的。从八九十年代农业生产以人力为主&#xff0c;到之后的机械渐渐代替人力&#xff0c;再到如今物联网技术在农业领域的应用&#xff0c;多种前沿技术应用于农业物联网&#xff0c;对智慧农业生产的各个环节…...