提示：本文毫無可讀性，完全是自己的讀書筆記

葉子頁的分裂

主鍵索引一次葉子頁分裂將導(dǎo)致其余索引上大量的磁盤I/O；對(duì)主鍵索引列的更新操作，會(huì)引起索引行的異動(dòng)，從而導(dǎo)致葉子頁的分裂。? ??

索引的選擇性（與Cardinality有關(guān)）

選擇性=cardinality/總行數(shù)

選擇性越是接近于1越好，換言之，cardinality越大越好

以常見的反例“性別”來進(jìn)行距離，cardinality=2，選擇性無限趨近于0,這無疑是非常糟糕的

最差情況下的選擇性比平均選擇性更重要，因?yàn)槿绻疹櫫似骄x擇性而忽略最差選擇性的話，有可能造成某一個(gè)SQL查詢特別慢

選擇性的好壞，與后面提到的索引片有很大的關(guān)系；選擇性越好，索引片越窄

cardinality是一個(gè)估算值（忽略同一字段建2個(gè)索引的睿智操作，不是我干的！）

相同字段下，不同時(shí)段創(chuàng)建的2個(gè)單一索引，Cardinality值不同

懷疑是統(tǒng)計(jì)沒有更新，手動(dòng)執(zhí)行更新

（t表1500萬行數(shù)據(jù)，在空閑庫執(zhí)行下面的語句時(shí)間338s，不要在忙時(shí)去執(zhí)行這個(gè)語句）

alter table t engine=innodb;

執(zhí)行后的索引信息如下

可以看到值與之前統(tǒng)計(jì)的值不同，而且同一字段對(duì)應(yīng)的2個(gè)索引的cardinality值仍然不同

此外，在show index這里展現(xiàn)的cardinality估算得并不準(zhǔn)確，甚至可能出現(xiàn)非常大的偏差

比如這個(gè)例子中的monthly_settlement，看起來在這個(gè)索引中，這一列的選擇性是最好的，station_id的選擇性看起來就沒這么好

然而實(shí)際情況并非如此

所以cardinality只能當(dāng)做一般性的參考，如果要嚴(yán)格的計(jì)算選擇性，還是需要用? ?“distinct 列名/總行數(shù)”? ? ?這種方式去計(jì)算，值越趨近于1，說明該列選擇性越好

組合謂詞的選擇性（謂詞：前面提到的3要素中的索引列）

簡(jiǎn)言之就是2個(gè)單列索引的效果可能遠(yuǎn)不如這2個(gè)字段的聯(lián)合索引

優(yōu)化器在評(píng)估訪問路徑成本時(shí)，必須先評(píng)估索引的選擇性。（其實(shí)選擇性的好壞也是三星索引的第一顆星）

等價(jià)的索引

where a=:a and b=:b

如果創(chuàng)建索引(a,b)和索引(b,a)，這2個(gè)索引是等價(jià)的，因?yàn)閣here語句后面跟的2個(gè)都是等值條件，如果其中一個(gè)是范圍條件，那么這2個(gè)索引就不等價(jià)了

回表采用的是隨機(jī)讀

三星索引

第一顆星：索引片的寬窄；

第二顆星：索引是否能夠處理排序；（避免排序）

第三顆星：查詢列是否在索引中

一般來說，第一顆星要比第二顆星重要，一個(gè)盡量窄的索引片比避免排序要重要；但這只是一般來說

下列例子說明了第一顆星的重要性

#sql語句

SELECT MAX(id)

FROM t

WHERE '2019-01-07 23:59:59' >= create_time

GROUP BY user_id;

索引1：idx_ct_uid(create_time,user_id)

索引2：idx_ct（create_time），idx_uid（user_id）

user_id是一個(gè)varchar（30）的列

全表行數(shù)為270W行，使用索引1的情況下，執(zhí)行時(shí)間為6s，使用索引2的情況下，執(zhí)行時(shí)間為1.5s

