MySQL数据库优化技巧有哪些实用方法？

提升查询效率的核心

索引是MySQL优化中最基础也是最重要的手段,合理使用索引能显著减少数据扫描行数，将查询从全表扫描变为索引扫描，从而大幅提升性能。

1 为合适的字段创建索引
并非所有字段都需要索引，通常建议为经常出现在WHERE、JOIN、ORDER BY子句中的字段建立索引，用户表的手机号、邮箱字段，订单表的订单ID、用户ID字段等，但需注意，索引会占用额外存储空间，且降低写操作（INSERT、UPDATE、DELETE）速度，因此需权衡读写比例。

2 避免索引失效的场景

• 对索引字段进行计算或函数操作：如WHERE YEAR(create_time) = 2023会导致索引失效，应改为create_time >= '2023-01-01' AND create_time < '2024-01-01'。
• 使用!=或<>操作符：某些情况下会导致索引失效，建议用范围查询替代。
• 左前导模糊查询：LIKE '%abc'无法使用索引，而LIKE 'abc%'可以利用索引。

3 复合索引的最左前缀原则
复合索引（多列索引）遵循“最左前缀”原则，即查询条件必须包含索引的最左列，索引为(a, b, c)，则查询条件包含a或a, b或a, b, c时可用到索引，但仅包含b或c则无法使用，复合索引的列顺序需根据查询场景精心设计。

查询优化：减少资源消耗的关键

慢查询是数据库性能的常见瓶颈,通过优化SQL语句可显著降低CPU和I/O消耗。

**2.1 避免SELECT **
`SELECT 会读取所有列数据，增加网络传输和内存消耗，且可能导致索引失效，应明确指定所需列名，如SELECT id, name FROM users`。

2 使用JOIN替代子查询
子查询（尤其是嵌套子查询）可能临时生成中间表，影响性能，在适当情况下，用JOIN替代子查询可提升效率。

-- 子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);
-- JOIN优化
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 1;

3 优化分页查询
对于深度分页（如LIMIT 100000, 10），MySQL需扫描前100010行数据，性能极差，可采用“延迟关联”优化：

SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp ON o.id = tmp.id;

表结构优化：奠定高效存储的基础

合理的表结构设计能减少数据冗余,提升查询和更新效率。

1 选择合适的存储引擎

• InnoDB：支持事务、行级锁，适合高并发、事务性强的场景（如电商、金融系统），是MySQL 5.7后的默认引擎。
• MyISAM：不支持事务，表级锁，适合读多写少的场景（如报表系统），但已逐渐被InnoDB取代。

2 合理设计字段类型

• 优先使用小类型：如用INT而非BIGINT，VARCHAR(50)而非VARCHAR(255)，减少存储空间占用。
• 避免NULL值：NULL值会增加存储和计算复杂度，尽量设置默认值（如DEFAULT 0）。
• 使用整数代替字符串：如用TINYINT表示性别（0=女，1=男），而非字符串’female’/’male’。

3 垂直拆分与水平拆分

• 垂直拆分：将大表按业务拆分为小表，如用户表拆分为用户基本信息表、用户扩展信息表，减少单表字段数量。
• 水平拆分：按某种规则（如时间、用户ID哈希）将大表拆分为多个小表，如订单表按月份拆分为orders_202301、orders_202302等，分散数据压力。

配置与维护：保障数据库稳定运行

合理的配置和定期维护是数据库长期稳定运行的保障。

1 调整关键参数

• innodb_buffer_pool_size：InnoDB缓冲池大小，建议设置为物理内存的50%-70，用于缓存数据和索引，减少磁盘I/O。
• max_connections：最大连接数，根据业务并发量调整，避免因连接过多导致服务拒绝。
• query_cache：查询缓存（MySQL 8.0已移除），在低并发、读密集场景可提升性能，但高并发时反而会增加锁竞争。

2 定期维护

• 优化表：使用OPTIMIZE TABLE清理碎片，适用于频繁更新的表（如日志表）。
• 分析表：通过ANALYZE TABLE更新索引统计信息，帮助优化器选择更优执行计划。
• 定期备份：结合全量备份（如mysqldump）和增量备份（如binlog），制定灾备恢复策略。

监控与诊断：及时发现性能瓶颈

通过监控工具和慢查询日志,可主动发现并解决性能问题。

1 开启慢查询日志
配置slow_query_log = ON和long_query_time = 1（记录执行超过1秒的查询），通过mysqldumpslow或pt-query-digest工具分析慢查询，针对性优化。

2 使用性能_schema
performance_schema是MySQL内置的性能监控工具，可实时监控线程、锁、索引使用等情况，帮助定位资源瓶颈。

3 第三方工具

• Prometheus + Grafana：搭建数据库监控大盘，实时展示QPS、TPS、连接数等指标。
• Percona Toolkit：包含pt-online-schema-change（在线变更表结构）、pt-index-usage（分析索引使用情况）等实用工具。

MySQL优化是一个系统工程,需从索引、查询、表结构、配置、维护等多维度综合考量，核心原则是“具体问题具体分析”：通过慢查询定位瓶颈，结合业务场景选择合适的优化手段，并持续监控和调整，最终目标是在保证数据一致性和业务稳定性的前提下，最大化数据库性能。