随着互联网应用的不断发展，数据的处理与存储成为一个非常重要的环节。数据库作为数据存储的核心，需要时刻保持高效的运行状态。然而，在一些高负载的应用场景下，我们会遇到一些数据库CPU消耗过高的问题。这时候，我们需要快速定位问题SQL语句并进行优化，才能保证应用的正常运行。本文将介绍如何通过一些简单的方法快速定位数据库消耗CPU的SQL语句。
监控数据库性能
在实际的工作中，为了快速定位问题SQL语句，我们需要先对数据库的性能进行监控。常见的数据库监控工具有：MySQL Workbench、Navicat、DBeaver、DataGrip等。这些工具可以监控数据库的CPU、内存、磁盘、网络等指标，通过这些指标我们可以了解数据库的整体运行状况。
查看CPU占用率高的进程
当我们发现数据库的CPU占用率过高时，需要查看当前占用CPU的进程。在Linux系统下，可以使用top命令查看系统的进程信息，并按照CPU占用率进行排序。在Windows系统下，可以使用任务管理器查看当前进程的CPU占用率。
查看慢查询日志
数据库的慢查询日志可以记录执行时间超过一定阈值的SQL语句，可以通过查看慢查询日志来定位数据库性能问题。在MySQL中，可以通过修改my.cnf文件中的slow_query_log参数来开启慢查询日志。慢查询日志的输出路径和日志格式可以通过slow_query_log_file和log_slow_verbosity参数进行配置。查看慢查询日志可以使用工具如：MySQL Workbench、pt-query-digest等。
使用Explain命令查看SQL语句执行计划
在定位SQL语句性能问题时，我们需要了解SQL语句的执行计划。在MySQL中，可以使用Explain命令查看SQL语句的执行计划。Explain命令会输出SQL语句的执行计划、索引使用情况、数据访问方式等信息，可以通过这些信息来定位性能问题。
Explain [SQL语句]
分析SQL语句
在了解了SQL语句的执行计划之后，我们需要进一步分析SQL语句，找出性能问题所在。在分析SQL语句时，我们需要关注以下几个方面：
通过对这些方面的分析，可以找出SQL语句性能问题的所在，并进行相应优化。
使用监控工具定位问题
以上提到的方法虽然可以帮助我们找到最耗费 CPU 的 SQL 语句，但有些情况下仍然不够。比如当数据库 服务器 同时处理多个连接时，使用以上方法定位的语句可能不是最耗费 CPU 的语句，因为在高并发的情况下，数据库的 CPU 使用情况可能会发生瞬间的变化。
因此，在实际场景中，使用监控工具是定位问题最为有效的方式之一。常用的数据库监控工具包括：MySQL 自带的 Performance Schema、pt-query-digest 等。这里以 Performance Schema 为例，简单介绍一下如何使用它定位数据库消耗 CPU 的 SQL 语句。
Performance Schema 是 MySQL 5.5 版本以后引入的性能监控工具，它可以捕获数据库执行的各种操作，包括 SQL 语句执行的时间、锁等待的时间、索引使用情况等。我们可以使用 Performance Schema 捕获数据库执行的语句，然后根据执行时间、执行次数等指标来判断 SQL 语句的消耗情况。
以下是使用 Performance Schema 定位数据库消耗 CPU 的 SQL 语句的步骤：
在 MySQL 5.6 版本以后，默认情况下 Performance Schema 已经是开启状态。可以使用以下命令来确认是否开启：
SHOW VARIABLES LIKE 'performance_schema';
如果结果为 ON，则表示 Performance Schema 已经开启。如果结果为 OFF，则需要手动开启。
Performance Schema 需要配置一些参数，以便可以捕获执行的 SQL 语句。以下是常用的配置参数：
performance_schema=ONperformance_schema_events_statements_history_size=10000performance_schema_events_statements_history_long_size=10000performance_schema_events_waits_history_size=10000
其中，performance_schema=ON 表示开启 Performance Schema；performance_schema_events_statements_history_size 和performance_schema_events_statements_history_long_size 分别表示保存 SQL 语句执行历史的大小，可以根据需要进行调整；performance_schema_events_waits_history_size 表示保存等待事件的大小，可以不进行配置。
在 Performance Schema 开启的情况下，可以使用以下命令来捕获 SQL 语句执行历史：
以上命令可以捕获执行过的 SELECT 语句。根据需要可以修改 WHERE 子句的条件。
SQL Profiler 是 SQL Server 自带的一个性能分析工具，可以帮助我们捕获 SQL Server 实例中的事件，如 SQL 执行、事务、错务等，同时提供了多种分析选项。
可以通过以下步骤开启 SQL Profiler 分析：
通过 SQL Profiler 可以捕获到执行耗时较长的 SQL 语句，并进行性能分析。
2、使用性能监视器（Performance Monitor）进行性能分析
性能监视器是 Windows 系统自带的一个性能分析工具，可以监控系统资源的使用情况，包括 CPU 使用率、内存使用情况、磁盘 I/O 等。
可以通过以下步骤开启性能监视器分析：
通过性能监视器，可以监控到 SQL Server 实例的各项性能指标，找到资源瓶颈，进一步优化 SQL Server 实例的性能。
总结
以上就是快速定位数据库消耗 CPU 的 SQL 语句的几种方法，每一种方法都有其优点和适用场景，可以根据具体情况选择合适的方法进行分析。
在进行性能分析时，需要注意以下几点：
电脑死机是重了病毒吗
由硬件原因引起的死机【散热不良】 显示器、电源和CPU在工作中发热量非常大，因此保持良好的通风状况非常重要，如果显示器过热将会导致色彩、图象失真甚至缩短显示器寿命。 工作时间太长也会导致电源或显示器散热不畅而造成电脑死机。 CPU的散热是关系到电脑运行的稳定性的重要问题，也是散热故障发生的“重灾区”。 【移动不当】 在电脑移动过程中受到很大振动常常会使机器内部器件松动，从而导致接触不良，引起电脑死机，所以移动电脑时应当避免剧烈振动。 【灰尘杀手】 机器内灰尘过多也会引起死机故障。 如软驱磁头或光驱激光头沾染过多灰尘后，会导致读写错误，严重的会引起电脑死机。 【设备不匹配】 如主板主频和CPU主频不匹配，老主板超频时将外频定得太高，可能就不能保证运行的稳定性，因而导致频繁死机。 【软硬件不兼容】 三维软件和一些特殊软件，可能在有的微机上就不能正常启动甚至安装，其中可能就有软硬件兼容方面的问题。 【内存条故障】 主要是内存条松动、虚焊或内存芯片本身质量所致。 应根据具体情况排除内存条接触故障，如果是内存条质量存在问题，则需更换内存才能解决问题。 【硬盘故障】 主要是硬盘老化或由于使用不当造成坏道、坏扇区。 这样机器在运行时就很容易发生死机。 可以用专用工具软件来进行排障处理，如损坏严重则只能更换硬盘了。 另外对于在不支持UDMA 66/100的主板，应注意CMOS中硬盘运行方式的设定。 【CPU超频】 超频提高了CPU的工作频率，同时，也可能使其性能变得不稳定。 究其原因，CPU在内存中存取数据的速度本来就快于内存与硬盘交换数据的速度，超频使这种矛盾更加突出，加剧了在内存或虚拟内存中找不到所需数据的情况，这样就会出现“异常错误”。 解决办法当然也比较简单，就是让CPU回到正常的频率上。 【内存条故障】 主要是内存条松动、虚焊或内存芯片本身质量所致。 应根据具体情况排除内存条接触故障，如果是内存条质量存在问题，则需更换内存才能解决问题。 【硬盘故障】 主要是硬盘老化或由于使用不当造成坏道、坏扇区。 这样机器在运行时就很容易发生死机。 可以用专用工具软件来进行排障处理，如损坏严重则只能更换硬盘了。 另外对于在不支持UDMA 66/100的主板，应注意CMOS中硬盘运行方式的设定。 【CPU超频】 超频提高了CPU的工作频率，同时，也可能使其性能变得不稳定。 究其原因，CPU在内存中存取数据的速度本来就快于内存与硬盘交换数据的速度，超频使这种矛盾更加突出，加剧了在内存或虚拟内存中找不到所需数据的情况，这样就会出现“异常错误”。 解决办法当然也比较简单，就是让CPU回到正常的频率上。
寻求SQL数据库的有关论文
ORACLE中SQL查询优化研究摘 要 数据库性能问题一直是决策者及技术人员共同关注的焦点，影响数据库性能的一个重要因素就是SQL查询语句的低效率。 论文首先分析了导致SQL查询语句性能低下的四个常见原因以及SQL调优的一般步骤，然后分别针对如何降低I/O操作、在查询语句中如何避免对查询结果的高成本操作以及在多表连接时如何提高查询效率进行了分析。 关键词 ORACLE；SQL；优化；连接1 引言 随着网络应用不断发展，系统性能已越来越引起决策者的重视。 影响系统性能的因素很多，低效的SQL语句就是其中一个不可忽视的重要原因。 论文首先分析导致SQL性能低下的常见原因，然后分析SQL调优应遵循的一般步骤，最后从如何降低I/O、避免对查询结果的高成本操作和多表连接中如何提高SQL性能进行了研究。 鉴于目前ORACLE在数据库市场上的主导地位，论文将只针对ORACLE进行讨论。 2 影响SQL性能的原因 影响SQL性能的因素很多，如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序(CBO)的正确判断等，这些往往和DBA密切相关。 纯粹从SQL语句出发，笔者认为影响SQL性能不外乎以下四个重要原因： (1)在大记录集上进行高成本操作，如使用了引起排序的谓词等。 (2)过多的I/O操作(含物理I/O与逻辑I/O)，最典型的就是未建立恰当的索引，导致对查询表进行全表扫描。 (3)处理了太多的无用记录，如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。 (4)未充分利用数据库提供的功能，如查询的并行化处理等。 第(4)个原因处理起来相对简单。 论文将针对前三个原因论述如何提高SQL查询语句的性能。 3 SQL优化的一般步骤 SQL优化一般需经过发现问题、分析问题、提出解决措施、应用措施、测试性能几个步骤，如图1所示。 “发现问题就是解决问题的一半”，因此在SQL调优过程中，定位问题SQL是非常重要的一步，一般可借助于ORACLE自带的性能优化工具如STATSPACK、TKPROF、AUTOTRACE等辅助用户进行，同时还应该重视动态性能视图如V$SQL、V$MYSTAT、V$SYSSTAT等的研究。 图1 SQL优化的一般步骤 4 SQL语句的优化 4.1 优化排序操作 排序的成本十分高昂，当在查询语句中使用了引起结果集排序的谓词时，SQL性能必然受到影响。 4.1.1 排序过程分析 当待排序数据集不是太大时，服务器在内存(排序区)完成排序操作，如果排序需要更多的内存空间，服务器将进行如下处理： (1) 将数据分成多个小的集合，对每一集合进行排序。 (2) 服务器向磁盘申请临时空间，将排好序的中间结果写入临时段，再对另外的集合进行排序。 (3) 在所有的集合均排好序后，服务器再将它们进行合并得到最终的结果，如果排序区尺寸太小，合并无法一次完成时，将分多次进行。 从上述分析可知，排序是一种十分昂贵的操作，它消耗大量的CPU时间和内存，触发磁盘分页和交换操作，因此只要有可能，我们就应该在SQL语句中尽量避免排序操作。 4.1.2 SQL中引起排序的操作 SQL查询语句中引起排序的操作大致有：ORDER BY 和GROUP BY 从句；DISTINCT修饰符；UNION、INTERSECT、MINUS集合操作符；多表连接时的排序合并连接(SORT MERGE JOIN)等。 4.1.3 如何避免排序 1)建立恰当的索引 对经常进行排序和连接操作的字段建立索引。 在建立索引后，当服务器向这些字段发出排序请求时，将直接引用索引而不进行排序操作；当进行等值连接查询操作时，若建立连接的字段未建立索引，服务器进行的是排序合并连接(SORT MERGE JOIN)，连接操作的过程如下： 对进行连接的两个或多个表分别进行全扫描； 对每一个表中的行集分别进行全排序； 合并排序结果。 如果建立连接的字段已建立索引，服务器进行嵌套循环连接(NESTED LOOP JOINS)，该连接方式不需要任何排序，其过程如下： 对驱动表进行全表扫描； 对返回的每一行利用连接字段值实施索引惟一扫描； 利用从索引扫描中返回的ROWID值在从表中定位记录； 合并主、从表中的匹配记录。 因此，建立索引可避免多数排序操作。 2)用UNIION ALL替换UNION UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。 大部分应用中是不会产生重复记录的，最常见的是过程表与历史表UNION 。 因此，采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。 4.2 优化I/O 过多的I/O操作会占用CPU时间、消耗大量内存和占用过多的栓锁，因此有必要对SQL的I/O进行优化。 优化I/O的最有效方式就是用索引扫描代替全表扫描。 4.2.1 应用基于函数的索引 基于函数的索引(FUNCTION BASED INDEX，简记为FBI)提供了索引计算列并在查询中使用这些索引的能力。 FBI的实质是对查询所需中间结果进行预处理。 如果一个FBI与查询语句中的内嵌函数完全匹配，CBO在生成查询计划时，将自动启用索引范围扫描(INDEX RANGE SCAN)替换全表扫描(FULL TABLE SCAN)。 考察下面的代码段并用AUTOTRACE观察创建FBI前后执行计划的变化。 select * from emp where upper(ename)=’SCOTT’ 创建FBI前，很明显是全表扫描。 Execution Plan …… 1 0 TABLE ACCESS (FULL) OF EMPLOYEES (Cost=2 Card=1 Bytes=22) idle>CREATE INDEX EMP_UPPER_FIRST_NAME ON EMPLOYEES(UPPER(FIRST_NAME))； 索引已创建。 再次运行相同查询， Execution Plan …… 1 0 TABLE ACCESS (BY INDEX ROWID) OF EMPLOYEES (Cost=1 Card=1 Bytes=22) 2 1 INDEX (RANGE SCAN) OF EMP_UPPER_FIRST_NAME (NON-UNIQUE) (Cost=1 Card=1) 这一简单的例子充分说明了FBI在SQL查询优化中的作用。 FBI所用的函数可以是用户自己创建的函数，该函数越复杂，基于该函数创建FBI对SQL查询性能的优化作用越明显。 4.2.2 应用物化视图和查询重写 物化视图是一个预计算结果集，其中通常包含聚集与多表连接等复杂操作。 数据库自动维护物化视图，且随用户的要求进行刷新。 查询重写机制就是用数据库中的替代对象(如物化视图)将用户提交的查询重写为完全不同但功能等价的查询。 查询重写对用户透明，用户完全按常规编写访问数据库的查询语句，优化程序(CBO)自动决定是否对用户提交的查询进行重写。 查询重写是提高查询性能的一种非常有效的方法，尤其是在数据仓库环境中针对汇总、多表连接以及其它高成本的操作方面。 下面以一个非常简单的例子来演示物化视图和查询重写在优化SQL查询性能方面的作用。 select ，，count(*) from emp，dept where = group by ， 查询计划及主要统计数据如下： 执行计划： ----------------------------------------- …… 2 1 HASH JOIN (Cost=5 Card=14 Bytes=224) 3 2 TABLE ACCESS (FULL) OF DEPT (Cost=2 Card=4 Bytes=52) 4 2 TABLE ACCESS (FULL) OF EMP (Cost=2 Card=14 Bytes=42) 主要统计数据： ----------------------------------------- 305 recursive calls 46 consistent gets 创建物化视图EMP_DEPT： create materialized view emp_dept build immediate refresh on demand enable query rewrite as select ，，count(*) from emp，dept where = group by ， / 再次执行查询，执行计划及主要统计数据如下： 执行计划： ------------------------------------- …… 1 0 TABLE ACCESS (FULL) OF EMP_DEPT (Cost=2 Card=327 Bytes=) 主要统计数据： ------------------------------------ 79 recursive calls 28 consistent gets 可见，在建立物化视图之前，首先执行两个表的全表扫描，然后进行HASH连接，再进行分组排序和选择操作；而建立物化视图后，CBO自动将上述复杂操作转换为对物化视图EMP_DEPT的全扫描，相关的统计数据也有了很大的改善，递归调用(RECURSIVE CALLS)由305降到79，逻辑I/O(CONSISTENT GETS)由46降为28。 4.2.3 将频繁访问的小表读入CACHE 逻辑I/O总是快于物理I/O。 如果数据库中存在被应用程序频繁访问的小表，可将这些表强行读入KEEP池，从而避免物理I/O的发生。 4.3 多表连接优化 最能体现查询复杂性的就是多表连接，多表连接操作往往要耗费大量的CPU时间和内存，因此多表连接查询性能优化往往是SQL优化的重点与难点。 4.3.1 消除外部连接 通过消除外部连接，不仅使得到的查询更易于读取，而且性能也经常可以得到改善。 一般的思路是，有以下形式的查询： SELECT …，OUTER_JOINED_ FROM SOME_TABLE，OUTER_JOINED_TO_TABLE WHERE …=OUTER_JOINED_TO_TABLE(+) 可转换为如下形式的查询： SELECT …，(SELECT COLUMN FROM OUTER_ JOINED_TO_TABLE WHERE …)FROM SOME_TABLE； 4.3.2 谓词前推，优化中间结果 多表连接的性能低下多数是因为连接操作与过滤操作的次序不合理，大多数用户在编写多表连接查询时，总是先进行连接操作再应用过滤条件，这导致服务器做了太多的无用功。 针对这类问题，其优化思路就是尽可能将过滤谓词前推，使不符合条件的记录提前被筛选掉，只对符合条件的少数记录进行连接处理，这样可成倍的提高SQL查询效能。 标准连接查询如下： Select _name，sum(_quant)， sum(_quant)，sum(_quant) From product a，tele_sale b，online_sale c，store_sale d Where _id=_id and _id=_id and _id=_id And _date>sysdate-90 Group by _id； 启用内嵌视图，且将条件_date>sysdate-90前移，优化后代码如下： Select _name，_sale_sum，_sale_sum，_sale_sum From product a， (select sum(sal_quant) tele_sale_sum from product，tele_sale Where _date>sysdate-90 and _id =tele__id) b， (select sum(sal_quant) online_sale_sum from product，tele_sale Where _date>sysdate-90 and _id =online__id) c， (select sum(sal_quant) store_sale_sum from product，store_sale Where _date>sysdate-90 and _id =store__id) d， Where _id=_id and _id=_id and _id=_id； 5 结束语 SQL语言在数据库应用中占有非常重要的地位，其性能的优劣直接影响着整个信息系统的可用性。 论文从影响SQL性能的最主要的三个方面入手，分析了如何优化SQL查询的I/O、避免高成本的排序操作和优化多表连接。 需要强调的一点是，理解SQL语句所解决的问题比SQL调优本身更重要，因此SQL调优需要系统分析人员、开发人员和数据库管理员密切协作。 参考文献 [1]Thomas Oracle by Design：Design and Build High-performance Oracle Application[M]，The McGral- Hill Companies，Inc，2003 [2]Kevin Loney，George Koch，Oracle 9i：The Complete Reference[M]，The McGral-Hill Companies，Inc，2002 [3] Oracle9i SQL Reference release 2(9.2)[OL/M]，2002.10. http：///technology/ [4] Oracle9i Data Warehousing Guide release 2(9.2) [OL/M]，2002.03. http：///technology/ [5]Alexey Danchenkov，Donald BURLeson，Oracle Tuning：The Definitive Reference[OL/M]，Rampant Techpress，2006. [6] Oracle9i Database Concepts release 2(9.2) [OL/M]，2002.08. http：///technology/ [7] Oracle9i supplied plsql packages and types reference release 2(9.2) [OL/M]，2002.12. http：/// technology/
什么是磁盘阵列？？
从RAID1到RAID5的几种方案中，不论何时有磁盘损坏，都可以随时拔出损坏的磁盘再插入好的磁盘(需要硬件上的热插拔支持)，数据不会受损，失效盘的内容可以很快地重建，重建的工作也由RAID硬件或RAID软件来完成。 但RAID0不提供错误校验功能，所以有人说它不能算作是RAID，其实这也是RAID0为什么被称为0级RAID的原因--0本身就代表没有。 1.3 RAID 的应用当前的PC机，整个系统的速度瓶颈主要是硬盘。 虽然不断有Ultra DMA33、 DMA66、DMA100等快速的标准推出，但收效不大。 在PC中，磁盘速度慢一些并不是太严重的事情。 但在服务器中，这是不允许的，服务器必须能响应来自四面八方的服务请求，这些请求大多与磁盘上的数据有关，所以服务器的磁盘子系统必须要有很高的输入输出速率。 为了数据的安全，还要有一定的容错功能。 RAID 提供了这些功能，所以RAID被广泛地应用在服务器体系中。 1.4 RAID 提供的容错功能是自动实现的(由RAID硬件或是RAID软件来做)。 它对应用程序是透明的，即无需应用程序为容错做半点工作。 要得到最高的安全性和最快的恢复速度，可以使用RAID1(镜像)；要在容量、容错和性能上取折衷可以使用RAID 5。 在大多数数据库服务器中，操作系统和数据库管理系统所在的磁盘驱动器是RAID 1，数据库的数据文件则是存放于RAID5的磁盘驱动器上。 1.5 有时我们看某些名牌服务器的配置单，发现其CPU并不是很快，内存也算不上是很大，显卡更不是最好，但价格绝对不菲。 是不是服务器系统都是暴利产品呢？当然不是。 服务器的配置与一般的家用PC的着重点不在一处。 除去更高的稳定性外，冗余与容错是一大特点，如双电源、带电池备份的磁盘高速缓冲器、热插拔硬盘、热插拔PCI插槽等。 另一个特点就是巨大的磁盘吞吐量。 这主要归功于RAID。 举一个例子来说，一台使用了SCSI RAID的奔腾166与一台IDE硬盘的PIIICopermine 800都用做文件服务器，奔腾166会比PⅢ的事务处理能力高上几十倍甚至上百倍，因为PⅢ处理器的运算能力根本用不上，反倒是奔腾166的RAID起了作用。 1.6 RAID现在主要应用在服务器，但就像任何高端技术一样，RAID也在向PC机上转移。 也许所有的 PC 机都用上了SCSI磁盘驱动器的RAID的那一天，才是PC机真正的出头之日
发表评论