如何進行MySQL管理基礎中的性能優化

136次閱讀

共計 4153 個字符，預計需要花費 11 分鐘才能閱讀完成。

如何進行 MySQL 管理基礎中的性能優化，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

1. 索引

1 應該添加索引的列包括：用于連接的列，WHERE、GROUP BY 或 ORDER BY 子句涉及的列。
2 有很多重復值的列不能通過建立索引來改善性能，取值唯一的列則能充分通過索引獲益。
3 索引并非越多越方便，它可能在增加搜索速度的同時，減慢 INSERT、DELETE 和 UPDATE 等寫操作。很小的表不需要創建索引。

一些服務器狀態變量的值可以反映索引的情況或為添加索引提供參考，例如：

Handler_read_key：通過索引讀取行的請求數。該值較低，表示索引不經常使用。
Handler_read_rnd_next：在數據文件中讀下一行的請求數。該值較高，表示存在大量的全表掃描，說明表上沒有適當地建立索引或查詢沒有利用到索引。

可以 mysqladmin extended-status 或 SHOW STATUS 命令查看狀態變量，例如：

mysql show status like Handler_read_%
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+———————–+——-+
6 rows in set (0.00 sec)

[@more@] 對于建立了索引的表，應該定期執行 ANALYZE TABLE 命令。該命令分析并存儲一個表的鍵分布，MySQL 使用存儲的鍵分布決定連接時表的連接順序，以及查詢時使用哪些索引。

2.query cache

查看相關的服務器系統變量，確認 query cache 已經打開：

mysql show variables like %query_cache%
+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 15728640 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———-+
6 rows in set (0.00 sec)

have_query_cache：MySQL 服務器是否支持 query cache。
query_cache_size：分配給 query cache 的內存總量。
query_cache_type：query cache 的類型。0 或 OFF 表示不使用 query cache，但系統還是會為 query cache 分配內存空間；1 或 ON 表示緩存所有查詢結果，但以 SELECT SQL_NO_CACHE 開頭的除外；2 或 DEMAND 表示只緩存以 SQL_CACHE 開頭的語句。

一旦表發生變化，query cache 中涉及該表的緩存內容就會失效，所以經常變化的表并不能從 query cache 的使用中受益，可以考慮為這些查詢添加 SQL_NO_CACHE 選項。

3. 查詢分析

在查詢語句最前面添加 EXPLAIN 關鍵字，返回的結果將列出該語句中所有的 SELECT。例如：

id 列為 SELECT 在整個查詢中的位置；select_type 列為查詢的類型，如簡單查詢、UNION 中最后面的查詢、子查詢等等；type 列為執行連接的情況，const 最佳，ALL 最差；possible_keys 列為可能使用的索引；key 列為實際使用的索引；rows 列為執行該查詢需要在相應表中檢索的行的數量，將每條記錄中的數字相乘即為該查詢的總開銷；Extra 列為一些其它相關信息。

4. 優化多表查詢

(1) 子查詢轉連接

例如：

select email from cdb_members where uid = (select uid from cdb_memberfields where nickname = aquxyz

可轉化為：

select email from cdb_members c_m, cdb_memberfields c_mf where c_m.uid = c_mf.uid and nickname = aquxyz

(2) 巧妙利用對結果集的限制

例如：

select nickname from cdb_memberfields where uid = (select uid from cdb_members where email like %com.cn and regdate = (select min(regdate) from cdb_members));

可轉化為：

select nickname from cdb_memberfields c_mf, cdb_members c_m where c_mf.uid = c_m.uid and c_m.email like %com.cn order by c_m.regdate asc limit 1;

(3) 使用變量

還是上面的例子，可轉化為如下兩條語句：

select @min:=min(regdate) from cdb_members;
select nickname from cdb_memberfields c_mf, cdb_members c_m where c_mf.uid = c_m.uid and c_m.email like %com.cn and c_m.regdate = @min;

5. 優化表

1 對于經常修改的表，使用定長字段，而不用變長字段（對 InnoDB 表無效）；刪除沒有用的字段。
2 OPTIMIZE TABLE 命令。
3 對表進行合并；在查詢中使用具有相同類型和長度的連接字段。

6. 調整服務器設置

可以通過調整一些服務器系統變量，提高 MySQL 服務器的性能。

1 key_buffer_size：MyISAM 表的索引塊使用的緩沖區大小。一般可以設置為服務器可用內存的 25%-30%。
2 table_cache：MySQL 的所有線程可以打開的表的總數。應根據 Opened_tables 狀態變量的值來決定是否要增大該值。
3 sort_buffer_size：為每個線程的排序操作分配的緩沖區大小。增大該值可以加快 ORDER BY 和 GROUP BY 操作。
4 read_buffer_size：為每個線程的 sequential scan 分配的緩沖區。增大該值可以提高查詢速度。
5 read_rnd_buffer_size：為每個線程的 random scan 分配的緩沖區。索引排序操作后，MySQL 從這個緩沖區中讀取排好順序的行，增大該值可以加快 ORDER BY 操作。每個客戶端都會分配此緩沖區，所以不要全局變量設置得太大。
6 binlog_cache_size：事務過程中為為 binary log 保存 SQL 語句的高速緩存。如果經常使用大型、多語句的事務，可以增大該值以獲得更好的性能。
7 bulk_insert_buffer_size：為每個線程的批量 insert 操作分配的樹狀 cache 的大小。
8 tread_cache_size：服務器緩存線程以便重用的總數。如果有許多新連接，可以考慮增大該值。

undefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefined

關于如何進行 MySQL 管理基礎中的性能優化問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

正文完