mysql创建高性能索引

序言

 

索引

1.创建索引基本语法格

在MySQL中，在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本语法格式如下：

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名(字段名 [(长度)] [ASC | DESC]);

表名：是指需要创建索引的表的名称。该表必须是已经存在的，如果不存在，需要先创建。

UNIQUE：可选。表示索引为唯一性索引。

FULLTEXT：可选。表示索引为全文索引。

SPATIAL：可选。表示索引为空间索引。

INDEX：用来指定字段为索引的。

索引名：给创建的索引起一个新名称。

字段名：指定索引对应的字段的名称。该字段必须为已经定义好的字段。

长度：指定索引的长度。必须是字符串类型才可以使用。

ASC：可选。表示升序排列。

DESC：可选。表示降序排列。

2.MySQL创建普通索引

使用ALTER TABLE语句创建index7_name索引。SQL代码如下：

ALTER TABLE index7 ADD INDEX index7_name(name(20))

3.MySQL创建唯一性索引

在index13表中的course_id字段上，建立名为index13_id的唯一性索引。SQL代码如下：

ALTER TABLE index13 ADD UNIQUE INDEX index13_id(course_id);

4.MySQL创建全文索引

在index14表中的info字段上建立名为index14_info的全文索引。SQL代码如下：

ALTER TABLE index14 ADD FULLTEXT INDEX index14_info(info);

5.MySQL创建单列索引

在index15表中的address字段上建立名为index15_addr的单列索引。address字段的数据类型为VARCHAR(20)，索引的数据类型为CHAR(4)。SQL代码如下：

ALTER TABLE index15 ADD INDEX index15_addr(address(4));

6.MySQL创建多列索引

在index16表中的name和address字段上建立名为index16_na的多列索引。SQL代码如下：

ALTER TABLE index16 ADD INDEX index16_na(name,address);

7.MySQL创建空间索引

ALTER TABLE index17 ADD SPATIAL INDEX index17_line(line);

其中，SPATIAL用于设置索引为空间索引；表index17的存储引擎必须是MyISAM类型；line字段必须是非空的，而且必须是空间数据类型。

8.查看索引

DESC category
SHOW KEYS FROM category
SHOW INDEX FROM category
SHOW INDEXES FROM category

什么是索引

　　索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。

　　在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足

条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

　　索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识值的数据页的逻辑指针清单。

索引的优点

　　1、大大的加快了数据的检索速度；

　　2、创建唯一索引，保证数据库表中每条数据的唯一性；

　　3、加快表与表之间的连接；

　　4、使用排序和分组检索数据时，可以显著的加快排序和分组的时间；

索引的缺点

　　1、索引需要占物理空间；

　　2、当对表中的数据进行增删改是，索引也要动态维护，降低了数据库的维护性；

　　3、在创建索引时，必须要确定要创建的索引列和索引类型；

索引类型

索引的类型

　　普通索引

　　这是最基本的索引，而且没有唯一性之类的限制；

--创建
 CREATE INDEX [IndexName] ON [TableName]([ColumnName],[ColumnName]...)
--删除
DROP INDEX [TableName].[IndexName]

　　唯一索引

　　唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

--创建
CREATE UNIQUE INDEX [IndexName] ON [TableName]([ColumnName],[ColumnName],...)
--删除
DROP INDEX [TableName].[IndexName]

　　主键索引

　　数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。建议建立唯一索引时，直接创建主键索引就好了。

　　聚集索引

　　该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引类似于电话簿，按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

--创建
CREATE CLUSTERED INDEX [IndexName] ON [TableName]([ColumnName],[ColumnName],...)
--删除
DROP INDEX [TableName].[IndexName]

　　聚集索引使用注意事项

　　(1) 定义聚集索引键时使用的列越少越好；

　　(2) 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=,可以按物理顺序更快的返回一个范围;

　　(3) 被连续访问的列;

　　(4) 返回大型结果集的查询；

　　(5) 经常被使用表联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能;

　　(6) OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引;

　　聚集索引不适用于

　　(1) 频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值，修改了一行数据，改行物流顺序改变了，其他行的物流顺序也要跟着改变）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的;

　　(2) 宽键。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内;

　　非聚集索引

　　数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。

　　

--创建
CREATE NONCLUSTERED INDEX [IndexName] ON [TableName]([ColumnName],[ColumnName]...)
--删除
DROP INDEX [TableName].[IndexName]

聚集索引和非聚集索引的区别

    其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

　 如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。

但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

索引适用场景

1. 较频繁的作为查询条件的字段应该创建索引

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

3. 更新非常频繁的字段不适合创建索引

4. 不会出现在 WHERE 子句中的字段不该创建索引

5. 单键索引还是组合索引

小结

聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描，在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询，这就使让ID号这个主键作为聚集索引成为一种资源浪费 。