MySQL/Oracle資料庫最佳化總結（非常全面）

MySQL資料庫最佳化的八種方式(經典必看) https://blog.csdn.net/baidu_37107022/article/details/77460464

1、選取最適用的欄位屬性

MySQL可以很好的支援大資料量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。因此，在建立表的時候，為了獲得更好的效能，我們可以將表中欄位的寬度設得儘可能小。

例如，在定義郵政編碼這個欄位時，如果將其設定為CHAR(255),顯然給資料庫增加了不必要的空間，甚至使用VARCHAR這種型別也是多餘的，因為CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型欄位。

另外一個提高效率的方法是在可能的情況下，應該儘量把欄位設定為NOTNULL，這樣在將來執行查詢的時候，資料庫不用去比較NULL值。

對於某些文字欄位，例如“省份”或者“性別”，我們可以將它們定義為ENUM型別。因為在MySQL中，ENUM型別被當作數值型資料來處理，而數值型資料被處理起來的速度要比文字型別快得多。這樣，我們又可以提高資料庫的效能。

2、使用連線（JOIN）來代替子查詢(Sub-Queries)

MySQL從4.1開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要將客戶基本資訊表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售資訊表中將所有發出訂單的客戶ID取出來，然後將結果傳遞給主查詢，如下所示：

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連線（JOIN）..替代。例如，假設我們要將所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

如果使用連線（JOIN）..來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，效能將會更好，查詢如下：

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

連線（JOIN）..之所以更有效率一些，是因為MySQL不需要在記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

3、使用聯合(UNION)來代替手動建立的臨時表

MySQL從4.0的版本開始支援union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合併的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。使用union來建立查詢的時候，我們只需要用UNION作為關鍵字把多個select語句連線起來就可以了，要注意的是所有select語句中的欄位數目要想同。下面的例子就演示了一個使用UNION的查詢。

SELECTName,PhoneFROMclientUNION

SELECTName,BirthDateFROMauthorUNION

SELECTName,SupplierFROMproduct

4、事務

儘管我們可以使用子查詢（Sub-Queries）、連線（JOIN）和聯合（UNION）來建立各種各樣的查詢，但不是所有的資料庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句執行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個資料同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，資料庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成資料的不完整，甚至會破壞資料庫中的資料。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗。換句話說，就是可以保持資料庫中資料的一致性和完整性。事物以BEGIN關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那麼，ROLLBACK命令就可以把資料庫恢復到BEGIN開始之前的狀態。

BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;

事務的另一個重要作用是當多個使用者同時使用相同的資料來源時，它可以利用鎖定資料庫的方法來為使用者提供一種安全的訪問方式，這樣可以保證使用者的操作不被其它的使用者所干擾。

5、鎖定表

儘管事務是維護資料庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響資料庫的效能，尤其是在很大的應用系統中。由於在事務執行的過程中，資料庫將會被鎖定，因此其它的使用者請求只能暫時等待直到該事務結束。如果一個資料庫系統只有少數幾個使用者來使用，事務造成的影響不會成為一個太大的問題；但假設有成千上萬的使用者同時訪問一個資料庫系統，例如訪問一個電子商務網站，就會產生比較嚴重的響應延遲。

其實，有些情況下我們可以透過鎖定表的方法來獲得更好的效能。下面的例子就用鎖定表的方法來完成前面一個例子中事務的功能。

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';

...

UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES

這裡，我們用一個select語句取出初始資料，透過一些計算，用update語句將新值更新到表中。包含有WRITE關鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執行之前，不會有其它的訪問來對inventory進行插入、更新或者刪除的操作。

6、使用外來鍵

鎖定表的方法可以維護資料的完整性，但是它卻不能保證資料的關聯性。這個時候我們就可以使用外來鍵。

例如，外來鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這裡，外來鍵可以把customerinfo表中的CustomerID對映到salesinfo表中CustomerID，任何一條沒有合法CustomerID的記錄都不會被更新或插入到salesinfo中。

