1.  MySQL優化-查看執行記錄

MySQL 提供了一個 EXPLAIN 命令, 它可以對 SELECT 陳述句進行分析, 并輸出 SELECT 執行的詳細資訊, 以供開發人員針對性優化，

使用explain這個命令來查看一個這些SQL陳述句的執行計劃，查看該SQL陳述句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來查看，

EXPLAIN 命令用法十分簡單, 在 SELECT 陳述句前加上 explain 就可以了, 例如:

1.1   SQL陳述句優化-explain分析問題

expain出來的資訊有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面對這些欄位進行解釋：

id: select查詢的識別符號. 每個 select都會自動分配一個唯一的識別符號.

select_type:表示查詢的型別，

table:輸出結果集的表

type:表示表的連接型別

possible_keys:表示查詢時，可能使用的索引

key:表示實際使用的索引

key_len:索引欄位的長度

ref:哪個欄位或常數與key一起被使用

rows:掃描出的行數(估算的行數)

Extra:執行情況的描述和說明

1.1.1   id

SELECT識別符，這是SELECT的查詢序列號

1.1.2   select_type

PRIMARY    :子查詢中最外層查詢

SUBQUERY : 子查詢內層第一個SELECT，結果不依賴于外部查詢

DEPENDENT SUBQUERY:子查詢內層第一個SELECT，依賴于外部查詢

UNION   :UNION陳述句中第二個SELECT開始后面所有SELECT，

SIMPLE：簡單的 select 查詢,不使用 union 及子查詢

UNION ：UNION 中的第二個或隨后的 select 查詢,不依賴于外部查詢的結果集

1.1.3   Table

顯示這一步所訪問資料庫中表名稱

1.1.4   Type

對表訪問方式

ALL：

SELECT * FROM emp \G

完整的表掃描通常不好

SELECT * FROM (SELECT * FROM emp WHERE empno = 1) a ;

system：表僅有一行(=系統表)，這是const聯接型別的一個特

const：表最多有一個匹配行

1.1.5   Possible_keys

該查詢可以利用的索引，如果沒有任何索引顯示  null

1.1.6   Key 

Mysql 從 Possible_keys 所選擇使用索引

1.1.7   Rows

2.  MySQL優化-慢查詢

MySQL的慢查詢，全名是慢查詢日志，是MySQL提供的一種日志記錄，用來記錄在MySQL中回應時間超過閥值的陳述句，具體環境中，運行時間超過long_query_time值的SQL陳述句，則會被記錄到慢查詢日志中，簡單的說就是運行很長時間的sql陳述句

MySQL的慢查詢日志功能，默認是關閉的，需要手動開啟，

2.1      查看是否開啟慢查詢

?  slow_query_log   ：是否開啟慢查詢日志，ON 為開啟，OFF 為關閉，如果為關閉可以開啟，

?  log-slow-queries  ：舊版（5.6以下版本）MySQL資料庫慢查詢日志存盤路徑，可以不設定該引數，系統則會默認給一個預設的檔案host_name-slow.log

?  slow-query-log-file：新版（5.6及以上版本）MySQL資料庫慢查詢日志存盤路徑，可以不設定該引數，系統則會默認給一個預設的檔案host_name-slow.log

?  long_query_time ：慢查詢閾值，當查詢時間多于設定的閾值時，記錄日志，單位為秒，

2.2   臨時開啟慢查詢功能

在 MySQL 執行 SQL 陳述句設定，但是如果重啟 MySQL 的話將失效

set global slow_query_log = ON;

set global long_query_time = 1;

2.3   永久開啟慢查詢功能

修改/etc/my.cnf組態檔，重啟 MySQL, 這種永久生效. [mysqld]

slow_query_log = ON

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1

2.4   慢查詢格式

說明:

第一行,SQL查詢執行的時間

第二行,執行SQL查詢的連接資訊，用戶和連接IP

第三行,記錄了一些我們比較有用的資訊，如下決議

                      Query_time,這條SQL執行的時間,越長則越慢

                      Lock_time,在MySQL服務器階段(不是在存盤引擎階段)等待表鎖時間

                      Rows_sent,查詢回傳的行數

                      Rows_examined,查詢檢查的行數，越長就當然越費時間

第四行,設定時間戳，沒有實際意義，只是和第一行對應執行時間，

第五行及后面所有行（第二個# Time:之前）,執行的sql陳述句記錄資訊，因為sql可能會很長

3.  MySQL優化-profiling分析查詢

