MYSQL 优化

angen2018

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

阅读量646

点赞数 3

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

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

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

版权

mysql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

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

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**：查询中各个部分的标识符，每个部分都有一个唯一的 id，如果多个部分具有相同的 id，则表示它们是同一个操作的一部分。

2. **select_type**：查询类型，例如 SIMPLE（简单查询），PRIMARY（主查询），SUBQUERY（子查询），DERIVED（派生表）等。

3. **table**：查询操作的表名。

4. **partitions**：匹配的分区信息，如果表没有分区，通常显示为 NULL。

5. **type**：这是表示访问类型的重要字段，例如：
   - `ALL`：全表扫描。
   - `index`：全索引扫描。
   - `range`：范围扫描，使用索引。
   - `ref`：非唯一索引查找。
   - `eq_ref`：唯一索引查找。
   - `const` 或 `system`：立即值或系统表，速度非常快。

6. **possible_keys**：可能使用的索引，这些是 MySQL 服务器认为可能用于查找的索引。

7. **key**：实际使用的索引。如果为 NULL，表示没有使用索引。

8. **key_len**：使用的索引的长度。

9. **ref**：对于当前表，索引查找中的关联列。

10. **rows**：估计的行数，即扫描的行数或结果集的大小。

11. **filtered**：列的值经过条件过滤后剩余的百分比，例如 100% 表示所有行都匹配，0% 表示没有行匹配。

12. **Extra**：额外信息，可能包含很多有用的提示，例如：
    - `Using where`：表示使用了 WHERE 子句来过滤行。
    - `Using index`：表示查询使用了覆盖索引，没有回表查询。
    - `Using temporary`：表示查询需要使用临时表。
    - `Using filesort`：表示使用了文件排序，而不是索引排序。
    - `Using join buffer`：表示使用了连接缓存。

13. **cost**：查询的成本，数值越小表示成本越低。

14. **rows_affected**：这个字段表示查询影响的行数，例如 UPDATE 或 DELETE 语句。

15. **est_rows_sent**：估算发送给客户端的行数。

16. **est_row_size**：估算每行的大小。

`EXPLAIN` 输出的字段可能会根据 MySQL 版本有所不同。在实际使用中，应该根据查询的具体情况来分析这些字段的含义。

MySQL支持多种类型的索引，每种索引都有其特定的用例和优化场景。以下是MySQL中常见的索引类型：

1. **主键索引（PRIMARY）**：唯一标识表中每一行的索引，不允许有空值。

2. **唯一索引（UNIQUE）**：索引列的值必须唯一，允许有空值，但空值不唯一。

3. **普通索引（INDEX）**：没有唯一性约束的索引，允许有相同值的重复索引。

4. **复合索引（INDEX）**：在表的多个列上创建的索引，只有查询条件中使用了创建索引时的第一个字段，索引才会被使用，遵循最左前缀规则。

5. **全文索引（FULLTEXT）**：专门用于全文搜索的索引，可以有效地搜索文本中的关键词。

6. **空间索引（SPATIAL）**：用于地理空间数据类型，支持空间数据的索引和查询。

7. **覆盖索引（Covering Index）**：一个索引包含所有需要查询的字段的值，查询不需要回表查询其他列。

8. **聚簇索引（Clustered）**：存储引擎层面的索引，索引结构中包含非索引列的数据，通常主键自动创建聚簇索引（InnoDB）。聚簇索引提高了IO密集型应用的性能。

9. **非聚簇索引（Non-Clustered）**：数据文件和索引文件分开存放，索引中不包含非索引列的数据。

10. **哈希索引（HASH）**：基于哈希表的索引，通常用于精确匹配，不支持范围查询。

11. **索引视图（Index on View）**：在视图上创建的索引，可以加速视图查询。

12. **外键索引**：在有外键约束的列上自动创建的索引，用于维护数据的引用完整性。

13. **辅助索引（Secondary Index）**：除了主键索引外的其他索引，用于加速查询。

14. **隐藏索引（Hidden Index）**：MySQL 8.0引入的新特性，允许用户隐藏索引，隐藏的索引不会被优化器考虑使用。