歸結(jié)原因，條件列是一個(gè)范圍查詢，索引1能用到的其實(shí)只有create_time這部分，但是user_id列的加入，使得這個(gè)聯(lián)合索引的索引片寬度大增，因此花了更多的時(shí)間去掃描。

寬索引

一個(gè)索引如果（至少）包含了第3顆星，那么這個(gè)索引就是一個(gè)寬索引

困難謂詞（索引失效問題）

簡(jiǎn)單來說就是參照下面的口訣：

全值匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計(jì)算，范圍之后全失效

like百分寫最右，覆蓋索引不寫星

不等空值還有or，索引失效要少用

表連接的索引

關(guān)于嵌套循環(huán)

本地謂詞和連接謂詞

只用于訪問一張表的謂詞被稱為本地謂詞

定義了表和表之間的連接關(guān)系的謂詞被稱為連接謂詞

拋開比較復(fù)雜的嵌套查詢

只有本地謂詞的那張表，將會(huì)成為外表，而另一張表自然成為了內(nèi)表

外表也就是驅(qū)動(dòng)表，要求是盡量的小

外表的小并不是指外表的總行數(shù)小，而是指經(jīng)過外表的過濾因子之后取得的索引片比較小，這樣會(huì)獲得更好的性能

在嵌套查詢中，內(nèi)表產(chǎn)生大量隨機(jī)讀取是影響性能的一個(gè)關(guān)鍵因素，因此內(nèi)表需要有一個(gè)比較好的寬索引，并且以連接謂詞作為前導(dǎo)列（也就是連接謂詞作為最左）

eg

select .... from A,B

where A.a=:a

and B.b=A.b;

在上面這個(gè)查詢中，A.a是A表的本地謂詞，所以A表很明顯的就是外表，而B表就是內(nèi)表，其中B.b是B表的連接謂詞，為了減少內(nèi)表的隨機(jī)讀，內(nèi)表上需要有一個(gè)合適的以B.b為前導(dǎo)列的寬索引。

（由于這里的連接條件里只有B.b=A.b，如果B.b的選擇性很差，那么這個(gè)內(nèi)表的查詢效率有可能很低，這種情況下，可以通過補(bǔ)全一些謂詞去提高連接謂詞的選擇性（冗余字段），可以使內(nèi)表盡可能快的查出結(jié)果，畢竟一般來說外表都是小表，內(nèi)表相對(duì)較大，對(duì)性能的影響是比較大的。該段參考《數(shù)據(jù)庫索引設(shè)計(jì)與優(yōu)化》第8章的那個(gè)例子（CCTRY），該案例中有一個(gè)補(bǔ)全FF缺陷的技巧； 這個(gè)技巧在P178頁中被描述為向下反范式，也就是內(nèi)表加列；與之對(duì)應(yīng)的還有向上反范式，也就是外表加列，不過需要注意所加冗余列的易維護(hù)性）

對(duì)于or的優(yōu)化

可以改寫成union+order by的形式

但是否能優(yōu)化好，取決于一些條件是否完全匹配

有一些語句即使索引優(yōu)化的很好，但union + order by仍然造成性能問題

--2019.7.25驗(yàn)證2年前的筆記

使用索引進(jìn)行排序：（避免排序）

order by也需要滿足索引最左前綴要求，才能用索引排序

比如索引為idx(b,c)

select xxx from table_a where a='x' order by b,c;

除此之外，索引列是一個(gè)常量的情況下，即使order by的部分不滿足最左，也可以使用

比如索引為idx(a,b,c)；索引列為常量的情況，說的就是where a='x'（如果是a>'x'，或者是a in('x','y')則屬于范圍，不屬于常量）

所以下列索引列和order by組成了a,b,c的順序，滿足了idx(a,b,c)的順序

select xxx from table_a where a='x' order by b,c;

