mysql優(yōu)化索引、配置，及慢查詢講解

下文內(nèi)容主要給大家?guī)?nbsp;mysql優(yōu)化索引、配置，及慢查詢講解，這里所講到的知識(shí)，與書籍略有不同，都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來幫助。           

s首先基本的思路

1）性能瓶頸定位

使用show命令、

慢查詢?nèi)罩尽?/span>

explain分析查詢、

profiling分析查詢、

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

3）配置優(yōu)化

MySQL數(shù)據(jù)庫常見的兩個(gè)瓶頸cpu、i/o：

CPU主要在飽和的時(shí)候發(fā)生在數(shù)據(jù)裝入內(nèi)存或磁盤上讀取數(shù)據(jù)的時(shí)候

i/o發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候，如果應(yīng)用分布在網(wǎng)絡(luò)上，那么查詢量相當(dāng)大的網(wǎng)絡(luò)瓶頸，我們可以通過mpstat、iostat、vmstat、sar等命令查看系統(tǒng)的性能狀態(tài)

例如：mpstat  3  3 {表示每3秒輸出三次}

另外除了云服務(wù)器硬件的性能瓶頸，對(duì)于mysql系統(tǒng)本身，我們可以使用工具來優(yōu)化數(shù)據(jù)庫的性能；通常有三種：

使用索引、   使用explain分析查詢、   調(diào)整mysql的內(nèi)部配置

1：查詢與索引優(yōu)化分析；

在優(yōu)化mysql時(shí)，通常需要對(duì)數(shù)據(jù)庫進(jìn)行分析，常見的分析有慢查詢?nèi)罩荆?/span>explain分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量。

show命令

可以通過show命令查看mysql狀態(tài)以及變量，找到系統(tǒng)的瓶頸；

查看mysql服務(wù)器配置信息 、

查看mysql服務(wù)器運(yùn)行的各種狀態(tài)、

顯示系統(tǒng)變量：=====>> mysqladmin variables -u username -ppassword

顯示系統(tǒng)狀態(tài)：======>> mysqladmin exlended -stautus -u username -ppassword

另外可以通過：

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

開啟慢查詢?nèi)罩?；在配置文件?/span>/etc/my.cnf中添加三個(gè)參數(shù)；

slow_query_log=1 [1表示開啟、0表示關(guān)閉]

slow_query_log_file=/usr/local/mysql/data/slow-query.log 慢查旬日志存放位置

long-query_time=1 表示查詢超過1秒的時(shí)間記錄

在my.cnf中添加log-queries-not-using-indexes參數(shù)，表示向慢查詢?nèi)罩局杏涗浵聸]有使用索引的查詢

慢查詢?nèi)罩疽部梢栽诿钚兄虚_啟：

只不過在命令行中的屬于臨時(shí)生效，而在主配置文件中屬于永久生效

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

查看超時(shí)的時(shí)間限制

另外我們還可以同過查看慢查詢?nèi)罩静榭磮?zhí)行效率低的sql語句

可以看到剛才指定慢查詢文件的路徑這條命令的執(zhí)行結(jié)果時(shí)間超過了0.01秒，所以也同樣被記錄了下來。

如果慢查詢?nèi)罩井?dāng)中的內(nèi)容很多的話，可以使用mysqldmpslow對(duì)日志文件進(jìn)行分類匯總，

具體使用方式可以通過mysqldmpslow --help查看使用的參數(shù)

如果有慢查詢的語句，那么該如何優(yōu)化呢

一：對(duì)數(shù)據(jù)entertime列進(jìn)行創(chuàng)建索引

二：優(yōu)化這個(gè)sql查詢語句

使用mysqldumpslow命令可以非常明確得到各種我們需要的查詢語句；對(duì)mysql查詢語句的監(jiān)控、分析、優(yōu)化是mysql非常重要的一部分。

explain 分析查詢：

使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql查詢語句；從而知道mysql是如何處理sql語句的。可以分析查詢語句或表結(jié)構(gòu)的性能瓶頸

EXPLAIN字段：

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

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

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

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

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

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

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

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