CREATETABLEcustomerinfo( CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

 

CREATETABLEsalesinfo( SalesIDINTNOTNULL,CustomerIDINTNOTNULL,

 

PRIMARYKEY(CustomerID,SalesID),

 

FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB;

注意例子中的引數“ONDELETECASCADE”。該引數保證當customerinfo表中的一條客戶記錄被刪除的時候，salesinfo表中所有與該客戶相關的記錄也會被自動刪除。如果要在MySQL中使用外來鍵，一定要記住在建立表的時候將表的型別定義為事務安全表InnoDB型別。該型別不是MySQL表的預設型別。定義的方法是在CREATETABLE語句中加上TYPE=INNODB。如例中所示。

7、使用索引

索引是提高資料庫效能的常用方法，它可以令資料庫伺服器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，效能提高更為明顯。

那該對哪些欄位建立索引呢？

一般說來，索引應建立在那些將用於JOIN,WHERE判斷和ORDERBY排序的欄位上。儘量不要對資料庫中某個含有大量重複的值的欄位建立索引。對於一個ENUM型別的欄位來說，出現大量重複值是很有可能的情況

例如customerinfo中的“province”..欄位，在這樣的欄位上建立索引將不會有什麼幫助；相反，還有可能降低資料庫的效能。我們在建立表的時候可以同時建立合適的索引，也可以使用ALTERTABLE或CREATEINDEX在以後建立索引。此外，MySQL從版本3.23.23開始支援全文索引和搜尋。全文索引在MySQL中是一個FULLTEXT型別索引，但僅能用於MyISAM型別的表。對於一個大的資料庫，將資料裝載到一個沒有FULLTEXT索引的表中，然後再使用ALTERTABLE或CREATEINDEX建立索引，將是非常快的。但如果將資料裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。

8、最佳化的查詢語句

絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。

下面是應該注意的幾個方面。

首先，最好是在相同型別的欄位間進行比較的操作。

在MySQL3.23版之前，這甚至是一個必須的條件。例如不能將一個建有索引的INT欄位和BIGINT欄位進行比較；但是作為特殊的情況，在CHAR型別的欄位和VARCHAR型別欄位的欄位大小相同的時候，可以將它們進行比較。

其次，在建有索引的欄位上儘量不要使用函式進行操作。

例如，在一個DATE型別的欄位上使用YEAE()函式時，將會使索引不能發揮應有的作用。所以，下面的兩個查詢雖然返回的結果一樣，但後者要比前者快得多。

第三，在搜尋字元型欄位時，我們有時會使用LIKE關鍵字和萬用字元，這種做法雖然簡單，但卻也是以犧牲系統效能為代價的。

例如下面的查詢將會比較表中的每一條記錄。

 

SELECT*FROMbooks

 

WHEREnamelike"MySQL%"

但是如果換用下面的查詢，返回的結果一樣，但速度就要快上很多：

 

SELECT*FROMbooks

 

WHEREname＞="MySQL"andname＜"MySQM"

最後，應該注意避免在查詢中讓MySQL進行自動型別轉換，因為轉換過程也會使索引變得不起作用。

最佳化Mysql資料庫的8個方法

本文透過8個方法最佳化Mysql資料庫：建立索引、複合索引、索引不會包含有NULL值的列、使用短索引、排序的索引問題、like語句操作、不要在列上進行運算、不使用NOT IN和<>操作

1、建立索引

對於查詢佔主要的應用來說，索引顯得尤為重要。很多時候效能問題很簡單的就是因為我們忘了新增索引而造成的，或者說沒有新增更為有效的索引導致。如果不加索引的話，那麼查詢任何哪怕只是一條特定的資料都會進行一次全表掃描，如果一張表的資料量很大而符合條件的結果又很少，那麼不加索引會引起致命的效能下降。但是也不是什麼情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什麼優勢，還會影響到更新速度，這被稱為過度索引。

2、複合索引

比如有一條語句是這樣的：select * from users where area='beijing' and age=22;

如果我們是在area和age上分別建立單個索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率，但是如果在area、age兩列上建立複合索引的話將帶來更高的效率。如果我們建立了(area, age, salary)的複合索引，那麼其實相當於建立了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左字首特性。因此我們在建立複合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

3、索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

4、使用短索引

對串列進行索引，如果可能應該指定一個字首長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

5、排序的索引問題

mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

6、like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

7、不要在列上進行運算

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from users where adddate<‘2007-01-01';

8、不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

資料庫SQL最佳化大總結之 百萬級資料庫最佳化方案

網上關於SQL最佳化的教程很多，但是比較雜亂。近日有空整理了一下，寫出來跟大家分享一下，其中有錯誤和不足的地方，還請大家糾正補充。

這篇文章我花費了大量的時間查詢資料、修改、排版，希望大家閱讀之後，感覺好的話推薦給更多的人，讓更多的人看到、糾正以及補充。

 

1.對查詢進行最佳化，要儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

最好不要給資料庫留NULL，儘可能的使用 NOT NULL填充資料庫.

備註、描述、評論之類的可以設定為 NULL，其他的，最好不要使用NULL。

不要以為 NULL 不需要空間，比如：char(100) 型，在欄位建立時，空間就固定了， 不管是否插入值（NULL也包含在內），都是佔用 100個字元的空間的，如果是varchar這樣的變長欄位， null 不佔用空間。

可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：

select id from t where num = 0

3.應儘量避免在 where 子句中使用 != 或 <> 運算子，否則將引擎放棄使用索引而進行全表掃描。

4.應儘量避免在 where 子句中使用 or 來連線條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or Name = 'admin'

可以這樣查詢：

select id from t where num = 10

union all

select id from t where Name = 'admin'

5.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多時候用 exists 代替 in 是一個好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

 

6.下面的查詢也將導致全表掃描：

select id from t where name like ‘%abc%’

若要提高效率，可以考慮全文檢索。

7.如果在 where 子句中使用引數，也會導致全表掃描。因為SQL只有在執行時才會解析區域性變數，但最佳化程式不能將訪問計劃的選擇推遲到執行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where num = @num

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num = @num

.應儘量避免在 where 子句中對欄位進行表示式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2 = 100

應改為:

select id from t where num = 100*2

9.應儘量避免在where子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3) = ’abc’       -–name以abc開頭的id

select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

應改為:

select id from t where name like 'abc%'

select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

10.不要在 where 子句中的“=”左邊進行函式、算術運算或其他表示式運算，否則系統將可能無法正確使用索引。

11.在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。

12.不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

這類程式碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：

create table #t(…)

13.Update 語句，如果只更改1、2個欄位，不要Update全部欄位，否則頻繁呼叫會引起明顯的效能消耗，同時帶來大量日誌。

14.對於多張大資料量（這裡幾百條就算大了）的表JOIN，要先分頁再JOIN，否則邏輯讀會很高，效能很差。

15.select count(*) from table；這樣不帶任何條件的count會引起全表掃描，並且沒有任何業務意義，是一定要杜絕的。

16.索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。

17.應儘可能的避免更新 clustered 索引資料列，因為 clustered 索引資料列的順序就是表記錄的物理儲存順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引資料列，那麼需要考慮是否應將該索引建為 clustered 索引。

18.儘量使用數字型欄位，若只含數值資訊的欄位儘量不要設計為字元型，這會降低查詢和連線的效能，並會增加儲存開銷。這是因為引擎在處理查詢和連 接時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。

19.儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。

20.任何地方都不要使用 select * from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。

21.儘量使用表變數來代替臨時表。如果表變數包含大量資料，請注意索引非常有限（只有主鍵索引）。

22. 避免頻繁建立和刪除臨時表，以減少系統表資源的消耗。臨時表並不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重複引用大型表或常用表中的某個資料集時。但是，對於一次性事件， 最好使用匯出表。

23.在新建臨時表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然後insert。

24.如果使用到了臨時表，在儲存過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

25.儘量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。

26.使用基於遊標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。

27.與臨時表一樣，遊標並不是不可使用。對小型資料集使用 FAST_FORWARD 遊標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的資料時。在結果集中包括“合計”的例程通常要比使用遊標執行的速度快。如果開發時 間允許，基於遊標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的儲存過程和觸發器的開始處設定 SET NOCOUNT ON ，在結束時設定 SET NOCOUNT OFF 。無需在執行儲存過程和觸發器的每個語句後向客戶端傳送 DONE_IN_PROC 訊息。

29.儘量避免大事務操作，提高系統併發能力。

30.儘量避免向客戶端返回大資料量，若資料量過大，應該考慮相應需求是否合理。

 

實際案例分析：拆分大的 DELETE 或INSERT 語句，批次提交SQL語句

　　如果你需要在一個線上的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。

　　Apache 會有很多的子程式或執行緒。所以，其工作起來相當有效率，而我們的伺服器也不希望有太多的子程式，執行緒和資料庫連結，這是極大的佔伺服器資源的事情，尤其是記憶體。

　　如果你把你的表鎖上一段時間，比如30秒鐘，那麼對於一個有很高訪問量的站點來說，這30秒所積累的訪問程式/執行緒，資料庫連結，開啟的檔案數，可能不僅僅會讓你的WEB服務崩潰，還可能會讓你的整臺伺服器馬上掛了。

　　所以，如果你有一個大的處理，你一定把其拆分，使用 LIMIT oracle(rownum),sqlserver(top)條件是一個好的方法。下面是一個mysql示例：

while(1){

 　　//每次只做1000條

　　 mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);

 　　if(mysql_affected_rows() == 0){

　　 　　//刪除完成，退出！

　　 　　break；

　　}

//每次暫停一段時間，釋放表讓其他程式/執行緒訪問。

usleep(50000)

}

 

