MySQL入门很简单 第七章索引.docx
《MySQL入门很简单 第七章索引.docx》由会员分享,可在线阅读,更多相关《MySQL入门很简单 第七章索引.docx(27页珍藏版)》请在冰豆网上搜索。
MySQL入门很简单第七章索引
索引:
索引是一种特殊的数据库结构,可以用来快速查询数据库表中的特定记录。
索引是提高数据库性能的重要方式。
MySQL中,所有的数据类型都可以被索引。
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等
第7章索引
索引是一种特殊的数据库结构,可以用来快速查询数据库表中的特定记录。
索引是提高数据库性能的重要方式。
MySQL中,所有的数据类型都可以被索引。
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
本章主要讲解的内容包括以下几个方面:
索引的含义和特点;
索引的分类;
如何设计索引;
如何创建索引;
如何删除索引。
通过本章的学习,读者可以了解索引的含义、作用,以及索引不同类别。
还可以了解用不同的方法创建索引。
同时,读者可以了解删除索引的方法。
7.1索引简介
索引由数据库表中一列或多列组合而成,其作用是提高对表中数据的查询速度。
本节将详细讲解索引的含义、作用、分类和设计索引的原则。
7.1.1索引的含义和特点
索引是创建在表上的,是对数据库表中一列或多列的值进行排序的一种结构。
索引可以提高查询的速度。
本小节将详细讲解索引的含义、作用和优缺点。
通过索引,查询数据时可以不必读完记录的所有信息,而只是查询索引列。
否则,数据库系统将读取每条记录的所有信息进行匹配。
例如,索引相当于新华字典的音序表。
如果要查"库"字,如果不使用音序,需要从字典的400页中逐页来找。
但是,如果提取拼音出来,构成音序表,就只需要从10多页的音序表中直接查找。
这样就可以大大节省时间。
因此,使用索引可以很大程度上提高数据库的查询速度。
这样有效的提高了数据库系统的性能。
不同的存储引擎定义了每个表的最大索引数和最大索引长度。
所有存储引擎对每个表至少支持16个索引,总索引长度至少为256字节。
有些存储引擎支持更多的索引数和更大的索引长度。
索引有两种存储类型,包括B型树(BTREE)索引和哈希(HASH)索引。
InnoDB和MyISAM存储引擎支持BTREE索引,MEMORY存储引擎支持HASH索引和BTREE索引,默认为前者。
索引有其明显的优势,也有其不可避免的缺点。
索引的优点是可以提高检索数据的速度,这是创建索引的最主要的原因;对于有依赖关系的子表和父表之间的联合查询时,可以提高查询速度;使用分组和排序子句进行数据查询时,同样可以显著节省查询中分组和排序的时间。
索引的缺点是创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间,每一个索引要占一定的物理空间;增加、删除和修改数据时,要动态的维护索引,造成数据的维护速度降低了。
因此,选择使用索引时,需要综合考虑索引的优点和缺点。
技巧:
索引可以提高查询的速度,但是会影响插入记录的速度。
因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序。
这样就降低了插入记录的速度,插入大量记录时的速度影响更加明显。
这种情况下,最好的办法是先删除表中的索引,然后插入数据。
插入完成后,再创建索引。
7.1.2索引的分类
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
本小节将详细讲解这几种索引的含义和特点。
1.普通索引
在创建普通索引时,不附加任何限制条件。
这类索引可以创建在任何数据类型中,其值是否唯一和非空由字段本身的完整性约束条件决定。
建立索引以后,查询时可以通过索引进行查询。
例如,在student表的stu_id字段上建立一个普通索引。
查询记录时,就可以根据该索引进行查询。
2.唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引。
在创建唯一性索引时,限制该索引的值必须是唯一的。
例如,在student表的stu_name字段中创建唯一性索引,那么stu_name字段的值就必需是唯一的。
通过唯一性索引,可以更快速地确定某条记录。
主键就是一种特殊唯一性索引。
3.全文索引
使用FULLTEXT参数可以设置索引为全文索引。
全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。
查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。
例如,student表的information字段是TEXT类型,该字段包含了很多的文字信息。
在information字段上建立全文索引后,可以提高查询information字段的速度。
MySQL数据库从3.23.23版开始支持全文索引,但只有MyISAM存储引擎支持全文检索。
在默认情况下,全文索引的搜索执行方式不区分大小写。
但索引的列使用二进制排序后,可以执行区分大小写的全文索引。
4.单列索引
在表中的单个字段上创建索引。
单列索引只根据该字段进行索引。
单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。
只要保证该索引只对应一个字段即可。
5.多列索引
多列索引是在表的多个字段上创建一个索引。
该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。
但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。
例如,在表中的id、name和sex字段上建立一个多列索引,那么,只有查询条件使用了id字段时该索引才会被使用。
6.空间索引
使用SPATIAL参数可以设置索引为空间索引。
空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。
MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。
目前只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值。
对于初学者来说,这类索引很少会用到。
7.1.3索引的设计原则
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。
本小节将向读者介绍一些索引的设计原则。
1.选择唯一性索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。
为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话,可能存在同名现象,从而降低查询速度。
2.为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDERBY、GROUPBY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。
如果为其建立索引,可以有效地避免排序操作。
3.为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。
因此,为这样的字段建立索引,可以提高整个表的查询速度。
4.限制索引的数目
索引的数目不是越多越好。
每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
修改表时,对索引的重构和更新很麻烦。
越多的索引,会使更新表变得很浪费时间。
5.尽量使用数据量少的索引
如果索引的值很长,那么查询的速度会受到影响。
例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6.尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。
例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。
如果只检索字段的前面的若干个字符,这样可以提高检索速度。
7.删除不再使用或者很少使用的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。
数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
注意:
选择索引的最终目的是为了使查询的速度变快。
上面给出的原则是最基本的准则,但不能拘泥于上面的准则。
读者要在以后的学习和工作中进行不断的实践。
根据应用的实际情况进行分析和判断,选择最合适的索引方式。
7.2创建索引
创建索引是指在某个表的一列或多列上建立一个索引,以便提高对表的访问速度。
创建索引有3种方式,这3种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTERTABLE语句来创建索引。
本节将详细讲解这3种创建索引的方法。
7.2.1创建表的时候创建索引
(1)
创建表时可以直接创建索引,这种方式最简单、方便。
其基本形式如下:
1CREATETABLE表名(属性名数据类型[完整性约束条件],
2属性名数据类型[完整性约束条件],
3......
4属性名数据类型
5[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY
6[别名](属性名1[(长度)][ASC|DESC])
7);
其中,UNIQUE是可选参数,表示索引为唯一性索引;FULLTEXT是可选参数,表示索引为全文索引;SPATIAL也是可选参数,表示索引为空间索引;INDEX和KEY参数用来指定字段为索引的,两者选择其中之一就可以了,作用是一样的;"别名"是可选参数,用来给创建的索引取的新名称;"属性1"参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;"长度"是可选参数,其指索引的长度,必须是字符串类型才可以使用;"ASC"和"DESC"都是可选参数,"ASC"参数表示升序排列,"DESC"参数表示降序排列。
1.创建普通索引
创建一个普通索引时,不需要加任何UNIQUE、FULLTEXT或者SPATIAL参数。
【示例7-1】下面创建一个表名为index1的表,在表中的id字段上建立索引。
SQL代码如下:
8CREATETABLEindex1(idINT,
9nameVARCHAR(20),
10sexBOOLEAN,
11INDEX(id)
12);
运行结果显示创建成功,使用SHOWCREATETABLE语句查看表的结构。
显示如下:
13mysql>SHOWCREATETABLEindex1\G
14***************************1.row***************************
15Table:
index1
16CreateTable:
CREATETABLE`index1`(
17`id`int(11)DEFAULTNULL,
18`name`varchar(20)DEFAULTNULL,
19`sex`tinyint
(1)DEFAULTNULL,
20KEY`index1_id`(`id`)
21)ENGINE=InnoDBDEFAULTCHARSET=utf8
221rowinset(0.00sec)
结果可以看到,id字段上已经建立了一个名为index1_id的索引。
使用EXPLAIN语句可以查看索引是否被使用,SQL代码如下:
23mysql>EXPLAINSELECT*FROMindex1whereid=1\G
24***************************1.row***************************
25id:
1
26select_type:
SIMPLE
27table:
index1
28type:
ref
29possible_keys:
index1_id
30key:
index1_id
31key_len:
5
32ref:
const
33rows:
1
34Extra:
351rowinset(0.00sec)
上面结果显示,possible_keys和key处的值都为index1_id。
说明index1_id索引已经存在,而且已经开始起作用。
2.创建唯一性索引
创建唯一性索引时,需要使用UNIQUE参数进行约束。
【示例7-2】下面创建一个表名为index2的表,在表中的id字段上建立名为index2_id的唯一性索引,且以升序的形式排列。
SQL代码如下:
36CREATETABLEindex2(idINTUNIQUE,
37nameVARCHAR(20),
38UNIQUEINDEXindex2_id(idASC)
39);
运行结果显示创建成功,使用SHOWCREATETABLE语句查看表的结构。
显示如下:
40mysql>SHOWCREATETABLEindex2\G
41***************************1.row***************************
42Table:
index2
43CreateTable:
CREATETABLE`index2`(
44`id`int(11)DEFAULTNULL,
45`name`varchar(20)DEFAULTNULL,
46UNIQUEKEY`id`(`id`),
47UNIQUEKEY`index2_id`(`id`)
48)ENGINE=InnoDBDEFAULTCHARSET=utf8
491rowinset(0.00sec)
结果可以看到,id字段上已经建立了一个名为index2_id的唯一性索引。
这里的id字段可以没有进行唯一性约束,也可以在该字段上成功创建唯一性索引。
但是,这样可能达不到提高查询速度的目的。
3.创建全文索引
全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。
而且,现在只有MyISAM存储引擎支持全文索引。
【示例7-3】下面创建一个表名为index3的表,在表中的info字段上建立名为index3_info的全文索引。
SQL代码如下:
1CREATETABLEindex3(idINT,
2infoVARCHAR(20),
3FULLTEXTINDEXindex3_info(info)
4)ENGINE=MyISAM;
运行结果显示创建成功,使用SHOWCREATETABLE语句查看表的结构。
显示如下:
5mysql>SHOWCREATETABLEindex3\G
6***************************1.row***************************
7Table:
index3
8CreateTable:
CREATETABLE`index3`(
9`id`int(11)DEFAULTNULL,
10`info`varchar(20)DEFAULTNULL,
11FULLTEXTKEY`index3_info`(`info`)
12)ENGINE=MyISAMDEFAULTCHARSET=utf8
131rowinset(0.00sec)
结果可以看到,info字段上已经建立了一个名为index3_info的全文索引。
如果表的存储引擎不是MyISAM存储引擎,系统会提示"ERROR1214(HY000):
Theusedtabletypedoesn'tsupportFULLTEXTindexes"。
注意:
目前只有MyISAM存储引擎支持全文索引,InnoDB存储引擎还不支持全文索引。
因此,在创建全文索引时一定注意表的存储引擎的类型。
对于经常需要索引的字符串、文字数据等信息,可以考虑存储到MyISAM存储引擎的表中。
4.创建单列索引
单列索引是在表的单个字段上创建索引。
【示例7-4】下面创建一个表名为index4的表,在表中的subject字段上建立名为index4_st的单列索引。
SQL代码如下:
14CREATETABLEindex4(idINT,
15subjectVARCHAR(30),
16INDEXindex4_st(subject(10))
17);
运行结果显示创建成功,使用SHOWCREATETABLE语句查看表的结构。
显示如下:
18mysql>SHOWCREATETABLEindex4\G
19***************************1.row***************************
20Table:
index4
21CreateTable:
CREATETABLE`index4`(
22`id`int(11)DEFAULTNULL,
23`subject`varchar(30)DEFAULTNULL,
24KEY`index4_st`(`subject`(10))
25)ENGINE=InnoDBDEFAULTCHARSET=utf8
261rowinset(0.00sec)
结果可以看到,subject字段上已经建立了一个名为index4_st的单列索引。
细心的读者可能会发现,subject字段长度为20,而index4_st索引的长度只有10。
这样做的目的还是为了提高查询速度。
对于字符型的数据,可以不用查询全部信息,而只查询其前面的若干字符信息。
5.创建多列索引
创建多列索引是在表的多个字段上创建一个索引。
【示例7-5】下面创建一个表名为index5的表,在表中的name和sex字段上建立名为index5_ns的多列索引。
SQL代码如下:
27CREATETABLEindex5(idINT,
28nameVARCHAR(20),
29sexCHAR(4),
30INDEXindex5_ns(name,sex)
31);
运行结果显示创建成功,使用SHOWCREATETABLE语句查看表的结构。
显示如下:
32mysql>SHOWCREATETABLEindex5\G
33***************************1.row***************************
34Table:
index5
35CreateTable:
CREATETABLE`index5`(
36`id`int(11)DEFAULTNULL,
37`name`varchar(20)DEFAULTNULL,
38`sex`char(4)DEFAULTNULL,
39KEY`index5_ns`(`name`,`sex`)
40)ENGINE=InnoDBDEFAULTCHARSET=utf8
411rowinset(0.00sec)
结果可以看到,name和sex字段上已经建立了一个名为index5_ns的单列索引。
多列索引中,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。
用EXPLAIN语句可以查看索引的使用情况。
如果只是有name字段作为查询条件进行查询,显示结果如下:
1mysql>EXPLAINselect*fromindex5wherename='hjh'\G
2***************************1.row***************************
3id:
1
4select_type:
SIMPLE
5table:
index5
6type:
ref
7possible_keys:
index5_ns
8key:
index5_ns
9key_len:
83
10ref:
const
11rows:
1
12Extra:
Usingindexcondition
131rowinset(0.00sec)
结果显示,possible_keys和key的值都是index5_ns。
额外信息(Extra)显示正在使用索引。
这说明使用name字段进行索引时,索引index5_ns已经被使用。
如果只使用sex字段作为查询条件进行查询,显示结果如下:
14mysql>EXPLAINselect*fromindex5wheresex='n'\G
15***************************1.row***************************
16id:
1
17select_type:
SIMPLE
18table:
index5
19type:
ALL
20possible_keys:
NULL
21key:
NULL
22key_len:
NULL
23ref:
NULL
24rows:
1
25Extra:
Usingwhere
261rowinset(0.00sec)
此时的结果显示,possible_keys和key的值都为NULL。
额外信息(Extra)显示正在使用Where条件查询,而未使用索引。
技巧:
使用多列索引时一定要特别注意,只有使用了索引中的第一个字段时才会触发索引。
如果没有使用索引中的第一个字段,那么这个多列索引就不会起作用。
因此,在优化查询速度时,可以考虑优化多列索引。
6.创建空间索引
创建空间索引时必须使用SPATIAL参数来设置。
创建空间索引时,表的存储引擎必须是MyISAM类型。
而且,索引字段必须有非空约束。
【示例7-6】下面创建一个表名为index6的表,在表中的space字段上建立名为index6_sp的空间索引。
SQL代码如下:
27CREATETABLEindex6(idINT,
28spaceGEOMETRYNOTNULL,
29SPATIALINDEXindex6_sp(space)
30)ENGINE=MyISAM;
运行结果显示创建成功,使用SHOWCREATETABLE语句查看表的结构。
显示如下:
31mysql>SHOWCREATETABLEindex6\G
32***************************1.row***************************
33Table:
index6
34CreateTable:
CREATETABLE`index6`(
35`id`int(11)DEFAULTNULL,
36`space`geometryNOTNULL,
37SPATIALKEY`index6_sp`(`space`)
38)ENGINE=MyISAMDEFAULTCHARSET=utf8
391rowinset(0.00sec)
结果可以看到,space字段上已经建立了一个名为index6_sp的空间索引。
值得注意的是,space字段是非空的,而且数据类型是GEOMETRY类型。
这个类型是空间数据类型。
空间类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。
这些空间数据类型平时很少用到。
7.2.2在已经存在的表上创建索引