一、影響性能的相關(guān)因素

存儲過程：

存儲過程：類似可以在數(shù)據(jù)庫里面寫代碼的一種腳本，在Mysql里面可以封裝一個函數(shù)，在這個函數(shù)里面可以寫很多的邏輯。可以把所有的業(yè)務(wù)放在存儲過程里面去實現(xiàn)。

缺點：數(shù)據(jù)庫很耗資源。數(shù)據(jù)庫的連接是有上限的，一個請求調(diào)用一個存儲過程去執(zhí)行業(yè)務(wù)邏輯，意味著單個線程響應(yīng)的時間變長，在并發(fā)高的情況下，每個線程返回的時間都變長的話，意味著數(shù)據(jù)庫的連接很快就被用完了，那么其他現(xiàn)在就會進入等待，那么在應(yīng)用層面就會積壓很多線程，意味著應(yīng)用中的線程會被耗盡，那么外面的請求就進不來了，就會導致服務(wù)器的雪崩，宕機。

在互聯(lián)網(wǎng)應(yīng)用里面并不是多么好，可以不在數(shù)據(jù)庫中作的事情，就不要在數(shù)據(jù)庫做

如果是傳統(tǒng)行業(yè)，并發(fā)比較小，那么使用存儲過程，在sql里面調(diào)用函數(shù)就也還行。

存儲過程中調(diào)用的函數(shù)有哪些？

case when

表的設(shè)計也是影響性能的因素

賬號密碼修改頻率低的，可以設(shè)計在一個實體類里面（一張表里面），可以放在緩存里面。

修改頻率高的放在一個實體類里面，不放緩存。

應(yīng)用層面的設(shè)計也是影響性能的因素

如果應(yīng)用層面（代碼）沒設(shè)計好，所有的請求都落到數(shù)據(jù)庫，那么sql優(yōu)化的再好也頂不住。

代碼規(guī)范、mysql規(guī)范、字段規(guī)范

二、sql優(yōu)化

1>、Mysql系統(tǒng)架構(gòu)

2>、引擎

區(qū)別：

數(shù)據(jù)庫默認是Innodb引擎。

3>、索引

1、什么是索引？

能幫我們快速的定位數(shù)據(jù)，它是一種新的數(shù)據(jù)結(jié)構(gòu)。

索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu)，使用索引可以快速訪問數(shù)據(jù)庫表中的特定信息。

聯(lián)合主鍵索引理解：

一張表永遠只有一個主鍵索引。

除了主鍵索引外的其他索引都可以叫做輔助索引。

創(chuàng)建了索引的字段，最好設(shè)計成不為null，最好把【不是null】選項勾選上

這種情況叫做聯(lián)合主鍵，這三個加起來才是主鍵，才是唯一主鍵，相當于 【組合索引】 一樣

索引長度理解：

什么是慢查詢？

mysql可以主動開一個慢查詢，慢查詢就是

超過指定時間的sql就會記錄到日志里面

1）、索引理解

Navicat里面添加索引

BTREE 底層就是B+TREE

如圖，給這個字段加上索引（就是把這個字段作為一個索引），然后數(shù)據(jù)庫底層就會把這個字段的數(shù)據(jù)另外弄成一個二叉樹的數(shù)據(jù)結(jié)構(gòu)， 那么在查詢的時候，如果把這個字段作為查詢條件，數(shù)據(jù)庫就會直接去這個二叉樹數(shù)據(jù)結(jié)構(gòu)找該數(shù)據(jù)。

比如表有10000條數(shù)據(jù)，有十條數(shù)據(jù)有該字段，那么沒給該字段加索引前，數(shù)據(jù)庫需要遍歷全表，遍歷這10000條數(shù)據(jù)，但是如果把該字段弄成索引，那么就只需要遍歷這個二叉樹而已，就是只需要遍歷10次就行，效率就大大提升了。

2）、樹高

