Mysql-聚簇索排序慢案例分析

標籤：img   orderby   佔用   href   margin   案例   還原   有趣的   csdn   

為什麼當 執行select較多時應當使用mysiam引擎呢？尤其是在有索引的情況下 本篇章依託一個實際應用，分析一下。

一.前言：網上看到有一個有趣的現象，一個有1W資料量的表，執行不同的orderby條件，查詢時間非常大，這個是實際應用中確實出現的問題？？為什麼呢？ 二.分析 a).情況描述：

1.有主鍵id，聯合索引(id,ver);用前者當orderby查詢慢，用後者orderby查詢會很快；2.每一行的資料量挺大3.id為主索引,而select查詢的欄位也僅僅有id，那麼不就是 索引覆蓋了唄，不用到物理磁碟回行資料，在索引上就能拿到要的資料了，但本應該查詢更快的卻慢了。 Mysql-索引覆蓋

b).分析：

肯定用的不是mysiam引擎，若是的話用這兩個索引查詢，其實速度是差不多的，因為索引上存的都是一個物理行的地址嘛，實際佔有的資料量又不大。但如果是innodb就不一樣了，它的主索引下邊可是拖家帶口存放著該行的所有資料的。

c).結論：

1.主因：用的innodb引擎

是聚簇索引，主鍵ID索引還下拖家帶口的掛著該行的其他資料，導致沿著ID排序時，要跨過好多小塊才能查詢遍曆每個ID；(而mysiam下頭沒那麼多資料，跨過相同的資料區塊會更快，遍曆更多的行)

2.從因：有幾個欄位下的資料量比較大，即拖家帶口帶的人還比較多，資料量比較大。每行資料量大，在磁碟儲存時佔用的塊兒也多3. 當時mysiam引擎時不存在這個問題

d).映射結論：

當 執行select較多時，應當使用mysiam引擎， 當執行 insert,update多時使用innodb引擎

更多結論請看： Mysql-索引總結

三.類比測試還原上面所說的條件，建立連個表， 控制變數，除了引擎不同外，其餘條件相同，主鍵ID主索引，聯合索引（id,ver）。 1.建立表t7，mysiam引擎
2.隨機插入一萬條資料
3.執行查詢語句，查看時間

顯然，時間相差不太大，都是一個量級的。

4.建立表t8，innodb引擎

5.隨機插入一萬條資料小插曲，按照上邊指令碼執行語句，等待時間非常長，為什麼呢？因為其為聚簇索引，有主鍵索引ID，在建立主鍵索引的時候，行的資料區塊大量移動，有分裂移動的時間在裡邊。操作是先刪除主鍵索引ID，插入資料後在add primary key（id）,再建立主鍵索引結構
6.執行查詢語句，查看時間

顯然，時間相差不太大，都是一個量級的。

原因：兩個語句都用到了聚簇索引，只是主鍵的跨塊兒太多， 而聯合索引為次級索引，下邊無資料，塊兒少，遍曆快。

7.總分析，只有t8表(innodb)的按照主鍵索引排序耗時多，其餘還好

時間排序結論：innodb.主索引 > innodb.次索引 > mysiam

效率將近差了30倍，問題處在了哪裡？

1.主因，沿著主鍵做order by排序，查詢時會跨頁很多塊，時間增加2.如果沒有幾個長的char欄位，資料區塊也不大，也就不會造成這麼大的差別，

比如，刪除表中str1,str2,str3欄位，查詢時間也會大大減少，差異不明顯

Mysql-聚簇索排序慢案例分析