1.索引的分類

為扎囊等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及扎囊網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都網(wǎng)站制作、成都網(wǎng)站設(shè)計、外貿(mào)營銷網(wǎng)站建設(shè)、扎囊網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

在MySQL中索引可以分為三類:

1） 主鍵索引:設(shè)置為主鍵的列會創(chuàng)建主鍵索引，主鍵唯一非空。

2） 單列索引：即索引中只包含一個列，一張表可以有多個單列索引

3） 唯一索引:索引列的值必須唯一，允許有空值

4）復(fù)合索引:即索引中可以包含多個列

2.創(chuàng)建索引

可以在創(chuàng)建表的同時創(chuàng)建索引，也可以在已有表的基礎(chǔ)上去創(chuàng)建索引

環(huán)境準(zhǔn)備創(chuàng)建數(shù)據(jù)庫和表:

CREATE DATABASE demo_01 DEFAULT CHARSET=utf8mb4;

USE demo_01;

CREATE TABLE `city` (

`city_id` INT(11) NOT NULL AUTO_INCREMENT,

`city_name` VARCHAR(50) NOT NULL,

`country_id` INT(11) NOT NULL,

PRIMARY KEY (`city_id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (

`country_id` INT(11) NOT NULL AUTO_INCREMENT,

`country_name` VARCHAR(100) NOT NULL,

PRIMARY KEY (`country_id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES(1,'西安',1);

INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES(2,'NewYork',2);

INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES(3,'北京',1);

INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES(4,'上海',1);

INSERT INTO `country` (`country_id`, `country_name`) VALUES(1,'China');

INSERT INTO `country` (`country_id`, `country_name`) VALUES(2,'America');

INSERT INTO `country` (`country_id`, `country_name`) VALUES(3,'Japan');

INSERT INTO `country` (`country_id`, `country_name`) VALUES(4,'UK');

為city表中的city_name字段創(chuàng)建索引

Create index idx_city_name on city(city_name)

查看city表中的索引

Show index from city

刪除city表上的索引idx_city_name

Drop index idx_city_name on city

使用alter命令添加索引

1). alter table tb_name add primary key(column_list);
該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL
2). alter table tb_name add unique index_name(column_list);
這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會出現(xiàn)多次）
3). alter table tb_name add index index_name(column_list);
添加普通索引， 索引值可以出現(xiàn)多次。
4). alter table tb_name add fulltext index_name(column_list);
該語句指定了索引為FULLTEXT， 用于全文索引

索引設(shè)計原則

在創(chuàng)建索引的時候我們需要遵循一些原則以更好的利用索引

1） 對經(jīng)常需要進(jìn)行查詢并且數(shù)據(jù)量很大的表創(chuàng)建索引

2） 在選擇索引字段的時候一般選擇作為查詢條件的字段，如果作為查詢條件的字段比較多則選擇最常用的一個。

3） 選擇區(qū)分度高的字段作為索引，區(qū)分度越高索引的效率越高

4） 雖然索引可以極大的提高查詢效率，但也不是多多益善，在做增刪改操作的時候需要對索引進(jìn)行維護(hù)，如果索引過多，維護(hù)的成本相應(yīng)的也高。而且如果索引過多，選擇索引也需要消耗一些資源。

5） 使用短索引，因為索引也是存在磁盤上的，磁盤的io效率對索引查找的性能也有較大的影響，索引越短，在一個存儲塊內(nèi)能存儲的索引值就越多，io效率也就越高

6） 創(chuàng)建組合索引，如果N個列創(chuàng)建的組合索引相當(dāng)于創(chuàng)建了N個索引。

單列索引和組合索引的區(qū)別：

這里有sql語句:

SELECT * FROM city WHERE city_name = '北京' AND country_id = 1

如果在city_name上創(chuàng)建單列索引

則執(zhí)行該sql語句時會使用索引查詢出city_name為北京的記錄放到中間表中，再在結(jié)果中過濾出country_id為1的記錄。

如果在city_name和country_id上創(chuàng)建單列索引

則執(zhí)行該sql語句時只會使用city_name上的索引，country_id上的索引不會用上，為什么呢？因為這里涉及到了mysql優(yōu)化器的優(yōu)化策略，當(dāng)多個條件查詢時，優(yōu)化器會評估用哪個條件的索引效率最高，它會選擇效率最高的索引來使用，所以這里city_name上的索引或country_id上的索引都可能被選中，也有可能同時使用這兩個索引。

如果在city_name和country_id上創(chuàng)建組合索引，且順序為city_name在前則測試如下:

SELECT * FROM city WHERE city_name = '北京'

會使用該索引

SELECT * FROM city WHERE country_id=1

不會使用該索引

SELECT * FROM city WHERE city_name = '北京' AND country_id = 1

會使用該索引

SELECT * FROM city WHERE country_id = 1 and city_name = '北京'

會使用該索引

為什么會產(chǎn)生這樣的結(jié)果呢？

我們可以把聯(lián)合索引看成是電話簿

人名由姓和名組成，聯(lián)合索引首先按姓氏進(jìn)行排序，如果姓氏相同則按名字排序，如果您知道姓就可以通過姓快速查找改姓的人，如果您知道姓和名就可以快速定位到人。如果您只知道名則無法利用電話簿的這種排序方式提高查找速度

所以在創(chuàng)建聯(lián)合索引時應(yīng)該仔細(xì)考慮索引列的順序，對索引列的所有列執(zhí)行搜索或者僅對索引列的前幾列進(jìn)行搜索時聯(lián)合索引非常有用，僅對后面任意列進(jìn)行搜索時聯(lián)合索引沒有用處。

最左前綴原則：

也就是以最左邊為起點任何連續(xù)的索引都能匹配上，所以在創(chuàng)建聯(lián)合索引的時候where查詢語句使用最頻繁的列放在最左邊擴展性比較好，比如上例中經(jīng)常需要根據(jù)city_name來查詢在創(chuàng)建聯(lián)合索引的時候就可以把city_name放在最左邊。