通用規則

多讀少寫加緩存，少讀多寫加隊列

帶事務的不要整體commit，分段commit

一、要保證數據庫的效率，要做好以下四個方面的工作：

① 數據庫設計

② sql語句優化

③ 數據庫參數配置

④ 恰當的硬件資源和操作系統

二、通俗地理解三個范式，對于數據庫設計大有好處。

在數據庫設計中，為了更好地應用三個范式，就必須通俗地理解三個范式(通俗地理解是夠用的理解，并不是最科學最準確的理解)：

第一范式：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解；(只要是關系型數據庫都滿足1NF)

第二范式：2NF是對記錄的唯一性約束，要求記錄有惟一標識，即實體的唯一性；

第三范式：3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余。 沒有冗余的數據庫設計可以做到。

但是，沒有冗余的數據庫未必是最好的數據庫，有時為了提高運行效率，就必須降低范式標準，適當保留冗余數據。具體做法是： 在概念數據模型設計時遵守第三范式，降低范式標準的工作放到物理數據模型設計時考慮。降低范式就是增加字段，允許冗余。

三、SQL優化的一般步驟

①通過show status命令了解各種SQL的執行頻率。

②定位執行效率較低的SQL語句-(重點select)

③通過explain分析低效率的SQL語句的執行情況

④確定問題并采取相應的優化措施

下面的例子：

#其中Com_XXX表示XXX語句所執行的次數。show?global?status?like?'Com_%';

重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數，可以容易地了解到當前數據庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執行比例是多少。

#其中Com_XXX表示XXX語句所執行的次數。show?global?status?like?'com_%';

#試圖連接MySQL服務器的次數show?status?like?'Connections';

#服務器工作的時間(單位秒)show?status?like?'Uptime';

#慢查詢的次數 (默認是10)show?status?like?'Slow_queries'

4.如何查詢mysql的慢查詢時間Show?variables?like?'long_query_time';

5. 修改mysql 慢查詢時間set?long_query_time=2

四、哪些列上適合添加索引

1、唯一性太差的字段不適合單獨創建索引，即使頻繁作為查詢條件select?*?from?emp?where?sex?=?'男'

2、更新非常頻繁的字段不適合創建索引select?*?from?emp?where?logincount?=?1

#查看索引的使用情況show?status?like?'Handler_read%';

大家可以注意：

handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。

handler_read_rnd_next:這個值越高，說明查詢低效。含義：在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引?！?/p>

優化group by 語句

默認情況，MySQL對所有的group by col1,col2進行排序。這與在查詢中指定order by col1, col2類似。如果查詢中包括group by但用戶想要避免排序結果的消耗，則可以使用order by null禁止排序

optimizer_trace

從MySQL5.6版本開始，optimizer_trace

可支持把MySQL查詢執行計劃樹打印出來，默認是關閉的，功能支持動態開關，因為對性能有20%左右影響，只建議分析問題時，臨時開啟。

1. 默認是關閉的mysql>?show?variables?like?'optimizer_trace';

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

|?Variable_name?|?Value?|

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

|?optimizer_trace?|?enabled=off,one_line=off?|

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

1?row?in?set?(0.05?sec)

2.演示 optimizer_trace 簡單的使用流程：

2.1 會話級別臨時開啟mysql>

set?session?optimizer_trace="enabled=on",end_markers_in_json=on;

2.2 執行你的SQLselect?host,user,plugin?from?user?;

2.3 查詢information_schema.optimizer_trace表mysql>?SELECT?trace?FROM?information_schema.OPTIMIZER_TRACE\G;

2.4 導入到一個命名為xx.trace的文件，然后用JSON閱讀器來查看SELECT?TRACE?INTO?DUMPFILE?“xx.trace”?FROM?INFORMATION_SCHEMA.OPTIMIZER_TRACE;

補充：永久開啟 optimizer_trace ? ?(重啟失效)mysql>?set?optimizer_trace="enabled=on";

五、最重要的參數就是內存，我們主要用的innodb引擎，所以下面兩個參數調的很大

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size =1G

對于myisam，需要調整key_buffer_size

