mysql性能優(yōu)化（一）

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

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

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

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

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

3.配置優(yōu)化

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

 

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

 

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

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

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

show命令：

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

 

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

 

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

 

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

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

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

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

 

也可以通過在數(shù)據(jù)庫中設(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)建庫、表建多少行，次測(cè)試建了20000行

 

腳本內(nèi)容

 

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

 

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

 

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：實(shí)際使用的索引。如果為NULL，則沒有使用索引。

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

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

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

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

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

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

 

profiling分析查詢

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

開啟profiling并且查看是否開啟

 

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

 

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

 

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

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

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

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

 

2.配置優(yōu)化

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

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

(1)max_connections:mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)連接較大，必須調(diào)高連接數(shù)值，否則會(huì)宕機(jī)，mysql連接數(shù)越多會(huì)為每個(gè)連接提供連接緩沖區(qū)，就會(huì)開銷越多的內(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ù)，如果超過了默認(rèn)值的秒數(shù)就會(huì)自動(dòng)斷開，默認(rèn)值為28800=8個(gè)小時(shí)，可調(diào)為7200

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

       如果設(shè)置太大，容易造成連接打開時(shí)間過長，從而造成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ū)的大，決定索引的處理速度，通過查看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)存中沒有找到直接從硬盤中讀取索引

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

 

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

重啟mysqld，就能生效

（5）query_cache_size簡稱QC

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

 

qcache_free_blocks:緩存中相鄰內(nèi)存塊的個(gè)數(shù)，如果多就證明碎片較多，通過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：超過此大小的查詢將不緩存

query_cache_min_res_unit：緩存塊的最小大小 

query_cache_size：查詢緩存大小

query_cache_type：緩存類型

 

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

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

 

當(dāng)前題目：mysql性能優(yōu)化（一）
路徑分享：http://muchs.cn/article48/ighdhp.html

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

廣告

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