好了，到這裡就寫完了。我知道還有很多沒有寫到的，還請大家補充。後面有空會介紹一些SQL最佳化工具給大家。讓我們一起學習，一起進步吧！

運維角度淺談MySQL資料庫最佳化

 

 一個成熟的資料庫架構並不是一開始設計就具備高可用、高伸縮等特性的，它是隨著使用者量的增加，基礎架構才逐漸完善。這篇博文主要談MySQL資料庫發展週期中所面臨的問題及最佳化方案，暫且拋開前端應用不說，大致分為以下五個階段：

1、資料庫表設計

  專案立項後，開發部根據產品部需求開發專案，開發工程師工作其中一部分就是對錶結構設計。對於資料庫來說，這點很重要，如果設計不當，會直接影響訪問速度和使用者體驗。影響的因素很多，比如慢查詢、低效的查詢語句、沒有適當建立索引、資料庫堵塞（死鎖）等。當然，有測試工程師的團隊，會做壓力測試，找bug。對於沒有測試工程師的團隊來說，大多數開發工程師初期不會太多考慮資料庫設計是否合理，而是儘快完成功能實現和交付，等專案有一定訪問量後，隱藏的問題就會暴露，這時再去修改就不是這麼容易的事了。

2、資料庫部署

  該運維工程師出場了，專案初期訪問量不會很大，所以單臺部署足以應對在1500左右的QPS（每秒查詢率）。考慮到高可用性，可採用MySQL主從複製+Keepalived做雙擊熱備，常見叢集軟體有Keepalived、Heartbeat。

