mysql最佳化通常使用的幾種方法

分享一下常見的幾種MySQL資料最佳化方式。。。。。。。


選取最適合的欄位屬性
1、MySQL可以很好的支援資料量的很大的存取，但是一般說來，資料庫中的表越小其查詢的速度就也快。所以，可以在建表的時候，為了獲取更好的效能，將表中的欄位長度設的儘可能的小。


2、儘可能的把欄位設定成NOT NULL，這樣在執行查詢的時候，資料庫不用去比較NULL值。


3、對於部分的文字欄位，例如“性別”或者“民族”，我們就可以用enum來定義。MySQL會把enum型別的資料當作數值型來處理，而數值型資料被處理起來的速度要比文字型別快的多。這樣我們就可以提高資料庫的效能。


使用連線（JOIN）來代替子查詢是（sub-Queries）
MySQL從4.1開始就支援SQL的子查詢。這個技術可以使用select語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。


例：將客戶基本資訊表中沒有任何訂單的客戶刪除掉


DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)


利用子查詢先從銷售資訊表中將所有發出訂單的客戶ID取出，然後將結果傳遞給主查詢。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，在某些情況下，子查詢可以被更有效率的連線（JOIN）替代。


例：將所有沒有訂單記錄的使用者取出來


SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)


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


SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID WHERE salesinfo.CustomerID ISNULL




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


使用聯合（union）來代替手動建立的臨時表
Union查詢可以把需要使用臨時表的兩條或者更多的select查詢合併成一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫的整齊、高效。使用union來建立查詢的時候，只需要用union作為關鍵字把多個select語句連線起來就可以了（所有的select語句中的欄位數目相同）


SELECT Name,Phone FROM client UNION


SELECT Name,BirthDate FROM author UNION


SELECT Name,Supplier FROM product


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


BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;


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


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

利率決議

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';


...


UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES


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


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


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


CREATE TABLE customerinfo( CustomerIDINT NOT


NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;


CREATE TABLE salesinfo( SalesIDNT NOT NULL,CustomerIDINT NOT NULL, PRIMARYKEY(CustomerID,SalesID),


FOREIGNKEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE)TYPE=INNODB;


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


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


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


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


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


在相同型別的欄位間進行比較的操作。
在建有索引的欄位上儘量不要使用函式進行操作。
例如：在一個date型別的欄位上使用yeae()函式時，將會使索引不能發揮應有的作用。


     3.在搜尋字元型欄位時，我們有時會使用like關鍵字和萬用字元，這種做法雖然簡單，但卻也是以犧牲系統效能為代價的。後者的查詢速度明顯比前者快得多。


    SELECT * FROM books WHERE name like"MySQL%"


    SELECT * FROM books WHERE name＞="MySQL" andname ＜"MySQM"


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