mysql索引優化實例（MySQL索引優化）

本文目錄一覽：

• 1、mysql優化：覆蓋索引（延遲關聯）
• 2、MySQL性能優化之索引設計
• 3、mysql—索引優化
• 4、mysql–索引優化

mysql優化：覆蓋索引（延遲關聯）

我們都知道InnoDB採用的B+ tree來實現索引的，索引又分為主鍵索引(聚簇索引)和普通索引(二級索引)。

那麼我們就來看下 基於主鍵索引和普通索引的查詢有什麼區別？

舉個栗子：

可以看出我們有一個普通索引k，那麼兩顆B+樹的示意圖如下：

[圖片上傳失敗…(image-9b05f7-1597911217600)]

（註：圖來自極客時間專欄）

當我們查詢** select * from T where k=5 其實會先到k那個索引樹上查詢k = 5，然後找到對應的id為500，最後回表到主鍵索引的索引樹找返回所需數據。

如果我們查詢 select id from T where k=5 **則不需要回表就直接返回。

也就是說，基於非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該盡量使用主鍵查詢。

概念如上，這裡我們還是用例子來說明：

/pre

[圖片上傳失敗…(image-20977-1597911217600)]

（註：圖來自極客時間專欄）

現在，我們一起來看看這條SQL查詢語句的執行流程： select * from T where k between 3 and 5

在這個過程中， 回到主鍵索引樹搜索的過程，我們稱為回表。 可以看到，這個查詢過程讀了k索引樹的3條記錄（步驟1、3和5），回表了兩次（步驟2和4）。

在這個例子中，由於查詢結果所需要的數據只在主鍵索引上有，所以不得不回表。那麼，有沒有可能經過索引優化，避免回表過程呢？

如果執行的語句是select ID from T where k between 3 and 5，這時只需要查ID的值，而ID的值已經在k索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裏面，索引k已經「覆蓋了」我們的查詢需求，我們稱為覆蓋索引。

由於覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

需要注意的是，在引擎內部使用覆蓋索引在索引k上其實讀了三個記錄，R3~R5（對應的索引k上的記錄項），但是對於MySQL的Server層來說，它就是找引擎拿到了兩條記錄，因此MySQL認為掃描行數是2。

上面介紹了那麼多 其實是在為延遲關聯做鋪墊，這裡直接續上我們本次慢查詢的sql：

我們都知道在做分頁時會用到Limit關鍵字去篩選所需數據，limit接受1個或者2個參數，接受兩個參數時第一個參數表示偏移量，即從哪一行開始取數據，第二個參數表示要取的行數。 如果只有一個參數，相當於偏移量為0。

當偏移量很大時，如limit 100000,10 取第100001-100010條記錄，mysql會取出100010條記錄然後將前100000條記錄丟棄，這無疑是一種巨大的性能浪費。

當有這種寫法時，我們可以採用延遲關聯來進行優化，重點關註： SELECT id FROM qa_question WHERE expert_id = 69 AND STATUS = 30 ORDER BY over_time DESC LIMIT 0, 10 ， 這裡其實利用了索引覆蓋，where條件後的expert_id 是有添加索引的，這裡查詢id 可以避免回表，大大提升效率。

工作中會遇到各種各樣的問題，對於一個研發來說最重要的是能夠從這些問題中學到什麼。好久沒有寫博客了，究其原因還是自己變得懶惰了。 (￣ェ￣;)

最後以《高性能Mysql》中的一段話結束：

MySQL性能優化之索引設計

上一篇給小夥伴們講了關於SQL查詢性能優化的相關技巧，一個好的查詢SQL離不開合理的索引設計。這篇小二就來嘮一嘮怎麼合理的設計一個索引來優化我們的查詢速度，要是有不合理的地方…嗯..

當然啦，開個玩笑，歡迎小夥伴們指正！

通常情況下，字段類型的選擇是需要根據業務來判斷的，通常需要遵循以下幾點。

下列各種類型表格內容來自菜鳥教程，權當備忘。

優化建議：

注意： INT(2)設置的為顯示寬度，而不是整數的長度，需要配合 ZEROFILL 使用 。

例如 id 設置為 TINYINT(2) UNSIGNED ，表示無符號，可以存儲的最大數值為255，其中 TINYINT(2) 沒有配合 ZEROFILL 實際沒有任何意義，例如插入數字200，長度雖然超過了兩位，但是這個時候是可以插入成功的，查詢結果同樣為200；插入數字5時，同樣查詢結果為5。