從上面的explain模擬優(yōu)化器執(zhí)行sql語句來看是沒有使用索引查詢的，而是全表掃描

從上面的explain模擬優(yōu)化器執(zhí)行sql語句看來沒有使用索引查詢，而是全表掃描

優(yōu)化方法：

顯示結(jié)果說明該查詢語句使用了index_stuname索引查詢數(shù)據(jù)而非全表掃描。

profiling分析查詢：

通過慢日志查詢可以知道那些sql語句執(zhí)行效率低，通過explain可以知道sql語句的具體執(zhí)行情況；索引等，可以通過profiling命令得到更準(zhǔn)確的sql執(zhí)行消耗系統(tǒng)資源的信息。

profiling默認(rèn)是關(guān)閉的，可通過查看的方式：

或者通過

打開profiling的功能：如下圖

接下來測(cè)試要執(zhí)行的sql語句

status:是profile里的狀態(tài)，duration：是status狀態(tài)下的耗時(shí)。因此我們關(guān)注的就是那個(gè)狀態(tài)最耗時(shí)，這些狀態(tài)中那些可以優(yōu)化。

當(dāng)然也可以查看更多的信息如CPU等等

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)閉調(diào)試功能，以免影響數(shù)據(jù)庫的正常使用：

mysql> set profiling=0;

2：配置優(yōu)化：

mysql參數(shù)優(yōu)化對(duì)不同的網(wǎng)站，及其線量，訪問量、帖子數(shù)量、網(wǎng)絡(luò)情況、以及硬件設(shè)備，都有關(guān)系，優(yōu)化不可能一次性完成，需要不斷的觀察以及調(diào)試，才能達(dá)到最佳效果。

對(duì)性能影響比較大的分為鏈接請(qǐng)求的變量和緩沖區(qū)變量

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

max_connections

mysql的最大連接數(shù)；如果服務(wù)器的并發(fā)請(qǐng)求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這是建立在服務(wù)器能夠支撐的情況之下，如果連接數(shù)越大，mysql回味每個(gè)連接提供連接緩沖區(qū)，這樣內(nèi)存的開銷會(huì)提高。所以要適當(dāng)?shù)恼{(diào)整該值。不能盲目的提高。

但是如果數(shù)值過小的話會(huì)出現(xiàn)ERROR 1040：Too many connections的錯(cuò)誤，可以通過以下的命令查看連接數(shù)啊

mysql>show variables like ‘max_connections’ 最大連接數(shù)

mysql>show  status like ‘max_used_connections’ 響應(yīng)的連接數(shù)

max_used_connections / max_connections * 100% （理想值≈ 85%） 

max_used_connections/ max_connections * 100% {理想值=85%}

那么如何設(shè)置max_xonnections？

修改/etc/my.cnf文件，在【mysqld】下面添加如下內(nèi)容，。如設(shè)置最大連接數(shù)為1024

max_connections=1024

之后重啟mysql服務(wù)

2：back_log

mysql能暫存的連接數(shù)量。當(dāng)主要mysql線程在一個(gè)很短的時(shí)間內(nèi)得到非常對(duì)的連接請(qǐng)求；它就會(huì)起作用，當(dāng)mysql；的鏈接數(shù)達(dá)到max_connections是，新的請(qǐng)求將會(huì)被存放在堆棧當(dāng)中。等待某一鏈接，釋放資源，該堆棧的數(shù)量即back_log，如果鏈接數(shù)量超過back_log，將不被授予鏈接資源

back_log值指出在mysql暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)段時(shí)間內(nèi)有多少個(gè)請(qǐng)求可以被存在堆棧中，如果期望在一個(gè)短時(shí)間內(nèi)與很多鏈接，你需要增加它

何設(shè)置back_log?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容，如設(shè)置最大連接數(shù)為1024

back_log = 數(shù)值

重啟mysql服務(wù)

3. wait_timeout和interactive_timeout

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

interactive_time -- 指的是mysql在關(guān)閉一個(gè)交互的連接之前所要等待的秒數(shù)，比如我們?cè)诮K端上進(jìn)入mysql管理，使用的即使交互的連接，這時(shí)候，如果沒有操作的時(shí)間超過了interactive_time設(shè)置的時(shí)間就會(huì)自動(dòng)斷開。默認(rèn)數(shù)值是28800，可調(diào)優(yōu)為7200。

