商务网站建设数据处理,蜜雪冰城用到了哪些网络营销方式,python 直播网站开发,360网站建设搜索阅读本文你的收获
了解系统运行效率提升的整体解决思路和方向学会MySQl中进行数据库查询优化的步骤学会看慢查询、执行计划、进行性能分析、调优 一、问题#xff1a;如果你的系统运行很慢#xff0c;你有什么解决方案#xff1f;
关于这个问题#xff0c;我们通常首先…阅读本文你的收获
了解系统运行效率提升的整体解决思路和方向学会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-log1 #开启慢查询日志默认为1即开启
#slow_query_log_file“WANGXF-PC-slow.log”
slow_query_log_fileD:/mysql/slow.log #慢查询日志的记录文件路径
#long_query_time10
long_query_time1 #查询超过1秒语句全部记录默认值是10秒
log_queries_not_using_indexes1 #没有使用索引的查询全部记录改完之后重启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数据库的性能优化思路如果本文对你有帮助的话请点赞评论关注或者转发给需要的朋友。
