MYSQL調(diào)優(yōu)

硬件設(shè)備

1、多CPU多Core,內(nèi)存永遠(yuǎn)不嫌大

2、使用RAID10多磁盤提IO能力或者用NAS,SAN

3、RAID要帶電池、緩存,數(shù)據(jù)庫服務(wù)需要帶UPS(在線電源)

4、全千兆網(wǎng)絡(luò)環(huán)境

系統(tǒng)調(diào)優(yōu)

1、全部采用64位版本(64MYSQL)

2、選擇穩(wěn)定內(nèi)核(權(quán)衡穩(wěn)定,性能,功能)

3、調(diào)整系統(tǒng)默認(rèn)配置參數(shù)(例如TCP/IP堆棧連接數(shù)),文件句柄數(shù)／進(jìn)程個數(shù)

# vim /etc/sysctl.conf

net.ipv4.ip_local_port_range = 32768 61000

# vim /etc/security/limits.conf--需要重新登錄mysql

mysql soft nofile 65535

mysql hard nofile 65535

mysql soft nproc 65535

mysql hard nproc 65535

# ulimit -HSn 65535

MySQL 安裝優(yōu)化

選擇合適的發(fā)行版本

1. 二進(jìn)制發(fā)行版(包括RPM 等包裝好的特定二進(jìn)制版本)

由于MySQL 開源的特性,不僅僅MySQL AB 提供了多個平臺上面的多種二進(jìn)制發(fā)行版本可以供大家選擇,還有不少第三方公司(或者個人)也給我們提供了不少選擇.(rpm/glibc)

使用MySQL AB 提供的二進(jìn)制發(fā)行版本我們可以得到哪些好處?

a) 通過非常簡單的安裝方式快速完成MySQL 的部署；

b) 安裝版本是經(jīng)過比較完善的功能和性能測試的編譯版本；

c) 所使用的編譯參數(shù)更具通用性的,且比較穩(wěn)定；

d) 如果購買了MySQL 的服務(wù),將能最大程度的得到MySQL 的技術(shù)支持；

2. 源碼安裝

與二進(jìn)制發(fā)行版本相比,如果我們選擇了通過源代碼進(jìn)行安裝,那么在安裝過程中我們能夠?qū)ySQL所做的調(diào)整將會更多更靈活一些.因為通過源代碼編譯我們可以：

a) 針對自己的硬件平臺選用合適的編譯器來優(yōu)化編譯后的二進(jìn)制代碼；

b) 根據(jù)不同的軟件平臺環(huán)境調(diào)整相關(guān)的編譯參數(shù)；

c) 針對我們特定應(yīng)用場景選擇需要什么組件不需要什么組件；

d) 根據(jù)我們的所需要存儲的數(shù)據(jù)內(nèi)容選擇只安裝我們需要的字符集；

e) 同一臺主機(jī)上面可以安裝多個MySQL；

f) 等等其他一些可以根據(jù)特定應(yīng)用場景所作的各種調(diào)整.

在源碼安裝給我們帶來更大靈活性的同時,同樣也給我們帶來了可能引入的隱患：

a) 對編譯參數(shù)的不夠了解造成編譯參數(shù)使用不當(dāng)可能使編譯出來的二進(jìn)制代碼不夠穩(wěn)定；

b) 對自己的應(yīng)用環(huán)境把握失誤而使用的優(yōu)化參數(shù)可能反而使系統(tǒng)性能更差；

c) 還有一個并不能稱之為隱患的小問題就是源碼編譯安裝將使安裝部署過程更為復(fù)雜,所花費的時間更長；

重要的編譯參數(shù)的介紹：

● “prefix”：設(shè)定安裝路徑,默認(rèn)為“/usr/local”；

● “datadir”：設(shè)定MySQL 數(shù)據(jù)文件存放路徑；

● “with-charset”：設(shè)定系統(tǒng)的默認(rèn)字符集；

● “with-collation”：系統(tǒng)默認(rèn)的校驗編碼的規(guī)則；

● “with-extra-charsets”：出了默認(rèn)字符集之外需要編譯安裝的字符集；

● “with-unix-socket-path”：設(shè)定socket 文件地址；

