MySQL索引的概念
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)琼讽，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針便脊。更通俗的說蚂四，數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度哪痰。
索引分為聚簇索引和非聚簇索引兩種遂赠，聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的，而非聚簇索引就不一樣了晌杰；聚簇索引能提高多行檢索的速度跷睦，而非聚簇索引對(duì)于單行的檢索很快。
要注意的是肋演，建立太多的索引將會(huì)影響更新和插入的速度抑诸，因?yàn)樗枰瑯痈旅總€(gè)索引文件。對(duì)于一個(gè)經(jīng)常需要更新和插入的表格爹殊，就沒有必要為一個(gè)很少使用的where字句單獨(dú)建立索引了蜕乡，對(duì)于比較小的表，排序的開銷不會(huì)很大梗夸，也沒有必要建立另外的索引层玲。
- 普通索引
普通索引(由關(guān)鍵字KEY或INDEX定義的索引)的唯一任務(wù)是加快對(duì)數(shù)據(jù)的訪問速度。因此反症，應(yīng)該只為那些最經(jīng)常出現(xiàn)在查詢條件(WHERE column = ...)或排序條件(ORDER BY column)中的數(shù)據(jù)列創(chuàng)建索引辛块。只要有可能，就應(yīng)該選擇一個(gè)數(shù)據(jù)最整齊铅碍、最緊湊的數(shù)據(jù)列(如一個(gè)整數(shù)類型的數(shù)據(jù)列)來創(chuàng)建索引润绵。
1.直接創(chuàng)建索引(length表示使用名稱前1ength個(gè)字符)
CREATE INDEX index_name ON table_name(column_name(length))
2.修改表結(jié)構(gòu)的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column_name)
3.創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引
CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
4.刪除索引
DROP INDEX index_name ON table_name;
5.建立復(fù)合索引
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);
注意命名時(shí)的習(xí)慣了嗎？使用"表名_字段1名_字段2名"的方式
- 唯一索引
與普通索引類似胞谈，不同的就是：索引列的值必須唯一尘盼，但允許有空值（注意和主鍵不同）。如果是組合索引烦绳，則列值的組合必須唯一卿捎，創(chuàng)建方法和普通索引類似。
如果能確定某個(gè)數(shù)據(jù)列將只包含彼此各不相同的值爵嗅，在為這個(gè)數(shù)據(jù)列創(chuàng)建索引的時(shí)候就應(yīng)該用關(guān)鍵字UNIQUE把它定義為一個(gè)唯一索引。這么做的好處：一是簡(jiǎn)化了MySQL對(duì)這個(gè)索引的管理工作笨蚁，這個(gè)索引也因此而變得更有效率睹晒；二是MySQL會(huì)在有新記錄插入數(shù)據(jù)表時(shí)趟庄，自動(dòng)檢查新記錄的這個(gè)字段的值是否已經(jīng)在某個(gè)記錄的這個(gè)字段里出現(xiàn)過了；如果是伪很，MySQL將拒絕插入那條新記錄戚啥。也就是說，唯一索引可以保證數(shù)據(jù)記錄的唯一性锉试。事實(shí)上猫十，在許多場(chǎng)合，人們創(chuàng)建唯一索引的目的往往不是為了提高訪問速度呆盖，而只是為了避免數(shù)據(jù)出現(xiàn)重復(fù)拖云。
–創(chuàng)建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column_name)
–修改表結(jié)構(gòu)
ALTER TABLE table_name ADD UNIQUE index_name ON (column_name)
–創(chuàng)建表的時(shí)候直接指定
CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE index_name (title)
);
3.主索引
在前面已經(jīng)反復(fù)多次強(qiáng)調(diào)過：必須為主鍵字段創(chuàng)建一個(gè)索引，這個(gè)索引就是所謂的"主索引"应又。主索引與唯一索引的唯一區(qū)別是：前者在定義時(shí)使用的關(guān)鍵字是PRIMARY而不是UNIQUE宙项。
4.外鍵索引
如果為某個(gè)外鍵字段定義了一個(gè)外鍵約束條件，MySQL就會(huì)定義一個(gè)內(nèi)部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件株扛。
5. 全文索引（FULLTEXT）
MySQL從3.23.23版開始支持全文索引和全文檢索尤筐，fulltext索引僅可用于 MyISAM 表；他們可以從CHAR洞就、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創(chuàng)建盆繁，或是隨后使用ALTER TABLE 或CREATE INDEX被添加。對(duì)于較大的數(shù)據(jù)集旬蟋，將你的資料輸入一個(gè)沒有FULLTEXT索引的表中油昂，然后創(chuàng)建索引，其速度比把資料輸入現(xiàn)有FULLTEXT索引的速度更為快咖为。不過切記對(duì)于大容量的數(shù)據(jù)表秕狰，生成全文索引是一個(gè)非常消耗時(shí)間非常消耗硬盤空間的做法。
文本字段上的普通索引只能加快對(duì)出現(xiàn)在字段內(nèi)容最前面的字符串(也就是字段內(nèi)容開頭的字符)進(jìn)行檢索操作躁染。如果字段里存放的是由幾個(gè)鸣哀、甚至是多個(gè)單詞構(gòu)成的較大段文字，普通索引就沒什么作用了吞彤。這種檢索往往以LIKE %word%的形式出現(xiàn)我衬，這對(duì)MySQL來說很復(fù)雜，如果需要處理的數(shù)據(jù)量很大饰恕，響應(yīng)時(shí)間就會(huì)很長(zhǎng)挠羔。
這類場(chǎng)合正是全文索引(full-text index)可以大顯身手的地方。在生成這種類型的索引時(shí)埋嵌，MySQL將把在文本中出現(xiàn)的所有單詞創(chuàng)建為一份清單破加，查詢操作將根據(jù)這份清單去檢索有關(guān)的數(shù)據(jù)記錄。全文索引即可以隨數(shù)據(jù)表一同創(chuàng)建雹嗦，也可以等日后有必要時(shí)再使用下面這條命令添加：
ALTER TABLE table_name ADD FULLTEXT(column1, column2)
有了全文索引范舀，就可以用SELECT查詢命令去檢索那些包含著一個(gè)或多個(gè)給定單詞的數(shù)據(jù)記錄了合是。下面是這類查詢命令的基本語法：
SELECT * FROM table_name
WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')
上面這條命令將把column1和column2字段里有word1、word2和word3的數(shù)據(jù)記錄全部查詢出來锭环。
–創(chuàng)建表的適合添加全文索引
CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
–修改表結(jié)構(gòu)添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_name(column_name)
–直接創(chuàng)建索引
CREATE FULLTEXT INDEX index_name ON table_name (column_name)
6. 單列索引聪全、多列索引
多個(gè)單列索引與單個(gè)多列索引的查詢效果不同，因?yàn)閳?zhí)行查詢時(shí)辅辩，MySQL只能使用一個(gè)索引难礼，會(huì)從多個(gè)索引中選擇一個(gè)限制最為嚴(yán)格的索引。
7. 組合(復(fù)合)索引（最左前綴）
平時(shí)用的SQL查詢語句一般都有比較多的限制條件玫锋，所以為了進(jìn)一步榨取MySQL的效率蛾茉，就要考慮建立組合索引。例如上表中針對(duì)title和time建立一個(gè)組合索引：
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))景醇。
建立這樣的組合索引臀稚，其實(shí)是相當(dāng)于分別建立了下面兩組組合索引：
–title,time
–title
為什么沒有time這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果三痰。簡(jiǎn)單的理解就是只從最左面的開始組合吧寺。并不是只要包含這兩列的查詢都會(huì)用到該組合索引，如下面的幾個(gè)SQL所示
–使用到上面的索引
SELECT * FROM article WHREE title='測(cè)試' AND time=1234567890;
SELECT * FROM article WHREE title='測(cè)試';
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;
MySQL索引的優(yōu)化
上面都在說使用索引的好處散劫，但過多的使用索引將會(huì)造成濫用稚机。因此索引也會(huì)有它的缺點(diǎn)：雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度获搏，如對(duì)表進(jìn)行INSERT赖条、UPDATE和DELETE。因?yàn)楦卤頃r(shí)常熙，MySQL不僅要保存數(shù)據(jù)纬乍，還要保存一下索引文件。建立索引會(huì)占用磁盤空間的索引文件裸卫。一般情況這個(gè)問題不太嚴(yán)重仿贬，但如果你在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件的會(huì)膨脹很快墓贿。索引只是提高效率的一個(gè)因素茧泪，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時(shí)間研究建立最優(yōu)秀的索引聋袋，或優(yōu)化查詢語句队伟。下面是一些總結(jié)以及收藏的MySQL索引的注意事項(xiàng)和優(yōu)化方法。
1. 何時(shí)使用聚集索引或非聚集索引幽勒？
2. 索引不會(huì)包含有NULL值的列
只要列中包含有NULL值都將不會(huì)被包含在索引中嗜侮，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL锈颗。
3. 使用短索引
對(duì)串列進(jìn)行索引缠借，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如宜猜，如果有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)硝逢，多數(shù)值是惟一的姨拥，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作渠鸽。
4. 索引列排序
MySQL查詢只使用一個(gè)索引叫乌，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的徽缚。因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作憨奸；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引凿试。
5. like語句操作
一般情況下不鼓勵(lì)使用like操作排宰，如果非使用不可，如何使用也是一個(gè)問題那婉。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引板甘。
6. 不要在列上進(jìn)行運(yùn)算
例如：select * from users where YEAR(adddate)<2007，將在每個(gè)行上進(jìn)行運(yùn)算详炬，這將導(dǎo)致索引失效而進(jìn)行全表掃描盐类，因此我們可以改成：
select * from users where adddate<’2007-01-01′。關(guān)于這一點(diǎn)可以圍觀：一個(gè)單引號(hào)引發(fā)的MYSQL性能損失呛谜。
最后總結(jié)一下在跳，MySQL只對(duì)一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時(shí)候的like(不以通配符%或_開頭的情形)。而理論上每張表里面最多可創(chuàng)建16個(gè)索引隐岛，不過除非是數(shù)據(jù)量真的很多猫妙，否則過多的使用索引也不是那么好玩的，比如我剛才針對(duì)text類型的字段創(chuàng)建索引的時(shí)候礼仗，系統(tǒng)差點(diǎn)就卡死了吐咳。
補(bǔ)充EXPLAIN 用法：
只有當(dāng)數(shù)據(jù)庫里已經(jīng)有了足夠多的測(cè)試數(shù)據(jù)時(shí)，它的性能測(cè)試結(jié)果才有實(shí)際參考價(jià)值元践。如果在測(cè)試數(shù)據(jù)庫里只有幾百條數(shù)據(jù)記錄韭脊，它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內(nèi)存里，這將使后續(xù)的查詢命令都執(zhí)行得非车ヅ裕快--不管有沒有使用索引沪羔。只有當(dāng)數(shù)據(jù)庫里的記錄超過了1000條、數(shù)據(jù)總量也超過了MySQL服務(wù)器上的內(nèi)存總量時(shí)，數(shù)據(jù)庫的性能測(cè)試結(jié)果才有意義蔫饰。
在不確定應(yīng)該在哪些數(shù)據(jù)列上創(chuàng)建索引的時(shí)候琅豆，人們從EXPLAIN SELECT命令那里往往可以獲得一些幫助。這其實(shí)只是簡(jiǎn)單地給一條普通的SELECT命令加一個(gè)EXPLAIN關(guān)鍵字作為前綴而已篓吁。有了這個(gè)關(guān)鍵字茫因，MySQL將不是去執(zhí)行那條SELECT命令，而是去對(duì)它進(jìn)行分析杖剪。MySQL將以表格的形式把查詢的執(zhí)行過程和用到的索引(如果有的話)等信息列出來冻押。
在EXPLAIN命令的輸出結(jié)果里，第1列是從數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字盛嘿，它們按被讀取的先后順序排列洛巢。type列指定了本數(shù)據(jù)表與其它數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系(JOIN)。在各種類型的關(guān)聯(lián)關(guān)系當(dāng)中次兆，效率最高的是system稿茉，然后依次是const、eq_ref芥炭、ref漓库、range、index和All(All的意思是：對(duì)應(yīng)于上一級(jí)數(shù)據(jù)表里的每一條記錄园蝠，這個(gè)數(shù)據(jù)表里的所有記錄都必須被讀取一遍--這種情況往往可以用一索引來避免)米苹。
possible_keys數(shù)據(jù)列給出了MySQL在搜索數(shù)據(jù)記錄時(shí)可選用的各個(gè)索引。key數(shù)據(jù)列是MySQL實(shí)際選用的索引砰琢，這個(gè)索引按字節(jié)計(jì)算的長(zhǎng)度在key_len數(shù)據(jù)列里給出蘸嘶。比如說，對(duì)于一個(gè)INTEGER數(shù)據(jù)列的索引陪汽，這個(gè)字節(jié)長(zhǎng)度將是4训唱。如果用到了復(fù)合索引，在key_len數(shù)據(jù)列里還可以看到MySQL具體使用了它的哪些部分挚冤。作為一般規(guī)律况增，key_len數(shù)據(jù)列里的值越小越好(意思是更快)。
ref數(shù)據(jù)列給出了關(guān)聯(lián)關(guān)系中另一個(gè)數(shù)據(jù)表里的數(shù)據(jù)列的名字训挡。row數(shù)據(jù)列是MySQL在執(zhí)行這個(gè)查詢時(shí)預(yù)計(jì)會(huì)從這個(gè)數(shù)據(jù)表里讀出的數(shù)據(jù)行的個(gè)數(shù)澳骤。row數(shù)據(jù)列里的所有數(shù)字的乘積可以讓我們大致了解這個(gè)查詢需要處理多少組合。
7.key和index區(qū)別
mysql的key和index多少有點(diǎn)令人迷惑澜薄，這實(shí)際上考察對(duì)數(shù)據(jù)庫體系結(jié)構(gòu)的了解的为肮。
1).key 是數(shù)據(jù)庫的物理結(jié)構(gòu)，它包含兩層意義肤京，一是約束（偏重于約束和規(guī)范數(shù)據(jù)庫的結(jié)構(gòu)完整性）颊艳，二是索引（輔助查詢用的）。包括primary key, unique key, foreign key 等。
primary key 有兩個(gè)作用棋枕，一是約束作用（constraint）白修，用來規(guī)范一個(gè)存儲(chǔ)主鍵和唯一性，但同時(shí)也在此key上建立了一個(gè)index重斑；
unique key 也有兩個(gè)作用兵睛，一是約束作用（constraint），規(guī)范數(shù)據(jù)的唯一性窥浪，但同時(shí)也在這個(gè)key上建立了一個(gè)index卤恳；
foreign key也有兩個(gè)作用，一是約束作用（constraint）寒矿，規(guī)范數(shù)據(jù)的引用完整性，但同時(shí)也在這個(gè)key上建立了一個(gè)index若债；
可見符相，mysql的key是同時(shí)具有constraint和index的意義，這點(diǎn)和其他數(shù)據(jù)庫表現(xiàn)的可能有區(qū)別蠢琳。（至少在Oracle上建立外鍵啊终，不會(huì)自動(dòng)建立index），因此創(chuàng)建key也有如下幾種方式：
1.在字段級(jí)以key方式建立傲须， 如 create table t (id int not null primary key);
2.在表級(jí)以constraint方式建立蓝牲，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
3.在表級(jí)以key方式建立，如create table t(id int, primary key (id));
其它key創(chuàng)建類似泰讽，但不管那種方式例衍，既建立了constraint，又建立了index已卸，只不過index使用的就是這個(gè)constraint或key佛玄。
2).index是數(shù)據(jù)庫的物理結(jié)構(gòu)，它只是輔助查詢的累澡，它創(chuàng)建時(shí)會(huì)在另外的表空間（mysql中的innodb表空間）以一個(gè)類似目錄的結(jié)構(gòu)存儲(chǔ)梦抢。索引要分類的話，分為前綴索引愧哟、全文本索引等奥吩；
因此，索引只是索引蕊梧，它不會(huì)去約束索引的字段的行為（那是key要做的事情）霞赫。
如，create table t(id int, index inx_tx_id (id));
3).最后的釋疑：
(1).我們說索引分類肥矢，分為主鍵索引绩脆、唯一索引、普通索引(這才是純粹的index)等，也是基于是不是把index看作了key靴迫。
比如 create table t(id int, unique index inx_tx_id (id)); --index當(dāng)作了key使用
(2).最重要的也就是惕味，不管如何描述，理解index是純粹的index玉锌，還是被當(dāng)作key名挥，當(dāng)作key時(shí)則會(huì)有兩種意義或起兩種作用。
