Mysql性能优化

最新推荐文章于 2025-05-28 01:55:06 发布

繁嬅丶落尽

最新推荐文章于 2025-05-28 01:55:06 发布

阅读量1.1k

点赞数 27

分类专栏： sql 文章标签： mysql 性能优化

本文链接：https://blog.youkuaiyun.com/qq_43555338/article/details/137051286

版权

sql 专栏收录该内容

3 篇文章

订阅专栏

本文探讨了MySQL中SQL查询变慢的原因，包括缺乏索引、服务器资源限制等，并提供了EXPLAIN和PROFILE工具的使用方法。重点介绍了优化JOIN、ORDERBY、GROUPBY、LIMIT等操作的策略，以及一些特殊情况下的优化技巧和注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于MySQL本身的限制，并非所有的SQL都能得到很好的优化，实际业务场景中可能要辅以其他技术方案甚至业务妥协来实现当前业务的优化。
但就其开发成本而言，SQL优化是首选考虑的优化方向。

SQL查询为什么慢？

从较为宏观的视角可能有以下原因：

SQL压根没建立索引
SQL索引性能不好
SQL尽管建立了索引，但是没走索引
SQL使用了磁盘临时表
其他业务锁表导致当前SQL查询等待
MySQL选错索引
…

从服务器资源的视角可能有以下原因：

伴随SQL执行，CPU资源消耗100%
伴随sQL执行，内存资源不够用，需要走磁盘l/o
原服务器本身就繁忙，分配不到资源
硬件资源本身就不足
…

SQL查询慢在哪里？

MySQL官方提供以下两种工具让我们来排查SQL究竟慢在哪里

profile
explain [extended]

在这里插入图片描述

explain [extended]的几个重要参数

type：判断SQL好坏的性能指标，从最优到最差依次︰null > system > const > eq_ref > ref >range > index > ALL。一般来说，要保证查询达到range级别，最好达到ref ;
key：判断SQL是否使用索引，使用了什么索引;
key_len：索引长度，主要受数据类型影响，长度越短，索引性能越好;
rows：表明优化器大概要扫描的行数，值越小性能越好;
extra：提供辅助优化信息，告诉我们应该从哪个方向优化;

SQL优化思路参考

1、优化JOIN连接查询

1.1、常见的优化点有：

关联字段建立索引（被驱动表建索引即可）
以小表驱动大表（不用过多关注, MySQL一般情况会自动选择)
合理设置join_buffer_size

1.2、JOIN SQL执行通常为以下执行步骤：

将驱动表t1所有数据（N行）加载到join_buffer内存中（如果join_buffer_size大小足够装下驱动表数据）
从t1中读入一行数据，取出关联字段到被驱动表t2 （M行）遍历查找（如果存在索引，树级搜索）符合条件的数据作为结果集的数据;
重复第二步,直至完成t1的遍历

1.3、从上面的执行过程可以看到：

增大join_buffer_size，可以将驱动表所有数据装载进内存，不会使用磁盘临时表，可提升性能;
关联字段建立索引，可以让被驱动表以树级复杂度进行搜索，提升遍历性能;
整体执行时间复杂度接近N *logM，以小表（N行）驱动大表可以提升性能;

2、优化子查询

2.1、IN型子查询优化（使用覆盖索引）
在这里插入图片描述
2.2、JOIN型子查询优化

2.3、FORM型子查询优化

3、优化ORDER BY排序

优化order by主要是优化using filesort，使其可以利用索引排序。其优化手段之一为建立排序可用的联合索引。
要建立可用索引，我们需要知道哪些情况下索引可以生效和不生效，考虑以下情况︰
在这里插入图片描述
合理设置ORDER BY相关配置参数

增大max_length_for_sort_data（线程级）
增大sort_buffer_size（线程级）

order by的另一优化方向是使用驱动表字段代替被驱动表字段,当然这是以语义相同为前提的。
在这里插入图片描述

4、优化GROUPBY分组

前面所讲的优化ORDER BY的方法同样适用于优化GROUP BY，这里不再重复。

GROUP BY的另一个优化点是∶如果不需要关心结果集的排序，可以添加ORDER BY NULL来消除文件排序（当没有通过order by指定排序列, group by会隐式按照分组的字段进行排序)
在这里插入图片描述
当GROUP BY无法使用索引时，MySQL会使用临时表或文件排序处理分组。对于这种查询语句可以通过使用hint提示SQL_BIG_RESULT/SQL_SMALL_RESULT来优化语句的性能。

SQL_BIG_RESUL会提示优化器结果集很大，直接使用磁盘临时表排序，没必要先走内存表发现空间不够再走磁盘；
SQL_SMALL_RESULT提示优化器结果集很小，可以直接使用内存临时表排序。

5、优化Limit分页

Limit分页的性能问题一般分为返回数据过大和偏移量大两种场景︰

对于返回数据过大的场景，缩小返回的行数即可；
对于偏移量大的场景，因为limit的实现机制是limit a,b 会先扫描a+b条数据，再截取[a,b)数据返回，所以最好能从业务上能控制分页的深度，如果业务不允许，则考虑是否可以使用延迟加载或辅助定位方法。

延迟加载是指构建一个可使用覆盖索引的子查询，通过覆盖索引先查出索引列数据，再与自身关联返回需要的数据。

分页的深度越深．延迟关联相比于正常分页的性能就更明显。但是这种优化仅适用于单表查询。
在这里插入图片描述
辅助定位是指将limit M,N查询转换为已知位置的查询(where id >= x limit N + 1)，每次分页查询时，从上一次分页中取出id最大值x，构建id >x条件。

6、优化union合并

Mysql总是通过创建并填充临时表的方式来执行union子查询，导致很多优化策略无法使用,经常需要手工将where、limit、order by等子句下推到union的各个子查询，以便优化器可用充分利用这些条件进行优化。

尽可能使用union all代替union，如果确实需要过滤重复行，请考虑代码实现过滤。

7、优化Where子句

where子句的优化关键在于如何建立合适索引对数据进行过滤。

关联查询的where子句只能使用驱动表索引，如where t1.a = " and t2.b="只能使用驱动表a的索引；
根据区分度建立联合索引；
*对于区分度都不高的多列,考虑高频使用的列放前面；
*对于区分度高的多列，考虑区分度高的列放前面；
结合order by列/group by列建立联合索引，如where t1.a= " order by b，考虑建立索引idx_a_b；
范围查询列放后面(范围查询列后面的列无法再使用索引)，如where t1.a= " and t1.b in(“) and t1.c=”，考虑建立索引idx_a_b或idx_c_b；
考虑根据枚举值拆分查询语句，并发查询提高性能；

8、优化count查询

五百万数据 mysql count（*）优化
在 InnoDB 存储引擎中，count(*) 函数是先从内存中读取数据到内存缓冲区，然后进行扫描获得行记录数。这里 InnoDB 会优先走二级索引；如果同时存在多个二级索引，会选择key_len 最小的二级索引；如果不存在二级索引，那么会走主键索引；如果连主键都不存在，那么就走全表扫描！