Mysql機能優化計劃分享

Mysql機能優化計劃分享。本站提示廣大學習愛好者：（Mysql機能優化計劃分享）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql機能優化計劃分享正文

網上有很多mysql 機能優化計劃，不外，mysql的優化同sql server比擬，更加費事，異樣的設置，在分歧的情況下 ，因為內存，拜訪量，讀寫頻率，數據差別等等情形，能夠會湧現分歧的成果，是以簡略地依據某個給出計劃來設置裝備擺設mysql是行欠亨的，最好能應用status信息對mysql停止詳細的優化。

mysql> show global status;

可以列出MySQL辦事器運轉各類狀況值，別的，查詢MySQL辦事器設置裝備擺設信息語句：
mysql> show variables;

1、慢查詢

mysql> show variables like ‘%slow%‘;
+——————+——-+
| Variable_name　　　　 | Value |
+——————+——-+
| log_slow_queries | ON　　　　 |
| slow_launch_time | 2　　　　　 |
+——————+——-+

mysql> show global status like ‘%slow%‘;
+———————+——-+
| Variable_name　　　　　　　 | Value |
+———————+——-+
| Slow_launch_threads | 0　　　　　 |
| Slow_queries　　　　　　　　 | 4148 |
+———————+——-+

設置裝備擺設中翻開了記載慢查詢，履行時光跨越2秒的即為慢查詢，體系顯示有4148個慢查詢，你可以剖析慢查詢日記，找出有成績的SQL語句，慢查詢時光不宜設置太長，不然意義不年夜，最好在5秒之內，假如你須要微秒級其余慢查詢，可以斟酌給MySQL打補釘：http://www.percona.com/docs/wiki/release:start，記得找對應的版本。
翻開慢查詢日記能夠會對體系機能有一點點影響，假如你的MySQL是主－從構造，可以斟酌翻開個中一台從辦事器的慢查詢日記，如許既可以監控慢查詢，對體系機能影響又小。

2、銜接數
常常會碰見”MySQL: ERROR 1040: Too many connections”的情形，一種是拜訪量確切很高，MySQL辦事器抗不住，這個時刻就要斟酌增長從辦事器疏散讀壓力，別的一種情形是MySQL設置裝備擺設文件中max_connections值太小：

mysql> show variables like ‘max_connections‘;
+—————–+——-+
| Variable_name　　　 | Value |
+—————–+——-+
| max_connections | 256　　 |
+—————–+——-+

這台MySQL辦事器最年夜銜接數是256，然後查詢一下辦事器呼應的最年夜銜接數：
mysql> show global status like ‘Max_used_connections‘;
MySQL辦事器曩昔的最年夜銜接數是245，沒有到達辦事器銜接數下限256，應當沒有湧現1040毛病，比擬幻想的設置是
Max_used_connections / max_connections * 100% ≈ 85%
最年夜銜接數占下限銜接數的85％閣下，假如發明比例在10%以下，MySQL辦事器銜接數下限設置的太高了。
3、Key_buffer_size
key_buffer_size是對MyISAM表機能影響最年夜的一個參數，上面一台以MyISAM為重要存儲引擎辦事器的設置裝備擺設：

mysql> show variables like ‘key_buffer_size‘;+—————–+————+
| Variable_name　　　 | Value　　　　　　 |
+—————–+————+
| key_buffer_size | 536870912 |
+—————–+————+

分派了512MB內存給key_buffer_size，我們再看一下key_buffer_size的應用情形：

mysql> show global status like ‘key_read%‘;
+————————+————-+
| Variable_name　　　　　　　　　　 | Value　　　　　　　 |
+————————+————-+
| Key_read_requests　　　　　　 | 27813678764 |
| Key_reads　　　　　　　　　　　　　　 | 6798830　　　　　 |
+————————+————-+

一共有27813678764個索引讀取要求，有6798830個要求在內存中沒有找到直接從硬盤讀取索引，盤算索引未射中緩存的幾率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
好比下面的數據，key_cache_miss_rate為0.0244%，4000個索引讀取要求才有一個直接讀硬盤，曾經很BT了，key_cache_miss_rate在0.1%以下都很好（每1000個要求有一個直接讀硬盤），假如key_cache_miss_rate在0.01%以下的話，key_buffer_size分派的過量，可以恰當削減。
MySQL辦事器還供給了key_blocks_*參數：

mysql> show global status like ‘key_blocks_u%‘;
+————————+————-+
| Variable_name　　　　　　　　　　 | Value　　　　　　　 |
+————————+————-+
| Key_blocks_unused　　　　　　 | 0　　　　　　　　　　　 |
| Key_blocks_used　　　　　　　　 | 413543　　　　　　 |
+————————+————-+

Key_blocks_unused表現未應用的緩存簇(blocks)數，Key_blocks_used表現已經用到的最年夜的blocks數，好比這台辦事器，一切的緩存都用到了，要末增長key_buffer_size，要末就是過渡索引了，把緩存占滿了。比擬幻想的設置：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
4、暫時表

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增長，假如是在磁盤上創立暫時表，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%，應當相當好了。我們再看一下MySQL辦事器對暫時表的設置裝備擺設：

mysql> show variables where Variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘);
+———————+———–+
| Variable_name　　　　　　　 | Value　　　　　 |
+———————+———–+
| max_heap_table_size | 268435456 |
| tmp_table_size　　　　　　 | 536870912 |
+———————+———–+

只要256MB以下的暫時表能力全體放內存，跨越的就會用到硬盤暫時表。
5、Open Table情形

mysql> show global status like ‘open%tables%‘;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables　　　 | 919　　　 |
| Opened_tables | 1951　 |
+—————+——-+

Open_tables表現翻開表的數目，Opened_tables表現翻開過的表數目，假如Opened_tables數目過年夜，解釋設置裝備擺設中table_cache(5.1.3以後這個值叫做table_open_cache)值能夠太小，我們查詢一下辦事器table_cache值：

mysql> show variables like ‘table_cache‘;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| table_cache　　　 | 2048　 |
+—————+——-+

比擬適合的值為：
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%
6、過程應用情形

mysql> show global status like ‘Thread%‘;
+——————-+——-+
| Variable_name　　　　　 | Value |
+——————-+——-+
| Threads_cached　　　　 | 46　　　　 |
| Threads_connected | 2　　　　　 |
| Threads_created　　　 | 570　　　 |
| Threads_running　　　 | 1　　　　　 |
+——————-+——-+

假如我們在MySQL辦事器設置裝備擺設文件中設置了thread_cache_size，當客戶端斷開以後，辦事器處置此客戶的線程將會緩存起來以呼應下一個客戶而不是燒毀（條件是緩存數未達下限）。Threads_created表現創立過的線程數，假如發明Threads_created值過年夜的話，注解MySQL辦事器一向在創立線程，這也是比擬耗資本，可以恰當增長設置裝備擺設文件中thread_cache_size值，查詢辦事器thread_cache_size設置裝備擺設：

mysql> show variables like ‘thread_cache_size‘;
+——————-+——-+
| Variable_name　　　　　 | Value |
+——————-+——-+
| thread_cache_size | 64　　　　 |
+——————-+——-+

示例中的辦事器照樣挺安康的。
7、查詢緩存(query cache)

mysql> show global status like ‘qcache%‘;
+————————-+———–+
| Variable_name　　　　　　　　　　　 | Value　　　　　 |
+————————-+———–+
| Qcache_free_blocks　　　　　　 | 22756　　　　　 |
| Qcache_free_memory　　　　　　 | 76764704　 |
| Qcache_hits　　　　　　　　　　　　　 | 213028692 |
| Qcache_inserts　　　　　　　　　　 | 208894227 |
| Qcache_lowmem_prunes　　　　 | 4010916　　　 |
| Qcache_not_cached　　　　　　　 | 13385031　 |
| Qcache_queries_in_cache | 43560　　　　　 |
| Qcache_total_blocks　　　　　 | 111212　　　　 |
+————————-+———–+

MySQL查詢緩存變量說明：
Qcache_free_blocks：緩存中相鄰內存塊的個數。數量年夜解釋能夠有碎片。FLUSH QUERY CACHE會對緩存中的碎片停止整頓，從而獲得一個余暇塊。
Qcache_free_memory：緩存中的余暇內存。
Qcache_hits：每次查詢在緩存中射中時就增年夜
Qcache_inserts：每次拔出一個查詢時就增年夜。射中次數除以拔出次數就是不中比率。
Qcache_lowmem_prunes：緩存湧現內存缺乏而且必需要停止清算以便為更多查詢供給空間的次數。這個數字最好長時光來看；假如這個數字在赓續增加，就表現能夠碎片異常嚴重，或許內存很少。（下面的 free_blocks和free_memory可以告知您屬於哪一種情形）
Qcache_not_cached：不合適停止緩存的查詢的數目，平日是因為這些查詢不是 SELECT 語句或許用了now()之類的函數。
Qcache_queries_in_cache：以後緩存的查詢（和呼應）的數目。
Qcache_total_blocks：緩存中塊的數目。
我們再查詢一下辦事器關於query_cache的設置裝備擺設：

mysql> show variables like ‘query_cache%‘;
+——————————+———–+
| Variable_name　　　　　　　　　　　　　　　　 | Value　　　　　 |
+——————————+———–+
| query_cache_limit　　　　　　　　　　　　 | 2097152　　　 |
| query_cache_min_res_unit　　　　　 | 4096　　　　　　 |
| query_cache_size　　　　　　　　　　　　　 | 203423744 |
| query_cache_type　　　　　　　　　　　　　 | ON　　　　　　　　 |
| query_cache_wlock_invalidate | OFF　　　　　　　 |
+——————————+———–+