雙機熱備博文：http://lizhenliang.blog.51cto.com/7876557/1362313

3、資料庫效能最佳化

  如果將MySQL部署到普通的X86伺服器上，在不經過任何最佳化情況下，MySQL理論值正常可以處理2000左右QPS，經過最佳化後，有可能會提升到2500左右QPS，否則，訪問量當達到1500左右併發連線時，資料庫處理效能就會變慢，而且硬體資源還很富裕，這時就該考慮軟體問題了。那麼怎樣讓資料庫最大化發揮效能呢？一方面可以單臺執行多個MySQL例項讓伺服器效能發揮到最大化，另一方面是對資料庫進行最佳化，往往作業系統和資料庫預設配置都比較保守，會對資料庫發揮有一定限制，可對這些配置進行適當的調整，儘可能的處理更多連線數。

具體最佳化有以下三個層面：

  3.1 資料庫配置最佳化

  MySQL常用有兩種儲存引擎，一個是MyISAM，不支援事務處理，讀效能處理快，表級別鎖。另一個是InnoDB，支援事務處理（ACID），設計目標是為處理大容量資料發揮最大化效能，行級別鎖。

  表鎖：開銷小，鎖定粒度大，發生死鎖機率高，相對併發也低。

  行鎖：開銷大，鎖定粒度小，發生死鎖機率低，相對併發也高。

  為什麼會出現表鎖和行鎖呢？主要是為了保證資料的完整性，舉個例子，一個使用者在操作一張表，其他使用者也想操作這張表，那麼就要等第一個使用者操作完，其他使用者才能操作，表鎖和行鎖就是這個作用。否則多個使用者同時操作一張表，肯定會資料產生衝突或者異常。

  根據以上看來，使用InnoDB儲存引擎是最好的選擇，也是MySQL5.5以後版本中預設儲存引擎。每個儲存引擎相關聯引數比較多，以下列出主要影響資料庫效能的引數。

  公共引數預設值：