innodb_flush_log_at_trx_commit和sync_binlog 兩個參數是控制MySQL 磁盤寫入策略以及數據安全性的關鍵參數

推薦的做法是

innodb_flush_log_at_trx_commit=2

sync_binlog=N (N為500 或1000) 默認sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系統自己控制它的緩存的刷新。這時候的性能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog信息都會被丟失。

且使用帶蓄電池后備電源的緩存cache，防止系統斷電異常。

一 參數意義

innodb_flush_log_at_trx_commit

InnoDB的innodb_flush_log_at_trx_commit屬性可以控制每次事務提交時InnoDB的行為。當屬性值為0時，事務提交時，不會對重做日志進行寫入操作，而是等待主線程按時寫入；當屬性值為1時，事務提交時，會將重做日志寫入文件系統緩存，并且調用文件系統的fsync，將文件系統緩沖中的數據真正寫入磁盤存儲，確保不會出現數據丟失；當屬性值為2時，事務提交時，也會將日志文件寫入文件系統緩存，但是不會調用fsync，而是讓文件系統自己去判斷何時將緩存寫入磁盤。

innodb_flush_log_at_commit是InnoDB性能調優的一個基礎參數，涉及InnoDB的寫入效率和數據安全。

當sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進制日志binary log時，會使用fdatasync()函數將它的寫二進制日志binary log同步到磁盤中去。

注:

如果啟用了autocommit，那么每一個語句statement就會有一次寫操作；否則每個事務對應一個寫操作。

根據上述描述，我做了一張圖，可以方便大家查看。

二 性能

兩個參數在不同值時對db的純寫入的影響表現如下：

測試場景1

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

測試場景2

innodb_flush_log_at_trx_commit=1

sync_binlog=1000

測試場景3

innodb_flush_log_at_trx_commit=1

sync_binlog=1

測試場景4

innodb_flush_log_at_trx_commit=1

sync_binlog=1000

測試場景5

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

場景TPS

場景141000

場景233000

場景326000

場景433000

由此可見，當兩個參數設置為雙1的時候，寫入性能最差，sync_binlog=N (N>1 )?innodb_flush_log_at_trx_commit=2 時，(在當前模式下)MySQL的寫操作才能達到最高性能。

三 安全

當innodb_flush_log_at_trx_commit和sync_binlog? 都為?1?時是最安全的，在mysqld 服務崩潰或者服務器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務。但是魚與熊掌不可兼得，雙11 會導致頻繁的io操作，因此該模式也是最慢的一種方式。

當innodb_flush_log_at_trx_commit設置為0，mysqld進程的崩潰會導致上一秒鐘所有事務數據的丟失。

當innodb_flush_log_at_trx_commit設置為2，只有在操作系統崩潰或者系統掉電的情況下，上一秒鐘所有事務數據才可能丟失。

雙1適合數據安全性要求非常高，而且磁盤IO寫能力足夠支持業務，比如訂單,交易,充值,支付消費系統。雙1模式下，當磁盤IO無法滿足業務需求時 比如11.11 活動的壓力

總結

一、為了保證事務的ACID特性，理論上每次事務提交都應該刷盤，但此時效率很低，有兩種優化方向：

(1)隨機寫優化為順序寫；

(2)每次寫優化為批量寫；

二、redo log是一種順序寫，它有三層架構：

(1)MySQL應用層：Log Buffer

(2)OS內核層：OS cache

(3)OS文件：log file

三、為了滿足不用業務對于吞吐量與一致性的需求，MySQL事務提交時刷redo log有三種策略：

(1)0：每秒write一次OS cache，同時fsync刷磁盤，性能好；

(2)1：每次都write入OS cache，同時fsync刷磁盤，一致性好；

(3)2：每次都write入OS cache，每秒fsync刷磁盤，折衷；

四、高并發業務，行業內的最佳實踐，是：

innodb_flush_log_at_trx_commit=2

版權聲明：本文由PHP面試資料網發布，如需轉載請注明出處。

總結

以上是生活随笔為你收集整理的php 面试mysql,mysql中优化必读的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。