但是如果索引列為a>'x'，這是一個(gè)范圍查詢，那么上面的語句無法使用idx(a,b,c)進(jìn)行索引排序（下面的語句可以，因?yàn)閛rder by滿足了最左）

select xxx from table_a where a>'x' order by a,b;

關(guān)于最左前綴之前的一個(gè)理解錯(cuò)誤的地方

idx(a,b,c)

select xxx from table_a where c='t' and b='y' and a='x'這樣仍然會(huì)走索引，雖然順序不一樣，但是a,b,c都在where條件里面，并不需要把where寫成a,b,c的順序才行。

一些無法使用索引排序的例子；（索引仍然為idx(a,b,c)）

1.逆序?qū)е碌姆较虿粚?duì)

select xxx from table_a where a='x' order by b desc,c desc;

上面2張圖對(duì)比可以看出desc字段沒有走索引，但是從key_len來看2者沒有變化，key_len記錄的應(yīng)該只是where后面走索引的字段，而對(duì)于排序部分是不記錄的，至少5.7版本是這樣

2.order by有其他列

select xxx from table_a where a='x' order by b,d;

3.中間列（b）斷了

select xxx from table_a where a='x' order by c;

4.索引列有一個(gè)是范圍查詢

select xxx from table_a where a='x' and b in ('x','y') order by c;

5.延遲關(guān)聯(lián)，本表會(huì)被當(dāng)成第二張表，因此也無法使用

eg.排序走索引測(cè)試

涉及的字段,type,status,create_time,update_time（重要：下文以a,b,c,d代替）

idx_0對(duì)上述4個(gè)(a,b,c,d)字段建有聯(lián)合索引

idx_1對(duì)前面2個(gè)(a,b)字段建有聯(lián)合索引

語句1

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time;

where a=,b= order by c類型，加上查詢字段的d，覆蓋索引，排序走索引

語句2

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time desc;

where a=,b= order by c desc類型，雖然排序是倒序，但仍然走了索引，并沒有filesort

語句3

select update_time from tra_trade where type='POS_ONLINE_PAY' and status!='SUCCESS' order by create_time;

where a=,b!= order by c 類型，出現(xiàn)b列出現(xiàn)了不等于，相當(dāng)于(a,b,c)中的b斷了，a肯定能走索引的，從key_len來看，b也走了，后面的c走不了，c出現(xiàn)了排序

語句4

select update_time from tra_trade where type='POS_ONLINE_PAY' and status!='SUCCESS' order by type,status,create_time;

where a=,b!= order by a,b,c 類型，全都走了索引

語句5

select update_time from tra_trade where type='POS_ONLINE_PAY' and status!='SUCCESS' order by type,create_time;

where a=,b!= order by a,c 類型，排序中的(a,b,c)斷了，c走不了索引，出現(xiàn)了排序

#前面5個(gè)類型屬于必須要記住的分類，后面的十幾個(gè)語句算是更細(xì)節(jié)的探索

語句6

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by status,update_time;

where a=,b= order by b,d 類型，排序中的(b,c,d)斷了，d肯定走不了索引會(huì)出現(xiàn)排序；這個(gè)語句的疑問在于b不滿足最左，同時(shí)也不像前面的where a,b order by c那樣滿足連貫，b是否能走索引？

語句6變種

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by status;

將語句6中排序的d列（update_time）去掉后，語句變成了where a,b order by b類型，沒有產(chǎn)生排序

語句7

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by status,create_time;

where a=,b= order by b,c 類型，語句7是前面語句6變種的延伸，與6的變種一樣，沒有產(chǎn)生排序

語句8

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by status,create_time desc;

where a=,b= order by b,c desc 類型，與7一樣，沒有產(chǎn)生排序

語句9

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time desc,status,type;

語句10

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time,status,type;

語句11

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time,type;

語句12

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time,status;

語句13

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time,type,update_time;

語句14

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time,status,update_time;

