全網同名IT老哥2021-04-30 16:41:06
介紹
上一篇文章中介紹了MySQL的索引基本原理以及常見的索引種類，這邊文章的重點在於如何構建一個高效能的MySQL索引，從中你可以學到如何分析一個索引是不是好索引，以及如何構建一個好的索引。
索引誤區
多列索引
一個索引的常見誤區是為每一列建立一個索引，如下面建立的索引：
CREATE TABLE `t` （ `c1` varchar（50） DEFAULT NULL， `c2` varchar（50） DEFAULT NULL， `c3` varchar（50） DEFAULT NULL， KEY `c1` （`c1`）， KEY `c2` （`c2`）， KEY `c3` （`c3`） ） ENGINE=InnoDB DEFAULT CHARSET=utf8；
t表裡有三列，並且為每列建立了一個索引。建立索引的人為了能夠快速訪問表中的任何一列，因此為每一列添加了一個單獨的索引。在多個列上建立索引通常並不能很好的提高MySQL查詢效能，雖然說MySQL 5。0之後引入了索引合併策略，可以將多個單列索引合併成一個索引，但這並不總是有效的。同時建立多個索引的時候還會增加資料插入的成本，在插入資料的時候需要同時維護多個索引的寫入操作。
索引的計算
看下面這條sql語句：
select name from student where id + 1 = 5
即使我們在student表的id列上建立索引，上面的這條SQL語句也無法使用索引。SQL語句中索引欄位不能是表示式的一部分，也不能是函式的引數。
索引的長度以及選擇性
儘量不要在一個很長的列上使用索引，否則會導致索引佔用的空間很大，同時在進行資料的插入和更新的時候意味著更慢的速度。因此使用uuid列作為索引並不是一個好的選擇。從上一篇文章中我們可以知道，為了加快資料的訪問索引是需要常駐記憶體的，假如說我們把64位uuid作為索引，那麼隨著表中資料量的增加索引的大小也在急劇增加。同時因為uuid並沒有順序性，因此在資料插入的時候都需要從根節點找到當前索引的插入位置，如果同一個節點中的索引大小達到上限，還會導致節點分裂，更加降低了插入速度。
建立索引另外一個需要考慮的是索引的選擇性，通常情況下我們會使用選擇性高的列作為索引，但是也不一定一直是這樣，下一節會介紹如何權衡索引的選擇性。
建立高效能索引
選擇正確的索引順序
在選擇索引的順序的時候有一個原則：將索引選擇性最高的列放在左側，同時索引的順序要與查詢索引的順序一致，並且要兼顧考慮排序和分組的需要。在一個多列B樹多列中索引的順序意味著索引首先按照最左側的列進行排序，其次是第二列。所以無論是where語句還是order by語句都需要儘量滿足這個順序，這樣才能更好的使用索引。
索引的選擇性
列的選擇性高的含義是透過這一列能夠更多的過濾掉無用的資料，舉個極端的例子，如果把自增id建成索引那麼它的選擇性是最高的，因為會把無用的資料都過濾掉，只會剩下一條有效資料。我們可以透過下面的方式來簡單衡量某一個列的選擇性：
select count（distinct columnA）/count（*） as selectivity from table
當上面的資料越大的時候意味著columnA的選擇性越高。這種方式提供了一個衡量平均選擇性的辦法，但是也不一定是有效的，需要具體情況具體分析。
字首索引
當遇到特別長的列，但又必須要建立索引的時候可以考慮建立字首索引。字首索引的含義是把某一列的前N個字元作為索引，建立字首索引的方式如下：
alter table test add key（columnA（5））；
上面這個語句就是把columnA的前5個字元建立為字首索引。字首索引是一種使索引更小、更快的有效辦法。但是字首所有有一個缺點：MySQL無法使用字首索引來做order by和group by，也無法使用字首索引做覆蓋掃描。
聚簇索引和非聚簇索引
聚簇索引
聚簇索引代表一種資料的儲存方式，表示同一個結構中儲存了B-Tree索引和資料行。也就是說當建立聚簇索引的時候實際的資料行存放在索引的葉子節點上。這也決定了每個表只能有一個聚簇索引。
聚簇索引組織資料的方式如下圖所示：
從圖中可以看到索引的葉子節點和資料行是存放在一起的，這樣的好處是可以直接讀取到資料行。在建立表的時候如果我們不顯式指定聚簇索引，那麼MySQL將會按照下面的邏輯來選擇聚簇索引：首先會透過主鍵列來聚集資料，如果沒有主鍵列那麼會選擇唯一的非空索引來替代。如果還沒有這樣的索引那麼會隱式的建立一個主鍵列來作為聚簇索引。
聚簇索引優點：
1、相關資料存放在一起，檢索的時候降低IO的次數
2、資料訪問更快
3、使用覆蓋索引掃描的查詢可以直接使用節點中的主鍵值
在使用上面的優點的時候聚簇索引也有一定的缺點：
1、聚簇索引將資料聚集在一起限制了插入速度，插入速度比較依賴於主鍵的順序
2、更新索引的時候代價會變高
3、二級索引的訪問的時候需要查詢兩次
非聚簇索引
非聚簇索引通常被稱為二級索引，與聚簇索引的不同在於，非聚簇索引的葉子節點存放的是資料的行指標或者是一個主鍵值。這樣在查詢資料的時候首先定位到葉子節點上的主鍵值（或者行指標），然後透過主鍵值再到聚簇索引中查詢到對應的資料。從中我們可以看到對於非聚簇索引的查詢需要走兩次索引。下圖是一個非聚簇索引：
這個索引是InnoDB中的耳機索引，葉子節點中儲存的是索引和主鍵。對於MyISAM葉子節點儲存的是索引和行指標。
覆蓋索引
如果一個索引包含或者說覆蓋所有需要查詢的欄位的值，那麼就稱為覆蓋索引。覆蓋索引可以極大的提高查詢的效率，如果我們的查詢中只查詢索引，而不用去回表那應該最好不過了。
通常我們使用explain關鍵字來檢視一個查詢語句的執行計劃，透過執行計劃我們可以瞭解到查詢的細節。如果是覆蓋索引，我們會看到執行計劃的Extra列裡有”Using Index”的資訊。在查詢語句中一般我們希望是where條件中的語句儘量能被覆蓋，並且順序要跟索引的保持一致。還有一個需要注意的點是MySQL不能在索引中使用like操作，這樣會導致後面的索引失效。
後記
本文主要講了幾種索引的原理以及如何構建一個高效能的索引。索引的優先是一個漸進的過程，隨著資料量和查詢語句的不同而發生變化，重要的是瞭解索引的原理，這樣做出正確的最佳化。下一篇文章中將會介紹explain關鍵字，教你如何來看執行計劃，以及如何判斷一個查詢語句是否需要最佳化的。
會點程式碼的大叔2019-01-01 09:38:05謝邀~
之前寫過一篇關於針對開發人員資料庫最佳化的文章，索引也是其中之一，那麼今天就針對Mysql索引講幾點。
索引的型別及什麼時候建立索引
說到MySQL的索引，大多數時候都是指B-Tree索引，M ySQL大部分引擎都是支援B-Tree索引的。B-Tree索引適用於全鍵值、範圍、字首的查詢；
主鍵、外來鍵必須有索引，當然很多系統都是邏輯外來鍵（或需要經常和其他表關聯），也需要建立索引；經常出現在where、order by、group by中的欄位；儘量把索引建立到小欄位上；對於文字欄位或者很長欄位，不要建索引；複合索引，文章第二部分再說明；
雜湊索引，是基於雜湊表，精確匹配索引所有列的查詢才有效；只有Memory引擎支援。
全文索引、聚簇索引、聚簇索引等等，就不詳細說了，因為。。。我也不太會，下面還是主要說B-Tree索引（後來說的索引，都是指B-Tree）。
聯合索引的限制
很多同學都喜歡給多個欄位建立聯合索引，那麼建立聯合索引需要注意些什麼呢：
索引的最左原則，如果不是按索引的最左列查詢，那麼將無法使用索引。最左原則：如果建立了一個聯合索引（name，age，gender），相當於建立了三個索引（name）、（name，age）、（name，age，gender）。
聯合索引，左邊的列有範圍查詢，那麼右邊的列無法使用索引。比如index（age，gender），where age > 20 and gender = ‘M’；這時候就會有問題。解決辦法也很簡單，兩個欄位分別建立索引。
索引的一些小技巧
前導模糊查詢，會導致索引失效：where name like ‘%三豐’；
資料區分度不大，不建議使用索引：where gender = ‘M’；性別只有男、女、未知三種；
等號左邊有函式，會索引失效：where LENGTH（col1） = 10；
隱式轉換的問題：where col2 = ‘100’，col2列是數字，等號左右型別不一致，col2會隱式轉換成字串；
儘量不好使用負向查詢，例如：！=、not in、not exists；
索引不是越多越好。
我將持續分享Java開發、架構設計、程式設計師職業發展等方面的見解，希望能得到你的關注。
Java技術架構2019-01-31 15:47:37索引應該是各種資料庫最佳化方案之中成本最低，見效最快的解決方案了，之前也發表過幾篇關於索引原理的文章，這次就重點講下：企業級應用如何構建高效索引，以及應該注意些什麼。
索引型別
1、B-tree索引
Myisam和innodb中，預設用B-tree索引，是一種平衡樹。可以抽象一下——-B-tree系統，可理解為“排好序的快速查詢結構”
2、hash索引
在memory表裡，預設是hash索引， hash的理論查詢時間複雜度為O（1）
既然hash的查詢如此高效，為什麼不都用hash索引？
1：hash函式計算後的結果，是隨機的，沒有辦法對範圍查詢進行最佳化。
2： 無法利用字首索引。 比如 在btree中， field列的值“hellopworld”，並加索引
查詢 xx=helloword，自然可以利用索引， xx=hello，也可以利用索引。 （左字首索引）
因為hash（‘helloword’），和hash（‘hello’），兩者的關係仍為隨機
3： 排序也無法最佳化。
4： 必須回行。就是說透過索引拿到資料位置，必須回到表中取資料
以下是建立索引時的注意事項
索引不是越多越好，單張表中索引數量不宜超過8個
合理建立聯合索引，（a，b，c） 相當於 （a） 、（a，b） 、（a，b，c）
禁⽌冗餘索引
索引是雙刃劍，會增加維護負擔，增⼤大IO壓⼒力。（a，b，c）、（a，b），後者為冗餘索引。可以利⽤用字首索引來達到加速的目的，減輕維護負擔
SELECT只獲取必要的欄位，不建議使⽤ SELECT *
好處：減少網路頻寬消耗；能有效利用覆蓋索引，表結構變更對程式基本⽆無影響
合理使⽤用覆蓋索引減少IO，避免排序
索引覆蓋是指如果查詢的列恰好是索引的一部分，那麼查詢只需要在索引檔案上進行，不需要回行到磁碟再找資料。這種查詢速度非常快，稱為”索引覆蓋”
理想的索引：1：查詢頻繁 2：區分度高 3：長度小 4： 儘量能覆蓋常用查詢欄位
注：主鍵查詢是天然的覆蓋索引
儘量避免用NULL（）
原因： NULL不利於索引，要用特殊的位元組來標註。在磁碟上佔據的空間其實更大。
索引的主鍵值，應儘量是連續增長的值，而不是要是隨機值（不要用隨機字串或UUID），否則會造成大量的頁分裂與頁移動
不對過⻓的VARCHAR欄位建⽴立索引。建議優先考慮字首索引，或新增CRC32或MD5偽列並建⽴立索引
在常用的列上加上適當索引
例： where cat_id=3 and price>100 ； //查詢第3個欄目，100元以上的商品
誤： cat_id上，和， price上都加上索引。
錯： 只能用上cat_id或Price索引，因為是獨立的索引，同時只能用上1個
多列索引上，索引發揮作用，需要滿足左字首要求
以 index（a，b，c） 為例
選擇區分度⼤大的列建⽴立索引。組合索引中，區分度大的欄位放在最前
重要SQL必須被索引：update、delete的where條件列、order by、group by、distinct欄位、多表join欄位。
不建議使用%前導查詢，例如：like“%abc”，無法利用到索引。
不建議使用負向查詢，例如notin、！=、notlike。
使用EXPLAIN判斷SQL語句是否合理使用索引，儘量避免extra列出現：Using File Sort、Using Temporary
暫時想到這麼多，想到再更，歡迎評論區指正，喜歡的幫忙點贊，請多多關注
珠海華商科技一Mark2018-12-27 17:11:47第一步、
肯定要從業務背景開始出發啦。
比如業務資訊是需要一個產品表，裡面需要儲存產品名稱，產品庫存，產品價格，產品分類，是否顯示，備註資訊。
轉換成sql程式碼：
CREATE TABLE `product` （
`id` bigint（0） UNSIGNED NOT NULL AUTO_INCREMENT，
`productName` varchar（64） NULL，
`numCount` int（10） NULL，
`price` decimal（10， 2） UNSIGNED NULL，
`productClass` varchar（32） NULL，
`isShow` int（1） UNSIGNED NULL DEFAULT 1 COMMENT ‘0隱藏，1顯示’，
`remark` varchar（255） NULL，
PRIMARY KEY （`id`），
INDEX `web_show`（`productClass`， `isShow`），
INDEX `web_search`（`productName`， `numCount`， `Price`， `productClass`， `isShow`）
）；
首先，web_show這個索引代表著可能頁面會根據產品分類和是否顯示進行分類查詢，所以建立一個索引。
web_search 則代表在產品搜尋頁面，透過productName和numCount還有Price和productClass進行篩選，原則上isShow代表是否顯示預設都需要的。
第二步、
若後期業務增加，或執行sql的方式不一樣，透過desc select ****方式，來檢視SQL執行過程，透過執行過程在對應地建立索引即可。
另外索引並非越多越好，需要合適地建立
資料和雲智慧2019-05-30 11:44:40建立高效能索引
選擇正確的索引順序
在選擇索引的順序的時候有一個原則：將索引選擇性最高的列放在左側，同時索引的順序要與查詢索引的順序一致，並且要兼顧考慮排序和分組的需要。在一個多列B樹多列中索引的順序意味著索引首先按照最左側的列進行排序，其次是第二列。所以無論是where語句還是order by語句都需要儘量滿足這個順序，這樣才能更好的使用索引。
索引的選擇性
列的選擇性高的含義是透過這一列能夠更多的過濾掉無用的資料，舉個極端的例子，如果把自增id建成索引那麼它的選擇性是最高的，因為會把無用的資料都過濾掉，只會剩下一條有效資料。我們可以透過下面的方式來簡單衡量某一個列的選擇性：
select count（distinct columnA）/count（*）as selectivity from table
當上面的資料越大的時候意味著columnA的選擇性越高。這種方式提供了一個衡量平均選擇性的辦法，但是也不一定是有效的，需要具體情況具體分析。
字首索引
當遇到特別長的列，但又必須要建立索引的時候可以考慮建立字首索引。字首索引的含義是把某一列的前N個字元作為索引，建立字首索引的方式如下：
alter table test add key（columnA（5））；
上面這個語句就是columnA的前5個字元建為字首索引。字首索引是一種使索引更小、更快的有效辦法。但是字首所有有一個缺點：MySQL無法使用字首索引來做order by和group by，也無法使用字首索引做覆蓋掃描。
聚簇索引和非聚簇索引
聚簇索引
聚簇索引代表一種資料的儲存方式，表示同一個結構中儲存了B-Tree索引和資料行。也就是說當建立聚簇索引的時候實際的資料行存放在索引的葉子節點上。這也決定了每個表只能有一個聚簇索引。
聚簇索引組織資料的方式如下圖所示：
從圖中可以看到索引的葉子節點和資料行是存放在一起的，這樣的好處是可以直接讀取到資料行。在建立表的時候如果我們不顯式指定聚簇索引，那麼MySQL將會按照下面的邏輯來選擇聚簇索引：首先會透過主鍵列來聚集資料，如果沒有主鍵列那麼會選擇唯一的非空索引來替代。如果還沒有這樣的索引那麼會隱式的建立一個主鍵列來作為聚簇索引。
聚簇索引優點：
1、相關資料存放在一起，檢索的時候降低IO的次數
2、資料訪問更快
3、使用覆蓋索引掃描的查詢可以直接使用節點中的主鍵值
在使用上面的優點的時候聚簇索引也有一定的缺點：
1、聚簇索引將資料聚集在一起限制了插入速度，插入速度比較依賴於主鍵的順序
2、更新索引的時候代價會變高
3、二級索引的訪問的時候需要查詢兩次
非聚簇索引
非聚簇索引通常被稱為二級索引，與聚簇索引的不同在於，非聚簇索引的葉子節點存放的是資料的行指標或者是一個主鍵值。這樣在查詢資料的時候首先定位到葉子節點上的主鍵值（或者行指標），然後透過主鍵值再到聚簇索引中查詢到對應的資料。從中我們可以看到對於非聚簇索引的查詢需要走兩次索引。下圖是一個非聚簇索引：
這個索引是InnoDB中的耳機索引，葉子節點中儲存的是索引和主鍵。對於MyISAM葉子節點儲存的是索引和行指標。
覆蓋索引
如果一個索引包含或者說覆蓋所有需要查詢的欄位的值，那麼就稱為覆蓋索引。覆蓋索引可以極大的提高查詢的效率，如果我們的查詢中只查詢索引，而不用去回表那應該最好不過了。
通常我們使用explain關鍵字來檢視一個查詢語句的執行計劃，透過執行計劃我們可以瞭解到查詢的細節。如果是覆蓋索引，我們會看到執行計劃的Extra列裡有“Using Index”的資訊。在查詢語句中一般我們希望是where條件中的語句儘量能被覆蓋，並且順序要跟索引的保持一致。還有一個需要注意的點是MySQL不能在索引中使用like操作，這樣會導致後面的索引失效。
猿界小哥2018-12-26 21:33:11這個問題可以看下，我已經發表的一篇文章，裡面講到如何設計高效能的索引
https：//www。toutiao。com/i6637764918164587021/
Java螞蟻2018-12-25 17:27:58構建索引是一個長期的過程，需要不斷重構，而達到最優，常見索引有單值索引，唯一索引，複合索引能滿足很多場景的需求。如果是MySQL資料庫，配合explain+SQL語句可以看到MySQL認為最優的執行計劃，從而發現效能的瓶頸，如果是Oracle可以使用PL/SQL等工具檢視執行計劃，兩者基本大同小異，具體問題，具體分析哈
