精华内容
下载资源
问答
  • SQL执行慢的原因分析

    千次阅读 2019-12-07 12:48:10
    我们在日常开发中,一定遇见过一条SQL执行的情况,我们俗称“SQL”,如果你对系统的接口性能要求较高的话,一定不会放过这种SQL,肯定会想办法进行解决,那么,导致SQL出现的原因,究竟都有哪些呢?...

    前言

    我们在日常开发中,一定遇见过一条SQL执行较慢的情况,我们俗称“慢SQL”,如果你对系统的接口性能要求较高的话,一定不会放过这种SQL,肯定会想办法进行解决,那么,导致慢SQL出现的原因,究竟都有哪些呢?

    这是一道来自腾讯的面试题,最初在CSDN上看到有博友分享面试经验,觉得很有趣,决定来研究一番,下面,我们就来好好看一下,原因可能出在哪里。

    本篇文章的讨论,主要基于MySQL数据库,Oracle等其他数据库不在本篇讨论范围之列。

    SQL执行较慢的可能原因

    一提到慢SQL,想必有一些服务端开发经验的童鞋,第一反应肯定是执行的SQL语句没有走上索引。但是我们想想,这是来自于腾讯的面试题,面试官想得到的答案一定不仅仅如此,就像是问题 “在浏览器输入 www.taobao.com 敲击回车键后,发生了什么?”一样,是一个较为开放性的问题,核心是想考察候选人对MySQL内部引擎机制的了解程度,这个问题是没有固定的答案的。

    下面我们对一条SQL慢,可能是由哪些原因导致的,来分析一下。

    下面的所有分析,建立在服务器上的应用程序执行SQL语句的场景。

    情况分析

    一条SQL执行慢,也可能是分为不同的情况来看,大体上可以分成两种情况来看:

    1、相同数据量级下,SQL偶尔执行慢,频率非常低

    2、相同数据量级下,SQL每次执行都很慢,频率非常高

    我们对每一种情况进行具体分析。

    SQL偶尔执行慢

    一条 SQL 大多数情况正常,偶尔才能出现很慢的情况,针对这种情况,大概率上SQL语句本身应该是没有问题的,那么需要从别的层面进行分析。

    网络抖动

    目前的互联网应用服务,数据库的部署与应用服务的部署,不会在一台服务器上,那么应用服务器与数据库服务器之间就需要通过网络通信,一般情况来讲,都会在同机房或同一个可用区进行部署,以保证网络通信的质量,但是难免可能会出现网络抖动的情况,这种情况下,对应用服务来讲,可能会出现一条SQL执行较慢的情况,但不是由于数据库原因导致的,而是由于网络原因导致的。

    InnoDB脏页刷新

    在MySQL的InnoDB引擎中,存在一个redo log日志文件,叫做重做日志,它是在数据库崩溃后,恢复期间用于纠正由未完成事务写入的数据的基于硬盘里的一种日志。

    简单点说,重做日志是把那些之前仅仅写到缓冲池中,但还没来得及刷新到硬盘里的脏页,把结果重新执行一遍。

    也就是说,在意外关闭之前没有完成更新的数据文件,将在初始化期间以及连接被接受之前自动恢复。

    默认情况下,重做日志在物理上表现为一组名为ib_logfile0和ib_logfile1的文件。

    而在InnoDB的内存中,有一块区域与redo log日志文件进行对应,叫做redo log缓冲区。

    redo log缓冲区是一块内存区域,保存将要写入redo log的数据。

    redo log缓冲区会定期把内存中的回滚日志刷到磁盘上。一个大的redo log缓冲区意味着允许大事务运行,而无需在事务提交之前将redo log写入磁盘。

    InnoDB 修改和新增数据操作,实际上操作的是缓冲池Buffer Pool中的数据。

    为了保障数据的安全稳定,不丢失数据。InnoDB并不是一个事务提交后就将 Buffer Pool 中被修改的数据同步到硬盘上,而是要先记录到redo log日志中,以防崩溃之后可以恢复。

    最后再从Buffer Pool 中把数据页连续写入磁盘。

    而是记录到redo log日志中也不是直接写磁盘,而是先写到redo log 缓冲区中。

    img

    那么问题来了,redo log 里的容量是有限的,如果数据库一直很忙,更新又很频繁,这个时候 redo log 很快就会被写满了,这个时候就没办法等到空闲的时候再把数据同步到磁盘的,只能暂停其他操作,全身心来把数据同步到磁盘中去的,而这个时候,就会导致我们平时正常的SQL语句突然执行的很慢,所以说,数据库在在同步数据到磁盘的时候,就有可能导致我们的SQL语句执行的很慢了。

    操作等待锁资源

    我们都知道,当执行一条update语句,或者执行select xx from for update的时候,会锁定被操作的记录行,也就是对行记录加记录锁,也就是我们一般意义上所说的行锁,被行锁锁定后,只有持有锁的事务可以对这些记录进行操作,其他事务对这些行的操作请求将会进入等待,直到行记录锁被释放。

    如果希望查看用户线程持有锁的状态,可以使用命令:

    show processlist
    

    该命令是显示用户正在运行的线程,需要注意的是,除了 root 用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程,看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

    上面就是我可以想到的SQL偶尔执行慢的情况,下面我们再来看看SQL一直执行慢的情况。

    SQL一直执行慢

    如果一条SQL一直执行很慢,那么恭喜你,你的SQL肯定写的有问题,我们来分析一下可能有什么问题。

    SQL执行没有索引

    如果一条SQL执行一直很慢,那么大概率是没有走索引,这里又分为两种情况:

    1、SQL语句的where条件中字段没有索引

    2、SQL语句的where条件中字段有索引,但是没走上

    假设我们有一张student表,存在三个字段,主键ID,姓名name,年龄age:

    mysql> CREATE TABLE `student` (
      `id` int(11) NOT NULL,
      `name` varchar(20) DEFAULT NULL,
      `age` int(5) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    

    我们先看第一种情况。

    执行SQL,查询年龄20岁以下的学生:

    select id, name, age from student where age < 20;
    

    使用explain命令查询执行计划:
    执行计划1

    SQL的where条件字段没有索引,因此进行了全表扫描。当数据量大的时候,SQL执行一定是非常的慢。

    我们再来看第二种情况。

    我们为student表的age字段来加上一个索引。

    alter table student add index idx_age (age);
    

    执行SQL,同样是查询年龄20岁以下的学生:

    select id, name, age from student where age -1 < 21;
    

    使用explain命令查询执行计划:
    执行计划2

    根据执行计划来看,还是进行了全表扫描操作,我们不是加索引了吗?为什么还会进行全表扫描呢?

    这里需要注意MySQL的机制,如果在where条件中对索引字段进行运算操作或者函数操作,SQL执行时不会触发索引执行。

    SQL执行走索引

    上面我们分析了SQL执行没有走索引,导致执行慢的情况,那么,如果我们的SQL走上了索引,还有可能出现执行很慢的情况吗?

    答案是:有可能。

    我们还是用student表,执行SQL语句:

    select id, name, age from student where age < 21;
    

    使用explain命令查询执行计划:
    执行计划3

    发现我们是执行了索引的操作,TYPE的类型是ALL,也就是说仍旧进行了全表扫描的动作,这又是为什么呢?

    这里要从InnoDB的索引组织结构说起,我在之前的 闲聊MySQL(七)InnoDB之索引结构 文中详细介绍过InnoDB的索引结构,InnoDB中的数据是面向主键索引进行数据存储的。 换句话说,实际的数据行都是存储在主键索引中的。辅助索引的查询,是需要首先找到主键索引的位置,再去根据主键索引的位置查询具体的记录值。

    InnoDB在执行这条语句的时候,会进行预测:究竟是走辅助索引扫描的行数少,还是直接扫描全表扫描的行数少呢?显然,扫描行数越少当然越好了,因为扫描行数越少,意味着I/O操作的次数越少。

    如果是扫描全表的话,那么扫描的次数就是这个表的总行数了,假设为 n;而如果走辅助索引的话,我们通过辅助索引找到主键之后,还得再通过主键索引来找我们整行的数据,也就是说,需要走两次索引。而且,我们也不知道符合age < 21这个条件的数据有多少行,万一这个表是全部数据都符合呢?这个时候意味着,走辅助索引不仅扫描的行数是 n,同时还得每行数据走两次索引。这样性能会更加的差。

    那么问题又来了,InnoDB是怎么知道哪种方式更加的合适呢?

    InnoDB是通过索引的区分度来判断的,一个索引上不同的值越多,意味着出现相同数值的索引越少,意味着索引的区分度越高。我们也把区分度称之为基数,即区分度越高,基数越大。所以呢,基数越大,意味着符合age < 21这个条件的行数越少。

    所以呢,一个索引的基数越大,意味着走索引查询越有优势。

    当然,这也只是InnoDB做选择的一个依据之一,具体的执行依据还会根据SQL解析器的执行优化做具体的判断。

    单表数据量过大

    上面我们提到了InnoDB的数据存储方式是面向主键索引进行数据存储的。 我在之前的文章闲聊MySQL(七)InnoDB之索引结构 ,分析InnoDB索引结构的时候,也分析过,InnoDB的数据表数量级超过千万后,性能会出现下降,核心是由于B+Tree的数据结构导致的。

    因此,当你的数据表超过千万量级的时候,SQL执行即使全部命中的索引,也有可能出现执行慢的情况。

    这个时候,建议考虑采用拆表。目前开源的优秀的分库分表中间件有很多,例如sharing-JDBC、MyCat等,可以根据业务的实际情况进行选择,这里就不过多的进行赘述。

    总结

    本篇,我们围绕着一个问题:一条SQL执行较慢可能有哪些原因导致的? 进行了研究,下面进行一下总结:

    1、SQL偶尔执行慢,可能是因为网络抖动、InnoDB的脏页刷新机制、操作等待锁资源导致的;

    2、SQL一直执行慢,可能是因为SQL执行没有索引或者没有走上索引,以及单表数据量过大导致的。

    当然一个SQL执行慢可能不仅仅是这些因素导致,还是要秉持具体情况 具体分析 的原则,分析可能的具体原因。

    展开全文
  • mysql sql执行慢 分析过程

    千次阅读 2017-09-11 17:05:38
    为了验证select 1 与 select 1 from tableName 与 select * from tableName的执行效率,需要测试一下各自执行的时间。于是总结一下,查看mysql语句运行时间的方法。 方法一: show profiles。 1. Show ...

    为了验证select 1 与 select 1 from tableName 与 select * from tableName的执行效率,需要测试一下各自执行的时间。于是总结一下,查看mysql语句运行时间的方法。

    方法一: show profiles。

    1. Show profiles是5.0.37之后添加的,要想使用此功能,要确保版本在5.0.37之后。

         Query Profiler是MYSQL自带的一种query诊断分析工具,通过它可以分析出一条SQL语句的性能瓶颈在什么地方。通常我们是使用的explain,以及slow query log都无法做到精确分析,

         但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况,比如CPU,IO等,以及该SQL执行所耗费的时间等。

         查看数据库版本方法:show variables like "%version%";  或者  select version();

        

    2.确定支持show profile 后,查看profile是否开启,数据库默认是不开启的。变量profiling是用户变量,每次都得重新启用。

       查看方法: show variables like "%pro%";

       设置开启方法: set profiling = 1;

       

       再次查看show variables like "%pro%"; 已经是开启的状态了。

    3.可以开始执行一些想要分析的sql语句了,执行完后,show profiles;即可查看所有sql的总的执行时间。

      

      show profile for query 1 即可查看第1个sql语句的执行的各个操作的耗时详情。

      

     show profile cpu, block io, memory,swaps,context switches,source for query 6;可以查看出一条SQL语句执行的各种资源消耗情况,比如CPU,IO等

     show profile all for query 6 查看第6条语句的所有的执行信息。

     测试完毕后,关闭参数:

    mysql> set profiling=0

    方法二: timestampdiff来查看执行时间。

    这种方法有一点要注意,就是三条sql语句要尽量连一起执行,不然误差太大,根本不准

    set @d=now();
    select * from comment;
    select timestampdiff(second,@d,now());
    

    如果是用命令行来执行的话,有一点要注意,就是在select timestampdiff(second,@d,now());后面,一定要多copy一个空行,不然最后一个sql要你自己按回车执行,这样就不准了。

    展开全文
  • SQL执行的原因分析

    千次阅读 2019-04-30 17:25:33
    sql执行的时候可以大致分为两种情况: 一、大部分情况下正常,偶尔 1.数据库刷新脏页 redolog写满:更新数据或者插入数据时,会先在内存中将相应的数据更新,并不会立刻持久化到磁盘中去,而是把更新记录...

    sql执行很慢的时候可以大致分为两种情况:

    一、大部分情况下正常,偶尔慢

       1.数据库刷新脏页

    • redolog写满:更新数据或者插入数据时,会先在内存中将相应的数据更新,并不会立刻持久化到磁盘中去,而是把更新记录存到redolog日志中去,待到空闲时,再通过redolog把最新数据同步到磁盘中去。所以当redolog写满的时候,就不会等到空闲时,而是暂停手中的活,去把数据同步到磁盘中,所以这个时候SQL就会执行的比较慢
    • 内存写满:如果一次查询的数据过多,查询的数据页并不在内存中,这时候就需要申请新的内存空间,而如果此时内存已满,就需要淘汰一部分内存数据页,如果是干净页就直接释放,如果是脏页就需要flush
    • 数据库认为空闲的时候:这时候系统不忙
    • 数据库正常关闭:内存脏页flush到磁盘上

        2.无法获取锁

    二、数据量不变的情况下,一直都很慢

    1. 字段没有索引
    2. 有索引没用
    3. 索引没用上
    4. 数据库选错索引:通过区分度判断走索引的话反而扫描的行数很大而且索引要走两边,选择全表扫描
    展开全文
  • SQL查询原因分析

    2011-08-18 11:33:56
    SQL查询原因分析SQL查询原因分析
  • 一、导致SQL执行慢的原因 1、硬件问题。如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了等。 2、没有索引或者索引失效。(一般在互联网公司,DBA会在半夜把表锁了,重新建立一遍索引,因为当你删除某个数据的...

    一、导致SQL执行慢的原因

    1、硬件问题。如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了等。

    2、没有索引或者索引失效。(一般在互联网公司,DBA会在半夜把表锁了,重新建立一遍索引,因为当你删除某个数据的时候,索引的树结构就不完整了。所以互联网公司的数据做的是假删除,一是为了做数据分析,二是为了不破坏索引 )

    3、数据过多(分库分表)

    4、服务器调优及各个参数设置(调整my.cnf)

    二、分析原因时,一定要找切入点

    1、先观察,开启慢查询日志,设置相应的阈值(比如超过3秒就是慢SQL),在生产环境跑上个一天过后,看看哪些SQL比较慢。

    2、Explain和慢SQL分析。比如SQL语句写的烂,索引没有或失效,关联查询太多(有时候是设计缺陷或者不得以的需求)等等。

    3、Show Profile是比Explain更近一步的执行细节,可以查询到执行每一个SQL都干了什么事,这些事分别花了多少秒。

    4、找DBA或者运维对MySQL进行服务器的参数调优。
    解析:
    (1)、explain出来的各种item的意义

    id:每个被独立执行的操作的标志,表示对象被操作的顺序。一般来说, id 值大,先被执行;如果 id 值相同,则顺序从上到下。
    select_type:查询中每个 select 子句的类型。
    table:名字,被操作的对象名称,通常的表名(或者别名),但是也有其他格式。
    partitions:匹配的分区信息。
    type:join 类型。
    possible_keys:列出可能会用到的索引。
    key:实际用到的索引。
    key_len:用到的索引键的平均长度,单位为字节。
    ref:表示本行被操作的对象的参照对象,可能是一个常量用 const 表示,也可能是其他表的
    key 指向的对象,比如说驱动表的连接列。
    rows:估计每次需要扫描的行数。
    filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。
    extra:重要的补充信息。

    (2)、profile的意义以及使用场景

    Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候,需要用profile 来对 sql 进行更细致的分析,找出 sql 所花的时间大部分消耗在哪个部分,确认 sql的性能瓶颈。

    (3)、explain 中的索引问题

    Explain 结果中,一般来说,要看到尽量用 index(type 为 const、 ref 等, key 列有值),避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列,需要建立索引。
    被驱动表的连接列,也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时,也可以考虑建立索引来达到直接排序和汇总的需求。

    展开全文
  • SQL 执行分析执行时间分析)一、SQL 执行时间分析1.通过 show processlist 来查看系统的执行情况2.通过 profiling 来进行查看2.1 查看 profiling 是否开启2.2 打开工具2.3 查看 SQL执行时间2.4 查看 SQL 执行...
  • 造成SQL语句执行的原因分析

    千次阅读 2019-07-25 17:23:50
    一条SQL语句执行,在实际情况下可能会经常遇到。这里需要分两种情况讨论:相同的SQL语句偶尔执行SQL语句每次执行都很SQL语句偶尔执行 SQL语句偶尔执行,说明不是SQL语句本身的问题,即数据库...
  • mysql 分析查找执行效率SQL语句

    万次阅读 2015-11-13 19:11:58
    启动Mysql时加参数--log-slow-queries来记录执行时间超过long_...slow log 是 MySQL 根据 SQL 语句的执行时间设定,写入的一个文件,用于分析执行的语句。 只要在 my.cnf 文件中配置好:  log-slow-queries =
  • select *from (select sa.SQL_TEXT,sa.SQL_FULLTEXT,sa.EXECUTIONS "执行次数",round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "...
  • 【0】如何分析mysql中sql执行的问题 步骤1、观察,至少跑一天,看看生产的sql情况; 步骤2、开启查询日志,设置阈值,比如超过5秒钟就是sql, 并将它抓取出来; 步骤3、explain+sql分析; 步骤4、...
  • MySQL中如何查看“查询”,如何分析执行SQL的效率?
  • 为提高oracle的sql执行效率,优化性能,通过oracle表分析功能进行调整,能加快sql查询效率2倍以上
  • 但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇无比。此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从而使语句高效的运行。例如,如果我们...
  • 查询每天执行慢SQL

    2020-12-04 22:55:01
    系统性能测试及高级开发人员可在评估或发现系统性能瓶颈过程中,在分析系统网络环境、数据量、请求的时间响应过程中,重要的一个问题方面的原因分析可从数据库中去查询出具体执行慢SQL场景,以分析出数据库的设计...
  • 一次线上Case,一条联合查询语句,直接在sql server查询分析执行,1秒以内,通过java程序执行要7秒返回结果 贴出sql 语句 select temp.id, temp.userId, temp.operationUserId, temp.filename from ( ...
  • 同一sql程序执行比数据库执行慢

    千次阅读 2018-03-28 14:34:55
    最近项目发现同一个sql在java端执行比在数据库执行慢很多,原因可能是程序的sql参数类型与数据库字段的类型不一致。
  • 为什么你的SQL执行

    千次阅读 2019-05-29 18:35:13
    为什么你的SQL执行SQL语句执行很原因分析1.没走索引1.1对索引字段进行了计算操作1.2存在隐式类型转换1.3 like操作1.4隐式编码转换1.5 not in 操作1.6扫描行数太多2.等待锁3.刷脏页4.执行undo log索引设计原则...
  • 在工作中,我们用于捕捉性能问题最常用的就是打开查询,定位执行效率差的SQL,那么当我们定位到一个SQL以后还不算完事,我们还需要知道该SQL执行计划,比如是全表扫描,还是索引扫描,这些都需要通过EXPLAIN去...
  • MYSQL 使用OR查询SQL执行

    千次阅读 2019-11-10 22:07:47
    根据接口分析,发现程序在执行SQL时发现又一些数据在执行SQL时很,一条SQL运行需要花30S左右。 SELECT DISTINCT t.id AS appId, t.priority FROM yzs_unified_app t LEFT JOIN yzs_unified_app_...
  • oracle数据库执行sql

    千次阅读 2018-04-24 17:49:00
    查看sql是否走索引2:查看索引是否失效3:hint 强制走索引(只是用来查看hint状态下,查询是否更改,应用是不能改的)4:收集该表所有信息(包括索引)5:分析该表所有信息(包括索引)6:再次执行并查看注意:哪个...
  • oracle查看执行最与查询次数最多的sql语句注:本文来源 于《oracle查看执行最与查询次数最多的sql语句》前言在ORACLE数据库应用调优中,一个SQL的执行次数/频率也是常常需要关注的,因为某个SQL执行太频繁,要么...
  • 问题一:存储过程放在查询分析器中执行,大概耗时5秒左右即可成功执行,但是在网站中执行却会超时 1. 将该存储过程放到查询分析器中执行,大概耗时5秒左右即可成功执行,但是在网站中执行却会超时; 2.经查阅...
  • 一条sql执行,可能是因为什么? 怎么优化?

    万次阅读 多人点赞 2020-05-14 17:10:03
    下面是网络中流传最广的一篇sql查询速度的原因及解决方法的文章,其对于处理mysql的查询有借鉴作用。由于此文转载多次,很难找到最开始的原文链接,就附送本人最先看到此文的链接:...
  • db2调优sql执行分析

    千次阅读 2011-08-16 22:28:09
    db2调优sql执行分析  在之前一直有个误解,认为把表的全部字段建立索引跟不建是一样的,所以在一次实际应用中,当数据量到达了100万之后,检索速度明显地,而且在执行查询时间cpu占用率非常的高,导致影响到了...
  • 本篇文章主要是通过数据库锁机制和索引来分析SQL语句执行速度慢的原因。 偶尔执行速度慢 如果一条SQL语句绝大多数时候执行速度正常,偶尔执行慢。那么可能是因为产生了锁竞争,也可能是数据库为了保持数据一致性,在...
  • mysql查看查询、分析执行SQL的效率

    千次阅读 2017-07-18 14:44:07
    查看慢SQL是否启用,查看命令:show variables like 'log_slow_queries';  如果结果为ON则是开启了,如果为OFF则表示禁用了。 2 开启查询命令:set global log_slow_queries = on;
  • 注:上一篇博文中提到了如何定位效率比较低的sql语句,本文重在介绍如何使用explain和profile对其进行分析和改进,因为系统设计的数据表和SQL语句较少,所以此种方案不失为一种好的选择。一、explain介绍在分析查询...
  • Sql执行平时都很快但是偶尔就会很 记录一下在翻看MySQL技术文章的资料,觉得很不错就自己记录一下。大部分来源于网络。 SQL执行的原因 一条Sql执行,那是每次执行都还是偶尔,简单的总结一下: 一、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 120,784
精华内容 48,313
关键字:

如何分析sql执行慢