MySQL系列6—索引

索引

一、索引介绍

1、索引是为了提升查询速度，会降低添加数据、更新数据、删除数据的速度。
2、经常用于where查询条件的字段添加索引 如：id。
3、字段重复数据较大的数据不适合添加索引，如：性别，保证数据的唯一性，唯一性越高查询速度越快。
4、数量较少的表，不适合添加索引。
索引介绍
用处： 几乎所有的索引都是添加到字段中的
作用： 使用索引，是为了加快查询速度，提高查询效率，约束数据的有效性
原理： 系统根据某种算法或者未来添加的数据，单独建立一个文件，实现快速匹配查找
索引的优点
通过唯一索引可以创建出每一行数据的唯一性
可大大提高数据库的检索速度，这是最主要的原因
加强表与表之间的联系，实现多表查询
在分组和排序中，同样可以少耗费很多时间
可以提高表的性能

索引的缺点
运行速度： 添加索引会延缓插入数据和修改数据的速度
耗费空间： 索引本身产生的文件就有可能比数据的文件还要大
消耗时间： 创建索引和维护索引要耗费时间，这种时间是根据数据量的增加而增加

​ 索引的分类
​ 1.主键索引
​ 作用： 确定数据表中一条特定数据记录的位置
​ 关键字： primary key
​ 创建： 一般创建在建表时候 id int not null auto_increment primary key
​ 注意： 主键不能为空，一个表中只能有一个主键索引
​ 2.外键索引
​ 概念： 外面的键，不是在自己的表中，如果一张表中的一个字段（非主键）指向另一张表中的主键，那么可以称该字段为外键
​ 作用： 用来在多表查询的时候方便联系两表数据
​ 关键字： foreign key
​ 使用： foreign key（外键名） references 主表（主键）
​ 约束
​ 对子表： 在对子表进行增和改的时候，如果外键对应在父表中找不到，那么就会失败
​ 对父表： 在对父表进行删和改的时候，如果在主表中已有数据被引用，那么也将失败
​ 外键的形成条件
​ 要求表类型必须是innodb
​ 如果不是innodb，那么即使创表成功，也没有了约束的效果
​ 外键的数据类型必须与主键的数据类型一致
​ 在一张表中外键名不能重复
​ 如果数据存在，那么必须保证外键中的数据和附表中的主键数据一致
​ 3.唯一索引
​ 作用： 唯一索引是为了避免出现重复的值，他的存在不是为了提高访问速度，而是避免出现重复数据
​ 关键字： unique
​ 使用： 一般在创建列的时候使用 name char（30） not null default ‘DB’ unique
​ 注意： 只有确认某一列不能出现重复的值得时候才能使用
​ 4.普通索引
​ 概念： 普通索引依附在某一列上，提高查询速度
​ 关键字： index
​ 使用： create index 索引名 on 表名（列名）
​ 索引的操作
​ 查看索引： show index from 表名
​ 删除索引： drop index 索引名 on 表名

二、索引的创建

1. 使用alter table 语句创建索引

   普通索引：

   alter table table_name add index index_name(字段);

   唯一索引：

   alter table table_name add unique(字段);

   主键索引：

   alter table table_name add primary key(字段);

2. 使用create index 语句对表增加索引

   create index index_name on table_name(username(length));

   如果是char或varchar类型，length可以小于字段实际长度；如果是text类型，必定要指定length

   create 只能创建两种索引：普通索引和唯一索引

   create index index_name on table_name(字段);

   create unique index index_name on table_name(字段);

三、删除索引

​ 删除索引可以使用alter table 或drop index 语句来实现，drop index 可以在alter table 内部作为一条语句来处理

​ 格式：drop index index_name on table_name;

​ alter table table_name drop index index_name;

​ alter table table_name drop primary key;

​ 一个表只有一个primary key索引，如果没有primary key，有多个unique索引时，则MySQL将删除掉第一个unique索引。