mysql的監控及最佳化

標籤：buffer   模組   .sql   介紹   mysql   top   type   rtu   失敗   

---恢複內容開始---

1.資料庫的用途：寫資料和讀資料

2.查詢語句的生命週期：

(1)mysql伺服器監聽3306連接埠(2)驗證訪問使用者(3)建立mysql線程

(4)檢查記憶體(Qcache)

(5)解析SQL(6)產生查詢計劃(7)開啟表

(8)檢查記憶體(Buffer Pool)

(9)到磁碟取資料(10)寫入記憶體(11)返回資料給用戶端(12)關閉表(13)關閉線程(14)關閉串連

3.mysql構架

總的來說，MySQL Server可以看成是二層架構，第一層我們通常叫做 SQL Layer，在 MySQL 資料庫系統處理底層資料之前的所有工作都是在這一層完成的，包括許可權判斷，sql 解析，執行計畫最佳化，query cache 的處理等等；第二層就是儲存引擎層，我們通常叫做 Storage Engine Layer，也就是底層資料存取操作實現部分，由多種儲存引擎共同組成。

由mysql連接器(mysql用戶端、各種程式設計語言介面等)、mysq串連池、mysql查詢最佳化工具、儲存引擎層(InnoDB等)、檔案系統(file&logs)和mysql伺服器組成

(1)mysql串連數：預設最大串連數是100，串連數少的話，在大並發下串連數會不夠用，會有很多線程在等待其他串連釋放，就可能會導致資料庫連接逾時或者回應時間過長，所以需要調整最大串連數。

(2)設定mysql的最大串連數

在linux中的最大串連數的設定檔/etc/my.cnf中或者cd/opt/lappp/etc/my.cnf中修改或者增加[mysqld]下面內容

最大串連數：max_connections=1000;

逾時時間：wait_timeout=10

查看當前有多少個串連：show status like ‘%Threads_connected%‘;或者show processlist;

4.緩衝的兩個維度

(1)緩衝1(mysql層)：查詢快取Query Cache

查詢sql的生命週期到達檢查記憶體環節的時候，當查詢命中緩衝，mysql會立刻返回結果，跳過解析、最佳化和執行階段。查詢快取會跟蹤系統中的每張表，如果這些表發生變化，那麼和這張表相關的所有查詢快取全部失效。

在查詢快取的時候，mysql不會對sql進行任何處理，它精確的使用用戶端傳來的查詢，只要字元大小寫，或者注釋有一點點的不同，查詢快取就認為是不同的，任何一個包含不確定的函數，比如：now(),current_date()的查詢不會被緩衝。

開啟查詢快取對於讀寫都增加了額外的開銷，對於讀在查詢開始前需要先檢查緩衝；對於寫，在寫入後需要更新緩衝(失效)。

(1.1)配置查詢快取：linux下為my.cnf,windows下為my.ini

是否開啟查詢快取：query_cache_type=on/off

分配給查詢快取的總記憶體，一般建議不超過256M：query_cache_size=200M

限制儲存的最大結果，如果查詢結果超出，就不會緩衝：query_cache_limit=1M

查詢快取狀態：show variables like ‘%query_cache%‘;

監控qcache當前使用方式：show status like ‘%Qcache%‘;查詢出來的第一項如果數值過大，可以使用flush query cache;語句來清理查詢快取片段，提高記憶體使用量效能。

緩衝的命中率=Qcache hits/(Qcache_hits+Qcache_inserts);

(2)緩衝2(儲存引擎層)InnoDB_Buffer_Pool

 buffer pool是innodb儲存引擎帶的一個緩衝池，如果記憶體中存在的話，直接返回，提交查詢回應時間

innodb buffer pool 和qcache的區別：qcache緩衝的是sql語句和對應該語句的結果集，buffer pool中緩衝的是表中的資料，如果表中資料有變化，那麼qcache中是查詢不到的，因為表更改後，在查詢改sql，在qcache中失效；bufferpool不會，因為它存的是表中的資料；bufferpool設定的越大越好，一般設定為伺服器實體記憶體的70%

(2.1)配置

bp大小：innodb_buffer_pool_size=20M

