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

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

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

SHOW VARIABLES LIKE ‘have_query_cache’; 

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

SET GLOBAL query_cache_size = 41984; 
SHOW VARIABLES LIKE ‘query_cache_size’; 
+------------------+-------+ 
| Variable_name    | Value | 
+------------------+-------+ 
| query_cache_size | 41984 | 
+------------------+-------+ 

1.1.3. 緩存開(kāi)啟的方式

mysql> SET SESSION query_cache_type =ON; 

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

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

另外：

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

例如，一個(gè)客戶(hù)可以禁用自己的查詢(xún)緩存，方法如下：

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. 查詢(xún)高速緩沖狀態(tài)和維護(hù)

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

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

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

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

SHOW STATUS：為了監(jiān)視查詢(xú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：查詢(xún)緩存被訪(fǎng)問(wèn)的次數(shù)。

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

QCACHE_lowmem_prunes：由于內(nèi)存較少?gòu)木彺鎰h除的查詢(xún)數(shù)量。

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

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

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

1.2. 高速緩存語(yǔ)句要求

下面的兩個(gè)查詢(xún)被查詢(xún)緩存認(rèn)為是不相同的：

SELECT * FROM tbl_name 
Select * from tbl_name 

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

1.3. 不緩存的語(yǔ)句

如果一個(gè)查詢(xún)包含下面函數(shù)中的任何一個(gè)，它不會(huì)被緩存

BENCHMARK() 
CONNECTION_ID() 
CURDATE() 
CURRENT_DATE() 
CURRENT_TIME() 
CURRENT_TIMESTAMP() 
CURTIME() 
DATABASE() 
帶一個(gè)參數(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. 說(shuō)明

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

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

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

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

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

2.4. 參數(shù)

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

3. 其他優(yōu)化

3.1. 使用 ENUM 而不是 VARCHAR

ENUM 類(lèi)型是非常快和緊湊的。在實(shí)際上，其保存的是TINYINT，但其外表上顯示為字符串。這樣一來(lái)，用這個(gè)字段來(lái)做一些選項(xiàng)列表變得相當(dāng)?shù)?**。

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

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

3.2. 從 PROCEDURE ANALYSE() 取得建議

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

PROCEDURE ANALYSE() 會(huì)讓 MySQL 幫你去分析你的字段和其實(shí)際的數(shù)據(jù)，并會(huì)給你一些有用的建議。只有表中有實(shí)際的數(shù)據(jù)，這些建議才會(huì)變得有用，因?yàn)橐鲆恍┐蟮臎Q定是需要有數(shù)據(jù)作為基礎(chǔ)的。

例如，如果你創(chuàng)建了一個(gè) INT 字段作為你的主鍵，然而并沒(méi)有太多的數(shù)據(jù)，那么，PROCEDURE ANALYSE()會(huì)建議你把這個(gè)字段的類(lèi)型改成 MEDIUMINT 。或是你使用了一個(gè) VARCHAR 字段，因?yàn)閿?shù)據(jù)不多，你可能會(huì)得到一個(gè)讓你把它改成 ENUM 的建議。這些建議，都是可能因?yàn)閿?shù)據(jù)不夠多，所以決策做得就不夠準(zhǔn)。

一定要注意，這些只是建議，只有當(dāng)你的表里的數(shù)據(jù)越來(lái)越多時(shí)，這些建議才會(huì)變得準(zhǔn)確。

4. mysql引擎

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

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

5. mysql集群搭建

待補(bǔ)充

6. mysql主從搭建

待補(bǔ)充

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