MySQL再發一彈，不要再說不會了！

0、推薦學習

一、查詢截取分析

1）慢查詢日誌

MySQL 的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。 具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的默認值為10，意思是運行10秒以上的語句。 我們可以查看哪些SQL超出了我們的最大忍耐時間值，比如一條SQL執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，可以結合之前explain進行全面分析。

開始使用：默認情況下，MySQL數據庫沒有開啟慢查詢日誌，需要我們手動來設置這個參數。通過show variables like '%slow_query_log' 查看是否開啟了慢查詢日誌

在這裡插入圖片描述

設置方法：

<code># 以下方式只對當前數據庫有效，MySQL重啟後失效
set global slow_query_log = 1;
set global long_query_time = 1.0;
# 主要重新連接或者新開一個會話才能看到修改值
set session long_query_time = 1.0;/<code>

永久生效就得修改 my.cnf

<code>slow_query_log = 1

#指定生成位置，如果沒有指定默認生成 host_name-slow.log
slow_query_log_file=/var/lib/mysql/cbuc_slow.log/<code>

開啟後如果long_query_time沒有指定，默認為10秒，那麼假如運行時間正好等於long_query_tie的情況，並不會被記錄下來，也就是說在mysql源碼裡面的判斷是大於long_query_time,而非大於等於實驗：

<code># 手動製造一條慢SQL
select sleep(9)/<code>

跟蹤日誌文件 ： tail -50f cbuc_slow.log

查詢當前系統中有多少條慢查詢：

<code>show global status like '%Slow_queries%'/<code>

【配置小結】在 my.ini或者my.cnf配置文件下的配置

<code>show_query_log = 1;
show_query_log_file = /var/lib/mysql/cbuc_slow.log
long_query_time = 3;
log_output = FILE/<code>

日誌分析工具mysqldumpslow查看mysqldumpslow的幫助信息:

1. s：是表示按照何種方式排序；
2. c：訪問次數
3. l：鎖定時間
4. r：返回記錄
5. t：查詢行數
6. al：平均鎖定時間
7. ar：平均返回記錄數
8. at：平均查詢時間
9. t：即為返回前面多少條的數據
10. g：後邊搭配一個正則匹配模式，大小寫不敏感

【使用參考】1、 得到返回記錄集最多的10個SQL

<code>mysqldumpslow -s -t 10 /var/lib/mysql/cbuc_slow.log/<code>

2、 得到訪問次數最多的10個SQL

<code>mysqldumpslow -s -c -t 10 /var/lib/mysql/cbuc_slow.log/<code>

3、 得到按照時間排序的前10條裡面含有左連接的查詢語句

<code>mysqldumpslow -s -t -t 10 -g "left join"  /var/lib/mysql/cbuc_slow.log/<code>

4、 另外建議在使用這些命令是結合 | 和 more使用, 否則有可能出現爆屏的情況

<code>mysqldumpslow -s r -t 10 /var/lib/mysql/cbuc_slow.log | more/<code>

2）Show Profile

是mysql提供可以用來分析當前會話中語句執行的資源消耗情況，可以用於SQL的調優的測量 默認情況下，參數處於關閉狀態,並保存最近15次的運行結果

【分析步驟】

• 查看是否支持

<code># 默認是關閉，使用前需要開啟
show variables like 'profiling';/<code>

• 開啟

<code>set profiling = 1;/<code>

• 測試

<code># 運行兩個SQL查看
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id/<code>

查看結果 ：

參數說明：

• ALL：顯示所有的開銷信息
• BLOCK IO ：顯示塊 IO 相關開銷
• CONTEXT SWITCHES ：上下文切換相關開銷
• CPU ：顯示CPU相關開銷信息
• IPC ：顯示發送和接收相關開銷信息
• MEMORY ：顯示內存相關開銷信息
• PAGE FAULTS ：顯示頁面錯誤相關開銷信息
• SOURCE ：顯示和Source_function,Source_file,Source_line 相關的開銷信息
• SWAPS ：顯示交換次數相關開銷的信息

3）全局查詢日誌

• 配置啟用
  在 mysql 的my.cnf或my.ini中設置

<code># 開啟
general_log = 1

# 記錄日誌文件的路徑
general_log_file = /path/logfile

# 輸出格式
log_output = FILE/<code>

• 編碼啟用

命令：

<code>set global general_log = 1;/<code>

全局日誌可以存放在日誌文件文件中，也可以存放在MySQL系統表中。存放在日誌中性能會更好一些，存儲到表中：

