索引的作用-一個(gè)例子

索引對(duì)查詢的速度有著至關(guān)重要的影響，理解索引也是進(jìn)行數(shù)據(jù)庫(kù)性能調(diào)優(yōu)的起點(diǎn)。
考慮如下情況，假設(shè)數(shù)據(jù)庫(kù)中一個(gè)表有10^6條記錄，DBMS的頁(yè)面大小為4K，并存儲(chǔ)100條記錄。
如果沒(méi)有索引，查詢將對(duì)整個(gè)表進(jìn)行掃描，最壞的情況下，如果所有數(shù)據(jù)頁(yè)都不在內(nèi)存，
需要讀取10^{4個(gè)頁(yè)面，如果這10}4個(gè)頁(yè)面在磁盤(pán)上隨機(jī)分布，需要進(jìn)行10^4次I/O，
假設(shè)磁盤(pán)每次I/O時(shí)間為10ms(忽略數(shù)據(jù)傳輸時(shí)間)，則總共需要100s(但實(shí)際上要好很多很多)。
如果對(duì)之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(10^6)=3次頁(yè)面讀取，最壞情況下耗時(shí)30ms。
這就是索引帶來(lái)的效果，很多時(shí)候，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時(shí)，應(yīng)該想想是否可以建索引。

索引的優(yōu)點(diǎn)：

1. 減少了服務(wù)器需要掃描的數(shù)據(jù)量
2. 避免排序和臨時(shí)表
3. 將隨機(jī)IO變?yōu)轫樞騃O

聚集索引和非聚集索引的區(qū)別及優(yōu)缺點(diǎn)

1. 聚集索引一個(gè)表只能有一個(gè)，而非聚集索引一個(gè)表可以存在多個(gè)
2. 聚集索引存儲(chǔ)記錄是物理上連續(xù)存在，而非聚集索引是邏輯上的連續(xù)，物理存儲(chǔ)并不連續(xù)
3. 聚集索引:物理存儲(chǔ)按照索引排序；聚集索引是一種索引組織形式，索引的鍵值邏輯順序決定了表數(shù)據(jù)行的物理存儲(chǔ)順序
   　 非聚集索引:物理存儲(chǔ)不按照索引排序；非聚集索引則就是普通索引了，僅僅只是對(duì)數(shù)據(jù)列創(chuàng)建相應(yīng)的索引，不影響整個(gè)表的物理存儲(chǔ)順序.
4. 索引是通過(guò)二叉樹(shù)的數(shù)據(jù)結(jié)構(gòu)來(lái)描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過(guò)有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。

優(yōu)勢(shì)與缺點(diǎn):

聚集索引插入數(shù)據(jù)時(shí)速度要慢（時(shí)間花費(fèi)在“物理存儲(chǔ)的排序”上，也就是首先要找到位置然后插入）,查詢數(shù)據(jù)比非聚集數(shù)據(jù)的速度快

InnoDB只能還有一個(gè)聚集索引

• 如果表中含有主鍵,則InnoDB組織數(shù)據(jù)就是通過(guò)這個(gè)聚集索引
• 如果沒(méi)有主鍵，則Mysql會(huì)選擇第一個(gè)（按照聲明的順序）不允許null的unique的普通索引作為聚集索引
• 如果沒(méi)有索引，或者索引都不是非null的唯一索引，則使用InnoDB引擎內(nèi)置的ROWID作為聚集索引

InnoDB的聚集索引可以包含多列

比如在創(chuàng)建表的時(shí)候指定包含多列的主鍵，在存儲(chǔ)的時(shí)候按照聚集索引包含的列的前后順序來(lái)分組排序存放

InnoDB可以有多個(gè)非聚集索引

非聚集索引通過(guò)引用主鍵索引（聚集索引）來(lái)訪問(wèn)或者定位數(shù)據(jù)

其他索引(普通索引)中不會(huì)保存行的物理位置,而是保存主鍵的值,所以通過(guò)"二級(jí)索引"進(jìn)行查找是先找到主鍵,
再找到行,要進(jìn)行二次索引查找

InnoDB中聚集索引和主鍵的關(guān)系

在InnoDB表中，其聚集索引相當(dāng)于整張表，而整張表也是聚集索引。主鍵必然是聚集索引，而聚集索引則未必是主鍵。

select選擇字段是否用到索引

1- 單列索引

• 當(dāng)在where子句中在單列索引字段在數(shù)值運(yùn)算的一側(cè)或者函數(shù)，那么就不會(huì)使用單列索引

select id from people where id + 1 = 5; # 不使用索引
select id from people where id = 5+1; # 使用索引
select ... from people where to_days(current_date) - to_days(date_col) <= 10; # 不使用索引

• 對(duì)于創(chuàng)建 blob text varchar 類型字段的索引，必須指定長(zhǎng)度，因?yàn)镸ysql不允許這些列的完整長(zhǎng)度
  create index index_name on table_name(varchat_columns(10))

