mysql優化篇（基於索引）

一寸HUI發表於2020-10-22
MySql優化索引

在上一篇文章：Mysql索引（一篇就夠le） 中介紹了索引的基本使用，分類和原理，也強烈建議先讀Mysql索引（一篇就夠le），然後繼續本文的閱讀

我們也知道mysql的優化可以從很多的方面進行，比如硬體，換個ssd的磁碟也很爽，環境的搭建，比如讀寫分離等，引數的配置，表結構的設計，欄位型別的合理選用，sql的正確書寫方式，索引的構建等，這裡我們主要從sql的查詢做優化，主要是基於走索引的方式去學習。

一、優化能夠帶來什麼

優化能帶來的肯定是爽！！！當你作為一個使用者去體驗某個網頁正在載入中。。。內心肯定是崩潰的，秒出的網頁肯定是爽。做開發測試的是每次查詢幾分鐘甚至十幾分鍾，內心不會崩潰，只想砸電腦，所以速度快就是爽。速度快了，使用者爽了，你公司爽了，你老闆爽了，你也就爽了，薪資說不定也爽了。上面我們提到了優化可以從很多個方面進行：最常見的就是sql和索引的優化了，因為寫CURD的小夥伴都免不了寫個查詢語句，然後語句走不走索引呢，這也會決定你爽不爽了，這種層次的優化相對成本低，效果好，主要還是根據業務場景來，不像msyql環境，配置啊等，因為會有很多個業務呀，不可能讓環境是合適所有的業務場景吧，頂多有個合理居中值。（瞎扯，編不下去了。。。不過確實能帶來爽和成就感）

二、優化思路

既然要做優化，首先要知道哪些需要優化吧，然後做分析這個sql慢是因為什麼，然後才是怎麼解決呢，在實際的環境中可能先要定位慢的語句，然後觀察一段時間是不是一直都慢呢，還是有時候慢有時候又很快呢，這些都和實際環境中的併發，msyql環境當時的健康程度有關吧，所以要先鎖定目標然後觀察，然後把那些都慢的sql都弄出來進行分析，最後做優化。這裡會說一下怎麼找到慢的sql查詢和分析，優化交給下一節。

2.1、慢查詢語句（mysqldumpslow）

mysql中有很多的日誌檔案，binlog日誌，慢日誌，查詢日誌，錯誤日誌。這裡我們要說的是慢日誌，預設情況下是沒有開始慢日誌查詢日誌的，因為會影響一些些效能。（慘遭打臉，自己玩的竟突開啟了）

show variables like '%slow_query_log%';

  雖然是開啟了慢日誌查詢的功能，還得要有個閾值判斷執行多久是慢日誌查詢吧（預設10s）： show variables like '%long_query_time%';

   太長了，我們改改，10s一點都不爽；set global long_query_time=3;改完配置後記得重啟session。來測試：

   上圖是弄了一個超過3s的執行語句，檢視慢日誌的檔案，確實被記錄在裡頭了，還包括了很多其他的值，比如時間，使用者，主機，執行時間等。

那如果這個慢日誌的檔案很大很大，很多的查詢，不可能這麼手動的去找吧。所有就要有慢日誌的分析工具了：mysqldumpslow

[root@lgh mysql]# ./bin/mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  #按照某種方式排序，預設at
                al: average lock time #平均鎖定時間
                ar: average rows sent #平均返回記錄數
                at: average query time #平均查詢時間
                 c: count #訪問次數
                 l: lock time #鎖定時間
                 r: rows sent #返回記錄
                 t: query time #查詢時間
  -r           reverse the sort order (largest last instead of first) #倒序
  -t NUM       just show the top n queries  #topN
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string  #正則
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time #總時間（包括鎖定時間）

./bin/mysqldumpslow -s t -t 3 /apps/mysql8/logs/mysql/slow3306.log

./bin/mysqldumpslow -s t -t 2 -r /apps/mysql8/logs/mysql/slow3306.log

  用起來不是很難，當然還有很多其他的慢日誌的一些工具，比如：mysqlsla，pt-query-digest等，這些都需要自己去安裝的。

