索引的作用-一個例子
索引對查詢的速度有著至關重要的影響崇决，理解索引也是進行數(shù)據(jù)庫性能調優(yōu)的起點牺六。
考慮如下情況颤枪，假設數(shù)據(jù)庫中一個表有10^6條記錄，DBMS的頁面大小為4K淑际，并存儲100條記錄畏纲。
如果沒有索引，查詢將對整個表進行掃描春缕，最壞的情況下盗胀，如果所有數(shù)據(jù)頁都不在內存，
需要讀取104個頁面淡溯，如果這104個頁面在磁盤上隨機分布读整，需要進行10^4次I/O簿训，
假設磁盤每次I/O時間為10ms(忽略數(shù)據(jù)傳輸時間)咱娶，則總共需要100s(但實際上要好很多很多)。
如果對之建立B-Tree索引强品，則只需要進行l(wèi)og100(10^6)=3次頁面讀取膘侮，最壞情況下耗時30ms。
這就是索引帶來的效果的榛，很多時候琼了，當你的應用程序進行SQL查詢速度很慢時，應該想想是否可以建索引。
索引的優(yōu)點：
- 減少了服務器需要掃描的數(shù)據(jù)量
- 避免排序和臨時表
- 將隨機IO變?yōu)轫樞騃O
聚集索引和非聚集索引的區(qū)別及優(yōu)缺點
- 聚集索引一個表只能有一個雕薪，而非聚集索引一個表可以存在多個
- 聚集索引存儲記錄是物理上連續(xù)存在昧诱，而非聚集索引是邏輯上的連續(xù)，物理存儲并不連續(xù)
- 聚集索引:物理存儲按照索引排序所袁；聚集索引是一種索引組織形式盏档，索引的鍵值邏輯順序決定了表數(shù)據(jù)行的物理存儲順序
非聚集索引:物理存儲不按照索引排序；非聚集索引則就是普通索引了燥爷，僅僅只是對數(shù)據(jù)列創(chuàng)建相應的索引蜈亩，不影響整個表的物理存儲順序. - 索引是通過二叉樹的數(shù)據(jù)結構來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點就是數(shù)據(jù)節(jié)點前翎。而非聚簇索引的葉節(jié)點仍然是索引節(jié)點稚配，只不過有一個指針指向對應的數(shù)據(jù)塊。
優(yōu)勢與缺點:
聚集索引插入數(shù)據(jù)時速度要慢（時間花費在“物理存儲的排序”上港华，也就是首先要找到位置然后插入）,查詢數(shù)據(jù)比非聚集數(shù)據(jù)的速度快
InnoDB只能還有一個聚集索引
- 如果表中含有主鍵,則InnoDB組織數(shù)據(jù)就是通過這個聚集索引
- 如果沒有主鍵道川，則Mysql會選擇第一個（按照聲明的順序）不允許null的unique的普通索引作為聚集索引
- 如果沒有索引，或者索引都不是非null的唯一索引苹丸，則使用InnoDB引擎內置的ROWID作為聚集索引
InnoDB的聚集索引可以包含多列
比如在創(chuàng)建表的時候指定包含多列的主鍵愤惰，在存儲的時候按照聚集索引包含的列的前后順序來分組排序存放
InnoDB可以有多個非聚集索引
非聚集索引通過引用主鍵索引（聚集索引）來訪問或者定位數(shù)據(jù)
其他索引(普通索引)中不會保存行的物理位置,而是保存主鍵的值,所以通過"二級索引"進行查找是先找到主鍵,
再找到行,要進行二次索引查找
InnoDB中聚集索引和主鍵的關系
在InnoDB表中，其聚集索引相當于整張表赘理，而整張表也是聚集索引宦言。主鍵必然是聚集索引，而聚集索引則未必是主鍵商模。
select選擇字段是否用到索引
1- 單列索引
- 當在where子句中在單列索引字段在數(shù)值運算的一側或者函數(shù)奠旺，那么就不會使用單列索引
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; # 不使用索引
- 對于創(chuàng)建 blob text varchar 類型字段的索引，必須指定長度施流，因為Mysql不允許這些列的完整長度
create index index_name on table_name(varchat_columns(10))
確定前綴索引的長度：為了盡量使得單列前綴索引更有選擇性响疚，需要進行列的選擇性測試
選擇性 = 不重復的行數(shù) / 總行數(shù)
計算方式如下：
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)建這個索引
alter table table_name add index/key index_name(index_column(4));
注意：
- 以下不能使用前綴索引：
order by、group by瞪醋、覆蓋索引
2- 復合索引
考慮如下索引
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 ;
使用索引的情況
全值匹配
和索引中所有列進行匹配
select gender from people where last_name = 'Tom' and first_name = 'cat' and age=10;
使用了符合索引的全部列
即where子句對索引列全值匹配匹配最左前綴列
select gender from people where last_name = 'Tom';
使用了復合索引的第一列匹配列的最左前綴
select gender from people where last_name like 'Tom%';
使用了索引的第一列
注意like子句開頭不能包含通配符(%,_)忿晕，而且必須是常量字符串，而不能是其他字段名银受，否則使用不到索引
- 匹配范圍（where子句中只有第一個符合最左前綴列的范圍匹配可以使用到索引）
要求：1. 范圍列是最左前綴列践盼；只能匹配第一個范圍列
select gender from people where last_name between 'Alice' and 'Davis';
使用了符合索引的第一列
select gender from people where first_name between 'Alice' and 'Davis';
不滿足要求，所以不會使用索引
表示范圍的可以使用到索引：
<,<=,=,>,>=
between and
in(num1,num2)
注意 != , <> 這些不等于不能使用索引宾巍，而是應該使用 column > num or column < num 來等價替換咕幻，來使用索引
where子句先是精確匹配再是范圍匹配
select gender from people where last_name = 'James' and first_name between 'Alice' and 'Davis';
使用索引的第一列（全匹配）和第二列（范圍匹配）創(chuàng)建索引時指定列的排序模式，排序時使用相同的排序順序或則完全相反的排序順序
創(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沒有使用到索引。
不使用索引列的情況
- 不是索引的最左前綴列
- 不是單列索引的列的最左前綴，主要限制為 like子句的開頭包含通配符前綴
- 跳過中間列蓝厌，只能使用中間列前面的那些索引列
- 一個范圍匹配（包括 like子句）的后面無法再使用索引
- where 子句中使用了索引玄叠，則其后面的order by group by子句沒有辦法再使用索引
- 索引的列在運算符的一側，即緊鄰的是 +拓提，- / *這些運算符诸典，而不是緊鄰 = ，<,>,>=,<= 這些符號崎苗；
或者對索引列使用函數(shù)狐粱；這些都將不能使用索引。
最左前綴和對and的查詢優(yōu)化
待續(xù)
可以使用到索引的子句
待續(xù)
多個子句使用索引的情況
每一個select查詢只能使用一個索引
待續(xù)
where中是用or連接的查詢條件
待續(xù)
where中在索引列之間包含普通列
待續(xù)
3- 覆蓋索引
如果一個索引包含（覆蓋）所有需要查詢的字段的值胆数，我們就稱為覆蓋索引
即 select 子句選擇的字段都出現(xiàn)在定義多列索引的字段中肌蜻。
因為索引中包含查詢的所有字段，所以就不需要根據(jù)索引回數(shù)據(jù)行獲取其他非索引列的數(shù)據(jù)必尼，
而是直接將索引中的數(shù)據(jù)直接返回蒋搜。
創(chuàng)建索引
alter table table_name add index index_name(column_1,column_2,column_3);
查詢時使用覆蓋索引
select column_2 from table_name where column_2 = 8;
注意這里沒有使用最左前綴列，而是通過索引覆蓋來使用索引
覆蓋索引的限制
待續(xù)
4- 不同類型索引的選擇問題
如果一個列被多個不同類型索引包含判莉，那么如何選擇索引的問題
待續(xù)
