?

本文根據(jù)優(yōu)才網(wǎng)課程整理，面向web開發(fā)者，內(nèi)容以實(shí)用為主，專業(yè)DBA可以繞行。

如果你在大公司，可能有專門的DBA來做這些事情，如果你在一個(gè)小公司當(dāng)架構(gòu)師或者技術(shù)總監(jiān)，或者你自己創(chuàng)業(yè)，那DBA的活你也得干了。咱們來講一下基本的mysql安裝和優(yōu)化。

?

一： MYSQL安裝和基本配置

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

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

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

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

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

現(xiàn)在數(shù)據(jù)庫裝好了， 帳號(hào)和權(quán)限也設(shè)置了，是不是就可以使用了呢,還要檢查幾個(gè)配置。打開配置文件 vim /etc/my.cnf

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

如果你的mysql經(jīng)過一段時(shí)間運(yùn)行，挺過了訪問高峰，咱們?cè)賮頇z查一下配置是否合適。下面說的配置，都必須是在運(yùn)行一段時(shí)間后檢查才有意義。如果剛啟動(dòng)沒多久，mysql的運(yùn)行狀態(tài)沒有代表性，不能作為參考。檢查配置參數(shù)也沒意義。

?

二：連接數(shù)(connection)配置

max_connections 可以設(shè)置最大并發(fā)連接數(shù)。當(dāng)MySql的并發(fā)連接達(dá)到這個(gè)設(shè)定值時(shí)，新的連接將會(huì)被拒絕（“Can not connect to MySQL server. Too many connections”-mysql 1040錯(cuò)誤，）。當(dāng)發(fā)現(xiàn)MySql有能力處理更多的并發(fā)的時(shí)候， 建議調(diào)大這個(gè)值，相應(yīng)給服務(wù)器帶來更高的負(fù)載(CPU/IO/內(nèi)存)。

查看設(shè)置的最大連接是多少：

      mysql> show variables like 'max_connections'; 

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

| Variable_name | Value |

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

| max_connections | 151 | 允許的最大連接數(shù)

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



看當(dāng)前連接數(shù)：show status like 'threads_connected';

最大連接數(shù): show status like "max_used_connections";


    

如果max_used_connections已經(jīng)接近 max_connections了，就說明max_connections太小。不合適了。

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

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

不同的操作系統(tǒng)在這個(gè)隊(duì)列大小上有它自己的限制。試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效的。默認(rèn)值為50。對(duì)于Linux系統(tǒng)推薦設(shè)置為小于512的整數(shù)。

如果鏈接數(shù)超過max_connections+back_log ，才會(huì)出錯(cuò)。

max_connect_errors=10
當(dāng)客戶端連接服務(wù)端超時(shí)(超過connect_timeout), 服務(wù)端就會(huì)給這個(gè)客戶端記錄一次error，當(dāng)出錯(cuò)的次數(shù)達(dá)到max_connect_errors的時(shí)候，這個(gè)客戶端就會(huì)被鎖定。除非執(zhí)行FLUSH HOSTS命令。

connect_timeout=5
連接超時(shí)的秒數(shù)

?

三：查詢緩存(query_cache)配置

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

MYSQL的查詢緩存用于緩存select查詢結(jié)果，并在下次接收到同樣的查詢請(qǐng)求時(shí)，不再執(zhí)行實(shí)際查詢處理而直接 返回結(jié)果，有這樣的查詢緩存能提高查詢的速度，使查詢性能得到優(yōu)化.

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

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

查看查詢緩存的設(shè)置：

      SHOW VARIABLES LIKE '%query_cache%';

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

| Variable_name | Value |

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

| have_query_cache | YES |

| query_cache_limit | 1048576 | 如果單個(gè)查詢結(jié)果大于這個(gè)值，則不Cache

| query_cache_min_res_unit | 4096 | 每次給QC結(jié)果分配內(nèi)存的大小

| query_cache_size | 33554432 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

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



query_cache_type=1


    

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

query_cache_size 默認(rèn)是32M，太小了，可調(diào)到128M或者256M。 可以通過Qcache_lowmem_prunes變量的值來檢查是否當(dāng)前的值滿足你目前系統(tǒng)的負(fù)載。

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

Qcache_lowmem_prunes可以檢查是否設(shè)置的太小。

query_cache_limit 默認(rèn)是1M，根據(jù)你的常用查詢的數(shù)據(jù)結(jié)果大小來定。如果返回的數(shù)據(jù)小，可以設(shè)置小一點(diǎn)。
設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)

查看緩存使用效果如何：

      show status like '%Qcache%';

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

| Variable_name | Value |

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

| Qcache_free_blocks | 160 | 目前還處于空閑狀態(tài)的 Query Cache中內(nèi)存 Block 數(shù)目，數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會(huì)對(duì)緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊。

