MySQL入門教程之學習筆記

慢速SQL：執行時間超過給定時間範圍的查詢就稱為慢速查詢。

在MySQL中如何記錄慢速SQL？

答：可以在my.cnf中設定如下資訊：

 

1 [mysqld] 

2 ; enable the slow query log, default 10 seconds 

3 log-slow-queries 

4 ; log queries taking longer than 5 seconds 

5 long_query_time = 5 

6 ; log queries that don't use indexes even if they take less than long_query_time 

7 ; MySQL 4.1 and newer only 

8 log-queries-not-using-indexes

這三個設定的意思是可以記錄執行時間超過5 秒和沒有使用索引的查詢.

MySQL中日誌分類：

1. error log mysql錯誤記錄日誌

2. bin log 記錄修改資料時候產生的quer並用二進位的方式進行儲存

3. mysql-bin.index 記錄是記錄所有Binary Log 的絕對路徑，保證MySQL 各種線程能夠順利的根據它找到所有需要的Binary Log 檔案。

4. slow query log 記錄慢速SQL，是一個簡單的文字格式設定，可以通過各種文字編輯器查看其中的內容。其中記錄了語句執行的時刻，執行所消耗的時間，執行使用者。

5. innodb redo log 記錄Innodb 所做的所有物理變更和事務資訊，保證事務安全性。

 

SQL架構可分為：SQL 層 與 Storage Engine層

SQL Layer 中包含了多個子模組：
1、初始化模組
顧名思議，初始化模組就是在MySQL Server 啟動的時候，對整個系統做各種各樣的初始化操作，比如各種buffer，cache 結構的初始化和記憶體空間的申請，各種系統變數的初始化設定，各種儲存引擎的初始化設定，等等。
2、核心API
核心API 模組主要是為了提供一些需要非常高效的底層操作功能的最佳化實現，包括各種底層資料結構的實現，特殊演算法的實現，字串處理，數文書處理等，小檔案I/O，格式化輸出，以及最重要的記憶體管理部分。核心API 模組的所有原始碼都集中在mysys 和strings檔案夾下面，有興趣的讀者可以研究研究。
3、網路互動模組
底層網路互動模組抽象出底層網路互動所使用的介面api，實現底層網路資料的接收與發送，以方便其他各個模組調用，以及對這一部分的維護。所有源碼都在vio 檔案夾下面。
4、Client & Server 互動協議模組
任何C/S 結構的軟體系統，都肯定會有自己專屬的資訊互動協議，MySQL 也不例外。MySQL的Client & Server 互動協議模組部分，實現了用戶端與MySQL 互動過程中的所有協議。當然這些協議都是建立在現有的OS 和網路通訊協定之上的，如TCP/IP 以及Unix Socket。
5、使用者模組
使用者模組所實現的功能，主要包括使用者的登入串連許可權控制和使用者的授權管理。他就像MySQL 的大門守衛一樣，決定是否給來訪者"開門"。
6、存取控制模組

造訪客人進門了就可以想幹嘛就幹嘛嗎？為了安全考慮，肯定不能如此隨意。這時候就需要存取控制模組即時監控客人的每一個動作，給不同的客人以不同的許可權。存取控制模組實現的功能就是根據使用者模組中各使用者的授權資訊，以及資料庫自身特有的各種約束，來控制使用者對資料的訪問。使用者模組和存取控制模組兩者結合起來，組成了MySQL 整個資料庫系統的許可權安全管理的功能。
7、串連管理、連接線程和線程管理
串連管理模組負責監聽對MySQL Server 的各種請求，接收串連請求，轉寄所有串連請求到線程管理模組。每一個串連上MySQL Server 的用戶端請求都會被分配（或建立）一個連接線程為其單獨服務。而連接線程的主要工作就是負責MySQL Server 與用戶端的通訊，接受用戶端的命令請求，傳遞Server 端的結果資訊等。線程管理模組則負責管理維護這些連接線程。包括線程的建立，線程的cache 等。
8、Query 解析和轉寄模組
在MySQL 中我們習慣將所有Client 端發送給Server 端的命令都稱為query，在MySQLServer 裡面，連接線程接收到用戶端的一個Query 後，會直接將該query 傳遞給專門負責將各種Query 進行分類然後轉寄給各個對應的處理模組，這個模組就是query 解析和轉寄模組。其主要工作就是將query 語句進行語義和文法的分析，然後按照不同的操作類型進行分類，然後做出針對性的轉寄。
9、Query Cache 模組
Query Cache 模組在MySQL 中是一個非常重要的模組，他的主要功能是將用戶端提交給MySQL 的Select 類query 請求的返回結果集cache 到記憶體中，與該query 的一個hash 值做一個對應。該Query 所取資料的基表發生任何資料的變化之後，MySQL 會自動使該query 的Cache 失效。在讀寫比例非常高的應用系統中，Query Cache 對效能的提高是非常顯著的。當然它對記憶體的消耗也是非常大的。
10、Query 最佳化器模組
Query 最佳化器，顧名思義，就是最佳化用戶端請求的query，根據用戶端請求的query 語句，和資料庫中的一些統計資訊，在一系列演算法的基礎上進行分析，得出一個最優的策略，告訴後面的程式如何取得這個query 語句的結果。
11、表變更管理模組
表變更管理模組主要是負責完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等語句的處理。
12、表維護模組
表的狀態檢查，錯誤修複，以及最佳化和分析等工作都是表維護模組需要做的事情。
13、系統狀態管理模組

