面試：給我說說你平時是如何優化MySQL的？

面試：給我說說你平時是如何優化MySQL的？

1、explain

在MySQL優化的時候、我們使用最多的是explain檢視 SQL 的執行計劃

類型 解釋

type 連接配接類型。一個好的SQL語句至少要達到range級别、杜絕出現all級别

key 使用到的索引名。如果沒有選擇索引，值是NULL。可以采取強制索引方式【force index】、這裡可以用force index()，這個指令可以指定本次查詢強制使用哪個索引，因為Mysql優化器的選擇并不是最優的索引。

ken_len 索引長度。

rows 掃描列數。該值是個預估值。

extra 注意常見的不太友好的值有：Using filesort, Using temporary。

具體的參考文檔：

https://www.cnblogs.com/laowenBlog/p/12640755.html

2、SQL語句中IN包含的值不應過多

MySQL對于IN做了相應的優化，即将IN中的常量全部存儲在一個數組裡面，而且這個數組是排好序的。

但是如果數值較多，産生的消耗也是比較大的。

例如：select id from table_name where num in(1,2,3) 對于連續的數值，能用 between 就不要用 in 了；

再或者使用連接配接來替換。

3、SELECT語句務必指明字段名稱

SELECT *增加很多不必要的消耗（cpu、io、記憶體、網絡帶寬）；

增加了使用覆寫索引的可能性；

當表結構發生改變時，前斷也需要更新。

是以要求直接在select 後面接上字段名。

4、當隻需要一條資料的時候，使用limit 1

這是為了使EXPLAIN中type列達到const類型

5、如果排序字段沒有用到索引，就盡量少排序

排序的字段、盡量加上索引。

6、如果限制條件中其他字段沒有索引，盡量少用or

or兩邊的字段中，如果有一個不是索引字段，而其他條件也不是索引字段，會造成該查詢不走索引的情況。

很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。

7、盡量用union all代替union

union和union all的差異主要是前者需要将結果集合并後再進行唯一性過濾操作；

這就會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲。

當然，union all的前提條件是兩個結果集沒有重複資料。

8、不使用 order by rand()

MySQL RAND()函數調用可以在0和1之間産生一個随機數

order by rand() 随機查詢取前幾條記錄。

優化前：

select id from

表名

order by rand() limit 3;

優化後：

表名

t1

join (select rand() * (select max(id) from

表名

) as nid) t2

on t1.id > t2.nid limit 3;

9、區分in和exists

select * from 表1 where id in ( select id from 表2 );

// 相當于

select from 表1 where exists ( select from 表2 where 表2.id = 表1.id );

區分 in 和 exists 主要是造成了驅動順序的改變、這是性能變化的關鍵。

如果是exists，那麼以外層表為驅動表、先被通路。

如果是in，那麼先執行子查詢。

是以：in 适合于外表大而内表小的情況。exists适合于外表小而内表大的情況。

10、區分 not in和not exists

推薦使用not exists，不僅僅是效率問題，not in可能存在邏輯問題。

// 原始SQL語句

select colname … from A表

where a.id not in (select b.id from B表)

// 優化後的SQL語句

Left join B表 on where a.id = b.id

where b.id is null

11、使用合理的分頁以提高分頁效率

// 原始SQL

select id,name from table_name limit 866613, 20;

使用如上SQL語句分頁、随着表資料量的增加，直接使用limit分頁查詢會越來越慢。

優化的方法如下：

可以取前一頁的最大行數的id，然後根據這個最大的id來限制下一頁的起點。

比如此列中，上一頁最大的id是866612。sql可以采用如下的寫法。

select id,name from table_name where id > 866612 limit 20;

12、分段查詢

在一些使用者選擇頁面中，可能一些使用者選擇的時間範圍過大，造成查詢緩慢。

主要的原因是掃描行數過多。

這個時候可以通過程式，分段進行查詢，循環周遊，将結果合并處理進行展示。