1

2

3

4

5

6

max_connections = 151

#同時處理最大連線數，推薦設定最大連線數是上限連線數的80%左右   

sort_buffer_size = 2M

#查詢排序時緩衝區大小，只對order by和group by起作用，可增大此值為16M

open_files_limit = 1024 

#開啟檔案數限制，如果show global status like 'open_files'檢視的值等於或者大於open_files_limit值時，程式會無法連線資料庫或卡死

  MyISAM引數預設值：

1

2

3

4

5

6

7

8

9

10

key_buffer_size = 16M

#索引快取區大小，一般設定實體記憶體的30-40%

read_buffer_size = 128K  

#讀操作緩衝區大小，推薦設定16M或32M

query_cache_type = ON

#開啟查詢快取功能

query_cache_limit = 1M  

#查詢快取限制，只有1M以下查詢結果才會被快取，以免結果資料較大把快取池覆蓋

query_cache_size = 16M  

#檢視緩衝區大小，用於快取SELECT查詢結果，下一次有同樣SELECT查詢將直接從快取池返回結果，可適當成倍增加此值

  InnoDB引數預設值：

1

2

3

4

5

6

7

8

9

10

innodb_buffer_pool_size = 128M

#索引和資料緩衝區大小，一般設定實體記憶體的60%-70%

innodb_buffer_pool_instances = 1    

#緩衝池例項個數，推薦設定4個或8個

innodb_flush_log_at_trx_commit = 1  

#關鍵引數，0代表大約每秒寫入到日誌並同步到磁碟，資料庫故障會丟失1秒左右事務資料。1為每執行一條SQL後寫入到日誌並同步到磁碟，I/O開銷大，執行完SQL要等待日誌讀寫，效率低。2代表只把日誌寫入到系統快取區，再每秒同步到磁碟，效率很高，如果伺服器故障，才會丟失事務資料。對資料安全性要求不是很高的推薦設定2，效能高，修改後效果明顯。

innodb_file_per_table = OFF  

#預設是共享表空間，共享表空間idbdata檔案不斷增大，影響一定的I/O效能。推薦開啟獨立表空間模式，每個表的索引和資料都存在自己獨立的表空間中，可以實現單表在不同資料庫中移動。

innodb_log_buffer_size = 8M  

#日誌緩衝區大小，由於日誌最長每秒鐘重新整理一次，所以一般不用超過16M

  3.2 系統核心最佳化

  大多數MySQL都部署在linux系統上，所以作業系統的一些引數也會影響到MySQL效能，以下對linux核心進行適當最佳化。

1

2

3

4

5

6

7

8

9

10

net.ipv4.tcp_fin_timeout = 30

#TIME_WAIT超時時間，預設是60s

net.ipv4.tcp_tw_reuse = 1    

