MySQL点滴学习之索引类型

来源:互联网 时间:1970-01-01


索引是一种特殊的文件,它们包含着对数据表里所有记录的引用指针。通俗地说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。

索引是快速搜素的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。

例如: 创建一个user的表

CREATE TABLE user(
Id INT NOT NULL,
userName VARCHAR(50) NOT NULL,
age int not null,
City varchar(50)
);

然后,我们随机往表里把1000个不同userName值插入到user表中。其中有一条:555,zhy。

在查找userName="zhy"的记录 SELECT * FROM user WHERE userName="zhy"时,如果我们创建了userName列的索引,MySQL将在索引中排序userName列,对于索引中的每一项,MySQL在内部会为它保存一个数据文件中实际记录所在位置的“指针”。

因此在查找name等于zhy记录的Id,MySQL能够在userName的索引中查找zhy值,然后直接转到数据文件中相应的行,准确的返回该行的Id(555)。 再这个过程中,MySQL只需要处理一个行就可以返回结果,如果没有userName列的索引,MySQL要扫描数据文件中的所有记录,集1000个记录,显然,需要Mysql处理的记录数量越少,则它完成任务的速度越快!

索引的类型:

1. 普通索引

这是最基本的索引类型,而且没有唯一性之类的限制。普通索引创建方式:

创建索引:create index indexName on user(userName(length));
修改表结构:alter user add index (indexName) on (userName(length))
创建表的时候直接指定 : create table user(Id int not null,userName varchar(16) not null, index (indexName) (userName (length) ));

删除索引的方法:

drop index (indexName) on user;



2. 唯一索引

索引列的值必须唯一,允许有空值。

创建索引: create unique index indexName on user(userName(length));
修改表结构: alter user add unique(indexName) on (userName(length))
创建表的时候直接指定: Create table user (Id int not null, userName varchar(16) not null, unique [indexName) (userName(length)));

3. 主键索引

不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引。

create table user(Id int not null, userName varchar(16)not null, primary key(Id)); 当然也可以用alter 命令,但是记住:一个表只能有一个主键。

4. 组合索引

为了提高效率,下面我们讲name,city,age建到一个索引里:

alter table user add index name_city_age(name(10),city,age); 建表时,userName长度为16,这里用10. 这是因为一般情况下名字的长度不会超过10,这样就会加速索引查询速度,还会减少索引文件的大小,提高insert的更新速度。 建立这样的组合索引,其实相当于分别建立了userName,city,age userName,city userName 这三组组合索引。为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。

组合索引和单列索引不太一样,如果我们分别在userName、city、age上建立单列索引,虽然此时有三个索引,但是MySQL只能用到其中的那个它认为是最有效率的单列 索引, 所以效率远远低于组合索引。

参考:MySQL点滴学习之索引类型
http://www.lai18.com/content/437485.html



相关阅读:
Top