主要看資料問題：掃描的行數成百萬級以上的時候就可以使用分段查詢。

13、避免在 where 子句中對字段進行 null 值判斷

對于null的判斷會導緻引擎放棄使用索引而進行全表掃描。

14、不建議使用%字首模糊查詢

例如：LIKE “%name”或者LIKE “%name%”；

這種查詢會導緻索引失效而進行全表掃描；但是可以使用LIKE “name%”。

那如何查詢“%name%”呢？答案：使用全文索引。

在我們查詢中經常會用到

select id,fnum,fdst from table_name where user_name like '%zhangsan%';

這樣的語句，普通索引是無法滿足查詢需求的。

慶幸的是在MySQL中，有全文索引來幫助我們。

需要解決的前提條件：

表的類型必須是MyISAM、建立全文檢索的字段類型必須是char,varchar,text；

建立全文索引的sql文法是：

ALTER TABLE

table_name

ADD FULLTEXT INDEX

idx_user_name

(

user_name

);

// 或者

CREATE fulltext INDEX index_name ON table_name(colum_name);

使用全文索引的sql語句是：

// 在SELECT的WHERE字句中用MATCH函數,

// 索引的關鍵詞用AGAINST辨別,

// IN BOOLEAN MODE是隻有含有關鍵字就行,不用在乎位置,是不是起啟位置.

SELECT * FROM articles WHERE MATCH (tags) AGAINST ('旅遊' IN BOOLEAN MODE);

15、避免在where子句中對字段進行表達式操作

比如

SELECT user_id,user_project FROM table_name WHERE age*2 = 36;

中對字段就行了算術運算，這會造成引擎放棄使用索引，建議改成

SELECT user_id,user_project FROM table_name WHERE age = 36/2;

16、避免隐式類型轉換

where 子句中出現 column 字段的類型和傳入的參數類型不一緻的時候發生的類型轉換，建議先确定where中的參數類型。

17、對于聯合索引來說，要遵守最左字首法則

舉列來說索引含有字段id,name,school;

可以直接用id字段，也可以id,name這樣的順序;

但是name，school都無法使用這個索引。

是以在建立聯合索引的時候一定要注意索引字段順序，常用的查詢字段放在最前面。

18、必要時可以使用force index來強制查詢走某個索引

有的時候MySQL優化器采取它認為合适的索引來檢索sql語句，但是可能它所采用的索引并不是我們想要的。這時就可以采用force index來強制優化器使用我們制定的索引。

19、注意範圍查詢語句

對于聯合索引來說，如果存在範圍查詢，比如between,>,<等條件時，會造成後面的索引字段失效。

20、關于JOIN優化

LEFT JOIN A表為驅動表

INNER JOIN MySQL會自動找出那個資料少的表作用驅動表

RIGHT JOIN B表為驅動表

注意：MySQL中沒有full join，可以用以下方式來解決

select * from A left join B on B.name = A.name

where B.name is null

union all

select * from B;

盡量使用inner join，避免left join

參與聯合查詢的表至少為2張表，一般都存在大小之分。

如果連接配接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表；

但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。

合理利用索引：

被驅動表的索引字段作為on的限制字段。

利用小表去驅動大表

如果能夠減少驅動表的話，減少嵌套循環中的循環次數，以減少 IO總量及CPU運算的次數。

巧用STRAIGHT_JOIN

inner join是由mysql選擇驅動表，但是有些特殊情況需要選擇另個表作為驅動表；

比如有group by、order by等「Using filesort」、「Using temporary」時。

STRAIGHT_JOIN來強制連接配接順序；

在STRAIGHT_JOIN左邊的表名就是驅動表，右邊則是被驅動表。

在使用STRAIGHT_JOIN有個前提條件是該查詢是内連接配接，也就是inner join。

其他連結不推薦使用STRAIGHT_JOIN，否則可能造成查詢結果不準确。

原文位址

https://www.cnblogs.com/laowenBlog/p/12643261.html