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優化

更多詳情見官網://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:把內存中臨時表複製到磁盤上,

從上看來主要的時間花在了數據的傳輸上面,所以可以針對該項進行優化,更多詳情見官網://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優化

製造點實驗數據:

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作為一個基礎內容,有超級強大,穩定,要學的東西特別多,所以優化的東西還有很多,後面有時間會慢慢道來,希望各位童鞋能夠一起學習,也希望自己能夠再接再厲!!!

 

參考:

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

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

//zhuanlan.zhihu.com/p/120043388

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

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


		  				
Tags: