標籤：關閉 dpi isl msf 參與 複製 eth logs 大小
轉寄原地址：http://www.cnblogs.com/hanzhao1987/p/6100096.html
mysql 優點：
簡單易用，成本低，易擴充，複製功能領先
mysql的生命週期：
Mysql伺服器監聽3306連接埠>驗證訪問使用者>建立mysql線程>檢查記憶體（Qcache）>解析sql>產生查詢計劃>開啟表>檢查記憶體(Buffer PooL)>到磁碟取資料>寫入記憶體>返回資料給用戶端>關閉表>關閉線程>關閉串連
Mysql架構：
mysql連接器：用戶端
mysql伺服器：串連池；查詢最佳化工具（緩衝）；儲存引擎層（索引結構，緩衝）；檔案系統（file,log）
----------------------------------------------------------------------------------------------
Mysql串連數：
串連數少的情況下，在大並發下，會導致等待串連釋放，造成資料庫連接逾時或者相應時間過長
mysql 的設定檔中 max_connections = 1000 最大連結數，不寫預設是100
wait_timeout = 10 逾時時間
查看 當前 有多少個串連
show status like ‘%Threads_connected%‘
show processlist
-----------------------------------------------------------------------------------------
mysql緩衝
1.mysql層：查詢快取 Query Cache：儲存select的完整結果，當有同樣的查詢，則跳過解析，最佳化和執行的階段，直接返回結果，當這條緩衝select語句相關的表有變化時，此緩衝失效
開啟可以提高效能，但是對於讀寫增加了開銷，讀的時候要先檢查緩衝，對於寫，寫入之後要更新緩衝
但是一般這種開銷比較小，但是也要根據業務特徵權衡
win(my.ini) linux(my.cnf)
SHOW VARIABLES LIKE ‘%query_cache%‘ :
query_cache_type=on // on 開啟off 關閉
query_cache_size=200M 一般建議不超過256M
query_cache_limit=1M 限制儲存結果的大小，如果查出來的結果比這個大，就不會被緩衝
查詢quache當前使用方式：
SHOW STATUS LIKE ‘%Qcache%‘
2.儲存引擎層：InnoDB_Buffer_Pool
他緩衝的是整個表中的資料，越大越好，一般設定為伺服器實體記憶體的70%
參數：
查看 InnoDB_Buffer_Pool ：
SHOW VARIABLES LIKE ‘%innoDB_buffer_pool%‘:
InnoDB_buffer_pool_size :大小
查詢 InnoDB_buffer_pool 當前使用方式：
SHOW STATUS LIKE ‘%InnoDB_buffer_pool %‘
主要關注兩個參數：
InnoDB_buffer_pool_read_requests 總共查詢bp的次數
InnoDB_buffer_pool_reads 從物理硬碟中擷取到資料的次數
通過這兩個參數我們可以知道bp的命中率
------------------------------------------------------------------------------------------------------------------
mysql慢查詢日誌
可以用 mysqlslowdump 來分析慢查詢日誌
配置 mysql 慢查詢
Linux:
在mysql設定檔my.cnf中增加：log-slow-queries=/opt/data/slowquery.log (指定記錄檔存放位置，可以為空白，系統會給一個預設的檔案host_name-slow.log)long_query_time=2 (記錄超過的時間，預設為10s)log-queries-not-using-indexes (log下來沒有使用索引的query,可以根據情況決定是否開啟)
Windows:
在my.ini的[mysqld]添加如下語句：log-slow-queries = E:\web\mysql\log\mysqlslowquery.loglong_query_time = 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’;#設定mysql慢查詢日誌路徑，此路徑需要有寫入權限
這種方式不用重啟mysql服務。 -----查詢：SHOW VARIABLES LIKE ‘%query%‘;
使用這個語句可以看到當前mysql慢查詢是否開啟，以及mysql的慢查詢記錄檔在哪。
slow_query_log #是否開啟慢查詢
slow_query_log_file #日誌的存放位置
long_query_time #超過多少秒的查詢就寫入日誌
-----解析mysql慢查詢日誌使用mysqldumpslow命令可以解析mysql慢查詢日誌。
Mysqldumpslow命令參數如下：
-s，是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
-t，是top n的意思，即為返回前面多少條的資料；
-g，後邊可以寫一個正則匹配模式，大小寫不敏感的； 比如說要按照sql執行時間最長的前20條sql
mysqldumpslow -s t -t 20 -g ‘select‘/opt/data/slowquery_2016050921.log
得到按照時間排序的前10條裡面含有左串連的查詢語句。
mysqldumpslow -s t -t 10 -g ‘left join‘/opt/data/slowquery_2016050921.log Mysqldumpslow命令結果
------------------- 針對佔用資源最嚴重的sql進一步的分析Explain
該命令是查看查詢最佳化工具如何決定執行查詢的主要方法，這個功能有局限性，只是一個近似結果，有時它是一個很好的近似，有時可能相差甚遠。但它的輸出是可以擷取的最準確資訊，值得仔細學習。
一個簡單的執行計畫
| 1 |
mysql> explain select * from students where id in (2,3); |
1.explain不會考慮觸發器、預存程序或函數對查詢的影響。
2.explain不會考慮緩衝對查詢結果的影響
3.不會考慮mysql執行查詢所做的特定最佳化
4.是基於統計資訊的估算，並非精確值。
5.mysql5.6之前只支援對select進行explain
select列,它有以下幾種值
simple 它表示簡單的select,沒有union和子查詢.
primary 最外面的select,在有子查詢的語句中，最外面的select查詢就是primary
DERIVED 值表示包含在FROM字句的子查詢中的SELECT，MySQL會遞迴執行並將結果放到一個暫存資料表中。伺服器內部稱其“派生表”，因為該暫存資料表是從子查詢中派生來的Table列：
顯示輸出的行所引用的表名，如果是子查詢的話，表名會顯示derived type列:指MySQL的訪問類型，也就是如何尋找表中的行，下面是最重要的存取方法，依次從最差到最優:all<index<range<ref<eq_ref<const,system<nullALL
就是全表掃描，通常意味著MySQL必須掃描整張表，從頭到尾，去找需要的行。對innodb表就是按主鍵順序。
Index
聯結類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）。
Range
範圍掃描就是一個有限制的索引掃描，它開始於索引裡面的一個點，返回匹配整個範圍的行。這比全索引掃描好一些，因為它用不著遍曆全部索引。顯而易見的是between或在where字句帶有“>”或“<”的查詢或者in。
Ref
非唯一性索引掃描，返回匹配某個單獨值的所有行。
Eq_ref
使用這種索引尋找，MySQL知道最多隻返回一條合格記錄。這種訪問方式在MySQL使用主鍵或唯一索引時可以看到。MySQL對於這類訪問類型最佳化的非常好。
Const，system
當MySQL能對查詢的某部分進行最佳化並將其轉化成一個常量時，它就會使用這些類型訪問。
NULL
這種訪問方式意味著MySQL能在最佳化階段分解查詢語句，在執行階段甚至用不著再訪問表或者索引。
Key_len列：
該列顯示了MySQL在索引裡使用的位元組數。
Ref列：
顯示索引的哪一列被使用了
Rows列
MYSQL 認為必須檢查的用來返回請求資料的行數
Extra
這一列包含的是不適合在其他列顯示的額外資訊。
一些比較重要的如下：
“using index”
此值表示MySQL將使用覆蓋索引，以避免訪問表。
“using where”
表示查詢使用了where條件
“using temporary”
這意味著MySQL在對查詢結果排序時會使用一個暫存資料表。
“using filesort”
這意味著MySQL會對結果使用一個外部的索引排序，而不是按索引順序從表裡讀取行。
--------------------------------------------------------------------------------------------------------
MySQL profile分析SQL執行帶來的開銷是最佳化SQL的重要手段。在MySQL資料庫中，可以通過配置profiling參數來啟用SQL剖析。該參數開啟後，後續執行的SQL語句都將記錄其資源開銷，諸如IO，環境切換，CPU，Memory等等。
開啟 profile：
set @@profiling=1;
查看profile是否開啟
select @@profiling;
1為開啟，0為關閉
開啟後只對當前session有效。
開啟profile後，執行的sql都會被profile記錄
show profiles;可以看到當前已經被記錄的sql
查看profile 結果show profile for query n;#n為show profile中的query_idshow profile可以看到sql執行計畫中每步的執行時間，以及cpu、記憶體、io的消耗
show profile cpu for query 1;#查看cpu消耗
show profile block io for query 1;#查看io消耗
show profile memory for query 1;#查看cpu
也可以一起寫
show profile cpu ,block io for query 1--------------------------------------------------------------------------------------------------------------------------------SQL最佳化方法: 改變 SQL 執行計畫
明確了最佳化目標之後，我們需要確定達到我們目標的方法。對於 SQL 陳述式來說，達到上述2個目標的方法其實只有一個，那就是改變 SQL 的執行計畫，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的資料，以達到 “減少 IO 次數” 和 “降低 CPU 計算” 的目標。
盡量少 join
MySQL 的優勢在於簡單，但這在某些方面其實也是其劣勢。MySQL 最佳化器效率高，但是由於其統計資訊的量有限，最佳化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join，一方面由於其最佳化器受限，再者在 Join 這方面所下的功夫還不夠，所以效能表現離 Oracle 等關係型資料庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些情境下要優於這些資料庫前輩。
盡量少排序
排序操作會消耗較多的 CPU 資源，所以減少排序可以在快取命中率高等 IO 能力足夠的情境下會較大影響 SQL 的回應時間。
對於MySQL來說，減少排序有多種辦法，比如：
通過利用索引來排序的方式進行最佳化
減少參與排序的記錄條數
非必要不對資料進行排序
盡量避免select *
Select * 一般都會造成全表掃描，顯示所有列，select 需要的欄位即可。
盡量用 join 代替子查詢
雖然 Join 效能並不佳，但是和 MySQL 的子查詢比起來還是有非常大的效能優勢。MySQL 的子查詢執行計畫一直存在較大的問題，雖然這個問題已經存在多年，但是到目前已經發布的所有穩定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認這一問題，並且承諾儘快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。
盡量用 union all 代替 union：
union 和 union all 的差異主要是前者需要將兩個(或者多個)結果集合并後再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。所以當我們可以確認不可能出現重複結果集或者不在乎重複結果集的時候，盡量使用 union all 而不是 union。
禁用外鍵
---------------------------------------------- SQL最佳化的基本原則 避免大sql
一個SQL只能在一個cpu上運行
高並發環境中，大SQL容易影響效能問題
可能一個大SQL把資料庫搞死
拆分SQL
保持事物的短小精悍
即開即用，用完即關
無關操作踢出事務，減少資源佔用
保持一致性的前提下，拆分事務
避免大批次更新
避開高峰
白天限制速度
加sleep
避免類型轉換
避免取過量資料，建議使用limit
避免在SQL 語句中進行數學運算、Function Compute、邏輯判斷等操作
避免OR
同一欄位，推薦in
不同欄位，推薦union
優先最佳化高並發的 SQL，而不是執行頻率低某些“大”SQL
從全域出發最佳化，而不是片面調整
儘可能對每一條運行在資料庫中的SQL進行explain。
-------------------------------------------------------------索引的設計原則1.搜尋的索引列，不一定是所要選擇的列，換句話說，最適合索引的列是出現在WHERE 子句中的列，或串連子句中指定的列，而不是出現在SELECT 關鍵字後的挑選清單中的列。
2.使用唯一索引，考慮某列中值的分布。對於惟一值的列，索引的效果最好，而具有多個重複值的列，其索引效果最差。
3.使用短索引。如果對串列進行索引，應該指定一個前置長度，只要有可能就應該這樣做。例如，如果有一個CHAR(200) 列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。對前10 個或20 個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。
4.利用最左首碼。在建立一個n 列的索引時，實際是建立了MySQL可利用的n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左首碼。
5.不要過度索引。不要以為索引“越多越好”，什麼東西都用索引是錯的。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的效能，這一點我們前面已經介紹 過。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表 的修改速度。此外，MySQL在產生一個執行計畫時，要考慮各個索引，這也要費時間。建立多餘的索引給查詢最佳化帶來了更多的工作。索引太多，也可能會使 MySQL選擇不到所要使用的最好索引。只保持所需的索引有利於查詢最佳化。如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左 索引。如果是，則就不要費力去增加這個索引了，因為已經有了。
------------------------------------------------- 主鍵索引，主鍵上會自動增加一個主鍵索引
唯一索引，資料是唯一的時候加上唯一索引，也就是唯一約束，使用唯一索引查詢效率要比普通索引和多列索引速度快
單列索引（普通索引），任何列上都可添加
多列索引（複合式索引），如果where字句後面有多列，或者資料有重複的，這樣需要加多列索引，盡量避免對複合式索引的列進行更新，因為更新資料後索引也會重新維護。
注意：修改表結構會，索引會失效，需要重構索引
mysql監控以及調優 ---轉