● “with-tcp-port”：指定特定監(jiān)聽端口,默認(rèn)為3306；

● “with-mysqld-user”：指定運行mysqld 的os 用戶,默認(rèn)為mysql；

● “without-query-cache”：禁用Query Cache 功能；

● “without-innodb”：禁用Innodb 存儲引擎；

● “--enable-thread-safe-client”：以線程方式編譯客戶端；

● “with-pthread”：強(qiáng)制使用pthread 線程庫編譯；

● “with-named-thread-libs”：指定使用某個特定的線程庫編譯；

● “without-debug”：使用非debug 模式；

● “with-mysqld-ldflags”：mysqld 的額外link 參數(shù)；

● “with-client-ldflags”：client 的額外link 參數(shù)；

MySQL 日志設(shè)置優(yōu)化

MySQL 的日志包括(分離日志文件和數(shù)據(jù)文件,把它們放不同存儲)：

1、錯誤日志(ErrorLog)

2、二進(jìn)制日志(Binlog)

3、查詢?nèi)罩?Query Log)

4、慢查詢?nèi)罩?Slow Query Log)

5、事務(wù)日志 tr_logs(3group)-->innodb

1、在默認(rèn)情況下,系統(tǒng)僅僅打開錯誤日志,關(guān)閉了其他所有日志,以達(dá)到盡可能減少IO 損耗提高系統(tǒng)性能的目的.

2、在生產(chǎn)系統(tǒng)中很少有系統(tǒng)會打開查詢?nèi)罩?因為查詢?nèi)罩敬蜷_之后會將MySQL 中執(zhí)行的每一條Query 都記錄到日志中,會該系統(tǒng)帶來比較大的IO 負(fù)擔(dān),而帶來的實際效益卻并不是非常大.一般只有在開發(fā)測試環(huán)境中,為了定位某些功能具體使用了哪些SQL 語句的時候,才會在短時間段內(nèi)打開該日志來做相應(yīng)的分析.

3、在重要一點的實際應(yīng)用場景中,都至少需要打開二進(jìn)制日志,因為這是MySQL很多存儲引擎進(jìn)行增量備份的基礎(chǔ),也是MySQL 實現(xiàn)復(fù)制的基本條件.

4、為了調(diào)整mysql性能,定位執(zhí)行較慢的SQL 語句,很多系統(tǒng)也會打開慢查詢?nèi)罩緛碛涗泩?zhí)行時間超過特定數(shù)值(由我們自行設(shè)置)的SQL 語句.

調(diào)優(yōu)方法：

1、把日志放在單獨的存儲上,提升mysql IO的性能.

2、Binlog 相關(guān)參數(shù)及優(yōu)化策略：

mysql> show variables like '%binlog%';

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

| Variable_name | Value |

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

| binlog_cache_size | 1048576 |

| innodb_locks_unsafe_for_binlog | OFF |

| max_binlog_cache_size | 4294967295|

| max_binlog_size | 1073741824|

| sync_binlog | 0 |

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

“binlog_cache_size"：在對數(shù)據(jù)增刪改的過程中容納二進(jìn)制日志SQL 語句的緩存大小.

“max_binlog_cache_size”：和"binlog_cache_size"相對應(yīng),但是所代表的是binlog 能夠使用的最大cache 內(nèi)存大小

“max_binlog_size”：Binlog 日志最大值,一般來說設(shè)置為512M 或者1G,但不能超過1G.

“sync_binlog”：這個參數(shù)是對于MySQL 系統(tǒng)來說是至關(guān)重要的,他不僅影響到Binlog 對MySQL 所帶來的性能損耗,而且還影響到MySQL 中數(shù)據(jù)的完整性.

a)sync_binlog=0,當(dāng)事務(wù)提交之后,MySQL 不做fsync 之類的磁盤同步指令刷新binlog_cache 中的信息到磁盤,而讓Filesystem 自行決定什么時候來做同步,或者cache 滿了之后才同步到磁盤.

b)sync_binlog=n,當(dāng)每進(jìn)行n 次事務(wù)提交之后,MySQL 將進(jìn)行一次fsync 之類的磁盤同步指令來將binlog_cache 中的數(shù)據(jù)強(qiáng)制寫入磁盤.

