数据库查询性能优化一直是程序员绕不开的话题。当我们遇到业务刷新报表慢或者查询结果获取延迟过大的时候，我们可以通过提问来思考如何优化。1、什么样的环境？硬件环境查询的执行速度与我们的硬件息息相关。目前使用什么样的CPU，多少核多少线程，内存的大小直接影响运算速度。磁盘是SSD还是HDD、网卡等，速度的快慢直接影响到我们数据读取的时延。虽然软件环境不像硬件，各种参数看得见摸得着，但还是影响着我们的查询性能。每个系统在特定场景下其实都有自己的优势。我们的查询系统是一个什么样的架构，它适合什么样的查询，是在线还是离线，是计算多还是数据读取多，这些我们在优化的时候要清楚。我们来看看如何基于这个思路来做性能优化。2.什么样的查询？首先，我们在优化查询的时候，要看它是什么类型的查询。写还是查询（这里只从篇幅谈查询），CPU密集型还是IO密集型。如果我们的系统适用于OLTP低延迟的枚举场景，显然不适合在这个系统上做OLAP的大规模分析。OLTP一般侧重于数据一致性高的枚举，而OLAP由于数据量巨大，一般需要向量并发查询。OLAP不关注毫秒级低延迟，OLTP不关注亿级数据统计。3、如何发现性能瓶颈3.1用vmstat查看系统状态当整个系统不知道当前的瓶颈在哪里时，我们可以使用vmstat工具简单地查看一下系统的大概情况。如下图，2表示每两秒采集一次服务器状态。procs：查看进程状态r：运行队列，即当前可运行（正在运行或等待运行）的进程数。目前CPU比较空闲，这个数量很少。当这个值超过CPU数量时，就会出现CPU瓶颈。b:Blockedprocesses，即处于不可中断睡眠状态的进程数。memory：查看内存状态swpd：已用虚拟内存的大小，如果大于0，说明机器开始使用虚拟内存，虚拟内存运行起来会很慢。这里的值为0表示我们关闭了虚拟内存功能。free：可用物理内存的大小。buff：内存作为系统缓冲区的大小。cache：内存用作系统缓存的大小。swap：磁盘和内存之间数据交换的状态。内西：每秒从磁盘读取的虚拟内存的大小。如果这个值大于0，说明物理内存不够用。so：每秒写入磁盘的虚拟内存大小。io：磁盘的io信息bi：每秒从块设备接收到的块数。bo：每秒发送到块设备的块数。如果这两个值较大，说明IO比较频繁，可以考虑IO优化。system：系统状态信息in：CPU每秒中断次数（包括时钟中断）cs：每秒上下文切换次数，我们调用系统函数和线程切换，我们需要进行上下文切换，如果这个值太大，我们可以考虑减少系统上下文切换，比如用协程代替多线程。CPU：我们的CPU信息：包括用户时间和nice时间，非内核代码（或用户代码）的运行时间。sy：Thetimetakenbythesystem，运行内核代码（如系统调用）所花费的时间。id：CPU空闲时间。wa:等待IOCPU时间。如果这个值太大，说明IO系统瓶颈在IO上。如果CPU占用率高，说明系统在CPU上。如果系统的swap频繁，很可能是系统内存泄露或者内存不够用，需要扩容内存。如果等待的IO很多，系统瓶颈就出现在IO上。如果上下文切换，或者系统调用的比例过大，那么我们就需要在设计我们的程序时考虑减少系统调用或者上下文切换。3.2CPU占用率过高我们可以通过uptime、top、mpstat或sar等工具查看当前CPU占用率过高。我们可以通过uptime查看当前系统的整体情况，当前系统时间和运行时间，登录用户数，以及最近5分钟、10分钟和15分钟的系统平均负载。top可以显示更详细的信息。head部分有详细的CPU使用率信息，下面的列表也记录了每个进程的CPU使用率。如果是多线程，我们还可以使用top-H-ppid查看进程各个线程的CPU占用情况。我们找出是哪个线程占的比重大之后，我们就可以根据线程名来查看线程名了。该怎么办。大致了解什么样的处理使得CPU比较高。mpstat可以查看系统各个核心的运行状态。sar的功能比较齐全，这里就不做科普了。用户态CPU占用率比较高。一般来说，我们写程序的效率太低了。具体可以使用perf工具或者intel的vtunes来排查性能瓶颈。perftop的执行结果如下图所示。我们得到相应的堆栈信息后，就可以有针对性的排除CPU瓶颈了。（vtune的用法可以自行google）。从以上工具检测到的情况来看，如果CPU水位真的很高，那么CPU基本就是性能瓶颈了。如果不高，则需要进入下一步判断性能瓶颈。3.3IO占用率过高IO定位有多种工具。一般我们可以使用iostat、pidstat和iotop工具来排查IO问题。当然，我们也可以使用其他工具。您可以搜索相关工具并自行使用。下面介绍一些常用的工具。pidstatpidstat是sysstat工具的一个命令，用于监控所有或指定进程对cpu、内存、线程、设备IO等系统资源的占用情况。用户可以通过指定统计的次数和时间来获取所需的统计信息。我们可以通过这个命令知道是哪个进程占用了较多的IO。然后我们可以通过指定进程号来查看更详细的信息。这样我们就可以知道哪个进程中哪个线程占用了更多的IO资源，然后我们就可以通过对应的TID找到对应的执行代码进行分析。iostatiostat是I/Ostatistics（输入/输出统计）的缩写，可以监控系统的磁盘操作活动，报告磁盘活动统计信息。但是iostat只是统计了系统的整体情况，并不能对某个进程进行深入的分析。我们可以使用iotop工具进行个别进程分析，使用方法与top类似。1表示每秒打印一次当前磁盘的统计信息。我们需要关注以下指标。avgrq-sz直接反映了当前io的类型，比如读取大块数据或者读取少量数据。avgqu-sz反映了当前IO繁忙的情况。如果队列长度太长，说明IO太忙，无法处理很多任务。也就是说，IO成为了瓶颈。await也是一样。如果wait比较高，说明IO成了负担。svctm和avgrq-sz一样，反映了IO操作的处理规模。如果读写大块数据，这个时间会更长。iotopiotop可以用来查看哪些进程占用了I/O??。使用方法与top类似，这里不再赘述。3.4其他情况，如果TOP使用率不高，IO不是瓶颈，可能是程序架构，比如并发控制不够好，很多线程处于休眠状态。这种情况下可以通过pstack查看当前所有线程的栈。4.优化性能瓶颈CPU瓶颈面临这种类型，一般我们需要使用perf来优化相应的代码，核心思想是减少计算量。具体方法如下，仅供参考：SIMD常用来代替老式的计算指令或C++运算符。可以引入类似于Intel的MKL库来辅助计算。减少不必要的重复计算，减少for循环次数。比如一些std库的数据结构中有find函数，有起始坐标，利用好起始坐标，避免从0坐标开始重复查询。如果系统调用过多，比如分配内存，可以考虑预分配内存的方式，或者直接使用tcmalloc等类似的内存管理库来覆盖底线。如果条件允许，可以基于这类库开发适合自己的内存管理。系统IO瓶颈IO瓶颈一般都和磁盘有关。网络上，因为升级了网卡，速度更快了。相比之下，有限的IO基本上就是磁盘上的IO。下面只讲磁盘的IO。优化。如果IO瓶颈是读请求引起的，可以考虑在上层多开一些缓存。比如全局的querycache，session级别的sessioncache，块设备的blockcache等，从上层减少对磁盘的io请求。如果IO瓶颈是小数据大并发写入造成的，我们可以考虑在内存中做一个缓存。多次写入首先在内存中处理，然后通过控制时间或大小阈值刷新到磁盘。优越的。如果是大数据写入，我们可以考虑做平滑写入，限制每次写入的次数。如果因为流量在某个时间点出现峰值，然后回落，可以考虑通过第三方写入。比如在消息队列中，先写入消息队列i进行削峰，然后再顺利写入系统。另外，我们也可以换更好的硬件，比如磁盘阵列。内存瓶颈型内存瓶颈一般比较难出现。毕竟内存比较便宜，基本能满足内存的需求。如果虚拟内存的问题导致程序运行效率低下，一方面要考虑增加内存，关闭虚拟内存来解决问题。同时也要思考自己的程序模型，比如减少中间数据的存在，多使用copy-on-write技术，使用系统的nocopy接口来替代旧的接口等。5.如果以后真的没办法优化了，真的要看看现在的query是不是真的适合我们的系统。同样，每个系统都有自己的业务。一般公司的系统系统中有很多种数据库引擎。这也是一种为我们的查询找到正确引擎的方法。
