MySQL优化指南

12006

1. 数据库优化

MySQL数据库的性能优化有以下几个方面：

合理设置MySQL缓存池：MySQL有多种缓存机制，如 Key Buffer、Query Cache 和 InnoDB Buffer Pool（默认），如果配置不当会影响数据库性能，具体根据实际需求对其进行合理设置；
合理设置MySQL表：把 MyISAM 表转为 InnoDB 表，可以提高性能，设置表类型时，需要考虑表大小、并发读写等因素；
选择合适的存储引擎：MySQL有多种存储引擎，如MyISAM、InnoDB（默认）、Memory等，不同存储引擎对于不同的使用场景会有不同的表现，需要根据实际情况选择合适的存储引擎；
优化SQL语句：SQL是MySQL使用的语言，在SQL语句中加上合适的索引可以提高查询效率，需要注意SQL语句的性能；
定期维护和优化MySQL数据库：需要进行定期的MySQL数据库维护和优化，如清理不必要的日志和缓存数据、备份数据、调整服务器参数等。

Untitled 1.png

以下是一些推荐优化的配置：

最大使用内存：根据服务器的配置和实际情况，可以设置MySQL最大使用的内存，以提高查询性能。
配置缓存：MySQL缓存可以大大提高查询性能，减少访问磁盘的频率。可以通过配置文件或者使用MySQL自带的工具来设置缓存。
优化查询语句：根据具体业务场景，可以优化查询语句，减少查询的数据量和查询的次数，提高查询效率。
分区表：对于数据量非常大的表，可以考虑使用分区表来提高查询效率。
设置合适的interactive_timeout和wait_timeout参数：这两个参数分别表示服务器关闭交互式和非交互式活动连接之前等待的秒数。一般需要把这两个参数设置得尽可能低。可以在MySQL的配置文件中，加入以下设置：

[mysqld]
interactive_timeout=240
wait_timeout=240

2. 查看MySQL当前状态

通过江湖面板的 MySQL 插件，你可以查看MySQL运行的当前状态：

江湖面板-MySQL当前状态.png

在江湖面板左侧，选择“MySQL”，打开MySQL插件的管理界面。
查看MySQL插件中的“当前状态”，如果有数据过高，则可以按照相关建议，在“性能优化”中调整。
例外：如果“已打开的表”这一数据过高，则需要增大table_open_cache，并将open_files_limit设置为16096。

注意： table_open_cache这一参数默认超过2048是无效的。如果增大table_open_cache后MySQL性能没有改善，则需要做以下配置：

执行命令：

vi /usr/lib/systemd/system/mysql-apt.service

在打开的文件中，找到[Service]，在[Service]下增加以下配置：
```
LimitNOFILE=infinity
LimitMEMLOCK=infinity
```

重启MySQL服务：

systemctl daemon-reload
systemctl restart mysql-apt.service

3. JianghuJS项目配置

JianghuJS项目都需要连接到数据库才能运行。如果一个JianghuJS项目创建了过多的MySQL连接，有可能会耗尽MySQL的资源，导致其他项目进程无法使用MySQL。因此，需要正确的配置，才能保证项目可以合理的利用系统资源。

JianghuJS项目中的两个配置，会决定项目使用多少MySQL的资源：

package.json文件中，启动脚本里设置的workers数量

// package.json
{
  // 省略其他配置
  "scripts": {
    "start": "egg-scripts start --daemon --workers=2 --port=7001 --title=my_jianghujs_project", ## 启动脚本，不指定workers数量则会使用全部CPU核数
    // 省略其他脚本
  },
}

./config/config.prod.js配置文件中，knex中的pool配置

// ./config/config.prod.js
module.exports = appInfo => {
  return {
    // 省略其他设置
    knex: {
      client: {
        dialect: 'mysql',
        connection: {
          // 省略连接设置
        },
        pool: { min: 0, max: 10 }, # 最小与最大MySQL连接数
      },
      app: true
    }
  };
};

每个jianghuJS项目可以使用的连接数 = workers数量 x connection pool中的连接数。对于一般的项目来说，workers数量通常设置为1-2，pool中的最大连接数通常设置为7-10。你需要根据项目具体的功能、访问量来设置合理的数值。

作业

根据本文内容，完成下面作业

使用面板，创建一个 “jianghu” 的数据库
对 “jianghu” 数据库进行备份
尝试修改下数据库 root 密码

## 1. 数据库优化

MySQL数据库的性能优化有以下几个方面：

