索引研究：（六）索引使用

索引研究：（六）索引使用

• 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但是这不是组合索引。
• 组合索引，即一个索引包含多个列。

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

普通索引

这是最基本的索引，他没有任何限制。普通索引（由关键字KEY或INDEX定义的索引）的唯一任务就是加快对数据的访问速度。因此，应该只为那些经常出现在查询条件（Where column = ）或者排序条件（ORDER BY column）中的数据列创建索引。

// 创建索引 ：如果是CHAR,VARCHAR类型的，length可以小于字段实际长度；如果是BLOB或者是TEXT类型，必须制定length
create index indexName on mytable(userName(length))

// 修改表结构
alter mytable add index[indexName] on (userName(length))

// 创建表的时候直接指定
create table mytable(id int not null ,userName varchar(16) not null,index[indexName](userName(length)));

// 删除索引
drop index [indexName] on mytable

唯一索引

唯一索引

它与前面的普通索引类似，不同的是：普通索引允许被索引的数据列包含重复的值。而唯一索引的值必须唯一，但是允许有空值。如果是组合索引，则列值的组合必须唯一。

// 创建索引
create unique index indexName on mytable(username(length))

// 修改表结构
alter mytable add unique[indexName] on (userName(length))

// 创建表的时候直接指定
create table mytable (id intn not null , userName varchar(16) not null,unique[indexName](userName(length)))

主键索引

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

一般是在建表的同时创建主键索引

create table mytable(id int not null,user_name varchar(16) not null ,primary key(id))

外建索引

如果为某个外键字段定义了一个外键约束条件，MySql就会定义一个内部素银来帮助自己以最有效率的方式去管理和使用外检约束条件。

组合索引

create table mytable (id int not null , user_name varchar(16) not null,city varchar(50) not null, age int not null);

为了进一步榨取MySQL的效率，就要考虑简历组合索引。就是将name，city，age 建到一个索引里：

aleter table mytable add index name_city_age(name(10),city,age)

建表时，user_name长度为16,这里用10。这是因为一般情况下名的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高insert的更新速度。

建立了这样的组合索引，其实相当于分别建立了下面三组组合索引：

user_name,city,age

user_name,city

user_name

为什么没有city,age这样的组合索引，因为Mysql组合索引 最左前缀的结果，结果的理解就是之从最左面的开始组合，并不是只要包含这三类的查询都会用到该组合索引，下面的几个sql会用到索引：

select * from mytable where user_name = 'admin ' and city = '郑州'

select * from mytable where name = 'admin'

下面几个则不会用到索引

select * from mytable where age ='20' and city = '郑州'

select * from mytable where city = '郑州'

如果分别在username,city,age上面建立单列索引，**让该表有三个单列索引，查询时和上述的组合索引效率也大不一样，远远低于我们的组合索引。**因为虽然此时有三个索引，但是MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。