索引介绍和使用

一.索引的概念

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引好比是一本书前面的目录，这本书就相当于一个数据库，如果我们想要查看某个内容，建立索引之后就可以先去目录里面找到对应的章节页码，然后直接跳转到具体的位置，这样能加快数据库的查询速度，不需要将所有的内容从头开始检索一遍

二.索引的作用

建立索引的目的是加快对表中记录的查找或排序。

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

索引具有两面性，合理建立的索引可以优化数据库查询的速度， 但是同时索引的建立，会带来更多的系统开销，如果建立了没有用到的索引，不适当的索引，过多的索引，反而会导致查询性能下降

三.常用索引介绍及创建

索引主要分为聚簇索引和非聚簇索引，聚簇索引是根据数据行的键值在表中排序存储数据行，索引指向该聚簇索引的某个特定分区

非聚簇索引中的逻辑顺序并不等同于表中行的物理顺序，其中指针包括数据行在数据页中的偏移量

1.普通索引（最基本的索引，没有任何限定）

-直接创建索引

create index index_nam on table(column)

-修改表结构的方式添加索引

alert table table_name add index index_name column

-创建表的时候同时创建索引

create table 'table'(

id int not null auto_increment,

name varchar(255),

password varchar(255),

primary key(id),

index index_name name

)

-删除索引

drop index index_name on table;

2.唯一索引

唯一索引要求索引列的值必须唯一，但是允许有空值（跟主键不同）。如果是组合索引，则列值地组合必须唯一。

-创建唯一索引

create unique index index_nam on table(column)

-修改表结构的方式添加唯一索引

alert table table_name add unique index_name column

-创建表的时候同时创建唯一索引

create table 'table'(

id int not null auto_increment,

name varchar(255),

password varchar(255),

primary key(id),

unique index_name name(255)

)

3.主键索引

一般数据库都会根据主键生成索引，这是在创建表的时候就完成的。

4.如何使用索引

假设我们在查询时使用类似如下的语句：

select * from user where username="cai";

在select前面加上关键词explain，数据库将解释如何处理select，提供有关表如何联接和联接的次序。

explain select * from user where username="cai"; 在navicat中执行可以可以得到如下结果

1.id   select识别符。这是select的查询序列号。

2.select_type 枚举类型如下：

simple  简单select(不使用union或子查询)
primary   最外面的select
union    union中的第二个或后面的select语句
dependent union  union中的第二个或后面的select语句，取决于外面的查询
union result  union的结果。
subquery 子查询中的第一个select
dependent subquery  子查询中的第一个select，取决于外面的查询
derived    导出表的select(from子句的子查询)

3.table  输出的行所引用的表。

4.type  联接类型，枚举类型较多，有兴趣的可以自己去找一下

5.possible_keys 指出能使用哪个索引在该表中找到行

6.key 显示实际使用的索引

7.key_len 显示决定使用的键的长度

8.ref 使用哪个列或常数与key一起从表中选择

8.rows  rows列显示mysql认为它执行查询时必须检查的行数。

在 explain的帮助下，就知道什么时候该给表添加索引，以使用索引来查找记录从而让select 运行更快。

下面直接给name加上一个索引，就可以加快查询的速度。

create index user_username on user(username);

然后再用explain语句查看执行

检查的行数少了很多，而且对应有索引列的展示

如果有多个条件进行查询的时候，类似

select * from user where username="cai" and password="cai";

这种情况可能第一反应 是给password也建立一个索引，这并不是最好的方法。我们可以使用组合索引

create index user_username_password on user(username,password);

索引建立完整之后我们可以继续用explain语句查看

现在我们可以用以下的语句查看这张表有哪些索引：

show index from user 得到结果如下：

1.table 表名

2.Non_unique 索引是否可以包含重复的值，0：不行，1：可以

3.Key_name 索引的名字

4.Seq_in_index 索引中列的序列号

5.Column_name 列名称

6.Collation 列以什么方式存储在索引中，A表示升序

7.Cardinality 索引中唯一值的数目估算值

8.Sub_part 如果整列被编入索引，则为NULL

9.Packed 指示关键字如何被压缩，没有压缩，则为NULL

10.Null 如果列有null值，则为YES

11.Index_type 索引方法

12.Comment 多种评注

综上所述 在如何为数据库建立恰当的索引方面，应该有一些基本的概念了