MYSQL效能最佳化-安裝時最佳化參數配置提高服務效能

安裝時最佳化參數配置提高服務效能

在Linux下安裝Mysql採用預設配置安裝的Mysql卻未必是工作在最佳效能狀態的，需要對其進行最佳化。一般認為在

Mysql的設定檔中，下列系統參數是比較關鍵的：

　　(1)　interactive_timeout ：
　　伺服器在關閉它前在一個互動串連上等待行動的秒數。一個互動的客戶被定義為對 mysql_real_connect()使用

CLIENT_INTERACTIVE 選項的客戶。 預設數值是28800，我把它改為7200。

　　(2)　back_log ：
　　要求 MySQL 能有的串連數量。當主要MySQL線程在一個很短時間內得到非常多的串連請求，這個參數就會起作用

，然後主線程花些時間(儘管很短)檢查串連並且啟動一個新線程。

　　back_log 值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。只有如果期望在一

個短時間內有很多串連，你需要增加它，換句話說，這值對到來的TCP/IP串連的偵聽隊列的大小。每個作業系統在這

個隊列大小上都有它自己的限制。 試圖設定back_log高於作業系統的限制將是無效的。

      在mysql中back_log的設定取決於作業系統
      在linux下這個參數的值不能大於系統參數tcp_max_syn_backlog的值
      通過以下命令可以查看tcp_max_syn_backlog的當前值
      cat  /proc/sys/net/ipv4/tcp_max_syn_backlog
      通過以下命令進行修改sysctl -w net.ipv4.tcp_max_syn_backlog=n
      深入探討一點
      tcp/ip網路一般會有如下過程
      從產生socket到bind連接埠在listen進而建立串連
      具體到listen，就是listen(int fd, int backlog)的調用，這裡backlog和mysql中back_log具有一定的關係，

即作業系統backlog的要不小於mysql中back_log的值，在linux核心2.6.6中backlog在/include/net/tcp.h中由

TCP_SYNQ_HSIZE變數定義

　　觀察一下主機處理序列表，如果發現大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect

| NULL | login | NULL 的待串連進程時，就有必要加大 back_log 的值了。預設數值是50，我把它改為500。

　　(3)　max_connections ：
　　允許的同時客戶的數量。增加該值增加 mysqld 要求的檔案描述符的數量。這個數字應該增加，否則，你將經常

看到 Too many connections 錯誤。 預設數值是100，我把它改為1024 。

　　(4)　key_buffer_size ：
　　索引塊是緩衝的並且被所有的線程共用。key_buffer_size是用於索引塊的緩衝區大小，增加它可得到更好處理的

索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，系統將開始換頁並且真的變慢了。預設數值是

8388600(8M)，我的MySQL主機有2GB記憶體，所以我把它改為 402649088(400MB)。

是否要增加這個參數的值主要看以下兩點：
1、Key_reads/Key_read_requests：比例應該接近於0.01甚至越小越好
2、Key_writes/Key_write_requests：比例接近1較好
解決的辦法當然是增加key_buffer_size的值啦，來實在的到控制台下面運行：

 程式碼
SET GLOBAL key_buffer_size=16777216;
這是設定全域的，如果只是當前會話的話，將GLOBAL換成SESSION即可。

　　(5)　record_buffer ：
　　每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想

要增加該值。預設數值是131072(128K)，我把它改為16773120 (16M)

　　(6)　sort_buffer ：
　　每個需要進行排序的線程分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。預設數值是

2097144(2M)，我把它改為 16777208 (16M)。

　　(7)　table_cache ：
　　為所有線程開啟表的數量。增加該值能增加mysqld要求的檔案描述符的數量。MySQL對每個唯一開啟的表需要2個

檔案描述符。預設數值是64，我把它改為512。

　　(8)　thread_cache_size ：
　　可以複用的儲存在中的線程的數量。如果有，新的線程從緩衝中取得，當中斷連線的時候如果有空間，客戶的線

置在緩衝中。如果有很多新的線程，為了提高效能可 以這個變數值。通過比較 Connections 和 Threads_created 狀

態的變數，可以看到這個變數的作用。我把它設定為 80。

　　(9)　wait_timeout ：
　　伺服器在關閉它之前在一個串連上等待行動的秒數。 預設數值是28800，我把它改為7200。

通過設定tmp_table_size選項來增加一張暫存資料表的大小，例如做進階GROUP BY操作產生的暫存資料表。如果調高該值，

MySQL同時將增加heap表的大小，可達到提高聯結查詢速度的效果，建議盡量最佳化查詢，要確保查詢過程中產生的臨時

表在記憶體中，避免暫存資料表過大導致產生基於硬碟的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name　　           | Value　|

+———————————-+———+

| Created_tmp_disk_tables | 21197  |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+——————————–+———–+

每次建立暫存資料表，Created_tmp_tables增加，如果暫存資料表大小超過tmp_table_size，則是在磁碟上建立暫存資料表，

Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的伺服器Created_tmp_disk_tables /

Created_tmp_tables * 100% ＝1.20%，應該相當好了

預設為16M，可調到64-256最佳，線程獨佔，太大可能記憶體不夠I/O堵塞

 

註：參數的調整可以通過修改 /etc/my.cnf 檔案並重啟 MySQL 實現。很明顯的，根據伺服器的硬體設定的不同，和