確定前綴索引的長(zhǎng)度：為了盡量使得單列前綴索引更有選擇性，需要進(jìn)行列的選擇性測(cè)試

選擇性 = 不重復(fù)的行數(shù) / 總行數(shù)
計(jì)算方式如下：

select count(left(index_column,3))/count(*) as pre_index_3,
    count(left(index_column,4))/count(*) as pre_index_4
    from database_name.table_name;

如果發(fā)現(xiàn)pre_index_4 > pre_index_3
那么就創(chuàng)建這個(gè)索引
alter table table_name add index/key index_name(index_column(4));

注意：

• 以下不能使用前綴索引：
  order by、group by、覆蓋索引

2- 復(fù)合索引

考慮如下索引

      CREATE TABLE `people` (
        `last_name` varchar(50)  NOT NULL,
        `first_name` varchar(50)  NOT NULL,
        `age` int(8) NOT NULL ,
        `gender` enum('m','f') NOT NULL,
        KEY `last_name` (`last_name`,`first_name`,`age`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

使用索引的情況

1. 全值匹配
   和索引中所有列進(jìn)行匹配
   select gender from people where last_name = 'Tom' and first_name = 'cat' and age=10;
   使用了符合索引的全部列
   即where子句對(duì)索引列全值匹配

2. 匹配最左前綴列
   select gender from people where last_name = 'Tom';
   使用了復(fù)合索引的第一列

3. 匹配列的最左前綴
   select gender from people where last_name like 'Tom%';
   使用了索引的第一列
   注意like子句開(kāi)頭不能包含通配符(%,_)，而且必須是常量字符串，而不能是其他字段名，否則使用不到索引

4. 匹配范圍（where子句中只有第一個(gè)符合最左前綴列的范圍匹配可以使用到索引）
   要求：1. 范圍列是最左前綴列；只能匹配第一個(gè)范圍列
   select gender from people where last_name between 'Alice' and 'Davis';
   使用了符合索引的第一列
   select gender from people where first_name between 'Alice' and 'Davis';
   不滿足要求，所以不會(huì)使用索引
   表示范圍的可以使用到索引：
   <,<=,=,>,>=
   between and
   in(num1,num2)
   注意 != , <> 這些不等于不能使用索引，而是應(yīng)該使用 column > num or column < num 來(lái)等價(jià)替換，來(lái)使用索引

5. where子句先是精確匹配再是范圍匹配
   select gender from people where last_name = 'James' and first_name between 'Alice' and 'Davis';
   使用索引的第一列（全匹配）和第二列（范圍匹配）

6. 創(chuàng)建索引時(shí)指定列的排序模式，排序時(shí)使用相同的排序順序或則完全相反的排序順序
   創(chuàng)建索引：CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC);
   1-相同順序排序：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC;
   2-完全逆序排序Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC;
   3-不完全順序排序 Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC
   排序使用到的索引只有1，2；3沒(méi)有使用到索引。

不使用索引列的情況

1. 不是索引的最左前綴列
2. 不是單列索引的列的最左前綴，主要限制為 like子句的開(kāi)頭包含通配符前綴
3. 跳過(guò)中間列，只能使用中間列前面的那些索引列
4. 一個(gè)范圍匹配（包括 like子句）的后面無(wú)法再使用索引
5. where 子句中使用了索引，則其后面的order by group by子句沒(méi)有辦法再使用索引
6. 索引的列在運(yùn)算符的一側(cè)，即緊鄰的是 +，- / *這些運(yùn)算符，而不是緊鄰 = ，<,>,>=,<= 這些符號(hào)；
   或者對(duì)索引列使用函數(shù)；這些都將不能使用索引。

最左前綴和對(duì)and的查詢優(yōu)化

待續(xù)

可以使用到索引的子句

待續(xù)

多個(gè)子句使用索引的情況

每一個(gè)select查詢只能使用一個(gè)索引
待續(xù)

where中是用or連接的查詢條件

待續(xù)

where中在索引列之間包含普通列

待續(xù)

3- 覆蓋索引

如果一個(gè)索引包含（覆蓋）所有需要查詢的字段的值，我們就稱為覆蓋索引
即 select 子句選擇的字段都出現(xiàn)在定義多列索引的字段中。
因?yàn)樗饕邪樵兊乃凶侄?，所以就不需要根?jù)索引回?cái)?shù)據(jù)行獲取其他非索引列的數(shù)據(jù)，
而是直接將索引中的數(shù)據(jù)直接返回。

創(chuàng)建索引
alter table table_name add index index_name(column_1,column_2,column_3);

查詢時(shí)使用覆蓋索引
select column_2 from table_name where column_2 = 8;
注意這里沒(méi)有使用最左前綴列，而是通過(guò)索引覆蓋來(lái)使用索引

覆蓋索引的限制

待續(xù)

4- 不同類型索引的選擇問(wèn)題

如果一個(gè)列被多個(gè)不同類型索引包含，那么如何選擇索引的問(wèn)題

待續(xù)