一、關(guān)于一個SQL的簡單的工作過程

1、工作前提描述

　　1、啟動MySQL，在內(nèi)存中分配一個大空間innodb_buffer_pool(其中l(wèi)og_buffer)

　　2、多用戶線程連接MySQL，從內(nèi)存分配用戶工作空間(其中排序空間)

　　3、磁盤上有數(shù)據(jù)庫文件、ib_logfile、tmp目錄、undo

2、SQL的簡易流程

　　1、DQL操作

　　　　1、首先進(jìn)行內(nèi)存讀

　　　　2、如果buffer pool中沒有所需數(shù)據(jù)，就進(jìn)行物理讀

　　　　3、物理讀數(shù)據(jù)讀入buffer pool，再返回給用戶工作空間

　　2、DML操作(例update)

　　　　1、內(nèi)存讀，然后進(jìn)行物理讀，讀取所需修改的數(shù)據(jù)行

　　　　2、從磁盤調(diào)入undo頁到buffer pool中

　　　　3、修改前的數(shù)據(jù)存入undo頁里，產(chǎn)生redo

　　　　4、修改數(shù)據(jù)行(buffer pool中數(shù)據(jù)頁成臟頁)，產(chǎn)生redo

　　　　5、生成的redo先是存于用戶工作空間，擇機(jī)拷入log_buffer中

　　　　6、log線程不斷的將log_buffer中的記錄寫入redo logfile中

　　　　7、修改完所有數(shù)據(jù)行，提交事務(wù)，刻意再觸發(fā)一下log線程

　　　　8、待log_buffer中的相關(guān)信息都寫完，響應(yīng)事務(wù)提交成功

　　至此，日志寫入磁盤，內(nèi)存臟塊還在buffer pool中(后臺周期寫入磁盤，釋放buffer pool空間)。

 

二、影響SQL執(zhí)行性能的因素，及具體看方式

1、大量物理讀

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show global status like 'i%read%';| Innodb_buffer_pool_reads              | 647      || Innodb_data_read                      | 48402944 || Innodb_data_reads                     | 2996     || Innodb_pages_read                     | 2949     || Innodb_rows_read                      | 1002172  |

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　1、Innodb_buffer_pool_reads：物理讀次數(shù)

　　2、Innodb_data_read：物理讀數(shù)據(jù)字節(jié)量

　　3、Innodb_data_reads：物理讀IO請求次數(shù)

　　4、Innodb_pages_read：物理讀數(shù)據(jù)頁數(shù)

　　5、Innodb_rows_read：物理讀數(shù)據(jù)行數(shù)

2、Log寫性能

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show engine innodb status \G---LOG---Log sequence number 144064129　　//已經(jīng)生成的日志量(累計值)/單位：字節(jié)Log flushed up to   144064129　　//已經(jīng)寫入的日志量(累計值)Pages flushed up to 144064129　　//已經(jīng)寫入的臟頁量(累計值)Last checkpoint at  144064120　　//檢查點(diǎn)0 pending log flushes, 0 pending chkp writes92 log i/o's done, 0.00 log i/o's/second

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　關(guān)于redo log的寫入：

　　1、Innodb_os_log_written：日志刷盤的字節(jié)數(shù)，如果在commit不怎么變化的情況下，這個值出現(xiàn)暴增，說明系統(tǒng)出現(xiàn)大事務(wù)了（處理：kill線程，必要情況kill掉mysql進(jìn)程）；

　　2、Innodb_log_writes：日志寫的次數(shù)。

3、磁盤排序

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show status like 'Sort_merge_passes';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     |+-------------------+-------+1 row in set (0.00 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　用戶所需數(shù)據(jù)，如果沒有內(nèi)存buffer pool中，就發(fā)生物理讀；　　

　　如果需要過濾掉很多數(shù)據(jù)，就會影響物理讀和內(nèi)存讀，因?yàn)榉祷睾芏嗟臄?shù)據(jù)（物理讀），在內(nèi)存中需要過濾掉很多數(shù)據(jù)（內(nèi)存讀）；　　

　　如果涉及到group/order by，會在用戶工作空間完成排序等，如果結(jié)果集過大，用戶空間過小，進(jìn)行磁盤排序，Sort_merge_passes>0 ，這就很影響數(shù)據(jù)庫性能了。

 

三、MySQL線程及其工作

　　MySQL的工作機(jī)制是單進(jìn)程多線程：IO線程=一個log線程+四個read線程+四個write線程

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show engine innodb status \G--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

1、讀操作：innodb_read_io_threads

　　1、發(fā)起者：用戶線程發(fā)起讀請求

　　2、完成者：讀線程執(zhí)行請求隊列中的讀請求操作

