MySQL性能優化--優化資料庫結構之優化資料類型
By：授客 QQ：1033553122
優化數字資料(Numeric Data)
l 對于唯一ID或其它可用字元串或數字表示的值，選擇用數字列好過用字元串列。因為相比對應的字元串，可使用更少的位元組存儲大數字，同時，轉換并比較數字速度更快且消耗更少的記憶體。
參考連接配接：http://dev.mysql.com/doc/refman/5.7/en/optimize-numeric.html
優化字元和字元串類型(Character and String Type)
l 當不需要語言特定的比對功能時，使用二進制比對(binary collation)指令對來擷取更快的比較和排序操作。在特定查詢中使用BINARY 操作符以使用二進制比對。
eg:
mysql> SELECT 'a' = 'A';
-> 1
mysql> SELECT BINARY 'a' = 'A';
-> 0
mysql> SELECT 'a' = 'a ';
mysql> SELECT BINARY 'a' = 'a ';
l 當需要比較不同列的值時，盡可能為那些列定義相同的字元集和比對方法，避免執行查詢時進行字元串轉換。
l 對于小于8KB的列值，使用二進制VARCHAR,而非BLOB，GROPU BY和ORDER BY語句會生成臨時表，如果原始表沒包含任何BLOB列，那麼這些臨時表可使用MEMORY 存儲引擎。
l 如果表包含字元串列，如名字和位址，但是許多查詢不檢索那些列，可考慮把這些字元串列拆分到一個單獨的表，必要時使用攜帶外鍵的join查詢。當MySQL檢索來自某記錄行的任意值時，它會讀取包含該記錄行(也可能還有其它相鄰行)所有列的資料塊。保持每個記錄行盡可能的小，僅含最頻繁使用的列，這樣允許在每個資料塊中放入更多的記錄行。這樣緊湊的表，可減少大部分查詢帶來的磁盤I/O和記憶體使用。
l 當在InnoDB表中使用随機生成的值作為主鍵時，如果可能的話，使用一個“升序值”(ascending value)如目前日期和時間作為其字首。當連續的主鍵值在實體上連續存儲時，可以加快InnoDB的插入和檢索速度。
參考連接配接：
http://dev.mysql.com/doc/refman/5.7/en/optimize-character.html
優化BLOB
l 當存儲一個包含文本資料的大二進制對象，考慮先壓縮。當整個表都被InnoDB或MyISAM壓縮過時不能使用該技術。
l 對于包含多列的表，為減少查詢記憶體占用，不使用BLOB列的話可考慮把BLOB列拆分到單獨的表，并在需要時使用join方式引用。
l 由于檢索和展示BLOB值的性能要求和其它資料類型不一樣，可以考慮把特定于BLOB的表放在不同的儲存設備，甚至是一個單獨的資料庫執行個體。例如，檢索一個BLOB可能需要一個大的順序讀磁盤(sequential disk read),相比SSD 裝置，更适合傳統的硬碟驅動。
l 與其直接比較長文本字元串的相等性，可在某個單獨的列中存儲長文本所在列列值的哈希，并為存儲哈希值的列建立索引，查詢的時候測試哈希是否相等(使用MD5()、CRC32()函數生成哈希值)。由于哈希函數會為不同輸入生成重複結果，是以查詢中還需要增加語句blob_column=long_string_value以防止錯誤的比對。針對哈希值的更小，更容易的索引掃描有助于提高性能。
http://dev.mysql.com/doc/refman/5.7/en/optimize-blob.html
使用PROCEDURE ANALYSE
ANALYSE([max_elements[,max_memory]])
ANALYSE（）檢測來自查詢的結果并傳回分析結果，為每列的資料類型給出可能幫助減小表的優化建議。使用方法如下，直接把語句拼接到查詢末尾：
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
eg:
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
可選參數：
l max_elements：預設256，指定每列中，ANALYSE()關注的不重複值的最大數量。ANALYSE()用于檢測優化資料類型是否應該為ENUM,如果有多餘max_elements個補重複值，則ENUM不為建議類型。
l max_memory：預設為8192，當ANALYSE()試圖查找所有不重複值時，為每列配置設定的最大記憶體量。
注意：PROCEDURE ANALYSE()不能在UNION語句中使用。
http://dev.mysql.com/doc/refman/5.7/en/procedure-analyse.html
作者：授客
QQ：1033553122
全國軟體測試QQ交流群：7156436
Git位址：https://gitee.com/ishouke
友情提示：限于時間倉促，文中可能存在錯誤，歡迎指正、評論！
作者五行缺錢，如果覺得文章對您有幫助，請掃描下邊的二維碼打賞作者，金額随意，您的支援将是我繼續創作的源動力，打賞後如有任何疑問，請聯系我!!!
微信打賞
支付寶打賞 全國軟體測試交流QQ群
