1. 設(shè)置高速緩存

1.1. 設(shè)置高速緩存

1.1.1. 查看高速緩存是否可用

SHOW VARIABLES LIKE ‘have_query_cache’; 

1.1.2. 設(shè)置和查詢高速緩存大小

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; 

如果查詢緩存大小設(shè)置為大于0，query_cache_type變量影響其工作方式。這個變量可以設(shè)置為下面的值：

• 0或OFF：將阻止緩存或查詢緩存結(jié)果。
• 1或ON：將允許緩存，以SELECTSQL_NO_CACHE開始的查詢語句除外。
• 2或DEMAND：僅對以SELECTSQL_CACHE開始的那些查詢語句啟用緩存。

另外：

設(shè)置query_cache_type變量的GLOBAL值將決定更改后所有連接客戶端的緩存行為。具體客戶端可以通過設(shè)置query_cache_type變量的會話值控制它們本身連接的緩存行為。

例如，一個客戶可以禁用自己的查詢緩存，方法如下：

mysql> SET SESSION query_cache_type =OFF; 
SHOW VARIABLES LIKE 'query_cache_size';#顯示緩存大小 
SET SESSION query_cache_type = OFF;#關(guān)閉緩存 

1.1.4. 設(shè)置緩存結(jié)果的***值最小值

SET GLOBAL query_cache_limit=10485760;         #10M 
SET GLOBAL query_cache_min_res_unit=41984; 

1.1.5. 查詢高速緩沖狀態(tài)和維護

可以使用下面的語句檢查MySQL服務(wù)器是否提供查詢緩存功能：

mysql> SHOW VARIABLES LIKE'have_query_cache'; 
+------------------+-------+ 
| Variable_name    | Value | 
+------------------+-------+ 
| have_query_cache | YES   | 
+------------------+-------+ 

FLUSH QUERY CACHE：語句來清理查詢緩存碎片以提高內(nèi)存使用性能。該語句不從緩存中移出任何查詢。

RESET QUERY CACHE：語句從查詢緩存中移出所有查詢。FLUSH TABLES語句也執(zhí)行同樣的工作。

SHOW STATUS：為了監(jiān)視查詢緩存性能，使用SHOWSTATUS查看緩存狀態(tài)變量，例如：

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：空閑內(nèi)存塊的數(shù)量。

QCACHE_free_memory：空閑內(nèi)存內(nèi)存的數(shù)量。

QCACHE_hits：查詢緩存被訪問的次數(shù)。

QCACHE_inserts：加入到緩存的查詢數(shù)量。

QCACHE_lowmem_prunes：由于內(nèi)存較少從緩存刪除的查詢數(shù)量。

QCACHE_not_cached：非緩存查詢數(shù)(不可緩存，或由于query_cache_type設(shè)定值未緩存)。

Qcache_queries_in_cache：登記到緩存內(nèi)的查詢的數(shù)量。

Qcache_total_blocks：查詢緩存內(nèi)的總塊數(shù)。

1.2. 高速緩存語句要求

下面的兩個查詢被查詢緩存認為是不相同的：

SELECT * FROM tbl_name 
Select * from tbl_name 

查詢必須是完全相同的(逐字節(jié)相同)才能夠被認為是相同的。

1.3. 不緩存的語句

如果一個查詢包含下面函數(shù)中的任何一個，它不會被緩存

BENCHMARK() 
CONNECTION_ID() 
CURDATE() 
CURRENT_DATE() 
CURRENT_TIME() 
CURRENT_TIMESTAMP() 
CURTIME() 
DATABASE() 
帶一個參數(shù)的ENCRYPT() 
FOUND_ROWS() 
GET_LOCK() 
LAST_INSERT_ID() 
LOAD_FILE() 
MASTER_POS_WAIT() 
NOW() 
RAND() 
RELEASE_LOCK() 
SYSDATE() 
不帶參數(shù)的UNIX_TIMESTAMP() 
USER() 

2. EXPLAIN

2.1. 查看表的索引

SHOW INDEX FROM tbl_name; 

2.2. 創(chuàng)建索引

ALTER TABLE 表名 ADD INDEX 索引名 (索引列) ; 

2.3. 說明

使用 EXPLAIN 關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。

EXPLAIN 的查詢結(jié)果還會告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的……等等，等等。

挑一個你的SELECT語句(推薦挑選那個最復(fù)雜的，有多表聯(lián)接的)，把關(guān)鍵字EXPLAIN加到前面。然后，你會看到一張表格。下面的這個示例中，我們忘記加上了group_id索引，并且有表聯(lián)接：

當我們?yōu)?group_id 字段加上索引后：

我們可以看到，前一個結(jié)果顯示搜索了 7883 行，而后一個只是搜索了兩個表的 9 和 16 行。查看rows列可以讓我們找到潛在的性能問題。

2.4. 參數(shù)

• id：這是SELECT的查詢序列號。
• select_type：SELECT類型，可以為以下任何一種：
• SIMPLE：簡單SELECT(不使用UNION或子查詢)
• PRIMARY：最外面的SELECT
• UNION：UNION中的第二個或后面的SELECT語句
• DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
• UNION RESULT：UNION的結(jié)果。
• SUBQUERY:子查詢中的***個SELECT
• DEPENDENT SUBQUERY:子查詢中的***個SELECT，取決于外面的查詢
• DERIVED:導(dǎo)出表的SELECT(FROM子句的子查詢)
• table:輸出的行所引用的表。
• type:聯(lián)接類型。下面給出各種聯(lián)接類型，按照從***類型到最壞類型進行排序：
  • system表僅有一行(=系統(tǒng)表)。
  • const表最多有一個匹配行，它將在查詢開始時被讀取。
  • eq_ref比較的時候，“=”前后的變量都加了索引。
