本篇內(nèi)容介紹了“MySQL性能優(yōu)化實(shí)例分析”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),佳木斯企業(yè)網(wǎng)站建設(shè),佳木斯品牌網(wǎng)站建設(shè),網(wǎng)站定制,佳木斯網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,佳木斯網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

實(shí)例1：復(fù)合索引的最佳左前綴原則（where ... order by ...）

創(chuàng)建如下表和索引

CREATE TABLE test03(  a1 INT(4) NOT NULL,  a2 INT(4) NOT NULL,  a3 INT(4) NOT NULL,  a4 INT(4) NOT NULL);ALTER TABLE test03 ADD INDEX idx_a1_a2_a3_a4(a1,a2,a3,a4);

看第一種sql語句的執(zhí)行計(jì)劃如下：（where 后面的條件與索引順序一致）

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a3=1 AND a4=1;

第二種sql語句的執(zhí)行計(jì)劃如下：（where 后面的條件與索引順序不一致）

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a4=1 AND a3=1 AND a2=1 AND a1=1;

如上兩者的執(zhí)行計(jì)劃一模一樣，為什么？

因?yàn)榈诙lsql在真正執(zhí)行前經(jīng)過了sql優(yōu)化器的調(diào)整，所以與上條保持一致。第一種sql是推薦寫法。

再看一個(gè)如下sql語句：

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a4=1 ORDER BY a3;

我們看執(zhí)行計(jì)劃的結(jié)果：使用了using where，則進(jìn)行了回表查詢，索引失效。以上sql用到了a1,a2兩個(gè)索引，該兩個(gè)字段不需要回表查詢，因此是using index，而a4因?yàn)榭缌惺褂茫斐闪怂饕?，需要回表查詢，因此是using where，以上可以通過key_length校驗(yàn)。

再看一個(gè)如下sql語句：

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=1 ORDER BY a3;

我們看到以上sql出現(xiàn)了using filesort(文件內(nèi)排序，"多了一次額外的查找/排序")；不要跨列使用(where 和order by 拼起來，不要跨列使用)

再看一個(gè)如下sql語句：

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=1 ORDER BY a2,a3;

我們可以看到where后面的a1 和order by 后面a2,a3構(gòu)成了連續(xù)性，所以就沒有出現(xiàn)using filesort。

總結(jié)：

1. 如果(a,b,c,d)復(fù)合索引和使用順序全部一致（且不跨列使用），則復(fù)合索引全部使用。如果部分一致（且不跨列使用），則使用部分索引。

2. where 和order by 拼起來，不要跨列使用

案例2：單表優(yōu)化

創(chuàng)建如下表：

CREATE TABLE book(  bid INT(4) PRIMARY KEY,  NAME VARCHAR(20) NOT NULL,  authorid INT(4) NOT NULL,  publicId INT(4) NOT NULL,  typeid INT(4) NOT NULL);INSERT INTO book VALUES(1,'tjava',1,1,2);INSERT INTO book VALUES(2,'tc',2,1,2);INSERT INTO book VALUES(3,'wx',3,2,1);INSERT INTO book VALUES(4,'math',4,2,3);

查詢authorid=1 且 typeid 為2或者3的bid

EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;

我們看到了恐怖的事情（type 為ALL），因?yàn)闆]有使用任何索引.

<1>第一步優(yōu)化，加上索引如下：

ALTER TABLE book ADD INDEX idx_bta (bid,typeid,authorid);

我們看到了type使用index，略微的有點(diǎn)提升，using index說明了使用了覆蓋索引，也提升了一點(diǎn)點(diǎn)。

<2>進(jìn)一步優(yōu)化，依據(jù)最左前綴原則優(yōu)化，注意：不用的索引要刪除，避免干擾。

ALTER TABLE book ADD INDEX idx_bta1 (typeid,authorid,bid);

<3>進(jìn)一步優(yōu)化，因?yàn)榉秶樵僼ypeid in (2,3)有時(shí)會失效，所以我們把typeid 放在后面：

ALTER TABLE book ADD INDEX idx_bta1 (authorid,typeid,bid);EXPLAIN SELECT bid FROM book WHERE  authorid = 1 AND typeid IN (2,3) ORDER BY typeid DESC;

我們可以看出，type提高了兩個(gè)級別，直接有index變成了ref.

大家可以看下同時(shí)出現(xiàn)了using where 和 using index，為什么呢？

using where代表回原表查詢，using index代表不回原表查詢，因?yàn)閠ype in (2,3) 讓索引失效了，所以回原表查詢。

例如以下，不使用in語句

EXPLAIN SELECT bid FROM book WHERE  authorid = 1 AND typeid = 2 ORDER BY typeid DESC;

通過key_len再次證明in可以使索引失效。

小結(jié)：

a. 最佳左前綴，保持索引的定義和使用的順序一致性

b. 索引需要逐步優(yōu)化

c. 將含有in的范圍查詢放在where條件的最后，防止失效

案例3：兩表優(yōu)化

創(chuàng)建兩個(gè)表如下：

CREATE TABLE teacher2(  tid INT(4) PRIMARY KEY,  cid INT(4) NOT NULL);
INSERT INTO teacher2 VALUES(1,2);INSERT INTO teacher2 VALUES(2,1);INSERT INTO teacher2 VALUES(3,3);
CREATE TABLE course2(  cid INT(4),  cname VARCHAR(20));
INSERT INTO course2 VALUES(1,'java');INSERT INTO course2 VALUES(2,'python');INSERT INTO course2 VALUES(3,'koltin');

看如下sql查詢：

SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';

假設(shè)t表10條數(shù)據(jù)，c表300條數(shù)據(jù)

現(xiàn)在有一個(gè)問題，索引應(yīng)該往哪個(gè)表里加？

規(guī)范：小表驅(qū)動大表、索引建立在經(jīng)常使用的字段上

由t表和c表的數(shù)量可以得出，t表的cid使用次數(shù)頻繁，t表cid一次要循壞300次，故使用頻繁，因此給t表的cid加索引。（一般情況下對于左外連接，給左表加索引；右外連接，給右表加索引）

當(dāng)編寫..on t.cid = c.cid時(shí)，將數(shù)據(jù)量小的表放在左邊(假設(shè)此時(shí)t表數(shù)據(jù)量小)

<1>無索引優(yōu)化看如下執(zhí)行計(jì)劃：

可以看出extra 中的Using join buffer，說明sql寫的太爛，連MySql都看不下去了，作用是MySql引擎使用了連接緩存。

<2>由上面的規(guī)則，我們給t表中的cid加索引

ALTER TABLE teacher2 ADD INDEX index_teacher2_cid (cid);

可見t表已經(jīng)提升了好幾個(gè)檔次

<3>一般where后面的字段要加索引

ALTER TABLE course2 ADD INDEX index_course2_cname(cname);

可見c表也提升了好幾個(gè)檔次。

綜上所述，就是兩表查詢的優(yōu)化過程。

案例4：三表優(yōu)化

規(guī)則如下：

a. 小表驅(qū)動大表

b. 索引建立在經(jīng)常查詢的字段上

可按照單表，兩表的優(yōu)化規(guī)則進(jìn)行優(yōu)化。

“MySql性能優(yōu)化實(shí)例分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

當(dāng)前題目：MySql性能優(yōu)化實(shí)例分析
URL網(wǎng)址：http://chinadenli.net/article14/gidgge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、標(biāo)簽優(yōu)化、域名注冊、做網(wǎng)站、網(wǎng)站內(nèi)鏈、ChatGPT

廣告

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