MySQL的慢查询性能优化与高阶操作

最新推荐文章于 2020-11-03 15:53:17 发布

suoyue_zhan

最新推荐文章于 2020-11-03 15:53:17 发布

阅读量215

点赞数

分类专栏：数据库 MySQL 文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/suoyue_py/article/details/108926193

版权

数据库同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

MySQL

12 篇文章 0 订阅

订阅专栏

在这里插入图片描述

锁

表级锁：开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发访问效率较低。
行级锁：开销大，加锁慢，有可能会出现死锁；锁定粒度最小，发生锁冲突的概率低，并发访问效率较高。
共享锁（读锁）：其他事务可以读，但不能写。MySQL 可以通过 lock in share mode 语句显示使用共享锁。
排他锁（写锁）：其他事务不能读取，也不能写。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB会自动给涉及的数据集加排他锁，或者使用 select for update 显示使用排他锁。

存储引擎

InnoDB：在 5.5 版本后成为了 MySQL 的默认存储引擎，特点是支持 ACID 事务、支持外键、支持行级锁提高了并发效率。
MyISAM：官方提供的存储引擎，其特点是支持全文索引，查询效率比较高，缺点是不支持事务、使用表级锁。
MEMORY：功能等同与MyISAM，数据存储在内存，速度快。
TokuDB：第三方开发的开源存储引擎，有非常快的写速度，支持数据的压缩存储、可以在线添加索引而不影响读写操作。但是因为压缩的原因，TokuDB 非常适合访问频率不高的数据或历史数据归档，不适合大量读取的场景。

版本问题

在 5.6 版本后 InnoDB 引擎也支持了全文索引，并且在 5.7.6 版本后支持了中文索引
在 MySQL 8.0 之后 InnoDB 会把索引持久化到日志中，重启服务之后自增索引是不会丢失的
实例：在一个自增表里面一共有 5 条数据，id 从 1 到 5，删除了最后两条数据，也就是 id 为 4 和 5 的数据，之后重启的 MySQL 服务器，又新增了一条数据，请问新增的数据 id 为几？
通常的答案是如果表为 MyISAM 引擎，那么 id 就是 6，如果是 InnoDB 那么 id 就是 4。

MySQL8.0 的一些新特性

默认字符集格式改为了 UTF-8；
增加了隐藏索引的功能，隐藏后的索引不会被查询优化器使用，可以使用这个特性用于性能调试；
支持了通用表表达式，使复杂查询中的嵌入表语句更加清晰；
新增了窗口函数的概念，可以用来实现新的查询方式。
其中，窗口函数与 SUM、COUNT 等集合函数类似，但不会将多行查询结果合并，而是将结果放在多行中，即窗口函数不需要 GROUP BY。

MySQL的性能优化

原因：性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

SQL语句的解析过程是由命令解析器来解析的，具体可参考：
https://www.cnblogs.com/annsshadow/p/5037667.html

编写过程：
	select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..
解析过程：			
	from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

SQL优化，主要就是在优化索引

索引：相当于书的目录，是帮助MySQL高效获取数据的数据结构（B+树、Hash…）

索引的优势：

提高查询效率（降低IO使用率）
降低CPU使用率（…order by age desc,因为 B树索引本身就是一个排好序的结构，因此在排序时可以直接使用）

索引的弊端：

索引本身很大，可以存放在内存/硬盘（通常为硬盘）
索引不是所有情况均适用
a.少量数据
b.频繁更新的字段
c.很少使用的字段
索引会降低增删改的效率

在这里插入图片描述

高阶操作-开启慢查询日志

一、通过SQL命令语句操作

检查是否开启了慢查询日志：

 show variables like '%slow_query_log%' ;

临时开启慢查询日志（0和OFF则为关闭）：

set global slow_query_log = 1 ;
或者
set global slow_query_log='ON';

设置慢查询日志存放的位置（此处设置在D盘，文件名为mysql.log）：

set global slow_query_log_file='D:\\mysql.log';

查看慢查询阀值（单位秒，long_query_time值默认10）：

show variables like '%long_query_time%' ;

临时设置阀值（需要重新打开链接才能看到修改后的值）：

set global long_query_time = 0.5 ;

查询超过阀值的SQL语句（或直接去慢查询日志存放的位置查看日志文件）：

show global status like '%slow_queries%' ;

以上命令对于慢查询日志的操作都是临时开启的，即关闭/重启数据库服务就失效了
一般开发阶段，优化时才开启，因为开启慢查询日志会或多或少带来一定的性能影响
若需要永久设置慢查询日志开启，以及设置慢查询日志时间阈值可在MySQL的配置文件中进行修改

二、通过mysqldumpslow工具操作

通过mysqldumpslow工具可以通过一些过滤条件快速查找出需要定位的慢SQL语句。
Linux系统专用，windows系统要使用的话，在安装MySQL时需要在开发者工具处勾选，否则默认不安装

mysqldumpslow --help
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式		
--获取返回记录最多的3个SQL
	mysqldumpslow -s r -t 3  日志文件mysql.log
--获取访问次数最多的3个SQL
	mysqldumpslow -s c -t 3 mysql.log
--按照时间排序，前10条包含left join查询语句的SQL
	mysqldumpslow -s t -t 10 -g "left join" mysql.log
语法：
	mysqldumpslow 各种参数  慢查询日志的文件

优化方法，官网：https://dev.mysql.com/doc/refman/5.5/en/optimization.html
在这里插入图片描述
MySQL的查询优化器会干扰我们的优化

故使用explain命令分析SQL的执行计划，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况

查询执行计划： explain +SQL语句

在这里插入图片描述

图片来源于网络，若有侵权请联系处理

suoyue_zhan

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的慢查询性能优化与高阶操作

mysql的慢查询日志是mysql提供的一种日志记录，用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。默认情况下，Mysql数据库并不启动慢查询日志，需要手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
复制链接

扫一扫