MYSQL設計最佳化，mysql設計

MYSQL設計最佳化，mysql設計

本文將從各方面介紹最佳化mysql設計的一些方式。

1、最佳化sql語句(1)定位需要最佳化的sql語句1)show status統計SQL語句頻率

對Myisam和Innodb儲存引擎都計數的參數：

SHOW STATUS可以根據需要顯示session層級的統計結果和global層級的統計結果。

1.Com_select  執行select操作的次數，一次查詢只累加1；
2.Com_insert 執行insert操作的次數，對於批量插入的insert操作，只累加一次；
3.Com_update 執行update操作的次數；
4.Com_delete　執行delete操作的次數；

執行如：SHOW STATUS WHERE Variable_name = 'Com_select';

對Innodb儲存引擎計數的參數（計算的方式不一樣）：
1.Innodb_rows_read select查詢返回的行數；
2.Innodb_rows_inserted 執行Insert操作插入的行數；
3.Innodb_rows_updated 執行update操作更新的行數；
4.Innodb_rows_deleted 執行delete操作刪除的行數；
通過以上幾個參數，可以很容易的瞭解當前資料庫的應用是以插入更新為主還是以查詢操作為主，以及各種類型的SQL大致的執行比例是多少。
對於更新操作的計數，是對執行次數的計數，不論提交還是復原都會累加。

對於事務型的參數
1.Com_commit 事務提交次數
2.Com_rollback 交易回復次數
對於復原操作非常頻繁的資料庫，可能意味著應用編寫存在問題。

資料庫的基本情況的參數：
1.Connections 試圖串連Mysql伺服器的次數
2.Uptime 伺服器工作時間

3.Slow_queries 慢查詢的次數

2)定位執行效率較低的SQL語句兩種方式定位執行效率較低的SQL語句：
(1)通過慢查詢日誌定位那些執行效率較低的sql語句（需要查詢結束後），用--log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的記錄檔.
(2)使用show processlist命令查看當前MySQL在進行的線程，包括線程的狀態，是否鎖表等等，可以即時的查看SQL執行情況，同時對一些鎖表操作進行最佳化。

3)EXPLAIN命令分析SQL語句通過explain或者desc 擷取MySQL如何執行SELECT語句的資訊
EXPLAIN SELECT * FROM message a LEFT JOIN mytable b ON a.id = b.id WHERE a.id=1;
返回結果
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
|   1    | SIMPLE        | b     | ALL    | NULL             | NULL           | NULL      |       | 9999   |              |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
select_type：select 類型
table：      輸出結果集的表
type：       表示表的連線類型
①當表中僅有一行是最佳的連線類型；
②當select操作中使用索引進行表串連時type的值為ref；
③當select的表串連沒有使用索引時，經常會看到type的值為ALL，表示對該表進行了全表掃描，這時需要考慮通過建立索引來提高表串連的效率。

possible_keys：表示查詢時,可以使用的索引列.
key：          表示使用的索引
key_len：      索引長度
rows：         掃描範圍
Extra：執行情況的說明和描述

例如上面的例子，因為是對b表的全表掃描導致效率下降，則對b表的 id 欄位建立索引，查詢需要掃描的行數將會減少。
返回結果
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
|   1    | SIMPLE        | b     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+(2)sql語句最佳化方式

1)大批量插入資料 

1)對於Myisam類型的表，可以通過以下步驟快速的匯入大量的資料。 
前後兩個命令用來開啟或者關閉Myisam表非唯一索引的更新。在匯入大量的資料到一個非空的Myisam表時，通過設定這兩個命令，可以提高匯入的效率。
ALTER TABLE mytable DISABLE KEYS;
INSERT INTO mytable(id, username, city, age) VALUES(1, 'name1', 'city1', 10),(2, 'name2', 'city2', 20),(3, 'name3', 'city3', 30);
ALTER TABLE mytable ENABLE KEYS;
對於匯入大量資料到一個空的Myisam表，預設就是先匯入資料然後才建立索引的，所以不用進行設定。

