MySQL優化之快取優化詳解（一）

在平常被問到最多的問題就是關於MySQL 資料庫效能最佳化方面的問題，所以最近打算寫一個MySQL資料庫效能最佳化方面的系列文章，希望對初中級MySQL DBA 以及其他對MySQL 效能優化有興趣的朋友們有所幫助。

高興的是有博友mark了我的文章。我知道mark之後，很少會再來繼續關注的。但從側面說明了在博友點開部落格的同時，他感覺這篇部落格是有價值的，是能夠彌補他的知識欠缺。一篇部落格最重要的是對自己有用，如果再對別人有用，那是最好的結果。我堅持寫部落格的目的是為了當自己遺忘知識點的時候，能夠最快的找到可靠的解決方案。當自己的歸納的知識，再記起來就會遺忘的慢一點，等時間久了，這部分知識終於化成了自己脫口而出的話，那就再也不怕遺忘了。這篇部落格將繼續講MySQL的內容，這篇講快取優化，講的過程也是我學習的過程。

先來看下我們mysql的版本，我的mac 上裝的版本是5.7的，很多內容都已經改變掉了。這裡講的主要是5.6的版本。

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

一、MySQL快取分類

MySQL的最佳化指的是一個很大的系統，面試的時候我之前是從sql的語句優化方面去說的，這種優化也有作用，不過是從邏輯方面去優化。但是當所有的邏輯層面已經無可優化，所有的索引都已經加好，表結構也設計的合理，但是遇到高並發的時候，為什麼MySQL還是扛不住呢。當然可以透過其他的方面去緩解MySQL的壓力，這裡我們暫且不談。對MySQL而言，我們要盡最大的可能去壓榨機器的效能，讓所有的運算資源都不浪費，都可以為我們服務。 MySQL運行在伺服器上，這裡特別指Linux伺服器。那麼伺服器的硬碟、CPU,內存，網路都有影響到MySQL的效能。 MySQl是非常耗費記憶體的，線上伺服器的MySQL記憶體要吃到80%左右，記憶體太小，其他的最佳化空間其實很小。

另外連接（connection）也是影響MySQL效能的重要一方面。 MySQL客戶機與MySQL伺服器之間的連線是MySQL客戶機與MySQL伺服器重複握手的結果。每次'握手'都經歷身分驗證、權限驗證等環節，握手需要佔用一定的網路資源和MySQL伺服器記憶體資源。

不得不提的是鎖定競爭，對於並發效能要求比較高的資料庫而言，如果有激烈的鎖定競爭，對資料庫的效能將會是很大的打擊。鎖定競爭會明顯的增加執行緒上下文切換的開銷，這些開銷都與預期的需求無關。

二、show status 與show variables

在MySQL系列的前幾篇博客，會經常的看到這些指令，那麼我們分別看下，這兩個指令給MySQL系統管理員展示的是什麼資訊：

show status

#MySQL服務運行的時候，MySQL服務實例的狀態資訊是動態的。用該指令可以顯示目前MySQL伺服器連線的會話狀態變數資訊。預設情況下變數名首字母大寫。

show variables

show variables 用來顯示MySQL 服務實例的各種系統變數(如:全域系統變量，會話系統變量， 靜態變數)，這些變數包含MySQL編譯時參數的預設值，或是my.cnf中設定的參數值。系統變數或參數是一個靜態的概念，預設情況下系統變數名都是小寫字母。

使用MySQL命令show status 或show session status ,可以查看當前MySQL 伺服器連接的會話變數訊息，會話狀態的變數值對當前的MySQL客戶機有效，例如：Opened_tables、Opened_table_definitions狀態變數。

快取機制

#

缓存之所以有效，主要是因为程序运行时对内存或者外存的访问呈现局部性特征，局部性特征为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能再次被访问，空间局部性是指，某个位置被访问后，其相邻的位置的数据很可能被访问到。而MySQL的缓存机制就是把刚刚访问的数据（时间局部性）以及未来即将访问到的数据（空间局部性）保存到缓存中，甚至是高速缓存中。从而提高I/O效率。

按照缓存读写功能的不同，MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢，或者频繁的I/O，对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后，一次性的写入到硬盘中。Buffer 缓存主要用于写数据，提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据，如果Cache缓存已经存储满，则启用LRU算法，进行数据淘汰。淘汰掉最远未使用的数据，从而开辟新的存储空间。不过对于特大型的网站，依靠这种策略很难缓解高频率的读请求，一般会把访问非常频繁的数据静态化，直接由nginx返还给用户。程序和数据库I/O设备交互的越少，则效率越高。

三、MySQL 超时

在使用MySQL的过程中，可能会出现各种超时（timeout）异常，典型的有连接超时、锁等待等。

查看超时时间的类型有哪些：

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name        | Value  |
+-----------------------------+----------+
| connect_timeout       | 10    |
| delayed_insert_timeout   | 300   |
| innodb_flush_log_at_timeout | 1    |
| innodb_lock_wait_timeout  | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout     | 28800  |
| lock_wait_timeout      | 31536000 |
| net_read_timeout      | 30    |
| net_write_timeout      | 60    |
| rpl_stop_slave_timeout   | 31536000 |
| slave_net_timeout      | 3600   |
| wait_timeout        | 28800  |
+-----------------------------+----------+

1、连接超时（connect_timeout）

connect_timeout默认为10s,获取MySQL连接是客户机与服务器之间握手的结果，并且是多次握手的结果，每次握手，除了验证账户名和身份信息外，还需要验证主机、域名解析。如果客户机和服务器之间存在网络故障，可以通过connect_timeout参数来设置，防止它们之间重复握手。

interactive_timeout指的是交互式的终端，在命令行中输入的这种。超过了其设置的默认值就会断开。

wait_timeout指的是非交互式的终端，比如PHP实例化的Mysql连接，一直占用着，超过了这个参数设置的值，就会自动断开。

net_write_timeout MySQL服务器产生一个很大的数据集，MySQL客户机在该值设置的时间内不能接受完毕，则会断开连接。

net_read_timeout MySQL客户机读取了一个很大的数据，在设置值内不能读取完毕，则会自动断开连接。

InnoDB锁等待超时

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50  |
+--------------------------+-------+

InnoDB 的锁等待时间默认为50s,设置行级锁锁等待的值，当出现锁等待的时候，等待时长超过该值会导致锁等待的SQL回滚（不是整个事务回滚）。如果希望整个事务回滚，需要开启innodb_rollback_on_timeout参数。

mysql> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF  |
| innodb_rollback_segments  | 128  |
+----------------------------+-------+

innodb_rollback_on_timeout设置为true 后，遇到事务超时，会回滚整个事务的操作。

复制连接超时

当主从配置是，从服务器（slave）从主服务器（master）读取二进制日志失败后，从服务器会等待 slave_net_timeout 后，从新从master机拉去二进制日志。可以设置成10s.

mysql> show variables like 'slave_net_timeout';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| slave_net_timeout | 3600 |
+-------------------+-------+

这部分总结，应该是周日晚上就该整理好的，结果拖到了今天。后面的计划又要后延了，拖延症真严重。

以上是MySQL優化之快取優化詳解（一）的詳細內容。更多資訊請關注PHP中文網其他相關文章！