　　3、如何調(diào)整讀線程的數(shù)量

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show variables like 'innodb_read_io_threads';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| innodb_read_io_threads | 4     |+------------------------+-------+1 row in set (0.01 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　　　默認(rèn)是開啟4個讀線程，靜態(tài)參數(shù)，修改至配置文件中

　　4、如何確定是否需要增加讀線程的數(shù)量

　　　　查看線程的狀態(tài)：I/O thread 2 state: waiting for i/o request (read thread)

2、寫操作：innodb_write_io_threads

　　1、發(fā)起者：page_cleaner線程發(fā)起

　　2、完成者：寫線程執(zhí)行請求隊列中的寫請求操作

　　3、如何調(diào)整寫線程的數(shù)量

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show variables like 'innodb_write_io_threads';+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| innodb_write_io_threads | 4     |+-------------------------+-------+1 row in set (0.01 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　　　默認(rèn)是開啟4個寫線程，靜態(tài)參數(shù)，修改至配置文件中

　　4、如何確定是否需要增加寫線程的數(shù)量

　　　　查看線程的狀態(tài)：I/O thread 6 state: waiting for i/o request (write thread)

關(guān)于innodb_purge_threads：page cleaner 線程
作用：

　　1、負(fù)責(zé)對 undo 數(shù)據(jù)頁的清空

　　2、數(shù)據(jù)頁中 delete 標(biāo)志行的清除

　　3、清理 innodb buffer pool，負(fù)責(zé)把內(nèi)存中的臟頁發(fā)起寫請求，write 線程負(fù)載把臟頁刷新到磁盤上。

3、日志線程

3.1、只有一個日志線程

　　1、是否繁忙

　　　　I/O thread 1 state: waiting for i/o request (log thread)：閑

　　2、日志寫性能

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show global status like 'Innodb_log_waits';+------------------+-------+| Variable_name    | Value |+------------------+-------+| Innodb_log_waits | 0     |+------------------+-------+1 row in set (0.00 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　　　如果log buffer太小，就很容易滿，導(dǎo)致無法寫入，產(chǎn)生日志等待。

　　3、日志寫壓力

　　　　1、每秒吞吐量

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show global status like 'Innodb_os_log_written';　　#redo log寫的字節(jié)數(shù)+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Innodb_os_log_written | 57856 |　　+-----------------------+-------+1 row in set (0.01 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　　　2、每秒寫入次數(shù)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show global status like 'Innodb_log_writes';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Innodb_log_writes | 59    |+-------------------+-------+1 row in set (0.01 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

3.2、對于日志監(jiān)控來說，三個經(jīng)典參數(shù)

　　1、Innodb_log_waits　　#redo寫入的等待次數(shù)

　　2、Innodb_log_writes　　#redo寫入的次數(shù)

　　3、Innodb_os_log_written　　#寫入redo logfile中的字節(jié)量

3.3、日志寫入異常判斷

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show engine innodb status \G
Pending flushes (fsync) log: 0;
mysql> show global status like 'Innodb_os_log_pending_fsyncs';+------------------------------+-------+| Variable_name                | Value |+------------------------------+-------+| Innodb_os_log_pending_fsyncs | 0     |+------------------------------+-------+1 row in set (0.00 sec)

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　1、fsync：繞過文件系統(tǒng)緩存，直接將內(nèi)存中的數(shù)據(jù)寫入存儲中，實(shí)現(xiàn)數(shù)據(jù)真正寫入可靠的介質(zhì)磁盤里。(對于redo log來說，通過fsync方式寫入磁盤才是可靠的保證，因?yàn)閷懭胛募到y(tǒng)緩存的提交成功響應(yīng)并不是真正的將redo寫入磁盤的logfile中)(sync:同步)

　　2、pending：掛起(寫不動)，redo寫入存儲cache過程中，某種原因io繁忙，cache被占滿，超時響應(yīng)，就會被掛起；

　　3、>0就說明系統(tǒng)IO出現(xiàn)問題，=0說明is OK。

 

四、log buffer調(diào)整依據(jù)

　　log buffer：日志緩存，一般都很小，調(diào)整100M足夠使用

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

mysql> show variables like "%log_buffer%";+------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| innodb_log_buffer_size | 16777216 |+------------------------+----------+

seo優(yōu)化培訓(xùn),網(wǎng)絡(luò)推廣培訓(xùn),網(wǎng)絡(luò)營銷培訓(xùn),SEM培訓(xùn),網(wǎng)絡(luò)優(yōu)化,在線營銷培訓(xùn)

　　5.7默認(rèn)是16M，5.6默認(rèn)是8M

1、文件中(log file)：每次寫的時候全局都寫，不會挑著撿著寫

　　1、日志寫線程每一秒redo日志緩沖刷新到重做日志文件

　　2、每個事務(wù)提交時會將重做日志緩沖刷新到重做日志文件

　　3、每當(dāng)重做日志緩沖池剩余空間小于1/2時，重做日志緩沖刷新到重做日志文件。

2、在企業(yè)中往往設(shè)置50-100M，最多設(shè)置為2、300M，調(diào)整依據(jù)：

　　1、內(nèi)存空間足夠大

　　2、日志產(chǎn)生量大，系統(tǒng)io阻塞了，系統(tǒng)的io占用的是一個帶寬，log_writes線程被阻塞，log buffer滿了，數(shù)據(jù)庫會hang住。

　　3、Innodb_log_waits（狀態(tài)值）

　　　　The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.

http://www.cnblogs.com/geaozhang/p/7214257.html