Mysql-性能参数优化(二)

Myth丶恋晨 2024-04-01 13:13 90阅读 0赞

## 1、mysql连接数 ##

![在这里插入图片描述][4da01b819a1549d58f7beb0e5a33c808.png]

> max\_connections：  
> 是指MySQL服务实例能够同时接受的的最大并发连接数。MySQL实际上支持最大连接数加一的算法，保障当连接数用完的时候，超级管理员依然可以和服务端建立连接，进行管理。

> max\_user\_connections：  
> 设置指定账号的最大并发连接数。

> max\_connect\_errors：  
> 当某台非法主机恶意连接MySQL服务端，遭到的错误达到设置值后，MySQL会解决来自该主机的所有连接。但执行flush hosts后会清零。

> 查看mysql连接状态  
> ![在这里插入图片描述][cd5e8667563843509f1e1c1317cc6b4e.png]  
> Connection\_errors\_max\_connections  
> 当MySQL的最大并发数大于系统变量（show variables）中max\_connections的最大并发数，因此而被拒绝的次数，将会记录在这个变量里。如果Connection\_error\_max\_connections值比较大，则说明当前系统并发比较高，要考虑调大max\_connections的值。  
> Connections  
> 表示MySQL从启动至今，成功建立连接的连接数，这个值是不断累加的。  
> Max\_used\_connections  
> 表示MySQL从启动至今，同一时刻并发的连接数，取得是最大值。如果这个值大于 max\_connections则表明系统经常处于高并发的状态，应该考虑调大最大并发连接数。

## 2、 线程 ##

![在这里插入图片描述][2cc087501e7340f78337a7f93d4201bc.png]

> thread\_cache\_size  
> 设置连接线程缓存的数目。这个缓存相当于MySQL线程的缓存池（thread cache pool）,将空闲的连接线程放入连接池中缓存起来，而非立即销毁。当有新的连接请求时，如果连接池中有空闲的连接，则直接使用。否则要重新创建线程。创建线程是一个不小的系统开销。MySQL的这部分线程处理和Nginx的线程处理有异曲同工之妙，以后介绍Nginx的线程处理时，会拿来做对比。

> thread\_handling  
> 默认值是： one-thread-per-connection 表示为每个连接提供或者创建一个线程来处理请求，直至请求完毕，连接销毁或者存入缓存池。当值是no-threads 时，表示在始终只提供一个线程来处理连接，一般是单机做测试使用的。

> thread\_stack stack  
> 是堆的意思，进程和线程都是有唯一的ID的，进程的ID系统会维护线程的ID,则由具体的线程库区维护，当进程或者线程休眠的时候，进程的上下文信息要在内存中开辟出一块区域，保存进程的上下文信息，以便于迅速唤醒程序。默认为MySQL的每个线程设置的堆栈大小为：262144/1024=256k

![在这里插入图片描述][d3798528769e46bda26afd803fe4d6f3.png]

> Thread\_cached  
> 当前线程池的线程数

> Thread\_connected  
> 当前的连接数

> Thread\_cached:  
> 当前连接线程创建数, 如果这个值过高，可以调整thread cache size 也就是调整线程缓存池的大小。

> Thred\_runnint:  
> 当前活跃的线程数。

> 连接请求堆栈  
> MySQL在很短的时间内，突然收到很多的连接请求时，MySQL会将不能来得及处理的连接请求保存在堆栈中，以便MySQL后续处理。  
> back\_log参数设置了堆栈的大小，可以通过如下命令查看

![在这里插入图片描述][5430ad103e4a47cb99cfb10f1f366a62.png]

![在这里插入图片描述][9c25c26711fb4c109b4e57d27fdaf825.png]

> Aborted\_clients  
> MySQL 客户机被异常关闭的次数。

> Aborted\_connects  
> 试图连接到MySQL服务器而失败的连接次数。

> mysql连接优化  
> 对于mysql服务器最大连接数值的设置范围比较理想的是： Max\_used\_connections / max\_connections  
> 在10%以上 如果在10%以下，说明mysql服务器的max\_connections设置过高

