【MySQL】索引

索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

那么我们再来看看

什么是索引

索引是建立在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询速度。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，有效的提高了数据库系统的性能。

实际上，索引也是一种数据结构，目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。

为什么要有索引

索引的作用：

• 快速取数据；
• 保证数据记录的唯一性；
• 实现表与表之间的参照完整性；
• 在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

总的一句话就是：提高查询速度。

那么

索引建立在哪里

索引建立在表上，是对表中一列或者多列值进行排序的一种结构。索引和表中的属性是有很大的关系的，要通过属性排序之后的结果建立结构。索引是存储在文件中的：

• InnoDB：.frm 存储表的结果 .ibd 存储表中的数据和索引。
• MyISAM：.frm 存储表的结果 .myi 存储索引 .myd 存储数据。

索引的种类

• 普通索引：在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身二点完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。
• 唯一性索引：使用unique参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引。可以更快速的确定某条记录。主键就是一种特殊唯一性索引。
• 全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char,varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。MySQL数据库中3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文索引。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。
• 单列索引：在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。
• 多列索引：多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
• 空间索引：使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

索引的使用

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

索引的优缺点

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

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

索引的建立

创建表的时候创建索引：

CREATE TABLE 表名 (
	属性名 数据类型 [完整性约束条件],
	属性名 数据类型 [完整性约束条件],
	......
	属性名 数据类型
	[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [别名] (属性名1 [(长度)] [ASC|DESC])
	);

• 创建普通索引:

CREATE TABLE index1(
	id INT,
	name VARCHAR(20),
	sex BOOLEAN,
	INDEX(id)
	);

• 创建唯一性索引:

CREATE TABLE index2(
	id INT UNIQUE,
	name VARCHAR(20),
	UNIQUE INDEX index2_id(id ASC)
	);

• 创建全文索引:

CREATE TABLE index3(
	id INT,
	info VARCHAR(20),
	FULLTEXT INDEX index3_info(info)
	)ENGINE = MyISAM;

• 创建单列索引:

CREATE TABLE index4(
	id INT,
	subject VARCHAR(30)