#1表示開啟複用，允許TIME_WAIT socket重新用於新的TCP連線，0表示關閉

net.ipv4.tcp_tw_recycle = 1  

#1表示開啟TIME_WAIT socket快速回收，0表示關閉

net.ipv4.tcp_max_tw_buckets = 4096   

#系統保持TIME_WAIT socket最大數量，如果超出這個數，系統將隨機清除一些TIME_WAIT並列印警告資訊

net.ipv4.tcp_max_syn_backlog = 4096

#進入SYN佇列最大長度，加大佇列長度可容納更多的等待連線

  在linux系統中，如果程式開啟的檔案控制程式碼數量超過系統預設值1024，就會提示“too many files open”資訊，所以要調整開啟檔案控制程式碼限制。

1

2

3

4

# vi /etc/security/limits.conf  #加入以下配置，*代表所有使用者，也可以指定使用者，重啟系統生效

* soft nofile 65535

* hard nofile 65535

# ulimit -SHn 65535   #立刻生效

  3.3 硬體配置

  加大實體記憶體，提高檔案系統效能。linux核心會從記憶體中分配出快取區（系統快取和資料快取）來存放熱資料，透過檔案系統延遲寫入機制，等滿足條件時（如快取區大小到達一定百分比或者執行sync命令）才會同步到磁碟。也就是說實體記憶體越大，分配快取區越大，快取資料越多。當然，伺服器故障會丟失一定的快取資料。

  SSD硬碟代替SAS硬碟，將RAID級別調整為RAID1+0，相對於RAID1和RAID5有更好的讀寫效能（IOPS），畢竟資料庫的壓力主要來自磁碟I/O方面。

4、資料庫架構擴充套件

  隨著業務量越來越大，單臺資料庫伺服器效能已無法滿足業務需求，該考慮加機器了，該做叢集了~~~。主要思想是分解單臺資料庫負載，突破磁碟I/O效能，熱資料存放快取中，降低磁碟I/O訪問頻率。

  4.1 主從複製與讀寫分離

  因為生產環境中，資料庫大多都是讀操作，所以部署一主多從架構，主資料庫負責寫操作，並做雙擊熱備，多臺從資料庫做負載均衡，負責讀操作，主流的負載均衡器有LVS、HAProxy、Nginx。

  怎麼來實現讀寫分離呢？大多數企業是在程式碼層面實現讀寫分離，效率比較高。另一個種方式透過代理程式實現讀寫分離，企業中應用較少，常見代理程式有MySQL Proxy、Amoeba。在這樣資料庫叢集架構中，大大增加資料庫高併發能力，解決單臺效能瓶頸問題。如果從資料庫一臺從庫能處理2000 QPS，那麼5臺就能處理1w QPS，資料庫橫向擴充套件性也很容易。

  有時，面對大量寫操作的應用時，單臺寫效能達不到業務需求。如果做雙主，就會遇到資料庫資料不一致現象，產生這個原因是在應用程式不同的使用者會有可能操作兩臺資料庫，同時的更新操作造成兩臺資料庫資料庫資料發生衝突或者不一致。在單庫時MySQL利用儲存引擎機制表鎖和行鎖來保證資料完整性，怎樣在多臺主庫時解決這個問題呢？有一套基於perl語言開發的主從複製管理工具，叫MySQL-MMM（Master-Master replication managerfor Mysql，Mysql主主複製管理器），這個工具最大的優點是在同一時間只提供一臺資料庫寫操作，有效保證資料一致性。

  主從複製博文：http://lizhenliang.blog.51cto.com/7876557/1290431

  讀寫分離博文：http://lizhenliang.blog.51cto.com/7876557/1305083

 MySQL-MMM博文：http://lizhenliang.blog.51cto.com/7876557/1354576

  4.2 增加快取

  給資料庫增加快取系統，把熱資料快取到記憶體中，如果快取中有要請求的資料就不再去資料庫中返回結果，提高讀效能。快取實現有本地快取和分散式快取，本地快取是將資料快取到本地伺服器記憶體中或者檔案中。分散式快取可以快取海量資料，擴充套件性好，主流的分散式快取系統有memcached、redis，memcached效能穩定，資料快取在記憶體中，速度很快，QPS可達8w左右。如果想資料持久化就選擇用redis，效能不低於memcached。

  工作過程：

  

  4.3 分庫

  分庫是根據業務不同把相關的表切分到不同的資料庫中，比如web、bbs、blog等庫。如果業務量很大，還可將切分後的庫做主從架構，進一步避免單個庫壓力過大。

  4.4 分表

  資料量的日劇增加，資料庫中某個表有幾百萬條資料，導致查詢和插入耗時太長，怎麼能解決單表壓力呢？你就該考慮是否把這個表拆分成多個小表，來減輕單個表的壓力，提高處理效率，此方式稱為分表。

  分表技術比較麻煩，要修改程式程式碼裡的SQL語句，還要手動去建立其他表，也可以用merge儲存引擎實現分表，相對簡單許多。分表後，程式是對一個總表進行操作，這個總表不存放資料，只有一些分表的關係，以及更新資料的方式，總表會根據不同的查詢，將壓力分到不同的小表上，因此提高併發能力和磁碟I/O效能。

  分表分為垂直拆分和水平拆分：

  垂直拆分：把原來的一個很多欄位的表拆分多個表，解決表的寬度問題。你可以把不常用的欄位單獨放到一個表中，也可以把大欄位獨立放一個表中，或者把關聯密切的欄位放一個表中。

  水平拆分：把原來一個表拆分成多個表，每個表的結構都一樣，解決單表資料量大的問題。

  4.5 分割槽

  分割槽就是把一張表的資料根據表結構中的欄位（如range、list、hash等）分成多個區塊，這些區塊可以在一個磁碟上，也可以在不同的磁碟上，分割槽後，表面上還是一張表，但資料雜湊在多個位置，這樣一來，多塊硬碟同時處理不同的請求，從而提高磁碟I/O讀寫效能，實現比較簡單。

