MySQL学习笔记——索引

什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构。 MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高MySQL 的检索速度。索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

优点：

1.索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因；

2.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

3.索引可以帮助服务器避免排序和创建临时表。

缺点：

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

2.索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大；

3.对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度；

4.如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果；

5.对于非常小的表，大部分情况下简单的全表扫描更高效。

创建索引准则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

应该创建索引的列：

1.在经常需要搜索的列上，可以加快搜索的速度

2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

3.在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度

4.在经常需要根据范围（<， <= ， = ， > ， >= ， BETWEEN ， IN ）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

5.在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6.在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引的列：

1.对于那些在查询中很少使用的列不应该创建索引。

2.对于那些只有很少数据值或者重复值多的列也不应该增加索引。

3.对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。

4.当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

索引分类

主键索引：当我们创建一个表后，指定主键后，自动将主键设置为主键索引

普通索引：就是在一个普通的字段上建立索引

唯一索引：数据表中的某个字段是唯一的，没有重复

全文索引：在某个字段上建立全文索引，将记录内的关键字抽取出来，然后对每个关键字进行索引

创建索引

create [ 索引类型 ] index 索引名 on 表名 ( 表的某个字段上 )

# 创建普通索引

CREATE INDEX index_name ON table_name(col_name);

# 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

# 创建普通组合索引

CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

# 创建唯一组合索引

CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

如：

create index idx_name on account(name);

create unique index idx_name on account(name);

修改表，添加索引

alter table 表名 add [ 索引类型 ] index ( 表的字段 )

如：

alter table account add index idx_name(name);

删除索引

drop index 索引名 on 表名

如：

drop index idx_name on account;