MySQL 5.5的InnoDB到底有何問(wèn)題？從MySQL5.7就開(kāi)始，開(kāi)發(fā)人員決定了InnoDB統(tǒng)合二級(jí)索引的，常規(guī)自下而上的方法，而不是早中期版本中自上而下的方法了。在這篇文章中，我們將一個(gè)示例來(lái)

MySQL 5.5的InnoDB到底有何問(wèn)題？

從MySQL5.7就開(kāi)始，開(kāi)發(fā)人員決定了InnoDB統(tǒng)合二級(jí)索引的，常規(guī)自下而上的方法，而不是早中期版本中自上而下的方法了。在這篇文章中，我們將一個(gè)示例來(lái)那說(shuō)明該如何最終形成InnoDB索引。到最后，我將請(qǐng)解釋怎么為innodb_fill_factor設(shè)置更比較合適的值。

索引構(gòu)建體系過(guò)程

在有數(shù)據(jù)的表上構(gòu)建體系索引，InnoDB中有以下幾個(gè)階段：1.加載階段（從聚簇索引讀取數(shù)據(jù)并構(gòu)建體系二級(jí)索引條目）2.合并排序階段3.插入到階段（將排序記錄插到二級(jí)索引）在5.6版本之前，MySQL通過(guò)四次直接插入一條記錄來(lái)構(gòu)建二級(jí)索引。這是一種“從上而下”的方法。搜索插到位置從樹(shù)的根部（頂部）就開(kāi)始并達(dá)到葉頁(yè)（底部）。該記錄直接插入光標(biāo)打向的葉頁(yè)上。在查找直接插入位置和進(jìn)行業(yè)面word文檔合并和合并方面開(kāi)銷不大。從MySQL5.7結(jié)束，添加索引期間的插到階段不使用“排序索引形成完整”，也一般稱“批量索引讀取”。在這種方法中，索引是“由外及里”最終形成的。即葉頁(yè)（底部）簡(jiǎn)單的方法構(gòu)建，后再非葉級(jí)別待到根（頂部）。

示例

在這些情況下不使用排序的索引統(tǒng)合：

ALTERTABLEt1ADDINDEX（求求求CREATEINDEX）

ALTER TABLE t1 ADD FULLTEXT INDEXALTER TABLE t1 ADD COLUMN,ALGORITHM INPLACE

OPIMIZEt1

對(duì)于到最后兩個(gè)業(yè)務(wù)應(yīng)用程序，ALTER會(huì)創(chuàng)建角色一個(gè)中間表。中間表索引（主要和都無(wú)所謂）在用“排序索引統(tǒng)合”統(tǒng)合。

算法

在0級(jí)別創(chuàng)建頁(yè)，還要甚至于頁(yè)創(chuàng)建戰(zhàn)隊(duì)一個(gè)游標(biāo)

使用0級(jí)別處的游標(biāo)插入到頁(yè)面，等到填進(jìn)

頁(yè)面被填滿后，修改一個(gè)兄弟頁(yè)（千萬(wàn)不能插到到兄弟頁(yè)）

為當(dāng)前的整頁(yè)創(chuàng)建戰(zhàn)隊(duì)節(jié)點(diǎn)指針（子頁(yè)中的大于鍵，子頁(yè)碼），并將節(jié)點(diǎn)指針再插入上一級(jí)（父頁(yè)）

在較高級(jí)別，檢查游標(biāo)有無(wú)已gprs定位。假如沒(méi)有，請(qǐng)為該級(jí)別創(chuàng)建戰(zhàn)隊(duì)父頁(yè)和游標(biāo)

在父頁(yè)插到節(jié)點(diǎn)指針

假如父頁(yè)已塞滿，請(qǐng)重復(fù)一遍步驟3,4,5,6

現(xiàn)在再插入兄弟頁(yè)并使游標(biāo)指向兄弟頁(yè)

