mysql如何优化慢查询_慢sql优化思路

mysql如何优化慢查询_慢sql优化思路这篇文章主要是就在公司实习的时候,对SQL优化工作作出的一些整理。  在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。进行SQL优化的手段也主要是修改SQL写法,或者新增索引。  现在从记录项目中的一点点做起。  (1)数据库中设置SQL慢查询    一、第一步.开启mysql慢查询     方…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

这篇文章主要是就在公司实习的时候,对SQL优化工作作出的一些整理。

    在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。进行SQL优化的手段也主要是修改SQL写法,或者新增索引。

    现在从记录项目中的一点点做起。

    (1)数据库中设置SQL慢查询

      一、第一步.开启mysql慢查询  

    方式一:

       修改配置文件  在 my.ini 增加几行:  主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log日志记录( slow_query_log)

mysql如何优化慢查询_慢sql优化思路

      方法二:通过MySQL数据库开启慢查询:

mysql如何优化慢查询_慢sql优化思路

(2)分析慢查询日志         

       直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句,来分析sql慢查询语句

      例如:执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

       得到如下结果: 显示结果分析:  

                   table |  type | possible_keys | key |key_len  | ref | rows | Extra  EXPLAIN列的解释:           

                   table                 显示这一行的数据是关于哪张表的           

                  type                  这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 

                   rows                显示需要扫描行数

                   key                   使用的索引

 

(3)常见的慢查询优化

 

 (1)索引没起作用的情况

    1. 使用LIKE关键字的查询语句

        在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置索引才会起作用。

    2. 使用多列索引的查询语句

        MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。

 

 (2)优化数据库结构

        合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快。数据库结构的设计,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表 

        对于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

2. 增加中间表

        对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

 

(3)分解关联查询

    将一个大的查询分解为多个小查询是很有必要的。

  很多高性能的应用都会对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联,很多场景下这样会更高效,例如:       

 SELECT * FROM tag 
        JOIN tag_post ON tag_id = tag.id
        JOIN post ON tag_post.post_id = post.id
        WHERE tag.tag = 'mysql';

        分解为:

        SELECT * FROM tag WHERE tag = 'mysql';
        SELECT * FROM tag_post WHERE tag_id = 1234;
        SELECT * FROM post WHERE post.id in (123,456,567);

(4)优化LIMIT分页

      在系统中需要分页的操作通常会使用limit加上偏移量的方法实现,同时加上合适的order by 子句。如果有对应的索引,通常效率会不错,否则MySQL需要做大量的文件排序操作。

      一个非常令人头疼问题就是当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,这是mysql需要查询10020条然后只返回最后20条,前面的10000条记录都将被舍弃,这样的代价很高。

        优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

      对于下面的查询:

       select id,title from collect limit 90000,10;

      该语句存在的最大问题在于limit M,N中偏移量M太大(我们暂不考虑筛选字段上要不要添加索引的影响),导致每次查询都要先从整个表中找到满足条件 的前M条记录,之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大,且筛选字段没有合适的索引,且M特别大那么这样的代价是非常高的。 试想,如我们下一次的查询能从前一次查询结束后标记的位置开始查找,找到满足条件的100条记录,并记下下一次查询应该开始的位置,以便于下一次查询能直接从该位置 开始,这样就不必每次查询都先从整个表中先找到满足条件的前M条记录,舍弃,在从M+1开始再找到100条满足条件的记录了。

方法一:虑筛选字段(title)上加索引

       title字段加索引  (此效率如何未加验证)

 

方法二:先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理:先查询出90000条数据对应的主键id的值,然后直接通过该id的值直接查询该id后面的数据。

 

方法三:“关延迟联”

如果这个表非常大,那么这个查询可以改写成如下的方式:

      Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

        这里的“关延迟联”将大大提升查询的效率,它让MySQL扫描尽可能少的页面,获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

 

方法四:建立复合索引 acct_id和create_time

    select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

     注意sql查询慢的原因都是:引起filesort

 

(5)分析具体的SQL语句

 1、两个表选哪个为驱动表,表面是可以以数据量的大小作为依据,但是实际经验最好交给mysql查询优化器自己去判断。

  例如:  select * from a where id in (select id from b );  

        对于这条sql语句它的执行计划其实并不是先查询出b表的所有id,然后再与a表的id进行比较。
mysql会把in子查询转换成exists相关子查询,所以它实际等同于这条sql语句:select * from a where exists(select * from b where b.id=a.id );

        而exists相关子查询的执行原理是: 循环取出a表的每一条记录与b表进行比较,比较的条件是a.id=b.id . 看a表的每条记录的id是否在b表存在,如果存在就行返回a表的这条记录。

exists查询有什么弊端?
      由exists执行原理可知,a表(外表)使用不了索引,必须全表扫描,因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查(外表到里表中),顺序是固定死的。