而 TINYINT(2) 配合 ZEROFILL 後，當插入數字5時，實際存儲的還是5，不過在查詢是MySQL會在前面補上一個0，即查詢出來的實際為 05 。

優化建議：

優化建議：

通常來說，考慮好表中每個字段應該使用什麼類型和長度，建完表需要做的事情不是馬上建立索引，而是先把相關主體業務開發完畢，然後把涉及該表的SQL都拿出來分析之後再建立索引。

盡量少建立單值索引( 唯一索引除外 )，應當設計一個或者兩三個聯合索引，讓每一個聯合索引都盡量去包含SQL語句中的 where、order by、group by 的字段，同時確保聯合索引的字段順序盡量滿足SQL查詢的最左前綴原則。

索引基數是指這個字段在表裡總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別字段，性別一共有三個值：男、女、保密，那麼該字段的基數就是3。

如果對這種小基數字段建立索引的話，因為索引樹中只有男、女、保密三個值，根本沒法進行快速的二分查找，同時還需要回表查詢，還不如全表掃描嘞。

一般建立索引，盡量使用那些基數比較大的字段，那麼才能發揮出B+樹快速二分查找的優勢來。

在 where 和 order by 出現索引設計衝突時，是優先針對where去設計索引？還是優先針對order by設計索引？

通常情況下都是優先針對 where 來設計索引，因為通常情況下都是先 where 條件使用索引快速篩選出來符合條件的數據，然後對進行篩選出來的數據進行排序和分組，而 where 條件快速篩選出來的的數據往往不會很多。

對生產實際運行過程中，或者測試環境大數據量測試過程中發現的慢查詢SQL進行特定的索引優化、代碼優化等策略。

終於輪到實戰了，小二最喜歡實戰了。

寫到這裡不得不吐槽一下，這個金三銀四的跳槽季節，年前提離職了，結果離職還沒辦完就封村整整兩個禮拜了，嗚嗚嗚…

上節小二就提到會有個很有意思的小案例，那麼在疫情當下，門都出不去的日子，感覺這個例子更有意思了，咱們來討論一下各種社交平台怎麼做的用戶信息搜索呢。

社交平台有一個小夥伴們都喜歡的功能，搜索好友信息，比如小二熟練的點開省份…城市..性別..年齡..身高…

咳咳咳…小二怎麼可能幹這種事情，小二的心裏只有代碼，嗯…沒錯，就是這樣。

這個就可以說是對於用戶信息的查詢篩選了，通常這種表都是非常大數據量的，在不考慮分庫分表的情況下，怎麼通過索引配合SQL來優化呢？

通常我們在編寫SQL是會寫出類似如下的SQL來執行，有 where、order by、limit 等條件來查詢。

那麼接下來小二一個一個慢慢增加字段來分析分析，怎麼根據業務場景來設計索引。

針對這種情況，很簡單，設計一個聯合索引 (provice, city, sex) 就完事了。

那麼這時候有小夥伴就會說了，很簡單啊，範圍字段放最後咱還是知道的，聯合索引改成 (provice, city, sex, age) 不就可以了。

嗯，是的，這麼乾沒毛病，但是小夥伴們有沒有想過有些人萬一既喜歡帥哥又喜歡美女，別想歪了哈…，挺多小姐姐就既喜歡帥哥又喜歡美女的。

那麼這個時候小姐姐就不搜索性別了，那麼這個時候聯合索引只能用到前兩個字段了，那麼不符合咱們的專業標準啊，咋辦呢？這時候還是有辦法的，咱們只需要動動小腦袋改改SQL就行了，在沒有選擇性別時判斷一下，改成下面這樣就可以了。

咋辦嘞，同樣往聯合索引裏面塞，例如 (provice, city, sex, hobby, xx, age) 。

針對這種多個範圍查詢的話，為了比較好的利用索引，在業務允許的情況下可以使用固定範圍，然後數據庫字段存儲範圍標識就可以了，這樣就轉化為了等值匹配，就可以很好地利用索引了。

例如最後登錄時間字段不記錄最後登錄時間，而是記錄設置字段 is_login_within_seven_days 在7天內有登錄則為1，否則為0，最後索引設計成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。

那麼根據場景最後設計出來的這個索引可能已經可以覆蓋大部分的查詢流量了，那麼如果還有其他一部分熱度比較高的查詢怎麼辦呢，辦法也很簡單啊，再加一兩個索引即可。

