数据库索引

一、索引定义

索引是一种独立存在的，对数据库表中一列或多列的值进行排序（目的：加快查询速度，提高查询效率）。

二、案例演示

索引是如何提高查询速度的？

示例：

-- 未建立索引
select * from test_index where name='我的年龄12999';
-- 通过show profiles，查看查询的执行时间
show PROFILES;
-- 建立一个索引
create index name_index on test_index(name);
-- 再次通过show profiles，查看查询的执行时间
-- 结论：索引是可以提高查询速度的

三、建立索引

1、索引的分类

普通索引index：简单排序 加速查询

唯一索引unique

• 主键索引:primary key 加快查询+实现实体完整性约束(不能有重复，也不能是null值)
• 唯一索引:unique加快查询 不允许有重复值 可以空值(null值)

组合索引(多个字段构成索引)

全文索引fulltext(功能：分词)

空间索引(spatial):了解 数据库引擎

2、索引建立方式

2.1 普通索引

语法：

-- 方法1：创建索引
create index 索引名 on 表名(字段名(长度));
-- 文本型字段可以指定长度，其它的数据类型是不需要指定长度的

如果是 CHAR ， VARCHAR 类型， length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length ，下 同。

-- 方法2:修改表结构来建索引
alter table 表名 add index 索引名(字段名(宽度));
-- 关于宽度的描述与方法1相同

-- 方法3：建立表的同时直接指定索引
create table 表名(字段名1 类型1(宽度),
字段名2 类型2(宽度),
……,
index 索引名(字段名(宽度))
);

示例：

create index name_index on test_index(name);
drop index name_index on test_index;
alter table test_index add index xxhh(name(10));
drop index xxhh on test_index;
drop table test_index;
create table test_index(
id int not null primary key auto_increment,
name varchar(20) not null,
index xxhh(name(20))
);

2.2 唯一索引

语法：

方法1：创建唯一索引
create unique index 索引名 on 表名(字段名(宽度));
方法2：修改表结构的同时建立唯一索引
alter table 表名 add unique 索引名(字段名(宽度));
方法3：创建表的时候直接指定
create table 表名(字段名1 类型1(宽度),
字段名2 类型2(宽度),
……,
unique 索引名(字段名(宽度))
);

示例：

create unique index xx on mytable(id);
drop index xx on mytable;
alter table mytable add unique hh(id);
drop index hh on mytable;
drop table mytable;
create table mytable(
id int not null,-- 非空约束
username varchar(16) not null,
unique xxhh(id)
);

2.3 主键索引

语法：

create table 表名(字段名1 类型1(宽度),
字段名2 类型2(宽度),
……,
primary key(字段名)
);

示例：

create table mytable(
id int not null,-- 非空约束
username varchar(16) not null,
primary key(id)
);

2.4 组合索引

语法：

create table 表名(表名(字段名1 类型1(宽度),
字段名2 类型2(宽度),
……,
index 索引名(字段名1(宽度),字段名2(宽度))
);
alter table 表名 add index 索引名(字段名1(宽度),字段名2(宽度));
create index 索引名 on 表名(字段名1(宽度),字段名2(宽度));

示例：

create table mytable(
id int not null,
username varchar(16) not null,
city varchar(50) not null,
age int not null,
index city_age_username(username varchar(10),city varchar(50),age)
);

3、删除索引

语法：

drop index 索引名 on 表名;

示例:

drop index xxhh on test_index;

四、案例

1.先创建表

create table test
(
id int not null primary key auto_increment,
name varchar(30)not null
);

2.执行index.sql文件

set profiling = 1;

3.查询信息为39999的信息并查询执行时间
 

select * from test where name='我的年龄是39999';-- 查询时间0.060s
show profiles;-- 上述上SQL的执行时间为0.0602115

4.建立索引

create index name_index on test(name(20));
-- 针对name字段建立索引，索引名称为name_index;

5.再次查询

select * from test where name='我的年龄是39999';-- 查询时间为0.003s
show profiles;-- 0.0021415s

6.删除索引

drop index name_index on test;-- 删除索引