456-MySQL(优化）

深碍√TFBOYSˉ_ 2022-09-07 03:59 134阅读 0赞

## MySQL优化 ##

**MySQL需要优化的地方有哪些？？？**  
分为3个方面：

## 1、SQL和索引的优化 ##

（数据量比较大，执行SQL效率低，要优化）  
开启慢查询日志-》设置相应的合理的慢查询时间–》记录慢查询SQL–》用explain分析SQL的执行计划–》相应的优化措施–》数据量太大的话，用分页。用索引。用多列索引。大小表设置。多表查询不用in，用外连接替代。

## 2、应用优化 ##

**1、连接数据库上**，可以引入连接池（中间件）  
**2、引入缓存**（存储热点数据，redis，请求来了，先在redis上查一下，如果redis上直接查到就不经过数据库，如果没有查到就去访问MySQL数据库，访问回来，先把访问的数据往redis上缓存一下，再把结果返回给我们。redis是基于内存的数据库）  
业务上增加redis、memcache，一般用缓存把经常访问的数据缓存起来。  
**引入redis缓存的话，就要防止这些问题** ：缓存数据一致性问题。缓存穿透和缓存雪崩的问题。

除了优化SQL和索引，很多时候，在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对上层的应用来进行一些优化，使得上层应用访问数据库的压力能够减到最小。

## 3、MySQL Server优化 ##

**各种参数的配置** 关闭自适应哈希索引  
重置InnoDB log buffer大小，buffer\_pool大小，减少磁盘I/O次数。

对于MySQL Server端的优化，主要指的就是MySQL Server启动时加载的配置文件的配置项内容的优化  
（就是那个my.ini或者my.cnf），下面我们看看它的配置文件中有哪些是我们需要重点关注的优化参数。

## 连接池 ##

应用上一般访问数据库，都是先和MySQL Server创建连接，然后发送SQL语句，Server处理完成后，再把结果通过网络返回给应用，然后关闭和MySQL Server的连接，因此短时间大量的数据库访问，消耗的TCP三次握手和四次挥手所花费的时间就很大了，稍微大一点的项目，我们都会在应用访问数据库的那一层上，添加连接池模块，相当于应用和MySQL Server事先创建一组连接，当应用需要请求MySQL Server时，不需要再进行TCP连接和释放连接了，一般连接池都会维护以下资源：  
1、连接池里面保持固定数量的活跃TCP连接，供应用使用。  
2、如果应用瞬间访问MySQL的量比较大，那么连接池会实时创建更多的连接给应用使用。  
3、当连接池里面的TCP连接一段时间内没有被用到，连接池会释放多余的连接资源，保留它设置的最大空闲连接量就可以了。  
连接池可以自己实现，也可以用第三方写好的库。

## MySQL查询缓存 ##

**MySQL的查询缓存是把select查询语句上一次的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询**。  
但是当两个select查询中间出现insert，update，delete语句的时候，查询缓存就会被清空。**查询缓存适用更新不频繁的表**，因为当表更新频繁的话，查询缓存也总是被清空，过多的查询缓存的数据添加和删除，就会影响MySQL的执行效率，还不如每次都从磁盘上查来得快（缓存指的就是一块内存，内存I/O比磁盘I/O快很多）。  
**可以在MySQL上通过以下命令，来查看查询缓存的设置：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**通过show status命令，可以查看MySQL查询缓存的使用状况，如下：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**可以通过set命令设置上面的缓存参数开启MySQL查询缓存功能**  
，也可以找到MySQL的配置文件（windows是my.ini，linux是my.cnf），修改query\_cache\_type参数为1就可以了，然后重启MySQL Server就可以使用了，如下：  
![在这里插入图片描述][1936322671b74447abcefd49ba7174e2.png]  
修改完成，重启MySQL Server，查询缓存生效。

## 索引和数据缓存 ##

**主要指的就是innodb\_buffer\_pool\_size配置项**，从名字上就能看到，该配置项是针对InnoDB存储引擎起作用的，这个参数定义了InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。  
innodb\_buffer\_pool\_size是同时为数据块和索引块做缓存，这个值设得越高，访问表中数据需要的磁盘 I/O 就越少。  
![在这里插入图片描述][a155b2e94edb4e99a941ac849dc716dd.png]

## MySQL线程缓存 ##

**主要指配置文件中thread\_cache\_size配置项**。给大家讲过MySQL Server网络模块采用经典的**I/O复用+线程池**模型，之所以引入线程池，主要就是为了在业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的，**所以线程池就是在业务使用之前，先创建一组固定数量的线程**，**等待事件发生**，当有SQL请求到达MySQL Server的时候，在线程池中取一个线程来执行该SQL请求就可以了，执行完成后，不销毁线程，而是把线程再归还到线程池中，等待下一次任务的处理（MySQL会根据连接量，自动加大线程池的数量）。  
![在这里插入图片描述][216bb2293f304579ad144418ee788979.png]  
配置完thread\_cache\_size，重启MySQL Server服务生效。

## 并发连接数量和超时时间 ##

MySQL Server作为一个服务器，可以设置客户端的最大连接量和连接超时时间，如果数据库连接统计数量比较大，这两个参数的值需要设置大一些。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
在配置文件（my.cnf或my.ini）最下面，添加配置：**max\_connections=2000，然后重启MySQLServer，设置生效。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
**MySQL Server对于超时未通信的连接，进行主动关闭操作**。设置超时时间，超过设置时间没有请求就主动断开，单位是秒，在配置文件中添加配置：wait\_timeout = 600。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/3922dc2db69744ed87d05a80f8aa76b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/c9b519ba42d84a3fb65e40ad6e1fd85d.png
[1936322671b74447abcefd49ba7174e2.png]: /images/20220829/597036095e024fe699cf8d71306de467.png
[a155b2e94edb4e99a941ac849dc716dd.png]: /images/20220829/4c313be6358c4bdc92d9cf004155f75c.png
[216bb2293f304579ad144418ee788979.png]: /images/20220829/f3677be9f7424b36a12602df3682ee5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/bee0a99b25834a389fea9a997570cfe1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/164a2259037240e3b534d8b991f2479a.png