MySQL數(shù)據(jù)庫如何實現(xiàn)正常優(yōu)化

下文主要給大家?guī)?a title="MySQL" target="_blank" href="http://www.kemok4.com/mysql/">MySQL數(shù)據(jù)庫如何實現(xiàn)正常優(yōu)化，希望這些內(nèi)容能夠帶給大家實際用處，這也是我編輯MySQL數(shù)據(jù)庫如何實現(xiàn)正常優(yōu)化這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

MySQL數(shù)據(jù)庫優(yōu)化：

前言：
在一個網(wǎng)站架構(gòu)中，首先出現(xiàn)瓶頸的一定是數(shù)據(jù)庫，其次是存儲。
1、硬盤優(yōu)化：不用虛擬機，用物理機（因為數(shù)據(jù)庫是 IO 密集型的應(yīng)用）
a、CPU   64位CPU，百度為例：一臺機器 8-16 顆 CPU。普通公司：2-4顆 cpu。
b、內(nèi)存（mem） 百度為例： 96G-128G，3-4個實例。普通公司：32G-64G，跑2個實例。
c、磁盤（disk）   數(shù)量越多越好。性能：SSD（高并發(fā)） > SAS（普通業(yè)務(wù)線上） > SATA（線下）
raid   4塊盤：raid0 > raid10 > raid5 > raid1
d、網(wǎng)卡   多塊網(wǎng)卡bond，以及buffer，tcp優(yōu)化。
2、軟件優(yōu)化
操作系統(tǒng)：x86_64系統(tǒng)
軟件：mysql 編譯優(yōu)化
3、my.cnf 里參數(shù)的優(yōu)化
注意：my.cnf 里參數(shù)優(yōu)化的幅度很小，大部分架構(gòu)以及SQL語句優(yōu)化。
思想：
監(jiān)控：生產(chǎn)參數(shù)是一般情況下參數(shù)。
命令監(jiān)控：show global status\G
調(diào)優(yōu)工具：mysqlreport
案例：一些MySQL的錯誤skip-name-resolve：
http://blog.chinaunix.net/uid-7354272-id-2643611.html
4、SQL語句的優(yōu)化
a、索引優(yōu)化
1）、白名單機制---百度，項目開發(fā)，DBA參與，減少上線后的慢SQL數(shù)量。
抓出慢SQL，配置 my.cnf
long_query_time = 2
log-slow-queries=/data/3306/slow-log.log
按天輪詢：slow-log.log
2）、慢查詢?nèi)罩痉治龉ぞ?----mysqlsla（推薦）
MySQLdumpslow，mysqlsla，myprofi，mysql-explain-slow-log，mysqllogfilter 比較
3）、每天晚上0點定時分析慢查詢，發(fā)到核心開發(fā)，DBA分析及高級運維，CTO的郵箱里。
DBA分析給出優(yōu)化建議--->核心開發(fā)確認(rèn)更改--->DBA線上操作處理。
b、大的復(fù)雜的SQL語句拆分成多個小的SQL語句。
子查詢，join連表查詢，某個表4000萬條記錄。
c、數(shù)據(jù)庫是存儲數(shù)據(jù)的地方，但是不是計算數(shù)據(jù)的地方。    
對數(shù)據(jù)計算，應(yīng)用類處理，都要拿到前端應(yīng)用解決。禁止在數(shù)據(jù)庫上處理。
d、搜索功能，like‘%老男孩%’，一般不要用mysql 數(shù)據(jù)庫。
SQL 語句的詳細(xì)優(yōu)化細(xì)節(jié)：
① 能用定長 char 類型的就不用 varchar 類型。
② 數(shù)據(jù)庫查詢盡量不用 select ，除非要查所有字段。
mysql> select id,name from test;  不用mysql> select  from test;
③ select 查詢的時候，where 條件后面的列類型如果是字符串類型就要加引號，如果是數(shù)字類型就不要加引號。
④ 如果一個條件列的前 n 個字符已經(jīng)接近唯一值，就可以對一個列的前 n 個字符創(chuàng)建索引，不需要對整個列創(chuàng)建索引了。
⑤ 可以創(chuàng)建聯(lián)合索引，但要注意前綴特性。如果要做復(fù)合索引，要把最常用的當(dāng)做常用條件列的字段放在前面。
⑥ 可以用 explain 查看 select 語句執(zhí)行計劃，慢查詢?nèi)罩净蛘?show full processlist 某語句長時間可以看到。
⑦ 能批量插入就批量插入，不要逐條插入。
mysql> insert into test values(1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
⑧ 使用 set profiles 查看 SQL 語句的執(zhí)行細(xì)節(jié)。
5、架構(gòu)上的優(yōu)化
1）、業(yè)務(wù)拆分：搜索功能，like‘%老男孩%’，一般不要用mysql 數(shù)據(jù)庫。
2）、業(yè)務(wù)拆分：某些業(yè)務(wù)應(yīng)用使用 nosql 持久化存儲，例如：memcahcedb，redis，ttserver
粉絲關(guān)注，好友關(guān)系等等。
3）、數(shù)據(jù)庫前端必須要加 cache，例如：memcached，用戶登錄，商品查詢。
4）、動態(tài)的數(shù)據(jù)靜態(tài)化。整個文件靜態(tài)化，頁面片段靜態(tài)化。
5）、數(shù)據(jù)庫集群與讀寫分離。一主多從，通過程序或者 dbproxy 進(jìn)行集群讀寫分離。
6）、單表超過2000萬。拆庫拆表，人工拆庫拆表（登錄、商品、訂單）
7）、百度，阿里國內(nèi)前×××司，會這樣搞。
6、流程、制度，安全優(yōu)化
任何一次人為數(shù)據(jù)庫記錄的更新，都要走一個流程：
a、人的流程：開發(fā)-->核心開發(fā)-->運維或DBA
b、測試流程：內(nèi)網(wǎng)測試-->IDC測試-->線上執(zhí)行
c、客戶端管理，phpmyadmin
有關(guān) mysql 的 innodb_flush_log_at_trx_commit 參數(shù)：
innodb_flush_log_at_trx_commit = 1
參數(shù)解釋：
0：log buffer 將每秒一次地寫入 log file 中，并且 log file 的 flush ( 刷到磁盤 )操作同時進(jìn)行。該模式下在事務(wù)提交的時候，不會主動觸發(fā)寫入磁盤的操作。
1：每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且 flush ( 刷到磁盤 ) 中去，該模式為系統(tǒng)默認(rèn)。
2：每次事務(wù)提交時 MySQL 都會把 log buffer 的數(shù)據(jù)寫入 log file，但是 flush ( 刷到磁盤 ) 操作并不會同時進(jìn)行。該模式下，MySQL 會每秒執(zhí)行一次 flush ( 刷到磁盤 ) 操作。
參數(shù)修改：
找到 mysql 配置文件 mysql.cnf，修改成合適的值，然后重啟 mysql。
注意事項：
當(dāng)設(shè)置為0，該模式速度最快，但不×××全，mysqld進(jìn)程的崩潰會導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失。
當(dāng)設(shè)置為1，該模式是最安全的，但也是最慢的一種方式。在mysqld 服務(wù)崩潰或者云服務(wù)器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務(wù)。。
當(dāng)設(shè)置為2，該模式速度較快，也比0安全，只有在操作系統(tǒng)崩潰或者系統(tǒng)斷電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。

對于以上關(guān)于MySQL數(shù)據(jù)庫如何實現(xiàn)正常優(yōu)化，大家是不是覺得非常有幫助。如果需要了解更多內(nèi)容，請繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會喜歡上這些內(nèi)容的。