對(duì)性能的影響：

wait_timeout：

（1）如果設(shè)置大小，那么連接關(guān)閉的很快，從而使一些持久的連接不起作用

（2）如果設(shè)置太大，容易造成連接打開時(shí)間過長(zhǎng)，在show processlist時(shí)，能看到太多的sleep狀態(tài)的連接，從而造成too many connections錯(cuò)誤

（3）一般希望wait_timeout盡可能地低

interactive_timeout的設(shè)置將要對(duì)你的web application沒有多大的影響

查看wait_timeout和interactive_timeout

mysql> show variables like '%wait_timeout%';

mysql> show variables like '%interactive_timeout%';

如何設(shè)置wait_timeout和interactive_timeout?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容

wait_timeout=100
interactive_timeout=100

重啟MySQL Server進(jìn)入后，查看設(shè)置已經(jīng)生效。

2）綬沖區(qū)變量

全局緩沖：

4.key_buffer_size

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。

一共有6個(gè)索引讀取請(qǐng)求，有3個(gè)請(qǐng)求在內(nèi)存中沒有找到直接從硬盤讀取索引，計(jì)算索引未命中緩存的概率： 
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50% 

key_buffer_size只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態(tài)值created_tmp_disk_tables得知詳情。

 如何調(diào)整key_buffer_size

默認(rèn)配置數(shù)值是8388608(8M)，主機(jī)有4GB內(nèi)存，可以調(diào)優(yōu)值為268435456(256MB)

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容

key_buffer_size=268435456或key_buffer_size=256M

重啟MySQL Server進(jìn)入后，查看設(shè)置已經(jīng)生效。

5.  query_cache_size(查詢緩存簡(jiǎn)稱QC)

使用查詢緩沖，MySQL將查詢結(jié)果存放在緩沖區(qū)中，今后對(duì)于同樣的SELECT語句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。

一個(gè)SQL查詢?nèi)绻?/span>select開頭，那么MySQL服務(wù)器將嘗試對(duì)其使用查詢緩存。

注：兩個(gè)SQL語句，只要相差哪怕是一個(gè)字符（例如大小寫不一樣；多一個(gè)空格等），那么這兩個(gè)SQL將使用不同的一個(gè)CACHE。

通過檢查狀態(tài)值’Qcache%’，可以知道query_cache_size設(shè)置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。

Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。如果該值顯示較大，則說明Query Cache 中的內(nèi)存碎片較多了，FLUSH QUERY CACHE會(huì)對(duì)緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊。 

注：當(dāng)一個(gè)表被更新之后，和它相關(guān)的cache blocks將被free。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部。可以用FLUSH QUERY CACHE語句來清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小。通過這個(gè)參數(shù)我們可以較為準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的Query Cache 內(nèi)存大小是否足夠，是需要增加還是過多了。
Qcache_hits：表示有多少次命中緩存。我們主要可以通過該值來驗(yàn)證我們的查詢緩存的效果。數(shù)字越大，緩存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新來的SQL請(qǐng)求在緩存中未找到，不得不執(zhí)行查詢處理，執(zhí)行查詢處理后把結(jié)果insert到查詢緩存中。這樣的情況的次數(shù)越多，表示查詢緩存應(yīng)用到的比較少，效果也就不理想。當(dāng)然系統(tǒng)剛啟動(dòng)后，查詢緩存是空的，這很正常。
Qcache_lowmem_prunes：多少條Query 因?yàn)閮?nèi)存不足而被清除出Query Cache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合，能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠，是否非常頻繁的出現(xiàn)因?yàn)閮?nèi)存不足而有Query 被換出。這個(gè)數(shù)字最好長(zhǎng)時(shí)間來看；如果這個(gè)數(shù)字在不斷增長(zhǎng)，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少。（上面的free_blocks和free_memory可以告訴您屬于哪種情況） 
Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。 
Qcache_queries_in_cache：當(dāng)前Query Cache 中cache 的Query 數(shù)量； 
Qcache_total_blocks：當(dāng)前Query Cache 中的block 數(shù)量；。 

