MySql資料庫最佳化的幾條核心建議

1. 為什麼要進行資料庫最佳化

1. 避免網站頁面出現訪問錯誤

• 由於資料庫連線timeout產生頁面5xx錯誤 , to many connection
• 由於慢查詢造成頁面無法載入 : 由於資料庫無法及時的返回資料, 導致頁面一直無法返回
• 由於阻塞造成資料無法提交 :

• 很多資料庫問題都是由於低效的查詢引起的

• 流暢頁面的訪問速度
• 良好的網站功能體驗

2. MySql資料庫最佳化

可以從哪幾個方面進行資料庫的最佳化？如下圖所示：

1. SQL及索引最佳化

   根據需求寫出良好的SQL，並建立有效的索引，實現某一種需求可以多種寫法，這時候我們就要選擇一種效率最高的寫法。這個時候就要了解sql最佳化

2. 資料庫表結構最佳化

   根據資料庫的正規化，設計表結構，表結構設計的好直接關係到寫SQL語句。

3. 系統配置最佳化

   大多數執行在Linux機器上，如tcp連線數的限制、開啟檔案數的限制、安全性的限制，因此我們要對這些配置進行相應的最佳化。

4. 硬體配置最佳化

   選擇適合資料庫服務的cpu，更快的IO，更高的記憶體；cpu並不是越多越好，某些資料庫版本有最大的限制，ＩＯ操作並不是減少阻塞。

注：透過上圖可以看出，該金字塔中，最佳化的成本從下而上逐漸增高，而最佳化的效果會逐漸降低。

3. SQL及索引最佳化

3.1 準備資料

在mysql官網中, 給我們提供了一個例項資料庫, 直接將這個例項資料庫匯入即可

1. 頁面地址:  https://dev.mysql.com/doc/sakila/en/sakila-installation.html

1. 點選例項資料下載地址, 下載即可

1. 關於sakila-db.zip壓縮包所包含的檔案如下解釋

sakila-schema.sql檔案包含建立Sakila資料庫結構所需的所有CREATE語句，包括表、檢視、儲存過程和觸發器。
sakila-data.sql檔案包含填充sakila-schema建立的結構所需的INSERT語句。以及必須在初始資料載入之後建立的觸發器的定義。
sakila.mwb檔案是一個MySQL工作臺資料模型，您可以在MySQL Workbench中開啟它來檢查資料庫結構。

1. 將 sakila-db.zip 上傳到linux, 並解壓

使用rz命令上傳即可: 如果使用rz顯示沒有此命令, 輸入 yum -y install lrzsz 下載即可

由於是一個zip檔案, 需要下載 unzip  才能解壓壓縮包:  yum -y install unzip

解壓壓縮包: unzip sakila-db.zip

1. 在資料庫中建立對應表和庫

連線資料庫: shell> mysql -u root -p123456
匯入資料:  mysql> SOURCE /root/sakila-db/sakila-schema.sql;

1. 匯入資料

mysql> SOURCE /root/sakila-db/sakila-data.sql;

1. 驗證是否成功

USE sakila;  # 使用資料庫
SHOW TABLES; # 檢視所有表

SELECT COUNT(*) FROM film;

SELECT COUNT(*) FROM film_text;

3.2 檢視錶結構

可透過sqlyog檢視基本結構關係

3.3 如何發現有問題的SQL

mysql提供了慢查詢日誌檢視功能, 可以幫助查詢某一條SQL執行的一些狀態

1. 查詢mysql是否開啟慢查詢日誌

show variables like 'slow_query_log';

1. 開啟mysql的慢查詢日誌:

show variables like 'slow_query_log'  ;
//檢視是否開啟慢查詢日誌

set global log_queries_not_using_indexes=on; // 將不使用索引的慢查詢日誌進行記錄
set global slow_query_log=on;  //開啟慢查詢日誌

show variables like 'slow_query_log_file';
//檢視慢查詢日誌儲存的位置

