MySQL數據庫如何實現正常優化

下文主要給大家帶來MySQL數據庫如何實現正常優化，希望這些內容能夠帶給大家實際用處，這也是我編輯MySQL數據庫如何實現正常優化這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

MySQL數據庫優化：

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

對于以上關于MySQL數據庫如何實現正常優化，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。