我們?cè)俨樵円幌路?wù)器關(guān)于query_cache的配置：

上圖可以看出query_cache_type為ON表示緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存 
query_cache_min_res_unit：緩存塊的最小大小 ，query_cache_min_res_unit的配置是一柄”雙刃劍”，默認(rèn)是4KB，設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
query_cache_size：查詢緩存大小 (注：QC存儲(chǔ)的最小單位是1024 byte，所以如果你設(shè)定了query_cache_type：緩存類型，決定緩存什么樣的查詢，注意這個(gè)值不能隨便設(shè)置，必須設(shè)置為數(shù)字，可選項(xiàng)目以及說明如下：

如果設(shè)置為0，那么可以說，你的緩存根本就沒有用，相當(dāng)于禁用了。

如果設(shè)置為1，將會(huì)緩存所有的結(jié)果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。

如果設(shè)置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M

保存文件，重新啟動(dòng)MYSQL服務(wù)，然后通過如下查詢來驗(yàn)證開啟了：

query_cache_wlock_invalidate：當(dāng)有其他客戶端正在對(duì)MyISAM表進(jìn)行寫操作時(shí)，如果查詢?cè)?/span>query cache中，是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。  

查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 
如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。 
查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 
查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當(dāng)減??；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點(diǎn)小，要不就是碎片太多。
查詢緩存命中率 = Qcache_hits/(Qcache_hits +Qcache_inserts) * 100% 

Query Cache 的限制

a) 所有子查詢中的外部查詢SQL 不能被Cache；
b) 在Procedure，Function 以及Trigger 中的Query 不能被Cache；
c) 包含其他很多每次執(zhí)行可能得到不一樣結(jié)果的函數(shù)的Query不能被Cache。
鑒于上面的這些限制，在使用Query Cache 的過程中，建議通過精確設(shè)置的方式來使用，僅僅讓合適的表的數(shù)據(jù)可以進(jìn)入Query Cache，僅僅讓某些Query的查詢結(jié)果被Cache。

如何設(shè)置query_cache_size？

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容

query_cache_size=256M
query_cache_type=1

重啟MySQL Server進(jìn)入后，查看設(shè)置已經(jīng)生效。

6. max_connect_errors是一個(gè)MySQL中與安全有關(guān)的計(jì)數(shù)器值，它負(fù)責(zé)阻止過多嘗試失敗的客戶端以防止暴力破解密碼的情況, 當(dāng)超過指定次數(shù)，MYSQL服務(wù)器將禁止host的連接請(qǐng)求，直到mysql服務(wù)器重啟或通過flush hosts命令清空此host的相關(guān)信息。max_connect_errors的值與性能并無太大關(guān)系。

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容

max_connect_errors=20

重啟MySQL Server進(jìn)入后，查看設(shè)置已經(jīng)生效。

         MySQL性能優(yōu)化————影響性能的因素

如果將mysql服務(wù)器比作一臺(tái)跑車，那么服務(wù)器硬件就好比發(fā)動(dòng)機(jī)，引擎等公具，而里面的設(shè)施皮椅就可以比作MySQL的性能優(yōu)化，只有兩者兼?zhèn)洳潘愕纳鲜且粋€(gè)完整的跑車；

在這里我們主要針對(duì)的是對(duì)mysql的性能進(jìn)行優(yōu)化屬于剛才說的里面的設(shè)施。

包括連接數(shù)，查詢緩存等

MySQL影響性能的因素：

1.商業(yè)需求的影響

2.系統(tǒng)架構(gòu)及實(shí)現(xiàn)的影響

1）二進(jìn)制多媒體數(shù)據(jù)

2）超大文本數(shù)據(jù)

3：sql語句使用的不恰當(dāng)，以及優(yōu)化前后的變化

在數(shù)據(jù)庫中最主要的優(yōu)化包括cpu、內(nèi)存和磁盤i/o的優(yōu)化

當(dāng)然這些必須根據(jù)自身公司的服務(wù)器進(jìn)行判斷：比如CPU可以支持多核，內(nèi)存最小64GB、以上等等