15. **虚拟索引（Virtual Index）**：基于函数的索引，MySQL 8.0引入的新特性，允许索引函数的结果。

每种索引类型都有其特定的使用场景和优化策略，选择合适的索引类型可以显著提高查询性能。索引虽然可以提高查询速度，但同时也会增加写操作的负担，因此需要根据实际业务需求和数据访问模式来设计索引。

查看表上用了哪些索引

SHOW INDEX FROM `table_name` FROM `database_name`;

主要就是复合索引

复合索引（也称为多列索引或组合索引）是数据库中的一种索引类型，它基于表中两个或更多列的组合来创建。使用复合索引可以提高查询性能，尤其是在需要同时根据多个列进行搜索或排序的场景中。

以下是使用复合索引的一些关键点：

1. **创建复合索引**：你可以使用`CREATE INDEX`语句来创建复合索引。例如，如果你有一个`employees`表，包含`department_id`和`job_title`两个列，可以这样创建复合索引：

   ```sql
   CREATE INDEX idx_department_job ON employees(department_id, job_title);
   ```

2. **最左前缀规则**：在使用复合索引时，必须遵守最左前缀规则。这意味着查询条件中必须包含索引的第一个列，才能有效使用索引。例如，如果你创建了`idx_department_job`索引，以下查询将有效使用索引：

   ```sql
   SELECT * FROM employees WHERE department_id = 10;
   ```

   但是，如果你只指定了第二个列作为查询条件，如下所示，它将不会使用索引：

   ```sql
   SELECT * FROM employees WHERE job_title = 'Engineer';
   ```

3. **使用多个列**：要使用复合索引的多个列，查询条件必须包含索引中的第一个列和随后的列。例如：

   ```sql
   SELECT * FROM employees WHERE department_id = 10 AND job_title = 'Engineer';
   ```

   这个查询将有效使用`idx_department_job`索引。

4. **索引的选择性**：复合索引的选择性是指索引列中不同值的分布情况。具有高选择性的索引（即列值重复率低）通常更有效。

5. **排序和范围查询**：复合索引也可以用于排序和范围查询。例如，如果你对`department_id`和`job_title`进行排序，索引可以加速这个过程：

   ```sql
   SELECT * FROM employees ORDER BY department_id, job_title;
   ```

6. **覆盖索引**：全值匹配，如果复合索引包含所有查询的列，它就是一个覆盖索引，这可以提高查询效率，因为MySQL可以直接从索引中获取所需的数据，而不需要回表查询。

7. **维护成本**：虽然复合索引可以提高查询性能，但它们也会增加插入、删除和更新操作的维护成本。因此，应该根据实际的查询模式和性能测试来决定是否创建复合索引。

8. **索引列的顺序**：在创建复合索引时，应该根据列在查询条件中出现的频率和选择性来确定列的顺序。最常用于搜索条件的列应该放在索引的前面。

通过合理地使用复合索引，你可以显著提高数据库查询的性能，尤其是在处理具有多个搜索条件的复杂查询时。

索引失效

MySQL索引失效可能发生在多种情况下，以下是一些常见的导致索引失效的原因：

1. **索引列参与计算**：对索引列进行运算或使用函数，如`age + 1 = 20`，会导致索引失效 。

2. **使用函数或表达式**：查询中对索引列使用函数或表达式，例如`DATE(column) = '2024-01-01'`，会使索引无法生效。

3. **查询中使用了OR条件**：如果OR两边有范围查询，如`WHERE a < 10 OR b > 20`，可能导致索引失效。

4. **LIKE操作**：以通配符%开头的LIKE查询，如`LIKE '%keyword'`，会使索引失效。

5. **不等于比较**：使用`!=`或`<>`操作符进行比较时，可能导致索引失效。

6. **ORDER BY操作**：如果`ORDER BY`子句中的列不是索引列，或者与索引列顺序不一致，可能导致索引失效。

7. **使用IN**：当`IN`子句的取值范围较大时，可能会导致索引失效。

8. **索引选择性低**：如果索引的选择性（即不重复的索引值与数据表记录总数的比值）太低，可能导致索引失效 。