2.2、語句分析

當我們找到了我們的哪些慢查詢後怎麼去分析呢，當然是藉助explain啦，當然還有個帥氣的小夥伴，show profile，接下來我們就一一介紹。

2.2.1、explain

1、explain可以用來幹嘛呢？

  1. 可以知道表的執行順序
  2. 在sql中哪些索引可以使用
  3. 在sql中哪些索引實際上被用
  4. 資料讀取操作的的操作型別
  5. sql中的每個表有多少行資料被優化器查詢
  6. 表之間的引用

看完上面可能一臉懵逼吧，那就再看一遍，然後繼續往下看就懂了

2、explain怎麼玩呢？

語法：explain +  sql查詢

explain select * from item_description td inner join (select * from item_general where item_id in (select item_id from item where item_id > 332604504321036693 and item_id < 332604504321036710)) tt on tt.item_id=td.item_id;

  如上我們先看一個explain+sql；

下面看下explain的具體說明：

1、id：select查詢的序號列，表示查詢select語句中表的執行順序

  • 當id相同，則從上往下執行
  • 如果id不同，則從大到小的順序執行
  • 如果id有相同的也有不同的，按照上面兩個規則執行，先大到小，先上到下

2、select_type：表示SELECT語句的型別。它可以是以下幾種取值：

  • SIMPLE:表示簡單查詢，其中不包括連線查詢和子查詢；
  • PRIMARY:表示主查詢，或者是最外層的查詢語句，最外層查詢為PRIMARY，也就是最後載入的就是PRIMARY；
  • UNION:表示連線查詢的第2個或後面的查詢語句， 不依賴於外部查詢的結果集
  • DEPENDENT UNION:連線查詢中的第2個或後面的SELECT語句，依賴於外面的查詢；
  • UNION RESULT:連線查詢的結果；
  • SUBQUERY:子查詢中的第1個SELECT語句；不依賴於外部查詢的結果集
  • DEPENDENT SUBQUERY:子查詢中的第1個SELECT，依賴於外面的查詢；
  • DERIVED:匯出表的SELECT（FROM子句的子查詢）,MySQL會遞迴執行這些子查詢，把結果放在臨時表裡。
  • DEPENDENT DERIVED:派生表依賴於另一個表
  • MATERIALIZED:物化子查詢
  • UNCACHEABLE SUBQUERY:子查詢，其結果無法快取，必須針對外部查詢的每一行重新進行評估
  • UNCACHEABLE UNION:UNION中的第二個或隨後的 select 查詢，屬於不可快取的子查詢

3、table:表示查詢的表

4、partitions:查詢將從中匹配記錄的分割槽。該值適用NULL於未分割槽的表

5、type:表示表的連線型別

  • system:該表是僅有一行的系統表。這是const連線型別的一個特例
  • const: 資料表最多隻有一個匹配行，它將在查詢開始時被讀取，並在餘下的查詢優化中作為常量對待。const表查詢速度很快，因為只讀取一次,const用於使用常數值比較PRIMARY KEY或UNIQUE索引的所有部分的場合。
  • eq_ref:對於每個來自前面的表的行組合，從該表中讀取一行,可以用於使用=運算子進行比較的索引列 。比較值可以是常量，也可以是使用在此表之前讀取的表中列的表示式
  • ref:對於來自前面的表的任意行組合，將從該表中讀取所有匹配的行，ref可以用於使用“＝”或“＜＝＞”操作符的帶索引的列。
  • fulltext:使用FULLTEXT 索引執行聯接
  • ref_or_null:這種連線型別類似於ref，但是除了MySQL還會額外搜尋包含NULL值的行。此聯接型別優化最常用於解析子查詢
  • index_merge:此聯接型別指示使用索引合併優化。在這種情況下，key輸出行中的列包含使用的索引列表，並key_len包含使用的索引 的最長鍵部分的列表
  • unique_subquery:型別替換 以下形式的eq_ref某些 IN子查詢,unique_subquery 只是一個索引查詢函式，它完全替代了子查詢以提高效率。
  • index_subquery:連線型別類似於 unique_subquery。它代替IN子查詢,但只適合子查詢中的非唯一索引
  • range:只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。當使用＝、＜＞、＞、＞＝、＜、＜＝、IS NULL、＜＝＞、BETWEEN或者IN操作符用常量比較關鍵字列時，型別為range
  • index:該index聯接型別是一樣的 ALL，只是索引樹被掃描。這發生兩種方式：1、如果索引是查詢的覆蓋索引，並且可用於滿足表中所需的所有資料，則僅掃描索引樹。在這種情況下，Extra列顯示為 Using index，2、使用對索引的讀取執行全表掃描，以按索引順序查詢資料行。 Uses index沒有出現在 Extra列中。
  • ALL:對於前面的表的任意行組合進行完整的表掃描
  • system>const>eq_ref>ref>index>ALL(這些比較常見，)