> open-files-limit  
> ini/cnf 参数: open-files-limit  
> mysql 变量: open\_files\_limit  
> 全局变量，不可动态调整，取值范围 0到65535。  
> open\_files\_limit指mysql能够打开的文件句柄数。该值不足时，会引发 Too many open files错误。具体需要多少个文件句柄，还需要根据 max\_connections 和 table\_open\_cache来计算。  
> open-files-limit可能受到操作系统的限制, 值不能大于 ulimit -n 上面的配置，是OS限制各个用户能够打开的文件描述符限制（hard soft区别参看man ulimit），新增上面两行，表示mysql用户能够打开65535个文件描述符。

> max\_connections设置过高有什么坏处？  
> 需要考虑使用的平台所支持的线程库数量（windows只能支持到2048）、服务器的配置（特别是内存大小）、每个连接占用资源（内存和负载）的多少  
> ![在这里插入图片描述][b75a5f938be142aeb5a33d5210fa1b6f.png]

## 3、 内存使用优化： ##

> 配置MySQL时该如何估算内存的消耗。那么该使用什么公式来计算呢？

> 关心内存怎么使用的原因是可以理解的。如果配置MySQL服务器使用太少的内存会导致性能不是最优的；如果配置了太多的内存则会导致崩溃，无法执行查询或者导致交换操作严重变慢。在现在的32位平台下，仍有可能把所有的地址空间都用完了，因此需要监视着。话虽如此，但我并不觉得找到什么可以计算内存使用的秘诀公式就能很好地解决这个问题。原因有 –如今这个公式已经很复杂了，更重要的是，通过它计算得到的值只是“理论可能”并不是真正消耗的值。事实上，有8GB内存的常规服务器经常能运行到最大的理论值– 100GB甚至更高。此外，你轻易不会使用到“超额因素” – 它实际上依赖于应用以及配置。一些应用可能需要理论内存的 10% 而有些仅需1%。 那么，我们可以做什么呢？首先，来看看那些在启动时就需要分配并且总是存在的全局缓冲  
> key\_buffer\_size,  
> innodb\_buffer\_pool\_size,  
> innodb\_additional\_memory\_pool\_size,  
> innodb\_log\_buffer\_size,  
> query\_cache\_size。  
> 如果你大量地使用MyISAM表，那么你也可以增加操作系统的缓存空间使得MySQL也能用得着。把这些也都加到操作系统和应用程序所需的内存值之中，可能需要增加32MB甚至更多的内存给MySQL服务器代码以及各种不同的小静态缓冲。这些就是你需要考虑的在MySQL服务器启动时所需的内存。其他剩下的内存用于连接。例如有8GB内存的服务器，可能监听所有的服务就用了6GB的内存，剩下的2GB内存则留下来给线程使用。

> 每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时 – 它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read\_buffer\_size, sort\_buffer\_size, read\_rnd\_buffer\_size, tmp\_table\_size 大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块，例如 tmp\_table\_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意，这里需要考虑的不只有一点 – 可能会分配多个同一种类型的缓存，例如用来处理子查询。一些特殊的查询的内存使用量可能更大 – 如果在MyISAM表上做成批的插入时需要分配 bulk\_insert\_buffer\_size 大小的内存。执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam\_sort\_buffer\_size 大小的内存。  
> 只有简单查询OLTP应用的内存消耗经常是使用默认缓冲的每个线程小于1MB，除非需要使用复杂的查询否则无需增加每个线程的缓冲大小。使用1MB的缓冲来对10行记录进行排序和用16MB的缓冲基本是一样快的（实际上16MB可能会更慢，不过这是其他方面的事了）。  
> 另外，就是找出MySQL服务器内存消耗的峰值。这很容易就能计算出操作系统所需的内存、文件缓存以及其他应用。在32位环境下，还需要考虑到32位的限制，限制 “mysqld” 的值大约为2.5G（实际上还要考虑到很多其他因素）。现在运行 “ps aux” 命令来查看 VSZ 的值 – MySQL 进程分配的虚拟内存。也可以查看 “Resident Memory” 的值，不过我想它可能没多大用处，因为它会由于交换而变小 – 这并不是你想看到的。监视着内存变化的值，就能知道是需要增加/减少当前的内存值了  
> 可能有的人想说，我们想要让服务器能保证100%不会耗尽内存，不管决定用什么样的查询、什么样的用户。很不幸，这其实很不明智也不可能，因为：

> 以下是很少考虑的MySQL服务器内存需求  
> 每个线程可能会不止一次需要分配缓冲。 考虑到例如子查询 – 每层都需要有自己的  
> read\_buffer,  
> sort\_buffer,  
> tmp\_table\_size 等。