9. **数据分布不均匀**：数据在索引列上的分布极不均匀，可能导致索引失效。

10. **索引前缀不匹配**：在使用复合索引时，没有遵循最左前缀规则，可能导致索引失效 。

11. **索引统计信息不准确**：如果MySQL的索引统计信息不准确，可能导致优化器选择错误的索引或不使用索引。

为了避免索引失效，应该避免上述情况，并根据具体的查询需求和数据模式合理设计索引。同时，定期更新统计信息，以及使用`EXPLAIN`等工具来分析查询计划，都是确保索引有效使用的重要措施。

8. **数据库结构优化**：优化表结构，如使用合适的数据类型、合理的字段长度、使用UNSIGNED表示非负整数等；表拆分，包括垂直拆分和水平拆分；实现读写分离和数据库集群以提高性能和扩展性。

9. **硬件优化**：考虑增加服务器内存、使用SSD硬盘、配置多处理器等，以提高MySQL的运行速度和效率。

10. **缓存优化**：合理利用MySQL的缓存机制，如查询缓存、InnoDB缓冲池等，以及考虑使用外部缓存系统如Redis 。

11. **Linux系统优化**：包括避免使用Swap交换分区、将操作系统和数据分区分开、使用硬件磁盘阵列、调整文件系统挂载选项等。

12. **排序优化**：尽量避免在大量数据上进行排序操作，如果不可避免，尽量使用索引排序，减少内存或磁盘的使用。

通过这些优化措施，可以显著提升MySQL数据库的性能和响应速度。数据库优化是一个持续的过程，需要定期进行性能评估和调优。

在MySQL中对一张表进行大量数据插入时，可以通过以下方面来优化插入速度：

1. **关闭自动提交**：
- 通过设置 `SET autocommit=0;` 来关闭自动提交，可以减少事务的提交次数，提高插入效率。

2. **使用批量插入**：
- 尽量使用 `INSERT INTO ... VALUES (...),(...),...` 的形式进行批量插入，减少单独插入语句的数量。

3. **减少索引**：
- 在插入大量数据前，考虑暂时禁用或删除非聚簇索引，以减少索引维护的开销。插入完成后再重新创建索引。

4. **使用事务**：
- 将多个插入操作包含在一个事务中，通过 `BEGIN;` 开启事务，最后通过 `COMMIT;` 提交事务。

5. **选择合适的存储引擎**：
- 有些存储引擎在处理大量插入时性能更优，例如 MyISAM 引擎在写入大量数据时通常比 InnoDB 快。

6. **调整插入顺序**：
- 按照索引列的顺序插入数据，以减少索引的重新构建次数。

7. **使用延迟插入**：
- 如果适用，可以使用 `DELAYED` 关键字来插入数据，MySQL 会将这些插入操作缓存起来，当系统负载较低时再执行。

8. **优化表结构**：
- 减少表的列数，特别是索引列，可以减少每次插入时的写入量。
- 使用合适的数据类型，避免冗余和过度的精度。

9. **关闭外键约束**：
- 如果表中有外键约束，临时关闭外键约束可以提高插入速度。

10. **使用分区表**：
- 如果数据可以逻辑上分组，使用分区表可以提高插入效率。

11. **调整系统配置**：
- 根据服务器的硬件配置，调整 MySQL 的配置参数，如 `innodb_buffer_pool_size`、`innodb_log_file_size` 等。

12. **减少锁的竞争**：
- 尽量避免在高并发环境下进行大量数据插入，或者使用更细粒度的锁。

13. **使用多线程或多进程**：
- 如果服务器资源充足，可以使用多线程或多进程并行插入数据。

14. **优化网络延迟**：
- 如果数据来自远程服务器，优化网络延迟可以提高整体插入速度。

15. **清理空间碎片**：
- 在插入前，使用 `OPTIMIZE TABLE` 命令整理表碎片，为新数据预留空间。

16. **使用临时表**：
- 先将数据插入到临时表，然后再将临时表的数据导入目标表。

17. **监控系统负载**：
- 监控服务器的 CPU、内存、磁盘 I/O 等资源使用情况，确保服务器在最佳状态下运行。

