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來實現全局事務。