set global long_query_time=1;  
//大於1秒鐘的資料記錄到慢日誌中，如果設定為預設0，則會有大量的資訊儲存在磁碟中，磁碟很容易滿掉

1. 檢測是否已經開啟:

Show databases;
Use sakila;
select * from store;  // 執行查詢
select * from staff;  // 執行查詢

監聽日誌檔案，看是否寫入 : 注意  後面的日誌檔案的路徑需要透過查詢slow_query_log_file引數

tail -100f /var/lib/mysql/node01-slow.log

mysql慢查詢日誌儲存的格式說明:

說明：
 1、# Time: 190412 22:05:02    >>>      查詢的執行時間
 2、# User@Host: root[root] @ localhost []  Id:     2 >>> 執行sql的主機資訊
 3、# Query_time: 0.001274  Lock_time: 0.000164 Rows_sent: 7  Rows_examined: 7   >>>> SQL的執行資訊：
   Query_time：SQL的查詢時間
   Lock_time：鎖定時間
   Rows_sent：所傳送的行數
   Rows_examined：所掃描的行數
 4、SET timestamp=1555077861; >>>> SQL執行時間
 5、select * from shop;  >>>>> SQL的執行內容

如何透過慢查日誌發現有問題的SQL

1. 查詢次數多且每次查詢佔用時間長的sql

• 通常為pt-query-digest分析的前幾個查詢；該工具可以很清楚的看出每個SQL執行的次數及百分比等資訊，執行的次數多，佔比比較大的SQL

• 注意pt-query-digest分析中的Rows examine項。掃描的行數越多，IO越大。

• 注意pt-query-digest分析中的Rows examine 和Rows Send的對比。說明該SQL的索引命中率不高，對於這種SQL，我們要重點進行關注。

4.MySQL慢查日誌分析工具

4.1 mysqldumpslow

如何進行檢視慢查詢日誌，如果開啟了慢查詢日誌，就會生成很多的資料，然後我們就可以透過對日誌的分析，生成分析報表，然後透過報表進行最佳化。

• 如何使用此工具:
• 可透過命令 : mysqldumpslow -h  檢視使用教程

mysqldumpslow -v  : 檢視verbose(詳細配置)資訊

mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log : 檢視慢查詢日誌的前10個分析結果

上圖兩條就是分析的結果，每條結果都顯示是執行時間，鎖定時間，傳送的行數，掃描的行數

這個工具是最常用的工具，透過安裝mysql進行附帶安裝，但是該工具統計的結果比較少，對我們的最佳化鎖表現的資料還是比較少. (可以看下這個: pt-query-digest)

5. 透過explain查詢分析SQL的執行計劃

explain : SQL的執行計劃側面反映出了SQL的執行效率，具體執行方式只需要在執行的SQL前面加上explain關鍵詞即可。

• 各個欄位說明

1. id列數字越大越先執行，如果說數字一樣大，那麼就從上往下依次執行，id列為null的就表是這是一個結果集，不需要使用它來進行查詢。

2. select_type列常見的有：

   A：simple：表示不需要union操作或者不包含子查詢的簡單select查詢。有連線查詢時，外層的查詢為simple，且只有一個

   B：primary：一個需要union操作或者含有子查詢的select，位於最外層的單位查詢的select_type即為primary。且只有一個

   C：union：union連線的兩個select查詢，第一個查詢是dervied派生表，除了第一個表外，第二個以後的表select_type都是union

   D：dependent union：與union一樣，出現在union 或union all語句中，但是這個查詢要受到外部查詢的影響

   E：union result：包含union的結果集，在union和union all語句中,因為它不需要參與查詢，所以id欄位為null

   F：subquery：除了from子句中包含的子查詢外，其他地方出現的子查詢都可能是subquery

   G：dependent subquery：與dependent union類似，表示這個subquery的查詢要受到外部表查詢的影響

   H：derived：from字句中出現的子查詢，也叫做派生表，其他資料庫中可能叫做內聯檢視或巢狀select

