一.索引的概念
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
索引好比是一本书前面的目录,这本书就相当于一个数据库,如果我们想要查看某个内容,建立索引之后就可以先去目录里面找到对应的章节页码,然后直接跳转到具体的位置,这样能加快数据库的查询速度,不需要将所有的内容从头开始检索一遍
二.索引的作用
建立索引的目的是加快对表中记录的查找或排序。
为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
索引具有两面性,合理建立的索引可以优化数据库查询的速度, 但是同时索引的建立,会带来更多的系统开销,如果建立了没有用到的索引,不适当的索引,过多的索引,反而会导致查询性能下降
三.常用索引介绍及创建
索引主要分为聚簇索引和非聚簇索引,聚簇索引是根据数据行的键值在表中排序存储数据行,索引指向该聚簇索引的某个特定分区
非聚簇索引中的逻辑顺序并不等同于表中行的物理顺序,其中指针包括数据行在数据页中的偏移量
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子句的子查询)
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 多种评注
综上所述 在如何为数据库建立恰当的索引方面,应该有一些基本的概念了