MySql基礎知識、儲存引擎與常用資料類型

標籤：mysql   基礎知識   儲存引擎   常用資料類型   

  林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka

        本文將主要講解了資料庫的一些基礎知識，介紹了MySql的儲存引擎，最後講了MySql常用的資料類型

1、基礎知識

1.1、資料庫概述

• 簡單地說：資料庫（Database或DB）是儲存、管理資料的容器；

• 嚴格地說：資料庫是“按照某種資料結構對資料進行組織、儲存和管理的容器”。

• 總結：資料永遠是資料庫的核心。

1.2、關聯式資料庫管理系統

• 通過“資料庫管理系統”，資料庫使用者可以輕鬆地實現資料庫容器中各種資料庫物件的訪問（增、刪、改、查等操作），並可以輕鬆地完成資料庫的維護工作（備份、恢複、修複等操作）

• 資料庫使用者無法直接通過作業系統擷取資料庫檔案中的具體內容；

• 資料庫管理系統通過叫用作業系統的進程管理、記憶體管理、裝置管理以及檔案管理等服務，為資料庫使用者提供管理、控制資料庫容器中各種資料庫物件、資料庫檔案的介面。

1.3、關聯式資料庫

        關聯式資料庫，是建立在關聯式資料庫模型基礎上的資料庫，藉助於集合代數等概念和方法來處理資料庫中的資料，同時也是一個被組織成一組擁有正式描述性的表格，該形式的表格作用的實質是裝載著資料項目的特殊收集體，這些表格中的資料能以許多不同的方式被存取或重新召集而不需要重新組織資料庫表格。關聯式資料庫的定義造成中繼資料的一張表格或造成表格、列、範圍和約束的正式描述。每個表格（有時被稱為一個關係）包含用列表示的一個或更多的資料種類。 每行包含一個唯一的資料實體，這些資料是被列定義的種類。

基本特徵

• 關聯式資料庫

        在一個給定的應用領域中，所有實體及實體之間聯絡的集合構成一個關聯式資料庫。

• 關聯式資料庫的型與值

        關聯式資料庫的型稱為關聯式資料庫模式，是對關聯式資料庫的描述，若干域的定義，在這些域上定義的若干關係模式。
關聯式資料庫的值是這些關係模式在某一時刻對應的關係的集合，通常簡稱為關聯式資料庫。

1.4、結構化查詢語言 (SQL)SQL 

        結構化查詢語言 (SQL)SQL(Structured Query Language)是一種介於關係代數與關係演算之間的語言，其功能包括查詢、操縱、定義和控制四個方面，是一個通用的功能極強的關聯式資料庫標準語言。目前，SQL語言已被確定為關聯式資料庫系統的國際標準，被絕大多數商品化關聯式資料庫系統採用。在SQL語言中，指定要做什麼而不是怎麼做，不需要告訴SQL如何訪問資料庫，只要告訴SQL需要資料庫做什麼。可以在設計或運行時對資料控制項使用SQL語句。

1.5、SQL資料庫的體繫結構
SQL資料庫的體繫結構基本上也是三級模式結構

        SQL術語與傳統的關聯式模式術語不同。在SQL中，外模式對應於視圖，模式對應於基本表，元組稱為行，屬性稱為列。內模式對應於隱藏檔。SQL資料庫的體繫結構具有如下特徵：

• SQL模式(Schema)是表和約束的集合。

• 表(Table)是行(Row)的集合，每行是列(Column)的序列，每列對應一個資料項目。

• 表可以是一個基本表，也可以是一個視圖。基本表是實際儲存在資料庫中的表。視圖是從基本表或其他視圖中匯出的表，它本身不隔離儲存區 (Isolated Storage)在資料庫中，也就是說資料庫中只存放視圖的定義而不存放視圖的資料，這些資料仍存放在匯出視圖的基本表中，因此視圖是一個虛表。

• 一個基本表可以跨一個或多個隱藏檔，一個隱藏檔也可存放一個或多個基本表，一個表可以帶若干索引，索引也存放在隱藏檔中。每個隱藏檔與外部儲存空間上一個物理檔案對應，隱藏檔的邏輯結構組成了關聯式資料庫的內模式。