為什么千萬級別的數(shù)據(jù)，只需要3層樹高就可以？

樹高是4的話，有3次IO。

如圖，下面的圖的樹高是4，IO次數(shù)就是3.（因為根節(jié)點已經(jīng)被緩存了，所以IO次數(shù)就是4-1=3）

因為非葉子節(jié)點只存儲指針，而每個節(jié)點屬于一次io，一次io有16k，3次io就可以存有千萬級別的數(shù)據(jù)對應(yīng)的指針

這個點不是很理解？

**磁盤IO次數(shù)=樹的高度 ** 還是 磁盤IO次數(shù)=樹的高度-1 ？

讀取每次節(jié)點，都相當于一次的磁盤IO，搜索整棵樹，路徑長度為樹高，磁盤IO次數(shù)=樹的高度，樹高越矮，磁盤IO次數(shù)越少，性能就越高。

? 比如樹高=4，經(jīng)歷了3次磁盤IO，因為mysql會把根節(jié)點（最上面的非葉子節(jié)點叫根節(jié)點）緩存起來，所以樹高為4的話，磁盤IO次數(shù)為3。所以根節(jié)點被緩存起來后，樹高就變成3，io次數(shù)也是3，所以磁盤IO次數(shù)=樹的高度

為什么樹高=4，只經(jīng)歷了3次IO？

因為根節(jié)點被緩存起來了，所以樹高就變成3了，IO次數(shù)也就變成3了。

問題：那第一次查詢的時候，樹的根節(jié)點應(yīng)該還沒被緩存起來，所以樹高=4，IO次數(shù)也是4嗎？

3）、在線二叉樹

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

磁盤IO次數(shù)=樹高-1

總結(jié)

1、索引數(shù)據(jù)結(jié)構(gòu)都是B+Tree

2、為什么不用二叉樹或者紅黑樹呢？

? 二叉樹特定情況下會變成單向鏈表，性能低，可以用更好的數(shù)據(jù)結(jié)構(gòu) B+Tree。

3、有BTree和B+Tree，有什么區(qū)別，為什么選擇B+Tree？

? BTree 的非葉子節(jié)點是存實際數(shù)據(jù)的，會導致樹的高度更高，那么磁盤IO次數(shù)就會越多，效率就會減低

? B+Tree 中 非葉子節(jié)點是不存儲實際的數(shù)據(jù)，存儲的是值和指針（因為體積小，那么樹高就更低，磁盤IO次數(shù)就更少，查詢效率就更快）

? MySql默認一次的磁盤IO是16k，16k固定的情況下，非葉子節(jié)點體積越小，能容納數(shù)據(jù)就越多（樹的階越大，樹高越矮，這個【階】就是非葉子節(jié)點里面存的數(shù)據(jù)多少）。

? 讀取每次節(jié)點，都相當于一次的磁盤IO，搜索整棵樹，路徑長度為樹高，磁盤IO次數(shù)=樹的高度-1，樹高越矮，磁盤IO次數(shù)越少，性能就越高。

? 比如樹高=4，經(jīng)歷了3次磁盤IO，因為mysql會把根節(jié)點（最上面的非葉子節(jié)點叫根節(jié)點）緩存起來，所以樹高為4的話，磁盤IO次數(shù)為3。

4）什么是索引覆蓋？

索引覆蓋：輔助索引里面存的值，就已經(jīng)包括了我需要的字段了（如圖就是該字段是設(shè)置為輔助索引，而sql要查的字段就剛好是它），所以就不用再通過索引里面的值去回表找主鍵索引來查詢數(shù)據(jù)，

因為輔助索引（字段）里面的值就已經(jīng)是我們想要的數(shù)據(jù)了，再回表去查更多的數(shù)據(jù)也沒有什么意義。

也可以簡單的理解為：索引覆蓋就是不用再進行回表查詢，就是不用通過輔助索引的值去主鍵索引那里查完整的行數(shù)據(jù)。通過輔助索引就能找到對應(yīng)字段的列的數(shù)據(jù)（前提：查詢的該字段也是一個索引）

