面试官：MySQL优化手段有哪些？

MySQL 是面试中必问的模块，而 MySQL 中的优化内容又是常见的面试题，所以本文来看“工作中优化MySQL的手段有哪些？”。

图片

工作中常见的 MySQL 优化手段分为以下五大类：

• 索引优化：确保高频查询字段有合适索引。
• SQ优化：减少全表扫描、避免不必要计算。
• 事务与锁优化：避免长事务、使用批量插入。
• 架构优化：数据量大时进行读写分离或分库分表。
• 硬件和配置优化：升级硬件和 MySQL 参数调优。

1.索引优化

索引优化包含以下内容：

• 高频字段需要创建索引：对于读多少写的场景，一定要创建正确的索引，避免全表扫描，提升查询效率。
• 避免索引失效：在有索引的前提下，确保索引不会失效，因此需要避免一些导致索引失效的场景，例如以下这些：

1. 联合索引未遵循最左匹配原则。
2. 使用列运行或内置函数导致索引失效。
3. like 查询未非前缀模糊查询。
4. 隐私类型转换等。

• 避免回表查询：如果查询只需要索引字段，避免回表，例如以下示例：

-- 原始查询（需回表）
SELECT * FROM orders WHERE user_id = 100;
-- 优化为覆盖索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
SELECT user_id, status FROM orders WHERE user_id = 100;

2.SQL优化

2.1 避免 SELECT *

只查询需要的字段，减少数据传输和内存占用：

-- 不推荐
SELECT * FROM products;
-- 推荐
SELECT id, name, price FROM products;

2.2 分页优化

大数据量分页时，避免 LIMIT 100000, 10，而是使用上次查询 ID 作为起始 ID 进行查询：

-- 原始分页（性能差）
SELECT * FROM logs ORDER BY id LIMIT 100000, 10;
-- 优化：使用游标分页（记录上一页最后一条的 id）
SELECT * FROM logs WHERE id>100000 ORDER BY id LIMIT 10;

2.3 JOIN 优化

• 确保关联字段有索引，使用小表驱动大表。

图片

例如以下示例：

-- 小表（emp）驱动大表（dept）
SELECT * FROM emp 
INNER JOIN dept ON emp.dept_id = dept.id;

3. 事务和锁优化

3.1 减少长事务

长事务会导致锁竞争和回滚段膨胀：

-- 不推荐：事务中包含耗时操作
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 执行其他耗时操作...
COMMIT;
-- 推荐：尽快提交事务

3.2 批量操作优化

使用批量插入代替逐条插入：

-- 不推荐
INSERT INTO logs (msg) VALUES ('a');
INSERT INTO logs (msg) VALUES ('b');
-- 推荐
INSERT INTO logs (msg) VALUES ('a'), ('b');

4.架构优化

数据量比较大时，可采取以下措施：

• 读写分离：采用数据库的读写分离架构，将读操作和写操作分布到不同的数据库服务器上。这样可以减轻主数据库的负载，提高读操作的性能。在查询每个年龄段的用户时，可以将查询请求发送到从数据库上，从而提高查询的并发处理能力和响应速度。

图片

• 分库分表：单表行数超过 500 万行或者单表容量超过 2GB，推荐进行分库分表。

图片

• 分布式数据库：使用大数据下性能更好的分布式数据库，例如 TiDB、ElasticSearch 等分布式数据提升性能。

5.硬件和配置优化

5.1 数据库配置优化

• 调整缓存参数：增大 innodb_buffer_pool_size（建议分配 50%-80% 可用内存）以提高读取性能。
• 优化日志参数：增大 innodb_log_file_size 和 innodb_log_buffer_size，减少磁盘写入。

5.2 提升硬件配置

• 使用 SSD 硬盘：提升 IO 操作性能。
• 增加内存容量：足够的内存可以减少磁盘 I/O 操作。
• 选择多核处理器：多核 CPU 可以更好地支持高并发查询，提升系统的整体吞吐量。
• 升级网络设备：使用高速网络接口卡可以提高数据传输速度。

小结

MySQL 常见的优化手段包含 5 大类，索引优化、SQL 优化、事务和锁优化、架构优化和硬件及配置优化。你还知道哪些优化手段呢？