彻底弄懂MySQL的优化

最新推荐文章于 2025-04-16 05:00:00 发布

小胖子——鑫

最新推荐文章于 2025-04-16 05:00:00 发布

阅读量1k

点赞数 21

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq_34798605/article/details/146731448

版权

一、背景

在我们的工作中，我们与数据库打交道的频率非常大，尤其是业务系统，MySQL数据库的应用时刻相关，所以如果更好的利用好我们的MySQL数据库，它直接影响系统的响应速度、可扩展性和整体性能。在企业级 Web 开发中，MySQL优化是至关重要的。下面从不同角度，列出详细的 MySQL 优化技巧，涵盖查询优化、索引设计、表结构设计、配置调整等方面，以及穿插一些案例分享

二、查询SQL语句方面的优化

1. 合理使用索引

单列索引：为查询频繁的字段（如 WHERE、ORDER BY、GROUP BY 中的字段）创建单列索引，这是一种最简单的方式，也是我们最常用的一种方式
组合索引：对于涉及多列条件的查询，建议使用组合索引。注意组合索引的顺序（最左前缀匹配原则）。
覆盖索引：确保查询的字段全部被索引覆盖，这样 MySQL 可以直接从索引中获取数据，而无需回表访问数据。
避免过度索引：过多的索引会增加写操作的开销，如 INSERT、UPDATE 和 DELETE 操作，因为每次都要维护索引。(这个其实也蛮关键的，我们在公司可能会遇到很多大表，索引非常多，其实性能也比较差，至于大表如何进行优化，我们可以后续再进行梳理)

2. 优化查询语句

避免使用 SELECT \*：明确选择需要的字段，避免多余的字段查询，减小数据传输量。是一种很常见的优化思路，优化效果如果是涉及字段非常多的表，优化效果立竿见影
避免在 WHERE 条件中对字段进行函数操作：如 WHERE YEAR(date_column) = 2023，这种操作会使索引失效，改为 WHERE date_column >= '2023-01-01' AND date_column < '2024-01-01'。
避免在 WHERE 条件中使用 OR：OR 会导致全表扫描，尽量使用 IN 或分解查询。
尽量减少子查询：使用 JOIN 替代子查询。子查询会在嵌套时频繁执行，每次可能都会导致重新扫描表。
合理使用 JOIN：如果有多表关联查询，确保关联的字段有索引，且表连接顺序要优化（小表驱动大表）。

3. 分页查询优化

大数据分页：对于数据量非常大的分页查询，可以避免 LIMIT offset 方式，而是通过索引定位起始位置，例如 WHERE id > last_page_id LIMIT 10。这个利用id来进行分页的思想，在我这边定时任务刷数的时候运用的比较多，性能也是非常好
减少数据扫描量：分页时不要 SELECT *，只选择主键字段返回结果后再根据主键查询详细信息。

4、合理使用临时表和缓存

复杂查询：对于复杂统计查询，可以先用定时任务进行提前计算，放入结果表，等用户点击查询的时候，只查询结果即可，性能会好很多。
缓存机制：在应用层或数据库层（如使用 Redis、Memcached）对频繁访问的数据做缓存，避免每次都查询数据库。

5. 避免死锁和锁等待

减少锁范围：尽量让锁的范围小（如只锁定必要的行），避免表锁的使用。具体的场景是：我们可以尽量使用id(主键)来进行数据的更新，不要使用其他字段作为更新的条件，避免锁定的行数过多
减少事务执行时间：事务越长，锁定的资源时间越长，容易导致锁等待甚至死锁。尽量减少事务中的查询或更新操作时间。具体实践案例：查询操作一律去掉事务，查询最新的数据即可，提高程序性能

二、索引优化

1. 主键和唯一索引的合理使用

主键索引：选择唯一且不变的字段作为主键，可以考虑分布式ID生成，尽量不用数据库自增作为主键。
唯一索引：在不允许重复值的字段上（如用户名、邮箱等）创建唯一索引，避免重复数据的插入。