分析圖：

覆蓋索引的解釋：

5）什么是回表查詢？

一個表只有一個主鍵索引，其他的我們自己添加的索引都可以叫做輔助索引（比如復合索引這些都能叫做輔助索引）

回表查詢就是 通過輔助索引的葉子節(jié)點里面存的主鍵值，到主鍵索引哪里找到對應(yīng)的數(shù)據(jù)。

主鍵索引里面存的值就是完整的一行數(shù)據(jù)

2、Mysql索引實現(xiàn)

主鍵索引就是表的id

輔助索引就是我們自己添加的索引

1）、MyISAM索引實現(xiàn)

MyISAM的主鍵索引和輔助索引找數(shù)據(jù)的分析圖

這里的主鍵找到數(shù)據(jù)的索引，還得根據(jù)索引的物理位置在另外的文件（.myd文件）去找具體的數(shù)據(jù)，比Innodb慢

2）、Innodb索引實現(xiàn)

Innodb的主鍵索引和輔助索引

3）、聚簇索引

聚集索引是指數(shù)據(jù)庫表行中數(shù)據(jù)的物理順序與鍵值的邏輯（索引）順序相同。

跟上面分析的索引實現(xiàn)一樣

通俗講:
聚簇索引：將數(shù)據(jù)存儲與索引放到了一塊，找到索引也就找到了數(shù)據(jù)
非聚簇索引：將數(shù)據(jù)存儲于索引分開結(jié)構(gòu)，索引結(jié)構(gòu)的葉子節(jié)點指向了數(shù)據(jù)的對應(yīng)

3、索引的利弊

索引是獨立于我們數(shù)據(jù)之外的另外一種數(shù)據(jù)結(jié)構(gòu)，需要存儲空間的。

新增、修改等操作都要重新生成索引樹，需要額外消耗資源（弊），但是相比我們查詢所帶來的性能的提升（利），這點消耗可以忽略不計。

索引的好處：
1，提高表數(shù)據(jù)的檢索效率；
2，如果排序的列是索引列，大大降低排序成本；
3，在分組操作中如果分組條件是索引列，也會提高效率；

**索引的問題：**索引需要額外的維護成本；

字段修改的頻率比查詢的頻率高的話，就不用建立索引。

4、創(chuàng)建索引情況

1，較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引；

2，唯一性太差的字段不適合單獨創(chuàng)建索引，即使頻繁作為查詢條件；

? 比如：查詢 男和女 ，篩選完還是有海量的數(shù)據(jù)

3，更新非常頻繁的字段不適合創(chuàng)建索引；

? 添加索引的話，頻繁修改就需要頻繁重新生成索引樹，消耗的資源 大于 查詢的性能帶來的收益

4，不會出現(xiàn)在WHERE 子句中的字段不該創(chuàng)建索引；

? 都不作為查詢條件，更沒有必要添加索引

5、單值索引和組合索引

單值索引

只是把一個字段作為索引，這個就是單值索引

分析圖：

組合索引

也叫復合索引

這個索引就是組合索引，組合索引用的比較多。

分析圖：

組合索引比單值索引查詢要快，但是要記得組合索引得符合【最左原則】

最左原則：where后面查詢的第一個字段（不用按where順序）要和組合索引的第一個字段相同。

注意這個情況，也是符合最左原則的，最左原則是要條件符合，不是說寫的順序要符合。

比如：組合索引（A、B、C），那么where條件 （ABC ACB BAC CBA 等）都是符合最左原則的。

組合索引（A、B、C），那么where條件 （BC CB BC 等）里面都沒有A，查詢的時候無法命中組合索引中的A索引字段，那么這個才叫做不符合最左原則

最佳創(chuàng)建組合索引

應(yīng)該按照最高頻的字段來創(chuàng)建組合索引，最大程度去覆蓋到sql的查詢條件，這樣查詢性能也能提升，生成的索引樹成本也能降低。

