MYSQL 优化

angen2018

已于 2024-08-28 13:08:26 修改

阅读量750

点赞数 3

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

于 2024-08-27 17:54:38 首次发布

本文链接：https://blog.csdn.net/qq_42572322/article/details/141608421

前言：优化有风险，需要谨慎！任何技术可以解决一个问题，但是有很大可能带来一个更大的问题。

mysql紧急问题，业务卡顿：

1，show processlist (查看连接session连接状态)

2，explain 分析查询计划，show index from tableName 分析索引

3，查看是否有死锁 show status like '%lock%'

MySQL优化是一个多方面的过程，主要包括以下几个方面：

1. **监控报警**：使用监控工具如Prometheus+Grafana监控MySQL性能，及时发现查询性能变慢并报警提醒运维人员。

2. **排查慢SQL**：开启慢查询日志，通过`show status like 'slow_queries';`查看慢查询次数，并使用`mysqldumpslow`工具找出最慢的SQL语句进行分析。

开启MySQL的慢查询日志是一个重要的性能监控手段，可以帮助你识别和优化执行效率低的查询语句。以下是开启慢查询日志的步骤：

1. **修改MySQL配置文件**：你需要在MySQL的配置文件中设置相关参数。配置文件的位置取决于操作系统和MySQL的安装方式，通常可能是`/etc/my.cnf`、`/etc/mysql/my.cnf`或`~/.my.cnf`等。你需要添加或修改以下配置项：

   ```ini
   [mysqld]
   slow_query_log = ON
   slow_query_log_file = /path/to/your/slow-query.log
   long_query_time = 1
   ```

   - `slow_query_log = ON`：开启慢查询日志。
   - `slow_query_log_file`：指定慢查询日志文件的存放路径和文件名。
   - `long_query_time`：设置慢查询阈值，单位为秒。这里设置为1秒，表示执行时间超过1秒的查询将被记录。

2. **通过命令行设置**：你也可以通过执行MySQL命令来动态设置慢查询日志，这不需要重启MySQL服务：

   ```sql
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';
   SET GLOBAL long_query_time = 1;
   ```

3. **重启MySQL服务**：如果你修改了配置文件，需要重启MySQL服务以使设置生效。重启命令取决于你的操作系统，例如在Linux上可能使用：

   ```bash
   sudo systemctl restart mysql
   ```

4. **检查慢查询日志**：开启慢查询日志后，你可以通过查看指定的日志文件来分析慢查询。使用`tail`命令可以实时查看日志文件的更新：

   ```bash
   tail -f /path/to/your/slow-query.log
   ```

5. **分析慢查询日志**：使用`mysqldumpslow`工具或其他日志分析工具来分析慢查询日志，找出最慢的查询或频繁执行的低效查询。

请注意，记录慢查询日志会引入一些性能开销，尤其是在高负载系统中，因此建议只在必要时开启，并在分析完成后关闭或减少日志级别。此外，对于生产环境，建议通过配置文件设置并重启服务来开启慢查询日志，以确保设置的持久性。

3. **基础优化**：包括缓存优化，如调整InnoDB缓冲池大小；硬件优化，如增加内存、升级SSD硬盘；参数优化，如调整最大连接数和线程池大小；定期清理垃圾；使用合适的存储引擎，如InnoDB或MyISAM；读写分离和分库分表等。

4. **表设计优化**：包括混合业务分表、冷热数据分表；联合查询改为中间关系表；遵循三个范式；字段建议非空约束；使用冗余字段；数据类型优化等。

5. **索引优化**：考虑索引失效的场景，遵循索引设计原则，优化连接查询、子查询、排序、分组，处理深分页查询问题，尽量覆盖索引，注意字符串前缀索引，使用MySQL 5.6支持的索引下推，以及在写多读少的场景下使用普通索引。

6. **SQL优化**：避免使用`SELECT *`，指定需要的列；避免在WHERE子句中对列使用函数或操作符，以利于索引的使用；合理使用正规化和反正规化来平衡数据冗余和查询效率。

7. **查询优化**：理解MySQL查询流程，利用慢查询日志分析慢查询语句，使用`EXPLAIN`分析查询计划，调整索引或语句本身。

EXPLAIN查询出来的字段分别代表的意思如下：

`EXPLAIN` 是 MySQL 中一个非常重要的命令，用于显示 SQL 查询的执行计划。它可以帮助开发者理解查询的执行过程，以及如何优化查询性能。以下是 `EXPLAIN` 结果中常见字段的含义：

1. **id**：查询中各个

最低0.47元/天解锁文章

200万优质内容无限畅学