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

如何進行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。例如：

mysql> explain select a.username, b.nickname, a.email from members a, memberfields b where a.uid = b.uid and a.email lik
e "%163.com";
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY       | NULL    | NULL    | NULL        | 46237 |             |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 3       | test2.b.uid |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
2 rows in set (0.00 sec)

   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管理基礎中的性能優化問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。