数据库复习之（四）--索引

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

• 索引的含义和特点
• 索引的分类
• 如何设计索引
• 如何创建索引
• 如何删除索引

索引的含义和特点

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构，所以可以提高查询速度。
索引相当于新华字典的音序表，在查询数据时不需要读完所有的信息，而是只查询索引列。

• 优点：
  可以提高检索速度。对于由依赖关系的子表和父表之间联合查询是，可以提高查询速度；使用分组和排序子句进行数据查询是，同样可以显著节省查询中分组和排序的时间。
• 缺点：
  创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引也需要占用物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据维护速度降低。
  **注：**向有索引的表中插入数据时，由于要动态维护索引，插入的速度就会被降低，当插入大量数据时，可以先删除索引，在插入数据，然后再创建索引。

索引的分类

1. 普通索引
   在创建索引时，不加入任何限制条件。这类索引可以创建在任何数据类型上，其值是否唯一和非空，要字段本身的完整性约束条件来决定。建立索引后，查询时可以通过索引进行查询。
2. 唯一索引
   使用UNIQUE参数可以设置索引为唯一性索引。限制该索引的值必须是唯一的。主键就是一种特殊的唯一索引。
3. 全文索引
   使用FULLTEXT可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。
4. 单列索引
   在单个字段上创建索引。单列索引可以是普通索引，唯一索引，全文索引。
5. 多列索引
   在多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是只有查询条件中使用了这些字段的第一个字段时，索引才会被使用。
6. 空间索引
   使用参数SPATIAL可以设置空间索引。只能建立在空间数据类型上，可以提高系统获取空间数据的效率。空间数据类型：geometry和point，linestring和polygon。MyISAM存储引擎支持空间检索。
7. 稀疏索引
   在稀疏索引中，只为索引码的某些值建立索引项。同理因为稀疏索引也是聚集索引。每一个索引项包括索引值以及指向该搜索码值的第一条数据记录的指针。
   
8. 稠密索引
   在稠密索引中文件中的每个搜索码值都对应一个索引值。索引项包括索引值以及指向该搜索码值的第一条数据记录的指针。由于该索引符合聚集索引，因此记录根据相同的码值排序。

索引设计原则

1. 选择唯一性索引：可以更快的通过索引来确定某条记录
2. 为经常需要排序、分组和联合操作的字段建立索引：经常需要order by、group by、distinct和 uninon等操作子段，有效避免排序操作。
3. 为常作为查询条件的字段建立索引：提高表的查询速度
4. 限制索引数目：每个索引都会占用物理空间；修改表时，索引维护比较麻烦
5. 尽量使用数据量少的索引：索引值的长度会影响查询速度
6. 尽量使用前缀来索引：提高检索速度
7. 删除很少使用或不在使用的索引

创建和查看索引

创建索引的方式有3种：创建表时创建，在已经存在的表上创建，使用ALTER TABLE语句来创建索引

1. 创建表时直接创建：使用index 关键字

   create table tablename
   (
   propname1  type1 [constraint1],
   propname2  type2 [constraint2],
   ...
   [UNIQUE | FULLTEXT | SPATIAL] INDEX|KEY
   [indexname] (propname1 [(length)] [ASC|DESC])
   ); 
   

2. 在已经存在的表上创建

   create  [UNIQUE | FULLTEXT | SPATIAL] INDEX indexname 
   on tablename (propname [(length)] [ASC|DESC]);
   

3. 通过ALTER TABLE 语句创建

   alter  table tablename 
   add index [UNIQUE | FULLTEXT | SPATIAL] INDEX| KEY indexname  (propname [(length)] [ASC|DESC]);
   

删除索引

DROP INDEX indexname ON tablename；

注： 本文参考《Mysql 5.7 从入门到实践》张婷 一书。