MySQL--索引

索引

一、索引定义

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

二、案例演示

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

示例：

-- 未建立索引

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;