?

本文根據優才網課程整理，面向web開發者，內容以實用為主，專業DBA可以繞行。

如果你在大公司，可能有專門的DBA來做這些事情，如果你在一個小公司當架構師或者技術總監，或者你自己創業，那DBA的活你也得干了。咱們來講一下基本的mysql安裝和優化。

?

一： MYSQL安裝和基本配置

在linux上安裝，可以用包管理工具來安裝，比較簡單：
RedHat 系列：yum -y install mysql mysql-server
Debian系列：sudo apt-get install mysql mysql-server

安裝之后不知道mysql裝到哪了怎么辦，用whereis mysql 命令來找一下。 先找到mysql的默認配置文件。一般來說，安裝后有這么幾個備選的配置：

      my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf　
    

就2014年的機器配置來說，咱們直接用my-huge.cnf。把my-huge.cnf 復制到/etc/下，改名my.cnf。配置文件就有了， 然后啟動mysql： /etc/init.d/mysqld start 。

安裝之后默認的帳號是root, 密碼為空。咱們要做的第一件事是改root密碼。
進入mysql：mysql -uroot -p
選擇數據庫： use mysql
改密碼： UPDATE user SET Password = PASSWORD('xxxx') WHERE user = 'root';
刷新權限： FLUSH PRIVILEGES;

現在數據庫裝好了， 帳號和權限也設置了，是不是就可以使用了呢,還要檢查幾個配置。打開配置文件 vim /etc/my.cnf

skip-networking 要關閉。
bind-address = 127.0.0.1 這一行要關閉或者修改成允許的IP
skip-name-resolve 禁止dns解析，只能用IP連，這個可以打開。

如果你的mysql經過一段時間運行，挺過了訪問高峰，咱們再來檢查一下配置是否合適。下面說的配置，都必須是在運行一段時間后檢查才有意義。如果剛啟動沒多久，mysql的運行狀態沒有代表性，不能作為參考。檢查配置參數也沒意義。

?

二：連接數(connection)配置

max_connections 可以設置最大并發連接數。當MySql的并發連接達到這個設定值時，新的連接將會被拒絕（“Can not connect to MySQL server. Too many connections”-mysql 1040錯誤，）。當發現MySql有能力處理更多的并發的時候， 建議調大這個值，相應給服務器帶來更高的負載(CPU/IO/內存)。

查看設置的最大連接是多少：

      mysql> show variables like 'max_connections'; 

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 151 | 允許的最大連接數

+-----------------+-------+



看當前連接數：show status like 'threads_connected';

最大連接數: show status like "max_used_connections";


    

如果max_used_connections已經接近 max_connections了，就說明max_connections太小。不合適了。

還有一些跟連接數相關的配置：
back_log=50
MySQL能暫存的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，這就起作用。如果MySQL的連接數據達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。

back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。

不同的操作系統在這個隊列大小上有它自己的限制。試圖設定back_log高于你的操作系統的限制將是無效的。默認值為50。對于Linux系統推薦設置為小于512的整數。

如果鏈接數超過max_connections+back_log ，才會出錯。

max_connect_errors=10
當客戶端連接服務端超時(超過connect_timeout), 服務端就會給這個客戶端記錄一次error，當出錯的次數達到max_connect_errors的時候，這個客戶端就會被鎖定。除非執行FLUSH HOSTS命令。

connect_timeout=5
連接超時的秒數

?

三：查詢緩存(query_cache)配置

查詢緩存就是內存中的一塊存儲區域，其存儲了用戶的SQL文本以及相關的查詢結果。通常情況下，用戶下次查詢時，如果所使用的SQL文本是相同的，并且自從上次查詢后，相關的紀錄沒有被更新過，此時數據庫就直接采用緩存中的內容。從內存中讀取要比從硬盤上速度要快好幾百倍。

MYSQL的查詢緩存用于緩存select查詢結果，并在下次接收到同樣的查詢請求時，不再執行實際查詢處理而直接 返回結果，有這樣的查詢緩存能提高查詢的速度，使查詢性能得到優化.