6、possible_keys:指出MySQL能使用哪個索引在該表中找到行。若該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看它是否引用某些列或適合索引的列來提高查詢效能。如果是這樣，可以建立適合的索引來提高查詢的效能。

7、kye:表示查詢實際使用的索引，如果沒有選擇索引，該列的值是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

8、key_len：表示MySQL選擇的索引欄位按位元組計算的長度，若鍵是NULL，則長度為NULL。注意，通過key_len值可以確定MySQL將實際使用一個多列索引中的幾個欄位

9、ref：表示使用哪個列或常數與索引一起來查詢記錄。

10、rows：顯示MySQL在表中進行查詢時必須檢查的行數。

11、filtered：按表條件篩選的行的百分比

12、Extra：表示MySQL在處理查詢時的詳細資訊

  • using filesort: 使用了檔案排序，很影響效能
  • using temporary: 使用了臨時表，很影響效能
  • using index: 使用了覆蓋索引
  • using where: 使用了where
  • using MRR: 使用了MRR優化
  • using join buffer: 使用了連結快取
  • impossible where: where的條件總是false
  • select tbles optimized away: 在沒有group by操作的情況下，不必等到執行階段再計算
  • distnct: 優化distinct操作
  • using index condition: 使用了ICP優化

更多詳情見官網：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

2.2.2、show profile

Show Profile是mysql提供的可以用來分析當前會話中sql語句執行的資源消耗情況的工具，可用於sql調優的測量。預設情況下處於關閉狀態，並儲存最近15次的執行結果。

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL  #顯示所有的開銷資訊
  | BLOCK IO #顯示塊IO開銷
  | CONTEXT SWITCHES #上下文切換開銷
  | CPU #顯示CPU開銷資訊
  | IPC #顯示傳送和接收開銷資訊
  | MEMORY #顯示記憶體開銷資訊
  | PAGE FAULTS #顯示頁面錯誤開銷資訊
  | SOURCE #顯示和Source_function，Source_file，Source_line相關的開銷資訊
  | SWAPS #顯示交換次數開銷資訊
}

 show variables like '%profiling%';

  set global profiling =on;

  然後我們隨性的多執行一些查詢語句，在通過show profiles檢視：

 上面有3列，一個是查詢的id，一個是執行的時間，最後一個是執行語句。

然後我們對id=8的語句進行診斷：show profile cpu,block io for query Query_ID;/*Query_ID為#3步驟中show profiles列表中的Query_ID*/

  上表中第一列為該sql查詢的狀態，也是sql的一個生命週期，從開始，到許可權，開啟表，初始化，上鎖，優化，統計，執行，傳送資料，查詢結束，關閉表等。

在狀態這一列中要注意一些點：

  • converting HEAP to MyISAM：查詢結果太大，記憶體不夠，資料往磁碟上搬了。
  • Creating tmp table：建立臨時表。先拷貝資料到臨時表，用完後再刪除臨時表。
  • Copying to tmp table on disk：把記憶體中臨時表複製到磁碟上，

從上看來主要的時間花在了資料的傳輸上面，所以可以針對該項進行優化，更多詳情見官網：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

三、優化具體點

3.1、sql的執行順序

在去寫好sql和優化sql查詢之前，知道sql的執行順序尤為的重要（所以這裡要多讀幾遍）