1. **合理设置MySQL缓存池**：MySQL有多种缓存机制，如 Key Buffer、Query Cache 和 InnoDB Buffer Pool（默认），如果配置不当会影响数据库性能，具体根据实际需求对其进行合理设置；
2. **合理设置MySQL表**：把 MyISAM 表转为 InnoDB 表，可以提高性能，设置表类型时，需要考虑表大小、并发读写等因素；
3. **选择合适的存储引擎**：MySQL有多种存储引擎，如MyISAM、InnoDB（默认）、Memory等，不同存储引擎对于不同的使用场景会有不同的表现，需要根据实际情况选择合适的存储引擎；
4. **优化SQL语句**：SQL是MySQL使用的语言，在SQL语句中加上合适的索引可以提高查询效率，需要注意SQL语句的性能；
5. **定期维护和优化MySQL数据库**：需要进行定期的MySQL数据库维护和优化，如清理不必要的日志和缓存数据、备份数据、调整服务器参数等。

![Untitled 1.png](/__appId__/__appId__/upload/articleMaterial/12608/Untitled3.png)

以下是一些推荐优化的配置：

- 最大使用内存：根据服务器的配置和实际情况，可以设置MySQL最大使用的内存，以提高查询性能。
- 配置缓存：MySQL缓存可以大大提高查询性能，减少访问磁盘的频率。可以通过配置文件或者使用MySQL自带的工具来设置缓存。
- 优化查询语句：根据具体业务场景，可以优化查询语句，减少查询的数据量和查询的次数，提高查询效率。
- 分区表：对于数据量非常大的表，可以考虑使用分区表来提高查询效率。
- 设置合适的`interactive_timeout`和`wait_timeout`参数：这两个参数分别表示服务器关闭交互式和非交互式活动连接之前等待的秒数。一般需要把这两个参数设置得尽可能低。可以在MySQL的配置文件中，加入以下设置：

```
[mysqld]
interactive_timeout=240
wait_timeout=240
```

## 2. 查看MySQL当前状态

通过江湖面板的 MySQL 插件，你可以查看MySQL运行的当前状态：

![江湖面板-MySQL当前状态.png](/__appId__/__appId__/upload/articleMaterial/12609/江湖面板-MySQL当前状态.png)

1. 在江湖面板左侧，选择“MySQL”， 打开MySQL插件的管理界面。
2. 查看MySQL插件中的“当前状态”，如果有数据过高，则可以按照相关建议，在“性能优化”中调整。
3. 例外：如果“已打开的表”这一数据过高，则需要增大`table_open_cache`，并将`open_files_limit`设置为`16096`。

**注意：** `table_open_cache`这一参数默认超过`2048`是无效的。如果增大`table_open_cache`后MySQL性能没有改善，则需要做以下配置：

- 执行命令：
```
vi /usr/lib/systemd/system/mysql-apt.service
```
- 在打开的文件中，找到`[Service]`，在`[Service]`下增加以下配置：
```
LimitNOFILE=infinity
LimitMEMLOCK=infinity
```
- 重启MySQL服务：
```
systemctl daemon-reload
systemctl restart mysql-apt.service
```

## 3. JianghuJS项目配置

JianghuJS项目都需要连接到数据库才能运行。如果一个JianghuJS项目创建了过多的MySQL连接，有可能会耗尽MySQL的资源，导致其他项目进程无法使用MySQL。因此，需要正确的配置，才能保证项目可以合理的利用系统资源。

JianghuJS项目中的两个配置，会决定项目使用多少MySQL的资源：

- `package.json`文件中，启动脚本里设置的`workers`数量

```
// package.json
{
  // 省略其他配置
  "scripts": {
    "start": "egg-scripts start --daemon --workers=2 --port=7001 --title=my_jianghujs_project", ## 启动脚本，不指定workers数量则会使用全部CPU核数
    // 省略其他脚本
  },
}
```

- `./config/config.prod.js`配置文件中，`knex`中的`pool`配置

```
// ./config/config.prod.js

module.exports = appInfo => {
  return {
    // 省略其他设置
    knex: {
      client: {
        dialect: 'mysql',
        connection: {
          // 省略连接设置
        },
        pool: { min: 0, max: 10 }, # 最小与最大MySQL连接数
      },
      app: true
    }
  };
};
```

每个jianghuJS项目可以使用的连接数 = workers数量 x connection pool中的连接数。对于一般的项目来说，`workers`数量通常设置为1-2，`pool`中的最大连接数通常设置为7-10。你需要根据项目具体的功能、访问量来设置合理的数值。

## 作业

根据本文内容，完成下面作业

- 使用面板，创建一个 “jianghu” 的数据库
- 对 “jianghu” 数据库进行备份
- 尝试修改下数据库 root 密码