例子：為查詢緩存優(yōu)化你的查詢

大多數(shù)MySQL已經(jīng)開啟了查詢緩存，這是最有效提高優(yōu)化的方法之一；很多相同的查詢執(zhí)行多次，被放到一個(gè)緩存當(dāng)中，后續(xù)得到查詢就不用操作表而直接訪問緩存當(dāng)中的數(shù)據(jù)

 query_cache_size(查詢緩存簡(jiǎn)稱QC)

注：兩個(gè)SQL語句，只要相差哪怕是一個(gè)字符（例如大小寫不一樣；多一個(gè)空格等），那么這兩個(gè)SQL將使用不同的一個(gè)CACHE。

通過檢查狀態(tài)值’Qcache%’，可以知道query_cache_size設(shè)置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。

Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。如果該值顯示較大，則說明Query Cache 中的內(nèi)存碎片較多了，FLUSH QUERY CACHE會(huì)對(duì)緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊。 

注：當(dāng)一個(gè)表被更新之后，和它相關(guān)的cache blocks將被free。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部?？梢杂?/span>FLUSH QUERY CACHE語句來清空free blocks
我們?cè)俨樵円幌路?wù)器關(guān)于query_cache的配置：

上圖可以看出query_cache_type為off表示不緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存 
query_cache_min_res_unit：緩存塊的最小大小 ，query_cache_min_res_unit的配置是一柄”雙刃劍”，默認(rèn)是4KB，設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
query_cache_size：查詢緩存大小 (注：QC存儲(chǔ)的最小單位是1024 byte，所以如果你設(shè)定了一個(gè)不是1024的倍數(shù)的值，這個(gè)值會(huì)被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。)
query_cache_type：緩存類型，決定緩存什么樣的查詢，注意這個(gè)值不能隨便設(shè)置，必須設(shè)置為數(shù)字，可選項(xiàng)目以及說明如下：

如果設(shè)置為0，那么可以說，你的緩存根本就沒有用，相當(dāng)于禁用了。

如果設(shè)置為1，將會(huì)緩存所有的結(jié)果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。

如果設(shè)置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

query_cache_wlock_invalidate：當(dāng)有其他客戶端正在對(duì)MyISAM表進(jìn)行寫操作時(shí)，如果查詢?cè)?/span>query cache中，是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。 

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M
query_cache_type=1

保存文件，重新啟動(dòng)MYSQL服務(wù)，然后通過如下查詢來驗(yàn)證是否真正開啟了：

2：explain你的select查詢

幫助你查看你的mysql是如何處理的的sql語句，分析你的查詢語句或者表結(jié)構(gòu)的性能瓶頸。

另外還會(huì)告訴你使用的是什么索引；數(shù)據(jù)表是如何被搜索的和排序的

explain分析查詢

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。通過explain命令可以得到:

> explain select * from test1.tb1 where stuname='admin'\G;

profiling分析查詢

通過慢日志查詢可以知道哪些SQL語句執(zhí)行效率低下，通過explain我們可以得知SQL語句的具體執(zhí)行情況，索引使用等，還可以結(jié)合show命令查看執(zhí)行狀態(tài)。如果覺得explain的信息不夠詳細(xì)，可以同通過profiling命令得到更準(zhǔn)確的SQL執(zhí)行消耗系統(tǒng)資源的信息。

profiling默認(rèn)是關(guān)閉的?？梢酝ㄟ^以下語句查看

mysql> show variables like '%profiling%';    //off表示未開啟

打開profiling功能： mysql>set profiling=1; 執(zhí)行需要測(cè)試的sql 語句：

mysql> select @@profiling;

+---------------------+

| @@profiling |

+---------------------+

|           1 |

+----------------------+

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

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

mysql> show profiles\G;   //可以得到被執(zhí)行的SQL語句的時(shí)間和ID

*************************** 1. row ***************************

Query_ID: 1

Duration: 0.00012650

   Query: select @@profiling

*************************** 2. row ***************************

Query_ID: 2

Duration: 0.00121725

   Query: select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

mysql> show profile for query 2;  //得到對(duì)應(yīng)SQL語句執(zhí)行的詳細(xì)信息

