MySQL性能優(yōu)化、慢查詢分析、優(yōu)化索引和配置

10年積累的成都做網(wǎng)站、成都網(wǎng)站制作經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你，你也不認識我。但先網(wǎng)站設(shè)計制作后付款的網(wǎng)站建設(shè)流程，更有順義免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

一.每項的基本思路步驟

1.性能瓶頸定位：show命令、慢查詢?nèi)罩尽xplain分析查詢、profiling分析查詢、

2.索引及查詢優(yōu)化

3.配置優(yōu)化

二.mysql是常見的兩個瓶頸是cpu和i/o的瓶頸，cup在內(nèi)存和磁盤上讀取數(shù)據(jù)，磁盤i/o發(fā)生在裝入數(shù)據(jù)遠大于內(nèi)存容量的時候，當查詢量過大的時候瓶頸會暴露在網(wǎng)絡(luò)中，可以通過命令查看系統(tǒng)性能:mpstat、iostat、sar、vmstat

 

使用另外兩個查看

 

優(yōu)化數(shù)據(jù)庫通常有三種：使用索引、explain分析查詢、調(diào)整mysql內(nèi)部配置

1.查詢和索引優(yōu)化

優(yōu)化mysql需要對數(shù)據(jù)庫分析，有慢查詢、explain、profiling分析、show命令查看系統(tǒng)狀態(tài)和系統(tǒng)變量。

show命令：

使用variables查看數(shù)據(jù)庫配置信息

 

查看數(shù)據(jù)庫運行的各種狀態(tài)值

 

在命令行中使用mysqladmin查看系統(tǒng)變量及狀態(tài)信息

 

 在數(shù)據(jù)庫中使用help  show可以查看所有參數(shù)參考

慢查詢?nèi)罩?/p>

開啟慢查詢?nèi)罩荆涸趍y.cnf配置文件中添加3個參數(shù)，然后重啟mysql數(shù)據(jù)庫服務(wù)

重啟服務(wù)并且查看是否存在目錄

 

也可以通過在數(shù)據(jù)庫中設(shè)置

set global slow_query_log=on;    set long_query_time=時間；   set global  slow_query_log_file="目錄"

查看慢查詢設(shè)置信息

 

為了測試可以建個腳本執(zhí)行創(chuàng)建庫、表建多少行，次測試建了20000行

 

腳本內(nèi)容

 

執(zhí)行一條命令并且查看慢查詢?nèi)罩?/p>

 

在日志文件中查看 如果在日志中查詢不到在/etc/my.cnf中把time時間改小點

 

explain分析查詢

使用explain關(guān)鍵字是可以模擬優(yōu)化器執(zhí)行sql查詢語句

 

EXPLAIN字段：

Table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

type：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq_reg、ref、range、index和ALL

possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引。

key：實際使用的索引。如果為NULL，則沒有使用索引。

key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)

rows：MySQL認為必須檢索的用來返回請求數(shù)據(jù)的行數(shù)

Extra：關(guān)于MYSQL如何解析查詢的額外信息 

沒有索引不是單個查找而是全表掃描

優(yōu)化方法：創(chuàng)建索引、然后在進行查找執(zhí)行explain

 

profiling分析查詢

通過profiling命令可以更精準的查到sql執(zhí)行消耗資源的信息（profiling默認是關(guān)閉的）

開啟profiling并且查看是否開啟

 

執(zhí)行語句測試

 

使用profile查看sql語句執(zhí)行的詳細信息   status：是profile里的狀態(tài)，duration：是status狀態(tài)下的所耗的時間

 

還可以測試更多的信息

SHOW PROFILE [type [, type] ... ][FOR QUERY n]

type:
       ALL:顯示所有的開銷信息
      BLOCK IO:顯示塊IO相關(guān)開銷
      CPU:顯示用戶CPU時間、系統(tǒng)CPU時間
      IPC:顯示發(fā)送和接收相關(guān)開銷信息
       PAGE FAULTS:顯示頁面錯誤相關(guān)開銷信息
      SWAPS:顯示交換次數(shù)相關(guān)開銷的信息

測試完之后，要關(guān)閉profiling以免影響數(shù)據(jù)庫的正常使用

 

2.配置優(yōu)化