如何优化?
      建索引。但是由上面分析可知,要建索引只能在b表的id字段建,不能在a表的id上,mysql利用不上。

这样优化够了吗?还差一些。
      由于exists查询它的执行计划只能拿着a表的数据到b表查(外表到里表中),虽然可以在b表的id字段建索引来提高查询效率。
但是并不能反过来拿着b表的数据到a表查,exists子查询的查询顺序是固定死的。

为什么要反过来?
       因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问:在双方两个表的id字段上都建有索引时,到底是a表查b表的效率高,还是b表查a表的效率高?

该如何进一步优化?
       把查询修改成inner join连接查询:select * from a inner join b on a.id=b.id; (但是仅此还不够,接着往下看)

为什么不用left join 和 right join?
       这时候表之间的连接的顺序就被固定住了,比如左连接就是必须先查左表全表扫描,然后一条一条的到另外表去查询,右连接同理。仍然不是最好的选择。

为什么使用inner join就可以?
       inner join中的两张表,如: a inner join b,但实际执行的顺序是跟写法的顺序没有半毛钱关系的,最终执行也可能会是b连接a,顺序不是固定死的。如果on条件字段有索引的情况下,同样可以使用上索引。

那我们又怎么能知道a和b什么样的执行顺序效率更高?
       你不知道,我也不知道。谁知道?mysql自己知道。让mysql自己去判断(查询优化器)。具体表的连接顺序和使用索引情况,mysql查询优化器会对每种情况做出成本评估,最终选择最优的那个做为执行计划。

        在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高,如果两个表都建有索引的情况下,mysql同样会评估使用a表条件字段上的索引效率高还是b表的。

利用explain字段查看执行时运用到的key(索引)
       而我们要做的就是:把两个表的连接条件的两个字段都各自建立上索引,然后explain 一下,查看执行计划,看mysql到底利用了哪个索引,最后再把没有使用索引的表的字段索引给去掉就行了。

 

 

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/181870.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • SGD随机梯度下降_随机梯度法

    SGD随机梯度下降_随机梯度法BGDvsSGDBGDvsSGD名词解释功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能,丰富你的文章UML图表FLowchart流程图导出与导入导出导入BGDvsSGD…

  • Qt多线程通信

    Qt多线程通信简述:1>Qt线程间共享数据主要有两种方式:1)使用共享内存。即使用一个两个线程都能够共享的变量(如全局变量),这样两个线程都能够访问和修改该变量,从而达到共享数据的目的。2)使用singal/slot机制,把数据从一个线程传递到另外一个线程。第一种方法在各个编程语言都普遍使用,而第二种方法是QT的特有的,本文主要介绍第二种。2>

  • SpringMVC框架工作流程图及工作原理

    SpringMVC框架工作流程图及工作原理SpringMVC框架的工作原理图:SpringMVC的具体工作原理1、客户端用户发送请求至前端控制器DispatcherServlet。2、DispatcherServlet收到请求调用HandlerMapping处理器映射器。3、HandlerMapping处理器映射器找到具体的处理器(可以根据xml配置、注解进行查找),生成处理器对象及处理器拦截器(如果有则生成)一并返回给Dispatc…

  • iis配置后localhost无法访问_如何打开服务器管理器

    iis配置后localhost无法访问_如何打开服务器管理器在iis中打开网站提示:http500内部服务器错误。网上查了很久的资料得到以下两步骤: 1.启动DistributedTransactionCoordinator(dtc,完整的简称是msdtc):在‘开始‘——’运行‘中输入“services.msc”(或   者是通过’控制面板‘进入’管理工具‘,再进入’服务‘),找到msdtc,右键启动。   一般情况会弹

  • linux top命令 详解「建议收藏」

    linux top命令 详解「建议收藏」top命令主要用来观察和收集运行在系统上的进程的一些有用信息。ps只是一个快照,是ps命令执行的那一瞬间的系统中进程的快照。top则可以用于持续观察。第一步,在命令行键入top,回车进入top管理界面。第一行其实和uptime的执行效果是一样的。分析一下,12:48:06是当前运行这个命令的时候,机器的时间。up134days,19:36,指的是说这台机器,持续运行了134天了,启动于134天前的19:36。5users代表的是当前运行这个命令的时候,这台机器上总共登陆有5个用

  • nport串口服务器原理,MOXA串口服务器NPORT-5130详细配置

    《MOXA串口服务器NPORT-5130详细配置》由会员分享,可在线阅读,更多相关《MOXA串口服务器NPORT-5130详细配置(13页珍藏版)》请在人人文库网上搜索。1、MOXA串口联网服务器NPORT5130特点-以太网口支持100/10M自适应,串口支持RS-422,RS-485(2w/4w)-低成本、信用卡大小-支持Windows/LinuxCOM串口驱动程序模式-提供包括…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号