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

為城子河等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及城子河網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都做網(wǎng)站、網(wǎng)站設(shè)計、外貿(mào)營銷網(wǎng)站建設(shè)、城子河網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

一.每項的基本思路步驟

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

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

3.配置優(yōu)化

二.mysql是常見的兩個瓶頸是cpu和i/o的瓶頸，cup在內(nèi)存和磁盤上讀取數(shù)據(jù)，磁盤i/o發(fā)生在裝入數(shù)據(jù)遠大于內(nèi)存容量的時候，當(dāng)查詢量過大的時候瓶頸會暴露在網(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命令可以更精準(zhǔn)的查到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ù)

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

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

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

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

當(dāng)觀察主機進程列表時發(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：當(dāng)前query cache 中cache的query 數(shù)量

qcache_total_blocks：當(dāng)前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ù)   查看是否啟動成功

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機、免備案服務(wù)器”等云主機租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

分享文章：mysql性能優(yōu)化（一）-創(chuàng)新互聯(lián)
標(biāo)題URL：http://www.rwnh.cn/article34/pjepe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供關(guān)鍵詞優(yōu)化、搜索引擎優(yōu)化、網(wǎng)站設(shè)計、定制網(wǎng)站、營銷型網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化

廣告

聲明：本網(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)