索引的簡介
類似于一本書的目錄倾贰，起到優(yōu)化查詢的內(nèi)容
索引的分類
- BTREE
- RTREE
- Hash innodb中自適應hash算法县习，自動維護
- fullText：全文索引（實現(xiàn)和es差不多的功能沟启，把一句話拆分成一個個詞三娩，但效果不怎么理想鸿脓，因為MySQL是結構化存儲抑钟，es是json格式存儲）
- Gis：地理位置索引（學的mongodb涯曲，一般存儲地圖）
BTREE 索引的演變
上層節(jié)點存放下層節(jié)點的最小值，即根節(jié)點存放枝節(jié)點的最小值在塔，枝節(jié)點存放葉子節(jié)點的最小值
BTREE+樹在葉子節(jié)點增加了雙向指針幻件，BTREE*是在葉子節(jié)點和枝節(jié)點增加雙向指針
輔助索引（二級索引）
1 .管理員選擇一個列建立輔助索引
2 .MySQL自動將此列取出來
3 .將此列值排好序（從小到大）
4 .將排好的數(shù)據(jù)均勻的分配到葉子節(jié)點上
5 .生成枝節(jié)點和跟節(jié)點
6 .在葉子節(jié)點中的值，都會存儲主鍵ID
聚集索引
1 .如果用的是輔助索引心俗，拿到輔助索引葉子節(jié)點的聚集索引的ID值傲武，去遍歷，然后去查找數(shù)據(jù)
2 .MySQL會自動選擇主鍵城榛，作為聚集索引揪利，沒有主鍵會選擇唯一主鍵，如果都沒有會生成隱藏的
3 .MySQL進行存儲數(shù)據(jù)時狠持，會按照聚集索引列的值的順序疟位，有序的存儲數(shù)據(jù)行
4 .聚集索引直接將原表數(shù)據(jù)頁阀坏，作為葉子節(jié)點此熬，然后提取聚集索引列向上生成枝節(jié)點和根節(jié)點
輔助索引細分
- 單列輔助索引
- 聯(lián)合索引（覆蓋索引）【重要】
- 唯一索引
索引樹高度問題
索引樹高度一般越低越好，一般維持在3-4層最佳
數(shù)據(jù)行多的時候
采用分布式架構礁芦，進行分庫分表
字段長度
盡量選擇正勒，字符串長度短的列作為索引列得院，如果業(yè)務不允許，則采用前綴索引
數(shù)據(jù)類型
char 和varchar的選擇章贞，enum的選擇
關于索引操作的問題
查詢索引
desc 表名祥绞；
PRI ===> 主鍵索引（聚集索引）
MUL===> 普通索引（輔助）
UNI====> 唯一索引
show index from 表名; //查的比較全
創(chuàng)建索引
# 創(chuàng)建普通索引
alter table city add index idx_name(name)
這也是屬于DDL語句，也會鎖表鸭限。在業(yè)務不繁忙時間做蜕径，或者使用pt-osc工具
# 創(chuàng)建聯(lián)合索引
alter table city add index idx_c_p(countrycode,population);
# 建立唯一索引
alter table city add index uniqe uniqx_dis(district);
注： 唯一索引列的值必須是唯一的，不能有重復值
# 建立前綴索引
alter table city add index idx_dis(district(5))
執(zhí)行計劃分析
作用
將優(yōu)化器選擇后的執(zhí)行計劃败京，給截取出來兜喻，給管理員判斷執(zhí)行效率。（在語句執(zhí)行之前赡麦，把執(zhí)行計劃拿到）
獲取執(zhí)行計劃
desc sql語句;
explain sql語句朴皆；
分析執(zhí)行計劃
table
表名，如果是多表連接隧甚，可能一個表出現(xiàn)問題车荔，此時這個table就有作用了
type 重要
查詢類型，MySQL查詢中有兩大類型 全表掃描和索引掃描
索引掃描的級別為: index,range,ref,eq_ref,const(system),null 從左到右的性能依次變好
-
index: 全索引掃描（整個索引列都進行遍歷）
desc select id from ctiy -
range: 索引范圍掃描（>,<,>=,<=,between and, in,and ,or,like）
desc select id from city where id <100; desc select * from city countrycode='CHN' and countrycode='USA'like,>,<,>=,<=的性能要比and,or,between and,in 好戚扳，因為MySQL采用BTREE* 樹索引，枝節(jié)點和葉子節(jié)點都有雙向指針族吻。and,or,in,between and 都用不了雙向指針帽借，一般要把and和or改寫為union all對于輔助索引來說珠增，!=和not in 不走索引,但對于主鍵來講 !=和not in 等語句是走range -
ref: 輔助索引等值查詢
desc select * from city where countrycode='CHN' -
eq_ref: 在多表連接時，子表使用主鍵列或唯一鍵作為連接條件
A join B on A.x = B.y,B作為子表砍艾，B.y為主鍵列時蒂教，為eq_ref ，A為驅動表脆荷，是不走索引的desc select b.name,a.name,a.population from city as a join country as b on a.countrycode=b.code where a.population<100 -
const（system): 兩個一樣凝垛，當主鍵或者唯一鍵的等值查詢時
desc select * from city where id=2 - NULL: 所查詢的數(shù)據(jù)，在數(shù)據(jù)中沒有時蜓谋。
possible_key
可能會用到的key
key
最后用到的key
key_len
索引覆蓋長度梦皮，當前列可以為空時，其中有一個字節(jié)標識是否為空
單列索引長度
注： 所有列的可以為空桃焕，utf8mb4的字符集
varchar(20) 說明
- 能存任意20個字符
- 不管存儲的字符剑肯，數(shù)字，中文观堂，都是1個字符最大預留長度是4字節(jié)
- 對于中文让网，1個字符占4個字節(jié)
- 對于數(shù)字和字母，1個字符實際占用大小是1個字節(jié)
-
select length(列名) from 表名查看這一列师痕，每行的長度
int(5) ===> 4+1 ==》 1 為標識該列是否為空
char(2) ===> 4+4+1 ==》 1 為標識該列是否為空
varchar(2) ===> 4+4+1+1+1 ===> 1 為標識該列是否為空 其余兩個1位開始位置和結束位置
聯(lián)合索引
add index idx(a,b,c,d)
- 唯一值多的列放在左側
- 只要我們將來的查詢溃睹，所有的列都是等值查詢，無關順序排序
(a,b,c,d),(b,d,c,a),(a,b,d,c)……胰坟，因為優(yōu)化器自動將我們查詢條件進行排序了 - 不連續(xù)的部分條件
cda ==> a,c,d 只走a索引因篇，因b沒有了。建立聯(lián)合索引的時候是按照a,b,c,d的順序建立的===>優(yōu)化建議腕铸，可以刪除原來索引惜犀，在新建一個cda的索引。（在業(yè)務支持的情況下）
所有列相同的不同的索引會相互影響狠裹。
- 在where查詢中如果出現(xiàn)不等值(>,<,>=,<=,like)
add index idx(a,b,c,d)
…… where a=1 and b>3 and c=2 and d=5 如果出現(xiàn)不等值查詢虽界，索引只會卡在b那，即走a和b的索引
優(yōu)化建議:
1 .把不等值查詢放在最后面（不過優(yōu)化器會自動排序）
2 .更改聯(lián)合索引的順序涛菠，刪了重新建（重點）
- 對于多子句的情況莉御，應用聯(lián)合索引
需要按照語句的執(zhí)行順序，建立聯(lián)合索引
為什么要用聯(lián)合索引俗冻，而不是單列索引礁叔。因為MySQL默認只會使用一種索引
Extra
額外執(zhí)行的命令
重點： using filesort額外的排序 ，出現(xiàn)此語句迄薄，說明select排序的條件列中琅关，沒有合理的用到索引,涉及到排序的條件order by,group by,distinct,union
索引的應用規(guī)范
建立索引的原則（DBA的運維規(guī)范）
- 建表必須要有主鍵，一般是無關列讥蔽，自增長
- 經(jīng)常為where條件列 order by,group by,join on,distinct的條件
- 最好使用唯一值多的列涣易，做為聯(lián)合索引的最左列画机，其余的按照優(yōu)化細節(jié)來做
- 列值較長的索引列，我們建議使用前綴索引
- 降低索引條目新症，一方面不要創(chuàng)建沒用的索引步氏，不常使用的索引進行清除，percona toolkit(xxx),
pt-duplicate-key-checker ---檢查數(shù)據(jù)庫重復索引 - 索引維護徒爹，要避開業(yè)務繁忙期
- 小表不建索引
不走索引的情況（開發(fā)規(guī)范）
- 沒用查詢條件荚醒，或者條件沒用建立索引
select * from city,select * from city where 1=1 - 查詢結果集是原表的絕大部分數(shù)據(jù)，應該是25%以上
- 索引本身失效隆嗅，統(tǒng)計數(shù)據(jù)不真實
- 查詢條件調用函數(shù)在索引列上界阁，或者對索引列進行運算,運算包括（+,-,*,/,!）等
例desc select * from city where id-99=1 - 隱式轉換導致索引失效
CREATE TABLE ttt(id INT,num CHAR(2))
INSERT INTO ttt VALUES(1,2),(2,'3');
ALTER TABLE ttt ADD INDEX idx(num)
DESC SELECT * FROM ttt WHERE num=2 //不走索引
DESC SELECT * FROM ttt WHERE num='2' //走索引
num=2 ,MySQL會通過函數(shù)將2 轉換為字符串類型，根據(jù)上一條榛瓮，索引列有函數(shù)铺董，將不會走索引
- <>(相當于!),not ni 不走索引（輔助索引）
- like '%aa' ，百分號在前面時
- 聯(lián)合索引 ???
聯(lián)合索引禀晓，意外情況
將表中所有列建立聯(lián)合索引精续，每個列作為查詢條件都會走索引