+----------------------+-------------------------+

| Status              | Duration |

+----------------------+-------------------------+

| starting             | 0.000230 |

| checking permissions | 0.000013 |

| Opening tables       | 0.000030 |

| init                 | 0.000087 |

| System lock          | 0.000018 |

| optimizing           | 0.000128 |

| statistics           | 0.000378 |

| preparing            | 0.000026 |

| executing            | 0.000005 |

| Sending data         | 0.000187 |

| end                  | 0.000013 |

| query end            | 0.000011 |

| closing tables       | 0.000010 |

| freeing items        | 0.000061 |

| cleaning up          | 0.000021 |

+----------------------+-------------------------+

status:是profile里的狀態(tài)，duration：是status狀態(tài)下的耗時(shí)。因此我們關(guān)注的就是那個(gè)狀態(tài)最耗時(shí)，這些狀態(tài)中那些可以優(yōu)化。

當(dāng)然也可以查看更多的信息如CPU等等

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)閉調(diào)試功能，以免影響數(shù)據(jù)庫的正常使用：

mysql> set profiling=0;

3:為搜索字段建立索引:

索引并不一定給主鍵或是字段建立，而是給經(jīng)常需要查詢的目標(biāo)建立，相當(dāng)于字典的目錄，提速高效能。提高查詢效率，快速定位數(shù)據(jù) 

索引分為四種：

 CREATE INDEX indexName ON tablename(column1[,column2,……])

 全文索引 

只用于MyISAM 表 對(duì)文本域進(jìn)行索引。字段類型包括char、varchar、text

不過切記對(duì)于大容量的數(shù)據(jù)表，生成全文索引是一個(gè)非常消耗時(shí)間非常消耗硬盤空間的做法。

CREATE FULLTEXT INDEX indexname ON tablename(column)

全局緩沖：索引緩存的大小

.key_buffer_size

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。

 如何調(diào)整key_buffer_size

默認(rèn)配置數(shù)值是8388608(8M)，主機(jī)有4GB內(nèi)存，可以調(diào)優(yōu)值為268435456(256MB)

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容

key_buffer_size=268435456或key_buffer_size=256M

innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。

4：避免select*的使用：

如果數(shù)據(jù)庫的數(shù)據(jù)過多的話使用‘*’增加查詢的時(shí)間增大cpu和i/o的負(fù)載，全表查詢而且速度慢，應(yīng)該養(yǎng)成查詢的時(shí)候制定某一個(gè)字段，

5：選擇正確的存儲(chǔ)引擎：

myisam使用與查詢大量的的應(yīng)用；有時(shí)一個(gè)update字段，可能導(dǎo)致全表鎖定，當(dāng)然在count（*）這類計(jì)算的時(shí)候是速度非常快的因?yàn)橛杏?jì)數(shù)器

innodb復(fù)雜的存儲(chǔ)引擎支持行鎖，換支持事物，不適合count（*）

6:查看慢查詢?nèi)罩荆?/span>

慢查詢?nèi)罩鹃_啟：

在配置文件my.cnf中在[mysqld]一行下面加入3個(gè)配置參數(shù)，并重啟mysql服務(wù)

slow_query_log = 1   //0關(guān)閉  1開啟
slow_query_log_file = /usr/local/mysql/data/slow-query.log    //慢查詢?nèi)罩敬娣诺攸c(diǎn)

long_query_time = 1                               //表示查詢超過1秒才記錄                                

在my.cnf中添加log-queries-not-using-indexes參數(shù)，表示向慢查詢?nèi)罩局?/span>記錄下沒有使用索引的查詢。

慢查詢?nèi)罩鹃_啟方法二：

我們也可以通過命令行設(shè)置變量來即時(shí)啟動(dòng)慢日志查詢

mysql> set global slow_query_log = on;

mysql> set long_query_time =0.01;

mysql> set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";

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

mysql> show variables like '%slow_query_log%';

mysql> show variables like '%long_query_time%';

我們可以通過打開log文件查看得知哪些SQL執(zhí)行效率低下

[root@localhost data]# cat slow-query.log 

7：大批量數(shù)據(jù)的限制：