如果開啟改參數，停止mysql服務時，innodb將innodb緩衝池中的熱資料儲存到本地硬碟:innodb_buffer_pool_dump_now=ON,

如果開啟，啟動mysql服務時，mysql將本地熱資料載入到innodb緩衝池中：innodb_buffer_pool_load_at_startup=OFF,

查看ibp的狀態：show variables like ‘%innodb_buffer_pool%‘;

監控innodb_buffer_pool的使用方式：show status like ‘%innodb_buffer_pool%‘;

主要注意innodb_buffer_pool_read_requests和innodb_buffer_pool_reads,通過這兩個獲得bp的命中率

5.mysql慢查詢日誌：

(1)mysqlslowdump,用來協助資料庫管理員解決可能存在的效能問題

(2)設定檔：在my.cnf中增加：log-slow-queries=/opt/data/slowquery.log(指定記錄檔的存放位置，可以為空白，系統會給一個預設檔案host_name-slow.log)

記錄超過的時間：long_query_time=2,預設為10s

(3)執行命令：set global slow query log=on;

設定記錄查詢超過多長時間sql：set global long query time=1;

日誌路徑：set global slow query log file=‘%/opt/data/slow_query.log%‘;

查詢慢查詢狀態：show variables like ‘%query%‘;

(4)mysqldumpslow命令介紹：mysqldumpslow -s t -t 20 -g ‘select‘ 路徑

-s:按照何種方式排序，c\t\l\t分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，前面加上a表示相應的倒敘，-t:是top n的意思，返回前面多少條資料；-g:後邊可以寫一個正則匹配模式，大小寫不敏感。

6.mysql profile：分析sql執行帶來的開銷是最佳化sql的重要手段，諸如IO,環境切換，cpu,memory等

(1)配置

開啟profile：set @@profiling=1;

查看profile是否開啟：select @@profiling;1為開啟，0為關閉，開啟後只對當前的session有效

查看被記錄的sql:show profiles;或者show profiles for query n:查看指定的sql

show profile cpu/block io/memory for query 1;

7.sql最佳化目標

(1)減少IO次數

(2)降低cpu計算：order by,group by,distinct,最大數、最小數等，都是消耗cpu的大戶

(3)最佳化方法：改變sql的執行計畫，sql最佳化的基本原則：

(3.1)盡量少join

(3.2)盡量少排序：減少排序的方法：通過利用索引來排序、減少參與排序的記錄條數、非必要不對資料進行排序

(3.3)盡量避免select *

(3.4)盡量用join代替子查詢

(3.5)盡量用union all代替union

(3.6)禁用外鍵

(3.7)避免大sql、保持事物的簡短精悍、避免大批次更新、避免類型轉換、避免取過量資料，建議使用limit、避免sql中進行數學運算、Function Compute、邏輯判斷等、避免or(同一欄位推薦in不同欄位推薦union)

總結：1.避免磁碟io,盡量讓查詢在記憶體中完成2.通過sql和索引的調整，讓mysql用跟高效率的方式查詢

4.索引設計原則

下面這兩種方式都是添加普通索引

 create index ‘my_index‘ on bkl(name);
 alter table blk add index my_index2 (sex);
 刪除索引:
 alter table blk drop index my_index2;

(1)最適合索引的列是出現在where子句中的列，或者串連子句中指定的列

(2)使用唯一索引

(3)使用短索引

(4)利用最左首碼

(5)不要過度索引

 

 

 

 

 

 

 

 

 

 

---恢複內容結束---

1.資料庫的用途：寫資料和讀資料

2.查詢語句的生命週期：

(1)mysql伺服器監聽3306連接埠(2)驗證訪問使用者(3)建立mysql線程

(4)檢查記憶體(Qcache)

(5)解析SQL(6)產生查詢計劃(7)開啟表

(8)檢查記憶體(Buffer Pool)

(9)到磁碟取資料(10)寫入記憶體(11)返回資料給用戶端(12)關閉表(13)關閉線程(14)關閉串連

3.mysql構架

總的來說，MySQL Server可以看成是二層架構，第一層我們通常叫做 SQL Layer，在 MySQL 資料庫系統處理底層資料之前的所有工作都是在這一層完成的，包括許可權判斷，sql 解析，執行計畫最佳化，query cache 的處理等等；第二層就是儲存引擎層，我們通常叫做 Storage Engine Layer，也就是底層資料存取操作實現部分，由多種儲存引擎共同組成。

