索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间越多,如果表中查询的列有一个索引,MySQL能快速到达某个位置去搜寻数据文件,而不必查看所有数据。
1索引简介
数据库中的索引是某个表中一列或者若干列值的集合,是一个独立的、物理的数据库结构,一般是与图或视图中的列相关联,数据库用户可以利用索引快速地访问数据表中的数据信息。通常情况下,只有在需要经常查询索引列中的数据时,才在表上创建索引,因为索引的使用会占用磁盘的空间,并且降低添加、删除和更新数据行的速度。
索引是在存储引擎中实现的,所有存储引擎支持每个表至少16个索引,MySQL中索引的存储类型有两种:BTREE和HASH,具体和表的存储引擎相关,MyISAM和InnoDB存储引擎只支持BTREE索引;MEMORY存储引擎可以支持HASH和BTREE索引。
在数据库中,索引主要有以下的一些作用:
- 通过创建索引,可以保证数据记录的唯一性;
- 可以大大加快数据检索速度;
- 在使用分组或排序子句进行检索数据时,可以显著减少查询中分组和排序的时间。
索引的分类:
普通索引和唯一索引
- 普通索引:基本索引类型,允许在定义索引的列中插入重复值和空值
- 唯一索引:索引列的值必须唯一,但允许有空值,主键索引是一种特殊的唯一索引,不允许有空值
单列索引和组合索引
- 单列索引:一个索引值包含单个列,一个表中可以有多个单列索引
- 组合索引:在表的多个字段上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。
全文索引:全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入空值和重复值,全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建,MySQL中只有MyISAM存储引擎支持全文索引。
索引的设计原则:
- 索引并非越多越好,索引会影响INSERT、DELETE和UPDATE语句的性能
- 避免对经常更新的表进行过多的索引,索引列应该尽可能减少,对于经常查询的字段应该创建索引,但是避免添加不必要的字段
数据量小的表最好不要使用索引,此时建立索引并不会优化性能
在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引
- 灵活使用唯一索引
- 在频繁进行排序或分组的列上建立索引,可以提升性能
2创建索引
MySQL支持多种方法在单个或多列上创建索引:在创建表的定义语句CREATE TABLE中指定索引,使用ALTER TABLE语句在存在的表上创建索引,或者使用CREATE INDEX语句在已存在的表上添加索引。
创建表的时候创建索引:
create table table_name [col_name data_type] [unique|fulltext|spatial[index|key] [index_name] (column_name,...) [asc|desc];
在已经存在的表上创建索引
使用ALTER TABLE语句创建索引:
alter table table_name add [unique|fulltext|spatial] [index|key] [index_name] (column_name,...) [asc|desc];
使用CREATE INDEX语句创建索引:
create [unique|fulltext|spatial] [index|key] [index_name] on table_name (column_name,...) [asc|desc];
3删除索引
MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句。
使用ALTER TABLE删除索引:
alter table table_name drop index index_name;
使用DROP INDEX删除索引:
drop index index_name on table_name;