c),“sync_binlog”設(shè)置為0 和設(shè)置為1 的系統(tǒng)寫入性能差距可能高達(dá)5 倍甚至更多.

以下參數(shù)可以讓我們控制需要復(fù)制或者需要忽略而不進(jìn)行復(fù)制的DB 或者Table 的,分別為：

● Binlog_Do_DB：設(shè)定哪些數(shù)據(jù)庫(Schema)需要記錄Binlog；

● Binlog_Ignore_DB：設(shè)定哪些數(shù)據(jù)庫(Schema)不要記錄Binlog；

● Replicate_Do_DB：設(shè)定需要復(fù)制的數(shù)據(jù)庫(Schema),多個DB 用逗號(“,”)分隔；

● Replicate_Ignore_DB：設(shè)定可以忽略的數(shù)據(jù)庫(Schema)；

● Replicate_Do_Table：設(shè)定需要復(fù)制的Table；

● Replicate_Ignore_Table：設(shè)定可以忽略的Table；

3、Slow Query Log 相關(guān)參數(shù)及使用建議

mysql> show variables like 'log_slow%';

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

| Variable_name | Value |

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

| log_slow_queries | ON |

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

1 row in set (0.00 sec)

mysql> show variables like 'long_query%';

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

| Variable_name | Value |

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

| long_query_time | 1 |

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

1 row in set (0.01 sec)

log-slow-query=on

slow_query_log_file=/data/mysqld-slow.log

log-slow-admin-statements

log-queries-not-using-indexes

long_query_time=1 －－超過1秒的查詢都會被記錄下來

log-short-format －－短格式記錄慢日志

Query Cache 優(yōu)化(所有存儲引擎都支持查詢緩存):

查詢緩存：

MySQL 的Query Cache 實現(xiàn)原理實際上并不是特別的復(fù)雜,簡單的來說就是將客戶端請求的Query語句(當(dāng)然僅限于SELECT 類型的Query)通過一定的hash 算法進(jìn)行一個計算而得到一個hash 值,存放在一個hash 桶中.同時將該Query 的結(jié)果集(Result Set)也存放在一個內(nèi)存Cache 中的.存放Queryhash 值的鏈表中的每一個hash 值所在的節(jié)點中同時還存放了該Query 所對應(yīng)的Result Set 的Cache 所在的內(nèi)存地址,以及該Query 所涉及到的所有Table 的標(biāo)識等其他一些相關(guān)信息.系統(tǒng)接受到任何一個SELECT 類型的Query 的時候,首先計算出其hash 值,然后通過該hash 值到Query Cache 中去匹配,如果找到了完全相同的Query,則直接將之前所Cache 的Result Set 返回給客戶端而完全不需要進(jìn)行后面的任何步驟即可完成這次請求.而后端的任何一個表的任何一條數(shù)據(jù)發(fā)生變化之后,也會通知QueryCache,需要將所有與該Table 有關(guān)的Query 的Cache 全部失效,并釋放出之前占用的內(nèi)存地址,以便后面其他的Query 能夠使用.

－－不適合數(shù)據(jù)更改頻繁的應(yīng)用場景

Query Cache 的相關(guān)系統(tǒng)參數(shù)變量和狀態(tài)變量

mysql> show variables like '%query_cache%';

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

| Variable_name | Value|

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

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 268435456 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

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

● “have_query_cache”：該MySQL 是否支持Query Cache；

● “query_cache_limit”：Query Cache 存放的單條Query 最大Result Set ,默認(rèn)1M；

● “query_cache_min_res_unit”：Query Cache 每個Result Set 存放的最小內(nèi)存大小,默認(rèn)4k；

● “query_cache_size”：系統(tǒng)中用于Query Cache 內(nèi)存的大小；

● “query_cache_type”：系統(tǒng)是否打開了Query Cache 功能；

● “query_cache_wlock_invalidate”：針對于MyISAM 存儲引擎,設(shè)置當(dāng)有WRITE LOCK

