MySQL索引揭秘，详解索引概念与作用，让你的数据库查询快如闪电

简介：MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计就像汉语字典的目录页，可以按拼音、笔画、偏旁部首等排序的目录快速查找到需要的字。数据库索引用于快速找出在数据表中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的表有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

一、登陆MySQL数据库

执行指令# mysql –u root -p

二、索引的分类

常用的索引分为两类：单列索引和组合索引，其中单列索引包括普通索引、唯一索引和主键索引三种。下面分别介绍：

1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

1.1、普通索引： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

1.2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。

1.3、主键索引：是一种特殊的唯一索引，不允许有空值。

2、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

比如：

先执行指令mysql> use zrlog;切换到表空间zrlog，然后查看user表。如下图

接着再查看user表有哪些索引，执行的指令为mysql> show index from user\G;

三、索引的操作

1、普通索引相关操作

1.1创建一个表

create table tab1(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index i_name (name)

)

如下图：

1.2创建索引：create index ix_name on tab1(name);

1.3删除索引：drop index ix_name on tab1;

注意事项：对于创建索引时如果是BLOB和TEXT类型，必须指定length。)比如：create index index_name on tab1(extra(32))。

2、唯一索引相关操作

唯一性索引unique index和一般索引normal index最大的差异就是在索引列上增加了一层唯一约束。添加唯一性索引的数据列可以为空，但是只要存在数据值，就必须是唯一的。

2.1创建表和唯一索引

create table tab2(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

unique i_name (name)

)

如下图

2.2创建索引：create unique index ui_name on tab2(name(32));

2.3删除索引：drop unique index ui_name on tab2;

3、主键索引相关操作

在数据库中，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据，数据不能为空。

3.1创建表和主键索引

create table tab3(

nid int not null auto_increment,

name varchar(32) not null,

email varchar(64) not null,

extra text,

primary key(nid),

index tang (name)

)

如下图

3.2创建主键：alter table tab3 add primary key(name);

3.3删除主键：alter table tab3 drop primary key;

4、组合索引相关操作

在数据库中，组合索引，就是组合查询的意思，将两列或者多列组合成一个索引进行查询。

创建表和组合索引

create table tab4(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text

)

如下图

创建组合索引：create index x_name_email on tab4(name,email);

注意事项：数据库表中添加索引后能够提高查询速度，但前提必须是正确地使用索引来查询，如果以错误的方式使用，则即使建立索引不会奏效。