MySQL数据库的性能优化总结

梦里梦外; 2022-04-02 12:48 285阅读 0赞

### 一、MySQL数据库的优化目标、基本原则： ###

**1、优化目标：**

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，无论是索引优化、还是表结构优化，参数优化，最后都可以归纳到这这两个分类中：

（1）减少 I/O 次数：

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

（2）降低 CPU 计算：

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

**2、基本原则：**

（1）优先优化高并发的 SQL，而不是执行频率低某些“大”SQL：

对于破坏性来说，高并发的 SQL 总是会比低频率的 SQL 来得大，因为高并发的 SQL 一旦出现问题，甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL，由于频率低，即使遇到，最多就是让整个系统响应慢一点，但至少可能撑一会儿，让我们有缓冲的机会。

（2）从全局出发优化，而不是片面调整：

SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的 SQL，尤其是在通过调整索引优化 SQL 的执行计划的时候，千万不能顾此失彼，因小失大。

（3）尽可能对每一条运行在数据库中的SQL进行 explain：

优化 SQL，需要做到心中有数，知道 SQL 的执行计划才能判断是否有优化余地，才能判断是否存在执行计划问题。在对数据库中运行的 SQL 进行了一段时间的优化之后，很明显的问题 SQL 可能已经很少了，大多都需要去发掘，这时候就需要进行大量的 explain 操作收集执行计划，并判断是否需要进行优化。

### 二、定位分析SQL语句的性能瓶颈： ###

1、通过show status 命令了解各种SQL的执行效率：

> show \[session | global\] status;

可以根据需要加上参数来显示session级（当前连接，默认）和global级（自数据库上次启动至今）的统计结果。

> show status like 'Com\_%';  ---显示当前连接所有统计参数的值。
> 
> Com\_xxx表示每个xxx语句执行的次数，通常需要注意的是下面几个参数：Com\_select/Com\_insert/Com\_update/Com\_delete。

2、定位执行效率较低的SQL语句：

（1）通过show processlist命令实时查看当前SQL的执行情况；

（2）通过慢查询日志定位出现的问题：[《慢查询日志分析》：https://blog.csdn.net/a745233700/article/details/84350662][https_blog.csdn.net_a745233700_article_details_84350662]

3、通过explain分析慢SQL的执行计划：[《explain查看执行计划》：https://blog.csdn.net/a745233700/article/details/84335453][explain_https_blog.csdn.net_a745233700_article_details_84335453]

4、通过show profile 分析SQL的具体耗时瓶颈：[《使用show profile分析SQL的资源消耗》：https://blog.csdn.net/a745233700/article/details/84345344][show profile_SQL_https_blog.csdn.net_a745233700_article_details_84345344]

> 通过secect @have\_profiling命令能够看到当前MySQL是否支持profile；
> 
> 通过show profiles我们能够更清楚了解SQL执行的过程；
> 
> 通过show profile for query我们能看到执行过程中线程的每个状态和消耗的时间。

5、更多查看数据库性能的命令可以阅读这篇文章：[https://blog.csdn.net/a745233700/article/details/84708157][https_blog.csdn.net_a745233700_article_details_84708157]

### 三、数据库的优化方法： ###

该部分主要总结所有数据库优化的方法，每种方法的详情可以进入对应的文章进行阅读

1、设计符合范式的数据库：[https://blog.csdn.net/a745233700/article/details/80933854][https_blog.csdn.net_a745233700_article_details_80933854]

2、选择合适的存储引擎：[https://blog.csdn.net/a745233700/article/details/80793894][https_blog.csdn.net_a745233700_article_details_80793894]

3、SQL表结构、字段优化：[https://blog.csdn.net/a745233700/article/details/84405087][https_blog.csdn.net_a745233700_article_details_84405087]

4、SQL语句优化与索引优化：[https://blog.csdn.net/a745233700/article/details/84455241][https_blog.csdn.net_a745233700_article_details_84455241]

5、读写分离：主数据库负责写操作，从数据库负责读操作。[https://blog.csdn.net/a745233700/article/details/85263543][https_blog.csdn.net_a745233700_article_details_85263543]

6、分库分表：垂直切分与水平切分。[https://blog.csdn.net/a745233700/article/details/85244436][https_blog.csdn.net_a745233700_article_details_85244436]

7、分区：将表的数据按照特定的规则放在不同的分区，提高磁盘的IO效率，提高数据库的性能。[https://blog.csdn.net/a745233700/article/details/85250173][https_blog.csdn.net_a745233700_article_details_85250173]

8、数据库参数优化：IO参数、CPU参数：[https://blog.csdn.net/a745233700/article/details/114506553][https_blog.csdn.net_a745233700_article_details_114506553]

9、硬件配置升级

10、数据库集群

[https_blog.csdn.net_a745233700_article_details_84350662]: https://blog.csdn.net/a745233700/article/details/84350662
[explain_https_blog.csdn.net_a745233700_article_details_84335453]: https://blog.csdn.net/a745233700/article/details/84335453
[show profile_SQL_https_blog.csdn.net_a745233700_article_details_84345344]: https://blog.csdn.net/a745233700/article/details/84345344
[https_blog.csdn.net_a745233700_article_details_84708157]: https://blog.csdn.net/a745233700/article/details/84708157
[https_blog.csdn.net_a745233700_article_details_80933854]: https://blog.csdn.net/a745233700/article/details/80933854
[https_blog.csdn.net_a745233700_article_details_80793894]: https://blog.csdn.net/a745233700/article/details/80793894
[https_blog.csdn.net_a745233700_article_details_84405087]: https://blog.csdn.net/a745233700/article/details/84405087
[https_blog.csdn.net_a745233700_article_details_84455241]: https://blog.csdn.net/a745233700/article/details/84455241
[https_blog.csdn.net_a745233700_article_details_85263543]: https://blog.csdn.net/a745233700/article/details/85263543
[https_blog.csdn.net_a745233700_article_details_85244436]: https://blog.csdn.net/a745233700/article/details/85244436
[https_blog.csdn.net_a745233700_article_details_85250173]: https://blog.csdn.net/a745233700/article/details/85250173
[https_blog.csdn.net_a745233700_article_details_114506553]: https://blog.csdn.net/a745233700/article/details/114506553