要使用緩存，有幾個條件。
一是所采用的SQL語句是相同的。每次查詢的語句不一樣，肯定不能用到緩存。比如語句里帶當前秒數 where ctime > xxx
二是表數據沒有改過。沒有改過結構，沒有update，insert
三：客戶端與服務器的默認字符集得一樣

所以可以看出，要利用好緩存，有大量的相同的查詢，而很少改變表里的數據，否則沒有必要使用此功能.

查看查詢緩存的設置：

      SHOW VARIABLES LIKE '%query_cache%';

+------------------------------+----------+

| Variable_name | Value |

+------------------------------+----------+

| have_query_cache | YES |

| query_cache_limit | 1048576 | 如果單個查詢結果大于這個值，則不Cache

| query_cache_min_res_unit | 4096 | 每次給QC結果分配內存的大小

| query_cache_size | 33554432 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

+------------------------------+----------+



query_cache_type=1


    

如果設置為1，將會緩存所有的結果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。
如果設置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

query_cache_size 默認是32M，太小了，可調到128M或者256M。 可以通過Qcache_lowmem_prunes變量的值來檢查是否當前的值滿足你目前系統的負載。

query_cache_size的工作原理：一個SELECT查詢在DB中工作后，DB會把該語句緩存下來，當同樣的一個SQL再次來到DB里調用時，DB在該表沒發生變化的情況下把結果從緩存中返回給Client。這里有一個關建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間內沒有發生變更。那如果該表在發生變更時，Query_cache里的數據又怎么處理呢？首先要把Query_cache和該表相關的語句全部置為失效，然后在寫入更新。那么如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎么這么慢了。所以在數據庫寫入量或是更新量也比較大的系統，該參數不適合分配過大。而且在高并發，寫入量大的系統，建系把該功能禁掉。

Qcache_lowmem_prunes可以檢查是否設置的太小。

query_cache_limit 默認是1M，根據你的常用查詢的數據結果大小來定。如果返回的數據小，可以設置小一點。
設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費

查看緩存使用效果如何：

      show status like '%Qcache%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Qcache_free_blocks | 160 | 目前還處于空閑狀態的 Query Cache中內存 Block 數目，數目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。

| Qcache_free_memory | 23147296 | 緩存中的空閑內存總量。

| Qcache_hits | 52349 | 緩存命中次數。

| Qcache_inserts | 8827 | 緩存失效次數。

| Qcache_lowmem_prunes | 0 | 緩存出現內存不足并且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者內存很少。

| Qcache_not_cached | 2446 | 沒有被cache和不適合進行緩存的查詢的數量，通常是由于這些查詢不是SELECT語句以及由于query_cache_type設置的不會被Cache的查詢。show,use,desc

| Qcache_queries_in_cache | 5234 | 當前被cache的SQL數量。

| Qcache_total_blocks | 10796 | 緩存中塊的數量。

+-------------------------+----------+



show global status like 'Com_select'; 

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select | 12592 | com_select 變量記錄的是無緩存的查詢次數+錯誤查詢+權限檢查查詢。

+---------------+-------+


    

Mysql的查詢緩存命中率沒有官方算法，只有前人的經驗總結
命中率 ≈ qcache_hits / (qcache_hits + com_select)

緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% ,如果碎片率太高，20% ，可以FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數據量的話。

查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。

如何提高命中率：
1：字符集相同，2：SQL語句盡量固定(SQL語句避免隨機數，秒數等) 3 加大緩存空間 4：適當分表,動靜分離，

四：臨時表緩存(tmp_table_size)配置

mysql進行復雜查詢或者 做高級GROUP BY操作的時候，系統為了優化查詢，生成一些臨時表。通過設置tmp_table_size選項來設置臨時表占用空間的大小。

我們使用explain分析SQL，如果在Extra列看到Using temporary就意味著使用了臨時表。

