【性能优化】MySql数据库查询优化方案

2023-12-23 23:32:19

阅读本文你的收获

  1. 了解系统运行效率提升的整体解决思路和方向
  2. 学会MySQl中进行数据库查询优化的步骤
  3. 学会看慢查询、执行计划、进行性能分析、调优

一、问题:如果你的系统运行很慢,你有什么解决方案?

?关于这个问题,我们通常首先考虑的是硬件升级,毕竟服务器的内存、CPU、磁盘IO速度 、网络速度等都是制约我们系统快慢的首要因素。硬件的升级相对来说比较容易,花点钱买台好点的服务器就行了。如果你用的是云服务器,那就更Easy了,花钱升级增配就行了,几分钟就完成了。
那程序(软件)层面我们怎么进行优化呢?我想,主要是以下几个方面:

  1. 前端方面的优化
    页面缓存、前端框架层面的优化等
  2. 应用程序方面的优化:
  • 代码层面进行一些优化,如采用更加适合的数据容器、处理逻辑/算法方便的改进;
  • 采用性能更好的数据库访问技术,如Dapper比EF性能好,执行原生SQL语句比用ORM快等
  • 引入缓存机制,缓存热点数据以减轻数据库I/O压力
  • 采用异步编程,提高接口吞吐量等
  1. 数据库层面的优化
  • 识别慢查询
  • 加索引
  • 通过执行计划,优化SQL语句
  • 采用存储过程
  • 读写分离
  • 分库分表等

除此以外,还有Web服务器,如IIS服务器的调优、系统架构层面的集群优化等,本文主要分享的是数据库层面的调优。

案例的演示环境:

操作系统:Windows10 专业版
MySQL版本:MySql 5.7.17
MySql客户端:Navicat for MySql
开发框架:ASP.NET Core6.0

二、如何进行数据库调优?

2.1 查出那些查询很慢的SQL语句

慢查询(slow_query):是指MySQL中响应时间超过阀值(long_query_time,单位:秒)的SQL语句。

MySQL的慢查询日志是MySQL提供的一种日志记录,专门用来记录慢查询。可以按照以下的步骤来开启和查看慢查询日志:

  1. 修改一下mysql的配置文件,然后重启mysql服务器

    我们首先要知道windows下的配置文件在哪里?如何查看?配置哪些选项?

    在mysql命令行界面,输入以下命令:

    mysql> show variables like ‘%data%’;

在这里插入图片描述 找到配置文件

打开my.ini配置文件,并添加/修改如下的配置参数:

[mysqld]
…
slow-query-log=1 #开启慢查询日志,默认为1,即开启
#slow_query_log_file=“WANGXF-PC-slow.log”
slow_query_log_file=D:/mysql/slow.log #慢查询日志的记录文件路径
#long_query_time=10
long_query_time=1 #查询超过1秒语句全部记录,默认值是10秒
log_queries_not_using_indexes=1 #没有使用索引的查询全部记录

改完之后重启mysql的服务
查看服务
2. 测试是否记录慢查询!此处用sleep函数来模拟一个很慢的查询,花5秒。
模拟慢查询
打开“D:/mysql/slow.log”查看是否记录了慢查询日志:
打开慢查询日志
3. 慢查询分析工具mysqldumpslow

mysqldumpslow是MySQL自带的分析慢查询的工具。 该命令格式如下:

mysqldumpslow [ OPTS…][ LOGS…]

mysqldumpslow是一个perl脚本,只需下载并赋权即可执行。所以我们首先需要在windows下安装Perl,安装过程很简单,从官网 http://strawberryperl.com/ 下载windows安装包,安装好之后,测试perl -v,如果能显示版本号,表示安装成功。

几个常用的命令

  • 得到返回记录集最多的10条SQL:

perl mysqldumpslow.pl -s r -t 10 D:/mysql/slow.log

  • 得到访问次数最多的10条SQL:

perl mysqldumpslow.pl -s r -t 10 D:/mysql/slow.log

  • 得到按照时间排序的前10条里面含有左连接的SQL:

perl mysqldumpslow.pl -s t -t 10-g “left join” D:/mysql/slow.log

2.2 通过explain查看执行计划