4>、Explain命令

示例數(shù)據(jù)庫：

數(shù)據(jù)下載:https://github.com/datacharmer/test_db

MySQL官方文檔中提供的示例數(shù)據(jù)庫之一：employees

使用命令導入數(shù)據(jù):
mysql -uroot -p123456 -t < employees.sql

Explain命令的作用：通過這個Explain命令來查看sql的執(zhí)行計劃，看能怎么對sql進行優(yōu)化。

Explain命令主要來查看SQL語句的執(zhí)行計劃，查看該SQL語句有沒有使用索引，有沒有做全表掃描等。它可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理用戶的SQL語句

1)select_type:查詢類型

1、union

union是產(chǎn)生的兩個記錄集(字段要一樣的)并在一起，成為一個新的記錄集

union是將表內(nèi)容拼接成一列（上下拼接），也是根據(jù)字段共同屬性進行將表與表之間數(shù)據(jù)進行上下拼接

2、subquery

子查詢中的第一個select，其select_type為SUBQUERY

3、dependent subquery 和 dependent union

子查詢只有一個select ，第一個select 就是 subquery

子查詢有多個select ， 第一個 select 就是 dependent subquery，

第二個select 使用 union 連接的話，那就是 dependent union。

dependent 表示依賴的意思。表示 最外面的select 依賴于 子查詢中的結(jié)果。

4、derived

當這個語句查詢出來的數(shù)據(jù)作為別人的表的時候（查詢的數(shù)據(jù)跟在from后面），相當于派生表的類型

2）type：聯(lián)接類型

聯(lián)接類型

1、system 和 const

system：查詢速度最快，直接從內(nèi)存中查詢獲取到數(shù)據(jù)

const：通過主鍵索引查詢到數(shù)據(jù)

2、eq_ref

條件是：唯一索引

兩張表關(guān)聯(lián)的情況下（比如join之類的），查詢條件的這個字段剛好是主鍵字段的情況下，或者是唯一字段的情況下，那么type的類型就是 eq_ref

eq_ref：表示查詢的那個條件屬于該表的【主鍵字段】或者是【唯一字段】，

因為該字段屬于唯一主鍵，匹配這個主鍵成功就能直接找到對應(yīng)的數(shù)據(jù)，相當于 一對一 的情況。

3、ref

條件：非唯一索引

eq_ref 的性能比 ref 要高

如圖：兩張表關(guān)聯(lián)查詢時，第二張表（titles）的匹配字段（emp_no）屬于該表的非唯一性主鍵索引時，那么這張表的 type 聯(lián)接類型就屬于 ref。

解答：

把那三個字段弄成一個索引，相當于弄成一個【非唯一性主鍵索引】，那么在查詢的時候，類型就是 ref

ref 性能比 eq_ref 低的原因就在于 ref 出現(xiàn)的情況是 第二張表的查詢條件是 非唯一性主鍵索引，因為是非唯一性的，所以在索引樹查詢的時候，需要匹配如圖的三個字段的條件才行，不像唯一性主鍵索引，只需要匹配一個就行，所以 ref 的性能比 re_ref 低一點。

唯一索引和非唯一索引的區(qū)別：

一張表永遠只有一個主鍵索引。

這種叫做唯一索引，

這張表只有這個字段是主鍵，是主鍵索引，是唯一索引

這種叫非唯一索引

這種情況叫做聯(lián)合主鍵，這三個加起來才是主鍵，三個加起來才能算是一個【唯一主鍵】，相當于 【組合索引】 一樣

4、ref_or_null

出現(xiàn)的場景：

查詢的條件字段有索引，但是設(shè)計字段的時候是可以為null，所以如果用該字段作為查詢條件的話，就會出現(xiàn)ref_or_null的聯(lián)接類型

5、index_merge