• ref:前面的表加了索引。
• index：該聯(lián)接類型與ALL相同，只是索引樹被掃描。
• ALL：對于每個來自于先前的表的行組合，進行完整的表掃描。
• possible_keys：possible_keys列指出MySQL能使用哪個索引在該表中找到行。
• 如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。
• key:顯示MySQL實際決定使用的索引。如果沒有選擇索引，鍵是NULL。
• key_len:顯示MySQL決定使用的索引長度。如果索引是NULL，則長度為NULL。
• ref：顯示使用哪個列或常數(shù)與key一起從表中選擇行。
• rows：顯示MySQL認為它執(zhí)行查詢時必須檢查的行數(shù)。
• Extra：該列包含MySQL解決查詢的詳細信息。下面解釋了該列可以顯示的不同的文本字符串：
• Distinct：MySQL發(fā)現(xiàn)第1個匹配行后，停止為當前的行組合搜索更多的行。
• Not exists：MySQL能夠?qū)Σ樵冞M行LEFTJOIN優(yōu)化，發(fā)現(xiàn)1個匹配LEFT JOIN標準的行后，不再為前面的的行組合在該表內(nèi)檢查更多的行。
• range checkedfor each record (index map: #)：MySQL沒有發(fā)現(xiàn)好的可以使用的索引，但發(fā)現(xiàn)如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。
• Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關(guān)鍵字和行的指針來完成排序。然后關(guān)鍵字被排序，并按排序順序檢索行
• Using index：從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。當查詢只使用作為單一索引一部分的列時，可以使用該策略。
• Using temporary：為了解決查詢，MySQL需要創(chuàng)建一個臨時表來容納結(jié)果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。
• Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會有一些錯誤。
• Using sort_union(...), Using union(...), Using intersect(...)：這些函數(shù)說明如何為index_merge聯(lián)接類型合并索引掃描。詳細信息參見7.2.6節(jié)，“索引合并優(yōu)化”。
• Using index forgroup-by：類似于訪問表的Using index方式，Using index for group-by表示MySQL發(fā)現(xiàn)了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。并且，按最有效的方式使用索引，以便對于每個組，只讀取少量索引條目。詳情參見7.2.13節(jié)，“MySQL如何優(yōu)化GROUP BY”。

3. 其他優(yōu)化

3.1. 使用 ENUM 而不是 VARCHAR

ENUM 類型是非?？旌途o湊的。在實際上，其保存的是TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當?shù)?**。

如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態(tài)”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應(yīng)該使用 ENUM 而不是 VARCHAR。

MySQL也有一個“建議”(見第十條)告訴你怎么去重新組織你的表結(jié)構(gòu)。當你有一個 VARCHAR 字段時，這個建議會告訴你把其改成 ENUM 類型。使用 PROCEDURE ANALYSE() 你可以得到相關(guān)的建議。

3.2. 從 PROCEDURE ANALYSE() 取得建議

語法：SELECT * FROM student LIMIT 1,1 PROCEDURE ANALYSE(1);

PROCEDURE ANALYSE() 會讓 MySQL 幫你去分析你的字段和其實際的數(shù)據(jù)，并會給你一些有用的建議。只有表中有實際的數(shù)據(jù)，這些建議才會變得有用，因為要做一些大的決定是需要有數(shù)據(jù)作為基礎(chǔ)的。

例如，如果你創(chuàng)建了一個 INT 字段作為你的主鍵，然而并沒有太多的數(shù)據(jù)，那么，PROCEDURE ANALYSE()會建議你把這個字段的類型改成 MEDIUMINT ?；蚴悄闶褂昧艘粋€ VARCHAR 字段，因為數(shù)據(jù)不多，你可能會得到一個讓你把它改成 ENUM 的建議。這些建議，都是可能因為數(shù)據(jù)不夠多，所以決策做得就不夠準。

一定要注意，這些只是建議，只有當你的表里的數(shù)據(jù)越來越多時，這些建議才會變得準確。

4. mysql引擎

MySQL常用的存儲引擎為MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事務(wù)安全表，其他存儲引擎都是非事務(wù)安全表。

• MyISAM是MySQL的默認存儲引擎。MyISAM不支持事務(wù)、也不支持外鍵，但其訪問速度快，對事務(wù)完整性沒有要求。
• innoDB存儲引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是比起MyISAM存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引
• MEMORY存儲引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。每個MEMORY表只實際對應(yīng)一個磁盤文件。
• MEMORY類型的表訪問非常得快，因為它的數(shù)據(jù)是放在內(nèi)存中的，并且默認使用HASH索引。但是一旦服務(wù)關(guān)閉，表中的數(shù)據(jù)就會丟失掉。
• MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)完全相同。MERGE表本身沒有數(shù)據(jù)，對MERGE類型的表進行查詢、更新、刪除的操作，就是對內(nèi)部的MyISAM表進行的。

5. mysql集群搭建

待補充

6. mysql主從搭建

待補充

【本文為51CTO專欄作者“王森豐”的原創(chuàng)稿件，轉(zhuǎn)載請注明出處】