MySQL索引

索引

  索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度，但是也会影响插入数据的速度，所以在插入大量数据时，可以选择先删除表中的索引。

含义和特点

  索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结构。通过索引，查询数据是可以不必完全记录所有的信息，而只是查询索引列。
  索引有两种存储类型，包括B型数索引和哈希索引。
  索引有其明显的优势，也有不可避免的缺点：

• 优点：可以提高检索数据的速度。
• 缺点：创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加
• 索引需要占用物理空间，每一个索引要占用一定的物理空间
• 增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低

索引的分类

  MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
普通索引
  在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。
唯一性索引
  使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引，可以更快地确定某条记录，主键就是一种特殊唯一性索引。
全文索引
  使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR、和TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询的速度。
单列索引
  在表中的单个字段上创建索引。单列索引值根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。
多列索引
  多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
聚集性索引
  在innoDB引擎下建立的索引，将索引和数据存放在一起。
非聚集性索引
  在MyIsam引擎下建立的索引，将索引和数据分开存储在不同的文件中。

索引的设计原则

选择唯一性索引
  唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
为需要经常排序、分组和联合操作的字段建立索引
  经常需要ORDER BY、GROUP BY、DISTINCT、和UNION等操作的字段，排序操作会浪费很多时间，可以建立索引，有效地避免排序操作。
为常作为查询条件的字段建立索引
  如果某个字段经常用作查询条件，可以为这样的字段建立索引，提高查询速度。
限制索引的数目
  索引的数目不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越多。
尽量使用数据量少的索引
  如果索引的值很长，那么查询的速度就会受到影响。
尽量使用前缀来索引
  如果索引字段的值很长，最好使用值的前缀来索引。
删除不再使用或者很少使用的索引
  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要，这样的索引就应该被删除。

创建索引

创建表的时候创建索引

CREATE TABLE demo (
id INT PRIMARY KEY,
dname VARCHAR(20) NOT NULL,
sex VARCHAR(5) NOT NULL,
UNIQUE INDEX index_dname(dname )
);

  其中UNIQUE是可选参数，代表唯一性索引，可以替换为FULLTEXT和SPATIAL；INDEX和KEY参数用来指定字段为索引，二者的作用相同；“别名”是可选参数，用来给创建的索引取新的名称；“长度”是可选参数，指索引的长度，必须是字符串类型才可以使用；“ASC和DESC都是可选参数”，用来指定排序规则。

在已经存在的表上创建索引

  在已经存在的表上，可以直接为表上的一个或者几个字段创建索引，基本形式如下：

使用ALTER TABLE语句来创建索引

  在已经存在的表上，除了可以直接创建索引之外，也可以修改表结构的形式来创建索引，基本形式如下：

删除索引

  通过前面的学习我们已经知道索引可以帮助我们提高检索的速度，但是过多的索引其实是会降低表的更新速度的，所以我们需要及时将无效的索引删除掉来保证表的更新速度。
  对于已经存在的索引，可以通过DROP语句来删除索引，基本形式如下：

常见问题

1、MySQL中索引、主键和唯一性的区别是什么？
  索引建立在一个或者几个字段上。建立了索引之后，表中的数据就按照索引的一定规则排列，这样可以提高查询速度。
  主键是表中数据的唯一标识，不同记录的主键值不同。在建立主键的时候，系统会自动建立一个唯一性索引。
  唯一性也是建立在表中一个或者几个字段上。其目的是为了对于不同的记录，具有唯一性的字段的值是不同的。
2、表中建立了索引之后，导入大量数据为什么会很慢？
  对已经建立了索引的表中插入数据时，插入一条数据就要对该记录按照索引排序。因此，导入大量数据的时候速度会很慢。解决这种情况的办法是，在没有任何索引的情况下插入数据，然后建立索引