MySQL数据库查询性能优化的4个技巧干货
作者:IT学习日记v 时间:2024-01-13 23:25:16
前言
MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧。
SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句。
SQL的执行频率
SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前,可以先确认表的更删查改的一个执行频率对比,如果是查询占主导地位,则可以一步排查。
MySQL支持客户端通过show [session|global] status命令对服务器状态进行查询。
查看执行频率方式:
show global status like ‘com_______’(7个下划线,表示后面会有7个字符)
慢查询日志
确认了SQL的执行频率,则需要通过慢查询日志进行进一步定位哪些SQL语句执行时间占用较长。
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认是10s)的所有SQL语句的日志。
默认情况下,慢查询日志是没有开启的,需要在MySQL的配置文件(linux下为:/etc/my.cnf)中配置如下指令:
查询服务端是否开启慢查询日志:show variables like 'slow_query_log';
在mysql的配置文件中添加如下配置启动:
slow_query_log=1;开启mysql慢日志查询开关
long_query_time=xx;设置慢日志时间,只要SQL执行时间查过该值,则视为慢查询,记录在慢日志中。
配置完成后重启mysql服务端
linux中mysql的慢日志文件在: /var/lib/mysql/localhost-slow.log
window可以在my.ini文件中配置具体的地址
Query_time SQL执行的时间,越长则越慢
Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent 查询返回的行数
Rows_examined 查询检查的行数
show profiles详情分析
通过慢查询日志,我们可以定位到超过设置阈值的慢SQL,但是实际业务中,这并不能完全具有代表性,因为阈值是主观设置的,可能有大量执行时间低于阈值的SQL也存在问题,因此慢日志SQL并不能完全定位出所有的慢SQL。
show profiles 能够让我们了解到SQL执行时时间都耗费到哪里了。 通过have_profiling参数,可以查看mysql是否支持该profile操作。
格式: select @@have_profiling;
默认情况下,profiling是关闭的,可以同set指令开启session|global级别的profiling。
格式: set global | session profiling = 1;
优化方案:
show profiles; 查看每一条SQL的耗时基本情况
show profile for query query_id; 查询指定query_id的SQL语句各个阶段的耗时情况
show profile cpu for query query_id; 查询指定query_id的SQL语句cpu使用情况
explain执行计划
前面介绍的几种方式都是通过执行时间长短来判断SQL语句执行的性能好坏,但是这个相对来说是比较片面的,想要更全面地评判SQL语句好坏,则需要使用explain查看SQL的执行计划。
Explain或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句过程中表如何连接和连接的顺序。
语法:explain | desc select xxxx...
1、ID参数
select中的查询序号,表示的是查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上往下,id不同,值越大,越先执行)
2、select_type参数
表示select查询类型,常见的有SIMPLE(简单表,即不使用表连接或者子查询)、primary(主查询,即外层查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)
3、type参数
表示连接/访问类型,性能由好到差的连接类型为:null、system、const、eq_ref、ref、range、index、all
在优化的时候,尽量将type往前优化,最差也要为index
null:查询的时候不访问任何表,如:select "1"
system:当访问一些系统表的时候会出现
const:根据主键或者唯一索引访问时,会出现const
eq_ref:待确认
ref:使用非唯一性索引进行访问时,可能出现ref
range:
index:使用到了索引,但是对整个索引都进行了遍历,性能也比较差
all:全表扫描,性能最差
4、possible_key参数:显示在执行查询时,表中可能被使用到的索引,一个或者多个、
5、key参数:在执行查询时,实际上会命中的索引
6、key_len参数:表示使用到的索引的字节数,该值为索引字段最大可能长度,在不损失精确性的前提下,长度越短越好。
7、rows参数:MySQL认为必须要执行查询的行数,在idb引擎表中,是一个估计值,可能并不总是准确的
8、ref参数:待确认?
9、filtered参数:表示查询返回的行数占总读取行数的百分比,值越大越好
10、extra参数:额外的一些执行信息如排序
来源:https://juejin.cn/post/7126184944379887646
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
MySQL数据库中CHAR与VARCHAR之争
详解JS几种变量交换方式以及性能分析对比
![](https://img.aspxhome.com/file/2023/0/136750_0s.png)
解决antd Form 表单校验方法无响应的问题
![](https://img.aspxhome.com/file/2023/4/123084_0s.jpg)
python对RabbitMQ的简单入门使用教程
![](https://img.aspxhome.com/file/2023/4/93734_0s.jpg)
Django Admin设置应用程序及模型顺序方法详解
![](https://img.aspxhome.com/file/2023/1/127151_0s.png)
Vue中的常用指令及用法总结
python os.path模块使用方法介绍
oracle移植到mysql注意事项
python numpy元素的区间查找方法
SQL 2008的变更数据捕获——跟踪可变部分
![](https://img.aspxhome.com/file/UploadPic/20093/2009322183639136.jpg)
django manage.py扩展自定义命令方法
![](https://img.aspxhome.com/file/2023/5/64225_0s.jpg)
Python函数式编程实现登录注册功能
![](https://img.aspxhome.com/file/2023/8/65138_0s.jpg)
根据表名和索引获取需要的列名的存储过程
详解Pandas之容易让人混淆的行选择和列选择
![](https://img.aspxhome.com/file/2023/0/134840_0s.png)
微信小程序使用Vant Weapp组件库的方法步骤
![](https://img.aspxhome.com/file/2023/6/56286_0s.jpg)
sqlserver数据库迁移后,孤立账号解决办法
利用OpenCV实现YOLO对象检测方法详解
![](https://img.aspxhome.com/file/2023/0/79730_0s.png)
ASP中Global.asa使用方法说明
Python基于ssh远程连接Mysql数据库操作
![](https://img.aspxhome.com/file/2023/0/80790_0s.jpg)