如果我們要了解Query Cache 的使用情況,則可以通過Query Cache 相關(guān)的狀態(tài)變量來獲取,如通過如下命令：

mysql> show status like 'Qcache%';

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

| Variable_name | Value |

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

| Qcache_free_blocks | 7499 |

| Qcache_free_memory | 190662000 |

| Qcache_hits | 1888430018|

| Qcache_inserts | 1014096388 |

| Qcache_lowmem_prunes | 106071885 |

| Qcache_not_cached | 7951123988 |

| Qcache_queries_in_cache | 19315 |

| Qcache_total_blocks | 47870 |

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

● “Qcache_free_blocks”：Query Cache 中目前還有多少剩余的blocks.如果該值顯示較大,則說明Query Cache 中的內(nèi)存碎片較多了,可能需要尋找合適的機(jī)會進(jìn)行整理().

● “Qcache_free_memory”：Query Cache 中目前剩余的內(nèi)存大小.通過這個參數(shù)我們可以較為準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的Query Cache 內(nèi)存大小是否足夠,是需要增加還是過多了；

● “Qcache_hits”：多少次命中.通過這個參數(shù)我們可以查看到Query Cache 的基本效果；

● “Qcache_inserts”：多少次未命中然后插入.通過“Qcache_hits”和“Qcache_inserts”兩個參數(shù)我們就可以算出Query Cache 的命中率了：Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；

● “Qcache_lowmem_prunes”：多少條Query 因為內(nèi)存不足而被清除出Query Cache.通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合,能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠,是否非常頻繁的出現(xiàn)因為內(nèi)存不足而有Query 被換出

● “Qcache_not_cached”：因為query_cache_type 的設(shè)置或者不能被cache 的Query 的數(shù)量；

● “Qcache_queries_in_cache”：當(dāng)前Query Cache 中cache 的Query 數(shù)量；

● “Qcache_total_blocks”：當(dāng)前Query Cache 中的block 數(shù)量；

Query Cache 的限制

a) 5.1.17 之前的版本不能Cache 綁定變量的Query,但是從5.1.17 版本開始,Query Cache 已經(jīng)開始支持幫定變量的Query 了；

b) 所有子查詢中的外部查詢SQL 不能被Cache；

c) 在Procedure,Function 以及Trigger 中的Query 不能被Cache；

d) 包含其他很多每次執(zhí)行可能得到不一樣結(jié)果的函數(shù)的Query 不能被Cache.

MySQL Server 其它常用優(yōu)化：

1、網(wǎng)絡(luò)連接優(yōu)化：

● max_conecctions：整個MySQL 允許的最大連接數(shù)；

這個參數(shù)主要影響的是整個MySQL 應(yīng)用的并發(fā)處理能力,當(dāng)系統(tǒng)中實際需要的連接量大于max_conecctions 的情況下,由于MySQL 的設(shè)置限制,那么應(yīng)用中必然會產(chǎn)生連接請求的等待,從而限制了相應(yīng)的并發(fā)量.所以一般來說,只要MySQL 主機(jī)性能允許,都是將該參數(shù)設(shè)置的盡可能大一點.一般來說500 到800 左右是一個比較合適的參考值

● max_user_connections：每個用戶允許的最大連接數(shù)；

上面的參數(shù)是限制了整個MySQL 的連接數(shù),而max_user_connections 則是針對于單個用戶的連接限制.在一般情況下我們可能都較少使用這個限制,只有在一些專門提供MySQL 數(shù)據(jù)存儲服務(wù),或者是提供虛擬主機(jī)服務(wù)的應(yīng)用中可能需要用到.除了限制的對象區(qū)別之外,其他方面和max_connections 一樣.這個參數(shù)的設(shè)置完全依賴于應(yīng)用程序的連接用戶數(shù),對于普通的應(yīng)用來說,完全沒有做太多的限制,可以盡量放開一些.

● back_log：在MySQL 的連接請求等待隊列中允許存放的最大連接請求數(shù).