<code>set global log_output = 'TABLE'/<code>

此後，你所編寫的sql 語句，將會記錄到mysql 庫裡的 general_log 表，可以用下面的命令查看

<code>select * from mysql.general_log/<code>

二、Mysql鎖機制

1）概述

鎖是計算機協調多個進程或線程併發訪問某一資源的機制。在數據庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據併發訪問的一致性，有效性是所有數據庫必須解決的一個問題，鎖衝突也是影響數據庫併發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯的尤其重要，也更加複雜。

【案例理解】一件商品這個時候只有一件庫存，但是同時用A、B兩個人要下單，那麼是A下單成功還是B下單成功。這種時候就要使用到事務，我們要先從庫存表中取出物品數量，然後生成訂單，付款成功後生成付款信息，再更新商品數量。這個流程中，我們需要使用到鎖對有限的資源進行保護，解決隔離和併發問題。 【鎖的分類】

• 從數據操作的類型劃分 （讀/寫鎖）

1. 讀鎖（共享鎖）： 針對同一份數據，多個讀操作可以同時進行而不會互相影響。
2. 寫鎖（排它鎖）： 當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

• 從數據操作的顆粒度劃分

1. 表鎖
2. 行鎖

2）三級鎖

【表鎖】特點：（偏讀）

偏向MyISAM存儲引擎，開銷小，加鎖快；無死鎖；鎖定粒度大；發生鎖衝突的概率高，併發度最低。

手動加鎖：

<code>lock table  , /<code>

• 查看錶上加過的鎖：

<code>show open tables;/<code>

• 釋放表鎖

<code>unlock tables;/<code>

讀鎖說明：

新建兩個session會話，session1 和session2此時在session1中對mylock表進行read 鎖定，情況如下：

1. session1可以查詢該表的信息，session2也可以查詢該表的記錄
2. session1中不能查詢其他沒有鎖定的表，session2可以查詢和更新其它沒有鎖定的表
3. session1插入或更新鎖定的表都會提示錯誤，session2插入或更新鎖定的表會一直等待。
4. 當session1釋放鎖後，session2之前插入或更新執行完成。

寫鎖說明：同樣新建兩個session會話，session1 和

session2此時在session1中對mylock表進行write 鎖定，情況如下：

1. session1 對鎖定表的查詢+更新+插入操作都可以執行，session2 對鎖定表的查詢 被阻塞，需要等待鎖的釋放。但是如果session2之前有數據緩存，則可以讀出緩存數據，一旦數據發生改變，緩存將失效，操作將被阻塞。

【小結】：MyISAM在執行查詢語句的前，會自動給涉及的所有表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖。

鎖類型 他人可讀 他人可寫 讀鎖 是 否 寫鎖 否 否

1、 對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求，只有當讀鎖釋放後，才會執行其他進程的寫操作。

2、 對MyISAM表的寫操作（加寫鎖），會阻塞其他線程對同一表的讀和寫操作，只用當寫鎖釋放後，才會執行其他進程的讀寫操作。總結：讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把讀和寫都阻塞

【行鎖】特點：（偏讀）

偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。InnoDB與MyISAM的最大不同有兩點：

支持事務（TRANSACTION） 採用了行級鎖

事務複習：事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。

• 原子性（Atomicity）： 事務是一個原子操作的單元，其對數據的修改，要麼全部執行，要麼全都不執行。
• 一致性（Consistent）：在事務開始和完成時候，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改，以保持數據的完整性；事務結束時，所有內部的數據結構（如B樹索引或雙向鏈表）也都必須是正確的。
• 隔離性（Isolation）：數據庫系統提供一定的隔離機制，保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
• 持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。

併發事務處理帶來的問題：

• 更新丟失（Lost Update）
  當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行是，由於每個事務都不知道其他事務的存在，就會發生丟失更新的問題 -- 最後的更新覆蓋了由其他事務所做的更新。
• 髒讀（Dirty Reads）
  事務A讀取到了事務B已修改但尚未提交的數據，還在這個數據基礎上做了操作。此時，如果B事務回滾，A讀取的數據無效，不符合一致性要求。
• 不可重複讀（Non-Repeatable Reads）
  一個事務範圍內兩個相同的查詢卻返回了不同數據。
• 幻讀（Phantom Reads）
  一個事務按相同的查詢重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀”。也就是說事務A讀取到了事務B提交的新增數據，不符合隔離性。

事務隔離級別：

<code># 查看事務的隔離級別show variable like 'tx_isolate'/<code>