系統狀態管理模組負責在用戶端請求系統狀態的時候，將各種狀態資料返回給使用者，像DBA 常用的各種show status 命令，show variables 命令等，所得到的結果都是由這個模組返回的。
14、表管理器
這個模組從名字上看來很容易和上面的表變更和表維護模組相混淆，但是其功能與變更及維護模組卻完全不同。大家知道，每一個MySQL 的表都有一個表的定義檔案，也就是*.frm檔案。表管理器的工作主要就是維護這些檔案，以及一個cache，該cache 中的主要內容是各個表的結構資訊。此外它還維護table 層級的鎖管理。
15、日誌記錄模組
日誌記錄模組主要負責整個系統層級的邏輯層的日誌的記錄，包括error log，binarylog，slow query log 等。
16、複製模組
複製模組又可分為Master 模組和Slave 模組兩部分， Master 模組主要負責在Replication 環境中讀取Master 端的binary 日誌，以及與Slave 端的I/O 線程互動等工作。Slave 模組比Master 模組所要做的事情稍多一些，在系統中主要體現在兩個線程上面。一個是負責從Master 請求和接受binary 日誌，並寫入本地relay log 中的I/O 線程。另外一個是負責從relay log 中讀取相關日誌事件，然後解析成可以在Slave 端正確執行並得到和Master 端完全相同的結果的命令並再交給Slave 執行的SQL 線程。
17、儲存引擎介面模組
儲存引擎介面模組可以說是MySQL 資料庫中最有特色的一點了。目前各種資料庫產品

MySQL效能調優之監控方法：

1. set profiling=1 開啟效能監控，此命令在某些版本的mysql中無法使用

2. 然後執行SQL

3. show profiless，查看系統執行SQL的時間

4. show profile cpu, block io for query 數字ID （此ID為show profiles中的效能輸出日誌序號）

 MySQL 各儲存引擎使用了三種類型（層級）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。

在MySQL 資料庫中，使用表級鎖定的主要是MyISAM，Memory，CSV 等一些非事務性儲存引擎，而使用行級鎖定的主要是Innodb 儲存引擎和NDB Cluster 儲存引擎，頁級鎖定主要是BerkeleyDB 儲存引擎的鎖定方式。

 

MyISAM讀請求和寫等待隊列中的寫鎖請求的優先順序規則主要為以下規則決定：
1. 除了READ_HIGH_PRIORITY 的讀鎖定之外，Pending write-lock queue 中的WRITE 寫鎖定能夠阻塞所有其他的讀鎖定；
2. READ_HIGH_PRIORITY 讀鎖定的請求能夠阻塞所有Pending write-lock queue 中的寫鎖定；
3. 除了WRITE 寫鎖定之外，Pending write-lock queue 中的其他任何寫鎖定都比讀鎖定的優先順序低。

MyISAM寫鎖定出現在Current write-lock queue 之後，會阻塞除了以下情況下的所有其他鎖定的請求：
1. 在某些儲存引擎的允許下，可以允許一個WRITE_CONCURRENT_INSERT 寫鎖定請求
2. 寫鎖定為WRITE_ALLOW_WRITE 的時候，允許除了WRITE_ONLY 之外的所有讀和寫鎖定請求
3. 寫鎖定為WRITE_ALLOW_READ 的時候，允許除了READ_NO_INSERT 之外的所有讀鎖定請求
4. 寫鎖定為WRITE_DELAYED 的時候，允許除了READ_NO_INSERT 之外的所有讀鎖定請求
5. 寫鎖定為WRITE_CONCURRENT_INSERT 的時候，允許除了READ_NO_INSERT 之外的所有讀鎖定請求

 

