Mysql中索引的介紹

1.索引的分類

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

1） 主鍵索引:設置為主鍵的列會創建主鍵索引，主鍵唯一非空。

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

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

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

2.創建索引

可以在創建表的同時創建索引，也可以在已有表的基礎上去創建索引

環境準備創建數據庫和表:

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字段創建索引

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);
這條語句創建索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）
3). alter table tb_name add index index_name(column_list);
添加普通索引， 索引值可以出現多次。
4). alter table tb_name add fulltext index_name(column_list);
該語句指定了索引為FULLTEXT， 用于全文索引

索引設計原則

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

1） 對經常需要進行查詢并且數據量很大的表創建索引

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

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

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

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

6） 創建組合索引，如果N個列創建的組合索引相當于創建了N個索引。

單列索引和組合索引的區別：

這里有sql語句:

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

如果在city_name上創建單列索引

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

如果在city_name和country_id上創建單列索引

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

如果在city_name和country_id上創建組合索引，且順序為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 = '北京'

會使用該索引

為什么會產生這樣的結果呢？

我們可以把聯合索引看成是電話簿

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

所以在創建聯合索引時應該仔細考慮索引列的順序，對索引列的所有列執行搜索或者僅對索引列的前幾列進行搜索時聯合索引非常有用，僅對后面任意列進行搜索時聯合索引沒有用處。

最左前綴原則：

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