通過慢日志查詢可以知道哪些SQL陳述句執行效率低下，通過explain我們可以得知SQL陳述句的具體執行情況，索引使用等，還可以結合show命令查看執行狀態，
如果覺得explain的資訊不夠詳細，可以同通過profiling命令得到更準確的SQL執行消耗系統資源的信息，
profiling默認是關閉的，可以通過以下陳述句查看

打開功能： mysql>set profiling=1; --1是開啟、0是關閉

執行需要測驗的sql 陳述句：

mysql> show profiles\G; 可以得到被執行的SQL陳述句的時間和ID
mysql>show profile for query 1; 得到對應SQL陳述句執行的詳細資訊
show profile命令格式：
SHOW PROFILE [type [, type] … ]                                   
    [FOR QUERY n]                                                           
    [LIMIT row_count [OFFSET offset]]                            
type:                                                                                 
    ALL                                                                              
  | BLOCK IO                                                                     
  | CONTEXT SWITCHES                                                  
  | CPU                                                                             
  | IPC                                                                                  
  | MEMORY                                                                           
  | PAGE FAULTS                                                              
  | SOURCE                                                                       
  | SWAPS

　　以上的16rows是針對非常簡單的select陳述句的資源資訊，對于較復雜的SQL陳述句，會有更多的行和欄位，比如converting HEAP to MyISAM 、Copying to tmp table等等，由于以上的SQL陳述句不存在復雜的表操作，所以未顯示這些欄位，通過profiling資源耗費資訊，我們可以采取針對性的優化措施，

測驗完畢以后，關閉引數：mysql> set profiling=0

4.  MySQL鎖

資料庫鎖定機制簡單來說，就是資料庫為了保證資料的一致性，而使各種共享資源在被并發訪問變得有序所設計的一種規則，對于任何一種資料庫來說都需要有相應的鎖定機制，所以MySQL自然也不能例外，

MySQL資料庫由于其自身架構的特點，存在多種資料存盤引擎，每種存盤引擎所針對的應用場景特點都不太一樣，為了滿足各自特定應用場景的需求，每種存盤引擎的鎖定機制都是為各自所面對的特定場景而優化設計，所以各存盤引擎的鎖定機制也有較大區別，

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

4.1   表級鎖定（table-level）

表級別的鎖定是MySQL各存盤引擎中最大顆粒度的鎖定機制，該鎖定機制最大的特點是實作邏輯非常簡單，帶來的系統負面影響最小，所以獲取鎖和釋放鎖的速度很快，由于表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題，

當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，致使并大度大打折扣，

使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性存盤引擎，

4.2   行級鎖定（row-level）

行級鎖定最大的特點就是鎖定物件的顆粒度很小，也是目前各大資料庫管理軟體所實作的鎖定顆粒度最小的，由于鎖定顆粒度很小，所以發生鎖定資源爭用的概率也最小，能夠給予應用程式盡可能大的并發處理能力而提高一些需要高并發應用系統的整體性能，

雖然能夠在并發處理能力上面有較大的優勢，但是行級鎖定也因此帶來了不少弊端，由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了，此外，行級鎖定也最容易發生死鎖，

使用行級鎖定的主要是InnoDB存盤引擎，

4.3   頁級鎖定（page-level）

頁級鎖定是MySQL中比較獨特的一種鎖定級別，在其他資料庫管理軟體中也并不是太常見，頁級鎖定的特點是鎖定顆粒度介于行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的并發處理能力也同樣是介于上面二者之間，另外，頁級鎖定和行級鎖定一樣，會發生死鎖，

在資料庫實作資源鎖定的程序中，隨著鎖定資源顆粒度的減小，鎖定相同資料量的資料所需要消耗的記憶體數量是越來越多的，實作演算法也會越來越復雜，不過，隨著鎖定資源顆粒度的減小，應用程式的訪問請求遇到鎖等待的可能性也會隨之降低，系統整體并發度也隨之提升，

使用頁級鎖定的主要是BerkeleyDB存盤引擎，

4.4   總結:

?  表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低；

?  行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高；    

?  頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般，

?  適用：從鎖的角度來說，表級鎖更適合于以查詢為主，只有少量按索引條件更新資料的應用，如Web應用；而行級鎖則更適合于有大量按索引條件并發更新少量不同資料，同時又有并發查詢的應用，如一些在線事務處理（OLTP）系統，

標籤：MySQL

上一篇：MySQL卸載

下一篇：神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行程序（二）