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

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

一.每項(xiàng)的基本思路步驟

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

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

3.配置優(yōu)化

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

使用另外兩個(gè)查看

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

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

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

show命令：

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

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

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

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

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

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

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

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

set global slow_query_log=on;    set long_query_time=時(shí)間；   set global  slow_query_log_file="目錄"

查看慢查詢?cè)O(shè)置信息

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

腳本內(nèi)容

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

在日志文件中查看 如果在日志中查詢不到在/etc/my.cnf中把time時(shí)間改小點(diǎn)

explain分析查詢

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

EXPLAIN字段：

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

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

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

key：實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引。

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

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

rows：MySQL認(rèn)為必須檢索的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)

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

沒(méi)有索引不是單個(gè)查找而是全表掃描

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

profiling分析查詢

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

開(kāi)啟profiling并且查看是否開(kāi)啟

執(zhí)行語(yǔ)句測(cè)試

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

還可以測(cè)試更多的信息

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

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

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

2.配置優(yōu)化

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

連接請(qǐng)求的變量

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

查看大連接數(shù)

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

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

到第四臺(tái)連接的時(shí)候報(bào)錯(cuò)了 連接太多不讓連接

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

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

查看back_log的連接數(shù)

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

然后重啟服務(wù)

(3)wait_timeout和interactive_time

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

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

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

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

查看wait_timeout、interactive_timeout

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

wait_timeout = 秒數(shù)

interactive-timeout = 秒數(shù)

(4)key-buff_size

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

查看狀態(tài)值

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

一共有6個(gè)索引讀取請(qǐng)求，有3個(gè)請(qǐng)求在內(nèi)存中沒(méi)有找到直接從硬盤(pán)中讀取索引

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

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

重啟mysqld，就能生效

（5）query_cache_size簡(jiǎn)稱QC

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

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

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

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

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

qcache_lowmem_prunes：多少條query 因?yàn)閮?nèi)存不足而被清除出querycache

qcache_not_cached：不適合進(jìn)行緩存的查詢的數(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：超過(guò)此大小的查詢將不緩存

query_cache_min_res_unit：緩存塊的最小大小

query_cache_size：查詢緩存大小

query_cache_type：緩存類型

修改/etc/my.cnf添加query_cache_size =多少M(fèi)   query_cache_type=1（開(kāi)啟）

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

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

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

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

廣告

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