mysql索引——学习笔记及实验

MySQL索引

一、索引

1.1、索引概述

索引相当于一本书的目录；

有一列或者多列组合而成；

指向数据库中具体的位置；

可以优化数据库的查询速度，提高性能；

不同存储引擎决定不同的最大索引数量和最大的索引长度；（至少16个，至少256个字节）

B型数索引（默认）：innoDB,myisam存储引擎；

哈希索引：memory存储引擎。

1.2、作用

优点：

唯一性索引，保证数据表中每一行数据的唯一性；

创建索引的主要原因，加快数据检索速度；

加速表和表的连接，对参考完整性有意义；

减少查询中分组和排序的时间。

缺点：

创建索引耗费的时间随着数据量的增加而增加；

占用一定的物理空间；

在对数据表进行增加或删除，修改时，索引也要动态的维护，增加维护时间，所以最好先删除了索引，在执行插入等操作。

特征：

唯一性索引：

唯一性索引保证了在索引列中的全部数据是唯一的，不会包含数据冗余；

如果已有主键约束或者唯一性约束，在创建或者修改表时，mysql会自动创建一个唯一性索引；

如要保证唯一性，则应创建主键约束或者唯一性键约束，而不是创建唯一性索引；

创建唯一性键或者主键越约束时，mysql会自动创建一个唯一性索引，进而检查是否有数据冗余，如有，则取消执行并返回报错信息，确保了表中每一行数据的唯一性，确保了一个实体可以唯一确认，并且只能在可以保证实体完整性的列上创建唯一性索引。

（主键约束或者唯一性约束——>唯一性索引——>保证唯一性）

复合索引：

当两个或者多个列作为一个关键值时，最好创建复合索引；

最多16列；

来自同一个表；

注意列的排序顺序（where子句必须参考复合索引的第一个列）；

提高性能，减少索引数量；

1.3、分类

1.3.1普通索引：没有任何约束

Create table newtable(

Id int(4) not null primary key,

Name,varchar(20),

Age int(4),

Index name_index(name(10))

);

在创建好的表上：

Alter table newtable add index name_index(name(5),DESC|ASC 降序|升序)

1.3.2唯一性索引：unique，主键就是一种特殊唯一性索引

Create table newtable(

Id int(4) not null primary key,

Name,varchar(20),

Age int(4),

Unique Index name_index(id ASC)

);

在已经创建好的表上：

Alter table newtable add unique index(age)

1.3.3全文索引：fulltext，只能在char,varchar,text上创建，多用于多文字的字段查询

Create table newtable(

Id int(4) not null primary key,

Name,varchar(20),

Age int(4),

Fulltext Index name_index(name(10))

);

1.3.4单列索引：只要保证单列字段

1.3.5多列索引：两列或者多列字段，条件查询时只使用第一个字段

Create table newtable(

Id int(4) not null primary key,

Name,varchar(20),

Age int(4),

Index name_age_index(name,age)

);

1.3.6空间索引：spatial，只建立在空间数据类型上，例如geometry，point，linestring，polygon，mysiam存储引擎支持空间检索，不能为空。

二、索引定义和管理（以试验为例）

在数据库job下创建worklnfo表。创建表的同时在id字段上创建名为index_id的唯一性索引，而且以降序的格式排列。

Worklnfo表结构信息如下：

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
id	编号	INT(4)	是	否	是	是	是
Name	职位名称	VARCHAR(20)	否	否	是	否	否
Type	职位类型	VARCHAR(10)	否	否	否	否	否
Address	工作地点	VARCHAR(50)	否	否	否	否	否
Wages	工资	YEAR	否	否	否	否	否
Contents	工作内容	TINYTEXT	否	否	否	否	否
extra	附加信息	TEXT	否	否	否	否	否