Java Web資料庫篇之MySQL概述

Java Web系列文章彙總貼: Java Web知識總結彙總

MySQL概述

MySQL簡介

1、MySQL是目前最流行的關系型資料庫管理系統之一，在WEB應用方面，MySQL是最好的RDBMS(Relational DataBase Management System，關系資料庫管理系統)軟體。

2、關系資料庫的優勢在于，将資料儲存在不同的表中，而不是将所有資料放在一個大倉庫内，這樣就增加了速度并提高了靈活性。

3、MySQL采用的SQL是通路資料庫的最常用标準化語言。其體積小，速度快，并且開放源碼，大大降低了使用成本。

MySQL系統特性包括：(來自百度百科)

1．使用 C和 C++編寫，并使用了多種編譯器進行測試，保證了源代碼的可移植性。

2．支援 AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種作業系統。

3．為多種程式設計語言提供了 API。這些程式設計語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby,.NET和 Tcl 等。

4．支援多線程，充分利用 CPU 資源。

5．優化的 SQL查詢算法，有效地提高查詢速度。

6．既能夠作為一個單獨的應用程式應用在用戶端伺服器網絡環境中，也能夠作為一個庫而嵌入到其他的軟體中。

7．提供多語言支援，常見的編碼如中文的 GB 2312、BIG5，日文的 Shift_JIS等都可以用作資料表名和資料列名。

8．提供 TCP/IP、ODBC 和 JDBC等多種資料庫連接配接途徑。

9．提供用于管理、檢查、優化資料庫操作的管理工具。

10．支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。

11．支援多種存儲引擎。

12. MySQL 是開源的，是以你不需要支付額外的費用。

13. MySQL 使用标準的 SQL資料語言形式。

14. MySQL 對 PHP 有很好的支援，PHP是比較流行的 Web 開發語言。

15. MySQL是可以定制的，采用了 GPL協定，你可以修改源碼來開發自己的 MySQL 系統。

16. 線上 DDL/更改功能，資料架構支援動态應用程式和開發人員靈活性（5.6新增）

17. 複制全局事務辨別，可支援自我修複式叢集（5.6新增）

18. 複制無崩潰從機，可提高可用性（5.6新增）

19. 複制多線程從機，可提高性能（5.6新增）

20. 3倍更快的性能（5.7 [3] 新增）

21. 新的優化器（5.7新增）

22. 原生JSON支援（5.7新增）

23. 多源複制（5.7新增）

24. GIS的空間擴充 （5.7新增）

MySQL架構

MySQL邏輯架構：

MySQL工作流程：

更多：

MySQL邏輯架構及工作流程

MySQL必知必會知識點總結一二

MySQL查詢優化

MySQL查詢過程

MySQL整個查詢執行過程，總的來說分為6個步驟：

• 用戶端向MySQL伺服器發送一條查詢請求
• 伺服器首先檢查查詢緩存，如果命中緩存，則立刻傳回存儲在緩存中的結果。否則進入下一階段
• 伺服器進行SQL解析、預處理、再由優化器生成對應的執行計劃
• MySQL根據執行計劃，調用存儲引擎的API來執行查詢
• 将結果傳回給用戶端，同時緩存查詢結果

MySQL查詢性能優化

MySQL查詢性能優化要從三個方面考慮，庫表結構優化、索引優化和查詢優化。

庫表結構優化

1、選擇優化的資料類型

• 選擇更小的合适的資料類型
• 建索引的列，盡量不允許為NULL
• TimeStamp存儲優先于Datetime
• 使用tinyint代替bit，boolean等
• blob和text的使用場景

2、MySQL scheme的陷阱

• 表的設計不能有太多的列，數千的列會影響性能
• 關聯操作設計的表不要太多，否則執行會比較慢(12以内)

索引優化：

1、索引類型

• B-tree(B+tree)、哈希、空間資料索引、全文索引

2、索引政策

• 獨立列
• 字首索引-如果列很長，通常可以索引開始的部分字元，這樣可以有效節約索引空間，進而- 提高索引效率
• 多列索引-适應于多個查詢條件時

3、建立索引的代價

• 增加了資料庫的存儲空間，
• 在插入和修改資料時要花費較多的時間（因為索引也要随之變動）

查詢優化

1、查詢方式優化(切分、分解，分而治之)

2、查詢器優化，盡量避免不使用索引的情況出現

• 放棄索引的場景：!=/<>、in、is NULL等符号的出現，索引列出現運算場景
• 用Between代替in，Distinct代替Group by，Union all 代替 Union，如果可以的話
• Like查詢時，打頭字母搜尋，如XXX%，這種比對方式可使用索引

3、使用Explain檢視執行計劃

4、打開慢查詢，檢視耗時多的慢查詢語句

更多：

MySQL邏輯結構及性能優化原理

MySQL查詢性能優化

MySQL查詢語句優化

