高性能MySql

資料類型：

1、varchar，字元串列的最大長度比平均長度大和諾，适合用varchar類型；

2、char,适合存儲很短的字元串，或者所有值都接近同一個長度；

3、時間日期類型，盡量使用TIMESTAMP,它比DATETIME空間效率更高；

1、緩存表 、彙總表和影子表

2、 除非需要用到某些InnoDB不具備的特性，并且沒有其他辦法可以替代，否則都應該優先選擇InnoDB引擎。

3、mysql可以對整型指定寬度，例如：INT(11),對大多數應用是沒有意義的，整型長度隻對在mysql一些互動工具中作為顯示整型的長度，對于值計算來源INT(1)和INT(11)是相同的。

4、varchar适合場景：字元串列的最大長度比平均長度大很多；char适合場景：存儲很短的額字元串，或者所有值都接近同一長度，例如md5加密後的值；

5、大資料量alter table會鎖表并且重建整張表，執行時間可達幾個小時甚至幾天，解決方案：一、現在一台不提供服務的機器上執行alter table操作，然後和提供服務的主庫進行切換；二、“影子拷貝”，即建立一張與源表無關的新表，然後通過重命名和删除表的方式交換兩張 表；

6、 mysql disable keys禁用索引，此操作對單條索引無效，隻對多條索引有用；

範式

第一範式：無重複列，表中的每一列都是不可分割的基本資料項

第二範式：屬性完全依賴于主鍵，不能存在僅依賴于關鍵一部分的屬性

第三範式：屬性不傳遞依賴于其它非主屬性，非主鍵列必須直接依賴于主鍵，而不能傳遞依賴。

反範式：用空間換時間，将資料備援在多張表中，查詢中無須關聯

範式優點：

(1) 範式化的更新操作通常比反範式化要快

(2)當資料較好地範式化時，就隻有很少或者沒有重複資料，是以隻需要修改更少的資料

(3)範式化的表通常更小，占用更小的記憶體，是以處理速度更快

(4)很少有多餘的資料，意味着檢索清單時更少需要distinct和group by語句時間

範式缺點：

符合範式的schema設計，查詢時通常需要關聯查詢

schema設計簡單原則

盡量避免過度設計，例如會導緻極其複雜查詢的schema設計，或者有很多列的表設計；

使用小而簡單的合适資料類型，除非真是資料模型中有确切的需要，否則應該盡可能地避免使用NULL值

盡量使用相同的資料類型存儲相似或相關的值，尤其是要在關聯條件中使用的列；

注意可變長字元串，其在臨時表或者排序時可能悲觀的按最大長度配置設定記憶體

盡量使用整型辨別列

避免使用mysql已經遺棄的特性，例如指定浮點數的精度（可用decimal代替），或者整數的顯示寬度

小心使用ENUM和SET，盡量避免使用；避免使用BIT；

1、 不同的存儲引擎的所在的工作方式并不一樣，也不是所有得存儲引擎支援所有類型的索引；

2、 索引的優點：索引大大減少了伺服器需要掃描的資料量；索引可以幫助伺服器避免排序和臨時表；索引可以将随機IO變為順序IO;

1、獨立的列，索引列不能是表達式的一部分；

2、選擇合适的索引順序，将選擇性高的索引放在最前面；

3、聚蔟索引，包含B-Tree索引和資料行；優點：可以将相關資料儲存在一起，資料通路更快，使用聚簇索引掃描的查詢可以直接使用頁節點中的主鍵值；缺點：資料插入速度依賴于順序插入，聚蔟索引可能導緻全表掃描；

4、覆寫索引，如果一個索引包含所有需查詢的字段的值，該索引為覆寫索引。覆寫索引可以極大的提高查詢性能。在大多數存儲引擎中，覆寫索引隻能覆寫那些隻通路索引中部分列的查詢。

5、所用索引掃描來做排序，隻有當所用的列順序和order by子句的順序完全一緻，并且所有列的排序方向都一樣，mysql才能使用索引來對結果進行排序；

6、備援和重複索引，重複索引是指相同的列上按照相同個順序建立的相同類型的索引；要盡量不使用備援索引，盡量擴充已有的索引而不是建立新索引；

7、要删除未使用的索引，可提升insert update執行效率；

1、找到并修複損壞的表；

2、更新索引統計資訊，show index from table可查詢索引資訊；

3、減少索引和資料的碎片，optimize table可有效整理資料，去除資料碎片；例如：delete table後，optimize一下，可有效降低資料占用空間；

1、 哈希索引隻包含哈希值和行指針，而不存儲字段值，是以不能使用索引中的值來避免讀取行；

2、 哈希索引資料并不是按照索引值順序存儲的，是以也就無法用于排序；

3、 哈希索引頁不支援部門索引列比對查找，因為哈希索引始終是使用索引列的全部内容來計算哈希值的；

4、哈希索引隻支援等值比較查詢；不支援範圍查詢；

選擇索引的三個原則：

單行通路很慢的。如果伺服器從存儲中讀取一個資料塊隻是為了擷取其中一行，那麼久浪費了很多工作。最好讀取的塊中能包含盡可能多所需要的行。使用索引建立位置引用可提升效率。