注：增加快取、分庫、分表和分割槽主要由程式猿來實現。

5、資料庫維護

  資料庫維護是運維工程師或者DBA主要工作，包括效能監控、效能分析、效能調優、資料庫備份和恢復等。

  5.1 效能狀態關鍵指標

  QPS，Queries Per Second：每秒查詢數，一臺資料庫每秒能夠處理的查詢次數

  TPS，Transactions Per Second：每秒處理事務數

  透過show status檢視執行狀態，會有300多條狀態資訊記錄，其中有幾個值幫可以我們計算出QPS和TPS，如下：

  Uptime：伺服器已經執行的實際，單位秒

  Questions：已經傳送給資料庫查詢數

  Com_select：查詢次數，實際運算元據庫的

  Com_insert：插入次數

  Com_delete：刪除次數

  Com_update：更新次數

  Com_commit：事務次數

  Com_rollback：回滾次數

  那麼，計算方法來了，基於Questions計算出QPS：

1

2

  mysql> show global status like 'Questions';

  mysql> show global status like 'Uptime';

  QPS = Questions / Uptime

  基於Com_commit和Com_rollback計算出TPS：

1

2

3

  mysql> show global status like 'Com_commit';

  mysql> show global status like 'Com_rollback';

  mysql> show global status like 'Uptime';

  TPS = (Com_commit + Com_rollback) / Uptime

  另一計算方式：基於Com_select、Com_insert、Com_delete、Com_update計算出QPS

1

  mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');

  等待1秒再執行，獲取間隔差值，第二次每個變數值減去第一次對應的變數值，就是QPS

  TPS計算方法：

1

  mysql> show global status where Variable_name in('com_insert','com_delete','com_update');

  計算TPS，就不算查詢操作了，計算出插入、刪除、更新四個值即可。

  經網友對這兩個計算方式的測試得出，當資料庫中myisam表比較多時，使用Questions計算比較準確。當資料庫中innodb表比較多時，則以Com_*計算比較準確。

  5.2 開啟慢查詢日誌

  MySQL開啟慢查詢日誌，分析出哪條SQL語句比較慢，使用set設定變數，重啟服務失效，可以在my.cnf新增引數永久生效。