2. 覆盖索引

减少回表操作：对于查询涉及的字段全部在索引中时，MySQL 可以直接通过索引返回结果，避免回表查询。

3. 前缀索引

长字符串字段的索引：对 VARCHAR 等长字符串类型字段建立索引时，可以使用前缀索引（如 CREATE INDEX idx_name ON users(name(10))），通过截取前几位字符来节省索引空间。

4. 避免冗余索引

避免重复索引：例如已经有 (a, b) 组合索引时，不需要再单独给 a 建索引。
索引维护：定期检查无用的索引（使用 SHOW INDEX FROM table_name）并删除，减少索引维护的开销。

三、表结构设计优化

1. 合理的表字段设计

数据类型选择：选择最小且足够的字段类型。比如 INT(11) 占用 4 字节，如果值范围较小，可以使用 TINYINT（1 字节）、SMALLINT（2 字节）来节省空间。
使用 VARCHAR 而非 CHAR：CHAR 为定长，存储固定长度字符会造成空间浪费，而 VARCHAR 为变长，适合存储不确定长度的字符串。
避免使用 BLOB 和 TEXT 类型：大字段会造成性能问题，尽量将大文件或大数据放在文件系统中，数据库中仅存储文件路径。

2. 表分区

水平分表：当表数据量过大（如上亿条记录）时，可以将表进行水平拆分，比如按照时间、用户ID等进行分表，减小单个表的大小。
分区表：MySQL 提供表分区功能，可以根据数据范围将数据划分到不同的物理分区，优化大表查询性能。

3. 表规范化和反规范化

表规范化：将数据分离到多个表中，避免数据冗余。数据量少时，范式化设计更易于维护。
反规范化：当查询性能成为瓶颈时，可以考虑反规范化，增加冗余字段减少表的关联查询。

四、事务和锁机制优化

1. 减少锁竞争

行锁优先：尽量避免使用锁范围更大的表锁，MySQL 的 InnoDB 引擎支持行锁，保证并发性。
分批提交：批量操作数据时，可以将操作拆分成多个小批次提交，减少长时间锁持有。

2. 合理使用事务

尽量减少事务时间：事务应尽可能短，避免长时间持有锁，导致资源被其他事务等待。
事务隔离级别选择：根据业务需求选择合适的隔离级别，较高的隔离级别如 SERIALIZABLE 会有更多的锁定开销，常用的是 REPEATABLE READ。

3. 使用乐观锁

应用层乐观锁：对于并发更新的业务场景，可以在应用层使用版本号控制（乐观锁）来避免锁冲突。但是性能会比较差，有其他分布式锁的条件下，尽量使用分布式锁，不建议使用乐观锁

五、配置优化（DBA应关注）

1. 调整 InnoDB Buffer Pool

Buffer Pool 的大小：InnoDB 的 Buffer Pool 用于缓存数据和索引，配置合理的缓存大小是优化 MySQL 性能的关键之一。建议 Buffer Pool 设置为物理内存的 70-80%。

innodb_buffer_pool_size = 4G # 根据内存大小调整

2. 查询缓存（Query Cache）

关闭查询缓存：在 MySQL 5.7 及以后的版本，查询缓存功能逐渐被弃用，因为它在高并发场景下容易成为瓶颈。因此，建议将其关闭。

query_cache_type = 0

3. 线程池优化

调整连接线程：对于高并发的业务场景，可以调整 MySQL 的最大连接数（max_connections）和每个连接线程的最大数量。

max_connections = 500

4. 磁盘 I/O 优化

调整 innodb_flush_log_at_trx_commit：innodb_flush_log_at_trx_commit 控制日志何时写入磁盘。设置为 2 时，可以降低磁盘 I/O，提升性能，但会稍微增加数据丢失的风险。

innodb_flush_log_at_trx_commit = 2