出現(xiàn)的場景：一張表中有多個查詢條件且都有索引時，會出現(xiàn) index_merge 索引合并類型。

針對or導致的索引失效的分析圖

OR 前后只要存在非索引的列，都會導致索引失效

測試：

6、range

按指定的范圍進行檢索，就會顯示未 range類型

7、index

ALL 是全表掃描數(shù)據(jù)，index 是全表掃描索引

性能上面，index 僅僅比 ALL 好上一點而已

8、ALL

all 類型，就是一個全表掃描，跟用沒用索引一個樣。

3）Extra詳細信息

1、Not exists

查詢的條件是沖突的，一個是主鍵，是非空的（employees），一個要查這個字段為null的，主鍵不可能為空，就不再繼續(xù)掃描該表（dept_emp）了。

mysql根據(jù)語義發(fā)現(xiàn)這種查法是查不出數(shù)據(jù)的，就顯示為 【Not exists】

2、Range checked for each record

范圍查詢覆蓋了整張表，mysql表示沒有好的索引可以用，就顯示這個【Range checked for each record】

3、Using filesort

這里的sql是根據(jù)字段進行分組，所以這里顯示的 【Using filesort】 表示按照字段進行排序

（這個解釋比較籠統(tǒng)，后續(xù)可以研究）

4、Using index

這是性能很高的一種情況。當查詢所需的數(shù)據(jù)可以直接從索引樹中檢索到時就會顯示【Using index】

5、Using temporary

查詢的時候，Mysql需要創(chuàng)建一張臨時表來處理該查詢時會顯示【Using temporary】

臨時表是比較消耗資源的

6、Using where

當sql有where子句時，extra都會有說明。

但是這兩個where字句，一個有顯示 Using where ，一個沒有，不太理解

7、Using union

一張表中有多個查詢條件且都有索引時，會出現(xiàn)type類型為 index_merge ，進行索引合并類型，

Using union出現(xiàn)的場景 ：

這里也一樣，會使用union函數(shù)進行索引合并，跟type聯(lián)接類型為 index_merge 一樣

8、Using index for group-by

Using index for group-by 這個也可以理解為【索引覆蓋】

索引覆蓋：索引已經(jīng)包括了我需要的字段了，就不用再通過索引去回表找主鍵索引來查詢數(shù)據(jù)

分析圖：

表明可以在索引中找到分組所需的所有數(shù)據(jù)，不需要查詢實際的表。

覆蓋索引的理解：

4)命令匯總：

按順序：從上到下，性能最好的是 system ， 性能最差的是 ALL

三、Join理解及優(yōu)化

1、Inner join

誰是小表誰是大表，MySQL Optimizer會自動判斷，和我們寫表的順序是無關(guān)的

2、join

join 和 inner join 一樣的結(jié)果，誰是小表誰是大表，MySQL Optimizer會自動判斷，和我們寫表的順序是無關(guān)的

3、where

where、join 和 inner join 一樣的結(jié)果，誰是小表誰是大表，MySQL Optimizer會自動判斷，和我們寫表的順序是無關(guān)的

4、left join

left join 就不用解釋了，就是左表驅(qū)動右表，以左邊的表為基準。

MySQL優(yōu)化器要確定以誰為驅(qū)動表，也就是說以哪個表為基準，在處理此類問題時，MySQL

Optimizer采用了簡單粗暴的解決方法：哪個表的結(jié)果集小，就以哪個表為驅(qū)動表.

5、straight_join

straight_join 是能優(yōu)化sql性能的

STRAIGHT_JOIN 功能同 inner join 、 join 是一樣的，區(qū)別只是能讓左邊的表來驅(qū)動右邊的表，能改變 表優(yōu)化器 對于 聯(lián)表，其他的作用和 inner join 、 join 的效果都是一樣的

這個是 dept_emp 為驅(qū)動表

因為 無論是 Inner join 還是 straight_join , 用 Explain 命令看的話，都是以 dept_emp 為驅(qū)動表，所以兩者的耗時其實是差不多的。