連接請求等待隊列,實際上是指當(dāng)某一時刻客戶端的連接請求數(shù)量過大的時候,MySQL 主線程沒辦法及時給每一個新的連接請求分配(或者創(chuàng)建)連接線程的時候,還沒有分配到連接線程的所有請求將存放在一個等待隊列中,這個隊列就是MySQL 的連接請求隊列.當(dāng)我們的系統(tǒng)存在瞬時的大量連接請求的時候,則應(yīng)該注意back_log 參數(shù)的設(shè)置.系統(tǒng)默認(rèn)值為50,最大可以設(shè)置為65535.當(dāng)我們增大back_log 的設(shè)置的時候,同時還需要主義OS 級別對網(wǎng)絡(luò)監(jiān)聽隊列的限制,因為如果OS 的網(wǎng)絡(luò)監(jiān)聽設(shè)置小于MySQL 的back_log 設(shè)置的時候,我們加大“back_log”設(shè)置是沒有意義的.

2、線程連接優(yōu)化(session/會話)：

● thread_cache_size：Thread Cache 池中應(yīng)該存放的連接線程數(shù).

當(dāng)系統(tǒng)最初啟動的時候,并不會馬上就創(chuàng)建thread_cache_size 所設(shè)置數(shù)目的連接線程存放在Thread Cache 池中,而是隨著連接線程的創(chuàng)建及使用,慢慢的將用完的連接線程存入其中.當(dāng)存放的連接線程達(dá)到thread_cache_size 值之后,MySQL 就不會再續(xù)保存用完的連接線程了.

● thread_stack：每個連接線程被創(chuàng)建的時候,MySQL 給他分配的內(nèi)存大小.

當(dāng)MySQL 創(chuàng)建一個新的連接線程的時候,是需要給他分配一定大小的內(nèi)存堆棧空間,以便存放客戶端的請求Query 以及自身的各種狀態(tài)和處理信息.不過一般來說如果不是對MySQL 的連接線程處理機(jī)制十分熟悉的話,不應(yīng)該輕易調(diào)整該參數(shù)的大小,使用系統(tǒng)的默認(rèn)值(192KB)基本上可以所有的普通應(yīng)用環(huán)境.如果該值設(shè)置太小,會影響MySQL 連接線程能夠處理客戶端請求的Query 內(nèi)容的大小,以及用戶創(chuàng)建的Procedures 和Functions 等.

我們現(xiàn)看看連接線程相關(guān)的系統(tǒng)變量的設(shè)置值：

mysql> show variables like 'thread%';

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

| Variable_name | Value |

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

| thread_cache_size | 64 |

| thread_stack | 196608 |

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

再來看一下系統(tǒng)被連接的次數(shù)以及當(dāng)前系統(tǒng)中連接線程的狀態(tài)值：

mysql> show status like 'connections';

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

| Variable_name | Value |

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

| Connections | 127 |

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

mysql> show status like '%thread%';

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

| Variable_name | Value |

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

| Delayed_insert_threads | 0 |

| Slow_launch_threads | 0 |

| Threads_cached | 4 |

| Threads_connected | 7 |

| Threads_created | 11 |

| Threads_running | 1 |

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

通過上面的命令,我們可以看出,系統(tǒng)設(shè)置了Thread Cache 池最多將緩存64 個連接線程,每個連接線程創(chuàng)建之初,系統(tǒng)分配192KB 的內(nèi)存堆棧空間給他.系統(tǒng)啟動到現(xiàn)在共接收到客戶端的連接127 次,共創(chuàng)建了11 個連接線程,但前有7 個連接線程處于和客戶端連接的狀態(tài),而7 個連接狀態(tài)的線程中只有一個是active 狀態(tài),也就是說只有一個正在處理客戶端提交的俄請求.而在Thread Cache 池中當(dāng)共Cache 了4 個連接線程.

ThreadCache 命中率,也就是通過Thread Cache 池中取得連接線程的次數(shù)與系統(tǒng)接收的總連接次數(shù)的比率,如下：

Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%

我們可以通過上面的這個運算公式計算一下上面環(huán)境中的Thread Cache 命中率：Thread_Cache_Hit= (127 - 12) / 127 * 100% = 90.55%

常用存儲引擎優(yōu)化：

1、MyI SAM存儲引擎優(yōu)化(查詢緩存＋索引緩存)