2)對於Innodb類型的表，我們有以下幾種方式可以提高匯入的效率（對Innodb類型的表，上面的方式並不能提高匯入資料的效率）
①因為Innodb類型的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效提高匯入資料的效率。
如果Innodb表沒有主鍵，那麼系統會預設建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高匯入資料的效率。
②在匯入資料前執行SET UNIQUE_CHECKS=0，關閉唯一性校正，在匯入結束後執行SET UNIQUE_CHECKS=1，恢複唯一性校正，可以提高匯入的效率。
SET UNIQUE_CHECKS=0;
SET UNIQUE_CHECKS=1;
③如果使用自動認可的方式，建議在匯入前執行SET AUTOCOMMIT=0，關閉自動認可，匯入結束後再執行SET AUTOCOMMIT=1，開啟自動認可，也可以提高匯入的效率。
SET AUTOCOMMIT=0;

SET AUTOCOMMIT=1;

2)最佳化insert語句

1)如果同時插入很多行，請使用多個值的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。
Insert into test values(1,2),(1,3),(1,4)…
2)如果從不同客戶插入很多行，能通過使用INSERT DELAYED 語句得到更高的速度。 
Delayed 的含義是讓insert 語句馬上執行，其實資料都被放在記憶體的隊列中，並沒有真正寫入磁碟；這比每條語句分別插入要快的多；
LOW_PRIORITY 剛好相反，在所有其他使用者對錶的讀寫完後才進行插入；
3)將索引檔案和資料檔案分在不同的磁碟上存放（利用建表中的選項）；
4)如果批量插入，可以增加bulk_insert_buffer_size變數值的方法來提高速度，但是，這隻能對myisam表使用；
5)當從一個文字檔裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍；
6)根據應用情況使用 replace 語句代替 insert；
7)根據應用情況使用 ignore 關鍵字忽略重複記錄。
INSERT DELAYED INTO mytable(id, username, city, age) VALUES(4, 'name4', 'city4', 40);
INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);
REPLACE INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);

INSERT IGNORE INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);

3)最佳化group by語句

預設情況下，MySQL排序所有GROUP BY col1，col2，....（如同指定了ORDER BY  col1，col2，...）
如果查詢包括GROUP BY但想避免排序結果的消耗，可以指定 ORDER BY NULL禁止排序。
例如：

SELECT * FROM mytable GROUP BY username ORDER BY NULL;

4)最佳化order by語句

以下情況可以使用索引:
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;  --order by欄位都為同一複合式索引的一部分
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, --key_part2 DESC;--where條件和order by使用相同的索引欄位 
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;--order by的的所有欄位順序相同
以下情況不使用索引:
1)SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；--order by的欄位混合ASC和DESC
2)SELECT * FROM t1 WHERE key2=constant ORDER BY key1；--用於查詢行的關鍵字與ORDER BY中所使用的不相同

3)SELECT * FROM t1 ORDER BY key1, key2；--對不同的關鍵字使用ORDER BY

5)最佳化join語句 

Mysql4.1開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。
但是，有些情況下，子查詢可以被更有效率的串連(JOIN).. 替代。

假設我們要將所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用串連(JOIN).. 來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，效能將會更好，查詢如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL 

串連(JOIN).. 之所以更有效率一些，是因為 MySQL不需要在記憶體中建立暫存資料表來完成這個邏輯上的需要兩個步驟的查詢工作。

6)insert update delete的調度優先順序

MySQL還允許改變語句調度的優先順序，使來自多個用戶端的操作更好地協作(需要判斷應用是以查詢為主還是以更新為主的)。
以下改變調度策略的方法主要是針對Myisam儲存引擎的（對於Innodb儲存引擎，語句的執行是由獲得行鎖的順序決定的）
預設調度策略：
1）寫入操作優先於讀取操作。
2）對某張資料表的寫入操作某一時刻只能發生一次，寫入請求按照它們到達的次序來處理。
3）對某張資料表的多個讀取操作可以同時地進行。
語句調節符可以修改調度策略（以下是查詢為主的）：
1）LOW_PRIORITY 關鍵字應用於 DELETE 、 INSERT 、 LOAD DATA 、 REPLACE和UPDATE 。
2）HIGH_PRIORITY關鍵字應用於SELECT和INSERT語句。
3）DELAYED關鍵字應用於INSERT和REPLACE語句。
如果寫入操作是一個LOW_PRIORITY（低優先順序）請求，那麼讀取操作優先順序會高於寫操作。（在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。只有在沒有其它的讀取者的時候，才允許寫入者開始操作。這種調度方式可能存在LOW_PRIORITY的寫入操作永遠被阻塞的情況。）