from ->on ->join ->where ->group by ->having ->select ->distinct ->order by ->limit
1、from 對查詢指定的表計算笛卡爾積
2、on 按照 join_condition 過濾資料
3、join 新增關聯外部表資料
4、where 按照where_condition過濾資料
5、group by 進行分組操作
6、having 按照having_condition過濾資料
7、select 選擇指定的列
8、distinct 指定列去重
9、order by 按照order_by_condition排序
10、limit 取出指定記錄量

3.2、建立索引和不該建立索引時機

應該建立索引

  • 主鍵，唯一，外來鍵約束都會自動建立索引
  • 頻繁作為查詢條件的欄位已經建立索引
  • 查詢中與其他表關聯的欄位
  • 查詢中排序的欄位
  • 查詢中統計或分組的欄位

不應該建立索引

  • 頻繁更新的欄位
  • where條件用不到的欄位
  • 表記錄太少
  • 經常增刪改的表
  • 資料重複且分佈平均的表欄位

3.3、join優化

製造點實驗資料：

mysql優化篇（基於索引） 
CREATE TABLE IF NOT EXISTS department(
id INT(11)  PRIMARY KEY AUTO_INCREMENT COMMENT'部門編號（主鍵）',
d_name VARCHAR(50) COMMENT'部門名稱',
d_address VARCHAR(50) COMMENT'部門地址');
    
    
CREATE TABLE user(
id INT(11) NOT NULL PRIMARY KEY COMMENT'員工編號',
u_name VARCHAR(50) COMMENT'員工姓名',
sex int(11) COMMENT '性別',
d_id int(11) COMMENT '部門id',
word VARCHAR(50) COMMENT'員工工作',
entryTime datetime COMMENT'員工入職時間',
wage INT(11) COMMENT'員工工資',
bonus INT(11) COMMENT'員工獎金'
);

create table sex(
    id int(11),
    name char(1) not null
)

insert into sex values('1','男'),('0','女');

INSERT INTO user VALUES 
('1', '小王', '0', '2', 'a寫程式碼1', '2017-06-14 14:30:50', '4000', null),
('2', '小李', '1', '3', 'b寫程式碼2', '2016-08-16 14:32:08', '20800', '5000'),
('3', '小張', '1', '3', 'c寫程式碼2', '2016-05-04 14:33:05', '22700', null),
('4', '小高', '1', '1', 'a寫程式碼2', '2015-07-08 14:33:54', '5000', null),
('5', '小劉', '0', '4', '寫程式碼3', '2017-11-08 14:35:35', '10000', null),
('6', '王一', '1', '5', 'd寫程式碼1', '2016-11-01 14:36:28', '20000', '5000'),
('7', '王二', '0', '2', '寫程式碼5', '2018-03-22 14:38:44', '5000', null),
('8', '李四', '1', '7', '寫程式碼6', '2017-04-01 14:39:53', '5000', null),
('9', '李一', '0', '4', '寫程式碼8', '2018-08-01 14:40:43', '5000', null);


INSERT INTO  department(id,d_name,d_address)
(1,'銷售部','銷售部地址'),
(2,'學業部','學業部地址'),
(3,'董事部','董事部地址'),
(4,'人力資源部','人力資源部地址'),
(5,'產品部','產品部地址')，
(20,'研發部','三樓');
View Code

　　MySQL內部採用了一種叫做 nested loop join的演算法。Nested Loop Join 實際上就是通過驅動表的結果集作為迴圈基礎資料，然後一條一條的通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合併結果。如果還有第三個參與 Join，則再通過前兩個表的 Join 結果集作為迴圈基礎資料，再一次通過迴圈查詢條件到第三個表中查詢資料，如此往復，基本上MySQL採用的是最容易理解的演算法來實現join。（一定要用小表驅動大表）

看看這些表的索引情況：

  除了兩個表的主鍵自動建立的主鍵索引外，沒有其他的任何索引。