mysql參數(shù)優(yōu)化對于不同的網(wǎng)站，及其在現(xiàn)量、訪問量、帖子數(shù)量、網(wǎng)絡(luò)情況、硬件配置都有關(guān)系，通過不斷的調(diào)試才能達到最佳的效果

連接請求的變量

(1)max_connections:mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)連接較大，必須調(diào)高連接數(shù)值，否則會宕機，mysql連接數(shù)越多會為每個連接提供連接緩沖區(qū)，就會開銷越多的內(nèi)存

查看最大連接數(shù)

 

查看當前的響應(yīng)的連接數(shù)

 

調(diào)整最大連接數(shù)并測試 在/etc/my.cnf中加入?yún)?shù)max_connections =連接數(shù)

 

到第四臺連接的時候報錯了 連接太多不讓連接

(2)back_log：能暫存連接數(shù)量，當主要的mysql線程在一個短時間內(nèi)有很多的連接請求，它會起作用

當觀察主機進程列表時發(fā)現(xiàn)有很多大量的待連接時 用下面命令查看

 

查看back_log的連接數(shù)

 

修改back_log連接數(shù) 在/etc/my.cnf中添加參數(shù)

 

然后重啟服務(wù)

(3)wait_timeout和interactive_time

wait_timeout:是mysql在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)

interactive_time：是mysql關(guān)閉一個交互的連接之前所要等待的秒數(shù)，如果超過了默認值的秒數(shù)就會自動斷開，默認值為28800=8個小時，可調(diào)為7200

wait_timeout:如果設(shè)置太小，連接關(guān)閉的很快，從而時一些持久的連接不起作用

       如果設(shè)置太大，容易造成連接打開時間過長，從而造成too many  connections錯誤     一般建議wait_timeout盡可能降低

查看wait_timeout、interactive_timeout

 

修改/etc/my.cnf文件，在[mysqld]下面添加

wait_timeout = 秒數(shù)

interactive-timeout = 秒數(shù)

(4)key-buff_size

key_buff_size：是指定索引緩沖區(qū)的大，決定索引的處理速度，通過查看key_read_requests和key_reads可以知道key_buffer_size設(shè)置是否合理

查看狀態(tài)值

 

它們倆的比例最好是key_reads/key_buffer_size  1/100  1/1000

一共有6個索引讀取請求，有3個請求在內(nèi)存中沒有找到直接從硬盤中讀取索引

*key_buffer_size只對mylsam引擎表起作用   即使不使用mylsam表也能使用內(nèi)部臨時磁盤表這個表是mylsam引擎

 

調(diào)整key_buffer_size在/etc/my.cnf中[mysqld]下添加參數(shù)key_buffer_size=多少MB

重啟mysqld，就能生效

（5）query_cache_size簡稱QC

使用查詢緩沖，mysql將查詢結(jié)果存放在緩沖區(qū)中，對于同樣的select語句，將直接從緩沖區(qū)讀取結(jié)果。通過查看狀態(tài)值qcache%,可以知道query_cache_size設(shè)置是否合理

 

qcache_free_blocks:緩存中相鄰內(nèi)存塊的個數(shù)，如果多就證明碎片較多，通過flush query cache對緩存中的碎片進行清理，從而得到空閑的塊

qcache_free_memory：query cache 中目前剩余的內(nèi)存大小

qcache_hits：表示有多少次命中緩存

qcache_inserts：表示多少次未命中然后插入

qcache_lowmem_prunes：多少條query 因為內(nèi)存不足而被清除出querycache

qcache_not_cached：不適合進行緩存的查詢的數(shù)量

qcache_queries_in_cache：當前query cache 中cache的query 數(shù)量

qcache_total_blocks：當前query cache 中的block數(shù)量

查看query_cache的配置

 

query_cache_limit：超過此大小的查詢將不緩存

query_cache_min_res_unit：緩存塊的最小大小 

query_cache_size：查詢緩存大小

query_cache_type：緩存類型

 

修改/etc/my.cnf添加query_cache_size =多少M    query_cache_type=1（開啟）

重啟mysqld服務(wù)   查看是否啟動成功

 

當前題目：mysql性能優(yōu)化（一）
路徑分享：http://www.chinadenli.net/article48/ighdhp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、App開發(fā)、響應(yīng)式網(wǎng)站、App設(shè)計、外貿(mào)網(wǎng)站建設(shè)、軟件開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)