Mysql監控及最佳化

標籤：提交   執行計畫   查詢條件   mysql   mysql伺服器   start   load   lamp   配置   

一、Mysql串連數

1、配置Mysql串連數：

　　vim　/etc/my.cnf　[mysqld]下面修改

　　max_connections=1000　　不寫預設為100。

　　wait_timeout=60　　設定逾時時間

2、查看當前串連數：

　　show status like ‘%Threads_connected%‘;

　　

　　show processlist;

　　

 二、Mysql緩衝

1、開啟緩衝：

　　vim　/etc/my.cnf　mysqld下面添加或修改

　　query_cache_type=on #開啟緩衝

　　query_cache_size=10M #緩衝總大小

　　query_cache_limit=1M #查詢結果超過設定值,就不會緩衝

　　需重啟mysql服務生效。

2、查看緩衝狀態：

　　SHOW VARIABLES LIKE ‘%query_cache%‘;

　　

3、開啟profile：

　　set @@profiling=1;　　設定profile開啟

　　select @@profiling;　　查看profile是否開啟

　　show profiles;　　　　  查看所有的profile

　　

　　show profile for query  9;　　查看指定的sql語句消耗的時間

　　

　　

　　可以看出：同樣的sql語句，第9條是首次查詢消耗時間，耗時比較長，第10條是從緩衝查詢消耗時間，耗時明顯比較短。

　　註：1、select語句必須完全相同才會從走緩衝，例如：大小寫不一樣，雖然查詢結果一樣，但是不會走緩衝。

　　　　2、任何一個包含不確定的函數(比如：now(),current_date())的查詢不會被緩衝。

4、queryCache使用狀態：

　　SHOW STATUS LIKE ‘Qcache%‘;

　　

　　Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；
　　Qcache_free_blocks 　　 Query Cache中目前還有多少剩餘的blocks。如果該值顯示較大，則說明Query Cache 中的記憶體片段較多了，可能需要尋找合適的機會進行整理。如果這個值非常大，可以使用FLUSH QUERY CACHE;語句來清理查詢快取片段以提高記憶體使用量效能。該語句不從緩衝中移出任何查詢。

5、查詢語句生命週期：

　　1.Mysql伺服器監聽3306連接埠

　　2.驗證訪問使用者

　　3.建立Mysql線程

　　4.檢查記憶體（qcache）

　　5.解析SQL

　　6.產生查詢計劃

　　7.開啟表

　　8.檢查記憶體（Buffer Pool）

　　9.到磁碟讀取資料

　　10.寫入記憶體

　　11.返回資料給用戶端

　　12.關閉表

　　13.關閉線程

　　14.關閉串連

三、innodb 儲存引擎

1、開啟innodb_buffer_pool

　　vim　/etc/my.cnf　mysqld 下面添加或修改

　　innodb_buffer_pool_size=20M 　　#設定bufferpool大小

　　innodb_buffer_pool_dump_now=on 　　　　#預設為關閉OFF。如果開啟該參數，停止MySQL服務時，InnoDB將InnoDB緩衝池中的熱資料儲存到本地硬碟。

　　innodb_buffer_pool_load_at_startup = on   #預設為關閉OFF。如果開啟該參數，啟動MySQL服務時，MySQL將本地熱資料載入到InnoDB緩衝池中。

2、查看Innodb_buffer_pool狀態

　　SHOW VARIABLES LIKE ‘%innodb_buffer_pool%‘;

　　

3、查詢Innodb_buffer_pool當前使用方式：

　　SHOW STATUS LIKE ‘%Innodb_buffer_pool%‘;

　　

4、 自動認可

　　set @@autocommit=0;　　#關閉自動認可，僅對目前使用者有效

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

　　select @@autocommit;　　#查看自動認可是否開啟

　　commit;　　#提交執行語句

5、鎖：

　　show processlist;　　#查看當前鎖定sql

　　

　　註：如果修改的資料條件是索引列，則是行級鎖，否則就是表級鎖。

四、慢查詢

1、查詢慢查詢日誌是否開啟

　　SHOW VARIABLES LIKE ‘%query%‘;

　　

2、慢查詢日誌設定(執行命令)

　　set global slow_query_log=on;　　#開啟慢查詢日誌

　　set global long_query_time=1;　　#設定記錄查詢超過多少秒的Sql存入慢查詢

　　set global slow_query_log_file=‘/opt/data/slow_query.log‘;　　#設定慢查詢日誌路徑，此路徑需要有寫入許可權

3、解析慢查詢日誌

　　-s：是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；　　-t：是top n的意思，即為返回前面多少條的資料；　　-g：後邊可以寫一個正則匹配模式，大小寫不敏感的； 　　如：查詢按照Sql查詢時間最長的前10條sql

　　mysqldumpslow -s t -t 10 -g ‘select‘  /opt/lampp/var/mysql/xiaoxitest-slow.log

　　

4、使用EXPLAIN/DESC 查看Sql效率

　　EXPLAIN update user set age=11 where id=1;

　　DESC update user set age=11 where id=1;

　　

　　type列：依次從最差到最優

　　all<index<range<ref<eq_ref<const,system<null

　　ALL　　就是全表掃描，通常意味著MySQL必須掃描整張表，從頭到尾，去找需要的行。對innodb表就是按主鍵順序。　　Index　　聯結類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）。　　Range　　範圍掃描就是一個有限制的索引掃描，它開始於索引裡面的一個點，返回匹配整個範圍的行。這比全索引掃描好一些，因為它用不著遍曆全部索引。顯而易見的是between或在where字句帶有“>”或“<”的查詢或者in。　　Ref　　非唯一性索引掃描，返回匹配某個單獨值的所有行。　　Eq_ref　　使用這種索引尋找，MySQL知道最多隻返回一條合格記錄。這種訪問方式在MySQL使用主鍵或唯一索引時可以看到。MySQL對於這類訪問類型最佳化的非常好。　　Const，system　　當MySQL能對查詢的某部分進行最佳化並將其轉化成一個常量時，它就會使用這些類型訪問。　　NULL　　這種訪問方式意味著MySQL能在最佳化階段分解查詢語句，在執行階段甚至用不著再訪問表或者索引。 五、Mysql最佳化1、讀寫分離　　主：只負責寫資料　　從：只負責讀資料2、分布資料　　MySQL通常不會對頻寬造成很大的壓力。因此可以在不同的地理位置來分布資料，實現跨機房跨地區的資料分布。3、負載平衡　　通過MySQL複製可以將讀操作分布到多個伺服器上，實現對讀密集型應用的最佳化。4、減少IO次數　　大部分資料庫操作中90%時間都是IO操作所佔用的，減少IO次數是Sql最佳化種第一優先考慮。也是收效最明顯最佳化手段。5、減少CPU計算　　SQL最佳化中需要考慮的就是 CPU 運算量的最佳化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理記憶體中的資料比較運算)。當我們的 IO 最佳化做到一定階段之後，降低 CPU 計算也就成為了我們 SQL 最佳化的重要目標。6、改變 SQL 執行計畫　　明確了最佳化目標之後，我們需要確定達到我們目標的方法。對於 SQL 陳述式來說，達到上述2個目標的方法其實只有一個，那就是改變 SQL 的執行計畫，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的資料，以達到 “減少 IO 次數” 和 “降低 CPU 計算” 的目標。7、為經常使用的查詢條件建立索引

Mysql監控及最佳化