mysql 缓存和事物优化

Bertha 。 2022-10-03 00:46 192阅读 0赞

> **一、Query Cache**

1.QueryCache的负面影响：  
 1，Query的hash性能问题和命中率问题；  
 2，查询缓存及其容易失效；当表内容发生变化或者表结构发生变化，对应的查询缓存内容都会失效；  
 3，查询缓存中的结果容易产生重复；因为查询缓存中缓存的是查询结果，所以不同的查询的结果很容易重复；

2.Query Cache的使用：  
 1，设置query\_cache\_limit为查询缓存大小，如果为0，则不使用查询缓存；  
 2，使用SQL\_CACHE或者SQL\_NO\_CACHE来强制是否使用查询缓存；  
 3，查询查询缓存设置：show variables like ‘%query\_cache%’;  
   1，“have\_query\_cache”：该MySQL 是否支持Query Cache；  
   2，“query\_cache\_limit”：Query Cache 存放的单条Query 最大Result Set ，默认1M；  
   3，“query\_cache\_min\_res\_unit”：Query Cache 每个Result Set 存放的最小内存大小，默认4k；  
   4，“query\_cache\_size”：系统中用于Query Cache 内存的大小；  
   5，“query\_cache\_type”：系统是否打开了Query Cache 功能；  
 3.查询查询缓存使用情况：show status like ‘Qcache%’;  
 1，“Qcache\_free\_blocks”：Query Cache 中目前还有多少剩余的blocks。如果该值显示较大，则说明Query Cache 中的内存碎片较多了  
 2，“Qcache\_free\_memory”：Query Cache 中目前剩余的内存大小；  
 3，“Qcache\_hits”：多少次命中；  
 4，“Qcache\_inserts”：多少次未命中然后插入；Query Cache 命中率= Qcache\_hits / ( Qcache\_hits + Qcache\_inserts )；  
 5，“Qcache\_lowmem\_prunes”：多少条Query 因为内存不足而被清除出Query Cache；  
 6，“Qcache\_not\_cached”：因为query\_cache\_type 的设置或者不能被cache 的Query 的数量；  
 7，“Qcache\_queries\_in\_cache”：当前Query Cache 中cache 的Query 数量；  
 8，“Qcache\_total\_blocks”：当前Query Cache 中的block 数量；  
4. query cache的使用限制：  
 1，mysql query cache内容为 select 的结果集, cache 使用完整的 sql 字符串做 key, 并区分大小写，空格等。即两个sql必须完全一致才会导致cache命中。  
 2，prepared statement永远不会cache到结果，即使参数完全一样,  
 3，where条件中如包含了某些函数永远不会被cache, 比如current\_date, now等。  
 4，太大的result set不会被cache (< query\_cache\_limit)  
 5. query cache的使用方式：  
 1，如果没有绝对的使用把握，可以关闭查询缓存；  
 2，如果要使用查询缓存，最好能够精确的控制那些表内容放到查询缓存，哪些表不用查询缓存；

> **二、Innodb\_buffer\_pool\_size**

1，Innodb\_buffer\_pool\_size：innodb的缓存，可以用于缓存索引，同时还会缓存实际的数据；  
    innodb\_buffer\_pool\_size 参数用来设置Innodb 最主要的Buffer(Innodb\_Buffer\_Pool)  
    的大小，对Innodb 整体性能影响也最大，可以按需要设置大一些；  
2，可以通过show status like ‘Innodb\_buffer\_pool\_%’;查看innodb buffer的状态  
 1，Innodb\_buffer\_pool\_pages\_data：使用到了的缓存页数；  
 2，Innodb\_buffer\_pool\_pages\_flushed：刷新过的缓存页数；  
 3，Innodb\_buffer\_pool\_pages\_free：剩余的缓存页数；  
 4，Innodb\_buffer\_pool\_pages\_total：总缓存页数；  
 5，Innodb\_buffer\_pool\_read\_requests：从缓存中读取的数据量；  
 6，Innodb\_buffer\_pool\_reads：直接从磁盘读取的数据量；  
3，可以通过这些参数计算出缓存命中率和缓存利用率等；

> **三、事务优化**

**隔离级别优化**  
 1，innodb实现了READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE四种隔离级别；  
 2，默认使用REPEATABLE READ隔离级别；  
 3，可以通过SELECT @@tx\_isolation;查看当前的事务隔离级别；  
 4，可以通过set tx\_isolation=‘read-committed’;来修改默认的事务隔离级别；  
**innodb\_flush\_log\_at\_trx\_commit**  
  1，理解Innodb事务机制：  
   1，事务在buffer中对数据进行修改；  
   2，事务的变化记录在事务日志中；  
   3，在合适的时机同步事务日志中的数据到数据库中；  
 2，所以什么时候提交事务日志文件，对系统性能影响较大，可以通过设置  
   innodb\_flush\_log\_at\_trx\_commit来修改事务日志同步时机：  
   1，innodb\_flush\_log\_at\_trx\_commit = 0，每1秒钟同步一次事务日志文件；  
   2，innodb\_flush\_log\_at\_trx\_commit = 1，默认设置，每一个事务完成之后，同步一次事务日志文件；  
   3，innodb\_flush\_log\_at\_trx\_commit = 2，事务完成之后，写到事务日志文件中，等到日志覆盖再同步数据；  
   注意，1性能最差，2不能完全保证数据是写到数据文件中，如果宕机，可能会有数据丢失现象，但性能最高；1，性能和安全性居中；