为什么慢SQL会把服务器CPU压爆

我就是我 2022-12-10 14:38 102阅读 0赞

# 慢SQL & 连接池 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center]

数据库连接池是池化技术的一种实现，下面模仿一下简单的池化技术。

package com.wuhulala.thread.pool;
    
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.List;
    
    public class ConnectionPool { 
    
        private List<String> pool= new ArrayList<>(Arrays.asList("1", "2", "3", "4", "5", "6", "7", "8", "9", "10"));
    
        // 如果这个时候把线程给睡眠，估计cpu就不会爆炸，不然会一致重试。链接池的原理？？？？
        public synchronized String getConnection() { 
            if (!pool.isEmpty()) { 
                String s = pool.get(0);
                pool.remove(0);
                return s;
            }
            return null;
        }
    
        public synchronized void release(String s) { 
            pool.add(s);
        }
    
        public static void main(String[] args) { 
            int n = 200;
            ConnectionPool pool = new ConnectionPool();
    
            for (int i = 0; i < n; i++) { 
                new Thread(new Runnable() { 
                    @Override
                    public void run() { 
                        String poolName = null;
                        try { 
                            while ((poolName = pool.getConnection()) == null) { 
                                System.out.println(Thread.currentThread().getName() + "can not getObtain Pool");
                            }
                            System.out.println(Thread.currentThread().getName() + "obtain Pool [" + poolName + "]");
                            // 模拟慢sql
                            Thread.sleep(200000);
                        } catch (InterruptedException e) { 
                            e.printStackTrace();
                        } finally { 
                            pool.release(poolName);
                        }
                    }
                }).start();
            }
        }
    }

这段代码就是200个线程获取十个资源(可以理解为数据库连接)，然后10个线程获取后，执行慢SQL，其它线程空转重复轮训可否获取到连接。  
**以下是cpu表现：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 1]  
对比看了下是不是真正的连接池是这样的实现的：

## Druid ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 2]

## HikariPool ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 3]

## DBCP ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 4]

## TomcatPool ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 5]

## 总结 ##

可以看出果然如我们所料，超时时间内会无限的重试。

> 具体的细节没做过的探究

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/a09d16be360a4eac9ffbe579d9774010.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/75b871648d0b48af8459f754c8b6376a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/c562cde7962c4654bf00b9790ce1637b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/6d95be7a14044cf0a397ab5ec0ba42df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/f18135437c454caba6c8d492d030cc43.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNzYwNDQ_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/8534c326d4c6415f965ba67fa2d95c97.png