例如通常會查詢這個城市比較受歡迎(評分：score)的小姐姐，這時候添加一個聯合索引 (provice, city, sex, score) 那麼就可以了。

可以看出，索引時必須結合場景來設計的，思路就是盡量用不超過3個複雜的聯合索引來抗住大部分的80%以上的常用查詢流量，然後再用一兩個二級索引來抗下一些非常用查詢流量。

以上就是小二要給大家分享的索引設計，如果能動動你發財的小手給小二點個免費的贊就更好啦~

下篇小二就來講講MySQL事務和鎖機制。

mysql—索引優化

索引就是為特定的mysql字段進行一些特定的算法排序，比如二叉樹的算法和哈希算法，哈希算法是通過建立特徵值，然後根據特徵值來快速查找。

1.普通索引:(index)最基本的索引，沒有任何限制  目的:加快數據的查詢速度

2.唯一索引:(unique)  與”普通索引”類似，不同的就是：索引列的值必須唯一，但允許有空值。

3.主鍵索引(primary key) 它 是一種特殊的唯一索引，不允許有空值。

4.複合索引:index(a,b,c)  為了更多的提高mysql效率可建立組合索引，遵循」最左前綴「原則。

5.全文索引:fulltext  僅可用於 MyISAM 表，針對較大的數據，生成全文索引很耗時耗空間。

第一類是myisam存儲引擎使用的叫做b-tree結構，

第二類是innodb存儲引擎使用的叫做聚簇結構（也是一種 b-tree）。 如下圖：

注意：

1.myisam不需要回行處理 

2.innodb不需要回行處理,直接可以獲取數據,因為innodb的儲存引擎是包含了數據和索引文件的,其主鍵索引包含了數據,(唯一索引及普通索是沒有直接包含數據的)

1、索引列不能參與計算

有索引列參與計算的查詢條件對索引不友好（甚至無法使用索引），如from_unixtime(create_time) = ‘2014-05-29’。

原因很簡單，如何在節點中查找到對應key？如果線性掃描，則每次都需要重新計算，成本太高；如果二分查找，則需要針對from_unixtime方法確定大小關係。

因此，索引列不能參與計算。上述from_unixtime(create_time) = ‘2014-05-29’語句應該寫成create_time = unix_timestamp(‘2014-05-29’)。

2、最左前綴匹配

如有索引(a, b, c, d)，查詢條件a = 1 and b = 2 and c 3 and d = 4，則會在每個節點依次命中a、b、c，無法命中d。也就是最左前綴匹配原則。

3、冗餘和重複索引

冗餘索引是指在相同的列上按照相同的順序創建的相同類型的索引，應當盡量避免這種索引，發現後立即刪除。比如有一個索引(A,B)，再創建索引(A)就是冗餘索引。冗餘索引經常發生在為表添加新索引時，比如有人新建了索引(A,B)，但這個索引不是擴展已有的索引(A)

4、避免多個範圍條件

        select user.* from user where login_time ‘2017-04-01’ and age between 18 and 30;

比如想查詢某個時間段內登錄過的用戶：它有兩個範圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時使用它們 .

5、覆蓋索引 (能擴展就不新建)

如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那麼就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

1.索引條目遠小於數據行大小，如果只讀取索引，極大減少數據訪問量2.索引是有按照列值順序存儲的，對於I/O密集型的範圍查詢要比隨機從磁盤讀取每一行數據的IO要少的多

6、選擇區分度高的列作索引

如，用性別作索引，那麼索引僅能將1000w行數據劃分為兩部分（如500w男，500w女），索引幾乎無效。

區分度的公式是count(distinct ) / count(*)，表示字段不重複的比例，比例越大區分度越好。唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前的區分度趨近於0。

7、刪除長期未使用的索引

場景一(覆蓋索引 5)

索引應該建在選擇性高的字段上（鍵值唯一的記錄數/總記錄條數），選擇性越高索引的效果越好、價值越大，唯一索引的選擇性最高；

組合索引中字段的順序，選擇性越高的字段排在最前面；

where條件中包含兩個選擇性高的字段時，可以考慮分別創建索引，引擎會同時使用兩個索引（在OR條件下，應該說必須分開建索引）；

不要重複創建彼此有包含關係的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

組合索引的字段不要過多，如果超過4個字段，一般需要考慮拆分成多個單列索引或更為簡單的組合索引；

