MySQL-索引

1.含义

索引是对数据库表中一列或多列的值进行排序的一种结构。

2.特点

使用索引可以提高数据库中特定数据的查询速度；使用索引可以快速找出在某个或多个列中一特定值的行；所有MySQL列类型都可以被索引，使用索引是提高查询操作速度的最佳途径。

3.优缺点

优点：通过创建唯一索引，可以保证数据库表中每一行数据的唯一性；可以大大加快数据的查询速度；在实现数据库的参考完整性方面，可以加速表和表之间的连接；在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

缺点：创建索引和维护索引要耗费时间，并且随着数据量的增加所消耗的时间也会增加；索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间；对表中的数据进行增加、删除、更改的时候，索引也要动态的维护，极大的降低了数据的维护速度。

4.索引设计原则

索引并非越多越好，一个表中 如有大量的索引，不仅占用磁盘空 间，还会影响INSERT，DELETE、UPDATE等语句的性能，因为在表 中的 数据更改时，索引也会进行调整和更新。

避免对经常更新的表进行过多的索引，并且索引中 的列要尽可能 少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

数据量小的 表最好不要使用索引，由于数据较少，查询花费的时 间可能比遍历索引的时间还要短，索引可能不会产 生优化效果。

在条件表达式中经常用到的不同值较多的列上建立索引，在不同 值很少的列上不要 建立索引。比如在学生表的“性别”字段上只 有“男”与“女”两个不同值，因此就无须建立索引，如果建立索 引不但不会提 高查询效率，反而会严重降低数据更新速度。

当唯一性是某种数据本身的特征时， 指定唯一索引。使用唯一索 引需能确保定义的列的数据完整性，以提高查询速度。

在频繁进行排 序或分组（即进行group by或order by操作）的列上 建立索引，如果待排序的列有多个，可以在这些列 上建立组合索引。

5.索引分类

普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引：要求索引列必须唯一，但允许有空值。

单列索引：即一个索引值包含单个列，一个表可以有多个单列索引。

组合索引：指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。

全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找， 允许在这些 索引列中插入重复值和空值

空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据 类型有4 种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展， 使得能够用创建正规索引类似的语 法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL， 空间索 引只能在存储引擎为MyISAM的表中创建。

6.基本语法

--创建普通索引
create table dept_bak(
    d_id int primary key auto_increment,
    d_name varchar(50),
    d_start year,
    index()
)

show create table dept_bak;
explain select * from dept_bak where s_start=2019;

--删除索引
alter table tablename drop index indexname

--创建组合索引
create table test_db(
    vid int,
    vname varchar(50),
    vage int,
    vinfo varchar(200),
    index mul(vid,vname,vage)
)