MyISAM與InnoDB引擎

概述

MyISAM更适合讀取大于寫入的業務，同時不支援事務。

InnoDB支援事務，行級鎖支援，外鍵支援，效率上比MyISAM稍慢。

詳細：

MySQL存儲引擎中的MyISAM和InnoDB差別詳解

檔案存儲

MyISAM實體檔案結構

.frm檔案：與表相關的中繼資料資訊都存放在frm檔案，包括表結構的定義資訊等(與引擎無關的檔案)。

.myd檔案：myisam存儲引擎專用，用于存儲myisam表的資料

.myi檔案：myisam存儲引擎專用，用于存儲myisam表的索引相關資訊

InnoDB的實體檔案結構

.frm與表相關的中繼資料資訊都存放在frm檔案，包括表結構的定義資訊等(與引擎無關的檔案)。

.ibd檔案和.ibdata檔案：這兩種檔案都是存放innodb資料的檔案，之是以用兩種檔案來存放innodb的資料，是因為innodb的資料存儲方式能夠通過配置來決定是使用共享表空間存放存儲資料，還是用獨享表空間存放存儲資料。

• 獨享表空間存儲方式使用.ibd檔案，并且每個表一個ibd檔案
• 共享表空間存儲方式使用.ibdata檔案，所有表共同使用一個ibdata檔案
• 決定使用哪種方式的參數在mysql的配置檔案中 innodb_file_per_table=ON/OFF

InnoDB共享表空間與獨立表空間

簡介

Innodb存儲引擎可将所有資料存放于ibdata*的共享表空間，也可将每張表存放于獨立的.ibd檔案的獨立表空間(部分資料)。

共享表空間以及獨立表空間都是針對資料的存儲方式而言的。

• 共享表空間: 某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中，預設這個共享表空間的檔案路徑在data目錄下。 預設的檔案名為:ibdata1 初始化為10M。

  可以在配置檔案my.cnf中使用參數innodb_data_file_path設定一個或者多個檔案組成表空間。

  共享表空間中會包含Undo資訊，在事務未送出時資料即已經寫入了表空間檔案，當事務rollback時Undo資訊不會被删除，但是此空間會被标記，後續會以覆寫的方式被重新使用。

• 獨立表空間: 每一個表都将會生成以獨立的檔案方式來進行存儲，每一個表都有一個.frm表描述(結構)檔案，還有一個.ibd檔案。 其中這個檔案包括了單獨一個表的資料、索引、插入緩沖的内容，其餘資料仍存放在共享表空間中，預設情況下獨立表空間的存儲位置也是在表的位置之中。

  可以在配置檔案my.cnf中通過配置參數innodb_file_per_table = ON來開啟獨立表空間。

共享表空間VS獨立表空間

共享表空間

優點：

可以将表空間分成多個檔案存放到各個磁盤上（表空間檔案大小不受表大小的限制，如一個表可以分布在不同的檔案上）。資料和檔案放在一起友善管理。

缺點：

所有的資料和索引存放到一個檔案中，雖然可以把一個大檔案分成多個小檔案，但是多個表及索引在表空間中混合存儲，這樣對于一個表做了大量删除操作後表空間中将會有大量的空隙，特别是對于統計分析，日值系統這類應用最不适合用共享表空間。

獨立表空間：

優點：

1. 每個表都有自已獨立的表空間。

2. 每個表的資料和索引都會存在自已的表空間中。

3. 可以實作單表在不同的資料庫中移動。

4. 空間可以回收（除drop table操作處，表空不能自已回收）

• Drop table操作自動回收表空間，如果對于統計分析或是日值表，删除大量資料後可以通過:alter table TableName engine=innodb; 回縮不用的空間。
• 對于使innodb-plugin的Innodb使用turncate table也會使空間收縮。
• 對于使用獨立表空間的表，不管怎麼删除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。

缺點：

單表增加過大，如超過100個G。

相比較之下，使用獨占表空間的效率以及性能會更高一點。

參考：

mysql 共享表空間、獨立表空間

更多：

MySQL存儲引擎MyISAM與InnoDB差別總結整理

MySQL技術内幕–innodb存儲引擎圖解

MySQL技術内幕 InnoDB存儲引擎-閱讀筆記

MySQL體系結構和存儲引擎概述

MySQL三大特性

• 插入緩沖 – 保證寫性能
• 兩次寫 – 高可靠性
• 自适應哈希 – 提升讀寫性能

參考：

[MySQL] InnoDB三大特性之 - 插入緩沖

MySQL InnoDB特性：兩次寫（Double Write）

InnoDB Redo Flush及髒頁重新整理機制深入分析

mysql redo日志與刷髒

[MySQL] InnoDB三大特性之 - 兩次寫

[MySQL] InnoDB三大特性之 - 自适應哈希索引

MySQL知識點

MySQL常見問題