MySQL的Innodb存儲引擎的索引分為聚集索引和非聚集索引兩大類
成都創(chuàng)新互聯(lián)從2013年成立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元杏花嶺做網(wǎng)站,已為上家服務(wù),為杏花嶺各地企業(yè)和個人服務(wù),聯(lián)系電話:13518219792
特點：B+樹葉子節(jié)點存儲行數(shù)據(jù)
一個表中，必須有一個聚集索引，只能有一個聚集索引，Innodb通常把一個表的主鍵索引作為聚集索引，如果沒有主鍵InnoDB會選擇一個唯一索引代替。如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵來作為聚集索引，這個字段為6個字節(jié)，類型為長整形。
利用主鍵索引查找行數(shù)據(jù)是最快的，建議使用自增主鍵原因是利于索引樹的構(gòu)建（主鍵自增寫入時新插入的數(shù)據(jù)不會影響到原有頁，插入效率高；但是如果主鍵是無序的或者隨機的，那每次的插入可能會導(dǎo)致原有頁頻繁的分裂，影響插入效率）
特點：B+樹葉子節(jié)點存儲主鍵ID
一個表中可以有多個非聚集索引，每個非聚集索引即是一棵B+樹
通過非聚集索引查找數(shù)據(jù)時，需要先在非聚集索引上找到主鍵ID，再從聚集索引獲取行數(shù)據(jù)，這個過程就稱之為回表
B樹索引中的B樹實際上是B+樹，至于為什么使用B+樹而不使用B樹或者紅黑樹的原因在另外的文章中有提及。
特點：
特點：類似JDK中的HashMap，但無法支持范圍查詢
特點：使用的算法仍然是B樹索引，不同的就是索引列的值必須唯一
對于普通索引來說，查找到滿足條件的第一個記錄后，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。
對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續(xù)檢索，提升索引性能
另外插入行時會構(gòu)建該唯一索引，假如索引值重復(fù)將插入失敗，適合業(yè)務(wù)上做唯一性檢驗
通過建立倒排索引，可以極大的提升檢索效率,解決判斷字段是否包含的問題，但是業(yè)務(wù)上一般都不采用這種索引，而是使用ES處理全文搜索需求
僅對某個特定字段建立的索引，如(biz_id)
對多個字段建立的索引，如(biz_id,type)
建立索引，要使用離散度（選擇度）更高的字段。
我們先來看一個重要的屬性列的 離散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值個數(shù)：所有數(shù)據(jù)行行數(shù)
數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。簡單來說，如果列的重復(fù)值越多，離散度就越低，重復(fù)值越少，離散度就越高。
當(dāng)字段值比較長的時候，建立索引會消耗很多的空間，搜索起來也會很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引，這個就叫前綴索引。
創(chuàng)建一張商戶表，因為地址字段比較長，在地址字段上建立前綴索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));? // 截取12個字符作為前綴索引是最優(yōu)的嗎？
問題是，截取多少呢？截取得多了，達不到節(jié)省索引存儲空間的目的，截取得少了，重復(fù)內(nèi)容太多，字段的散列度（選擇性）會降低。怎么計算不同的長度的選擇性呢？
先看一下字段在全部數(shù)據(jù)中的選擇度計算公式：
select count(distinct address) / count(*) from shop;
select count(distinct left(address, n)) / count(*) as subn from shop;
count(distinct left(address,n)) / count(*) 的結(jié)果是會隨著 n 的變大而變大。舉個例子，現(xiàn)在有兩個address（東大街長興小區(qū)，東大街福樂小區(qū)），那么 distinct(address，2) distinct(address，3）
==所以，截取的長度越長就會越接近字段在全部數(shù)據(jù)中的選擇度
==所以，我們要權(quán)衡索引大小和查詢速度。
舉個例子，通過不同長度去計算，與全表的選擇性對比：
SELECT? COUNT(DISTINCT(address))/COUNT(*) sub,? ? ? ? ? ? -- 字段在全部數(shù)據(jù)中的選擇度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,? -- 截取前5個字符的選擇度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,?
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,? -- 截取前10個字符的選擇度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| sub? ? | sub5? | sub7? | sub9? | sub10? | sub11? | sub12? | sub13? | sub15? |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
可以看到在截取 11 個字段時 sub11(0.9993) 就已經(jīng)很接近字段在全部數(shù)據(jù)中的選擇度 sub(0.9958)了，而且長度也相較后面更短一些， 綜合考慮比較合適。
ALTER TABLE shop ADD KEY (address(11));
1.索引的個數(shù)不要過多（浪費空間，更新變慢）
2.在用于 where 判斷 order 排序和 join 的（on）字段上創(chuàng)建索引
3.區(qū)分度低的字段，例如性別，不要建索引（離散度太低，導(dǎo)致掃描行數(shù)過多）
4.更新頻繁的值，不要作為主鍵或者索引（頁分裂）
5.不建議用無序的值作為索引，例如身份證、UUID（在索引比較時需要轉(zhuǎn)為ASCII，并且插入時可能造成頁分裂）
6.若在多個字段都要創(chuàng)建索引的情況下，聯(lián)合索引優(yōu)于單值索引
7.聯(lián)合索引把散列性高（區(qū)分度高）的值放在前面
CREATE
[UNIQUE]
INDEX
ON
(字段
[ASC|DESC]);
UNIQUE
--確保所有的索引列中的值都是可以區(qū)分的。
[ASC|DESC]
--在列上按指定排序創(chuàng)建索引。
(創(chuàng)建索引的準(zhǔn)則：
1.如果表里有幾百行記錄則可以對其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。
2.不要試圖對表創(chuàng)建兩個或三個以上的索引。
3.為頻繁使用的行創(chuàng)建索引。
)
示例
create
index
i_1
on
emp(empno
asc);
二叉搜索樹、N叉樹
頁分裂：B+樹的插入可能會引起數(shù)據(jù)頁的分裂，刪除可能會引起數(shù)據(jù)頁的合并，二者都是比較重的IO消耗，所以比較好的方式是順序插入數(shù)據(jù)，這也是我們一般使用自增主鍵的原因之一。
頁分裂逆過程：頁合并，當(dāng)刪除數(shù)據(jù)后，相鄰的兩個數(shù)據(jù)頁利用率很低的時候會做數(shù)據(jù)頁合并
主鍵索引：key：主鍵，value：數(shù)據(jù)頁，存儲每行數(shù)據(jù)
非主鍵索引：key：非主鍵索引，value：主鍵key，導(dǎo)致回表
最左匹配：優(yōu)先將區(qū)分度高的列放到前面，這樣可以高效索引，
最左匹配原則遇到范圍查詢就停止匹配，范圍查詢(、、between、like)為什么？因為出現(xiàn)范圍匹配后，后面的索引字段無法保證有序，局部有序失去，順序失去則無法提高查詢效率
SELECT * FROM table WHERE a IN (1,2,3) and b 1;
如何建立索引？
還是對(a，b)建立索引，因為IN在這里可以視為等值引用，不會中止索引匹配，所以還是(a,b)!
索引組織表
索引用頁存儲：key【10】-point【6】，通過調(diào)整key大小，當(dāng)頁大小固定的情況下，通過調(diào)整key大小，使得N叉樹變化；
如key 10, point 6則單個索引16字節(jié)，頁大小為16k，則頁面總共可以存儲1024個索引，即N大小
覆蓋索引: 二級索引的信息已經(jīng)存在想要的列，例如主鍵
如果現(xiàn)在有一個高頻請求，要根據(jù)市民的身份證號查詢他的姓名，這個聯(lián)合索引就有意義了。它可以在這個高頻請求上用到覆蓋索引，不再需要回表查整行記錄，減少語句的執(zhí)行時間。
索引下推優(yōu)化：可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。
整理索引碎片，重建表：alter table T engine=InnoDB
??首先是看key的大小，另外是數(shù)據(jù)頁的大小，如果需要改變N，則需要從這兩個方面做改動；
一個innoDB引擎的表，數(shù)據(jù)量非常大，根據(jù)二級索引搜索會比主鍵搜索快，文章闡述的原因是主鍵索引和數(shù)據(jù)行在一起，非常大搜索慢，我的疑惑是：通過普通索引找到主鍵ID后，同樣要跑一邊主鍵索引，對于使用覆蓋索引的情況下，使用覆蓋索引可以直接解決問題
MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲數(shù)據(jù)對性能有很大的影響。通常來說，可以遵循以下一些指導(dǎo)原則：
(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。
(2)簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串的比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。
(3)盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。
重慶分公司
重慶分公司