在所有再插入的末尾，每個(gè)級(jí)別的游標(biāo)朝最右邊的頁(yè)。再提交所有游標(biāo)（意味著什么再提交可以修改頁(yè)面的迷你型事務(wù)，能量所有鎖存器）

為簡(jiǎn)單起見(jiàn)，上列算法跳過(guò)了或是壓解頁(yè)和BLOB（外部存儲(chǔ)的BLOB）如何處理的細(xì)節(jié)。

通過(guò)由下的最終形成索引為簡(jiǎn)單點(diǎn)起見(jiàn)，假設(shè)不成立子頁(yè)和非子頁(yè)中容許的最大記錄數(shù)為3

CREATETABLEt1(aINT PRIMARY KEY,b INT,c BLOB);

INSERTINTOt1VALUES(1,11,嗨小伙伴們111);

INSERTINTOt1VALUES(2,22,hello!222);

INSERTINTOt1VALUES(3,33,hello,hello333);

INSERTINTOt1VALUES(4,44,hello!444);

INSERTINTOt1VALUES(5,55,嗨小伙伴們555);

INSERTINTOt1VALUES(6,66,hi~666);

INSERTINTOt1VALUES(7,77,hello!777);

INSERT INTO t1 VALUES(8,82,hello!888);

INSERTINTOt1VALUES(9,99,嗨小伙伴們999);

INSERTINTOt1VALUES(10,1010,hi~101010);

ALTERTABLEt1ADDINDEXk1(b);

InnoDB將主鍵字段賞分到二級(jí)索引。二級(jí)索引k1的記錄格式為(b,a)。在排序階段成功后，記錄為：

(11,1),(22,2),(33,3),(44,4),(55,5),(66,6),(77,7),(78,8),(99,9),(1010,10)

初始再插入階段

讓我們從有記錄(11,1)正在。

在0級(jí)別（葉級(jí)別）創(chuàng)建家族頁(yè)

修改一個(gè)到頁(yè)的游標(biāo)

所有插到都將轉(zhuǎn)到此頁(yè)面，待到它填滿了

箭頭不顯示游標(biāo)當(dāng)前朝的位置。它目前位處第5頁(yè)，下一個(gè)插入到將投到此頁(yè)面。

還有兩個(gè)閑時(shí)插槽，所以再插入記錄(22,2)和(33,3)的很簡(jiǎn)單點(diǎn)是對(duì)下一條記錄(44,4)，頁(yè)碼5已滿（前面提到的假設(shè)大的記錄數(shù)為3）。這那是步驟。頁(yè)填充時(shí)的索引統(tǒng)合

創(chuàng)建家族一個(gè)兄弟頁(yè)，頁(yè)碼6

別直接插入兄弟頁(yè)

在游標(biāo)處遞交頁(yè)面，即小巧事務(wù)提交，釋放出鎖存器等

才是再提交的一部分，創(chuàng)建角色節(jié)點(diǎn)指針并將其插到到【當(dāng)前級(jí)別1】的父頁(yè)面中（即在1級(jí)別）

節(jié)點(diǎn)指針的格式(子頁(yè)面中的最大值鍵,子頁(yè)碼)。第5頁(yè)的小于鍵是(11,1)。在父級(jí)別再插入記錄((11,1)，5)。

1級(jí)別的父頁(yè)尚不存在，MySQL創(chuàng)建家族頁(yè)碼7和打向頁(yè)碼7的游標(biāo)。

將((11,1)，5)直接插入第7頁(yè)

現(xiàn)在，返回到0級(jí)并修改從第5頁(yè)到第6頁(yè)的鏈接，道理一樣

0級(jí)別的游標(biāo)現(xiàn)在打向兄弟頁(yè)，頁(yè)碼為6

將(44,4)插入到第6頁(yè)

下另一個(gè)直接插入-(55,5)和(66,6)-很簡(jiǎn)單，它們回到第6頁(yè)。