由mysql連接器(mysql用戶端、各種程式設計語言介面等)、mysq串連池、mysql查詢最佳化工具、儲存引擎層(InnoDB等)、檔案系統(file&logs)和mysql伺服器組成

(1)mysql串連數：預設最大串連數是100，串連數少的話，在大並發下串連數會不夠用，會有很多線程在等待其他串連釋放，就可能會導致資料庫連接逾時或者回應時間過長，所以需要調整最大串連數。

(2)設定mysql的最大串連數

在linux中的最大串連數的設定檔/etc/my.cnf中或者cd/opt/lappp/etc/my.cnf中修改或者增加[mysqld]下面內容

最大串連數：max_connections=1000;

逾時時間：wait_timeout=10

查看當前有多少個串連：show status like ‘%Threads_connected%‘;或者show processlist;

4.緩衝的兩個維度

(1)緩衝1(mysql層)：查詢快取Query Cache

查詢sql的生命週期到達檢查記憶體環節的時候，當查詢命中緩衝，mysql會立刻返回結果，跳過解析、最佳化和執行階段。查詢快取會跟蹤系統中的每張表，如果這些表發生變化，那麼和這張表相關的所有查詢快取全部失效。

在查詢快取的時候，mysql不會對sql進行任何處理，它精確的使用用戶端傳來的查詢，只要字元大小寫，或者注釋有一點點的不同，查詢快取就認為是不同的，任何一個包含不確定的函數，比如：now(),current_date()的查詢不會被緩衝。

開啟查詢快取對於讀寫都增加了額外的開銷，對於讀在查詢開始前需要先檢查緩衝；對於寫，在寫入後需要更新緩衝(失效)。

(1.1)配置查詢快取：linux下為my.cnf,windows下為my.ini

是否開啟查詢快取：query_cache_type=on/off

分配給查詢快取的總記憶體，一般建議不超過256M：query_cache_size=200M

限制儲存的最大結果，如果查詢結果超出，就不會緩衝：query_cache_limit=1M

查詢快取狀態：show variables like ‘%query_cache%‘;

監控qcache當前使用方式：show status like ‘%Qcache%‘;查詢出來的第一項如果數值過大，可以使用flush query cache;語句來清理查詢快取片段，提高記憶體使用量效能。

緩衝的命中率=Qcache hits/(Qcache_hits+Qcache_inserts);

(2)緩衝2(儲存引擎層)InnoDB_Buffer_Pool

 buffer pool是innodb儲存引擎帶的一個緩衝池，如果記憶體中存在的話，直接返回，提交查詢回應時間

innodb buffer pool 和qcache的區別：qcache緩衝的是sql語句和對應該語句的結果集，buffer pool中緩衝的是表中的資料，如果表中資料有變化，那麼qcache中是查詢不到的，因為表更改後，在查詢改sql，在qcache中失效；bufferpool不會，因為它存的是表中的資料；bufferpool設定的越大越好，一般設定為伺服器實體記憶體的70%

(2.1)配置

bp大小：innodb_buffer_pool_size=20M

如果開啟改參數，停止mysql服務時，innodb將innodb緩衝池中的熱資料儲存到本地硬碟:innodb_buffer_pool_dump_now=ON,

如果開啟，啟動mysql服務時，mysql將本地熱資料載入到innodb緩衝池中：innodb_buffer_pool_load_at_startup=OFF,

查看ibp的狀態：show variables like ‘%innodb_buffer_pool%‘;

監控innodb_buffer_pool的使用方式：show status like ‘%innodb_buffer_pool%‘;

主要注意innodb_buffer_pool_read_requests和innodb_buffer_pool_reads,通過這兩個獲得bp的命中率

5.mysql慢查詢日誌：

(1)mysqlslowdump,用來協助資料庫管理員解決可能存在的效能問題

(2)設定檔：在my.cnf中增加：log-slow-queries=/opt/data/slowquery.log(指定記錄檔的存放位置，可以為空白，系統會給一個預設檔案host_name-slow.log)