2、Innodb存儲引擎優(yōu)化(查詢緩存＋innodb_buffer_pool(索引/修改數(shù)據(jù)))

物理塊－－＞表－－＞索引(硬盤)－－＞索引緩存(buffer)－－＞查詢緩存(cache)－－＞查詢

1、MyI SAM存儲引擎優(yōu)化(所有寫操作都是直接操作物理磁盤,所以只能優(yōu)化它的查詢功能)

1)MyISAM 索引緩存相關(guān)的幾個系統(tǒng)參數(shù)和狀態(tài)參數(shù)：

◆ key_buffer_size,索引緩存大小；

這個參數(shù)用來設(shè)置整個MySQL 中的常規(guī)Key Cache 大小.一般來說,如果我們的MySQL 是運行在32 位平臺上,此值建議不要超過2GB 大小.如果是運行在64 位平臺紙上則不用考慮此限制,但也最好不要超過4GB.

◆ key_buffer_block_size,索引緩存中的Cache Block Size；

在前面我們已經(jīng)介紹了,在Key Cache 中的所有數(shù)據(jù)都是以Cache Block 的形式存在,而key_buffer_block_size 就是設(shè)置每個Cache Block 的大小,實際上也同時限定了我們將“.MYI”文件中的Index Block 被讀入時候的File Block 的大小.

2、Innodb存儲引擎優(yōu)化

1)Innodb緩存相關(guān)優(yōu)化

2)事務(wù)優(yōu)化

3)數(shù)據(jù)存儲優(yōu)化

1)Innodb緩存相關(guān)優(yōu)化

innodb_buffer_pool_size 參數(shù)用來設(shè)置Innodb 最主要的Buffer(Innodb_Buffer_Pool)的大小,也就是緩存用戶表及索引數(shù)據(jù)的最主要緩存空間,對Innodb 整體性能影響也最大.官方建議將Innodb 的Buffer Pool 設(shè)置為整個系統(tǒng)物理內(nèi)存的50% ～ 80% 之間.

mysql> show status like 'Innodb_buffer_pool_%';

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

| Variable_name | Value |

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

| Innodb_buffer_pool_pages_data | 70 |

| Innodb_buffer_pool_pages_dirty | 0 |

| Innodb_buffer_pool_pages_flushed | 0 |

| Innodb_buffer_pool_pages_free | 1978|

| Innodb_buffer_pool_pages_latched | 0 |

| Innodb_buffer_pool_pages_misc | 0 |

| Innodb_buffer_pool_pages_total | 2048 |

| Innodb_buffer_pool_read_ahead_rnd | 1 |

| Innodb_buffer_pool_read_ahead_seq | 0 |

| Innodb_buffer_pool_read_requests | 329 |

| Innodb_buffer_pool_reads | 19 |

| Innodb_buffer_pool_wait_free | 0 |

| Innodb_buffer_pool_write_requests | 0 |

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

從上面的值我們可以看出總共2048 pages,還有1978 是Free 狀態(tài)的僅僅只有70 個page 有數(shù)據(jù),read 請求329 次,其中有19 次所請求的數(shù)據(jù)在buffer pool 中沒有,也就是說有19 次是通過讀取物理磁盤來讀取數(shù)據(jù)的,所以很容易也就得出了Innodb Buffer Pool 的Read 命中率大概在為：(329 - 19)/ 329 * 100% = 94.22%.

innodb_additional_mem_pool_size 所設(shè)置的是用于存放Innodb 的字典信息和其他一些內(nèi)部結(jié)構(gòu)所需要的內(nèi)存空間.所以我們的Innodb 表越多,所需要的空間自然也就越大,系統(tǒng)默認(rèn)值僅有1MB.當(dāng)然,如果Innodb 實際運行過程中出現(xiàn)了實際需要的內(nèi)存比設(shè)置值更大的時候,Innodb 也會繼續(xù)通過OS來申請內(nèi)存空間,一個常規(guī)的幾百個Innodb 表的MySQL,如果不是每個表都是上百個字段的話,20MB 內(nèi)存已經(jīng)足夠了.

2)事務(wù)優(yōu)化

