本文章把我學習mysql時的一些學習筆記分享出來給各位學習mysql的朋友參考,希望些方法對各位有所幫助.

慢速SQL:執(zhí)行時間超過給定時間范圍的查詢就稱為慢速查詢.

在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 

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

MySQL中日志分類:

1.error log mysql錯誤記錄日志

2.bin log 記錄修改數據時候產生的quer并用二進制的方式進行存儲

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

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

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

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

SQL Layer 中包含了多個子模塊:

1、初始化模塊

顧名思議,初始化模塊就是在MySQL Server 啟動的時候,對整個系統(tǒng)做各種各樣的初始化操作,比如各種buffer，cache 結構的初始化和內存空間的申請,各種系統(tǒng)變量的初始化設定,各種存儲引擎的初始化設置,等等.

2、核心API

核心API 模塊主要是為了提供一些需要非常高效的底層操作功能的優(yōu)化實現，包括各種底層數據結構的實現，特殊算法的實現，字符串處理，數字處理等，小文件I/O，格式化輸出，以及最重要的內存管理部分。核心API 模塊的所有源代碼都集中在mysys 和strings文件夾下面，有興趣的讀者可以研究研究。

3、網絡交互模塊

底層網絡交互模塊抽象出底層網絡交互所使用的接口api，實現底層網絡數據的接收與發(fā)送，以方便其他各個模塊調用，以及對這一部分的維護。所有源碼都在vio 文件夾下面。

4、Client & Server 交互協(xié)議模塊

任何C/S 結構的軟件系統(tǒng)，都肯定會有自己獨有的信息交互協(xié)議，MySQL 也不例外。MySQL的Client & Server 交互協(xié)議模塊部分，實現了客戶端與MySQL 交互過程中的所有協(xié)議。當然這些協(xié)議都是建立在現有的OS 和網絡協(xié)議之上的，如TCP/IP 以及Unix Socket。

5、用戶模塊

用戶模塊所實現的功能,主要包括用戶的登錄連接權限控制和用戶的授權管理,他就像MySQL 的大門守衛(wèi)一樣,決定是否給來訪者"開門".

6、訪問控制模塊

造訪客人進門了就可以想干嘛就干嘛么？為了安全考慮，肯定不能如此隨意。這時候就需要訪問控制模塊實時監(jiān)控客人的每一個動作，給不同的客人以不同的權限。訪問控制模塊實現的功能就是根據用戶模塊中各用戶的授權信息，以及數據庫自身特有的各種約束，來控制用戶對數據的訪問。用戶模塊和訪問控制模塊兩者結合起來，組成了MySQL 整個數據庫系統(tǒng)的權限安全管理的功能。

7、連接管理、連接線程和線程管理

連接管理模塊負責監(jiān)聽對MySQL Server 的各種請求，接收連接請求，轉發(fā)所有連接請求到線程管理模塊。每一個連接上MySQL Server 的客戶端請求都會被分配（或創(chuàng)建）一個連接線程為其單獨服務。而連接線程的主要工作就是負責MySQL Server 與客戶端的通信，接受客戶端的命令請求，傳遞Server 端的結果信息等。線程管理模塊則負責管理維護這些連接線程。包括線程的創(chuàng)建，線程的cache 等。

8、Query 解析和轉發(fā)模塊

在MySQL 中我們習慣將所有Client 端發(fā)送給Server 端的命令都稱為query，在MySQLServer 里面，連接線程接收到客戶端的一個Query 后，會直接將該query 傳遞給專門負責將各種Query 進行分類然后轉發(fā)給各個對應的處理模塊，這個模塊就是query 解析和轉發(fā)模塊。其主要工作就是將query 語句進行語義和語法的分析，然后按照不同的操作類型進行分類，然后做出針對性的轉發(fā)。

9、Query Cache 模塊

Query Cache 模塊在MySQL 中是一個非常重要的模塊，他的主要功能是將客戶端提交給MySQL 的Select 類query 請求的返回結果集cache 到內存中，與該query 的一個hash 值做一個對應。該Query 所取數據的基表發(fā)生任何數據的變化之后，MySQL 會自動使該query 的Cache 失效。在讀寫比例非常高的應用系統(tǒng)中，Query Cache 對性能的提高是非常顯著的。當然它對內存的消耗也是非常大的。

10、Query 優(yōu)化器模塊

Query 優(yōu)化器，顧名思義，就是優(yōu)化客戶端請求的query，根據客戶端請求的query 語句，和數據庫中的一些統(tǒng)計信息，在一系列算法的基礎上進行分析，得出一個最優(yōu)的策略，告訴后面的程序如何取得這個query 語句的結果。

11、表變更管理模塊

表變更管理模塊主要是負責完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等語句的處理。

12、表維護模塊

表的狀態(tài)檢查,錯誤修復,以及優(yōu)化和分析等工作都是表維護模塊需要做的事情.

13、系統(tǒng)狀態(tài)管理模塊