記錄超過的時間：long_query_time=2,預設為10s

(3)執行命令：set global slow query log=on;

設定記錄查詢超過多長時間sql：set global long query time=1;

日誌路徑：set global slow query log file=‘%/opt/data/slow_query.log%‘;

查詢慢查詢狀態：show variables like ‘%query%‘;

(4)mysqldumpslow命令介紹：mysqldumpslow -s t -t 20 -g ‘select‘ 路徑

-s:按照何種方式排序，c\t\l\t分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，前面加上a表示相應的倒敘，-t:是top n的意思，返回前面多少條資料；-g:後邊可以寫一個正則匹配模式，大小寫不敏感。

6.mysql profile：分析sql執行帶來的開銷是最佳化sql的重要手段，諸如IO,環境切換，cpu,memory等

(1)配置

開啟profile：set @@profiling=1;

查看profile是否開啟：select @@profiling;1為開啟，0為關閉，開啟後只對當前的session有效

查看被記錄的sql:show profiles;或者show profiles for query n:查看指定的sql

show profile cpu/block io/memory for query 1;

7.sql最佳化目標

(1)減少IO次數

(2)降低cpu計算：order by,group by,distinct,最大數、最小數等，都是消耗cpu的大戶

(3)最佳化方法：改變sql的執行計畫，sql最佳化的基本原則：

(3.1)盡量少join

(3.2)盡量少排序：減少排序的方法：通過利用索引來排序、減少參與排序的記錄條數、非必要不對資料進行排序

(3.3)盡量避免select *

(3.4)盡量用join代替子查詢

(3.5)盡量用union all代替union

(3.6)禁用外鍵

(3.7)避免大sql、保持事物的簡短精悍、避免大批次更新、避免類型轉換、避免取過量資料，建議使用limit、避免sql中進行數學運算、Function Compute、邏輯判斷等、避免or(同一欄位推薦in不同欄位推薦union)

總結：1.避免磁碟io,盡量讓查詢在記憶體中完成2.通過sql和索引的調整，讓mysql用跟高效率的方式查詢

4.索引設計原則

下面這兩種方式都是添加普通索引

 create index ‘my_index‘ on bkl(name);
 alter table blk add index my_index2 (sex);
 刪除索引:
 alter table blk drop index my_index2;

(1)最適合索引的列是出現在where子句中的列，或者串連子句中指定的列

(2)使用唯一索引

(3)使用短索引

(4)利用最左首碼

(5)不要過度索引

 5.事務：把多件事情當做一件事情來處理，例如：轉賬，我把錢轉出去，別人收到我轉出去的錢，如果我在轉出的時候斷網了，那麼要麼都成功，要麼都失敗

6.什麼是鎖

鎖是資料庫系統區別於檔案系統的一個關鍵特性。鎖機制用於管理對共用資源的並發訪問，一方面最大程度的提供並發訪問，量一方面要確保每個使用者能以一致性得方式讀取和修改資料。

Innodb儲存引擎會在行層級上對錶資料加鎖。

(1)關閉自動認可

查看自動認可是否開啟：select @@autocommit;0為關閉，1為開啟

設定自動認可關閉：set @@autocommit=0;只對當前session有效

修改設定檔my.cnf中[mysqld]模組下加入autocommit =0;永久生效

(2)特點：Innodb是通過對索引上的索引項目加鎖來實現行鎖。如果不是索引的話，實現的是表鎖

(3)操作，分別開啟兩個終端，做update操作不同的行，不提交的時候，在第二個終端出現被鎖狀態，實現表鎖;如果是索引，分別開啟兩個終端，做update操作相同的行，不提交的時候，在第二個終端出現被鎖狀態，實現行鎖。

(4)查看死結：show engine innodb status\G;

7.explain：該命令是查看查詢最佳化工具如果決定執行查詢的主要方法，這個功能有局限性，只是一個近似結果，有時它是一個很好的近似，有時可能相差甚遠。

(1)explain select * from students where id =2;

(2)all(全表掃描)<index(索引)<range(範圍掃描)<ref(非唯一性索引掃描)<eq_ref(主鍵或者唯一索引)<const,system(？？)<null(帶函數的)

 

mysql的監控及最佳化