【Mysql】索引的分类、设计原则、创建与删除

【1】索引的含义和特点

索引是一种特殊的数据库结构，可以用来查询数据库表中的特定记录，索引是提高数据库性能的重要方式。MySQL中所有的数据类型都可以被索引，Mysql的索引包括普通索引，全文索引，单列索引，多列索引和空间索引等…

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度
通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。
否则，数据库系统将读取每条记录的所有信息进行匹配。

例如：索引相当于新华字典的音序表。如果要查“库”字，如果不使用音序，需要从字典的400页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度。这样有效的提高了数据库系统的性能。

不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B型树(BTREE)索引和哈希(HASH)索引。InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY存储引擎支持HASH索引和BTREE索引，默认为前者。

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

索引的优点是可以提高检索数据的速度
这是创建索引的最主要的原因:对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度;使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间
耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间，每一个索引要占一.定的物理空间;增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。因此，选择使用索引时，需要综合考虑索引的优点和缺点。

注意：索引可以提高查询的速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序。这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据。插入完成后，再创建索引。

【2】索引的分类

【1】普通索引
在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在student表的stu_ id 字段上建立-一个普通索引。查询记录时，就可以根据该索引进行查询。

【2】唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引。 在创建唯一性索引时， 限制该索引的值必须是唯一的。例如，在student表的stu _name 字段中创建唯一性索引， 那么stu name 字段的值就必需是唯一的。通过唯一性索引， 可以更快速地确定某条记录。主键就是-种特殊唯一性索引。

【3】全文索引
使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student 表的information 字段是TEXT类型，该字段包含了很多的文字信息。在information字段上建立全文索引后，可以提高查询information字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

【4】单列索引
在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引， 还可以是全文索引。只要保证该索引只对应一个字段即可。

【5】多列索引
多列索引是在表的多个字段上创建个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一一个字段时，索引才会被使用。例如，在表中的id、name 和sex字段上建立-一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

【3】索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创