3. table

   顯示的查詢表名，如果查詢使用了別名，那麼這裡顯示的是別名，如果不涉及對資料表的操作，那麼這顯示為null，如果顯示為尖括號括起來的

4. type

   依次從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引

   A：system：表中只有一行資料或者是空表，且只能用於myisam和memory表。如果是Innodb引擎表，type列在這個情況通常都是all或者index

   B：const：使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時，通常type是const。其他資料庫也叫做唯一索引掃描

   C：eq_ref：出現在要連線過個表的查詢計劃中，驅動表只返回一行資料，且這行資料是第二個表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時，只有所有的列都用作比較時才會出現eq_ref

   D：ref：不像eq_ref那樣要求連線順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時就可能出現，常見與輔助索引的等值查詢。或者多列主鍵、唯一索引中，使用第一個列之外的列作為等值查詢也會出現，總之，返回資料不唯一的等值查詢就可能出現。

   E：fulltext：全文索引檢索，要注意，全文索引的優先順序很高，若全文索引和普通索引同時存在時，mysql不管代價，優先選擇使用全文索引

   F：ref_or_null：與ref方法類似，只是增加了null值的比較。實際用的不多。

   G：unique_subquery：用於where中的in形式子查詢，子查詢返回不重複值唯一值

   H：index_subquery：用於in形式子查詢使用到了輔助索引或者in常數列表，子查詢可能返回重複值，可以使用索引將子查詢去重。

   I：range：索引範圍掃描，常見於使用>,<,is null,between ,in ,like等運算子的查詢中。

   J：index_merge：表示查詢使用了兩個以上的索引，最後取交集或者並集，常見and ，or的條件使用了不同的索引，官方排序這個在ref_or_null之後，但是實際上由於要讀取所個索引，效能可能大部分時間都不如range

   K：index：索引全表掃描，把索引從頭到尾掃一遍，常見於使用索引列就可以處理不需要讀取資料檔案的查詢、可以使用索引排序或者分組的查詢。

   L：all：這個就是全表掃描資料檔案，然後再在server層進行過濾返回符合要求的記錄。

5. possible_keys : 在查詢一條SQL的時候, 這個SQL有可能會使用到多個索引,但是SQL在執行的時候, 只會選擇其中的一個索引來使用

   查詢可能使用到的索引都會在這裡列出來

6. key : 兩個以上的索引稱為複合索引

   查詢真正使用到的索引，select_type為index_merge時，這裡可能出現兩個以上的索引，其他的select_type這裡只會出現一個。

7. key_len

   用於處理查詢的索引長度，如果是單列索引，那就整個索引長度算進去，如果是多列索引，那麼查詢不一定都能使用到所有的列，具體使用到了多少個列的索引，這裡就會計算進去，沒有使用到的列，這裡不會計算進去。留意下這個列的值，算一下你的多列索引總長度就知道有沒有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不會計入其中。另外，key_len只計算where條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到key_len中。

8. ref

   如果是使用的常數等值查詢，這裡會顯示const，如果是連線查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯欄位，如果是條件使用了表示式或者函式，或者條件列發生了內部隱式轉換，這裡可能顯示為func

9. rows

   這裡是執行計劃中估算的掃描行數，不是精確值

