mysql 在一個sql檔案中引用另外一個sql檔案中自定義函數_MySQL常用的SQL調優手段或工具有哪些

目錄

• MySQL binlog_format=mixed，可行嗎，為什麼
• MySQL誤删除frm檔案該怎麼辦？
• 你遇到過PHP連接配接MySQL的性能問題嗎，如何解決的？
• MySQL常用的SQL調優手段或工具有哪些
• 在一個2c4g的伺服器上如何用python操作8GB的超大檔案
• MySQL反應慢的排查思路

一、MySQL binlog_format=mixed，可行嗎，為什麼

不可行，因為會導緻主從資料不一緻

Mixed格式相當于 Row 和 Statement 模式的融合。遇到表結構變更的時候就會以statement模式來記錄。像update或者delete等修改資料的語句，還是會記錄所有行的變更。

但某些情況就會産生主從資料不一緻例如：

1、當帶有自增主鍵的更新多個列的表，并調用觸發器或存儲函數時

2、當SQL使用LOAD_FILE()功能時。（Bug＃39701）

3、當SQL語句引用一個或多個系統變量時。（Bug＃331168）

更多請參考：https://dev.mysql.com/doc/ref...

二、MySQL誤删除frm檔案該怎麼辦？

情況一：誤删後還未重新開機MySQL

1、從proc中恢複.frm檔案

cp /proc/

pidof mysqld

/fd/誤删除的.frm /datadir/db/對應庫的目錄/

情況二：誤删後也重新開機MySQL了

2、從備份中擷取表結構

• 實體備份。從實體備份中直接把.frm檔案拷貝回來。
• 邏輯備份。找到該表的DDL，在備用執行個體建立該表，再把.frm檔案拷貝回來。

注意事項：

1、無論是情況一還是情況二，都需要重新設定屬主和屬組。

2、若恢複期間對該表執行了新的DDL，則上述方法可能都無效。

3、本案例在MySQL 5.7.18版本（開啟表獨立空間模式）下親測通過。

三、你遇到過PHP連接配接MySQL的性能問題嗎，如何解決的？

PHP連接配接MySQL用得比較多的有原生mysql擴充、mysqli和pdo_mysql等，其與MySQL的連接配接可以分為三類

1、短連接配接，每次都需要建立新連接配接，資料庫開銷較大。尤其是高并發環境下，有可能會把資料庫連接配接數直接打滿并造成CPU很高。

2、長連接配接，但無法像連接配接池那樣做到連接配接複用，控制不好的話，更容易導緻資料庫連接配接數爆表。

3、利用第三方應用提供的連接配接池功能，如swoole、ProxySQL等，基本上就可以解決連接配接性能瓶頸了。尤其像ProxySQL這樣的還能順便解決讀寫分離、高可用切換等問題。

另建議設定net.ipv4.tcp_tw_reuse=1打開tcp重用，提高tcp連接配接性能。

四、MySQL常用的SQL調優手段或工具有哪些

1、根據執行計劃優化

通常使用desc或explain，另外可以添加format=json來輸出更詳細的json格式的執行計劃，主要注意點如下：

• type：顯示關聯類型。重點關注ALL（全表掃描）、index（全索引掃描）；
• key_len：使用到索引的長度。通常該值大于30就要注意被選中的索引是否字元串類型，可否進一步優化；
• rows：預估掃描的行數。通常該值大于1萬就要注意可否選擇更合适的索引減少掃描的行數；
• extra：顯示額外資訊。重點關注Using temporary，Using filesort，盡量通過添加或調整來消除。

2、利用profiling優化

通過探針的方式詳細記錄sql執行過程詳細代價，可以很清楚地了解到sql到底慢在哪個環節。

重點關注下列幾種情況是否耗時較大：

• sending data
• creating sort index
• sorting result
• query end
• Waiting ... lock
• Creating tmp table
• Copying to tmp table

3、利用optimizer_trace優化

可以輸出優化器評估SQL執行計劃的詳細過程，尤其是每個可能的索引選擇的代價。

利用它可以明白優化器為什麼選中索引A，而不選中索引B。

4、利用session status優化

通過flush status重置session級别的狀态值後，執行sql檢視相應的狀态變化量。

可重點關注幾個資訊：

Created_tmp_tables，建立記憶體臨時表
    Created_tmp_disk_tables，建立磁盤臨時表，尤其注意
    Handler_read_rnd，随機讀
    Handler_read_rnd_next，全表掃描或者排序或者讀下一行
    Select_scan，全表掃描
    Select_full_join，全表join
    Sort_merge_passes，多次歸并排序
           

5、其他優化工具

MySQL workbench、pt-query-digest等

五、在一個2c4g的伺服器上如何用python操作8GB的超大檔案

1、使用with open的方式，for line in f檔案對象f視為一個疊代器，會自動的采用緩沖IO和記憶體管理，并且能夠自動關閉檔案，推薦該方式

舉例：

with open('filename') as f:
    for line in f:
        do_things(line)
           

2、open file的方式，可以通過read(size)指定每次讀取的大小，将大檔案切割成小檔案來讀取，每次處理完小塊即釋放記憶體

舉例：

f = open(filePath)
while True:
 content = f.read(chunk_size)
 do_things(content)
           

3、linecache子產品，可以指定讀取檔案某一行

舉例：

content = linecache.getline('filename', linenum)
do_things(content)
           

六、MySQL反應慢的排查思路 （一）導緻MySQL慢可能的因素有

1、計算資源不足

2、系統層面未進行基本的優化，或不同程序間資源搶占

3、MySQL配置不科學(附神器：http://imysql.com/my-cnf-wiza...

4、垃圾SQL滿天飛

（二）檢視系統層面負載手段

1、top檢視整體負載情況，快速确認哪個程序系負載高

2、free檢視記憶體情況，是否有記憶體洩露和用了swap等風險

3、vmstat/sar檢視目前系統瓶頸到底在哪，如CPU、IO、網絡等

4、終極神器perf top檢視cpu消耗在哪些系統調用函數

（三）檢視MySQL的整體情況

1、觀察show processlist輸出中是否有臨時表、排序、大量邏輯讀、鎖等待等狀态

2、觀察show engine innodb status輸出中是否有大事務、長事務、鎖等待等狀态

（四）幹掉垃圾SQL，常用手段

1、用explain、desc觀察執行計劃

2、用profiling定位sql執行的瓶頸

3、用pt-query-digest分析慢sql

（五）幾個竅門

1、mysqld程序消耗CPU長時間超過90%的話，99.9%是因為沒用好索引

2、cpu的%sys高的話，大機率是swap或中斷不均衡導緻，也可能是有多個索引且超高并發寫入(更新)，或者有很嚴重的鎖等待事件

3、最⼤的瓶頸通常是在磁盤I/O上，是以盡量用高速磁盤裝置

4、如果物理磁盤無法再更新，則通過增加記憶體提升性能容量

5、遇到無法診斷的問題時，試試⽤perf top來觀測跟蹤

6、SQL執行慢，有時未必是效率低，也可能是因為鎖等待，甚⾄是磁盤滿了

詳情戳：精彩回顧┃《MySQL為什麼慢》視訊上線

公衆号：知數堂，更多MySQL幹貨知識，關注公衆号擷取。

原文連結：https://zhishutang.com/p7W

推薦閱讀：https://zhishutang.com/xdI