explain select * from department d left join user u on u.d_id = d.id;

  兩個表都是全表掃描呢，命名department表在id上有主鍵索引呢，但是作為左連線，department需要保留全部的資料，所以建立索引是沒什麼影響的，接下來我們在user表上建立d_id的索引idx_d_id: create index idx_d_id on user (d_id);

  看結果可以得出user表使用了索引，減少了資料的讀取，可以得出left join主要的優化在於右表的索引的建立，那right join也是一樣在於左表的索引的情況，對於inner join呢？我們看看。

在user表沒有建立idx_d_id索引前：

  在user表沒有建立idx_d_id索引後：

  好像優化器會選擇把小的表來驅動大的表，全表掃描小的表，大的表走索引。

這是兩個表的lefter join和right join的情況，那三個表的呢？還是上面的原則，有一個表會全保留，其他的走索引就好了。所以就是小表用來做驅動，大表用來走索引，這樣就可以提高left join和right join的速度了。當然索引也要合適。。

3.4、order by和group by優化

1、order by

　　order by:就是排序，我們都知道InnoDB儲存引擎的儲存是根據主鍵按照順序儲存的，所以這些都是已經排好序的，但是我們又不僅僅是根據主鍵排序，還要更加其他列進行排序，這樣又怎麼弄呢，當然我們可以在這些列上建立索引呀（單列，或者組合索引，推薦組合），索引就是有序的，這樣就不用額外的排序了，但是不可能每個列都建立好索引吧，還有就是預設的是asc排序，那desc排序呢，又當如何呢，這就會造成filesort，雖然可以排序，但是效率真的低，所以儘量不要使用。既然order by有兩種排序，一種是通過索引的預設排序這樣的速度好，還有就是filesort，但是filesort如何去優化下呢？

在MySQL中filesort 的實現演算法實際上是有兩種：

  • 雙路排序：是首先根據相應的條件取出相應的排序欄位和可以直接定位行資料的行指標資訊，然後在sort buffer 中進行排序。
  • 單路排序：是一次性取出滿足條件行的所有欄位，然後在sort buffer中進行排序。

　　在MySQL4.1版本之前只有第一種排序演算法雙路排序，第二種演算法是從MySQL4.1開始的改進演算法，主要目的是為了減少第一次演算法中需要兩次訪問表資料的 IO 操作，將兩次變成了一次，但相應也會耗用更多的sortbuffer 空間。當然，MySQL4.1開始的以後所有版本同時也支援第一種演算法。

　　MySQL主要通過比較我們所設定的系統引數 max_length_for_sort_data的大小和Query 語句所取出的欄位型別大小總和來判定需要使用哪一種排序演算法。如果 max_length_for_sort_data更大，則使用第二種優化後的演算法，反之使用第一種演算法。所以如果希望 ORDER BY 操作的效率儘可能的高，一定要注意max_length_for_sort_data 引數的設定。

是有filesort的情況（說白了就是不走索引）：

  1. where語句與order by語句，使用了不同的索引
  2. 檢查的行數過多，且沒有使用覆蓋索引
  3. ORDER BY中的列不包含在相同的索引，也就是使用了不同的索引
  4. 對索引列同時使用了ASC和DESC
  5. where語句或者ORDER BY語句中索引列使用了表示式，包括函式表示式
  6. where 語句與ORDER BY語句組合滿足最左字首，但where語句中使用了條件查詢。

同樣我們使用3.3節中的實驗資料（看完下面的結果可以好好體會下上面說的filesort的情況）：

   在join條件下的order by情況：order by的子句只引用了聯接中的第一個表，MySQL會先對第一個表進行排序，然後進行聯接。也就是expain中的Extra的Using Filesort.否則MySQL先把結果儲存到臨時表(Temporary Table),然後再對臨時表的資料進行排序.此時expain中的Extra的顯示Using temporary Using Filesort（這裡有個坑，就是使用inner join的情況下，預設會使用小表驅動大表，所以就算你把大表寫在前面，然後排序只用了大表的欄位，但是mysql優化器會把小表放在前面，然後使用了大表的排序，這樣就會生成臨時表）

  　　圖1：第一種方法用於第一個非常量表中存在ORDER BY所依賴的列的索引，那就可直接使用已經有序的索引來查詢關聯表的資料，這種方式是效能最優的，因為不需要額外的排序動作

