MySQL入门很简单 第七章索引.docx

1、MySQL入门很简单 第七章索引索引：索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等第7章 索引索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。本章主要讲解的内容包括以下几个方面：索引的含义和特点；索引的分类；如何设计索引；如何创建索引；如何删除索

2、引。通过本章的学习，读者可以了解索引的含义、作用，以及索引不同类别。还可以了解用不同的方法创建索引。同时，读者可以了解删除索引的方法。7.1 索引简介索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。本节将详细讲解索引的含义、作用、分类和设计索引的原则。7.1.1 索引的含义和特点索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。本小节将详细讲解索引的含义、作用和优缺点。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表。如果要查库字，

3、如果不使用音序，需要从字典的400页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找。这样就可以大大节省时间。因此，使用索引可以很大程度上提高数据库的查询速度。这样有效的提高了数据库系统的性能。不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH）索引。InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY存储引擎支持HASH索引和BTREE索引，默认为前者。索引有其明

4、显的优势，也有其不可避免的缺点。索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了 。因此，选择使用索引时，需要综合考虑索引的优点和缺点。技巧：索引可以提高查询的速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序

5、。这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据。插入完成后，再创建索引。7.1.2 索引的分类MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。本小节将详细讲解这几种索引的含义和特点。1普通索引在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在student表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。2唯一性索引使用UNIQUE参数可以

6、设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。例如，在student表的stu_name字段中创建唯一性索引，那么stu_name字段的值就必需是唯一的。通过唯一性索引，可以更快速地确定某条记录。主键就是一种特殊唯一性索引。3全文索引使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student表的information字段是TEXT类型，该字段包含了很多的文字信息。在information字段上建立全文索引后，可以提高查询infor

7、mation字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。4单列索引在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段 即可。5多列索引多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和se

8、x字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。6空间索引使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。7.1.3 索引的设计原则为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。1选择唯一性索引唯一性索引的值是唯一的

9、，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。2为经常需要排序、分组和联合操作的字段建立索引经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。3为常作为查询条件的字段建立索引如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。4限制索引的数目索引的数目不是越多越好。每个索引都需要占

10、用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。5尽量使用数据量少的索引如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。6尽量使用前缀来索引如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。7删除不再使用或者很少使用的索引表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库

11、管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。7.2 创建索引创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，这3种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将详细讲解这3种创建索引的方法。7.2.1 创建表的时候创建索引（1）创建表时可以直接创建索引，这种方式最简单、

12、方便。其基本形式如下：1 CREATE TABLE 表名 ( 属性名 数据类型 完整性约束条件, 2 属性名 数据类型 完整性约束条件, 3 . 4 属性名 数据类型 5 UNIQUE | FULLTEXT | SPATIAL INDEX | KEY 6 别名 ( 属性名1 (长度) ASC | DESC ) 7 ); 其中，UNIQUE是可选参数，表示索引为唯一性索引；FULLTEXT是可选参数，表示索引为全文索引；SPATIAL也是可选参数，表示索引为空间索引；INDEX和KEY参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；别名是可选参数，用来给创建的索引取的新名称；属

13、性1参数指定索引对应的字段的名称，该字段必须为前面定义好的字段；长度是可选参数，其指索引的长度，必须是字符串类型才可以使用；ASC和DESC都是可选参数，ASC参数表示升序排列，DESC参数表示降序排列。1创建普通索引创建一个普通索引时，不需要加任何UNIQUE、FULLTEXT或者SPATIAL参数。【示例7-1】 下面创建一个表名为index1的表，在表中的id字段上建立索引。SQL代码如下：8 CREATE TABLE index1 (id INT , 9 name VARCHAR(20) , 10 sex BOOLEAN , 11 INDEX ( id) 12 ); 运行结果显示创建成