不要濫用索引。因為過多的索引不僅僅會增加物理存儲的開銷，對於插入、刪除、更新操作也會增加處理上的開銷，而且會增加優化器在選擇索引時的計算代價。

因此太多的索引與不充分、不正確的索引對性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應該經過仔細分析，要有建立的依據。

mysql–索引優化

        索引覆蓋是指如果查詢的列恰好是索引的一部分,那麼查詢只需要在索引文件上進行,不需要回行到磁盤再找數據。這種查詢速度非常快,稱為」索引覆蓋」

        1查詢頻繁    2區分度高    3長度小    4盡量能覆蓋常用查詢字段

        索引長度直接影響索引文件的大小,影響增刪改的速度,並間接影響查詢速度(佔用內存多)。因此對於一些長短不同的位元組，我們會針對列中的值,從左往右截取部分,來建索引。但是：

        1:截的越短, 重複度越高,區分度越小, 索引效果越不好

        2:截的越長, 重複度越低,區分度越高, 索引效果越好,但帶來的影響也越大–增刪改變慢,並間影響查詢速度.

        所以,我們要在  區分度 + 長度  兩者上,取得一個平衡（ distinct 去重 ）

        select count (distinct left (word,6)) / count (*) from tablename;

        對於一般的系統應用區別度能達到 0.1 ,索引的性能就可以接受.

        alter table tablename add index word(word(4));

        給字符串類型的字段建立索引效率不高，但是必須要經常查這個字段怎麼建索引？

        比如說一個字段url，類型是字符串。那麼可以建一個字段 crcurl 來存儲url字段crc32後的值，並給 crcurl 建立索引。

        crc32：循環冗餘校驗。根據網上數據包或計算機文件等數據產生簡短固定位數校驗碼的一種散列函數，主要用來檢測或校驗數據傳輸或者保存後可能出現的錯誤。生成的數字在傳輸或者存儲之前計算出來並且附加到數據後面，然後接收方進行檢驗確定數據是否發生變化。一般來說，循環冗餘校驗的值都是32位的整數。

        crc32 是整形，在MySQL中，給整形字段建立索引效率比較高，crc32雖然不能確保唯一性，但是無礙，相同的機率也是極小，關鍵是可以大大減少查詢的範圍，給crcurl這個字段建立索引，查詢的時候帶上crcurl字段就可以利用到索引。

            不允許翻過100頁（百度搜索一般到70頁左右） 

            首先我們直接大數據分頁limit 5000000,10  發現耗時4.41秒

            接下來我們轉換方式使用where條件查詢，只耗時0.02秒

            2次的查詢結果不一致，這是因為數據被物理刪除過有空洞.，因此我們可以追加軟刪除功能

            分析:優化思路是 不查,少查,查索引,少取.

            我們現在必須要查,則只查索引,不查數據,得到id.

            再用id去查具體條目.  這種技巧就是延遲索引.

        分析：limit是先查詢再越過，也就是說我們先查詢出所有數據再進行跳躍，上圖我們越過500W頁，還使用了inner join  內存並沒有崩掉，這是因為我們子句tmp臨時表中只查詢了id（索引覆蓋，不需要回行去磁盤找數據了）然後拿到這10個id 分別查詢這10條數據 。

        排序可能發生2種情況:

        1:對於覆蓋索引,直接在索引上查詢時,就是有順序的, using index

        2:先取出數據,形成臨時表做filesort(文件排序,但文件可能在磁盤上,也可能在內存中)

        我們的爭取目標：取出來的數據本身就是有序的! 利用索引來排序,那麼什麼時候發生索引排序呢？即查詢索引和order by的字段是同一個字段

        goods表中 cat_id與shop_price組成聯合索引：

            select goods_id,cat_id,shop_price from goods where cat_id=4 order by shop_price;    可以直接利用索引來排序,

             using where按照shop_price索引取出的結果,本身就是有序的

             select goods_id,cat_id,shop_price from goods order by click_count;

              using filesort用到了文件排序,即取出的結果再次排序

        重複索引是指 在同1個列(如age), 或者順序相同的幾個列(age,school), 建立了多個索引,稱為重複索引,重複索引沒有任何幫助,只會增大索引文件,拖慢更新速度。

        冗餘索引是指2個索引所覆蓋的列有重疊, 稱為冗餘索引。比如x,m,列，加索引 index x(x),  index xm(x,m) x,xm索引, 兩者的x列重疊了,  這種情況,稱為冗餘索引. （mx, xm 不是重複的,因為列的順序不一樣）