mysql設計實現概念點滴，

 

資料庫是一個比較複雜的系統，因此，不論是從設計概念上還是實現上，必須要把維度劃分清晰。

架構

把接入和解析拆分不同功能模組。

其中在核心模組，我們也得從不同的維度考慮和設計系統。

並發控制，緩衝，儲存

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

緩衝

Mysql

核心中的緩衝

查詢快取
 key-value
，表更新則失效

索引緩衝
 25%

線程緩衝
  
線程描述，串連緩衝區（用戶端串連資訊和發送資料緩衝區），資料讀取緩衝區（
MyISAM
專用）


表緩衝

（中繼資料）


其他（批量插入緩衝，連結操作緩衝，排序緩衝等）

Note:

mysql
處理中間資料(如排序等)時會使用(
對包含Blob
和Text
則用MyISAM
，所以一般要盡量避免，否則會導致涉及隱式暫存資料表的操作開銷極大)

 

InnoDB

引擎中的緩衝

緩衝池：插入緩衝，資料頁，索引頁，自適應雜湊，鎖資訊，資料字典

日誌緩衝

額外記憶體池：資料結構本身分配記憶體，如緩衝控制對象，

 

並發控制

包括了鎖和事務，然後它們自身又有不同的維度和層級

鎖

      類型：讀寫鎖
      粒度：行鎖，表鎖

事務

      隔離級：Read Uncommited, ReadCommited, repeatable read(MVCC),serializable
      Note：
           操作事務，不同的引擎支援不一樣。innodb 和 MyISAM表的混合操作失敗後會導致資料不一致 

MVCC避免很多操作的加鎖操作。開放式並行存取VS封閉式並行存取。更新操作都會對資料增加版本號碼，select操作只對比目前的版本號小的資料進行操作。

不同的儲存引擎比較

MyISAM支援全文檢索索引，壓縮（適合不修改的資料，壓縮的表若要改需要解壓），不支援事務和行層級鎖
儲存結構：資料檔案+索引檔案

InnoDB將所有資料共同儲存在一個或幾個資料檔案中（資料表空間）。 
MVCC提高並發能力。基於聚簇索引建立（可以提供非常快速的主鍵尋找效能）。支援事務和行層級鎖

實體儲存體上的分層：資料表空間，段，區，頁

線程：
Master, read, write, log, insert buffer
，可以根據配置增加
read,write
的線程數

Master
線程的定時任務
1
秒，
10
秒，
 background

根據配置同步髒頁，合并緩衝，刪除
REDU
，同步處理記錄

對非叢集索引的插入和更新，會先加入到緩衝中，
 
後面批量寫入

兩次寫，增加可靠性

Memory速度快，但是功能有限制，不支援Blob和Text，Varchar效能有問題。

Archive

不支援索引，針對高速插入和壓縮排行了最佳化。

Blackhole

不儲存，唯寫日誌；用於複製設定或稽核線索

 
其它,CVS, NDB Cluster, Falcon, solidDB,PBXT,Maria,,,

總結：

InnoDB使用了叢集索引，而且有自己的緩衝區，把表資料載入記憶體，因此，有時遠比MyISAM快

監控

長查詢日誌，我們在設計系統中也可以考慮這種比較通用的業務層級日誌
 

使用注意

varchar適合那些最大長度遠大於平均長度，而且很少發生更新。（更新會導致片段）

BLOB和Text，MySQL當做實體物件處理，儲存引擎會特別的儲存它們，較大時使用“外部”儲存地區

 最佳化技巧：

COUNT(*):
代表忽略所有的列，否則統計時會對那些NULL的列進行忽略，效能就很不好了。COUNT(*)在沒有WHERE限制下效能很高

limit 10000,20 :效能開銷，導致讀取10020,忽略前10000行；最佳化技巧：   在覆蓋索引上進行位移而非全行資料位移。如select f.id,f.desc from t.f inner join (select id from t.f order by orderfield limit 10000,20) as f using (id);   改成使用位置查詢 select id, desc from t where position between 10000 and 10020 order by position;

 緩衝

查詢語句中有不確定函數，比如now()就不會使用緩衝。另外，必須要完全一致，大小寫或空格都將導致緩衝不命中

InnoDB使用MVCC架構，使查詢快取互動相對複雜。表上的任何鎖導致查詢不可緩衝。根據事務ID判斷緩衝是否失效。

    可以把緩衝策略改成DEMAND，只針對部分插敘使用緩衝。 

另外批量寫入也會比多行寫入效率高（只引起一次緩衝失效）。

索引

B-Tree vs Hash Index

Hash索引速度快，但是其局限：

1. 無法使用索引中的值來避免讀取行（不過一般記憶體中的行速度不錯)

2. 無法使用索引進行排序

3. 不支援部分索引值匹配。 這樣導致多索引值表中，查詢前兩個也會導致全表掃描。

4. 無法範圍查詢（跟排序有些重合）

InnoDB對查詢較多的資料建立自適應雜湊索引（Adaptive Hash Index)，把常用這些值在記憶體中hash索引起來。

偽hash作法，對 string 計算其crc32，然後把該值作為索引。 

R-Tree, 空間索引。 

全文索引， FULLTEXT， MyISAM表的特殊索引。全文索引引用於MATCH AGAINST操作，而不善於WHERE。首碼索引（字串的前幾位索引） 叢集索引，更新叢集索引列開銷昂貴。插入新行可能導致分頁，浪費儲存空間。對叢集索引的表順序插入會導致單點競爭.
索引減少了訪問的行數，這樣對於支援行鎖的，比如InnoDB,可以減少了鎖定

排序最佳化

當無法使用索引排序時，就要自己對結果排序（記憶體或磁碟上），這個過程叫檔案排序（FileSort)

雙路排序（Two Passes)讀取行指標和被排序的列，排完後對再根據行指標查詢輸出。缺點是，輸出時會根據行指標再去讀取資料，導致大量的隨機I/0。

單路排序(Single Pass)根據輸出需要，第一次把輸出的列和排序列按照 ORDERBY 列進行排序。

 

預存程序

減少網路和解析消耗

合并表和分區表

合并表

從邏輯上合并幾個表。用於日誌記錄和資料倉儲，可方便的增刪表。 

分區表的限制

避免查詢訪問所有分區但是仍然鎖定了所有分區

分區不支援外鍵

無法使用Load Index into cache

資料重新整理

innodb_flush_log_at_trx_commit，0， 每秒重新整理日誌緩衝到記錄檔
1，緩衝些到檔案中，並且在事務提交時把緩衝刷寫到持久性磁碟。 ( 會導致IO阻塞直至寫入磁碟完成。 )

2，每次提交寫記錄檔。每秒清理緩衝

InnoDB雙寫緩衝，寫入磁碟前先寫入雙緩衝中。這樣可以放在磁碟沒有寫入完全的資料損毀。

並發寫入調優

MyISAM的concurrent_insert變數0, 不允許並發，每次插入都鎖表

1, 只要表中沒有空缺就允許（預設）

2, 強制並發插入到表尾，會導致表的片段增多，需要定期對錶進行最佳化