因為這條sql語句 無論是用 inner join 還是 straight_join ， 都是以 dept_emp 為驅(qū)動表，所以看不出使用 straight_join后的性能提升。

所以我用 straight_join 把employees 表弄成驅(qū)動表，那么結(jié)果應(yīng)該就是 驅(qū)動表為 employees 的sql語句性能比較低。如圖也能看出

注意： straight_join 是用來讓左邊的表作為驅(qū)動表的，跟left join 一樣的作用，不過似乎使用 straight_join的性能會比使用 left join 的性能要高。

6、場景優(yōu)化情況

優(yōu)化情況：添加索引，或者是更改驅(qū)動表

優(yōu)化情況：根據(jù)情況設(shè)置緩沖區(qū)大小

7、Show profiles：比較性能

Show profiles是MySql用來分析當前會話SQL語句執(zhí)行的資源消耗情況,可以用于SQL的調(diào)優(yōu)測量

四、一些索引測試

1、模糊查詢like

用like做查詢時，通配符% 放在字段值后面（樣子為—>xxx%），進行前綴查詢，索引就能使用，

前綴查詢的樣子abc%，就可以拿a、b、c先去索引樹進行匹配，所以索引就可以使用。

如果把通配符%放在字段值最前面來進行后綴查詢（樣子為—>%xxx），那么索引就會失效。

比如后綴查詢的樣子是 【%abc】，我們根本不知道%是什么值，也就沒辦法在索引樹進行比對，所以索引就會失效

2、范圍查詢

>、<

進行范圍查詢后，后續(xù)的查詢條件的字段的索引就會失效 ，就不會再走索引

大于和小于 會導致后面的索引失效

between

between 不會導致后面索引失效

3、函數(shù)

使用函數(shù)會導致該字段的索引失效，如圖

4、or & and

**使用or不會導致索引失效的情況：**就是or左右的查詢條件的字段都有加索引（單值索引 或者 復合索引（復合索引要符合最左原則才行））

使用or導致索引失效的情況： or左右的查詢條件的字段有一個以上沒有加索引或者不滿足索引規(guī)則，那么使用or就會導致索引失效

or 會導致索引失效的情況

如上圖，如果想讓 這個 or 不導致索引失效，我們可以單獨給這個title 加上一個所以就可以了。

如圖：

5、算數(shù)表達式

在該字段進行算數(shù)表達式也會導致索引失效

如圖：

五、索引使用策略及優(yōu)化

1、索引選擇性

索引的選擇性是指索引列中不同值的數(shù)目與表中記錄數(shù)的比。

如果一個表中有2000條記錄，表索引列有1980個不同的值，那么這個索引的選擇性就是1980/2000=0.99。

比如有2000條數(shù)據(jù)，值記錄的是男和女，那么這個表索引列就是有2個不同的值，索引選擇性就是 2/2000，就是一千分之一，表明索引的效率很低。

如果是20000條數(shù)據(jù)，那么就是 2/20000 等于一萬分之一

一個索引的選擇性越接近于1，這個索引的效率就越高。

既然索引可以加快查詢速度，那么是不是只要是查詢語句需要，就建上索引？

答案是否定的。

因為索引雖然加快了查詢速度，但索引也是有代價的：索引文件本身要消耗存儲空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在運行時也要消耗資源維護索引，因此索引并不是越多越好。

情況1：如果表的記錄比較少，少于2000條數(shù)據(jù)，那么就沒必要建立索引。

情況2：超過2000條數(shù)據(jù)的表，也要看索引的選擇性，索引的選擇性太低也沒必要建立索引。比如男和女這種。

除了選擇性需要考量，這個索引的長度也要考量，因為索引的長度越長，那么需要消耗的空間就越多。

索引長度理解：

2、前綴索引

有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引，就是用列的前綴代替整個列作為索引key，當前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。

前綴索引分析：

