mysql中索引

一、概述

1、What？

 索引是对数据库表中一列或多列的值进行排序的的一种结构，可以提高数据库中特定的数据查询速度。

 索引时一个单独存储在磁盘上的数据库结构，包括对数据表里面的所有记录的引用指针。

 索引时在存储引擎中实现的，每种存储引擎的索引都不一定相同，也不一定支持所有索引类型，msyql中索引存储类型有两种：btree、hash，具体和表的存储引擎相关的：myisam、InnoDB。

2、Why？

 优点：

 1.通过创建唯一索引，可以保证数据库表中的每行数据的唯一性，提高数据的查询速度；

 2.加快数据库表语表之间的的连接；

 3.使用分组和排序子句进行数据查询时，减少查询中分组和排序的时间。

 缺点：

 1.创建索引和维护索引要耗费时间，随着数据量的增加所耗费的的时间也增加；

 2.索引需要占磁盘空间，每一个索引占一定的物理空间，如果有大量索引，索引文件可能比数据文件更快达到最大文件尺寸；

 3.对表中的数据进行增加、删除、修改时，索引需要动态维护，降低数据的维护速度。

二、索引分类

1.创建普通索引与单例索引,使用idnex关键字

alter table 表名 add index 索引名(表中索引的字段名);
alter table t_user add index user_name(t_name);

2.创建唯一索引，使用unique关键字

alter table 表名 add unique 索引名(表中索引的字段名);
alter table t_user add unique user_id(t_id);

3.创建组合索引，使用index关键字

alter table 表名 add index 索引名(字段1,t_字段2);
alter table t_user add index list_count(t_password,t_regtime);

4.创建全文索引，使用fulltext关键字

alter table 表名 add fulltext 索引名(表中要索引的字段名);

注：由于fulltext创建全文索引只要MyISAM存储支持，需要更改mysql存储引擎。全文索引适合大型数据集。

5.创建空间索引，使用spatidx关键字

alter table 表名 add spatidx 索引名(表中要索引的字段名);

注：由于spatidx创建全文索引只要MyISAM存储支持，需要更改mysql存储引擎。

三、索引操作

1.查询索引

例1：

show index from t_user;

Non_unique:索引唯一性，1表示非唯一，0表示唯一索引；

Key_naem:索引名称；

Seq_in_index:字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引中的顺序；

Column_name:定义索引的列字段；

Sub_part:索引长度；

Null:表示该字段是否能为空值；

Index_type:表示索引类型；

例2：

show create table t_user;

例3：

explain select * from t_user;

2.删除索引

使用alter table语句删除索引：

alter table 表名 drop index 索引名称;

使用drop index语句删除索引：

drop index 索引名称 on 表名;

四、索引设计原则

1.避免对经常更新的表进行过多的索引，索引中列尽量可能少，对于经常查询的字段应该创建索引；

2.数据量小的表最好不要用索引；

3.索引不是越多越好，表中如有大量的索引，不仅占用磁盘空间，也会影响instead、delete、update语句性能，因为表中的数据更改的同时，索引也会进行调整和更新。

4.数据本身具有唯一性特征时，指定唯一索引，使用唯一索引确保定义的列的数据完整性，以提高查询速度；

5、频繁进行排序和分组的列上创建索引，如果待排序的列上有多个，可在这些列上建立组合索引。