MySQL索引

1陆错、索引

索引(MySQL中也叫"key鍵")：是存儲引擎快速查詢記錄的一種數(shù)據(jù)結(jié)構(gòu)

2魄揉、索引存儲類型

InnoDB引擎使用的是B+Tree

3剪侮、索引類型

（1）主鍵索引(PRIMARY KEY)：值必須唯一，不允許有空值洛退，一般是在創(chuàng)建表的同時創(chuàng)建主鍵索引瓣俯，一個表只能有一個主鍵索引
（2）唯一索引(UNIQUE)：唯一索引列的值必須唯一，但允許有空值
ALTER TABLE 表名 ADD UNIQUE(字段名稱)
（3）普通索引(INDEX)：最基本的索引兵怯，沒有任何限制
ALTER TABLE 表名 ADD INDEX 索引名稱(字段名稱)
（4）組合索引(INDEX)：一個索引包含多個列彩匕，多用于避免回表查詢
ALTER TABLE 表名 ADD INDEX 索引名稱(字段名稱1，字段名稱2媒区，字段名稱N)
（5）全文索引(FULLTEXT)：大文本中進(jìn)行全文檢索驼仪，是目前搜索引擎使用的一種關(guān)鍵技術(shù)
ALTER TABLE 表名 ADD INDEX FULLTEXT(字段名稱)

4、查看指定表的索引

show index from 表名袜漩；

5绪爸、索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量，可以幫助服務(wù)器避免排序和臨時表
缺點(diǎn)：
①降低了更新表的速度宙攻，如對表進(jìn)行INSERT奠货、UPDATE和DELETE。因?yàn)楦卤頃r座掘，MySQL不僅要保存數(shù)據(jù)递惋，還要保存索引文件
②建立索引會占用磁盤空間的索引文件柔滔。一般情況這個問題不太嚴(yán)重，但如果在一個大表上創(chuàng)建多種組合索引萍虽，索引文件會膨脹很快
③如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容睛廊，為它建立索引就沒有太大的實(shí)際效果
④對于非常小的表，大部分情況下全表掃描更高效

6杉编、索引的設(shè)計(jì)原則

（1）適合索引的列是出現(xiàn)在where子句的列
（2）更新十分頻繁的字段不宜建立索引：因?yàn)楦虏僮鲿兏麭+樹超全，重建索引。這個過程是十分消耗數(shù)據(jù)庫性能的
（3）區(qū)分度不大的字段不宜建立索引：類似于性別這種區(qū)分度不大的字段王财，建立索引的意義不大卵迂。因?yàn)椴荒苡行н^濾數(shù)據(jù)，性能和全表掃描相當(dāng)绒净。另外返回?cái)?shù)據(jù)的比例在30%以外的情況下见咒，優(yōu)化器不會選擇使用索引
（4）業(yè)務(wù)上具有唯一特性的字段，即使是多個字段的組合挂疆，也必須建成唯一索引改览。雖然唯一索引會影響insert的速度，但是對于查詢的速度提升是非常明顯的缤言。即使在應(yīng)用層做了非常完善的校驗(yàn)控制宝当，只要沒有唯一索引，在并發(fā)的情況下胆萧，依然有臟數(shù)據(jù)產(chǎn)生
（5）多表關(guān)聯(lián)時庆揩，要保證關(guān)聯(lián)字段上一定有索引（外鍵字段要加索引）

7、 索引優(yōu)化規(guī)則

（1）如果MySQL估計(jì)使用索引比全表掃描還慢跌穗，則不會使用索引订晌。【返回?cái)?shù)據(jù)的比例是重要指標(biāo)蚌吸，比例越低越容易命中索引锈拨。記住這個范圍值：30%】
（2）前導(dǎo)模糊查詢不能命中索引【select * from user where name like '%s%'】
（3）非前導(dǎo)模糊查詢則可以使用索引，可優(yōu)化為使用非前導(dǎo)模糊查詢
（4）數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時候不會命中索引羹唠，特別是當(dāng)列類型是字符串奕枢，一定要將字符常量值用引號引起來
（5）復(fù)合索引的情況下，查詢條件不包含索引列最左邊部分(不滿足最左原則)佩微，不會命中復(fù)合索引
（6）union,in,or都能夠命中索引缝彬，建議使用in
（7）用or分割開的條件，如果or前的條件中列有索引哺眯，而后面的列中沒有索引跌造，那么涉及到的索引都不會被用到
（8）負(fù)向條件查詢不能使用索引，可以優(yōu)化為in查詢
（9）數(shù)據(jù)庫執(zhí)行計(jì)算或函數(shù)不會命中索引