通过上面的步骤锁定了哪些查询是慢查询,接下来我们可以用explain命令查看执行计划,看看是否命中了索引。如果没有索引则创建索引,如果创建了索引但是没有起作用,则可以优化SQL语句来使索引生效。
查看执行计划
创建索引后,再去分析,可以看到他已经命中索引了:
创建索引命中了索引
explain命令的执行结果每一列的说明:

信息描述
id查询的序号,包含一组数字,表示查询中执行select子句或操作表的顺序 两种情况 id相同,执行顺序从上往下 id不同,id值越大,优先级越高,越先执行
select_type查询类型,主要用于区别普通查询,联合查询,子查询等的复杂查询 1、simple ——简单的select查询,查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分,最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived(衍生),MySQL会递归执行这些子查询,把结果放到临时表中 5、union——如果第二个select出现在UNION之后,则被标记为UNION,如果union包含在from子句的子查询中,外层select被标记为derived 6、union result:UNION 的结果
table输出的行所引用的表
type显示联结类型,显示查询使用了何种类型,按照从最佳到最坏类型排序 1、system:表中仅有一行(=系统表)这是const联结类型的一个特例。 2、const:表示通过索引一次就找到,const用于比较primary key或者unique索引。因为只匹配一行数据,所以如果将主键置于where列表中,mysql能将该查询转换为一个常量。 3、eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。 4、ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,可能会找多个符合条件的行,属于查找和扫描的混合体。 5、range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好,因为它开始于索引的某一个点,而结束另一个点,不用全表扫描。 6、index:index 与all区别为index类型只遍历索引树。通常比all快,因为索引文件比数据文件小很多。 7、all:遍历全表以找到匹配的行。 注意:一般保证查询至少达到range级别,最好能达到ref。
possible_keys指出MySQL能使用哪个索引在该表中找到行
key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引,则该索引和查询的select字段重叠。
key_len表示索引中使用的字节数,该列计算查询中使用的索引的长度 在不损失精度的情况下,长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度,并非实际使用长度。
ref显示索引的哪一列被使用了,如果有可能是一个常数,哪些列或常量被用于查询索引列上的值
rows根据表统计信息以及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra包含不适合在其他列中显示,但是十分重要的额外信息 1、Using filesort:说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果,mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引,避免访问了表的数据行。如果同时出现using where,表名索引被用来执行索引键值的查找;如果没有同时出现using where,表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false,不能用来获取任何元组 7、select tables optimized away:在没有group by子句的情况下,基于索引优化Min、max操作或者对于MyISAM存储引擎优化count(*),不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。 8、distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

2.3 如何进一步进行查询性能分析?

profiling性能分析,可以更进一步去分析每一句sql语句的cpu性能及内存占用。

  1. 该功能默认是关闭的,要开启profiling功能,输入以下命令:
    开启性能分析

执行查询语句,接着显示所有查询的性能分析结果:

mysql> select * from syslog;

查询所有性能分析
显示query_id为1的查询的性能报告:
查询性能分析结果

2.4 数据库调优的手段

  1. 索引优化
    可以对where条件后面的条件字段,和order by/group by后面的排序、分组字段创建索引,以提高查询速度。

  2. SQL语句优化

  • select 时不用* 返回所有字段,查询具体字段可以提高一定速度
  • 不用is null或is not null、
  • not in/in 不能使,用exists查询替代
  • 不能用like ‘%xxx’,%开头的模糊查询用不到索引
  • 少用不等于 !=、大于>、小于<的查询
  • or查询用union查询替代
  1. MySQL实现读写分离
    一般数据库都是读多而写少,如果数据量一大,单体数据库服务器造成性能瓶颈,可以采用MySQL数据库集群实现读写分离,一主多从。主库负责写操作,多个从库只负责读操作。

  2. 分库、分表
    按业务系统不同,使用不同的数据库,即“分库”。
    分表则分为垂直分表和水平分表。
    (注:不做展开了,后续专门发文探讨)


本次主要分享的是MySQL数据库的性能优化思路,如果本文对你有帮助的话,请点赞+评论+关注,或者转发给需要的朋友。

文章来源:https://blog.csdn.net/ousetuhou/article/details/135174830
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。