MySQL臨時表分為“內存臨時表”和“磁盤臨時表”，其中內存臨時表使用MySQL的MEMORY存儲引擎，磁盤臨時表使用MySQL的MyISAM存儲引擎；

一般情況下，MySQL會先創建內存臨時表，但內存臨時表超過配置指定的值后，MySQL會將內存臨時表導出到磁盤臨時表

臨時表將在你連接MySQL期間存在。當你斷開時，MySQL將自動刪除表并釋放所用的空間。

      mysql> SHOW VARIABLES LIKE '%tmp_table_size%';

+----------------+----------+

| Variable_name | Value |

+----------------+----------+

| tmp_table_size | 33554432 | 

+----------------+----------+

1 row in set (0.00 sec)



mysql> show global status like 'created_tmp%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Created_tmp_disk_tables | 690421 | 服務器執行語句時在硬盤上自動創建的臨時表的數量

| Created_tmp_files | 755473 | mysqld已經創建的臨時文件的數量

| Created_tmp_tables | 14372959 | 服務器執行語句時自動創建的內存中的臨時表的數量。如果Created_tmp_disk_tables較大，你可能要增加tmp_table_size值使臨時 表基于內存而不基于硬盤

+-------------------------+----------+


    

每次創建臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁盤上創建臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務創建的臨時文件文件數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服務器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了

默認大小是 32M，可調到64-256最佳，線程獨占，太大可能內存不夠I/O堵塞

跟臨時表相關的另一配置是max_heap_table_size ，
用戶可以創建的獨立的內存表所允許的最大容量.這個變量不適用與用戶創建的內存表(memory table).
SHOW VARIABLES LIKE '%max_heap_table_size%'; 咱們希望臨時表是放到內存的。所以這個值設置的臨時表緩存的空間一樣就行。

（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果臨時表大于這兩個的任何一個，都會存硬盤緩存：自動地把它轉化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下

?

五：索引緩沖區（key_buffer_size）配置

key_buffer_size是對MyISAM表性能影響最大的一個參數.key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads /key_read_requests應該盡可能的低，至少是1:100，1:1000更好

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。

?

      mysql> show variables like 'key_buffer_size'; 

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| key_buffer_size | 67108864 | 索引緩沖區的大小

+-----------------+----------+



show variables like 'key_cache_block_size';





mysql> show global status like 'key%';

+------------------------+------------+

| Variable_name | Value |

+------------------------+------------+

| Key_blocks_not_flushed | 0 |索引緩存內已經更改，但還沒有清空到硬盤上的索引的數據塊數量。

| Key_blocks_unused | 0 | 索引緩存內未使用的塊數量。你可以使用該值來確定使用了多少鍵緩存

| Key_blocks_used | 53585 | 索引緩存內使用的塊數量。該值為高水平線標記，說明已經同時最多使用了多少塊。

| Key_read_requests | 4952122733 | 一共有XXX個索引讀取請求，

| Key_reads | 11879 | 索引讀取請求在內存中沒有找到，直接從硬盤讀取索引

| Key_write_requests | 10508455 | 將索引的數據塊寫入緩存的請求數。

| Key_writes | 6042774 | 將索引向硬盤寫入數據塊的物理寫操作的次數。

+------------------------+------------+


    

比例key_reads /key_read_requests應該盡可能的低，至少是1:100，1:1000更好
如果Key_reads太大,則應該把my.cnf中key_buffer_size變大.可以用Key_reads/Key_read_requests計算出cache失敗率
Key_writes/Key_write_requests：比例接近1較好

別人的經驗是內存在4GB左右的服務器該參數可設置為384M或512M。可以自己算一下自己數據庫的索引文件大小。注意：該參數值設置的過大反而會是服務器整體效率降低!

Cache命中比率：

      1 - Key_reads / Key_read_requests　　
    

Key buffer的使用率

      100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )
    

mysql數據庫的安裝以及常見優化設置