Innodb 的行級鎖定注意事項：
a) 儘可能讓所有的資料檢索都通過索引來完成，從而避免Innodb 因為無法通過索引鍵加鎖而升級為表級鎖定；
b) 合理設計索引，讓Innodb 在索引鍵上面加鎖的時候儘可能準確，儘可能的縮小鎖定範圍，避免造成不必要的鎖定而影響其他Query 的執行；
c) 儘可能減少基於範圍的資料檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；
d) 盡量控制事務的大小，減少鎖定資源量和鎖定時間長度；
e) 在業務環境允許的情況下，盡量使用較低層級的事務隔離，以減少MySQL 因為實現交易隔離等級所帶來的附加成本；

如何查看MyISAM中表級鎖定資訊：

答：show status like '%table_locks%'

     table_locks_immediate:顯示的數字就是鎖定的次數。

     table_locks_waited:顯示的數字是出現表級鎖定爭用而發生等待的次數

 

如何查看Innodb中行級鎖定資訊：

答： show status like '%Innodb_rows%'

Innodb 的行級鎖定狀態變數不僅記錄了鎖定等待次數，還記錄了鎖定總時間長度，每次平均時間長度，以及最大時間長度，此外還有一個非累積狀態量顯示了當前正在等待鎖定的等待數量。對各個狀態量的說明如下：
● Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
● Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
● Innodb_row_lock_time_avg：每次等待所花平均時間；
● Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
● Innodb_row_lock_waits：系統啟動後到現在總共等待的次數；

 

mysqlslap是一個mysql官方提供的壓力測試工具。以下是比較重要的參數：
–defaults-file，設定檔存放位置
–concurrency，並發數
–engines，引擎
–iterations，迭代的實驗次數
–socket，socket檔案位置

自動化的測試：
–auto-generate-sql，自動產生測試SQL
–auto-generate-sql-load-type，測試SQL的類型。類型有mixed，update，write，key，read。
–number-of-queries，執行的SQL總數量
–number-int-cols，表內int列的數量
–number-char-cols，表內char列的數量

例如：
shell>mysqlslap –defaults-file=/u01/mysql1/mysql/my.cnf –concurrency=50,100 –iterations=1 –number-int-cols=4 –auto-generate-sql –auto-generate-sql-load-type=write –engine=myisam –number-of-queries=200 -S/tmp/mysql1.sock
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.016 seconds
Minimum number of seconds to run all queries: 0.016 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 50
Average number of queries per client: 4

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.265 seconds
Minimum number of seconds to run all queries: 0.265 seconds
Maximum number of seconds to run all queries: 0.265 seconds
Number of clients running queries: 100
Average number of queries per client: 2

指定資料庫的測試：
–create-schema，指定資料庫名稱
–query，指定SQL語句，可以定位到某個包含SQL的檔案

例如：
shell>mysqlslap –defaults-file=/u01/mysql1/mysql/my.cnf –concurrency=25,50 –iterations=1 –create-schema=test –query=/u01/test.sql -S/tmp/mysql1.sock
Benchmark
Average number of seconds to run all queries: 0.018 seconds
Minimum number of seconds to run all queries: 0.018 seconds
Maximum number of seconds to run all queries: 0.018 seconds
Number of clients running queries: 25
Average number of queries per client: 1

Benchmark
Average number of seconds to run all queries: 0.011 seconds
Minimum number of seconds to run all queries: 0.011 seconds
Maximum number of seconds to run all queries: 0.011 seconds
Number of clients running queries: 50
Average number of queries per client: 1

 

MySQL 中索引使用相關的限制:
1. MyISAM 儲存引擎索引鍵長度總和不能超過1000 位元組；
2. BLOB 和TEXT 類型的列只能建立首碼索引；
3. MySQL 目前不支援函數索引；
4. 使用不等於（!= 或者<>）的時候MySQL 無法使用索引；
5. 過濾欄位使用了函數運算後（如abs(column)），MySQL 無法使用索引；
6. Join 語句中Join 條件欄位類型不一致的時候MySQL 無法使用索引；
7. 使用LIKE 操作的時候如果條件以萬用字元開始（ '%abc...'）MySQL 無法使用索引；
8. 使用非等值查詢的時候MySQL 無法使用Hash 索引；