如果大批量的添加數(shù)據(jù)會(huì)導(dǎo)致查詢效率低，還有就是數(shù)據(jù)入庫的時(shí)間長(zhǎng)，有時(shí)候會(huì)長(zhǎng)達(dá)幾個(gè)小時(shí)

max_allowed_packet = 32M

MySQL根據(jù)配置文件會(huì)限制Server接受的數(shù)據(jù)包大小。有時(shí)候大的插入和更新會(huì)受 max_allowed_packet 參數(shù)限制，導(dǎo)致寫入或者更新失敗。最大值是1GB，必須設(shè)置1024的倍數(shù)。

8：關(guān)閉交互式：

比如當(dāng)dba使用交互式的界面對(duì)數(shù)據(jù)庫進(jìn)行增、改、刪、查之后，忘記了退出數(shù)據(jù)庫的交互式頁面，如果有人看見在上面進(jìn)行操作修改數(shù)據(jù)，會(huì)為公司造成不可估量的損失，在這里我們可以通過只配置文件調(diào)整交互式存在的時(shí)間，防止其他人員進(jìn)行操作；

另外也可以釋放一個(gè)用戶的鏈接數(shù)，增大一個(gè)鏈接數(shù)量

.wait_timeout和interactive_timeout

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

interactive_time -- 指的是mysql在關(guān)閉一個(gè)交互的連接之前所要等待的秒數(shù)，比如我們?cè)诮K端上進(jìn)入mysql管理，使用的即使交互的連接，這時(shí)候，如果沒有操作的時(shí)間超過了interactive_time設(shè)置的時(shí)間就會(huì)自動(dòng)斷開。默認(rèn)數(shù)值是28800{8小時(shí)}，可調(diào)優(yōu)為7200。

9：增大用戶鏈接數(shù)：

有時(shí)候突然之間數(shù)據(jù)庫的性能變慢；鏈接客戶需要好長(zhǎng)的時(shí)間才能的到響應(yīng)，甚至有時(shí)候收不到，客戶就會(huì)不斷的進(jìn)行鏈接，這樣數(shù)據(jù)庫就更加的繁忙了，最后情況嚴(yán)重的話可能導(dǎo)致數(shù)據(jù)庫掛機(jī)，這里需要設(shè)置最大鏈接數(shù)量

1.max_connections

MySQL的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多， MySQL會(huì)為每個(gè)連接提供連接緩沖區(qū)，就會(huì)開銷越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值。

mysql>show variables like ‘max_connections’ 最大連接數(shù)

mysql>show  status like ‘max_used_connections’響應(yīng)的連接數(shù)

max_used_connections / max_connections * 100% （理想值≈ 85%） 

如果max_used_connections跟max_connections相同那么就是max_connections設(shè)置過低或者超過服務(wù)器負(fù)載上限了，低于10%則設(shè)置過大。

如何設(shè)置max_connections?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容，如設(shè)置最大連接數(shù)為1024

max_connections = 1024

10：MySQL的堆棧設(shè)置：2.back_log

如果當(dāng)鏈接數(shù)用戶過多，而且鏈接的最大數(shù)量不夠使用的時(shí)候可以設(shè)置堆棧，類似一個(gè)房間，講過多的鏈接先存放起來，等處理完之前的鏈接之后再處理房間里的鏈接，如果等待連接的數(shù)量超過back_log，將不被授予連接資源。

back_log值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)有多少個(gè)請(qǐng)求可以被存在堆棧中。只有如果期望在一個(gè)短時(shí)間內(nèi)有很多連接，你需要增加它。

當(dāng)觀察你主機(jī)進(jìn)程列表（mysql> show full processlist），發(fā)現(xiàn)大量

xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時(shí)，就要加大back_log 的值了或加大max_connections的值。

通過mysql> show variables like 'back_log';查看back_log的設(shè)置

如何設(shè)置back_log?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容，如設(shè)置最大連接數(shù)為1024

back_log = 數(shù)值{1024}

重啟mysql服務(wù)

對(duì)于以上關(guān)于mysql優(yōu)化索引、配置，及慢查詢講解，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R(shí)更新。