> 在每个连接中很多变量都可能需要重新设置。 如果开发者想设定自己的变量值来运行某些查询就不能继续使用全局值。  
> 可能有多个索引缓存。 为了配合执行查询可能会创建多个索引缓存。  
> 解析查询和优化都需要内存。 这些内存通常比较小，可以忽略，不过如果是某些查询在这个步骤中则需要大量内存，尤其是那些设计的比较特别的查询。

> 存储过程。 复杂的存储过程可能会需要大量内存。  
> 准备查询语句以及游标。 单次链接可能会有很多的准备好的语句以及游标。它们的数量最后可以限定，但是仍然会消耗大量的内存。  
> Innodb表缓存。 Innnodb表有自己的缓存，它保存了从一开始访问每个表的元数据。它们从未被清除过，如果有很多Innodb表的话，那么这个量就很大了。这也就意味着拥有 CREATE TABLE 权限的用户就可能把MySQL服务器的内存耗尽。

> MyISAM缓冲。 MyISAM表可能会分配一个足以装下指定表最大记录的缓冲，而且这个缓冲直到表关闭了才释放。  
> Blobs可能需要3倍的内存。 这在处理很大（max\_allowed\_packet 的值较大）的Blobs数据时很重要，如果处理256MB的数据可能需要768MB的内存。

> 存储引擎。 通常情况下，存储引擎会设置自己的每个线程的全局分配内存，它通常不能像缓存一样可以调节。现在应该通过各种方式来特别关注MySQL释放出来的存储引擎。

> 我想这还不是完成的列表，相反地，我觉得还是漏掉了一些（如果你知道，请给我回复加上）。但主要的原因是 – 找到每次内存消耗峰值是不切实际的，因此我的这些建议可以用来衡量一下你实际修改一些变量值产生的反应。例如，把 sort\_buffer\_size 从1MB增加到4MB并且在 max\_connections 为 1000 的情况下，内存消耗增长峰值并不是你所计算的3000MB而是30MB。

> 小结：  
> 每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时 – 它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read\_buffer\_size, sort\_buffer\_size, read\_rnd\_buffer\_size, tmp\_table\_size 大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块，例如 tmp\_table\_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意，这里需要考虑的不只有一点 – 可能会分配多个同一种类型的缓存，例如用来处理子查询。一些特殊的查询的内存使用量可能更大 – 如果在MyISAM表上做成批的插入时需要分配 bulk\_insert\_buffer\_size 大小的内存。执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam\_sort\_buffer\_size 大小的内存。

## 4、什么是查询缓存？ ##

> Query cache 作用于整个 MySQL，主要用来缓存 MySQL 中的查询结果集，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。  
> 优化查询缓存配置

> 通过以下命令查看缓存相关变量  
> show variables like ‘%query\_cache%’;

> 参数解释：  
> have\_query\_cache :  
> 表示此版本mysql是否支持缓存

> query\_cache\_limit ：  
> 缓存最大值

> query\_cache\_size :  
> 缓存大小  
> query\_cache\_type :off 表示不缓存，on表示缓存所有结果  
> 开启缓存方式：在my.ini 文件底部添加：  
> query\_cache\_type = 1  
> 如果query\_cache\_size 值为0 ，也需要在my.ini中设置为需要的值。

> 通过以下命令查看查询缓存状态  
> show status like ‘%Qcache%’;

> Qcache\_free\_blocks：  
> 目前还处于空闲状态的 Query Cache 中内存 Block 数目

> Qcache\_free\_memory：  
> 目前还处于空闲状态的 Query Cache 内存总量

> Qcache\_hits：  
> Query Cache 命中次数

> Qcache\_inserts：  
> 向 Query Cache 中插入新的 Query Cache的次数，也就是没有命中的次数

> Qcache\_lowmem\_prunes：  
> 当 Query Cache 内存容量不够，需要从中删除老的 Query Cache以给新的 Cache 对象使用的次数

> Qcache\_not\_cached：  
> 没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于query\_cache\_type 设置的不会被 Cache 的 SQL

> Qcache\_queries\_in\_cache：  
> 目前在 Query Cache 中的 SQL 数量

> Qcache\_total\_blocks：  
> Query Cache 中总的 Block 数量

> 检查换成命中情况  
> 查询缓存命中率的计算公式是  
> Qcache\_hits/(Qcache\_hits+Com\_select)。