◆ innodb_flush_log_at_trx_commit = 0,Innodb 中的Log Thread 每隔1 秒鐘會將log buffer中的數(shù)據(jù)寫入到文件,同時還會通知文件系統(tǒng)進(jìn)行文件同步的flush 操作,保證數(shù)據(jù)確實已經(jīng)寫入到磁盤上面的物理文件.但是,每次事務(wù)的結(jié)束(commit 或者是rollback)并不會觸發(fā)Log Thread 將log buffer 中的數(shù)據(jù)寫入文件.所以,當(dāng)設(shè)置為0 的時候,當(dāng)MySQL Crash 和OS Crash 或者主機(jī)斷電之后,最極端的情況是丟失1 秒時間的數(shù)據(jù)變更.(fsync)

◆ innodb_flush_log_at_trx_commit = 1,這也是Innodb 的默認(rèn)設(shè)置.我們每次事務(wù)的結(jié)束都會觸發(fā)Log Thread 將log buffer 中的數(shù)據(jù)寫入文件并通知文件系統(tǒng)同步文件.這個設(shè)置是最安全的設(shè)置,能夠保證不論是MySQL Crash 還是OS Crash 或者是主機(jī)斷電都不會丟失任何已經(jīng)提交的數(shù)據(jù).(fsync)

◆ innodb_flush_log_at_trx_commit = 2,當(dāng)我們設(shè)置為2 的時候,Log Thread 會在我們每次事務(wù)結(jié)束的時候?qū)?shù)據(jù)寫入事務(wù)日志,但是這里的寫入僅僅是調(diào)用了文件系統(tǒng)的文件寫入操作.而我們的文件系統(tǒng)都是有緩存機(jī)制的,所以Log Thread 的這個寫入并不能保證內(nèi)容真的已經(jīng)寫入到物理磁盤上面完成持久化的動作.文件系統(tǒng)什么時候會將緩存中的這個數(shù)據(jù)同步到物理磁盤文件Log Thread 就完全不知道了.所以,當(dāng)設(shè)置為2 的時候,MySQL Crash 并不會造成數(shù)據(jù)的丟失,但是OS Crash 或者是主機(jī)斷電后可能丟失的數(shù)據(jù)量就完全控制在文件系統(tǒng)上了.(async)

3)數(shù)據(jù)存儲優(yōu)化

索引規(guī)劃原則：

1、不使用唯一索引

2. 為了盡量減小secondary index 的大小,提高訪問效率,作為主鍵的字段所占用的存儲空間越小越好,最好是INTEGER 類型.當(dāng)然這并不是絕對的,字符串類型的數(shù)據(jù)同樣也可以作為Innodb 表的主鍵；

3. 創(chuàng)建表的時候盡量自己指定相應(yīng)的主鍵,讓數(shù)據(jù)按照自己預(yù)設(shè)的順序排序存放,提高特定條件下的訪問效率；

4. 盡可能不要在主鍵上面進(jìn)行更新操作,減少因為主鍵值的變化帶來數(shù)據(jù)的移動.

5. 盡可能提供主鍵條件進(jìn)行查詢；

分散IO 提升磁盤響應(yīng)：

建議將數(shù)據(jù)文件和事務(wù)日志文件分別存放于不同的物理磁盤上面以降低磁盤的相互爭用,提高整體IO 性能.

可以通過以下兩個參數(shù)指定：

innodb_log_group_home_dir 參數(shù)來指定Innodb 日志存放位置,同時再通過設(shè)置數(shù)據(jù)文件位置

innodb_data_home_dir 參數(shù)來告訴Innodb 我們希望將數(shù)據(jù)文件存放在哪里.

innodb_autoextend_increment 參數(shù)讓我們可以自行控制表空間文件每次增加的大小.

skip-locking－－忽略外部文件鎖定(如VFS層的鎖定)

skip-networking－－啟動mysql時不啟動網(wǎng)絡(luò)模塊,只允許通過socket連接.

編寫常用my.cnf配置文件：

# mysql -N -s -uroot -p456 -e "show variables;" > /root/mysql.cnf

# cat /root/mysql.cnf | sed 's/_/-/g' |awk '{print $1"="$2}' > /root/my.cnf