# mysql_learn

**Repository Path**: wh543/mysql_learn

## Basic Information

- **Project Name**: mysql_learn
- **Description**: No description available
- **Primary Language**: SQL
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-05-21
- **Last Updated**: 2021-06-27

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README


# MySQL 优化
![](.README_images/0ba0b800.png)
		

​		从MySQL的基本架构示意图入手，试图优化SQL在MySQL各个模块中的执行过程。

# 连接--配置优化

​		服务端连接数不够会导致应用程序获取不到连接。比如报了一个 Mysql: error 1040: Too many connections 的错误。
​		我们可以从两个方面来解决连接数不够的问题: 
​		从服务端来说，我们可以增加服务端的可用连接数。 如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以: 

1. 修改配置参数增加可用连接数，修改 max_connections 的大小:
```sql
show variables like 'max_connections'; -- 修改最大连接数，当有多个应用连接的时候
```
2. 或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是 28800 秒，8 小时，我们可以把这个值调小。
```sql
show global variables like 'wait_timeout'; --及时释放不活动的连接，注意不要释放连接池还在使用的连接
```

​		从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL都创建一个新的连接，应该怎么做?

​        这个时候我们可以引入连接池，实现连接的重用。我们可以在哪些层面使用连接池？ORM 层面(MyBatis 自带了一个连接池);或者 使用专用的连接池工具(阿里的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、老牌的DBCP和C3P0)。

​        当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢?大家可能会有一个误解，觉得连接池的最大连接数越大越好，这样在高并发的情况下客户端可以获取的连接数更多，不需要排队。实际情况并不是这样。连接池并不是越大越好，只要维护一定数量大小的连接池， 其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。



​        Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。 为什么默认值都是这么小呢?
​        在Hikari的github文档中，给出了一个PostgreSQL数据库建议的设置连接池大小的公式: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

​        它的建议是机器核数乘以2加1。也就是说，4核的机器，连接池维护9个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接池大小实现提升并发度和吞吐量的案例。

​        为什么有的情况下，减少连接数反而会提升吞吐量呢?为什么建议设置的连接池大小要跟CPU的核数相关呢?

​        每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多。



​        问题：CPU是怎么同时执行远远超过它的核数大小的任务的？时间片。上下文切换。而CPU的核数是有限的，频繁的上下文切换会造成比较大的性能开销。

# 缓存--架构优化

​		在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题:一方面是 会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到 影响。

​		我们可以用第三方的缓存服务来解决这个问题，例如 Redis。

​		运行独立的缓存服务，属于架构层面的优化。为减少单台数据库服务器的读写压力。



# 优化器--SQL语句分析与优化

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

## 打开慢日志开关

```mysql
show variables like 'slow_query%';
```

​		除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默 认是 10 秒。

```mysql
show variables like '%long_query%';
```

可以直接动态修改参数(重启后失效)。 

```mysql
set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值
show variables like '%long_query%';
show variables like '%slow_query%';
```

或者修改配置文件 my.cnf。

以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

```mysql
slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log
```

## 慢日志分析

### 日志内容

```mysql
show global status like 'slow_queries'; -- 查看有多少慢查询 
show variables like '%slow_query%'; -- 获取慢日志目录
```

### mysqldumpslow

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

```mysql
mysqldumpslow --help
```

例如:查询用时最多的 20 条慢 SQL:

```mysql
mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log
```



- Count 代表这个 SQL 执行了多少次;
- Time 代表执行的时间，括号里面是累计时间; Lock 表示锁定的时间，括号是累计;
- Rows 表示返回的记录数，括号是累计。