1

2

3

4

mysql> set global slow-query-log=on  #開啟慢查詢功能

mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';  #指定慢查詢日誌檔案位置

mysql> set global log_queries_not_using_indexes=on;   #記錄沒有使用索引的查詢

mysql> set global long_query_time=1;   #只記錄處理時間1s以上的慢查詢

  分析慢查詢日誌，可以使用MySQL自帶的mysqldumpslow工具，分析的日誌較為簡單。

  # mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    #檢視最慢的前三個查詢

  也可以使用percona公司的pt-query-digest工具，日誌分析功能全面，可分析slow log、binlog、general log。

  分析慢查詢日誌：pt-query-digest /var/log/mysql/mysql-slow.log

  分析binlog日誌：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql 

  pt-query-digest --type=binlog mysql-bin.000001.sql 

  分析普通日誌：pt-query-digest --type=genlog localhost.log

  5.3 資料庫備份

  備份資料庫是最基本的工作，也是最重要的，否則後果很嚴重，你懂得！但由於資料庫比較大，上百G，往往備份都很耗費時間，所以就該選擇一個效率高的備份策略，對於資料量大的資料庫，一般都採用增量備份。常用的備份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比較適用於小的資料庫，因為是邏輯備份，所以備份和恢復耗時都比較長。mysqlhotcopy和xtrabackup是物理備份，備份和恢復速度快，不影響資料庫服務情況下進行熱複製，建議使用xtrabackup，支援增量備份。

  Xtrabackup備份工具使用博文：http://lizhenliang.blog.51cto.com/7876557/1612800

  5.4 資料庫修復

  有時候MySQL伺服器突然斷電、異常關閉，會導致表損壞，無法讀取表資料。這時就可以用到MySQL自帶的兩個工具進行修復，myisamchk和mysqlcheck。

  myisamchk：只能修復myisam表，需要停止資料庫

  常用引數：

  -f --force    強制修復，覆蓋老的臨時檔案，一般不使用

  -r --recover  恢復模式

  -q --quik     快速恢復

  -a --analyze  分析表

  -o --safe-recover 老的恢復模式，如果-r無法修復，可以使用此引數試試

  -F --fast     只檢查沒有正常關閉的表

  快速修復weibo資料庫:

  # cd /var/lib/mysql/weibo 

  # myisamchk -r -q *.MYI

  mysqlcheck：myisam和innodb表都可以用，不需要停止資料庫，如修復單個表，可在資料庫後面新增表名，以空格分割

  常用引數：

  -a  --all-databases  檢查所有的庫

  -r  --repair   修復表

  -c  --check    檢查表，預設選項

  -a  --analyze  分析表

  -o  --optimize 最佳化表

  -q  --quik   最快檢查或修復表

  -F  --fast   只檢查沒有正常關閉的表

  快速修復weibo資料庫:

  mysqlcheck -r -q -uroot -p123 weibo 

  5.5 另外，檢視CPU和I/O效能方法

  #檢視CPU效能

  #引數-P是顯示CPU數，ALL為所有，也可以只顯示第幾顆CPU

  #檢視I/O效能

  #引數-m是以M單位顯示，預設K

  #%util：當達到100%時，說明I/O很忙。

  #await：請求在佇列中等待時間，直接影響read時間。

  I/O極限：IOPS（r/s+w/s）,一般RAID0/10在1200左右。（IOPS，每秒進行讀寫（I/O）操作次數）

  I/O頻寬：在順序讀寫模式下SAS硬碟理論值在300M/s左右，SSD硬碟理論值在600M/s左右。

--------------------- 

作者：Java仗劍走天涯 

來源：CSDN 

原文：https://blog.csdn.net/baidu_37107022/article/details/77460464 

版權宣告：本文為博主原創文章，轉載請附上博文連結！