MySQL前綴索引

前綴索引顧名思義，定義字符串的一部分當做索引，而不是把整個字符串當做索引。默認地，如果你創建索引的語句不指定前綴長度，那么索引就會包含整個字符串。

公司主營業務：網站設計制作、成都網站制作、移動網站開發等業務。幫助企業客戶真正實現互聯網宣傳，提高企業的競爭能力。成都創新互聯公司是一支青春激揚、勤奮敬業、活力青春激揚、勤奮敬業、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業文化，感謝他們對我們的高要求，感謝他們從不同領域給我們帶來的挑戰，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。成都創新互聯公司推出應城免費做網站回饋大家。

假設一張表有 id,name,email 2個字段

1.創建email列的普通索引應該是： alter table T add index idx_email1( email )

2.前綴索引的創建規則為： alter table table T add index idx_email2( email(6) )

當然第一索引包含是的整個字符串，第二個是該字段前6個字節（注意是字節）

對于這2中索引，B+樹怎么存儲呢？

INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163.com'), ('劍圣','lisi1998883@163.com'), ('露娜','zhangssxyz@163.com'), ('李白','zhangsy1998@163.com'), ('韓信','zhaq5481993@163.com'), ('百里玄策','hhaq5481993@163.com');

【誰還不是個野王啊】

普通索引存儲為：

是的你沒看錯，前綴索引那顆樹上的存儲的是email的前6位字節，也就是你創建前綴索引時指定的前綴字節長度。2種樹相比，前綴索引存儲了更少的數據，那么他所耗費的空間也就相比較少，這正是他的一個優點。同樣的也就相對的增加了掃描行數。

什么增加了掃描行數？？？？ 這是為什么呢？

那么小朋友咱們一起來看下吧。

假設SQL如此這般： select id,name,email from T where email = 'zhangsh1234@163.com'

那么這2個SQL，應該怎么操作呢。

idx_email1:

2.到主鍵上查到主鍵為ID1的，判斷email值是否正確【為什么判斷呢，其實我理解是為了二次判斷保證數據一致性吧，比較官方的解釋尚未找到】，正確放入結果集

3.取 idx_email1 索引樹上剛剛查到的位置的下一條記錄，如此往復。

循環過程中，需要回主鍵取1次數據，所以系統可以認為只掃描了一行【1次是數第一棵樹數出來的】

idx_email2:

1.從 索引數上找到滿足索引值為 'zhangs'的該記錄，取得 ID1的值

2.到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值是’ zhangsh1234@xxx.com ’，這行記錄放入結果集【不是要的值，丟棄，進行下一步】

3.取 idx_email2 上剛剛查到的位置的下一條記錄,重復以上步驟

在這個過程中，要回主鍵索引取 3 次數據，也就是掃描了 3 行。通過這個對比，你很容易就可以發現，使用前綴索引后，可能會導致查詢語句讀數據的次數變多。