> 查询缓存命中率  
> (Qcache\_hits - Qcache\_inserts) / Qcache\_hits \* 100%

## 4 、日志产生的性能影响 ##

> 由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源。MySQL的日志包括错误日志（ErrorLog），二进制日志（Binlog），查询日志（QueryLog），慢查询日志（SlowQueryLog）等。在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO损耗提高系统性能的目的。但是在一般稍微重要一点的实际应用场景中，都至少需要打开二进制日志，因为这是MySQL很多存储引擎进行增量备份的基础，也是MySQL实现复制的基本条件。

> binlog日志性能优化  
> “binlog\_cache\_size"：  
> 在事务过程中容纳二进制日志SQL语句的缓存大小。每个Client都可以分配设置大小的binlogcache空间。如果系统中存在很大较大的事务，可以尝试增加该值的大小，以获得更有的性能。  
> 通过MySQL的以下两个状态变量来判断当前的binlog\_cache\_size的状况：Binlog\_cache\_use和Binlog\_cache\_disk\_use。

> “max\_binlog\_cache\_size”：  
> 和"binlog\_cache\_size"相对应，但是所代表的是binlog能够使用的最大cache内存大小。当我们执行多语句事务的时max\_binlog\_cache\_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan’max\_binlog\_cache\_size’bytesofstorage”的错误。

> “max\_binlog\_size”：  
> Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。该大小并不能非常严格控制Binlog大小，尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进入当前日志，直到该事务结束。

> “sync\_binlog”：  
> 这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。  
> sync\_binlog=0： 当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog\_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。  
> sync\_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog\_cache中的数据强制写入磁盘。  
> 在MySQL中系统默认的设置是sync\_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog\_cache中的所有binlog信息都会被丢失。  
> 而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog\_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync\_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

> 大家都知道，MySQL的复制（Replication），实际上就是通过将Master端的Binlog通过利用IO线程通过网络复制到Slave端，然后再通过SQL线程解析Binlog中的日志再应用到数据库中来实现的。所以，Binlog量的大小对IO线程以及Msater和Slave端之间的网络都会产生直接的影响。

> MySQL中Binlog的产生量是没办法改变的，只要我们的Query改变了数据库中的数据，那么就必须将该Query所对应的Event记录到Binlog中。那我们是不是就没有办法优化复制了呢？当然不是，在MySQL复制环境中，实际上是是有8个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB或者Table的，分别为：  
> Binlog\_Do\_DB：设定哪些数据库（Schema）需要记录Binlog；  
> Binlog\_Ignore\_DB：设定哪些数据库（Schema）不要记录Binlog；  
> Replicate\_Do\_DB：设定需要复制的数据库（Schema），多个DB用逗号（“,”）分隔；  
> Replicate\_Ignore\_DB：设定可以忽略的数据库（Schema）；  
> Replicate\_Do\_Table：设定需要复制的Table；  
> Replicate\_Ignore\_Table：设定可以忽略的Table；  
> Replicate\_Wild\_Do\_Table：功能同Replicate\_Do\_Table，但可以带通配符来进行设置；  
> Replicate\_Wild\_Ignore\_Table：功能同Replicate\_Ignore\_Table，可带通配符设置；  
> 通过上面这八个参数，我们就可以非常方便按照实际需求，控制从Master端到Slave端的Binlog量尽可能的少，从而减小Master端到Slave端的网络流量，减少IO线程的IO量，还能减少SQL线程的解析与应用SQL的数量，最终达到改善Slave上的数据延时问题。

> 实际上，上面这八个参数中的前面两个是设置在Master端的，而后面六个参数则是设置在Slave端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系，但是对于优化MySQL的Replication来说都可以启到相似的功能。当然也有一定的区别，其主要区别如下：  
> 如果在Master端设置前面两个参数，不仅仅会让Master端的Binlog记录所带来的IO量减少，还会让Master端的IO线程就可以减少Binlog的读取量，传递给Slave端的IO线程的Binlog量自然就会较少。这样做的好处是可以减少网络IO，减少Slave端IO线程的IO量，减少Slave端的SQL线程的工作量，从而最大幅度的优化复制性能。

