溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“MySql性能優化實例分析”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
實例1：復合索引的最佳左前綴原則（where ... order by ...）
創建如下表和索引
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語句的執行計劃如下：（where 后面的條件與索引順序一致）
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a3=1 AND a4=1;
第二種sql語句的執行計劃如下：（where 后面的條件與索引順序不一致）
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a4=1 AND a3=1 AND a2=1 AND a1=1;
如上兩者的執行計劃一模一樣，為什么？
因為第二條sql在真正執行前經過了sql優化器的調整，所以與上條保持一致。第一種sql是推薦寫法。
再看一個如下sql語句：
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a4=1 ORDER BY a3;
我們看執行計劃的結果：使用了using where，則進行了回表查詢，索引失效。以上sql用到了a1,a2兩個索引，該兩個字段不需要回表查詢，因此是using index，而a4因為跨列使用，造成了索引失效，需要回表查詢，因此是using where，以上可以通過key_length校驗。
再看一個如下sql語句：
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=1 ORDER BY a3;
我們看到以上sql出現了using filesort(文件內排序，"多了一次額外的查找/排序")；不要跨列使用(where 和order by 拼起來，不要跨列使用)
再看一個如下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構成了連續性，所以就沒有出現using filesort。
總結：
1. 如果(a,b,c,d)復合索引和使用順序全部一致（且不跨列使用），則復合索引全部使用。如果部分一致（且不跨列使用），則使用部分索引。
2. where 和order by 拼起來，不要跨列使用
案例2：單表優化
創建如下表：
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），因為沒有使用任何索引.
<1>第一步優化，加上索引如下：
ALTER TABLE book ADD INDEX idx_bta (bid,typeid,authorid);
我們看到了type使用index，略微的有點提升，using index說明了使用了覆蓋索引，也提升了一點點。
<2>進一步優化，依據最左前綴原則優化，注意：不用的索引要刪除，避免干擾。
ALTER TABLE book ADD INDEX idx_bta1 (typeid,authorid,bid);
<3>進一步優化，因為范圍查詢typeid in (2,3)有時會失效，所以我們把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提高了兩個級別，直接有index變成了ref.
大家可以看下同時出現了using where 和 using index，為什么呢？
using where代表回原表查詢，using index代表不回原表查詢，因為type in (2,3) 讓索引失效了，所以回原表查詢。
例如以下，不使用in語句
EXPLAIN SELECT bid FROM book WHERE authorid = 1 AND typeid = 2 ORDER BY typeid DESC;
通過key_len再次證明in可以使索引失效。
小結：
a. 最佳左前綴，保持索引的定義和使用的順序一致性
b. 索引需要逐步優化
c. 將含有in的范圍查詢放在where條件的最后，防止失效
案例3：兩表優化
創建兩個表如下：
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';
假設t表10條數據，c表300條數據
現在有一個問題，索引應該往哪個表里加？
規范：小表驅動大表、索引建立在經常使用的字段上
由t表和c表的數量可以得出，t表的cid使用次數頻繁，t表cid一次要循壞300次，故使用頻繁，因此給t表的cid加索引。（一般情況下對于左外連接，給左表加索引；右外連接，給右表加索引）
當編寫..on t.cid = c.cid時，將數據量小的表放在左邊(假設此時t表數據量小)
<1>無索引優化看如下執行計劃：
可以看出extra 中的Using join buffer，說明sql寫的太爛，連MySql都看不下去了，作用是MySql引擎使用了連接緩存。
<2>由上面的規則，我們給t表中的cid加索引
ALTER TABLE teacher2 ADD INDEX index_teacher2_cid (cid);
可見t表已經提升了好幾個檔次
<3>一般where后面的字段要加索引
ALTER TABLE course2 ADD INDEX index_course2_cname(cname);
可見c表也提升了好幾個檔次。
綜上所述，就是兩表查詢的優化過程。
案例4：三表優化
規則如下：
a. 小表驅動大表
b. 索引建立在經常查詢的字段上
可按照單表，兩表的優化規則進行優化。
“MySql性能優化實例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