但是，對于這個查詢語句來說，如果你定義的 idx_email2 不是 email(6) 而是 email(8），也就是說取 email 字段的前 8 個字節來構建索引的話，即滿足前綴’zhangsh’的記錄只有一個，也能夠直接查到 ID1，只掃描一行就結束了。也就是說使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

那么問題來了，到底定義多長才算是合理呢？

一般的定義原則是 count(distinct(columnName))/count(*) ,當前綴索引【count(distinct(columnName(length)))，length是你想要創建列的前綴字節長度】越接近此值越好，當有多個前綴字節都一樣且都等于這個值時怎么選擇呢，當然是 字節越少越好了哈，字節越少越省空間。索引選取的越長，占用的磁盤空間就越大，相同的數據頁能放下的索引值就越少，搜索的效率也就會越低。

count(distinct(columnName(length))) 翻譯到SQL 為： count(dictinct(left(colunmName, length)))

前面我們說了使用前綴索引可能會增加掃描行數，這會影響到性能。其實，前綴索引的影響不止如此，我們再看一下另外一個場景。

來呀，上SQL： select id,email from T where email='zhangsh1234@163.com'

如果按照email全字段索引，那么此SQL 是不需要回表的【為什么不需要回表？兄嘚，這個相當于覆蓋索引了哈】

那么如果按照前綴索引是否需要回表呢？答案是的。

因為當判斷前6個字節相等后，需要拿到id 回表拿到email的全部內容進行比較，如果不相同，丟棄這行，否則加入結果集。

那么有人會問了，我把長度放大點，包含所有字節不就好了嗎？

那么此時會有如下問題。

1.當你此時的長度是囊括了全字段，但是系統是不知道的，他還是需要回表再次判斷的，去確定前綴索引的定義是否截斷了完整信息。

2.此時長度是夠了，那么能肯定因為業務日后不會增加長度嗎？

3.盡可能的加長長度，還不如直接建立全字段索引呢

綜上，使用前綴索引就用不上覆蓋索引對查詢性能的優化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素。

前面說到的是，可以根據字段前面幾個字節進行查詢的，那么對于身份證這種，一共 18 位，其中前 6 位是地址碼，所以同一個縣的人的身份證號前 6 位一般會是相同的。

或許你會說，多弄幾個字節不就好嗎？那么請問下自己為什么使用前綴索引呢，不就是為了節省空間嗎？

那么這么做合適嗎？ 不合適對嗎？ 乖~，快去反省下吧

那么采用前綴索引顯示是不行的，那么如果用前綴索引怎么辦呢，聰明的你應該已經猜到了，采用倒敘存儲，然后建立前綴索引。

放到SQL 中就應該是這樣的： select field_list from t where id_card = reverse('id_card_string');

當然了，這種邏輯建議放到業務邏輯中實現，而不是放到SQL 中。

按照上述第4節的內容，有人或許會有另一個想法，還倒敘建立前綴索引復雜不，hash索引或者hash字段不香嗎？

有人會問了，為什么要在創建一個值來存儲hash值呢，如果不存儲你知道原值是什么嗎？ 同時hash算法是有一定重復可能的（hash值碰撞）

【可以了解下partition算法哦:[ 】。如果重復了，不存儲原值，你是無法判斷出正確數據的。

注：【hash字段不代表hash索引，hash索引原理正在快馬加鞭】，簡單說下hash索引，hash索引不需要創建一個值來存儲hash值，而是有hasn表來存儲【hash值碰撞時，由一個鏈表來搞定了】，存儲的內容為 hash值和每行的行指針 。

說回來啊，跑題了

查詢時： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

不過有個問題相信你也想到了，不管是hash存儲值還是hash索引都是不支持范圍查詢的。

來總結下這2個優缺點吧

1.從占用空間來看呢，倒敘索引不需要額外開辟存儲空間，而hash字段需要額外的一個字段，所以從這點上看倒敘索引更勝一籌，NO!并不準確,如果前綴長度過長，那么這2個情況額外的空間也就相差無幾了

3.從查詢效率上看，使用 hash 字段方式的查詢性能相對更穩定一些。因為 crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近 1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數

1.全字段完整索引比較占空間，但是而走覆蓋索引

2.前綴索引，節省空間，但會增加掃描 次數 并且不能使用覆蓋索引【每次都需回表校驗】

3.倒序存儲，再創建前綴索引，用于繞過字符串本身前綴的區分度不夠的問題。【倒敘方法建立放到業務邏輯中】

4.hash字段索引，相比前綴索引性能較為穩定，但是有額外的存儲空間和計算消耗，同時也 不 支持范圍查詢

MySQL字符串索引amp;臟頁刷盤

字符串加索引的方式？

對于字符串進行添加索引，我們除了對整個字符串加索引以外，還可以添加前綴索引。

什么是前綴索引？

前綴索引的好處？

使用前綴索引，定義好長度，可以做到即節省空間，又不用額外增加太多的查詢成本。

前綴索引的弊端？

前綴索引會使覆蓋索引失效，額外增加回表的消耗，如果前綴索引的長度選擇區分度不高，會額外導致掃描行數增加。

其他給字符串加索引的方式？

什么是臟頁？

MySQL在更新數據的時候會寫redo log并且更新內存以后就會返回，數據文件并不會立即更新，這就是所謂的WAL機制。

當內存被更新以后，內存中的數據頁就會和磁盤上的數據頁存在不一致的情況，該內存也就被稱為 臟頁 。

內存中的數據被寫入磁盤以后，內容變為一致，此時該內存頁就被稱為干凈頁。

什么叫刷臟頁？

內存數據頁中的內容被寫入磁盤數據頁中的過程稱為刷臟頁。

什么時候會刷臟頁？

InnoDB如何控制刷臟頁的頻率？

首先確認InnoDB所在主機的IO能力，此時需要用到數據庫的innodb_io_capacity參數，該參數推薦設置為磁盤的IOPS。磁盤的IOPS可以通過fio工具進行測試。

InndoDB刷臟頁主要考慮以下兩個因素：

MySQL會根據F1(M)和F2(N)兩個值，取其中較大的值記為R，之后引擎可以按照innodb_io_capacity定義的能力乘以R%來控制刷臟頁的速度。

臟頁比例

參數innodb_max_dirty_pages_pct是臟頁比例的上限，MySQL 8.0中是90%。

當前臟頁比例可以通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total計算獲得，具體sql計算指令如下：

連坐機制

InnoDB在刷臟頁的時候，如果該臟頁旁邊的是頁也是臟頁，會同時把相鄰的臟頁刷掉。

該刷臟頁行為由參數innodb_flush_neighbors控制:

對于機械硬盤，開啟連坐會減少隨機IO的消耗，但對于SSD，沒必要開啟該參數。

mysql數據庫怎么創建索引

普通索引 添加INDEX

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

下面演示下給user表的name字段添加一個索引

mysql數據庫如何創建索引

mysql數據庫如何創建索引

主鍵索引 添加PRIMARY KEY

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

mysql數據庫如何創建索引

mysql數據庫如何創建索引

唯一索引 添加UNIQUE

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

mysql數據庫如何創建索引

全文索引 添加FULLTEXT

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

mysql數據庫如何創建索引

如何添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

mysql數據庫如何創建索引

mysql 前綴索引 怎么使用

CREATE [UNIQUE] INDEX ON (字段 [ASC|DESC]); UNIQUE --確保所有的索引列中的值都是可以區分的。 [ASC|DESC] --在列上按指定排序創建索引。 (創建索引的準則： 1.如果表里有幾百行記錄則可以對其創建索引(表里的記錄行數越多索引的效果就越明顯...

Mysql聯合索引的最左前綴原則以及b+tree

?覆蓋索引，這一點是最重要的，重所周知非主鍵索引會先查到主鍵索引的值再從主鍵索引上拿到想要的值，這樣多一次查詢索引下推。但是覆蓋索引可以直接在非主鍵索引上拿到相應的值，減少一次查詢。

在一張大表中 如果有 (a,b,c)聯合索引就等于同時加上了 (a) (ab) (abc) 三個索引 減少了存儲上的一部分的開銷和操作開銷

梯度漏斗，比如 select *from t where a = 1 and b = 2 and c = 3; 就等于在滿足 a = 1 的一部分數據中過濾掉b = 2 的 再從 a = 1 and b = 2 過濾掉 c = 3 的，越多查詢越高效。

即最左優先，在檢索數據時從聯合索引的最左邊開始匹配,類似于給(a,b,c)這三個字段加上聯合索引就等于同時加上了 (a) (ab) (abc) 這三種組合的查詢優化

舉個栗子：

CREATE TABLE `user`? (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(25),

`sex` varchar(25) ,

`city` varchar(25) ,

PRIMARY KEY (`id`) USING BTREE,

INDEX `name`(`name`, `sex`, `city`) USING BTREE

)

EXPLAIN select *from`user` where sex='';

這樣是無法觸發聯合索引的，因為不符合最左原則，沒有命中(a) (ab) (abc) 這種組合

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type? | possible_keys | key? | key_len | ref? | rows | filtered | Extra? ? ? ? ? ? ? ? ? ? |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+|? 1 | SIMPLE? ? ? | user? | NULL? ? ? | index | NULL? ? ? ? ? | name | 309? ? | NULL |? ? 3 |? ? 33.33 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+1 rowinset (0.02 sec)

另外使用執行計劃一定要看結果，只有possible_keys有值的情況下才是命中索引

查詢條件要符合最左原則才能使用到索引

注意： where條件的順序是否會影響索引的命中，就是本來(ab)的組合，故意寫where語句時寫成(ba)，答案是沒有影響，只要遵循了索引的最左原則即可，至少在mysql5.7測試沒有問題。

最后，談談索引的底層數據結構b+tree

我們知道BTREE 每個節點都是一個二元數組: [key, data]，所有節點都可以存儲數據。key為索引key,data為除key之外的數據。

查找算法：首先從根節點進行二分查找，如果找到則返回對應節點的data，否則對相應區間的指針指向的節點遞歸進行查找，直到找到節點或未找到節點返回空指針

B+Tree有以下不同點：非葉子節點不存儲data，只存儲索引key；只有葉子節點才存儲data，而Mysql中B+Tree：在經典B+Tree的基礎上進行了優化，增加了順序訪問指針。在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指針，就形成了帶有順序訪問指針的B+Tree。這樣就提高了區間訪問性能：請見下圖，如果要查詢key為從18到49的所有數據記錄，當找到18后，只需順著節點和指針順序遍歷即可

依據來源（官網的文檔?）：

MySQL索引機制(詳細+原理+解析)

MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

集一個索引包含多個列（最左前綴匹配原則）

索引列的值必須唯一，但允許有空值

全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創建

設定主鍵后數據會自動建立索引，InnoDB為聚簇索引

即一個索引只包含單個列，一個表可以有多個單列索引

覆蓋索引是指一個查詢語句的執行只用從所有就能夠得到，不必從數據表中讀取，覆蓋索引不是索引樹，是一個結果，當一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數據，這樣避免了查到索引后的回表操作，減少了I/O效率

查看索引

列名解析：

刪除索引

查看：

刪除前:

刪除后：

普通的索引，沒有什么介紹

查看:（注意和前綴索引Sub_part的區別）

當索引的列是unique的時候，會生成唯一索引，唯一索引關于null有下列兩種情況

SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值

MYSQL下的唯一索引的列，允許null值，并且允許多個空值

查看:

會建立兩個索引，一個非聚簇索引，一個是唯一索引

結果:

可以插入兩個空值（明人不說暗話，我喜歡MySQL）

一方面，它不會索引所有字段所有字符，會減小索引樹的大小.

另外一方面，索引只是為了區別出值，對于某些列，可能前幾位區別很大，我們就可以使用前綴索引。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對于BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

查看：

查看：

復合索引的最左前綴匹配原則 ：

對于復合索引，查詢在一定條件才會使用該索引

減少開銷。 建一個聯合索引(col1,col2,col3)，實際相當于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數據的表，使用聯合索引會大大的減少開銷！

覆蓋索引。 對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

效率高。 索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數據，然后再回表從100w條數據中找到符合col2=2 and col3= 3的數據，然后再排序，再分頁；如果是聯合索引，通過索引篩選出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一個字段，可以參考這篇博文

:

我們先進行下面一個實驗看看InnoDB下的主鍵索引的一個現象。

查看:

我們插入進去的時候，數據的id都是亂序的，為什么這里最后select查詢出來的結果都是進行了排序？

這是因為InnoDB索引底層實現的是B+tree，B+tree具有下列的特點：

所以上面的排序是為了使用B+tree的結構 ，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節點。后續還有新的節點進入的時候，和B-tree相同的操作，會進行分裂。

一般來說，聚簇索引的B+tree都是三層

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什么這里輔助索引葉子結點不直接存儲數據呢？

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的優勢在哪里？

Q:主鍵索引作為聚簇索引需要注意什么

在查詢語句中使用LIke關鍵字進行查詢時，如果匹配字符串的第一個字符為"%"，索引不會使用。如果“%”不是在第一位，索引就會使用

多列索引是在表的多個字段上創建的索引，滿足最左前綴匹配原則，索引才會被使用

查詢語句只有Or關鍵字時候，如果OR前后的兩個條件都是索引，這這次查詢將會使用索引，否則Or前后有一個條件的列不是索引，那么查詢中將不使用索引