需求： first_name 和 last_name 字段這些字段來查詢數(shù)據(jù)。

1、根據(jù)兩個字段查詢數(shù)據(jù)，沒有添加索引的情況下，耗時是0.068s

2、根據(jù)情況看是否要給字段建立單值索引，可通過計算出索引選擇性的大小來看。

查看 first_name 和 last_name 字段的索引選擇性，發(fā)現(xiàn)都太小，不適合建立單值索引。

3、復合索引

復合索引符合要求，索引選擇性達到了0.9313，可以建立，但是可以看到兩個字段的長度加起來是30，弄成符合索引之后，因為長度越長越消耗空間。

有沒有兼顧長度和選擇性的辦法？

可以考慮用fifirst_name和last_name的前幾個字符建立索引，例如<fifirst_name, left(last_name, 3)>，看看其選擇性

4、優(yōu)化復合索引，減少復合索引的長度

5、再對比下查詢性能，速度提升了 278倍

6、注意點

前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用于ORDER BY和GROUP BY操作，也不能用于

Covering index（即當索引本身包含查詢所需全部數(shù)據(jù)時，不再訪問數(shù)據(jù)文件本身）

3、自增id作主鍵的原因

在使用InnoDB存儲引擎時，如果沒有特別的需要，請永遠使用一個與業(yè)務(wù)無關(guān)的自增字段作為主鍵。

為什么要用一個自增的id作為主鍵？

解釋：

自增id：
涉及到磁盤的位置，因為如果輸入的位置是連續(xù)的話，可以把一個位置用完之后，才會再去申請下一塊位置，因為位置是連續(xù)的。

uuid：
因為不是連續(xù)的，所以存入的位置不會按順序插入，可能這個位置插一個數(shù)據(jù)，另一個位置插入一個數(shù)據(jù)，可能導致的結(jié)果就是 一個位置還沒用完，數(shù)據(jù)就會插到另一個位置。
這樣會產(chǎn)生很多空間的碎片，就是一個區(qū)域還沒用完，就會去重新申請新的一塊區(qū)域，導致空間浪費。

比如行李箱，一件一件擺好的話，存的衣服會更多（自增id），如果隨便丟進行李箱，那么能塞進去的衣服就會比較少（uuid），一些空間縫隙沒利用到

為什么要用自增id作為主鍵的分析圖

六、其他優(yōu)化

1、order by 優(yōu)化

order by 作用：對指定列進行排序

ORDER BY 實現(xiàn)原理：
1，通過有序索引而直接取得有序的數(shù)據(jù)；
2，通過MySQL 的排序算法將存儲引擎中返回的數(shù)據(jù)進行排序然后再將排序后的數(shù)據(jù)返回；

雙路排序：

順序讀性能比亂序讀高

內(nèi)存排完序之后，需要回表進行亂序讀，查詢需要的字段。

單路排序：

單路排序沒有 row_id這個磁盤地址

因為 sortBuffer 是把表的整行數(shù)據(jù)都讀過去的，所以在內(nèi)存排完序的結(jié)果就是最終的結(jié)果，不用跟雙路排序一樣還得回表查。

單路排序速度比雙路排序要快，不過比較占內(nèi)存。

區(qū)別：

order by 怎么選擇 單路排序還是雙路排序？

MySQL根據(jù)設(shè)置的【max_length_for_sort_data】變量來確定走【單路排序】還是【雙路排序】，默認值是1024字節(jié)，

如果需要返回的【列的總長度】（就是元組長度） 大于 【max_length_for_sort_data】，走【雙路排序】，

否則走【單路排序】。

查看：show variables like ‘max_sort_length’;

單路排序算法：sort buffer 緩沖區(qū)緩存整個行的數(shù)據(jù)，在根據(jù)排序字段進行排序，返回數(shù)據(jù)給客戶端，

缺點：占用內(nèi)存

優(yōu)點：避免回表查詢