各字段的說明：
query_cache_limit：跨越此年夜小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小年夜小
query_cache_size：查詢緩存年夜小
query_cache_type：緩存類型，決議緩存甚麼樣的查詢，示例中表現不緩存 select sql_no_cache 查詢
query_cache_wlock_invalidate：當有其他客戶正直在對MyISAM表停止寫操作時，假如查詢在query cache中，能否前往cache成果照樣等寫操作完成再讀表獲得成果。
query_cache_min_res_unit的設置裝備擺設是一柄”雙刃劍”，默許是4KB，設置值年夜對年夜數據查詢有利益，但假如你的查詢都是小數據查詢，就輕易形成內存碎片和糟蹋。
查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
假如查詢緩存碎片率跨越20%，可以用FLUSH QUERY CACHE整頓緩存碎片，或許嘗嘗減小query_cache_min_res_unit，假如你的查詢都是小數據量的話。
查詢緩存應用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查詢緩存應用率在25%以下的話解釋query_cache_size設置的過年夜，可恰當減小；查詢緩存應用率在80％以上並且Qcache_lowmem_prunes > 50的話解釋query_cache_size能夠有點小，要不就是碎片太多。
查詢緩存射中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例辦事器 查詢緩存碎片率 ＝ 20.46％，查詢緩存應用率 ＝ 62.26％，查詢緩存射中率 ＝ 1.94％，射中率很差，能夠寫操作比擬頻仍吧，並且能夠有些碎片。
8、排序應用情形

mysql> show global status like ‘sort%‘;
+——————-+————+
| Variable_name　　　　　 | Value　　　　　　 |
+——————-+————+
| Sort_merge_passes | 29　　　　　　　　　 |
| Sort_range　　　　　　　　 | 37432840　　　 |
| Sort_rows　　　　　　　　　 | 9178691532 |
| Sort_scan　　　　　　　　　 | 1860569　　　　 |
+——————-+————+

Sort_merge_passes 包含兩步。MySQL 起首會測驗考試在內存中做排序，應用的內存年夜小由體系變量 Sort_buffer_size 決議，假如它的年夜小不敷把一切的記載都讀到內存中，MySQL 就會把每次在內存中排序的成果存莅臨時文件中，等 MySQL 找到一切記載以後，再把暫時文件中的記載做一次排序。這再次排序就會增長 Sort_merge_passes。現實上，MySQL 會用另外一個暫時文件來存再次排序的成果，所以平日會看到 Sort_merge_passes 增長的數值是建暫時文件數的兩倍。由於用到了暫時文件，所以速度能夠會比擬慢，增長 Sort_buffer_size 會削減 Sort_merge_passes 和 創立暫時文件的次數。但自覺的增長 Sort_buffer_size 其實不必定能進步速度，見 How fast can you sort data with MySQL?（別的，增長read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的利益

9、文件翻開數(open_files)

mysql> show global status like ‘open_files‘;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_files　　　　 | 1410　 |
+—————+——-+
mysql> show variables like ‘open_files_limit‘;
+——————+——-+
| Variable_name　　　　 | Value |
+——————+——-+
| open_files_limit | 4590　 |
+——————+——-+

比擬適合的設置：Open_files / open_files_limit * 100% <= 75％
10、表鎖情形

mysql> show global status like ‘table_locks%‘;
+———————–+———–+
| Variable_name　　　　　　　　　 | Value　　　　　 |
+———————–+———–+
| Table_locks_immediate | 490206328 |
| Table_locks_waited　　　　 | 2084912　　　 |
+———————–+———–+

Table_locks_immediate表現立刻釋放表鎖數，Table_locks_waited表現須要期待的表鎖數，假如Table_locks_immediate / Table_locks_waited > 5000，最好采取InnoDB引擎，由於InnoDB是行鎖而MyISAM是表鎖，關於高並發寫入的運用InnoDB後果會好些。示例中的辦事器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足夠了。
11、表掃描情形

mysql> show global status like ‘handler_read%‘;
+———————–+————-+
| Variable_name　　　　　　　　　 | Value　　　　　　　 |
+———————–+————-+
| Handler_read_first　　　　 | 5803750　　　　　 |
| Handler_read_key　　　　　　 | 6049319850　 |
| Handler_read_next　　　　　 | 94440908210 |
| Handler_read_prev　　　　　 | 34822001724 |
| Handler_read_rnd　　　　　　 | 405482605　　　 |
| Handler_read_rnd_next | 18912877839 |
+———————–+————-+

調出辦事器完成的查詢要求次數：

mysql> show global status like ‘com_select‘;
+—————+———–+
| Variable_name | Value　　　　　 |
+—————+———–+
| Com_select　　　　 | 222693559 |
+—————+———–+

盤算表掃描率：
表掃描率 ＝ Handler_read_rnd_next / Com_select
假如表掃描率跨越4000，解釋停止了太多表掃描，很有能夠索引沒有建好，增長read_buffer_size值會有一些利益，但最好不要跨越8MB。

以上就是本文的全體內容，願望對年夜家的進修有所贊助。