10. extra

    這個列可以顯示的資訊非常多，有幾十種，常用的有

    A：distinct：在select部分使用了distinc關鍵字

    B：no tables used：不帶from字句的查詢或者From dual查詢

    C：使用not in()形式子查詢或not exists運算子的連線查詢，這種叫做反連線。即，一般連線查詢是先查詢內表，再查詢外表，反連線就是先查詢外表，再查詢內表。

    D：using filesort：排序時無法使用到索引時，就會出現這個。常見於order by和group by語句中

    E：using index：查詢時不需要回表查詢，直接透過索引就可以獲取查詢的資料。

    F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之後的版本最佳化關聯查詢的BNL，BKA特性。主要是減少內表的迴圈數量以及比較順序地掃描查詢。

    G：using sort_union，using_union，using intersect，using sort_intersection：

    using intersect：表示使用and的各個索引的條件時，該資訊表示是從處理結果獲取交集

    using union：表示使用or連線各個使用索引的條件時，該資訊表示從處理結果獲取並集

    using sort_union和using sort_intersection：與前面兩個對應的類似，只是他們是出現在用and和or查詢資訊量大時，先查詢主鍵，然後進行排序合併後，才能讀取記錄並返回。

    H：using temporary：表示使用了臨時表儲存中間結果。臨時表可以是記憶體臨時表和磁碟臨時表，執行計劃中看不出來，需要檢視status變數，used_tmp_table，used_tmp_disk_table才能看出來。

    I：using where：表示儲存引擎返回的記錄並不是所有的都滿足查詢條件，需要在server層進行過濾。查詢條件中分為限制條件和檢查條件，5.6之前，儲存引擎只能根據限制條件掃描資料並返回，然後server層根據檢查條件進行過濾再返回真正符合查詢的資料。5.6.x之後支援ICP特性，可以把檢查條件也下推到儲存引擎層，不符合檢查條件和限制條件的資料，直接不讀取，這樣就大大減少了儲存引擎掃描的記錄數量。extra列顯示using index condition

    J：firstmatch(tb_name)：5.6.x開始引入的最佳化子查詢的新特性之一，常見於where字句含有in()型別的子查詢。如果內表的資料量比較大，就可能出現這個

    K：loosescan(m..n)：5.6.x之後引入的最佳化子查詢的新特性之一，在in()型別的子查詢中，子查詢返回的可能有重複記錄時，就可能出現這個

除了這些之外，還有很多查詢資料字典庫，執行計劃過程中就發現不可能存在結果的一些提示資訊

1. filtered

   使用explain extended時會出現這個列，5.7之後的版本預設就有這個欄位，不需要使用explain extended了。這個欄位表示儲存引擎返回的資料在server層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。

6. 具體慢查詢最佳化的案例

6.1 函式 max() 的最佳化

例如: 查詢訂單的最後支付時間

 select max(payment_date) from payment;  

最佳化前：

檢視執行計劃 : explain select max(payment_date) from payment;

可以看到顯示的執行計劃，並不是很高效，可以拖慢伺服器的效率，如何最佳化了？

建立索引 :  create index inx_paydate on payment(payment_date);

索引是順序操作的，不需要掃描表，執行效率就會比較恆定！

• 6.2) 函式 count(*) 的最佳化

需求：在一條SQL中同時查詢2006年和2007年電影的數量

錯誤寫法 :

select count(*) from film where release_year='2006' or release_year='2007';

這種查詢只能查詢出2006年和2007年的總數量, 但是2006年和2007年分別是多少無法判斷出來

正確的編寫方式:

SELECT release_year, COUNT(*) FROM film GROUP BY release_year HAVING release_year='2006' OR release_year='2007';

區別：count（*）和count（id）

 create table t(id int);
 
 insert into t values(1),(2),(null);

count（*）：select count(*)from t;

count（id）：select count(id)from t;

說明：

count（id）是不包含null的值

count（*）是包含null的值

在建立表的時候, 儘可能的使用not null 的約束, 保證每一個欄位預設值不是null值, 這樣在執行count操作的時候, 就可以直接統計某一個欄位的內容, 而不是統計所有的欄位個數。

6.3 子查詢的最佳化

子查詢是我們在開發過程中經常使用的一種方式，在通常情況下，需要把子查詢最佳化為join查詢但在最佳化是需要注意關聯鍵是否有一對多的關係，要注意重複資料。

在編寫程式碼的時候, 如果使用子查詢可能會比多表連線查詢更加的簡單, 所以在開發階段, 可以使用子查詢, 只要能夠滿足需要就可以,但是在正式上線的商業化使用的時候, 儘可能將子查詢更改jon查詢會更好的一點,join查詢的效率要比子查詢效率更高

