Mysql優化
1. 設置高速緩存
1.1. 設置高速緩存
1.1.1. 查看高速緩存是否可用
- SHOW VARIABLES LIKE ‘have_query_cache’;
1.1.2. 設置和查詢高速緩存大小
- SET GLOBAL query_cache_size = 41984;
- SHOW VARIABLES LIKE ‘query_cache_size’;
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | query_cache_size | 41984 |
- +------------------+-------+
1.1.3. 緩存開啟的方式
- mysql> SET SESSION query_cache_type =ON;
如果查詢緩存大小設置為大于0，query_cache_type變量影響其工作方式。這個變量可以設置為下面的值：
- 0或OFF：將阻止緩存或查詢緩存結果。
- 1或ON：將允許緩存，以SELECTSQL_NO_CACHE開始的查詢語句除外。
- 2或DEMAND：僅對以SELECTSQL_CACHE開始的那些查詢語句啟用緩存。
另外：
設置query_cache_type變量的GLOBAL值將決定更改后所有連接客戶端的緩存行為。具體客戶端可以通過設置query_cache_type變量的會話值控制它們本身連接的緩存行為。
例如，一個客戶可以禁用自己的查詢緩存，方法如下：
- mysql> SET SESSION query_cache_type =OFF;
- SHOW VARIABLES LIKE 'query_cache_size';#顯示緩存大小
- SET SESSION query_cache_type = OFF;#關閉緩存
1.1.4. 設置緩存結果的***值最小值
- SET GLOBAL query_cache_limit=10485760; #10M
- SET GLOBAL query_cache_min_res_unit=41984;
1.1.5. 查詢高速緩沖狀態和維護
可以使用下面的語句檢查MySQL服務器是否提供查詢緩存功能：
- mysql> SHOW VARIABLES LIKE'have_query_cache';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | have_query_cache | YES |
- +------------------+-------+
FLUSH QUERY CACHE：語句來清理查詢緩存碎片以提高內存使用性能。該語句不從緩存中移出任何查詢。
RESET QUERY CACHE：語句從查詢緩存中移出所有查詢。FLUSH TABLES語句也執行同樣的工作。
SHOW STATUS：為了監視查詢緩存性能，使用SHOWSTATUS查看緩存狀態變量，例如：
- mysql> SHOW STATUS LIKE 'Qcache%';
- +-------------------------+--------+
- | Qcache_free_blocks | 36 |
- | Qcache_free_memory | 138488 |
- | Qcache_hits | 79570 |
- | Qcache_inserts | 27087 |
- | Qcache_lowmem_prunes | 3114 |
- | Qcache_not_cached | 22989 |
- | Qcache_queries_in_cache | 415 |
- | Qcache_total_blocks | 912 |
- +-------------------------+--------+
QCACHE_free_blocks：空閑內存塊的數量。
QCACHE_free_memory：空閑內存內存的數量。
QCACHE_hits：查詢緩存被訪問的次數。
QCACHE_inserts：加入到緩存的查詢數量。
QCACHE_lowmem_prunes：由于內存較少從緩存刪除的查詢數量。
QCACHE_not_cached：非緩存查詢數(不可緩存，或由于query_cache_type設定值未緩存)。
Qcache_queries_in_cache：登記到緩存內的查詢的數量。
Qcache_total_blocks：查詢緩存內的總塊數。
1.2. 高速緩存語句要求
下面的兩個查詢被查詢緩存認為是不相同的：
- SELECT * FROM tbl_name
- Select * from tbl_name
查詢必須是完全相同的(逐字節相同)才能夠被認為是相同的。
1.3. 不緩存的語句
如果一個查詢包含下面函數中的任何一個，它不會被緩存
- BENCHMARK()
- CONNECTION_ID()
- CURDATE()
- CURRENT_DATE()
- CURRENT_TIME()
- CURRENT_TIMESTAMP()
- CURTIME()
- DATABASE()
- 帶一個參數的ENCRYPT()
- FOUND_ROWS()
- GET_LOCK()
- LAST_INSERT_ID()
- LOAD_FILE()
- MASTER_POS_WAIT()
- NOW()
- RAND()
- RELEASE_LOCK()
- SYSDATE()
- 不帶參數的UNIX_TIMESTAMP()
- USER()
2. EXPLAIN
2.1. 查看表的索引
- SHOW INDEX FROM tbl_name;
2.2. 創建索引
- ALTER TABLE 表名 ADD INDEX 索引名 (索引列) ;
2.3. 說明
使用 EXPLAIN 關鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。
EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的數據表是如何被搜索和排序的……等等，等等。
挑一個你的SELECT語句(推薦挑選那個最復雜的，有多表聯接的)，把關鍵字EXPLAIN加到前面。然后，你會看到一張表格。下面的這個示例中，我們忘記加上了group_id索引，并且有表聯接：
當我們為 group_id 字段加上索引后：
我們可以看到，前一個結果顯示搜索了 7883 行，而后一個只是搜索了兩個表的 9 和 16 行。查看rows列可以讓我們找到潛在的性能問題。
2.4. 參數
- id：這是SELECT的查詢序列號。
- select_type：SELECT類型，可以為以下任何一種：
- SIMPLE：簡單SELECT(不使用UNION或子查詢)
- PRIMARY：最外面的SELECT
- UNION：UNION中的第二個或后面的SELECT語句
- DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
- UNION RESULT：UNION的結果。
- SUBQUERY:子查詢中的***個SELECT
- DEPENDENT SUBQUERY:子查詢中的***個SELECT，取決于外面的查詢
- DERIVED:導出表的SELECT(FROM子句的子查詢)
- table:輸出的行所引用的表。
- type:聯接類型。下面給出各種聯接類型，按照從***類型到最壞類型進行排序：
- system表僅有一行(=系統表)。
- const表最多有一個匹配行，它將在查詢開始時被讀取。
- eq_ref比較的時候，“=”前后的變量都加了索引。
- ref:前面的表加了索引。
- index：該聯接類型與ALL相同，只是索引樹被掃描。
- ALL：對于每個來自于先前的表的行組合，進行完整的表掃描。
- possible_keys：possible_keys列指出MySQL能使用哪個索引在該表中找到行。
- 如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。
- key:顯示MySQL實際決定使用的索引。如果沒有選擇索引，鍵是NULL。
- key_len:顯示MySQL決定使用的索引長度。如果索引是NULL，則長度為NULL。
- ref：顯示使用哪個列或常數與key一起從表中選擇行。
- rows：顯示MySQL認為它執行查詢時必須檢查的行數。
- Extra：該列包含MySQL解決查詢的詳細信息。下面解釋了該列可以顯示的不同的文本字符串：
- Distinct：MySQL發現第1個匹配行后，停止為當前的行組合搜索更多的行。
- Not exists：MySQL能夠對查詢進行LEFTJOIN優化，發現1個匹配LEFT JOIN標準的行后，不再為前面的的行組合在該表內檢查更多的行。
- range checkedfor each record (index map: #)：MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。
- Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然后關鍵字被排序，并按排序順序檢索行
- Using index：從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。當查詢只使用作為單一索引一部分的列時，可以使用該策略。
- Using temporary：為了解決查詢，MySQL需要創建一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。
- Using where：WHERE子句用于限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯接類型為ALL或index，查詢可能會有一些錯誤。
- Using sort_union(...), Using union(...), Using intersect(...)：這些函數說明如何為index_merge聯接類型合并索引掃描。詳細信息參見7.2.6節，“索引合并優化”。
- Using index forgroup-by：類似于訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。并且，按最有效的方式使用索引，以便對于每個組，只讀取少量索引條目。詳情參見7.2.13節，“MySQL如何優化GROUP BY”。
3. 其他優化
3.1. 使用 ENUM 而不是 VARCHAR
ENUM 類型是非常快和緊湊的。在實際上，其保存的是TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的***。
如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應該使用 ENUM 而不是 VARCHAR。
MySQL也有一個“建議”(見第十條)告訴你怎么去重新組織你的表結構。當你有一個 VARCHAR 字段時，這個建議會告訴你把其改成 ENUM 類型。使用 PROCEDURE ANALYSE() 你可以得到相關的建議。
3.2. 從 PROCEDURE ANALYSE() 取得建議
語法：SELECT * FROM student LIMIT 1,1 PROCEDURE ANALYSE(1);
PROCEDURE ANALYSE() 會讓 MySQL 幫你去分析你的字段和其實際的數據，并會給你一些有用的建議。只有表中有實際的數據，這些建議才會變得有用，因為要做一些大的決定是需要有數據作為基礎的。
例如，如果你創建了一個 INT 字段作為你的主鍵，然而并沒有太多的數據，那么，PROCEDURE ANALYSE()會建議你把這個字段的類型改成 MEDIUMINT ?；蚴悄闶褂昧艘粋€ VARCHAR 字段，因為數據不多，你可能會得到一個讓你把它改成 ENUM 的建議。這些建議，都是可能因為數據不夠多，所以決策做得就不夠準。
一定要注意，這些只是建議，只有當你的表里的數據越來越多時，這些建議才會變得準確。
4. mysql引擎
MySQL常用的存儲引擎為MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事務安全表，其他存儲引擎都是非事務安全表。
- MyISAM是MySQL的默認存儲引擎。MyISAM不支持事務、也不支持外鍵，但其訪問速度快，對事務完整性沒有要求。
- innoDB存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是比起MyISAM存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數據和索引
- MEMORY存儲引擎使用存在內存中的內容來創建表。每個MEMORY表只實際對應一個磁盤文件。
- MEMORY類型的表訪問非常得快，因為它的數據是放在內存中的，并且默認使用HASH索引。但是一旦服務關閉，表中的數據就會丟失掉。
- MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同。MERGE表本身沒有數據，對MERGE類型的表進行查詢、更新、刪除的操作，就是對內部的MyISAM表進行的。
5. mysql集群搭建
待補充
6. mysql主從搭建
待補充
【本文為51CTO專欄作者“王森豐”的原創稿件，轉載請注明出處】