| Qcache_free_memory | 23147296 | 緩存中的空閑內(nèi)存總量。

| Qcache_hits | 52349 | 緩存命中次數(shù)。

| Qcache_inserts | 8827 | 緩存失效次數(shù)。

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

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

| Qcache_queries_in_cache | 5234 | 當(dāng)前被cache的SQL數(shù)量。

| Qcache_total_blocks | 10796 | 緩存中塊的數(shù)量。

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



show global status like 'Com_select'; 

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

| Variable_name | Value |

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

| Com_select | 12592 | com_select 變量記錄的是無緩存的查詢次數(shù)+錯(cuò)誤查詢+權(quán)限檢查查詢。

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


    

Mysql的查詢緩存命中率沒有官方算法，只有前人的經(jīng)驗(yàn)總結(jié)
命中率 ≈ qcache_hits / (qcache_hits + com_select)

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

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

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

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

mysql進(jìn)行復(fù)雜查詢或者 做高級(jí)GROUP BY操作的時(shí)候，系統(tǒng)為了優(yōu)化查詢，生成一些臨時(shí)表。通過設(shè)置tmp_table_size選項(xiàng)來設(shè)置臨時(shí)表占用空間的大小。

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

MySQL臨時(shí)表分為“內(nèi)存臨時(shí)表”和“磁盤臨時(shí)表”，其中內(nèi)存臨時(shí)表使用MySQL的MEMORY存儲(chǔ)引擎，磁盤臨時(shí)表使用MySQL的MyISAM存儲(chǔ)引擎；

一般情況下，MySQL會(huì)先創(chuàng)建內(nèi)存臨時(shí)表，但內(nèi)存臨時(shí)表超過配置指定的值后，MySQL會(huì)將內(nèi)存臨時(shí)表導(dǎo)出到磁盤臨時(shí)表

臨時(shí)表將在你連接MySQL期間存在。當(dāng)你斷開時(shí)，MySQL將自動(dòng)刪除表并釋放所用的空間。

      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 | 服務(wù)器執(zhí)行語句時(shí)在硬盤上自動(dòng)創(chuàng)建的臨時(shí)表的數(shù)量

| Created_tmp_files | 755473 | mysqld已經(jīng)創(chuàng)建的臨時(shí)文件的數(shù)量

| Created_tmp_tables | 14372959 | 服務(wù)器執(zhí)行語句時(shí)自動(dòng)創(chuàng)建的內(nèi)存中的臨時(shí)表的數(shù)量。如果Created_tmp_disk_tables較大，你可能要增加tmp_table_size值使臨時(shí) 表基于內(nèi)存而不基于硬盤

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


    

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

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應(yīng)該相當(dāng)好了

默認(rèn)大小是 32M，可調(diào)到64-256最佳，線程獨(dú)占，太大可能內(nèi)存不夠I/O堵塞

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

（實(shí)際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果臨時(shí)表大于這兩個(gè)的任何一個(gè)，都會(huì)存硬盤緩存：自動(dòng)地把它轉(zhuǎn)化為基于磁盤的MyISAM表，存儲(chǔ)在指定的tmpdir目錄下

?

五：索引緩沖區(qū)（key_buffer_size）配置

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

key_buffer_size只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態(tài)值created_tmp_disk_tables得知詳情。

?

      mysql> show variables like 'key_buffer_size'; 

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

| Variable_name | Value |

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

| key_buffer_size | 67108864 | 索引緩沖區(qū)的大小

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



show variables like 'key_cache_block_size';





mysql> show global status like 'key%';

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

| Variable_name | Value |

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

| Key_blocks_not_flushed | 0 |索引緩存內(nèi)已經(jīng)更改，但還沒有清空到硬盤上的索引的數(shù)據(jù)塊數(shù)量。

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

| Key_blocks_used | 53585 | 索引緩存內(nèi)使用的塊數(shù)量。該值為高水平線標(biāo)記，說明已經(jīng)同時(shí)最多使用了多少塊。

| Key_read_requests | 4952122733 | 一共有XXX個(gè)索引讀取請(qǐng)求，

| Key_reads | 11879 | 索引讀取請(qǐng)求在內(nèi)存中沒有找到，直接從硬盤讀取索引

| Key_write_requests | 10508455 | 將索引的數(shù)據(jù)塊寫入緩存的請(qǐng)求數(shù)。

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

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


    

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

別人的經(jīng)驗(yàn)是內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為384M或512M。可以自己算一下自己數(shù)據(jù)庫的索引文件大小。注意：該參數(shù)值設(shè)置的過大反而會(huì)是服務(wù)器整體效率降低!

Cache命中比率：

      1 - Key_reads / Key_read_requests　　
    

Key buffer的使用率

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

mysql數(shù)據(jù)庫的安裝以及常見優(yōu)化設(shè)置