> 而如果是在Slave端设置后面的六个参数，在性能优化方面可能比在Master端要稍微逊色一点，因为不管是需要还是不需要复制的Event都被会被IO线程读取到Slave端，这样不仅仅增加了网络IO量，也给Slave端的IO线程增加了RelayLog的写入量。但是仍然可以减少Slave的SQL线程在Slave端的日志应用量。  
> 虽然性能方面稍有逊色，但是在Slave端设置复制过滤机制，可以保证不会出现因为默认Schema的问题而造成Slave和Master数据不一致或者复制出错的问题。

> Slow Query Log 相关参数及使用建议  
> 有些时候，我们为了定位系统中效率比较地下的Query语句，则需要打开慢查询日志，也就是Slow Query Log。

> “log\_slow\_queries”  
> 参数显示了系统是否已经打开SlowQueryLog功能，而“long\_query\_time”参数则告诉我们当前系统设置的SlowQuery记录执行时间超过多长的Query。

> 打开SlowQueryLog功能对系统性能的整体影响没有Binlog那么大，毕竟SlowQueryLog的数据量比较小，带来的IO损耗也就较小，但是，系统需要计算每一条Query的执行时间，所以消耗总是会有一些的，主要是CPU方面的消耗。如果大家的系统在CPU资源足够丰富的时候，可以不必在乎这一点点损耗，毕竟他可能会给我们带来更大性能优化的收获。但如果我们的CPU资源也比较紧张的时候，也完全可以在大部分时候关闭该功能，而只需要间断性的打开SlowQueryLog功能来定位可能存在的慢查询。

> MySQL的其他日志由于使用很少（QueryLog）或者性能影响很少，我们就不在此过多分析了，至于各个存储引擎相关的日志，我们留在后面“常用存储引擎优化”部分再做相应的分析。

> innodb\_buffer\_pool\_size 的合适设置  
> InnoDB 不仅仅缓存索引，同时还会缓存实际的数据。所以， InnoDB 存储引擎可以使用更多的内存来缓存数据库的相关信息。  
> innodb\_buffer\_pool\_size 参数用来设置 InnoDB 最主要的 Buffer（InnoDB Buffer Pool）的大小，缓存用户表及索引数据的最主要缓存空间，对 InnoDB 整体性能影响也最大。  
> 生产环境 InnoDB 存储引擎哟啊根据 Buffer Pool 实时信息作出进一步分析。

> show status like “Innodb\_buffer\_pool\_%”;  
> 1  
> 总共有 8191 个page， 有 7303 个是 Free 状态， 有 788个 page 有数据， read 请求 31569次，其中有 710次请求使用物理磁盘获取  
> InnoDB Buffer Pool 的 Read 命中率大概： （31569-710)/ 31569 \* 100% = 97.75%  
> Innodb\_buffer\_pool\_read\_ahead\_rnd：记录进行随机读的时候产生的预读次数；  
> Innodb\_buffer\_pool\_read\_ahead\_seq：记录连续读的时候产生的预读次数；  
> 预读 – 在一些高端存储才会有，简单来说，就是通过分析数据请求的特点来自动判断客户端在请求当前数据块之后可能会继续请求的数据块。通过该自动判断，存储引擎可能会一次性将当前请求的数据库和后面可能请求的下一个（或者几个）数据库全部读出，以期望通过这种方式减少磁盘 IO 次数，提高 IO 性能 。

> innodb\_log\_buffer\_size 参数的使用  
> 设置 InnoDB 的 Log Buffer 大小，系统默认 1MB。 Log Buffer 的主要作用就是 缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果系统不是写负载非常高而且以大事务居多的话， 8MB 以内大小足够。  
> 也可以通过 系统状态 参数提供的性能统计数据来分析 Log 的使用情况

show status like “Innodb\_log\_%”;  
在这里插入图片描述  
innodb\_additional\_mem\_pool\_size 参数  
设置用户存放 InnoDB 的字典信息和其他内部结构所需要的内存空间。所以， InnoDB 表越多，需要的空间自然也就越大，系统默认 1MB。  
一个常规的 几百个 InnoDB 表的 MySQL ，如果不是每个表都是上百个字段， 20MB 内存已经足够了。  
该参数对系统整体性能并无太大影响，设置超过实际所需存储，只是浪费内存而已。

Double Write Buffer  
Double Write Buffer 是 InnoDB 所使用的一种较为独特的文件 Flush 实现技术，主要作用是在减少文件同步次数提高 IO 性能的情况下，提高系统崩溃（Crash）或断电情况下的安全性，避免写入的数据不完整。