SELECT查詢被設定為HIGH_PRIORITY（高優先順序），則也會調整SELECT操作到正在等待的寫入操作之前。

設定方式：

1）啟動方式

如果你希望所有支援LOW_PRIORITY選項的語句都預設地按照低優先順序來處理，那麼請使用--low-priority-updates選項來啟動伺服器。

2）sql方式

通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優先順序，可以消除該選項對單個INSERT語句的影響。

INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALUES(7, 'name7', 'city7', 70);

2、最佳化資料表(1)最佳化表的資料類型函數PROCEDURE ANALYSE()可以對資料表中的列的資料類型提出最佳化建議，根據實際情況考慮是否實施最佳化。(雖然應用設計的時候需要考慮欄位的長度留有一定的冗餘，但是不推薦讓很多欄位都留有大量的冗餘，這樣即浪費儲存也浪費記憶體)
文法：
SELECT * FROM tbl_name PROCEDURE ANALYSE(); --輸出的對資料表中的每一列的資料類型提出最佳化建議
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);--不要為那些包含的值多於16個或者256位元組的ENUM類型提出建議。(如果沒有這樣的限制，輸出資訊可能很長；ENUM定義通常很難閱讀)
(2)拆分表提高訪問效率這裡我們所說的拆分，主要是針對Myisam類型的表，拆分的方法可以分成兩種情況：
縱向拆分：
縱向拆分是只按照應用訪問的頻度，將表中經常訪問的欄位和不經常訪問的欄位拆分成兩個表，經常訪問的欄位盡量是定長的，這樣可以有效提高表的查詢和更新的效率。
橫向拆分：
橫向拆分是指按照應用的情況，有目的的將資料橫向拆分成幾個表或者通過分區分到多個分區中，這樣可以有效避免Myisam表的讀取和更新導致的鎖問題。
(3)正常化和逆正常化根據實際情況考慮以下兩個需求：
正常化的需求：
正常化設計強調資料的獨立性，資料應該儘可能少地冗餘，因為存在過多的冗餘資料，意味著要佔用了更多的物理空間，同時也對資料的維護和一致性檢查帶來了麻煩。
逆正常化的需求：
對於查詢操作很多的應用，一次查詢可能需要訪問多表進行，如果通過冗餘相同資料紀錄在一個表中，更新的代價增加不多，但是查詢操作效率可以有明顯提高。
(4)記憶體暫存資料表使用create temporary table文法建立暫存資料表，它是基於session的表，資料儲存在記憶體裡面，當session斷掉後，表自然消除。
比如，對於統計分析的表，如果統計的資料量不大，利用insert和select將資料移到暫存資料表中比直接在表上做統計要效率更高。
(5)選擇更合適的表類型1）如果應用出現比較嚴重的鎖衝突，請考慮是否更改儲存引擎到innodb，行鎖機制可以有效減少鎖衝突的出現。
2）如果應用查詢操作很多，且對事務完整性要求不嚴格，則可以考慮使用Myisam儲存引擎。
3、最佳化用戶端應用(1)使用串連池 對於訪問資料庫來說，建立串連的代價比較昂貴，因此，我們有必要建立"串連池"以提高訪問的效能。
我們可以把串連當作對象或者裝置，池中又有許多已經建立的串連，訪問本來需要與資料庫的串連的地方，都改為和池相連，池臨時分配串連供訪問使用，結果返回後，訪問將串連交還。
(2)避免重複檢索理清訪問邏輯，需要對相同表的訪問，盡量集中在相同sql訪問，一次提取結果，減少對資料庫的重複訪問。
4、最佳化資料庫伺服器(1)使用mysql查詢快取作用：
查詢快取儲存SELECT查詢的文本以及發送給用戶端的相應結果。如果隨後收到一個相同的查詢，伺服器從查詢快取中重新得到查詢結果，而不再需要解析和執行查詢。
適用範圍：
不發生資料更新的表。當表更改（包括表結構和表資料）後，查詢快取值的相關條目會被清空。