　　圖2：第二種方式用於ORDER BY所依賴的列全部屬於第一張查詢表且沒有索引，那麼我們可以先對第一張表的記錄進行filesort（模式可能是模式1也可能是模式2），得到有序行索引，然後再做關聯查詢，filesort的結果可能是在記憶體中，也可能在硬碟上，這取決於系統變數sort_buffer_size

　　圖3：第三種方法用於當ORDER BY的元素不屬於第一張表時，需要把關聯查詢的結果放入臨時表，最後對臨時表進行filesort

mysql> explain select * from department d left join user u on u.d_id = d.id order by d.d_name; #在沒有索引的情況下，除了全表掃描，還有臨時表，filesort
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from department d left join user u on u.d_id = d.id order by d.id;#在沒有索引的情況下，除了全表掃描，還有臨時表，filsort
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> create index idx_d_id on user(d_id);  #建立索引
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from department d left join user u on u.d_id = d.id order by d.id; #建立索引後user表的rows為1，沒有使用filesort和臨時表
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | d     | NULL       | index | NULL          | PRIMARY  | 4       | NULL                 |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | ref   | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from department d left join user u on u.d_id = d.id order by u.d_id; #把排序條件換成user的欄位後，使用了臨時表和filesort
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL     | NULL    | NULL                 |    6 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | NULL       | ref  | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> explain select * from department d left join user u on u.d_id = d.id order by d.d_name; #把排序欄位換成department表的d_name列（沒有索引），使用了filesort
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL     | NULL    | NULL                 |    6 |   100.00 | Using filesort |
|  1 | SIMPLE      | u     | NULL       | ref  | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | NULL           |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)


mysql> explain select * from department d inner join user u on u.d_id = d.id order by d.d_name,u.id; #使用了user表的id排序，新增臨時表
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL     | NULL    | NULL                 |    6 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | NULL       | ref  | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select d.* from department d inner join user u on u.d_id = d.id order by d.d_name,u.id; #這裡只返回department表的全部列，還是使用了臨時表和filesort
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL     | NULL    | NULL                 |    6 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | NULL       | ref  | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | Using index                     |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select d.* from department d inner join user u on u.d_id = d.id order by d.d_name; #去掉user的id列排序後少了臨時表
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL     | NULL    | NULL                 |    6 |   100.00 | Using filesort |
|  1 | SIMPLE      | u     | NULL       | ref  | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | Using index    |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select d.* from department d inner join user u on u.d_id = d.id order by d.id; #使用department的id排序，沒了filesort
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | index | PRIMARY       | PRIMARY  | 4       | NULL                 |    6 |   100.00 | NULL        |
|  1 | SIMPLE      | u     | NULL       | ref   | idx_d_id      | idx_d_id | 5       | foundation_item.d.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

inner join下的一個坑點

   面對如上情況的問題，可以使用如下的方式試試（執行計劃差不多，有點區別，資料量很大的情況下可以試試，我也不確定速度會怎樣，可能會好些，可以過濾掉一些資料，在不同的業務場景下可以嘗試對比）

   有時候filesort是無法避免的，但是還是可以做一些優化的：

  1. 對於使用filesort的慢查詢，增大一些max_length_for_sort_data來使用單路排序
  2. 增加sort_buffer_size的大小，如果大量的查詢較小的話，這個很好，就快取中就搞定了
  3. 增加read_rnd_buffer_size大小，可以一次性多讀到記憶體中
  4. 列的長度儘量小些（去掉不必要的返回欄位）
  5. 改變tmpdir，使其指向多個物理盤(不是分割槽)的目錄，這將機會迴圈使用做為臨時檔案區

2、group  by

　　由於group by實際上也同樣會進行排序操作，而且與group by相比，group by 主要只是多了排序之後的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函式，那麼還需要一些聚合函式的計算。所以，在group by 的實現過程中，與 group by一樣也可以利用到索引。