MySQL 目前可以通過兩種演算法來實現資料的排序操作:
1. 取出滿足過濾條件的用於排序條件的欄位以及可以直接定位到行資料的行指標資訊，在SortBuffer 中進行實際的排序操作，然後利用排好序之後的資料根據行指標資訊返回表中取得用戶端請求的其他欄位的資料，再返回給用戶端；
2. 根據過濾條件一次取出排序欄位以及用戶端請求的所有其他欄位的資料，並將不需要排序的欄位存放在一塊記憶體地區中，然後在Sort Buffer 中將排序欄位和行指標資訊進行排序，最後再利用排序後的行指標與存放在記憶體地區中和其他欄位一起的行指標資訊進行匹配合并結果集，再按照順序返回給用戶端。

 

MySQL Explain 功能中給我們展示的各種資訊的解釋：
◆ ID：Query Optimizer 所選定的執行計畫中查詢的序號；
◆ Select_type：所使用的查詢類型，主要有以下這幾種查詢類型
◇ DEPENDENT SUBQUERY：子查詢中內層的第一個SELECT，依賴於外部查詢的結果集；
◇ DEPENDENT UNION：子查詢中的UNION，且為UNION 中從第二個SELECT 開始的後面所有SELECT，同樣依賴於外部查詢的結果集；
◇ PRIMARY：子查詢中的最外層查詢，注意並不是主鍵查詢；
◇ SIMPLE：除子查詢或者UNION 之外的其他查詢；
◇ SUBQUERY：子查詢內層查詢的第一個SELECT，結果不依賴於外部查詢結果集；
◇ UNCACHEABLE SUBQUERY：結果集無法緩衝的子查詢；
◇ UNION：UNION 語句中第二個SELECT 開始的後面所有SELECT，第一個SELECT 為PRIMARY
◇ UNION RESULT：UNION 中的合并結果；
◆ Table：顯示這一步所訪問的資料庫中的表的名稱；
◆ Type：告訴我們對錶所使用的訪問方式，主要包含如下集中類型；
◇ all：全表掃描
◇ const：讀常量，且最多隻會有一條記錄匹配，由於是常量，所以實際上只需要讀一次；
◇ eq_ref：最多隻會有一條匹配結果，一般是通過主鍵或者唯一鍵索引來訪問；
◇ fulltext：
◇ index：全索引掃描；
◇ index_merge：查詢中同時使用兩個（或更多）索引，然後對索引結果進行merge 之後再讀取表資料；
◇ index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或者唯一索引；
◇ rang：索引範圍掃描；
◇ ref：Join 語句中被驅動表索引引用查詢；
◇ ref_or_null：與ref 的唯一區別就是在使用索引引用查詢之外再增加一個空值的查詢；
◇ system：系統資料表，表中只有一行資料；
◇ unique_subquery：子查詢中的返回結果欄位組合是主鍵或者唯一約束；
◆ Possible_keys：該查詢可以利用的索引. 如果沒有任何索引可以使用，就會顯示成null，這一項內容對於最佳化時候索引的調整非常重要；
◆ Key：MySQL Query Optimizer 從possible_keys 中所選擇使用的索引；
◆ Key_len：被選中使用索引的索引鍵長度；
◆ Ref：列出是通過常量（const），還是某個表的某個欄位（如果是join）來過濾（通過key）的；
◆ Rows：MySQL Query Optimizer 通過系統收集到的統計資訊估算出來的結果集記錄條數；
◆ Extra：查詢中每一步實現的額外細節資訊，主要可能會是以下內容：
◇ Distinct：尋找distinct 值，所以當mysql 找到了第一條匹配的結果後，將停止該值的查詢而轉為後面其他值的查詢；
◇ Full scan on NULL key：子查詢中的一種最佳化方式，主要在遇到無法通過索引訪問null值的使用使用；
◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過收集到的統計資訊判斷出不可能存在結果；
◇ No tables：Query 語句中使用FROM DUAL 或者不包含任何FROM 子句；
◇ Not exists：在某些左串連中MySQL Query Optimizer 所通過改變原有Query 的組成而使用的最佳化方法，可以部分減少資料訪問次數；
◇ Range checked for each record (index map: N)：通過MySQL 官方手冊的描述，當MySQL Query Optimizer 沒有發現好的可以使用的索引的時候，如果發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL 檢查是否可以使用range 或index_merge 存取方法來索取行。
◇ Select tables optimized away：當我們使用某些彙總函式來訪問存在索引的某個欄位的時候，MySQL Query Optimizer 會通過索引而直接一次定位到所需的資料行完成整個查詢。當然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的時
候；
◇ Using filesort：當我們的Query 中包含ORDER BY 操作，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應的排序演算法來實現。
◇ Using index：所需要的資料只需要在Index 即可全部獲得而不需要再到表中取資料；
◇ Using index for group-by：資料訪問和Using index 一樣，所需資料只需要讀取索引即可，而當Query 中使用了GROUP BY 或者DISTINCT 子句的時候，如果分組欄位也在索引中，Extra 中的資訊就會是Using index for group-by；
◇ Using temporary：當MySQL 在某些操作中必須使用暫存資料表的時候，在Extra 資訊中就會出現Using temporary 。主要常見於GROUP BY 和ORDER BY 等操作中。
◇ Using where：如果我們不是讀取表的所有資料，或者不是僅僅通過索引就可以擷取所有需要的資料，則會出現Using where 資訊；
◇ Using where with pushed condition：這是一個僅僅在NDBCluster 儲存引擎中才會出現的資訊，而且還需要通過開啟Condition Pushdown 最佳化功能才可能會被使用。控制參數為engine_condition_pushdown 。