檢視我們所建立的t表：

create table t1(tid int);

接下來我們建立一個t1表

create table t1(tid int);

insert into t1 values(1);

我們要進行一個子查詢，需求：查詢t表中id在t1表中tid的所有資料；

select * from t where t.id in (select t1.tid from t1);

接下來我們用join的操作來進行操作

select id from t join t1 on t.id =t1.tid;

透過上面結果來看，查詢的結果是一致的，我們就將子查詢的方式最佳化為join操作。

接下來，我們在t1表中再插入一條資料

insert into t1 values (1);
select * from t1;

在這種情況下，如果我們使用子查詢方式進行查詢，返回的結果就是如下圖所示：

如果使用join方式進行查詢，如下圖所示：

在這種情況下出現了一對多的關係，會出現資料的重複，我們為了方式資料重複，不得不使用distinct關鍵詞進行去重操作

注意：這個一對多的關係是我們開發過程中遇到的一個坑，出現資料重複，需要大家注意一下。

總結: 需要將子查詢更為 join 查詢，效率比較高的

6.4 group by的最佳化

需求：每個演員所參演影片的數量-（影片表和演員表）

explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id;

最佳化後的SQL：

explain select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join ( select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c using(actor_id);

說明：從上面的執行計劃來看，這種最佳化後的方式沒有使用臨時檔案和檔案排序的方式了，取而代之的是使用了索引。查詢效率老高了。

這個時候我們表中的資料比較大，會大量的佔用IO操作，最佳化了sql執行的效率，節省了伺服器的資源，因此我們就需要最佳化。

注意：

mysql 中using關鍵詞的作用：也就是說要使用using，那麼表a和表b必須要有相同的列。

using(id)  類似於: on a.id = b.id

在用Join進行多表聯合查詢時，我們通常使用On來建立兩個表的關係。其實還有一個更方便的關鍵字，那就是using。

如果兩個表的關聯欄位名是一樣的，就可以使用Using來建立關係，簡潔明瞭。

6.5 Limit查詢的最佳化

Limit常用於分頁處理，時長會伴隨order by從句使用，因此大多時候使用Filesorts這樣會造成大量的IO問題。

需求：查詢影片id和描述資訊，並根據主題進行排序，取出從序號50條開始的5條資料。

select film_id,description from sakila.film order by title limit 50,5;

在檢視一下它的執行計劃：

對於這種操作，我們該用什麼樣的最佳化方式了？

• 最佳化步驟1：

使用有索引的列或主鍵進行order by操作，因為大家知道，innodb是按照主鍵的邏輯順序進行排序的。可以避免很多的IO操作。

最佳化1:

select film_id,description from sakila.film order by film_id limit 50,5;

檢視一下執行計劃

那如果我們獲取從500行開始的5條記錄，執行計劃又是什麼樣的了？

explain select film_id,description from sakila.film order by film_id limit 500,5

隨著我們翻頁越往後，IO操作會越來越大的，如果一個表有幾千萬行資料，翻頁越後面，會越來越慢，因此我們要進一步的來最佳化。

• 最佳化步驟2

記錄上次返回的主鍵， 在下次查詢時使用主鍵過濾。（說明：避免了資料量大時掃描過多的記錄）

上次limit是50,5的操作，因此我們在這次最佳化過程需要使用上次的索引記錄值

最佳化2:

select film_id,description from sakila.film  where film_id >55 and film_id<=60 order by film_id limit 0,5;

結論：掃描行數不變，執行計劃是很固定，效率也是很固定的

注意事項：

主鍵要順序排序並連續的，如果主鍵中間空缺了某一列，或者某幾列，會出現列出資料不足5行的資料；如果不連續的情況，建立一個附加的列index_id列，保證這一列資料要自增的，並新增索引即可。

總結:

儘可能避免 filesorts(檔案排序) 和 row的掃描(越少越好)

因為這兩種操作都會增加io操作