MySQL性能监控
MySQL數據庫監控一般主要關注連接數、緩存、事務和鎖、慢查詢等幾項指標。
1、MySQL連接數
MYSQL數據庫默認最大連接數是100，然而對于流量稍微大一點的論壇或網站這個連接數是遠遠不夠的，當并發數過大的時候會出現連接數不夠用，使得很多線程在等待其他連接釋放，會直接導致導致數據庫連接超時或者響應時間過長，所以需要調整最大連接數。
# 重新設置數據庫最大連接數
set global max_connections=200
# 查詢數據庫當前設置的最大連接數
show variables like '%max_connections%';
show global status like 'Max_used_connections'; #使用的最大連接數
?show status like 'Threads%';#使用的線程情況
2、Mysql緩存
? ? ? ?數據庫屬于 IO 密集型的應用程序，其主要職責就是數據的管理及存儲工作。而我們知道，從內存中讀取一個數據庫的時間是微秒級別，而從一塊普通硬盤上讀取一個IO是在毫秒 級別，二者相差3個數量級。所以，要優化數據庫，首先第一步需要優化的就是 IO，盡可能將磁盤IO轉化為內存IO。
緩存分為兩個維度
查詢緩存Query cache
存儲引擎InnoDB_Buffer_Pool
- 查詢緩存Query cache
查詢緩存會緩存完整的SELECT查詢結果，當查詢命中緩存時MySQL會將結果立刻返回，直接跳過了解析、優化和執行階段。當然，Query Cache 也有一個致命的缺陷，那就是當某個表的數據有任何變化，都會導致所有引用了該表的select語句在Query Cache 中的緩存數據失效。所以，當我們的數據變化非常頻繁的情況下，使用Query Cache 可能會得不償失。因此，應用程序不需要關心MySQL是通過緩存查詢出的結果還是實際執行過SQL語句返回的結果，因為這兩種結果是完全相同的
從上面的數據庫執行過程圖中可以看到，執行一條SQL查詢語句會先查詢該語句是否 存在于緩存中，需要注意的是當語句中的字符大小寫或注釋只要有一點點的不同，查詢緩存就會被認為是不同的查詢，導致無法命中查詢緩存。另外，對于不確定的 函數，如：now()、current_date()等這種查詢都不會被緩存
既然查詢緩存的有可以改善性能的優點，自然也有自己的缺點，主要體現在當開啟了查詢緩存時對于讀寫操作都增加了額外的開銷。相對于讀，再查詢開始前需要先檢查緩存，而對于寫，則是當寫入數據后需要更新緩存。
Query cache的參數配置
查詢緩存參數，在mysql配置文件中添加，linux下為my.cnf，windows下為my.ini：
# 1.是否開啟查詢緩存，具體選項是0(OFF),1(ON)和2(DEMAND)
query_cache_type = 1
# 2.分配給查詢緩存的總內存，一般建議不超過物理內存的一半
query_cache_size = 128M
# 3.表示單個結果集所被允許緩存的最大值，結果比這個大，就不會被緩存query_cache_limit = 1M
#4.分配內存塊時的最小單位大小；最合適的大小和應用程序查詢結果的平均大小直接相關 query_cache_min_res_unit=4096
#5.如果某個數據表被鎖住,是否仍然從緩存中返回數據,默認是OFF,表示仍然可以返回 ?query_cache_wlock_invalidate=OFF
Show variables like '%query_cache%';
Query cache結果
Qcache_free_blocks:表示查詢緩存中目前還有多少剩余的blocks，如果該值顯示較大，則說明查詢緩存中的內存碎片過多了，可能在一定的時間進行整理
Qcache_free_memory:查詢緩存剩余的內存大小，通過這個參數可以知道當前系統的查詢內存是否夠用，可以根據實際情況做出調整
Qcache_hits:表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢緩存的效果。數字越大，緩存效果越理想
Qcache_inserts: 表示多少次未命中然后插入，意思是新來的SQL請求在緩存中未找到，不得不執行查詢處理，執行查詢處理后把結果insert到查詢緩存中。這樣的情況的次數越多，表示查詢緩存應用到的比較少，效果也就不理想。當然系統剛啟動后，查詢緩存是空的，這很正常
Qcache_lowmem_prunes:該參數記錄有多少條查詢因為內存不足而被移除出查詢緩存。通過這個值，用戶可以適當的調整緩存大小
Qcache_not_cached: 表示因為query_cache_type的設置而沒有被緩存的查詢數量
Qcache_queries_in_cache:當前緩存中緩存的查詢數量
Qcache_total_blocks:當前緩存的block數量
Show status like 'Qcache%';
- 存儲引擎層-innodb buffer pool
buffer pool是innodb存儲引擎帶的一個緩存池，查詢數據的時候，它首先會從內存中查詢，如果內存中存在的話，直接返回，從而提高查詢響應時間；
innodb buffer pool和qcache的區別是：qcacche緩存的是sql語句對應的結果集，buffer pool中緩存的是表中的索引，數據，插入數據時的緩沖。Buffer pool是設置的越大越好，一般設置為服務器物理內存的70%。
innodb buffer pool性能調優重要的一個參數，默認8M：
innodb_buffer_pool_size = 8M??? # 大小
在my.cnf中設置：
innodb_buffer_pool_size = 512M
show VARIABLES like '%innodb_buffer%';
????????
3、事物和鎖
事物
一個最小的不可再分的工作單元；通常一個事務對應一個完整的業務(例如銀行賬戶轉賬業務，該業務就是一個最小的工作單元)?
一個完整的業務需要批量的sql(insert、update、delete)語句共同聯合完成
一個事物：
?? start transaction
? update table set id=1 where name = test
? select * from table? where id =1
? commit
?兩個事物：
update table set id=1 where name = test
select * from table? where id =1
注：兩個事物同時并行操作同一張表
鎖
MySQL大致可歸納為以下3種鎖：
表級鎖：開銷小，加鎖快；不會現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般
表鎖：
Update Score set id = xx
行鎖：
Update Score set id = xx where Stu_id=xx;
注：InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索 數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
共享鎖：
?共享鎖又叫做讀鎖，所有的事務只能對其進行讀操作不能寫操作，加上共享鎖后在事務結束之前其他事務只能再加共享鎖???
排他鎖：
?若某個事物對某一行加上了排他鎖，只能這個事務對其進行讀寫，在此事務結束之前，其他事務不能對其進行加任何鎖，其他進程可以讀取,不能進行寫操作，需等待其釋放
InnoDB引擎默認的修改數據語句，update,delete,insert都會自動給涉及到的數據加上排他鎖，
select語句默認不會加任何鎖類 型，如果加排他鎖可以使用select ...for update語句
加共享鎖可以使用select ... lock in share mode語句。所以加過排他鎖的數據行在其他事務種是不能修改數據的，也不能通過for update和lock in share mode鎖的方式查詢數據，但可以直接通過select ...from...查詢數據，因為普通查詢沒有任何鎖機制。
死鎖
鎖是指兩個或者多個事務在同一資源上互相占用，并且請求鎖定對方占用的資源，從而導致死循環的現象
START TRANSACTION;
UPDATE Score set grade = 99 where id =7;
select SLEEP(10);
UPDATE Score set grade = 76 where id =8;
?查看是否死鎖：
一般日志里有dblock等字樣，如顯示不全復制到文本編輯器或者輸出到文件
?結果寫文件：mysql -u root -p --execute="show engine innodb status \G" > /tmp/mysql.log
出現死鎖處理，查詢死進程
show processlist;???? KILL xxxx;
?多線程并發才有可能死鎖
?避免交叉加鎖
?減少涉及的表，表聯接會大大增加鎖范圍
?????????
四、Mysql慢查詢
開啟慢查詢日志，可以讓MySQL記錄下查詢超過指定時間的語句，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能
開始慢查詢的三個參數：
?slow_query_log 慢查詢開啟狀態
slow_query_log_file 慢查詢日志存放的位置（這個目錄需要MySQL的運行帳號的可寫權限，一般設置為MySQL的數據存放目錄）
long_query_time 查詢超過多少秒才記錄
- 慢查詢開啟方法
方法一 查詢分析器直接運行以下語句：
Set log_output=table
set global slow_query_log='ON';
set global long_query_time=1;
方法二，配置文件設置：
修改配置文件my.cnf，在[mysqld]下的下方加入
?????? log_output=table 存到表里
?????? slow_query_log = 1 或 on
long_query_time = 1 設置大于1秒的記錄
- 查看慢查詢相關參數
show variables like 'slow_query%';? #查詢是否開啟
show variables like 'long_query_time'; #查詢超時時間
show variables like '%log_output%';#查詢存儲類型
查看慢查詢sql
SELECT * FROM mysql.slow_log;
- Explain 執行計劃
Explain :該命令是查看查詢優化器如何決定執行查詢的主要方法
Explain列解析：
Id id相同由上向下執行，不同值越大優先級越高，越先被執行
select_type 示查詢中每個select子句的類型（簡單OR復雜）
?Type 表示MySQL在表中找到所需行的方式，又稱“訪問類型”
ALL, index, range, ref, eq_ref, const, system, NULL
? 從左到右，性能從最差到最好
All MySQL將遍歷全表以找到匹配的行
Index index類型只遍歷索引樹
range:索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行
ref：使用非唯一索引掃描或者唯一索引的前綴掃描，返回匹配某個單獨值的記錄行
eq_ref類似ref，區別就在使用的索引是唯一索引
const,system,當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問
NULL，MySQL在優化過程中分解語句，執行時甚至不用訪問
possible_keys 指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引
Key 顯示MySQL在查詢中實際使用的索引，若沒有使用，顯示為NULL
key_len 表示索引中使用的字節數，可計算查詢中使用的索引的長度
possible_keys 指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引
Ref 表示表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
Rows 表示根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數
Extra 包含不適合在其他列中顯示但十分重要的額外信息
a. Using index 該值表示相應的select操作中使用了覆蓋索引
b. Using where 表示mysql服務器將在存儲引擎檢索行后再進行過
c. Using temporary 表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢
d. Using filesort 無法利用索引完成的排序操作稱為“文件排序”
e. Using join buffer 該值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區來存儲中間結果。出現該值可能需添加索引來優
f. Impossible where? 這個值強調了where語句會導致沒有符合條件的行
h. Select tables optimized away 這個值意味著僅通過使用索引，優化器可能僅從聚合函數結果中返回一行
I. Index merges 當MySQL 決定要在一個給定的表上使用超過一個索引的時候，就會出現以下格式中的一個，詳細說明使用的索引以及合并的類型
五、查詢優化原則
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number
- 減少IO次數
IO永遠是數據庫最容易瓶頸的地方，這是由數據庫的職責所決定的，大部分數據庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數是 SQL 優化中需要第一優先考慮，也是收效最明顯的優化手段
- 降低 CPU 計算
除了 IO 瓶頸之外，SQL優化中需要考慮的就是 CPU 運算量的優化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理內存中的數據比較運算)。當我們的 IO 優化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優化的重要目標。
六、MSQL優化基本原則
1、選取最適用的字段類型，盡量避免浪費
2、使用連接（JOIN）來代替子查詢(Sub-Queries)
3、使用聯合(UNION)來代替手動創建的臨時表
4、減少排序
5、禁用外鍵
6、避免大sql
7、保持事物的短小精悍
8、避免大批量更新
9、避免取過量數據，靈活使用limit
10、避免在SQL?語句中進行數學運算、函數計算、邏輯判斷等操作
11、避免OR
12、優先優化高并發的 SQL，而不是執行頻率低某些“大”SQL
13、盡可能對每一條運行在數據庫中的SQL進行explain
14、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
15、使用索引查詢。
MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。
索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。
總結
- 上一篇： 美业多门店管理系统，智慧门店数字化方案
- 下一篇： HTML+CSS+JS网页设计期末课程大