MySQL 常见的数据库查询性能优化手段

落日映苍穹つ 2022-09-07 14:57 97阅读 0赞

> 简单介绍了一些常见MySQL数据库优化手段,比如减少数据访问、使用索引、使用关联查询等等。

### 文章目录 ###

*  1 优化数据访问
 *  2 重构查询
 *   *  2.1 优化子查询
     *  2.2 优化GROUP BY和DISTINCT
     *  2.3 优化LIMIT分页
     *  2.4 优化UNION

# 1 优化数据访问 #

**查询性能低下的最基本原因就是访问了太多数据，一些查询要不可避免地筛选大量的数据，大部分性能欠佳的查询都可以用减少数据访问的方式进行优化，这是最简单也是很有效的优化方式。**

1.  首先分析应用程序是否正在获取超过需要的数据，这通常表现在获取了过多的行或列。一些查询先向服务器请求不需要的数据，再丢掉他们，这个让服务器造成了额外的负担，增加了网络开销，消耗了内存和CPU资源。
    
    1.  只返回必要的列，通常不要使用`SELECT *`语句，只返回必要的行，通常使用 `LIMIT` 语句来限制返回的数据条数。
2.  其次确认MySQL是否在Server层检查了过多的数据，一般从查询的`执行时间、检查的行数、返回的行数`来看，但这些不可作为绝对的标准。
    
    1.  理想情况下，扫描的行数和返回的行数应该是相同的，使用一些优化手段如索引或者重构查询尽量去减少检查的行数，或者利用好**索引覆盖**减少回表次数。

**一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为:**

1.  在索引中使用WHERE条件来过滤不匹配的记录。这是存储引擎层直接完成的。
2.  使用索引覆盖扫描(在`Extra`列中出现了`Using index`)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
3.  从数据表中返回数据，然后过滤不满足条件的记录(在Extra列中出现`Using Where`)。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

# 2 重构查询 #

**有些时候我们需要重写查询以获取更好的性能，尽管得到的结果可能不同，也许最终程序的代码也会和查询一起被改。**

1.  把一个耗时的复杂查询分解成多个简单的查询。
2.  将大查询拆分为小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
    
    1.  **一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。比如定期的大量删除旧的数据，不应该在一个sql语句中删除全部数据，而是将一个大范围的delete语句切分为多个小范围的delete语句。**
3.  **分解联接**，把一个多表联接分解成多个单表查询，然后在应用程序端实现联接。这样做的好处有：
    
    1.  **让缓存效率更高**，许多应用可以缓存单表查询的结果，那么下次查询的时候可以减少这次查询。另外，对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
    2.  **执行单个查询可以减少锁的竞争。**
4.  **在应用层进行连接**，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
5.  **减少冗余记录查询**，分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

## 2.1 优化子查询 ##

**关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替，但这不是绝对的。**

`MySQL5.6`开始，引入物化子查询(针对where clause的subquery)，子查询物化将子查询结果存入临时表，确保子查询只执行一次，该表不记录重复数据且采用哈希索引查找，where后面的in()子查询不会被改写为exists子查询，因此可不做改写。

**子查询生成的临时表是没有索引的。**

## 2.2 优化GROUP BY和DISTINCT ##

**MySQL 经常用同样的方法来优化这两个查询，它们都会用索引来优化，这也是最有效的优化办法。**

当无法使用索引的时候，MySQL 会用临时表或者文件排序（filessort，但并不一定使用此磁盘文件）来执行 GROUP BY。

如果需要对关联查询做分组，那么通常采用标识列（比如id）来进行分组效率会比较高（如果允许）。

## 2.3 优化LIMIT分页 ##

进行分页操作时，通常都会通过Limit加上偏移量来查询某些数据，同时再加上适合的order by字句，如果有对应的索引，性能一般都不错。

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT1000,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10 000条记录都将被抛弃，这样的代价非常高。

如下案例：

select * from contacts ORDER BY phone LIMIT 40000,11;

**此时，最简单的一个优化的办法是尽可能利用到索引覆盖扫描，而不是一次性查询所有的列，然后根据需要做一次关联查询在返回所需要的列，这种方式称为延迟关联。**

select * from contacts INNER JOIN (select contact_id from contacts  ORDER BY phone LIMIT 40000,11) as xx USING(contact_id);

## 2.4 优化UNION ##

**MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将MHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询)。**

除非确实需要服务器消除重复的行，否则就一定要使用`UNION ALL`，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上`DISTINCT`选项，这会导致对整个临时我的数据做**唯一性检查**。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端)。

**参考资料：**

1.  《 MySQL 技术内幕: InnoDB 存储引擎》
2.  《高性能 MySQL》
3.  《MySQL实战45讲 | 极客时间 | 丁奇》

> 如有需要交流，或者文章有误，请直接留言。另外希望点赞、收藏、关注，我将不间断更新各种Java学习博客！