常见的MySQL优化方案1

骑猪看日落 2023-03-13 02:52 13阅读 0赞

## 前言：我们先了解一下sql性能优化 ##

性能优化（Optimize）指的是在保证系统正确性的前提下，能够更快速响应请求的一种手段。而且有些性能问题，比如慢查询等，如果积累到一定的程度或者是遇到急速上升的并发请求之后，会导致严重的后果，轻则造成服务繁忙，重则导致应用不可用。它对我们来说就像一颗即将被引爆的定时炸弹一样，时刻威胁着我们。因此在上线项目之前需要严格的把关，以确保 MySQL 能够以最优的状态进行运行。

## MySQL 的优化方案有哪些？ ##

MySQL 数据库常见的优化手段分为三个层面：SQL 和索引优化、数据库结构优化、系统硬件优化等，然而每个大的方向中又包含多个小的优化点，下面我们具体来看看。

## 优化注意事项： ##

依据数据而不是凭空猜测
    
    忌过早优化
    
    忌过度优化
    
    深入理解业务
    
    性能优化是持久战
    
    选择合适的衡量指标、测试用例、测试环境

## **优化目标** ##

**（1）减少 IO 次数**

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

**（2）降低 CPU 计算**  
除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

## 优化方案1： SQL 和索引优化 ##

**（1）使用正确的索引**

索引是数据库中最重要的概念之一，也是提高数据库性能最有效的手段之一，它的诞生本身就是为了提高数据查询效率的，就像字典的目录一样，通过目录可以很快找到相关的内容。我们应该尽可能的使用主键查询，而非其他索引查询，因为主键查询不会触发回表查询，因此节省了一部分时间，变相的提高了查询的性能。

> 索引类型：普通索引、主键索引、唯一索引、组合索引、全文索引

假如我们没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，我们就需要给最常使用的查询字段上，添加相应的索引，这样才能提高查询的性能

**（2）sql书写时的注意**

***在 MySQL 5.0 之前的版本要尽量避免使用 or 查询***

可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并，简单来说就是把多条件查询，比如 or 或 and 查询的结果集进行合并交集或并集的功能，因此就不会导致索引失效的问题了。如果限制条件中其他字段没有索引，尽量少用or。  
避免在 where 查询条件中使用 != 或者 <> 操作符，因为这些操作符会导致查询引擎放弃索引而进行全表扫描。

***适当使用前缀索引***  
MySQL 是支持前缀索引的，也就是说我们可以定义字符串的一部分来作为索引。我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率。  
要尽量避免使用 select \*，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力。

***关于JOIN优化***  
尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，但 Join 语句并不会创建临时表，因此性能会更高。

***我们要尽量使用小表驱动大表的方式进行查询***

也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表。  
不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率。

***增加冗余字段可以减少大量的连表查询***

因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。  
避免类型转换，这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换。

***尽量用 union all 代替 union***

union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

***尽量少排序 order by***

排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。如果排序字段没有用到索引，就尽量少排序。

***SQL语句中IN包含的值不应过多***

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。

当只需要一条数据的时候，使用limit 1，这是为了使EXPLAIN中type列达到const类型。  
区分in和exists， not in和not exists。  
使用合理的分页方式以提高分页的效率。

分段查询  
避免在 where 子句中对字段进行 null 值判断  
不建议使用%前缀模糊查询，如果使用%前缀来模糊查询，建议使用全文索引。

***对于联合索引来说，要遵守最左前缀法则***  
比如：组合索引（a,b,c）三列，我们可以使用（a）、（a,b）、(a,c)、（b、c）、（a,b,c）但是不能以（b,a）、(c,a)、（c,b）等组合使用否则导致索引失效。  
必要时可以使用force index来强制查询走某个索引。

## 文章最后： ##

**以上这些优化方案我们都可以通过 慢查询机制 EXPLAIN方式来验证。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70]  
**EXPLAIN各个字段详介绍：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70 1]

**type字段值如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][20200510014008472.gif]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70]: /images/20230312/29db9c90b8d74821896f140137c0a86e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70 1]: /images/20230312/957822ad93c6410da2b49826b03b878b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70 2]: /images/20230312/4079da153a634b679f92f3d7880c8223.png
[20200510014008472.gif]: /images/20230312/741c6a84c9dd456285be28cc9a322da7.png