寫鎖（排他鎖）：加上排它鎖後，其他事務不能再對A加任何類型的鎖。已獲取到排它鎖的事務既能讀數據，又能修改數據。

<code># 通過這段加鎖，mysql會對查詢結果中的每行都加排他鎖
select ... for update;/<code>

間隙鎖：當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP）”InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（GAP Lock）危害：因為Query執行過程中通過範圍查找的話，他會鎖定整個範圍內所有的索引鍵值，即使這個鍵值並不存在，間隙鎖有一個比較致命的弱點，就是當鎖定一個範圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值範圍內的任何數據。在某些場景下這可能會對性能造成很大的危害優化建議：

1. 儘可能讓給所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖。
2. 儘可能較少檢索條件，避免間隙鎖。
3. 儘量控制事務大小，減少鎖定資源量和時間長度。
4. 鎖住某行後，儘量不要去調別的行或表，趕緊處理被鎖住的行然後釋放掉鎖。
5. 涉及相同表的事務，對於調用表的順序儘量保持一致。
6. 在業務環境允許的情況下，儘可能低級別事務隔離。
   【頁鎖】
   開銷和加鎖時間介於表鎖和行鎖之間，會出現死鎖；鎖定粒度介於表鎖和行鎖之間，併發度一般。

三、主從複製

1）複製的基本原理

slave 會從 master 讀取binlog來進行數據同步

【三個步驟】

• master將改變記錄到二進制日誌（binary log）。這些記錄過程叫做二進制日誌時間，binary log events
• slave將master的binary log events拷貝到它的中繼日誌中（relay log）
• slave重做中繼日誌中的事件，將改變應用到自己的數據庫中，mysql複製是異步的且串行化的。

2）複製的基本原則

• 每個slave 只有一個master
• 每個slave只能有一個唯一的服務器ID
• 每個master可以有多個slave

複製的最大問題： 延遲

3）主從常見配置

mysql 版本一致且後臺以服務運行，主從配置都在[mysqld]結點下，都是小寫 【主機修改my.ini配置文件】

• [必須] 主服務器唯一ID

<code>server-id = 1/<code>

• [必須] 啟用二進制文件

<code>log-bin = 自己本地的路徑/data/mysqlbin
log-bin = D:/devSoft/MySQLServer5.5/data/mysqlbin/<code>

• [可選] 啟用錯誤日誌

<code>log-err = 自己本地的路徑/data/mysqlerr
log-err = D:/devSoft/MySQLServer5.5/data/mysqlerr/<code>

• [可選] 根目錄

<code>basedir = "自己本地路徑"
basedir = D:/devSoft/MySQLServer5.5//<code>

• [可選] 臨時目錄

<code>tmpdir = "自己本地路徑"
tmpdir =  D:/devSoft/MySQLServer5.5//<code>

• [可選] 數據目錄

<code>datadir = "自己本地路徑"
datadir =  D:/devSoft/MySQLServer5.5/data//<code>

• read-only = 0
  主機讀寫都可以
• [可選] 設置不要複製的數據庫

<code>binlog-ignore-db = mysql/<code>

• [可選] 設置需要複製的數據庫

<code>binlog-do-db = 需要複製的數據庫的名字/<code>

【從機修改my.ini配置文件】

• [必須] 從服務器唯一ID
• [可選] 啟用二進制文件 【修改後，主從機都需要重啟後臺mysql服務】【主從機都需要關閉防火牆】【在windows主機上建立賬戶並授權slave】
• 步驟1：

<code>GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'從機的數據庫IP'INDETIFIED BY '123456'/<code>

• 步驟2：

<code>flush privileges;/<code>

• 查看master狀態

<code>show master status;
# 記錄File和Position 的值/<code>

• 執行完以上步驟便不要再操作，防止主服務器狀態值發生改變

【在Linux從機上配置需要複製的主機】

• 步驟1

<code>change master to master_host = '主機IP'，
master_user='zhangsan',master_password = '123456',
master_log_file='file名字',
master_log_pos=position數字/<code>

• 步驟2：
  
  啟動從服務器複製功能

<code>start slave;/<code>

• 步驟3：

<code>show slave status
#下面兩個參數都是Yes，便說明主從配置成功
Slave_IO_Running：Yes
Slave_SQL_running:Yes/<code>

【主機新建庫，新建表，insert記錄，從機便會複製】【停止從服務複製功能】

<code>stop slave;/<code>

作者：蔡不菜丶
原文鏈接：https://juejin.im/post/5e985bea518825736512e1d4