　　由於group by無非就是用到索引和用不到索引的情況，用到索引的時候走索引速度快，用不到的情況用臨時檔案，所以會慢一些，其實就是在排序後分組，既然不能走索引的話就可以根據order  by不能走索引，使用filesort的優化策略一樣就好了，走索引的情況就要符合索引的最左字首原則，這裡不再深入的討論group by的原理，有興趣的可以自行去理解，反正我認為回了order by的優化就基本根據套路來優化group by。

　　還有就是能在where中過濾掉的就不要等到hiving過濾。

3.5、索引失效情況優化

很多時候明明建立了索引，就是使用的過程中不走索引，所以有時候也會很苦惱，我們就來看看哪些情況不走索引（注意：如下的任何優化方式都是通過explain做理論支撐的，沒有在實際的生成環境中跑過，所以有時候速度快不快也要看錶的設計，索引的設計，資料量的大小等）

1、使用 like '%%'  #使用like的時候要注意%在最左邊的時候是不會走索引的，其他的方式會走索引

  如上方式可以修改成： explain select * from (select id from user where word like '%寫程式碼1%') a inner join user u on a.id = u.id;  其中id為主鍵索引。源於《深入淺出mysql:資料開發、優化與管理維護(第二版)》，我也不知道這個會不會有速度提升，畢竟type是index，建立索引後的word列的type是range，接下來我們把word上的索引去掉看看。

 

 

 

 可以細細體會下如上的過程，在沒有建立索引的情況下使用join這個寫法速度快沒問題，但是在建立索引的情況下，估計沒得快（這裡有條件的話可以去測試一下），所以對於like '%%'這種寫法，在不能建立索引的情況下就使用join，可以建立索引的情況下就新增索引。

2、在where中使用函式，計算，型別轉換等不會走索引。

mysql> create index d_word on user(word); #建立索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from user where word= 'c寫程式碼2'; #使用等值查詢，使用到了索引
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | d_word        | d_word | 203     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where left(word,1)= 'c'; #在where中使用了函式或者計算，全表掃描沒有使用到索引
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3、在組合索引中不符合最左字首原則的不會走索引，或者只走部分索引。

order by使用索引最左字首
- order by a
- order by a,b
- order by a,b,c
- order by a desc, b desc, c desc 

如果where使用索引的最左字首定義為常量，則order by能使用索引
- where a=const order by b,c
- where a=const and b=const order by c
- where a=const and b > const order by b,c

不能使用索引進行排序
- order by a , b desc ,c desc  --排序不一致
- where d=const order by b,c   --a丟失
- where a=const order by c     --b丟失
- where a=const order by b,d   --d不是索引的一部分
- where a in(...) order by b,c --a屬於範圍查詢

4、字串沒有單引號索引也會失效

 5、使用or連線時，如果只有一個欄位有索引，則索引會失效 ，可以根據實際情況使用分別查詢然後union all連線

 6、limit分頁的優化

 附錄：資料生成存過

create table test(
id int(11) primary key auto_increment,
name varchar(56),
salary double
);

delimiter $$
create function rand_string(n int) returns varchar(255)
begin
    declare char_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare result_str varchar(255) default '';
    declare i int default 0;
    while i<n do
    set result_str = concat(result_str,SUBSTRING(char_str,FLOOR(1+rand()*52),1 ));
    set i=i+1;
    end while;
    return result_str;
end $$

delimiter $$
create procedure insert_tab(in start int(10),in max_num int(10))
begin
    declare i  int default 0;
    set autocommit = 0;
    repeat
    set i=i+1;
    insert into test(name,salary) values(rand_string(6),rand()*5000);
    until i = max_num
    end repeat;
    commit;
end $$

delimiter ;

call insert_tab(1,1000000);

到這裡為止介紹一部分的優化內容，mysql作為一個基礎內容，有超級強大，穩定，要學的東西特別多，所以優化的東西還有很多，後面有時間會慢慢道來，希望各位童鞋能夠一起學習，也希望自己能夠再接再厲！！！

 

參考：

https://www.cnblogs.com/yumingzhao/p/10711825.html

https://www.cnblogs.com/developer_chan/p/9231761.html

https://zhuanlan.zhihu.com/p/120043388

https://blog.csdn.net/lijingkuan/article/details/70341176

https://www.jb51.net/article/161684.htm







相關文章