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放在最左边。