通过上述方法，可以有效地提高大量数据插入的性能。不过，具体使用哪些方法还需要根据实际的业务场景和数据特点来决定。

在进行大量数据插入时，确保数据的完整性和避免违反唯一性约束是重要的，但如果你确定插入的数据不会违反任何唯一性约束（例如主键或唯一索引），并且你想要提高插入性能，可以考虑以下方法：

1. **使用`INSERT IGNORE`或`REPLACE INTO`**：
   - `INSERT IGNORE`在遇到唯一性冲突时忽略当前行，继续执行后续的插入操作。
   - `REPLACE INTO`在遇到唯一性冲突时先删除冲突的行，再执行插入。

2. **关闭外键约束**：
   - 如果表中有外键约束，并且这些约束在插入过程中不是必需的，可以考虑临时关闭外键约束（如果你使用的存储引擎支持这样做），以减少外键检查的开销。

3. **调整唯一性检查的级别**：
   - 对于某些存储引擎，如InnoDB，可以通过设置 `foreign_key_checks` 系统变量来关闭外键约束检查。例如：
     ```sql
     SET foreign_key_checks = 0;
     ```
   - 请注意，这种方法只影响当前会话或接下来的语句，直到重新设置为1。

4. **使用`ALTER TABLE ... DISABLE KEYS`**：
   - 对于MyISAM表或支持此语法的表，可以在插入前禁用键（即索引），然后插入数据，最后再启用键：
     ```sql
     ALTER TABLE your_table DISABLE KEYS;
     -- 执行插入操作
     ALTER TABLE your_table ENABLE KEYS;
     ```
   - 对于InnoDB表，可以使用 `ALTER TABLE ... ORDER BY ...` 的方式来优化插入顺序，但这不会禁用唯一性检查。

5. **选择合适的事务隔离级别**：
   - 降低事务隔离级别可能会减少锁的竞争，但这也可能导致其他问题，如脏读或幻读。

6. **使用批量插入**：
   - 批量插入可以减少磁盘I/O次数，提高插入效率。

7. **调整锁策略**：
   - 对于InnoDB表，使用 `INSERT ... ON DUPLICATE KEY UPDATE` 可以避免插入重复键时的锁等待。

请注意，关闭唯一性检查或外键约束可能会带来数据完整性的风险，因此这些操作应该谨慎使用，并且只在你完全了解可能的后果时使用。在进行大量插入操作后，应该重新启用所有约束，并验证数据的完整性。

对于mysql数据库表结构的优化

MySQL数据库表结构优化是确保数据库性能和可扩展性的关键步骤。以下是一些在优化表结构时应注意的事项：

1. **合理的数据类型**：
- 为每列选择最合适的数据类型，避免使用过大或过小的数据类型，这有助于减少存储空间并提高查询效率。

2. **避免冗余列**：
- 减少冗余数据，避免存储重复信息。冗余数据不仅浪费空间，还可能导致数据更新时的一致性问题。

3. **使用索引**：
- 为常用于搜索、排序和连接的列添加索引。同时，避免过度索引，因为索引虽然可以加快查询速度，但也会增加插入、删除和更新操作的负担。

4. **主键选择**：
- 选择一个合适的主键，最好是业务上具有唯一性特点的列。如果自然主键不存在，可以考虑使用自增ID或UUID作为主键。

5. **外键约束**：
- 使用外键维护数据的引用完整性，但要注意外键约束可能会影响查询性能。

6. **数据完整性**：
- 使用`NOT NULL`、`UNIQUE`、`PRIMARY KEY`和`CHECK`等约束来保证数据的准确性和完整性。

7. **表分区**：
- 对于大型表，考虑使用分区来提高查询和维护的效率。

8. **规范化设计**：
- 通过规范化（如第三范式）减少数据冗余，但也要注意避免过度规范化导致的查询性能问题。

9. **反规范化**：
- 在某些情况下，适当的反规范化可以减少复杂的多表连接，提高查询性能。

10. **使用合适的存储引擎**：
- 根据业务需求选择合适的存储引擎，例如InnoDB用于支持事务处理，MyISAM用于读取密集型的应用。