1、索引是什麼
1.1索引簡介
索引是表的目錄，是資料庫中專門用于幫助使用者快速查詢資料的一種資料結構。類似于字典中的目錄，查找字典内容時可以根據目錄查找到資料的存放位置，以及快速定位查詢資料。對于索引，會儲存在額外的檔案中。
1.2主要注意的地方：
(1).索引可以提高查詢速度，會減慢寫入速度，索引的缺點時建立和維護索引需要耗費時間。
(2).索引并不是越多越好，索引固然可以提高相應的select的效率，但同時也降低了insert及update的效率，因為insert或update是有可能會重建索引。
1.3什麼樣的字段适合建立索引
(1).表的主鍵、外鍵必須有索引；外鍵是唯一的，而且經常會用來查詢。
(2).經常與其他表進行連接配接的表，在連接配接字段上應該建立索引。
(3).資料量超過300的表應該有索引。
(4).重要的SQL或調用頻率高的SQL，比如經常出現在
where
子句中的字段，
order by
,
group by
distinct
的字段都要添加索引。
(5).經常用到排序的列上，因為索引已經排序。
(6).經常用在範圍内搜尋的列上建立索引，因為索引已經排序了，其指定的範圍是連續的。
1.4什麼場景不适合建立索引
1.對于那些在查詢中很少使用或者參考的列不應該建立索引，這是因為，既然這些列很少使用到，是以有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求。
2.對于那 些隻有很少資料值的列也不應該增加索引。因為本來結果集合就是相當于全表查詢了，是以沒有必要。這是因為，由于這些列的取值很少，例如人事表的性别列，在查詢的結果中，結果集的資料行占了表中資料行的很大比 例，即需要在表中搜尋的資料行的比例很大。增加索引，并不能明顯加快檢索速度。
3.對于那些定義為text, image和bit資料類型的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。
4、當修改性能遠遠大于檢索性能時，不應該建立索引。這是因為，修改性能和檢索性能是互相沖突的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因 此，當修改性能遠遠大于檢索性能時，不應該建立索引。
5.不會出現在where條件中的字段不該建立索引。
6.如果列均勻分布在 1 和 100 之間，卻隻是查詢中
where key_part1 > 1 and key_part1 < 90
不應該增加索引
1.5索引使用以及設計規範
1、越小的資料類型通常更好：越小的資料類型通常在磁盤、記憶體和CPU緩存中都需要更少的空間，處理起來更快。簡單的資料類型更好：整型資料比起字元，處理開銷更小，因為字元串的比較更複雜
2、盡量避免null：應該指定列為not null， 含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜，也可能導緻複合索引無效
3、主鍵選擇政策
1) 每個表必須顯示指定主鍵；
2) 主鍵盡量為一個字段，且為數字類型,避免使用字元串；
3) 主鍵盡量保持增長趨勢，建議使用id的生成器；
4)主鍵盡量杜絕聯合索引
4、每個表的索引個數盡量少于5個，避免建立重複備援索引；每個組合索引盡量避免超過3個字段，索引不是越多越好，謹慎添加索引，綜合考慮資料分布和資料更新
5、重要的SQL或調用頻率高的SQL
1) update/select/delete的where條件列字段都要添加索引；
2) order by , group by, distinct的字段都要添加索引
6、避免出現index merge（單索引or的查詢）；合理利用covering index
7、組合索引建立時，把區分度(選擇性)高的字段放在前面；根據SQL的特性，調整組合索引的順序
8、對于varchar字段加索引，建議使用字首索引，進而減小索引大小
2、索引的建立和删除
2.1建立資料表時建立索引的基本文法結構：
create
table
表名(
字段名1 資料類型 [限制條件...]
字段名2 資料類型 [限制條件...]
...
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](被用作索引的字段名[(長度)] [ASC|DESC])
);
#[]中的内容可加可不加，看具體情況； |:表示'或者' 字段名就是表的列名
2.2直接建立索引的基本文法結構:
create
[
`UNIQUE
|
FULLTEXT
|
SPATIAL
]` `INDEX
|
KEY` `[索引名]` `on` `表名 (被用作索引的字段名[(長度)] [
ASC
|
DESC
`])
);
2.3修改表結構的方式添加索引的基本文法:
alter
table
表名
add
索引格式;
2.4删除索引:
drop
index
索引名
on
表名;
2.5以上出現的索引屬性名的含義：
UNIQUE
`：(
unique
)，可選參數，表示索引為唯一索引。`
FULLTEXT
fulltext
) ，可選參數，表示索引為全文索引。`
SPATIAL
spatial
) ，可選參數，表示索引為空間索引。`
INDEX
|
KEY
index` `|` `key
)， 必選參數，用于指定字段為索引的，使用者在選擇時，隻需要二選一即可。`
[索引名]：可選參數，其作用是給建立的索引取新名稱。(起到友善使用的目的)
被標明的字段名：必選參數，被用作索引的對應的字段名稱，該字段必須被預先定義。
長度：可選參數，其指索引的長度，必須是字元串類型才可以使用。(比如：電話号碼)
[ASC | DESC]：(asc | desc)，可選參數，ASC 表示升序排列，DESC 表示降序排列。
3、索引類型
索引有很多種類型，可以為不同的場景提供更好的性能。在MySQL中，索引是在存儲引擎層而不是服務層。是以，并沒有統一的索引标準：不同的存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支援所有類型的索引。
3.1普通索引
建立普通索引，不需要添加 [UNIQUE | FULLTEXT | SPATIAL ] 等任何參數進行限制。
- 普通索引 (由關鍵字KEY或INDEX定義的索引) 的唯一任務是加快對資料的通路速度。
- 隻為那些最經常出現在‘查詢條件’(WHERE column = …) 或‘排序條件’(ORDER BYcolumn)中的資料列，來建立索引。
- 隻要有可能，就應該選擇一個資料最整齊、最緊湊的資料列(如一個int整數類型的資料列)來建立索引。
建表語句:
create
table
person(
id
int
`(
11
)
not
null
auto_increment
`,
name varchar(20) default null,
primary key (id),
key (name) using btree
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
添加索引:
create
index
name
on
person(name);
3.2唯一索引（主鍵）
建立唯一索引時，使用 UNIQUE 參數對 INDEX | KEY 進行限制。
- 與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一，建立方法和普通索引類似。
- 如果能确定某個資料列将隻包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。這麼做的好處：一是簡化了MySQL對這個索引的管理工作，這個索引也是以而變得更有效率；二是MySQL會在有新記錄插入資料表時，自動檢查新記錄的這個字段的值是否已經在某個記錄的這個字段裡出現過了；如果是，MySQL将拒絕插入那條新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。
- 事實上，在許多場合，人們建立唯一索引的目的往往不是為了提高通路速度，而隻是為了避免資料出現重複。
- 主索引：在前面已經反複多次強調過！必須為主鍵字段建立一個索引，這個索引就是所謂的"主索引"。
- 主索引 與 唯一索引的唯一差別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE。
eg:建立的表名為 address 的資料表，并在該表的 id 字段上建立名稱為 address_id 的唯一索引，
建表語句:
create table address(
id int(11) auto_increment primary key not null,
name varchar(50),
address varchar(200),
UNIQUE INDEX address_id (id ASC) # id 字段設為唯一索引，并賦予新名字address_id ,且ASC升序排列
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
3.3全文索引
全文索引隻能作用在 CHAR、VARCHAR、TEXT、類型的字段上。建立全文索引需要使用 FULLTEXT 參數進行限制。
- MySQL從3.23.23版開始支援全文索引和全文檢索，fulltext索引僅可用于 MyISAM表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被建立，或是随後使用ALTER TABLE或CREATE INDEX被添加。
- 對于較大的資料集，将你的資料輸入一個沒有FULLTEXT索引的表中，然後建立索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對于大容量的資料表，生成全文索引是一個非常消耗時間非常消耗硬碟空間的做法。
- 文本字段上的普通索引隻能加快對出現在字段内容最前面的字元串(也就是字段内容開頭的字元)進行檢索操作。如果字段裡存放的是由幾個、甚至是多個單詞構成的較大段文字，普通索引就沒什麼作用了。這種檢索往往以LIKE%word%的形式出現，這對MySQL來說很複雜，如果需要處理的資料量很大，響應時間就會很長。
- 這類場合正是全文索引(full-textindex)可以大顯身手的地方。在生成這種類型的索引時，MySQL将把在文本中出現的所有單詞建立為一份清單，查詢操作将根據這份清單去檢索有關的資料記錄。全文索引即可以随資料表一同建立，也可以等日後有必要時再使用指令添加。
- 有了全文索引，就可以用SELECT查詢指令去檢索那些包含着一個或多個給定單詞的資料記錄了。下面是這類查詢指令的基本文法： SELECT * FROM table_name
- WHERE MATCH(column1, column2)
- AGAINST(‘word1’, ‘word2’, ‘word3’)
- 上面這條指令将把column1和column2字段裡有word1、word2和word3的資料記錄全部查詢出來。
eg:建立的表名為 cards 的資料表，并在該表的 name 字段上建立名稱為 cards_number 的全文索引
建表語句:
create table cards(
id int(11) auto_increment primary key not null ,
name varchar(20) default null,
number bigint(11),
info varchar(50),
FULLTEXT KEY cards_number (name) # name字段設為全文索引，并賦予新名字cards_number
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
3.4單列索引
建立單列索引，即在資料表的單個字段上建立索引。建立該類型索引不需要引入限制參數，使用者在建立時隻需要指定單列字段名，即可建立單列索引。
- 多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL隻能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。
eg:建立的表名為 telephone 的資料表，并在該表的 tel 字段上建立名稱為 tel_num 的單列索引
create table telephone(
id int(11) primary key auto_increment not null,
name varchar(10) not null,
tel varchar(50) not null,
index tel_num (tel(20)) # tel字段設為單列索引，并賦予新名字tel_num,由于tel字段是字元串類型，是以可以設定'索引長度20'
);
3.5多列索引
建立多列索引，即在資料表的多個字段上建立索引。與上述單列索引類似，建立該類型索引不需要引入限制參數。
eg:建立的表名為 information 的資料表，并在該表的 name 和 sex 字段上建立名稱為 info 的多列索引
create table information(
inf_id int(11) auto_increment primary key not null,
name varchar(20) not null,
sex varchar(5) not null,
birthday varchar(50) not null,
index info (name,sex) # name,sex字段設為多列索引，并賦予新名字info
);
- 注意：在多列索引中，隻有查詢條件中使用了這些字段中的第一個字段(即上面示例中的 name 字段)，索引才會被使用。觸發多列索引的條件是使用者必須使用索引的第一字段，如果沒有用到第一字段，則索引不起任何作用，使用者想要優化查詢速度，可以應用該類索引形式。
3.6空間索引
建立空間索引，需要添加 SPATIAL 參數進行限制。同樣，必須說明的是，隻有 MyISAM 類型的表支援該類型 ‘ 空間索引 ’。而且，索引字段必須有非空限制。
eg:建立的表名為 list 的資料表，并在該表的 goods 字段上建立名稱為 listinfo 的空間索引，這裡 goods 字段有非空限制，
create table list(
id int(11) primary key auto_increment not null,
goods geometry not null,
SPATIAL INDEX listinfo (goods) # 這裡goods字段有非空限制，符合條件 goods字段設為空間索引，并賦予新名字listinfo
)engine=MyISAM;
注意： goods 字段上已經建立名稱為 listinfo 的空間索引，其中 goods 字段必須不能為空，且資料類型是 GEOMETRY，該類型是空間資料類型。
空間類型不能用其他類型代替，否則在生成空間素引時會産生錯誤且不能正常建立該類型索引。
外鍵索引：如果為某個外鍵字段定義了一個外鍵限制條件，MySQL就會定義一個内部索引來幫助自己以最有效率的方式去管理和使用外鍵限制條件。
另外：INDEX | KEY：(index | key)， 必選參數，用于指定字段為索引的，使用者在選擇時，隻需要二選一即可。
差別：
1). key ：是資料庫的實體結構，它包含兩層意義：一是限制（偏重于限制和規範資料庫的結構完整性）、二是索引（輔助查詢用的）。包括primary key, unique key, foreign key 等。primary key 有兩個作用，一是限制作用（constraint），用來規範一個存儲主鍵和唯一性，但同時也在此key上建立了一個index；unique key 也有兩個作用，一是限制作用（constraint），規範資料的唯一性，但同時也在這個key上建立了一個index；foreign key也有兩個作用，一是限制作用（constraint），規範資料的引用完整性，但同時也在這個key上建立了一個index；2). index：是資料庫的實體結構，它包含一層意義：它隻是索引（輔助查詢用的），它建立時會在另外的表空間（mysql中的innodb表空間）以一個類似目錄的結構存儲。是以，索引隻是索引，它不會去限制索引的字段的行為（那是key要做的事情）。
3.7聚簇索引
1.聚簇索引并不是一種單獨索引，而是一種資料儲存方式。InnoDB 的聚簇索引實際上在同一結構中儲存了B-Tree 索引和資料行。
2.當表有聚簇索引時，它的資料行實際上存放在索引的葉子頁中。“聚簇”表示資料行和相鄰的鍵值緊湊的儲存在一起。
3.對應InnoDB 來說如果表沒有定義主鍵，會選擇一個唯一的非空索引代替。如果沒有這樣的索引InnoDB 會隐式定義一個主鍵來作為聚簇索引。InnoDB 隻聚集在同一頁面中的記錄。
4.聚簇索引的優勢：
（1）可以把相關資料儲存在一起。（2）資料通路更快。資料和索引儲存在同一個 B-Tree 。（3）使用覆寫索引掃描的查詢可以直接使用頁節點的主鍵值
5.聚簇索引的缺點：
(1)聚簇索引最大的提高了I/O密集型應用的性能，但如果資料全部都放到記憶體中，則資料的順序就沒有那麼重要了，聚簇索引也就沒什麼優勢了。（2）插入速度嚴重依賴插入順序。按照主鍵插入的方式是InnoDB 速度最快的方式，但如果不是按照主鍵順序加載資料，那麼在加載後最好使用OPTIMIZE TABLE 指令重新組織一2下表（3）更新聚簇索引列的代價很高。因為會強制InnoDB 将每個被更新的行移動到新的位置
6.二級索引
主鍵索引的葉子節點存的是整行資料，在InnoDB 裡，主鍵索引也被稱為聚簇索引非主鍵索引的葉子節點内容是主鍵的值。在InnoDB 裡。非主鍵索引也被稱為二級索引。如：select* from order where user_id=3; user_id是普通索引。則會先搜尋user_id 的索引樹，得到id=5,再到id 索引樹搜尋一次，這個過程就是 “回表”。也就是說非主鍵索引需要查詢2次
3.8覆寫索引
1.mysql 可以使用索引直接來擷取列的資料，這樣就可以不再需要讀取資料行。如果索引的葉子節點中已經包含要查詢的資料，那麼還有什麼必要再回表查詢呢？如果一個索引包含（覆寫）所有要查詢的字段的值，那麼就稱為“覆寫索引”
2.覆寫索引可以提高查詢的性能，不需要回表，好處是：
（1）索引條目通常小于資料行，如果隻需讀取索引，那麼mysql 就會減少通路量（2）索引是按照列值順序存儲的，索引I/O 密集型的範圍查詢會比随機從磁盤讀取每一行資料的I/O 要少得多（3）一些存儲引擎如MyISAM 在記憶體隻緩存索引，資料則依賴作業系統來緩存，是以要通路資料需要一次系統調用，這可能導緻嚴重的性能問題，尤其是那些系統調用占了資料通路中最大開銷的場景（4)InnoDB 的聚簇索引，覆寫索引對InnoDB 表的特别有用。InnoDB 的二級索引在葉子節點儲存了行的主鍵值，是以如果二級主鍵能夠覆寫查詢，則可以避免對主鍵索引的二次查詢。
- select id from order where user_id between 1 and 3這時候隻需要查ID 的值，而ID 已經在user_id 索引樹上，是以可以直接提供查詢結果，不需要回表。
select from order where user_id between 1 and 3一旦用了select ，就會有其他列需要讀取，這時在讀完index以後還需要去讀data才會傳回結果。
這兩種處理方式性能差異非常大，特别是傳回行數比較多，并且讀資料需要 I/O 的時候，可能會有幾十上百倍的差異。是以建議根據需要用select *
4、索引原理
5.1索引實作的資料結構
Mysql對于不同的存儲引擎，索引的實作實作方式是不同的。主流的存儲引擎：MyISAM和InnoDB，兩種存儲引擎都使用B+Tree（B-Tree的變種）作為索引結構，但是在實作方式上，卻有很大的不同。下面是兩種BTree資料結構：
B-tree結構：
B-Tree無論是葉子結點還是非葉子結點，都含有key和一個指向資料的指針，隻要找到某個節點後，就可以根據指針找到磁盤位址進而找到資料。
B+tree結構:
B+Tree所有葉子結點才有指向資料的指針。非葉子結點就是純索引資料和主鍵。每個葉子結點都有指向下一個葉子結點的連結。
小結：非葉子結點存放在記憶體中，也叫内結點，是以，在有限的記憶體中， B-Tree中每個資料的指針會帶來額外的記憶體占用，減少了放入記憶體的非葉子結點數；B+Tree則盡可能多地将非葉子結點放入記憶體中
5.2MySQL中索引實作
由于B+Tree資料結構的優勢，目前mysql基本都采用B+Tree方式實作索引，下面列出了兩個最常用的存儲引擎的索引實作：
1、MyISAM：如下圖，葉子結點的data域存放的是資料的位址：
上圖表中共三列資料，col1為主鍵，表示MyISAM表的主索引示意圖，在MyISAM中，主索引和輔助索引（除主鍵以外的其它索引）在結構上沒有任何差別，隻是主索引的key是唯一的，輔助索引的key可以重複。
2、InnoDB：對比MyISAM，InnoDB的主鍵索引與輔助索引存儲方式是不同的：
主鍵索引：主鍵索引的葉子結點存放的是key值和資料，葉子結點載入記憶體時，資料一起載入，找到葉子結點的key，就找到了資料。
輔助索引：輔助索引的葉子結點存放的是key值和對應的記錄的主鍵值，使用輔助索引查詢，首先檢索輔助索引擷取主鍵，然後用主鍵在主索引中檢索擷取記錄。
小結：MyISAM索引葉子節點存放的是資料的位址，主鍵索引與輔助索引除了值得唯一性在結構上完全一樣。InnoDB索引葉子節點存放的内容因索引類型不同而不同，主鍵索引葉子節點存放的是資料本身，輔助索引葉子節點上存放的是主鍵值。
更多技術詳情請檢視雲掣官網
https://www.dtstack.com/dtsmart/