mysql資料庫負載的不同，參數的設定也是不同的。所以大家不要照搬上面的參數，而是要根據不同的硬體和負載修改

為最適合自己的參數。

慢查詢分析、最佳化索引和配置

索引及查詢最佳化

 索引的類型

Ø 普通索引：這是最基本的索引類型，沒唯一性之類的限制。

Ø 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。

Ø 主鍵：主鍵是一種唯一索引，但必須指定為”PRIMARY KEY”。

Ø 全文索引：MYSQL從3.23.23開始支援全文索引和全文檢索索引。在MYSQL中，全文索引的索引類型為FULLTEXT。全文索引

可以在VARCHAR或者TEXT類型的列上建立。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹中儲存。空間列類型的索引使用R-樹，MEMORY表

支援hash索引。

單列索引和多列索引（複合索引）

索引可以是單列索引，也可以是多列索引。對相關的列使用索引是提高SELECT操作效能的最佳途徑之一。

多列索引：

MySQL可以為多個列建立索引。一個索引可以包括15個列。對於某些列類型，可以索引列的左首碼，列的順序非常重要

。

多列索引可以視為包含通過串連索引列的值而建立的值的排序的數組。一般來說，即使是限制最嚴格的單列索引，它

的限制能力也遠遠低於多列索引。

最左首碼

多列索引有一個特點，即最左首碼（Leftmost Prefixing）。假如有一個多列索引為key(firstname lastname age)，

當搜尋條件是以下各種列的組合和順序時，MySQL將使用該多列索引：

firstname，lastname，age

firstname，lastname

firstname

也就是說，相當於還建立了key(firstname lastname)和key(firstname)。

索引主要用於下面的操作：

Ø 快速找出匹配一個WHERE子句的行。

Ø 刪除行。當執行聯結時，從其它表檢索行。

Ø 對具體有索引的列key_col找出MAX()或MIN()值。由前置處理器進行最佳化，檢查是否對索引中在key_col之前發生所有

關鍵字元素使用了WHERE key_part_# = constant。在這種情況下，MySQL為每個MIN()或MAX()運算式執行一次關鍵字

尋找，並用常數替換它。如果所有運算式替換為常量，查詢立即返回。例如：

SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;

Ø 如果對一個可用關鍵字的最左面的首碼進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組

一個表。如果所有關鍵字元素後面有DESC，關鍵字以倒序被讀取。

Ø 在一些情況中，可以對一個查詢進行最佳化以便不用查詢資料行即可以檢索值。如果查詢只使用來自某個表的數字型

並且構成某些關鍵字的最左面首碼的列，為了更快，可以從索引樹檢索出值。

SELECT key_part3 FROM tb WHERE key_part1=1

有時MySQL不使用索引，即使有可用的索引。一種情形是當最佳化器估計到使用索引將需要MySQL訪問表中的大部分行時

。(在這種情況下，表掃描可能會更快些）。然而，如果此類查詢使用LIMIT只搜尋部分行，MySQL則使用索引，因為它

可以更快地找到幾行並在結果中返回。例如：

 

 

合理的建立索引的建議：

(1)  越小的資料類型通常更好：越小的資料類型通常在磁碟、記憶體和CPU緩衝中都需要更少的空間，處理起來更快。

(2)  簡單的資料類型更好：整型資料比起字元，處理開銷更小，因為字串的比較更複雜。在MySQL中，應該用內建

的日期和時間資料類型，而不是用字串來儲存時間；以及用整數資料型別儲存IP地址。

(3)  盡量避免NULL：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的列很難進行查詢最佳化，因

為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值

 

這部分是關於索引和寫SQL語句時應當注意的一些瑣碎建議和注意點。

1. 當結果集只有一行資料時使用LIMIT 1

2. 避免SELECT *，始終指定你需要的列

從表中讀取越多的資料，查詢會變得更慢。他增加了磁碟需要操作的時間，還是在資料庫伺服器與WEB伺服器是獨立分

開的情況下。你將會經曆非常漫長的網路延遲，僅僅是因為資料不必要的在伺服器之間傳輸。

3. 使用串連（JOIN）來代替子查詢(Sub-Queries)

       串連（JOIN）.. 之所以更有效率一些，是因為MySQL不需要在記憶體中建立暫存資料表來完成這個邏輯上的需要兩個

步驟的查詢工作。

4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的欄位屬性長度

5. 儘可能的使用NOT NULL

6. 固定長度的表會更快

7. 拆分大的DELETE 或INSERT 語句

8. 查詢的列越小越快

Where條件

在查詢中，WHERE條件也是一個比較重要的因素，盡量少並且是合理的where條件是很重要的，盡量在多個條件的時候

，把會提取盡量少資料量的條件放在前面，減少後一個where條件的查詢時間。

有些where條件會導致索引無效：

Ø where子句的查詢條件裡有！=，MySQL將無法使用索引。

Ø where子句使用了Mysql函數的時候，索引將無效，比如：select * from tb where left(name, 4) = ‘xxx’

Ø 使用LIKE進行搜尋匹配的時候，這樣索引是有效：select * from tbl1 where name like ‘xxx%’，而like

‘%xxx%’ 時索引無效