Adaptive Hash Index  
Adaptive Hash index 的目的并不是为了改善磁盘 IO 性能，而是为了提高 Buffer Pool 中的数据访问效率，也就是给 Buffer Pool中的数据做索引。

## 5、 InnoDB事务优化 ##

> 脏读：指当一个事物正在访问数据，并且对数据进行修改，而这种修改没有提交到数据库中，这时，另外一个事物也访问这个数据，然后使用了这个数据。  
> 不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据不一样，因此成为不可重复读。  
> 幻读：指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改了这个表中的数据，如想表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还没有修改的数据行，就好像发生了幻觉一样。  
> 在这里插入图片描述  
> 对于高并发应用来说，为了尽可能保证数据的一致性，避免并发可能带来的数据不一致，自然是事务隔离级别越高越好。但是，对于 InnoDB 来说，所使用的事务隔离级别越高，实现复杂度自然就会更高，所要做的事情也会更高，整体性能也就更差。虽然 InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ，但实际上在大部分应用中，只需要 READ COMMITED 的事务隔离级别就可以满足需求了。（不太赞同，因为并发是很容易出现的场景）。  
> 事务与 IO 的关系及优化

> InnoDB 修改数据操作，实际上修改的是Buffer Pool中的数据。  
> 为了保障数据的安全稳定，不丢失数据。InnoDB并不是一个事务提交后就将 Buffer Pool 中被修改的数据同步到磁盘上，而是要先记录到redo log日志中，以防崩溃之后可以恢复。  
> 最后再从Buffer Pool 中把脏页连续写入磁盘。  
> 而且记录到redo log日志中也不是直接写磁盘，而是先写到redo log 缓冲区  
> 控制 InnoDB 事务日志刷新方式参数：innodb\_flush\_log\_at\_trx\_commit :  
> 在这里插入图片描述

> innodb\_flush\_log\_at\_trx\_commit = 0， InnoDB 中的 Log Thread 每隔 1秒将 log buffer 中的数据写入文件，同时还会通知文件系统进行与文件同步的 flush操作，保证数据确实已经写入磁盘。  
> innodb\_flush\_log\_at\_trx\_commit = 1， InnoDB 默认设置。每次事务的结束都会出发 Log Thread 将 Log Buffer 中的数据写入文件、并通知文件系统同步文件。这个设置最安全，能够保证不论是 MySQL 崩溃、OS崩溃还是主机断电都不会丢失任何已经提交的数据。  
> innodb\_flush\_log\_at\_trx\_commit = 2， 每次事务结束的时候将数据写入事务日志，仅仅是调用了文件系统的文件写入操作。而文件系统都是有缓存机制的，所以 Log Thread 的写入并不能保证内容已经写入到物理磁盘完成持久化的动作。文件系统什么时候会将缓存中的数据同步到物理磁盘、文件， Log Thread 就完全不知道，所以，当设置 2 的时候， MySQL 崩溃并不会造成数据的丢失，但是 OS 崩溃或主机断电后可能丢失的数据量就完全控制在文件上了。  
> 分析:  
> 设置1 最安全，由于 IO 同步操作多，所以，性能最低。  
> 设置 0 ，则每秒一次同步，性能相对高一下。  
> 如果设置 2 ，性能可能是三种最好的。但是也可能出现故障后丢失数据最多的一种。如果 OS 足够稳定，主键硬件设备足够好，且主机供电系统足够安全，可将设置为2 ，让系统整体性能尽可能高。【建议设置为 2】

> InnoDB数据存储优化  
> 查看 InnoDB 表空间的使用情况：  
> show table status like “tablename”\\G;

[4da01b819a1549d58f7beb0e5a33c808.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/f0d0df2ef6784499a874f3e6c127e48e.png
[cd5e8667563843509f1e1c1317cc6b4e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/8e6787468ae345908296c5426c120c76.png
[2cc087501e7340f78337a7f93d4201bc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0bef382f22834899b63626bc2ea45931.png
[d3798528769e46bda26afd803fe4d6f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/9561e39251d7492d9882cad515d03fa0.png
[5430ad103e4a47cb99cfb10f1f366a62.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0af54d9b7c5d452a955b50fefc46690e.png
[9c25c26711fb4c109b4e57d27fdaf825.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/199ea162dc504836ace97bb35a6d8b47.png
[b75a5f938be142aeb5a33d5210fa1b6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/8b134ab3c27d4abcabc79d0441d44784.png