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

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

news 文章来源：https://blog.csdn.net/ousetuhou/article/details/135174830 2024/11/14 19:41:07

阅读本文你的收获

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

一、问题：如果你的系统运行很慢，你有什么解决方案？

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

前端方面的优化
页面缓存、前端框架层面的优化等
应用程序方面的优化：

代码层面进行一些优化，如采用更加适合的数据容器、处理逻辑/算法方便的改进；
采用性能更好的数据库访问技术，如Dapper比EF性能好，执行原生SQL语句比用ORM快等
引入缓存机制，缓存热点数据以减轻数据库I/O压力
采用异步编程，提高接口吞吐量等

数据库层面的优化

识别慢查询
加索引
通过执行计划，优化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提供的一种日志记录，专门用来记录慢查询。可以按照以下的步骤来开启和查看慢查询日志：

修改一下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性能及内存占用。

该功能默认是关闭的，要开启profiling功能，输入以下命令：

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

mysql> select * from syslog;

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

2.4 数据库调优的手段

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

select 时不用* 返回所有字段，查询具体字段可以提高一定速度
不用is null或is not null、
not in/in 不能使，用exists查询替代
不能用like ‘%xxx’，%开头的模糊查询用不到索引
少用不等于 !=、大于>、小于<的查询
or查询用union查询替代
…

MySQL实现读写分离
一般数据库都是读多而写少，如果数据量一大，单体数据库服务器造成性能瓶颈，可以采用MySQL数据库集群实现读写分离，一主多从。主库负责写操作，多个从库只负责读操作。
分库、分表
按业务系统不同，使用不同的数据库，即“分库”。
分表则分为垂直分表和水平分表。
（注：不做展开了，后续专门发文探讨）

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