按順序通路範圍資料是很快的，這有兩個原因。第一，順序IO不需要多次磁盤尋道，是以比随機IO要快很多。第二，如果伺服器能夠按需要順序讀取資料，那麼就不再需要額外的排序操作，并且group by查詢也無須再做排序和将行按組進行聚合計算了。

索引覆寫查詢是很快的。如果一個索引包含了查詢需要的所有列，那麼存儲引擎就不需要再回标查找行。這避免了大量的單行通路。

查詢生命周期：從用戶端，到伺服器，然後在伺服器上進行解析，生成執行計劃，執行，并傳回結果給用戶端。

低效查詢分析方法：

确認應用程式是否在檢索大量超過需要的資料。通常意味着通路了太多的行，也有可能通路太多的列。

确認mysql伺服器層是否在分析大量超過需要的資料行。

低效查詢典型案列：

查詢不需要的記錄

多表關聯時傳回全部列

總是取出全部列

重複查詢相同的資料

衡量查詢開銷的三個名額：

響應時間

響應時間包括服務時間和排隊時間；服務時間：是指資料庫處理這個查詢真正花了多長時間，排隊時間：伺服器因為等待某些資源而沒有真正執行查詢的時間（可能是IO，行鎖等等）；

掃描的行數

傳回的行數

較短的行的通路速度更快，記憶體中的行比磁盤中的行通路速度更快；較短的行數，是在記憶體中查詢，當行數較多時則在磁盤中查詢；

一個複雜查詢還是多個簡單查詢

切分查詢（大查詢分為小查詢，例如：大掃描行數查詢切分成多個小掃描行數的查詢）

分解關聯查詢，優點：讓緩存效率更高；讓單個查詢減少鎖競争；在應用層做關聯，容易對資料庫進行拆分，提高系統性能；減少備援記錄的查詢；

mysql查詢過程：

用戶端發送一條查詢給伺服器

伺服器先查詢緩存，如果命中了緩存，直接傳回結果；否則，進入下一步；

伺服器進行sql解析、預處理，再由優化器生成對應的執行計劃；

mysql根據優化器生成的執行計劃，再調用存儲引擎API來執行查詢；

将查詢結果傳回給用戶端；

SHOW FULL PROCESSLIST可檢視目前狀态；

sleep:線程正在等待用戶端發送新的請求；

Query:線程正在執行查詢或者正在将結果發送給用戶端；

Locked:該線程正在等待表鎖；

Analyzing and statistics:線程正在收集存儲引擎的統計資訊，并生産查詢的執行計劃；

Coping to tmp table:線程正在執行查詢，并将其結果複制到臨時表中；

Sorting result:線程正在對結果集進行排序；

Sending data:線程可能在多種狀态之間傳送資料，或者正在生成結果集，或者正在向用戶端發送資料；

mysql在進行檔案排序的時候需要使用的臨時存儲空間可能比想象的要大得多。

不需要聽取那些關于子查詢的“絕對真理”；

應該用測試來嚴重對子查詢的執行計劃和響應時間的假設；

mysql不允許對同一張表同僚進行查詢和更新操作。

優化count()查詢

簡單優化，反向count(1)，再減去查詢結果；使用近似值代替；使用彙總表，定時彙總資料，總彙總表裡查詢結果；

優化關聯查詢

確定on和using列上有索引；確定order by和group by隻包含一列；

優化子查詢

盡可能使用關聯查詢代替；

優化group by和distinct

優化limit查詢

避免過多的offset；select * from order where id > 10030 order by id desc limit 20;

優化union查詢

如果無重複資料，使用union all 代替union；

優化通常都需要三管齊下：不做、少做、快速地做

可擴充性：當增加資源以處理負載和增加容量時系統能夠獲得的投資産出率。

向上擴充：也叫垂直擴充，購買更多性能強悍的硬體；

向外擴充：向外擴充政策劃分為三個部分：複制、拆分、以及資料分片

通過多執行個體擴充：每台伺服器上運作過個執行個體，然後劃分伺服器的硬體資源，将其配置設定給每個執行個體。在一台性能強悍的硬體上可以獲得10倍到15倍的合并系數。你需要平衡管理複雜度代價和更有性能的收益。

通過叢集擴充：未來典型的叢集資料庫可能更像是SQL和NoSQL的混合體，有多重存取機制來滿足不同的使用需求。MySQL Cluster(NDB Cluster)

向内擴充：處理不斷增長的資料和負載最簡單的辦法是對不再需要的資料進行歸檔和清理。

做歸檔和清理時考慮以下幾點：

對應用的影響

要歸檔的行

維護資料一緻性

避免資料丢失

解除歸檔

保持活躍資料獨立，即使不真的把老資料轉移到别的伺服器，也許應用也能受益于活躍資料和飛活躍資料的隔離。可以有一下集中做法：

1、将表劃分為幾個部分

2、MySQL分區

3、基于時間的資料分區

負載均衡

負載均衡有五個常見的目的：

可擴充性、高效性、可用性、透明性、一緻性

一、直接連接配接

1.1 複制上的讀寫分離

1.2 修改應用的配置

1.3 修改DNS名

1.4 轉移IP位址

二、引入中間件

2.1 負載均衡器

2.2 負載均衡算法

随機、輪詢、最少連接配接數、最快響應、哈希、權重

2.3 在伺服器池中增加和删除伺服器

三、一主多備的負載均衡

id列：這一列總是包含一個編号，辨別SELECT所屬的行。