MySQL資料庫優化的八種方式(經典必看)

来源:http://www.cnblogs.com/hurry-up/archive/2017/04/06/6672516.html
-Advertisement-
Play Games

1、選取最適用的欄位屬性 MySQL可以很好的支持大數據量的存取,但是一般說來,資料庫中的表越小,在它上面執行的查詢也就會越快。因此,在創建表的時候,為了獲得更好的性能,我們可以將表中欄位的寬度設得儘可能小。 例如,在定義郵政編碼這個欄位時,如果將其設置為CHAR(255),顯然給資料庫增加了不必要 ...


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語句使用不恰當的話,索引將無法發揮它應有的作用。

下麵是應該註意的幾個方面。

  a、 首先,最好是在相同類型的欄位間進行比較的操作

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

  b、 其次,在建有索引的欄位上儘量不要使用函數進行操作

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

  c、第三,在搜索字元型欄位時,我們有時會使用LIKE關鍵字和通配符,這種做法雖然簡單,但卻也是以犧牲系統性能為代價的

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

SELECT*FROMbooks

WHEREnamelike"MySQL%"

  但是如果換用下麵的查詢,返回的結果一樣,但速度就要快上很多:

SELECT*FROMbooks

WHEREname>="MySQL"andname<"MySQM"

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 為了ios開發中tableview讀取數據的方便,先弄了一個只有字母的json表,再將未處理的數據添加到上面。僅用於將首字母作為section head。 [{"initial":"A","list":[]},{"initial":"B","list":[]},{"initial":"C","lis ...
  • 安裝rz工具和wget工具:分別如下: sudo yum install lrzsz;yum install wget 安裝cmake編譯工具:rz -y 選擇下載的cmake工具上傳到伺服器中 安裝c、c++:yum -y install gcc-c++ 環境參數:Linux:Centos6.5, ...
  • 需求場景 項目中有這麼個需求:統計集群中各個節點的數據量存儲大小,不是記錄數。 一開始有點無頭緒,後面查看cassandra官方文檔看到Monitoring章節,裡面說到:Cassandra中的指標使用Dropwizard Metrics庫進行管理。 這些指標可以通過JMX查詢,也可以使用多個內置和 ...
  • 簡要介紹MySQL Flashback 的原理,安裝和使用。 ...
  • Hive的列除了支持基本的數據類型外,還支持使用Struct、Map和Array三種集合數據類型。 假設某表有如下一行,我們用JSON格式來表示其數據結構。在Hive下訪問的格式為 { "name": "John Doe", "salary": 100000.0 , "subordinates": ... ...
  • 1、應儘量避免在 where 子句中使用!=或<>操作符,否則將引擎放棄使用索引而進行全表掃描。 2、對查詢進行優化,應儘量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。 3、應儘量避免在 where 子句中對欄位進行 null 值判斷,否則將導致引擎放棄使用索引 ...
  • 用管理員身份cmd進入mysql安裝目錄bin里,執行 如果殺毒軟體攔截,添加為信任區 ...
  • 執行資料庫查詢時,有完整查詢和模糊查詢之分。 一般模糊語句格式如下: SELECT 欄位 FROM 表 WHERE 某欄位 LIKE 條件 其中關於條件,SQL提供了四種匹配模式: 1、% :表示任意0個或多個字元。可匹配任意類型和長度的字元,有些情況下若是中文,請使用兩個百分號(%%)表示。 例如 ...
一周排行
    -Advertisement-
    Play Games
  • 示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後,項目結構如下所示: MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread] 特性用於需要與 COM 組件交互的應用程式,尤其是依賴單線程模型(如 Windows Forms 應用程式)的組件。在 STA 模式下,線程擁有自己的消息迴圈,這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • 在WinForm中使用全局異常捕獲處理 在WinForm應用程式中,全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理,可以有效地捕獲並處理未預見的異常,從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • 前言 給大家推薦一款開源的 Winform 控制項庫,可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫,同時也提供了工具類庫、擴展 ...
  • 說明 該文章是屬於OverallAuth2.0系列文章,每周更新一篇該系列文章(從0到1完成系統開發)。 該系統文章,我會儘量說的非常詳細,做到不管新手、老手都能看懂。 說明:OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友,請關註我吧(*^▽^*) ...
  • 一、下載安裝 1.下載git 必須先下載並安裝git,再TortoiseGit下載安裝 git安裝參考教程:https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit,Git客戶端,32/6 ...
  • 前言 在項目開發過程中,理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼,還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程:Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 一:背景 1. 講故事 前段時間有位朋友找到我,說他的窗體程式在客戶這邊出現了卡死,讓我幫忙看下怎麼回事?dump也生成了,既然有dump了那就上 windbg 分析吧。 二:WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死,入口門檻很低,後續往下分析就不一定了,不管怎麼說先用 !clrsta ...
  • 前言 人工智慧時代,人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API,工具不僅易於集成,還具備高效處理能力。 本文將介紹一款如何利用這些API,為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...