雙路排序算法：sort buffer 緩沖區(qū)只緩存主鍵id和排序字段在內(nèi)存中，在根據(jù)排序字段進行排序，在做一次回表查詢，根據(jù)主鍵id查詢該行數(shù)據(jù)返回給客戶端。

區(qū)別1：

查詢返回的字段如果比較多，那么 order by 就是走【雙路排序】，

查詢返回的字段如果比較少，那么 order by 就是走 【單路排序】。

區(qū)別2：

雙路排序有值的磁盤物理地址，單路排序沒有。

**元組：**雙路排序的元組是 row_id（磁盤物理地址），單路排序的元組就是那些字段，所以單路排序的元組比雙路排序的元組長

解釋：

單路因為元組比較長，所以需要占的空間就比雙路排序的多，在同樣的內(nèi)存大小的 Sortbuffer內(nèi)存緩沖區(qū) 中，單路存的元組就比雙路的少，所以產(chǎn)生的臨時文件就會比雙路的多。

但是雙路排序有順序讀和亂序讀，所以它的磁盤io次數(shù)是比單路排序的要多。

所以總的來說，單路排序的性能會比雙路排序的性能會好些，但是還是得看情況而定

1）雙路排序會讀取兩次表， 且第二次是隨機讀取的
2）單路排序的元組比雙路排序要長，導致它需要多次向臨時文件寫入內(nèi)容，增加IO操作，當需要返回的列的總長度很長時尤其明顯。

提問：

問題：如果一次排序的順序高達1萬條，10萬條的數(shù)據(jù)，能一次性加載到內(nèi)存中嗎?

答：

如果要排序的數(shù)據(jù)量太多的話，不可能一次性把數(shù)據(jù)都讀取到內(nèi)存，這樣會把mysql的服務(wù)器的內(nèi)存一下子就占滿了。

MySQL會進行分批次的讀取，比如有1萬的數(shù)據(jù)，在內(nèi)存可能會一次讀1千，然后執(zhí)行完得到最終結(jié)果后，會把排好序存儲在一個臨時文件中（1千條排序后的結(jié)果）。

然后再重復上面的操作，1萬條數(shù)據(jù)，一次1千的分批讀，那么就會產(chǎn)生10個臨時文件，然后再針對這10個臨時文件重新讀取，然后在內(nèi)存中進行歸并排序，這個時候的性能肯定比直接一萬條數(shù)據(jù)直接排序的性能更高。

數(shù)據(jù)量多的情況下，單路排序和雙路排序都是這么分批次處理的。

?

優(yōu)化：

ORDER BY 實現(xiàn)原理：
1，通過有序索引而直接取得有序的數(shù)據(jù)；
2，通過MySQL 的排序算法將存儲引擎中返回的數(shù)據(jù)進行排序然后再將排序后的數(shù)據(jù)返回；

優(yōu)化方案：
1，加大max_length_for_sort_data 參數(shù)

（加大這個參數(shù)，那么就提高比元組長度大的概率，所以就提高走單路排序的概率）

2，去掉不必要的返回字段

（不必要的返回字段越多，元組長度就越長，占的空間就越多，導致排序緩沖區(qū)每次讀取的數(shù)據(jù)量變?。?/p>

3，增大sort_buffer_size （排序緩沖區(qū)）參數(shù)，

（增大的話，排序緩沖區(qū)一次性能讀取的數(shù)據(jù)就會變多）

增大 sort_buffer_size 和 max_length_for_sort_data 參數(shù)，盡量走單路排序，減少臨時文件產(chǎn)生，減少IO，盡量避免走雙路排序。

2、using temporary

http://mysql.taobao.org/monthly/2015/03/04/

3、group by 優(yōu)化

分組

GROUP BY的前提是排序,所以優(yōu)化手段和ORDER BY是一樣的。文章來源地址http://www.zghlxwxcb.cn/news/detail-723188.html

到了這里，關(guān)于01、MySQL-------性能優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！