• 使用者可以用SQL語句對視圖和基本表進行查詢等操作。在使用者看來，視圖和基本表是一樣的，都是關係(即表格)。

•   SQL使用者可以是應用程式，也可以是終端使用者。SQL語句可嵌入在宿主語言的程式中使用，宿主語言有Fortran、Cobol、Pascal、PL／I、C和Ada等語言；SQL語言也能作為獨立的使用者介面，供互動環境下的終端使用者使用。

1.6、SQL的組成

• 資料定義 這一部分也稱為“SQL DDL”，用於定義SQL模式、基本表、視圖和索引。

• 資料操縱 這一部分也稱為“SQL DML”。資料操縱分成資料查詢和資料更新兩類。其中資料更新又分成插入、刪除和修改三種操作。

• 資料控制 這一部分也稱為“SQL DCL”。資料控制包括對基本表和視圖的授權，完整性規則的描述，事務控制語句等。

嵌入式SQL使用 這一部分內容涉及到SQL語句嵌入在宿主語言程式中的使用規則。

2、儲存引擎

        關聯式資料庫表是用於儲存和組織資訊的資料結構，可以將表理解為由行和列組成的表格，類似於Excel的試算表的形式。有的表簡單，有的表複雜，有的表根本不用來儲存任何長期的資料，有的表讀取時非常快，但是插入資料時去很差；而我們在實際開發過程中，就可能需要各種各樣的表，不同的表，就意味著儲存不同類型的資料，資料的處理上也會存在著差異，那麼。對於MySQL來說，它提供了很多種類型的儲存引擎，我們可以根據對資料處理的需求，選擇不同的儲存引擎，從而最大限度的利用MySQL強大的功能。
在mysql用戶端中，使用以下命令可以查看MySQL支援的引擎。
show engines;

可以看到MySQL有多種儲存引擎：
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

平時用得最多的是MyISAM和InnoDB，下面主要來介紹這兩種。

2.1、MyISAM

MyISAM表是獨立於作業系統的，這說明可以輕鬆地將其從Windows伺服器移植到Linux伺服器；每當我們建立一個MyISAM引擎的表時，就會在本地磁碟上建立三個檔案，檔案名稱就是表明。例如，我建立了一個MyISAM引擎的tb_Demo表，那麼就會產生以下三個檔案：

1.tb_demo.frm，儲存表定義；

2.tb_demo.MYD，儲存資料；

3.tb_demo.MYI，儲存索引。

MyISAM表無法處理事務，這就意味著有交易處理需求的表，不能使用MyISAM儲存引擎。MyISAM儲存引擎特別適合在以下幾種情況下使用：

1.選擇密集型的表。MyISAM儲存引擎在篩選大量資料時非常迅速，這是它最突出的優點。

2.插入密集型的表。MyISAM的並發插入特性允許同時選擇和插入資料。例如：MyISAM儲存引擎很適合管理郵件或Web伺服器日誌資料。

2.2、InnoDB

InnoDB是一個健壯的事務型儲存引擎，這種儲存引擎已經被很多互連網公司使用，為使用者操作非常大的資料存放區提供了一個強大的解決方案。InnoDB是作為預設的儲存引擎。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下，使用InnoDB是最理想的選擇：

1.更新密集的表。InnoDB儲存引擎特別適合處理多重並發的更新要求。

2.事務。InnoDB儲存引擎是支援事務的標準MySQL儲存引擎。

3.自動災難恢複。與其它儲存引擎不同，InnoDB表能夠自動從災難中恢複。

4.外鍵約束。MySQL支援外鍵的儲存引擎只有InnoDB。

5.支援自動增加列AUTO_INCREMENT屬性。

一般來說，如果需要事務支援，並且有較高的並發讀取頻率，InnoDB是不錯的選擇。

2.3、MEMORY