9和10都看作是where a=,b= order by c,b,a 類型，區(qū)別只是c是否有帶desc；11是where a=,b= order by c,a 類型；12是where a=,b= order by c,b 類型；13是where a=,b= order by c,a,d 類型；11是where a=,b= order by c,b,d 類型；全都沒有產(chǎn)生排序

語句15

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by update_time;

where a=,b= order by d 類型，(a,b,d斷裂)，d肯定走不了索引，產(chǎn)生排序

語句16

select update_time from tra_trade where type='POS_ONLINE_PAY' and status='SUCCESS' order by status,payee_charge_amount;

where a=,b= order by b,e 類型，e都不在索引里，肯定產(chǎn)生排序，此外排序列不在索引列，using index都沒了，把覆蓋索引都給破壞了

語句17

select * from tra_trade force index (idx_0) where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time;

select * from where a=,b= order by c類型

語句18

select * from tra_trade force index (idx_0) where type='POS_ONLINE_PAY' and status='SUCCESS' order by create_time desc;

select * from where a=,b= order by c desc類型

17和18的排序一個(gè)正序一個(gè)倒序，但查詢列從索引的d字段變成了*，首先排序肯定是沒有產(chǎn)生的，但有意思的是執(zhí)行計(jì)劃里面的extra字段變了；正序走了icp，倒序就走了個(gè)using where

18個(gè)語句的測(cè)試結(jié)論：

1.order by中的倒序，不會(huì)產(chǎn)生排序（filesort）；如果你的執(zhí)行計(jì)劃里面產(chǎn)生了filesort，起碼不能簡(jiǎn)單歸結(jié)于是排序中有desc

2.如果where部分是常量，且查詢部分沒有出現(xiàn)中間斷裂的情況，那么order by可以不用遵循最左原則

3.如果where部分有范圍（不等于相當(dāng)于范圍），那么order by必須遵循最左原則

4.where部分與order by部分必須避免出現(xiàn)中間斷裂的情況，只要出現(xiàn)中間斷裂，必然產(chǎn)生filesort；只要不出現(xiàn)中間斷裂，無論order by部分字段的先后順序如何都不會(huì)產(chǎn)生filesort

5.排序中的desc并不會(huì)產(chǎn)生filesort，但desc是否會(huì)影響數(shù)據(jù)的過濾，不得而知，至少從17和18這2句的執(zhí)行計(jì)劃可以發(fā)現(xiàn)extra有明顯的不同

#第4點(diǎn)字面很難看明白，特別解釋一下

首先說明一下怎樣算斷裂：斷裂點(diǎn)通常有2個(gè)地方，一個(gè)是where和order by接壤的地方，比如where后面是ab，order by是d，中間的c沒有，就斷掉了；另一個(gè)地方就是order by自身，比如where是ab，order by是abd，也是漏掉了c；但如果where部分是ab，order by部分是ac，看起來order by部分好像斷掉了，實(shí)際上把where和order by連起來看，abc都已經(jīng)存在了，所以就不存在中間斷掉的情況。 此外，前面都是在where條件為常量的情況下，如果where部分存在范圍，那么order by必須滿足最左，并且一旦order by中存在斷裂則視為斷裂

結(jié)論第4點(diǎn)前半句中標(biāo)粗的“避免中間斷裂”的意思是where部分是ab，索引部分可以是c，或者cd，或者abc，或者是ac，或者bc；總之確保where和order by連起來是一個(gè)ab,c或者ab,cd或者ab,abc或者ab,ac或者ab,bc之類的模式，但絕對(duì)不能出現(xiàn)ab,d這種模式（也就是說where或者order by中只有a,b,d但），一旦出現(xiàn)斷裂，則必然出現(xiàn)filesort

第4點(diǎn)后半句中的“部分字段的先后順序”的意思是無論是order by abc還是cba還是cab都無關(guān)緊要，都不會(huì)出現(xiàn)filesort