索引是排好序的數(shù)據(jù)結(jié)構(gòu)！可以用在 where 條件查找的字段，和order by 排序的字段，有了索引，便可以快速地定位數(shù)據(jù)所在的物理地址并找出來。

索引的分類

1.普通索引(normal)：沒有任何約束，主要用于提高查詢效率

2.唯一索引(UNIQUE)：在普通索引的基礎(chǔ)上增加了數(shù)據(jù)唯一性的約束，可以有多個(gè)

3.主鍵索引(primary key)：主鍵索引在唯一索引的基礎(chǔ)上增加了不為空的約束，也就是 NOT NULL+UNIQUE，只能有一個(gè)

4.全文索引(FULLTEXT)：MySQL 自帶的全文索引只支持英文。

一般使用專門的全文搜索引擎，比如 ES(ElasticSearch)

創(chuàng)建索引

#普通索引

ALTER TABLE 表名 ADD INDEX 索引名 (column_list);

#唯一索引

ALTER TABLE 表名 ADD UNIQUE 索引名(column_list);

#主鍵索引

ALTER TABLE 表名 ADD PRIMARY KEY 索引名(column_list);

#全文索引(字符串?dāng)?shù)據(jù)類型)

ALTER TABLE 表名 ADD fulltext 索引名(column_list);  

#查看索引

show index from student \G

這里用\G 代替分號結(jié)束符；可以格式化輸出數(shù)據(jù)信息。

#刪除索引

修改表的方式刪除索引

ALTER TABLE 表名 DROP INDEX 索引名;

刪除主鍵索引

ALTER TABLE 表名 DROP PRIMARY KEY;

索引列為一列時(shí)為單一索引；
多個(gè)列組合在一起創(chuàng)建的索引叫做聯(lián)合索引

什么情況下可以創(chuàng)建索引？

1.字段的數(shù)值有唯一性的限制，比如id

2.頻繁作為 WHERE 查詢條件的字段，尤其在數(shù)據(jù)表大的情況下

如果在數(shù)據(jù)量非常大的情況下，沒有 WHERE 條件過濾是非?？膳碌?。

3.需要經(jīng)常 GROUP BY 和 ORDER BY 的列

4.UPDATE、DELETE 的 WHERE 條件列，一般也需要?jiǎng)?chuàng)建索引

原理是因?yàn)槲覀冃枰雀鶕?jù) WHERE 條件列檢索出來這條記錄，然后再對它進(jìn)行更新或刪除，非索引字段更新數(shù)據(jù)效果更明顯；

如果索引太多了，在更新數(shù)據(jù)的時(shí)候，如果涉及到索引更新，就會造成負(fù)擔(dān)。

5.DISTINCT 字段需要?jiǎng)?chuàng)建索引

6.做多表 JOIN 連接操作時(shí)，創(chuàng)建索引需要注意以下的原則

連接表的數(shù)量盡量不要超過 3 張，每增加一張表就相當(dāng)于增加了 一次嵌套的循環(huán)，數(shù)量級增長會非?？?對用于連接的字段創(chuàng)建索引，并且該字段在多張表中的類型必須一致。

什么時(shí)候不需要?jiǎng)?chuàng)建索引?

索引的價(jià)值是快速定位，如果起不到定位的字段通常是不需要?jiǎng)?chuàng)建索引的

1.WHERE 條件（包括 GROUP BY、ORDER BY）里用不到的字段不需要?jiǎng)?chuàng)建索引；

2.表記錄太少，比如少于 1000 行，那么是不需要?jiǎng)?chuàng)建索引；

3.字段中如果有大量重復(fù)數(shù)據(jù)，也不用創(chuàng)建索引，比如性別字段；

4.頻繁更新的字段不一定要?jiǎng)?chuàng)建索引。因?yàn)楦聰?shù)據(jù)的時(shí)候，也需要更新索引，如果索引太多，在更新索引的時(shí)候也會造成負(fù)擔(dān)，從而影響效率；

什么情況下索引失效?

1.如果索引列進(jìn)行了表達(dá)式計(jì)算，使用函數(shù)，則會失效

2.在 WHERE 子句中，如果在 OR 前的條件列進(jìn)行了索引，而在 OR 后的條件列沒有進(jìn)行索引，那么索引會失效。

3.當(dāng)我們使用 LIKE 進(jìn)行模糊查詢的時(shí)候，表達(dá)式不能是 % 開始

4.索引列盡量設(shè)置為 NOT NULL 約束

判斷索引列是否為 NOT NULL，往往需要走全表掃描，因此我們最好在設(shè)計(jì)數(shù)據(jù)表的時(shí)候就將字段設(shè)置為 NOT NULL 約束比如你可以將 INT 類型的字段，默認(rèn)值設(shè)置為 0。將字符類型的默認(rèn)值設(shè)置為空字符串 ('')

以上就是MySQL索引的基本語法的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引語法的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論