mysql 缓存和事物优化

一、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，性能和安全性居中；