查詢快取的主要參數：
SHOW VARIABLES LIKE '%query_cache%'; （或者 SHOW VARIABLES WHERE Variable_name LIKE '%query_cache%';) ：
have_query_cache  表示伺服器在安裝時已經配置了快取
query_cache_size  表示緩衝區大小，單位為位元組(1024位元組為1KB)
query_cache_type  值從0到2，含義分別為 
          0或者off（緩衝關閉）
                  1或者on（緩衝開啟，使用sql_no_cache的select除外）
                  2或者demand（只有帶sql_cache的select語句提供快取）

SET GLOBAL query_cache_size=1024*50;
設定查詢快取大小，單位位元組，1024位元組為 1KB，query_cache_size大小的設定必須大於40KB

SHOW STATUS命令即時監視查詢快取:
SHOW STATUS LIKE '%Qcache%';
Qcache_queries_in_cache  在緩衝中登入的查詢數目
Qcache_inserts           被加入到緩衝中的查詢數目
Qcache_hits              緩衝採樣數數目
Qcache_lowmem_prunes     因為缺少記憶體而被從緩衝中刪除的查詢數目
Qcache_not_cached        沒有被緩衝的查詢數目 (不能被緩衝的，或由於 QUERY_CACHE_TYPE)
Qcache_free_memory       查詢快取的空閑記憶體總數
Qcache_free_blocks       查詢快取中的空閑記憶體塊的數目
Qcache_total_blocks      查詢快取中的塊的總數目

(2)使用機器快取Cache（快取）、Memory（記憶體）、Hard disk（硬碟）都是資料存取單元，但存取速度卻有很大差異，呈依次遞減的順序。
對於CPU來說，它可以從距離自己最近的Cache高速地存取資料，而不是從記憶體和硬碟以低幾個數量級的速度來存取資料。
而Cache中所儲存的資料，往往是CPU要反覆存取的資料，有特定的機制（或程式）來保證Cache內資料的命中率（Hit Rate）。
因此，CPU存取資料的速度在應用快取後得到了巨大的提高。

因為將資料寫入快取的任務由Cache Manager負責，所以對使用者來說快取的內容肯定是唯讀。
需要你做的工作很少，程式中的SQL語句和直接存取DBMS時沒有分別，返回的結果也看不出有什麼差別。而資料庫廠商往往會在DB Server的設定檔中提供與Cache相關的參數，通過修改它們，可針對我們的應用最佳化Cache的管理。

(3)均衡負載1)讀寫分流（主從複製）
利用mysql的主從複製可以有效分流更新操作和查詢操作。
具體的實現是一個主伺服器，承擔更新操作（為了資料的一致性），多台從伺服器，承擔查詢操作（多台從伺服器一方面用來確保可用性，一方面可以建立不同的索引滿足不同查詢的需要），主從之間通過複製實現資料的同步。
主從複製最佳化：
對於主從之間不需要複製全部表的情況，可以通過在主的伺服器上搭建一個虛擬從伺服器，將需要複製到從伺服器的表設定成blackhole引擎，然後定義replicate-do-table參數只複製這些表，這樣就過濾出需要複製的binlog，減少了傳輸binlog的頻寬。因為搭建的虛擬從伺服器只起到過濾binlog的作用，並沒有實際紀錄任何資料，所以對主要資料庫伺服器的效能影響也非常的有限。
注意：
通過複製分流查詢的存在的問題是主要資料庫上更新頻繁或者網路出現問題的時候，主從之間的資料可能存在差異，造成查詢結果的異議，應用在設計的時候需要有所考慮。

2)分布式的資料庫
分布式的資料庫設計適合大資料量，負載高的情況，可平均多台伺服器的負載，有良好的擴充性和高效性（讀寫效率）。
分散式交易：
mysql從5.0.3開始支援分散式交易，目前分散式交易只對Innodb儲存引擎支援。
也可以使用mysql的Cluster功能（NDB引擎）或者使用自己用mysql api來實現全域事務。