萬盛學電腦網 >> 數據庫 >> mysql教程 >> MYSQL性能優化
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%’ 時索引無效
