《MySQL DBA 修炼之道》第五章查询优化

最新推荐文章于 2024-10-01 17:33:05 发布

程序媛-北北

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量951

点赞数 40

文章标签： mysql dba 数据库

本文链接：https://blog.csdn.net/Trishia/article/details/140693107

版权

《MySQL DBA 修炼之道》原文是在第六章查询优化，博主觉得比较重要，所以想提前整理为一篇博文。

查询优化是研发人员比较关注也是疑问最多的领域。

基础知识

1. 查询优化的常用策略

一般的常用策略优化数据访问、重写SQL、重新设计表、添加索引 4种。

（1）优化数据访问

应该尽量减少对数据的访问。一般有两个需要考虑的地方：应用程序减少对数据库的访问，数据库减少扫描的记录数。
例如：如果应用程序可以缓存数据，就可以不需要从数据库中直接读取数据。
例如：如果应用程序只需要几个列的数据，就没必要把所有列的数据全部读取出来，应该尽量避免 SELECT * FROM table_name语句。

慢查询日志中，如果Rows_examined这项值很高，实际上并不需要扫描大量的数据，这种情况下添加索引或者增加筛选条件都可以极大地减少记录扫描的行数。

（2）重写SQL
由于复杂查询严重降低并发性，因此为了让程序更加灵活与迅速，我们可以把复杂的查询分解为多个简单的查询。一般来讲，多个简单查询的总成本是小于一个复杂查询的。
对于需要进行大量数据的操作，可以分批执行，可以减少对生产系统产生的影响，从而缓解复制超时。

MySQL JOIN会严重降低并发性，对于高并发，高性能的服务，应该尽量避免连接太多表，如果可以，尽量在应用层就对影响性能的SQL实现分解。这样可以更加方便的缓存数据，更方便的迁移数据，扩展性好。

（3）重新设计库表
有些情况下，我们即使是重新SQL或添加索引也解决不了问题，这时候可能考虑更改表结构的设计。比如：增加缓存表，暂存统计数据，或者增加冗余列，以减少连接。优化的方向是反范式设计。（见上一章节）

（4）添加索引
生产环境中的问题，可能80%都是索引问题，所以优化好索引，就有了一个好的开始。

2. 各种语句的优化方案

（1）连接的优化（JOIN）

MySQL使用的是“Nested Loop Join”嵌套连接。
在这里插入图片描述
我们称tbl1为外部表/驱动表，tbl2为内部表。这种算法的成本与外部表与内部表的乘机是成正比的。如果嵌套的层次比较多，也就是说连接了很多表，那么成本是很昂贵的。MySQL一般会选择小表做驱动表，为了减少连接嵌套循环的连接次数，而且内部表一般在连接列上有索引，索引一般常驻在内存，查询速度能快点。

由于连接的成本比较高，因此对于高并发的应用应该尽量减少连接的查询，连接表不能太多，最好不要超过4个。可以考虑反范式设计减少表的连接，或者考虑应用层进行连接。
1）最好能将LEFT JOIN转变为INNER JOIN。
2) 使用EXPLAIN检查连接，留意ROWS列，值太高就要考虑优化索引或连接。
3）反范式设计，适当的反范式设计可以减少连接次数，
4）考虑应用层实现连接，将复杂的查询在业务层转化为简单的多个查询，还便于经过缓存存取数据。

（2）GROUP BY、DISTINCT、ORDER BY语句优化

尽量对较少的行进行排序
如果连接了多张表，ORDER BY、GROUP BY的列应该属于连接列的驱动表
利用索引排序。
要保证索引列和ORDER BY的列相同，且各列均按照相同的方式进行排序。
增加sort_buffer_size
sort_buffer_size是为每个排序线程分配的缓冲区大小，增加该值可以加快ORDER BY与GROUP BY操作。
增加 read_rnd_buffer_size
当按照排序后的顺序读取行时，通过该缓冲区读取行，从而避免搜索硬盘。将该变量设置为较大值，可以大大改进ORDER BY的性能。

sort_buffer_size，read_rnd_buffer_size 只需要为运行大查询的客户端更改绘画变量即可。不应该修改全局变量。

（3）优化子查询

由于子查询可读性比较好，所以有些研发人员习惯于编写子查询，特别是刚接触数据库编程的新手。但子查询往往也是性能杀手，在生产环境中，子查询是最常见的导致性能问题的症结所在。
对于数据库来说，在绝大部分情况下，连接会比子查询更快。使用连接的方式，MySQL优化器一般可以生成更佳的执行计划，可以预先装载数据，更高效地处理数据查询。而子查询往往需要运行重复的查询，子查询生成的临时表也没有索引，因此效率会更低。

矮子里面找大个，大部分情况，子查询还不如连接查询呢。

（4）优化Limit

Web应用经常需要对查询的结果进行分页，分页算法offset一旦值很大，效率就会很差，因为MySQL必须检索大量的记录，然后丢弃大部分数据（offset+row_count)。
1）限制页数，只显示前几页，超过了一定页数之后，直接显示“更多more”，对于N页之后的结果，用户一般也不会关心。
2）要避免设置offset值，也就是避免丢弃数据。

-- 通过增加定位列 id > 1000 可以避免设置offset值
SELECT id,name,address,phone
FROM customer
WHERE id > 1000
ORDER BY id LIMIT 10;

也可以使用条件限制要排序的结果集：

WHERE date_time BETWEEN '2024-04-01 00:00:00' AND '2024-07-01 00:00:00' ORDER BY id;

(5) 优化IN列

对于IN列表，MySQL会排序IN列表里面的值，并使用二分查找法的方式定位数据。
但是把IN改成OR的形式并不能提高执行效率。建议IN中列值不超过200个，对于高并发的业务，小于几十条最佳。
如果能将其转化为多个等于的查询，那么这种方式最佳。

SELECT * FROM tableA WHERE id IN(SELECT id FROM tableB);

我们可以先查询(SELECT id FROM tableB)，把获取到值逐个拼接，转化为SELECT * FROM tableA WHERE id = ?的形式，这个操作用程序实现很容易。

（6）优化Union

UNION语句默认是移出重复记录，结果集很大，还要全表扫描再去重，成本会很高。所以尽量使用UNION ALL语句。对于多个UNION数据库分表的时候，就应该保证数据是唯一的，这样就无需UNION去重。
另外，查询语句外层的WHERE并不会应用到每个单独的UNION，应该在每个UNION字句中添加上WHERE条件。