MySQL 處理GROUP BY 的方式，有兩種如下最佳化思路：
1. 儘可能讓MySQL 可以利用索引來完成GROUP BY 操作，當然最好是鬆散索引掃描的方式最佳。在系統允許的情況下，我們可以通過調整索引或者調整Query 這兩種方式來達到目的；

2. 當無法使用索引完成GROUP BY 的時候，由於要使用到暫存資料表且需要filesort，所以我們必須要有足夠的sort_buffer_size 來供MySQL 排序的時候使用，而且盡量不要進行大結果集的GROUPBY 操作，因為如果超出系統設定的暫存資料表大小的時候會出現將暫存資料表資料copy 到磁碟上面再進行操作，這時候的排序分組操作效能將是成數量級的下降；

 

DINSTINCT 其實和 GROUP BY 原理類似，同樣可以使用鬆散索引。

 

MySQL Schema 設計最佳化小記：

1. 適度冗餘

2. 大欄位垂直分拆

3. 大表水平分拆

 

時間欄位類型：timestamp 佔用4個位元組，datetime,date佔用8個位元組，但是timestamp只能用在1970年以後的記錄，datetime,date可用在1001年開始。

 

MySQL binlog日誌最佳化方案：

Binlog 相關參數及最佳化策略
我們首先看看Binlog 的相關參數，通過執行如下命令可以獲得關於Binlog 的相關參數。當然，其中也顯示出了" innodb_locks_unsafe_for_binlog"這個Innodb 儲存引擎特有的與Binlog 相關的參數：
mysql> show variables like '%binlog%';
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| binlog_cache_size | 1048576 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| sync_binlog | 0 |
+--------------------------------+------------+
"binlog_cache_size"：在事務過程中容納二進位日誌SQL 陳述式的緩衝大小。二進位日誌緩衝是伺服器支援事務儲存引擎並且伺服器啟用了二進位日誌(—log-bin 選項)的前提下為每個用戶端分配的記憶體，注意，是每個Client 都可以分配設定大小的binlog cache 空間。如果讀者朋友的系統中經常會出現多語句事務的華，可以嘗試增加該值的大小，以獲得更好的效能。當然，我們可以通過MySQL 的以下兩個狀態變數來判斷當前的binlog_cache_size 的狀況：Binlog_cache_use 和Binlog_cache_disk_use。"max_binlog_cache_size"：和"binlog_cache_size"相對應，但是所代表的是binlog 能夠使用的最大cache 記憶體大小。當我們執行多語句事務的時候，max_binlog_cache_size 如果不夠大的話，系統可能會報出" Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage"的錯誤。

