MySQL之性能调优

最新推荐文章于 2025-03-08 09:00:00 发布

柠檬茶五元

最新推荐文章于 2025-03-08 09:00:00 发布

阅读量1.2k

点赞数 35

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_60469045/article/details/139619371

版权

想要发挥 MySQL 的最佳性能，需要遵循 3 个基本使用原则。

让MySQL回归存储的基本职能：MySQL 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离；
查询数据时，尽量单表查询，减少跨库查询和多表关联；
杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。
- 大事务：运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。
- 大 SQL：复杂的SQL意味着过多的表的关联，MySQL 数据库处理关联超过3张表以上的SQL时，占用资源多，性能低下。
- 大批量：多条SQL一次性执行完成，可以减少一条条执行SQL产生的额外开销，但必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。
- 大字段：blob、text类型的大字段要尽量少用，必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新。

1.使用Explain进行分析：

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：

select_type：查询类型（简单查询、联合查询、子查询）
key:使用的索引
rows：扫描的行数

2.优化数据访问：

1.减少请求的数据量：

只返回必要的列：最好不要使用SELECT*语句。
只返回必要的行：使用LIMIT语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行重复查询。

2.减少服务器端扫描的行数：使用索引覆盖来查询。

3.重构查询方式：

1.切分大查询：

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

#大查询
DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

#切分块
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2.分解大连接查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有:

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
减少锁竞争；
在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

#大连接查询
SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

#分解后
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

4.为搜索字段建立索引：

索引并不一定就是给主键或唯一的字段。如果在表中，有某个字段经常用来搜索，就需要加入索引。

5.尽可能使用Enum而不是varchar：

节省存储空间:Enum字段只需要占用1或2个字节的存储空间,而Varchar字段需要根据实际数据长度来分配存储空间。对于固定长度的数据,Enum更加节省存储。
提高查询效率:Enum字段的值是预定义的,查询时只需要比较整数值,效率更高。Varchar需要字符串比较,相对来说查询速度会慢一些。
数据校验:Enum字段的值受限于预定义的集合,可以有效避免输入无效数据。Varchar没有这种内置的校验机制。
索引优化:Enum字段的索引更小、更紧凑,可以提高索引的性能。Varchar索引由于存储的是字符串,相对更占用存储空间。