使用MySQL Memory儲存引擎的出發點是速度。為得到最快的回應時間，採用的邏輯儲存介質是系統記憶體。雖然在記憶體中儲存表資料確實會提供很高的效能，但當mysqld守護進程崩潰時，所有的Memory資料都會丟失。獲得速度的同時也帶來了一些缺陷。它要求儲存在Memory資料表裡的資料使用的是長度不變的格式，這意味著不能使用BLOB和TEXT這樣的長度可變的資料類型，VARCHAR是一種長度可變的類型，但因為它在MySQL內部當做長度固定不變的CHAR類型，所以可以使用。

一般在以下幾種情況下使用Memory儲存引擎：

1.目標資料較小，而且被非常頻繁地訪問。在記憶體中存放資料，所以會造成記憶體的使用，可以通過參數max_heap_table_size控制Memory表的大小，設定此參數，就可以限制Memory表的最大大小。

2.如果資料是臨時的，而且要求必須立即可用，那麼就可以存放在記憶體表中。

3.儲存在Memory表中的資料如果突然丟失，不會對應用服務產生實質的負面影響。

Memory同時支援散列索引和B樹索引。B樹索引的優於散列索引的是，可以使用部分查詢和通配查詢，也可以使用<、>和>=等操作符方便資料採礦。散列索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了，因此散列索引值適合使用在=和<>的操作符中，不適合在<或>操作符中，也同樣不適合用在order by子句中。

可以在表建立時利用USING子句指定要使用的版本。例如：
複製代碼 代碼如下:

create table users(

id smallint unsigned not null auto_increment,

username varchar(15) not null,

pwd varchar(15) not null,

index using hash (username),

primary key (id))engine=memory;

上述代碼建立了一個表，在username欄位上使用了HASH散列索引。下面的代碼就建立一個表，使用BTREE索引。
複製代碼 代碼如下:

create table users(

id smallint unsigned not null auto_increment,

username varchar(15) not null,

pwd varchar(15) not null,

index using btree (username),

primary key (id))engine=memory;

2.4、MERGE

MERGE儲存引擎是一組MyISAM表的組合，這些MyISAM表結構必須完全相同，儘管其使用不如其它引擎突出，但是在某些情況下非常有用。說白了，Merge表就是幾個相同MyISAM表的彙總器；Merge表中並沒有資料，對Merge類型的表可以進行查詢、更新、刪除操作，這些操作實際上是對內部的MyISAM表進行操作。Merge儲存引擎的使用情境。

對於伺服器日誌這種資訊，一般常用的儲存策略是將資料分成很多表，每個名稱與特定的時間端相關。例如：可以用12個相同的表來儲存伺服器日誌資料，每個表用對應各個月份的名字來命名。當有必要基於所有12個日誌表的資料來產生報表，這意味著需要編寫並更新多表查詢，以反映這些表中的資訊。與其編寫這些可能出現錯誤的查詢，不如將這些表合并起來使用一條查詢，之後再刪除Merge表，而不影響原來的資料，刪除Merge表只是刪除Merge表的定義，對內部的表沒有任何影響。

2.5、小結

InnoDB儲存引擎：用於交易處理應用程式，具有眾多特性

MyISAM儲存引擎：主要用於管理費事務表，它提供高速儲存和檢索，以及全文檢索搜尋能力

MEMORY儲存引擎：提供“記憶體中”表，MEMORY儲存引擎的所有資料都在記憶體中，資料的處理速度快，但安全性不高

3、名詞解釋

• 欄位和資料類型

        雖然所有這些引擎都支援通用的資料類型，例如整型、實型和字元型等，但是，並不是所有的引擎都支援其它的欄位類型，特別是BLOG(二進位大對象)或者TEXT文本類型。其它引擎也許僅支援有限的字元寬度和資料大小。
這些局限性可能直接影響到你可以儲存的資料，同時也可能會對你實施的搜尋的類型或者你對那些資訊建立的索引產生間接的影響。這些區別能夠影響你的應用程式的效能和功能，因為你必須要根據你要儲存的資料類型選擇對需要的儲存引擎的功能做出決策。