"max_binlog_size"：Binlog 日誌最大值，一般來說設定為512M 或者1G，但不能超過1G。該大小並不能非常嚴格控制Binlog 大小，尤其是當到達Binlog 比較靠近尾部而又遇到一個較大事務的時候，系統為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL 都記錄進入當前日誌，直到該事務結束。這一點和Oracle 的Redo 日誌有點不一樣，因為Oracle 的Redo 日誌所記錄的是資料檔案的物理位置的變化，而且裡面同時記錄了Redo 和Undo 相關的資訊，所以同一個事務是否在一個日誌中對Oracle 來說並不關鍵。而MySQL 在Binlog 中所記錄的是資料庫邏輯變化資訊，MySQL 稱之為Event，實際上就是帶來資料庫變化的DML 之類的Query 語句。"sync_binlog"：這個參數是對於MySQL 系統來說是至關重要的，他不僅影響到Binlog 對MySQL 所帶來的效能損耗，而且還影響到MySQL 中資料的完整性。對於"sync_binlog"參數的各種設定的說明如下：
● sync_binlog=0，當事務提交之後，MySQL 不做fsync 之類的磁碟同步指令重新整理binlog_cache 中的資訊到磁碟，而讓Filesystem 自行決定什麼時候來做同步，或者cache 滿了之後才同步到磁碟。
● sync_binlog=n，當每進行n 次事務提交之後，MySQL 將進行一次fsync 之類的磁碟同步指令來將binlog_cache 中的資料強制寫入磁碟。在MySQL 中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟排清指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache 中的所有binlog 資訊都會被丟失。而當設定為"1"的時候，是最安全但是效能損耗最大的設定。因為當設定為1 的時候，即使系統Crash，也最多丟失binlog_cache 中未完成的一個事務，對實際資料沒有任何實質性影響。從以往經驗和相關測試來看，對於高並發事務的系統來說，"sync_binlog"設定為0 和設定為1 的系統寫入效能差距可能高達5 倍甚至更多。

 

MySQL QueryCache 負面影響：
a) Query 語句的hash 運算以及hash 尋找資源消耗。當我們使用Query Cache 之後，每條SELECT類型的Query 在到達MySQL 之後，都需要進行一個hash 運算然後尋找是否存在該Query 的Cache，雖然這個hash 運算的演算法可能已經非常高效了，hash 尋找的過程也已經足夠的最佳化了，對於一條Query 來說消耗的資源確實是非常非常的少，但是當我們每秒都有上千甚至幾千條Query 的時候，我們就不能對產生的CPU 的消耗完全忽視了。
b) Query Cache 的失效問題。如果我們的表變更比較頻繁，則會造成Query Cache 的失效率非常高。這裡的表變更不僅僅指表中資料的變更，還包括結構或者索引等的任何變更。也就是說我們每次緩衝到Query Cache 中的Cache 資料可能在剛存入後很快就會因為表中的資料被改變而被清除，然後新的相同Query 進來之後無法使用到之前的Cache。
c) Query Cache 中緩衝的是Result Set ，而不是資料頁，也就是說，存在同一條記錄被Cache 多次的可能性存在。從而造成記憶體資源的過渡消耗。當然，可能有人會說我們可以限定QueryCache 的大小啊。是的，我們確實可以限定Query Cache 的大小，但是這樣，Query Cache 就很容易造成因為記憶體不足而被換出，造成命中率的下降。

 

在短連線應用程式系統中，thread_cache_size 的值應該設定的相對大一些，不應該小於應用系統對資料庫的實際並發請求數。

 

通過系統設定和目前狀態的分析，我們可以發現，thread_cache_size 的設定已經足夠了，甚至還遠大於系統的需要。所以我們可以適當減少thread_cache_size 的設定，比如設定為8 或者16。根據Connections 和Threads_created 這兩個系統狀態值，我們還可以計算出系統建立串連串連的ThreadCache 命中率，也就是通過Thread Cache 池中取得連接線程的次數與系統接收的總串連次數的比率，如下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%

一般來說，當系統穩定運行一段時間之後，我們的Thread Cache 命中率應該保持在90%左右甚至更高的比率才算正常。可以看出上面環境中的Thread Cache 命中比率基本還算是正常的。

 

如何查看MySQL開啟Table的數量：

mysql> show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 6 |
+---------------+-------+

 

MySQL buffer注意事項

join_buffer_size 和 sort_buffer_size 是針對的每個線程的buffer大小而言的，而不是整個系統共用的Buffer。

 

假設是一台單獨給MySQL 使用的主機，實體記憶體總大小為8G，MySQL 最大串連數為500，同時還使用了MyISAM 儲存引擎，這時候我們的整體記憶體該如何分配呢？
記憶體配置為如下幾大部分：
a) 系統使用，假設預留800M；
b) 線程獨享，約2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB)，組成大概如下:
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c) MyISAM Key Cache，假設大概為1.5GB；
d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；