系統(tǒng)狀態(tài)管理模塊負責在客戶端請求系統(tǒng)狀態(tài)的時候,將各種狀態(tài)數據返回給用戶,像DBA 常用的各種show status 命令,show variables 命令等,所得到的結果都是由這個模塊返回的.

14、表管理器

這個模塊從名字上看來很容易和上面的表變更和表維護模塊相混淆，但是其功能與變更及維護模塊卻完全不同。大家知道，每一個MySQL 的表都有一個表的定義文件，也就是*.frm文件。表管理器的工作主要就是維護這些文件，以及一個cache，該cache 中的主要內容是各個表的結構信息。此外它還維護table 級別的鎖管理。

15、日志記錄模塊

日志記錄模塊主要負責整個系統(tǒng)級別的邏輯層的日志的記錄，包括error log，binarylog，slow query log 等。

16、復制模塊

復制模塊又可分為Master 模塊和Slave 模塊兩部分， Master 模塊主要負責在Replication 環(huán)境中讀取Master 端的binary 日志，以及與Slave 端的I/O 線程交互等工作。Slave 模塊比Master 模塊所要做的事情稍多一些，在系統(tǒng)中主要體現在兩個線程上面。一個是負責從Master 請求和接受binary 日志，并寫入本地relay log 中的I/O 線程。另外一個是負責從relay log 中讀取相關日志事件，然后解析成可以在Slave 端正確執(zhí)行并得到和Master 端完全相同的結果的命令并再交給Slave 執(zhí)行的SQL 線程。

17、存儲引擎接口模塊

存儲引擎接口模塊可以說是MySQL 數據庫中最有特色的一點了,目前各種數據庫產品.

MySQL性能調優(yōu)之監(jiān)控方法:

1. set profiling=1 開啟性能監(jiān)控，此命令在某些版本的mysql中無法使用

2. 然后執(zhí)行SQL

3. show profiless，查看系統(tǒng)執(zhí)行SQL的時間

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

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

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

MyISAM讀請求和寫等待隊列中的寫鎖請求的優(yōu)先級規(guī)則主要為以下規(guī)則決定:

1.除了READ_HIGH_PRIORITY 的讀鎖定之外，Pending write-lock queue 中的WRITE 寫鎖定能夠阻塞所有其他的讀鎖定；

2. READ_HIGH_PRIORITY 讀鎖定的請求能夠阻塞所有Pending write-lock queue 中的寫鎖定；

3. 除了WRITE 寫鎖定之外，Pending write-lock queue 中的其他任何寫鎖定都比讀鎖定的優(yōu)先級低。

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 的執(zhí)行；

c) 盡可能減少基于范圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；

d) 盡量控制事務的大小，減少鎖定的資源量和鎖定時間長度；

e) 在業(yè)務環(huán)境允許的情況下，盡量使用較低級別的事務隔離，以減少MySQL 因為實現事務隔離級別所帶來的附加成本；

如何查看MyISAM中表級鎖定信息:

答:show status like '%table_locks%'

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

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

如何查看Innodb中行級鎖定信息:

答:show status like '%Innodb_rows%'

Innodb 的行級鎖定狀態(tài)變量不僅記錄了鎖定等待次數,還記錄了鎖定總時長,每次平均時長,以及最大時長，此外還有一個非累積狀態(tài)量顯示了當前正在等待鎖定的等待數量,對各個狀態(tài)量的說明如下：

● Innodb_row_lock_current_waits：當前正在等待鎖定的數量；

● Innodb_row_lock_time：從系統(tǒng)啟動到現在鎖定總時間長度；

● Innodb_row_lock_time_avg：每次等待所花平均時間；

● Innodb_row_lock_time_max：從系統(tǒng)啟動到現在等待最常的一次所花的時間；

● Innodb_row_lock_waits：系統(tǒng)啟動后到現在總共等待的次數；

mysqlslap是一個mysql官方提供的壓力測試工具,以下是比較重要的參數:

–defaults-file，配置文件存放位置

–concurrency，并發(fā)數

–engines，引擎

–iterations，迭代的實驗次數

–socket，socket文件位置

自動測試:

–auto-generate-sql，自動產生測試SQL

–auto-generate-sql-load-type，測試SQL的類型。類型有mixed，update，write，key，read。

–number-of-queries，執(zhí)行的SQL總數量

–number-int-cols，表內int列的數量

–number-char-cols，表內char列的數量

例如:

1. 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 
2. Benchmark 
3. Running for engine myisam 
4. Average number of seconds to run all queries: 0.016 seconds 
5. Minimum number of seconds to run all queries: 0.016 seconds 
6. Maximum number of seconds to run all queries: 0.016 seconds 
7. Number of clients running queries: 50 
8. Average number of queries per client: 4 
9. Benchmark  --Vevb.com 
10. Running for engine myisam 
11. Average number of seconds to run all queries: 0.265 seconds 
12. Minimum number of seconds to run all queries: 0.265 seconds 
13. Maximum number of seconds to run all queries: 0.265 seconds 
14. Number of clients running queries: 100 
15. Average number of queries per client: 2 

指定數據庫的測試:

–create-schema,指定數據庫名稱.