插入記錄(77,7)類似(44,4)，之外父頁(yè)面（頁(yè)面編號(hào)7）巳經(jīng)修真者的存在因此它有兩個(gè)以上記錄的空間。首先將節(jié)點(diǎn)指針((44,4),8)插到第7頁(yè)，后再將(77,7)有記錄到同級(jí)8頁(yè)中。

插到記錄(89,8)和(99,9)很簡(jiǎn)單點(diǎn)，因?yàn)榈?頁(yè)有兩個(gè)多余的時(shí)間插槽。

下另一個(gè)直接插入(1010,10)。將節(jié)點(diǎn)指針((77,7),8)插入1級(jí)別的父頁(yè)（頁(yè)碼7）。MySQL在0級(jí)創(chuàng)建戰(zhàn)隊(duì)同級(jí)頁(yè)碼9。將記錄(1010,10)插到第9頁(yè)并將光標(biāo)可以修改這一點(diǎn)頁(yè)面。以此類推。在上面的示例中，數(shù)據(jù)庫(kù)在0級(jí)別重新提交到第9頁(yè)，在1級(jí)別并提交到第7頁(yè)。

我們現(xiàn)在有了三個(gè)求下載的B-tree索引，它是自下誠(chéng)信立院統(tǒng)合的！

索引再填充因子全局變量innodb_fill_factor應(yīng)用于設(shè)置里插入到B-tree頁(yè)中的空間量。默認(rèn)值為100，它表示在用整個(gè)業(yè)面（不和頁(yè)眉）。聚簇索引更具innodb_fill_factor100的無(wú)需承擔(dān)項(xiàng)。在這種下，聚簇索引也空間的1/16盡量空閑。即6.25%的空間用于未來(lái)的DML。

值80意味著MySQL建議使用了80%的頁(yè)空間填充，確認(rèn)好20%于未來(lái)的更新。如果不是innodb_fill_factor100則沒(méi)有余下空間供未來(lái)插入二級(jí)索引。如果不是在再添加索引后，希望表上有更多的DML，則可能導(dǎo)致業(yè)面全部拆分并再一次單獨(dú)設(shè)置。在情況下，我建議你建議使用80-90之間的值。此變量還會(huì)影響不大在用OPTIMIZETABLE和ALTER TABLE DROP COLUMN,ALGOITHMINPLACE重新創(chuàng)建戰(zhàn)隊(duì)的索引。也不可能可以設(shè)置太低的值，比如少于50。是因?yàn)樗饕龝?huì)占用資源實(shí)在是浪費(fèi)更多的磁盤(pán)空間，值較低時(shí)，索引中的頁(yè)數(shù)較多，索引統(tǒng)計(jì)信息的采樣肯定又不是最佳的。優(yōu)化器可以你選更具次優(yōu)統(tǒng)計(jì)信息的錯(cuò)誤查詢計(jì)劃。

排序索引構(gòu)建的優(yōu)點(diǎn)

沒(méi)有頁(yè)面表格合并（不包括壓解表）和合并

沒(méi)有反復(fù)重復(fù)搜索插入位置

插到絕對(duì)不會(huì)被系統(tǒng)重裝記錄（頁(yè)分配.），而重裝日志子系統(tǒng)的壓力較小

缺點(diǎn)

ALTER正在進(jìn)行時(shí)，插入到性能降底Bug＃82940，但在后續(xù)版本中計(jì)劃修復(fù)。

sock 文件如何生成？

3種比較普遍的情況

鏈接信息的參數(shù)有不對(duì)的，比如，密碼錯(cuò)了等。解決方案，這個(gè)帳號(hào)用mysqlclient工具測(cè)試連接上幫一下忙。

sock文件在mysql中配置（象在文件中配置）的與MySQLdb默認(rèn)去連的并非上網(wǎng)通路徑。解決方案，(userroot,dbmysql,passwd111,hostlocalhost,unix_socket{path_did_sock})

服務(wù)什么也沒(méi)開(kāi)起來(lái)。