• 鎖定

        資料庫引擎中的鎖定功能決定了如何管理資訊的訪問和更新。當資料庫中的一個對象為資訊更新鎖定了，在更新完成之前，其它處理不能修改這個資料(在某些情況下還不允許讀這種資料)。
        鎖定不僅影響許多不同的應用程式如何更新資料庫中的資訊，而且還影響對那個資料的查詢。這是因為查詢可能要訪問正在被修改或者更新的資料。總的來說，這種延遲是很小的。大多數鎖定機制主要是為了防止多個處理更新同一個資料。由於向資料中插入資訊和更新資訊這兩種情況都需要鎖定，你可以想象，多個應用程式使用同一個資料庫可能會有很大的影響。
        不同的儲存引擎在不同的對象層級支援鎖定，而且這些層級將影響可以同時訪問的資訊。得到支援的層級有三種:表鎖定、塊鎖定和行鎖定。支援最多的是表鎖定，這種鎖定是在MyISAM中提供的。在資料更新時，它鎖定了整個表。這就防止了許多應用程式同時更新一個具體的表。這對應用很多的多使用者資料庫有很大的影響，因為它延遲了更新的過程。
        頁級鎖定使用Berkeley DB引擎，並且根據上傳的資訊頁(8KB)鎖定資料。當在資料庫的很多地方進行更新的時候，這種鎖定不會出現什麼問題。但是，由於增加幾行資訊就要鎖定資料結構的最後8KB，當需要增加大量的行，也別是大量的小型資料，就會帶來問題。
行級鎖定提供了最佳的並行訪問功能，一個表中只有一行資料被鎖定。這就意味著很多應用程式能夠更新同一個表中的不同行的資料，而不會引起鎖定的問題。只有InnoDB儲存引擎支援行級鎖定。

• 建立索引

        建立索引在搜尋和恢複資料庫中的資料的時候能夠顯著提高效能。不同的儲存引擎提供不同的製作索引的技術。有些技術也許會更適合你儲存的資料類型。
有些儲存引擎根本就不支援索引，其原因可能是它們使用基本表索引(如MERGE引擎)或者是因為資料存放區的方式不允許索引(例如FEDERATED或者BLACKHOLE引擎)。

• 交易處理

交易處理功能通過提供在向表中更新和插入資訊期間的可靠性。這種可靠性是通過如下方法實現的，它允許你更新表中的資料，但僅當應用的應用程式的所有相關操作完全完成後才接受你對錶的更改。例如，在會計處理中每一筆會計分錄處理將包括對借方科目和貸方科目資料的更改，你需要要使用交易處理功能保證對借方科目和貸方科目的資料更改都順利完成，才接受所做的修改。如果任一項操作失敗了，你都可以取消這個交易處理，這些修改就不存在了。如果這個交易處理過程完成了，我們可以通過允許這個修改來確認這個操作。

4、資料類型

• 字串資料型別

1.char（n）和varchar（n）中括弧中n代表字元的個數，並不代表位元組個數，所以當使用了中文的時候(UTF8)意味著可以插入m個中文，但是實際會佔用m*3個位元組。
2.同時char和varchar最大的區別就在於char不管實際value都會佔用n個字元的空間，而varchar只會佔用實際字元應該佔用的空間+1，並且實際空間+1<=n。
3.超過char和varchar的n設定後，字串會被截斷。
4.char的上限為255位元組，varchar的上限65535位元組，text的上限為65535。
5.char在儲存的時候會截斷尾部的空格，varchar和text不會。
6.varchar會使用1-3個位元組來儲存長度，text不會。

• 數值資料類型

整型

 

上面定義的都是有符號的，當然了，也可以加上unsigned關鍵字，定義成無符號的類型，那麼對應的取值範圍就要翻翻了，比如：

tinyint unsigned的取值範圍為0~255。

浮點型

 

• 位元據類型

        二進位類型可儲存任何資料，如文字、映像、多媒體等。具體類型描述如下：

                              

• 資料類型的屬性

  林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka

MySql基礎知識、儲存引擎與常用資料類型