1重绷、概述
??索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針膜毁≌炎浚可以將索引理解為一本書(shū)前面的目錄，能加快數(shù)據(jù)的查詢速度瘟滨。
對(duì)于沒(méi)有索引的表候醒，MySQL會(huì)遍歷全部數(shù)據(jù)后選擇符合條件的記錄，因此單表查詢可能幾十萬(wàn)數(shù)據(jù)就是瓶頸杂瘸，而通常大型網(wǎng)站單日就可能會(huì)產(chǎn)生幾十萬(wàn)甚至幾百萬(wàn)的數(shù)據(jù)倒淫，沒(méi)有索引查詢會(huì)變的非常緩慢，而有了相應(yīng)的索引之后败玉，MySQL會(huì)直接在索引中查找符合條件的選項(xiàng)敌土，效率會(huì)大大提升镜硕。
??索引分為聚簇索引和非聚簇索引兩種：
- 聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的，對(duì)于數(shù)據(jù)而言返干，通常來(lái)說(shuō)物理順序結(jié)構(gòu)只有一種兴枯，因此每張數(shù)據(jù)表也只能有一個(gè)聚簇索引。在設(shè)置主鍵時(shí)矩欠，系統(tǒng)會(huì)默認(rèn)為其加上聚簇索引财剖，當(dāng)然也可以使用其他字段作為索引，此時(shí)需要在設(shè)置主鍵之前先手動(dòng)為待選字段添加上唯一的聚簇索引癌淮，然后再設(shè)置主鍵躺坟，就可以解決這一問(wèn)題。
- 非聚簇索引記錄的物理順序與邏輯順序沒(méi)有必然的聯(lián)系乳蓄，與數(shù)據(jù)的存儲(chǔ)物理位置也沒(méi)有關(guān)系咪橙；每張數(shù)據(jù)表對(duì)應(yīng)的非聚簇索引可以有多個(gè)，根據(jù)不同列的約束可以建立不同要求的非聚簇索引栓袖。
??簡(jiǎn)單總結(jié)即匣摘，聚簇索引能提高多行檢索的效率，而非聚簇索引對(duì)于單行的檢索更有效裹刮。
2音榜、索引的類型
普通索引
??普通索引是最基本的索引，它沒(méi)有任何限制捧弃，是大多數(shù)情況下用到的索引赠叼。
直接創(chuàng)建索引
CREATE INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時(shí)添加索引
ALTER TABLE tbl_name ADD INDEX index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時(shí)同時(shí)創(chuàng)建索引
CREATE TABLE tbl_name (
……
INDEX index_name (col_name(length))
)
唯一索引
??唯一索引與普通索引類似，不同之處是：索引列的值必須唯一违霞，但允許有空值（注意和主鍵不同）嘴办。如果是組合索引，則列值的組合必須唯一买鸽，創(chuàng)建方法和普通索引類似涧郊。
直接創(chuàng)建索引
CREATE UNIQUE INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時(shí)添加索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時(shí)同時(shí)創(chuàng)建索引
CREATE TABLE tbl_name (
……
UNIQUE index_name (col_name(length))
)
全文索引
??全文索引僅可用于MyISAM數(shù)據(jù)表，對(duì)于較大的數(shù)據(jù)集而言眼五，將資料輸入進(jìn)一個(gè)沒(méi)有全文索引的數(shù)據(jù)表中妆艘，然后創(chuàng)建索引；其速度遠(yuǎn)比把資料輸入現(xiàn)有全文索引的數(shù)據(jù)表中更快看幼。不過(guò)對(duì)于大容量的數(shù)據(jù)表來(lái)說(shuō)批旺，生成全文索引是一個(gè)非常消耗時(shí)間及硬盤(pán)空間的做法。
直接創(chuàng)建索引
CREATE FULLTEXT INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時(shí)添加索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時(shí)同時(shí)創(chuàng)建索引
CREATE TABLE tbl_name (
……
FULLTEXT index_name (col_name(length))
)
多列索引诵姜、單列索引
??單個(gè)多列索引與多個(gè)單列索引的查詢效果不同汽煮。當(dāng)使用多個(gè)單列索引并執(zhí)行查詢時(shí)，MySQL只會(huì)從多個(gè)索引中選擇一個(gè)限制最為嚴(yán)格的索引，剩余的索引將起不到作用暇赤。在建立多列索引時(shí)心例，字段的順序也是需要注意的，應(yīng)該將嚴(yán)格的索引放在前面翎卓，這樣篩選的力度會(huì)更大契邀，效率更高。
組合索引（最左前綴）
??平時(shí)用的SQL查詢語(yǔ)句一般都有比較多的限制條件失暴，因此為了進(jìn)一步提高M(jìn)ySQL的效率坯门，就要考慮建立組合索引。例如上圖中針對(duì)“l(fā)ast_name”和“first_name”建立一個(gè)組合索引：
INDEX name (last_name,first_name)
??建立這樣的組合索引逗扒，其實(shí)是相當(dāng)于分別建立了下面兩組組合索引：
(last_name,first_name)
(last_name)
??之所以沒(méi)有(first_name)這樣的組合索引古戴，是因?yàn)镸ySQL組合索引采取“最左前綴”的結(jié)果。簡(jiǎn)單的理解就是只從最左面的開(kāi)始組合矩肩，組合索引的最左列一定選擇好现恼，否則無(wú)法起到索引的效果。如果查詢時(shí)最左列不在查詢條件中則該組合索引不會(huì)被使用黍檩。
??最左列一定是使用最頻繁的列叉袍，然而并不是只要包含在組合索引中的列的查詢都會(huì)用到該組合索引，例如以下形式的查詢語(yǔ)句能夠使用組合索引：
SELECT * FROM tbl_name WHERE last_name='Widenius';
SELECT * FROM tbl_name WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM tbl_name WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
??以下形式的查詢語(yǔ)句不能使用組合索引：
SELECT * FROM tbl_name WHERE first_name='Michael';
SELECT * FROM tbl_name WHERE last_name='Widenius' OR first_name='Michael';
查看索引
SHOW INDEX FROM tbl_name;
SHOW KEYS FROM tbl_name;
刪除索引
DORP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;
3刽酱、索引的缺點(diǎn)
??雖然索引極大提高了數(shù)據(jù)查詢的速度喳逛，但同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT棵里、UPDATE和DELET時(shí)润文，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件殿怜，這樣就會(huì)降低數(shù)據(jù)的維護(hù)速度典蝌。
建立索引會(huì)占用磁盤(pán)空間，一般而言這個(gè)問(wèn)題不太嚴(yán)重头谜，但如果在一個(gè)擁有大量數(shù)據(jù)的表上創(chuàng)建了多種組合索引骏掀，索引文件的會(huì)膨脹很快。
索引只是提高效率的一個(gè)因素柱告，如果有大數(shù)據(jù)量的表砖织，就需要花費(fèi)時(shí)間研究建立最優(yōu)秀的索引組合，或優(yōu)化查詢語(yǔ)句末荐，且隨著數(shù)據(jù)量的增加，維護(hù)索引的成本也會(huì)增加新锈。
4甲脏、使用索引的注意事項(xiàng)
如何選擇聚簇索引或非聚簇索引
| 動(dòng)作描述 | 使用聚簇索引 | 使用非聚簇索引 |
|---|---|---|
| 列經(jīng)常被分組排序 | 使用 | 使用 |
| 返回某范圍內(nèi)的數(shù)據(jù) | 使用 | 不使用 |
| 一個(gè)或極少不同值 | 不使用 | 不使用 |
| 少量的不同值 | 使用 | 不使用 |
| 大量的不同值 | 不使用 | 使用 |
| 頻繁更新的列 | 不使用 | 使用 |
| 外鍵列 | 使用 | 使用 |
| 主鍵列 | 使用 | 使用 |
| 頻繁修改索引列 | 不使用 | 使用 |
使用前綴長(zhǎng)度
??對(duì)于CHAR和VARCHAR列，只用一列的一部分就可創(chuàng)建索引。創(chuàng)建索引時(shí)块请，使用col_name(length)語(yǔ)法娜氏，對(duì)前綴編制索引。前綴包括每列值的前l(fā)ength個(gè)字符墩新。BLOB和TEXT列也可以編制索引贸弥，但是必須給出前綴長(zhǎng)度。
例如海渊，有一個(gè)CHAR(255)的列绵疲，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是唯一的臣疑，那么就不要對(duì)整個(gè)列進(jìn)行索引盔憨，而是CREATE INDEX index_name ON tbl_name(col_name(10))。
使用短索引不僅可以提高查詢速度讯沈，而且可以節(jié)省磁盤(pán)空間和I/O操作郁岩。
LIKE語(yǔ)句
??若LIKE語(yǔ)句是不以通配符開(kāi)頭的常量串，MySQL會(huì)使用索引缺狠。比如：
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'
或
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'
??而以下情況無(wú)法使用索引：
//LIKE語(yǔ)句以通配符開(kāi)頭
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'
//LIKE語(yǔ)句不是常量串
SELECT * FROM tbl_name WHERE key_col LIKE other_col
WHERE子句
- 在WHERE子句的查詢條件中進(jìn)行運(yùn)算會(huì)導(dǎo)致索引失效问慎；
- 在WHERE子句的查詢條件中使用了函數(shù)會(huì)導(dǎo)致索引失效；
- 在WHERE子句的查詢條件中使用“or”來(lái)連接條件會(huì)導(dǎo)致索引失效挤茄；
- 在WHERE子句的查詢條件中使用“!=”或“<>”操作符會(huì)導(dǎo)致索引失效如叼。
多表連接
??如果數(shù)據(jù)表需要很多連接查詢，首先應(yīng)該確認(rèn)兩張數(shù)據(jù)表中連接的字段已經(jīng)創(chuàng)建索引驮樊。這樣薇正，MySQL內(nèi)部會(huì)啟動(dòng)優(yōu)化連接的SQL語(yǔ)句的機(jī)制。
除此之外囚衔，這些被用來(lái)連接的字段挖腰，應(yīng)該是屬于同一類型。例如练湿，如果要把DECIMAL類型的字段和一個(gè)INT類型的字段連接在一起猴仑，MySQL就無(wú)法使用它們的索引。另外對(duì)于STRING類型肥哎，還需要有相同的字符集才行(兩張數(shù)據(jù)表的字符集有可能不一樣)辽俗。
避免使用“SELECT * ”
??從數(shù)據(jù)庫(kù)里讀出的數(shù)據(jù)越多，那么查詢就會(huì)變得越慢篡诽。而且如果數(shù)據(jù)庫(kù)服務(wù)器和WEB服務(wù)器是兩臺(tái)獨(dú)立的服務(wù)器的話崖飘，還會(huì)增加網(wǎng)絡(luò)傳輸?shù)呢?fù)擔(dān)。因此杈女，應(yīng)該養(yǎng)成需要什么就查找什么的好習(xí)慣：
//不推薦使用
SELECT * FROM tbl_name WHERE id = 1;
//推薦使用
SELECT username FROM tbl_name WHERE id = 1;
為每張數(shù)據(jù)表設(shè)置一個(gè)ID字段
??應(yīng)該為數(shù)據(jù)庫(kù)里的每張表都設(shè)置一個(gè)ID做為其主鍵朱浴，且最好為INT類型的(推薦使用UNSIGNED)吊圾，并設(shè)置上AUTO_INCREMENT。
使用例如VARCHAR類型來(lái)當(dāng)主鍵會(huì)使MySQL性能下降翰蠢。而且项乒，還有一些操作，例如集群梁沧、分區(qū)等需要使用主鍵檀何，在這些情況下，主鍵的性能和設(shè)置變得非常重要廷支。
盡可能的使用“NOT NULL”
??要盡可能地把字段定義為“NOT NULL”频鉴。即使該數(shù)據(jù)表無(wú)須保存“NULL”（沒(méi)有值），也有許多表包含了可空列（Nullable Column）酥泞，這僅僅因?yàn)樗悄J(rèn)選項(xiàng)砚殿。除非真的要保存“NULL”，否則就把字段定義為“NOT NULL”芝囤。
MySQL難以優(yōu)化引用了可空列的查詢似炎，它會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜悯姊∠勖辏可空列需要更多的儲(chǔ)存空間，還需要在MySQL內(nèi)部進(jìn)行特殊處理悯许。當(dāng)可空列被索引的時(shí)候仆嗦，每條記錄都需要一個(gè)額外的字節(jié)，還可能導(dǎo)致MyISAM中固定大小的索引(例如一個(gè)整數(shù)字段上的索引)變成可變大小的索引先壕。
即使要在表中儲(chǔ)存“沒(méi)有值”的字段瘩扼，還是有可能不使用“NULL”的±牛考慮使用0集绰、特殊值或空字符串來(lái)代替它。
5谆棺、索引的SQL語(yǔ)句匯總
普通索引
直接創(chuàng)建索引
CREATE INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時(shí)添加索引
ALTER TABLE tbl_name ADD INDEX index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時(shí)同時(shí)創(chuàng)建索引
CREATE TABLE tbl_name (
……
INDEX index_name (col_name(length))
)唯一索引
直接創(chuàng)建索引
CREATE UNIQUE INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時(shí)添加索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時(shí)同時(shí)創(chuàng)建索引
CREATE TABLE tbl_name (
……
UNIQUE index_name (col_name(length))
)全文索引
直接創(chuàng)建索引
CREATE FULLTEXT INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時(shí)添加索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時(shí)同時(shí)創(chuàng)建索引
CREATE TABLE tbl_name (
……
FULLTEXT index_name (col_name(length))
)查看索引
SHOW INDEX FROM tbl_name;
SHOW KEYS FROM tbl_name;刪除索引
DORP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;
版權(quán)聲明：歡迎轉(zhuǎn)載栽燕，歡迎擴(kuò)散，但轉(zhuǎn)載時(shí)請(qǐng)標(biāo)明作者以及原文出處改淑，謝謝合作碍岔！ ↓↓↓