14、功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：13 mysql SHOW CREATE TABLE index1 G 14 * 1. row * 15 Table: index1 16 Create Table: CREATE TABLE index1 ( 17 id int(11) DEFAULT NULL, 18 name varchar(20) DEFAULT NULL, 19 sex tinyint(1) DEFAULT NULL, 20 KEY index1_id (id) 21 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 22

15、1 row in set (0.00 sec) 结果可以看到，id字段上已经建立了一个名为index1_id的索引。使用EXPLAIN语句可以查看索引是否被使用，SQL代码如下：23 mysql EXPLAIN SELECT * FROM index1 where id=1 G 24 * 1. row * 25 id: 1 26 select_type: SIMPLE 27 table: index1 28 type: ref 29 possible_keys: index1_id 30 key: index1_id 31 key_len: 5 32 ref: const 33 rows: 1

16、 34 Extra: 35 1 row in set (0.00 sec) 上面结果显示，possible_keys和key处的值都为index1_id。说明index1_id索引已经存在，而且已经开始起作用。2创建唯一性索引创建唯一性索引时，需要使用UNIQUE参数进行约束。【示例7-2】 下面创建一个表名为index2的表，在表中的id字段上建立名为index2_id的唯一性索引，且以升序的形式排列。SQL代码如下：36 CREATE TABLE index2 (id INT UNIQUE , 37 name VARCHAR(20) , 38 UNIQUE INDEX index2_id

17、( id ASC) 39 ); 运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：40 mysql SHOW CREATE TABLE index2 G 41 * 1. row * 42 Table: index2 43 Create Table: CREATE TABLE index2 ( 44 id int(11) DEFAULT NULL, 45 name varchar(20) DEFAULT NULL, 46 UNIQUE KEY id (id), 47 UNIQUE KEY index2_id (id) 48 ) ENGINE=InnoDB D

18、EFAULT CHARSET=utf8 49 1 row in set (0.00 sec) 结果可以看到，id字段上已经建立了一个名为index2_id的唯一性索引。这里的id字段可以没有进行唯一性约束，也可以在该字段上成功创建唯一性索引。但是，这样可能达不到提高查询速度的目的。3创建全文索引全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。而且，现在只有MyISAM存储引擎支持全文索引。【示例7-3】 下面创建一个表名为index3的表，在表中的info字段上建立名为index3_ info的全文索引。SQL代码如下：1 CREATE TABLE index3 (id IN

19、T , 2 info VARCHAR(20) , 3 FULLTEXT INDEX index3_info ( info ) 4 )ENGINE=MyISAM; 运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：5 mysql SHOW CREATE TABLE index3 G 6 * 1. row * 7 Table: index3 8 Create Table: CREATE TABLE index3 ( 9 id int(11) DEFAULT NULL, 10 info varchar(20) DEFAULT NULL, 11 FULLTEXT

20、KEY index3_info (info) 12 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 13 1 row in set (0.00 sec) 结果可以看到，info字段上已经建立了一个名为index3_info的全文索引。如果表的存储引擎不是MyISAM存储引擎，系统会提示ERROR 1214 (HY000): The used table type doesnt support FULLTEXT indexes。注意：目前只有MyISAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引。因此，在创建全文索引时一定注意表的存储引擎的类型。对于经常

21、需要索引的字符串、文字数据等信息，可以考虑存储到MyISAM存储引擎的表中。4创建单列索引单列索引是在表的单个字段上创建索引。【示例7-4】 下面创建一个表名为index4的表，在表中的subject字段上建立名为index4_st的单列索引。SQL代码如下：14 CREATE TABLE index4 (id INT , 15 subject VARCHAR(30) , 16 INDEX index4_st ( subject(10) ) 17 ); 运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：18 mysql SHOW CREATE TABLE

22、index4 G 19 * 1. row * 20 Table: index4 21 Create Table: CREATE TABLE index4 ( 22 id int(11) DEFAULT NULL, 23 subject varchar(30) DEFAULT NULL, 24 KEY index4_st (subject(10) 25 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 26 1 row in set (0.00 sec) 结果可以看到，subject字段上已经建立了一个名为index4_st的单列索引。细心的读者可能会发现，subject

23、字段长度为20，而index4_st索引的长度只有10。这样做的目的还是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，而只查询其前面的若干字符信息。5创建多列索引创建多列索引是在表的多个字段上创建一个索引。【示例7-5】 下面创建一个表名为index5的表，在表中的name和sex字段上建立名为index5_ns的多列索引。SQL代码如下：27 CREATE TABLE index5 (id INT , 28 name VARCHAR(20) , 29 sex CHAR(4) , 30 INDEX index5_ns ( name, sex ) 31 ); 运行结果显示创建成功，使

24、用SHOW CREATE TABLE语句查看表的结构。显示如下：32 mysql SHOW CREATE TABLE index5 G 33 * 1. row * 34 Table: index5 35 Create Table: CREATE TABLE index5 ( 36 id int(11) DEFAULT NULL, 37 name varchar(20) DEFAULT NULL, 38 sex char(4) DEFAULT NULL, 39 KEY index5_ns (name,sex) 40 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 41

25、1 row in set (0.00 sec) 结果可以看到，name和sex字段上已经建立了一个名为index5_ns的单列索引。多列索引中，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。用EXPLAIN语句可以查看索引的使用情况。如果只是有name字段作为查询条件进行查询，显示结果如下：1 mysql EXPLAIN select * from index5 where name=hjh G 2 * 1. row * 3 id: 1 4 select_type: SIMPLE 5 table: index5 6 type: ref 7 possible_keys: index

26、5_ns 8 key: index5_ns 9 key_len: 83 10 ref: const 11 rows: 1 12 Extra: Using index condition 13 1 row in set (0.00 sec) 结果显示，possible_keys和key的值都是index5_ns。额外信息（Extra）显示正在使用索引。这说明使用name字段进行索引时，索引index5_ns已经被使用。如果只使用sex字段作为查询条件进行查询，显示结果如下：14 mysql EXPLAIN select * from index5 where sex=n G 15 * 1. ro

27、w * 16 id: 1 17 select_type: SIMPLE 18 table: index5 19 type: ALL 20 possible_keys: NULL 21 key: NULL 22 key_len: NULL 23 ref: NULL 24 rows: 1 25 Extra: Using where 26 1 row in set (0.00 sec) 此时的结果显示，possible_keys和key的值都为NULL。额外信息（Extra）显示正在使用Where条件查询，而未使用索引。技巧：使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。

28、如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。因此，在优化查询速度时，可以考虑优化多列索引。6创建空间索引创建空间索引时必须使用SPATIAL参数来设置。创建空间索引时，表的存储引擎必须是MyISAM类型。而且，索引字段必须有非空约束。【示例7-6】 下面创建一个表名为index6的表，在表中的space字段上建立名为index6_sp的空间索引。SQL代码如下：27 CREATE TABLE index6 (id INT , 28 space GEOMETRY NOT NULL, 29 SPATIAL INDEX index6_sp (space ) 30 )ENGINE=M

29、yISAM; 运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：31 mysql SHOW CREATE TABLE index6 G 32 * 1. row * 33 Table: index6 34 Create Table: CREATE TABLE index6 ( 35 id int(11) DEFAULT NULL, 36 space geometry NOT NULL, 37 SPATIAL KEY index6_sp (space) 38 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 39 1 row in set (0.00 sec) 结果可以看到，space字段上已经建立了一个名为index6_sp的空间索引。值得注意的是，space字段是非空的，而且数据类型是GEOMETRY类型。这个类型是空间数据类型。空间类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到。7.2.2 在已经存在的表上创建索引