您当前的位置: 站长圈 > 技术文章 > 数据库技术 > MySQL数据库——索引

MySQL数据库——索引

来源:站长圈 作者:adminzzq 点击: 0
一、索引简介
索引是对数据库表中一列或多列的值进行排序的一种结构。使用索引可提高数据库中特定数据的查询速度。
索引是在存储引擎中实现的,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储引擎类型有两种:BTREE和HASH,具体和表的存储引擎相关:MyISAM和InnoDB存储引擎只支持BTREE索引;MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
      索引的优点:
1)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。
2)可以大大加速数据的查询速度,这也是创建索引的最主要原因。
3)在实现数据的参考完整性方面,可以加速表和表之间的连接。
4)在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。
事物都一分为二的,增加索引也有许多不得之处,主要有:
1)创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
2)索引需要占磁盘空间,除了数据表占据空间外,每一个索引还要占一定的物理空间。
3)当对表中的数据进行增加、删除和修改时,索引也要动态的维护,这就降低了数据的维护速度。
索引的分类:
MySQL的索引可以分为以下几类:
1、普通索引和唯一索引
普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。
唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一,主键索引是一种特殊的唯一索引,不允许有空值。
2、单列索引和组合索引
单列索引是一个索引只包含单个列,一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段在左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。
3、全文索引
全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
4、空间索引
空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型的4种:GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISAM的表中创建。
 
二、创建索引
MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列,使用ALTER TABLE语句在已有的表上创建索引或者使用CREATE INDEX语句在已存在的表上添加索引。
1、创建表的时候创建索引
使用CREATE TABLE创建表时,在定义约束的同时相当于在指定的列上创建了一个索引。
创建表时创建索引的基本语法格式如下:
CREATE TABLE table_name(col_name data_type)
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]
UNIQUE、FULLTEXT和SPATIAL为可选参数,分别指唯一索引、全文索引和空间索引;INDEX和KEY两者作用相同,用来指定创建索引:col_name为需要创建索引的字段名,该列必须从数据表中定义的多个列中选择;index_name指定索引的名称,为可选参数,如果不指定,MySQL默认col_name为索引值;length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;ASC或DESC指定升序或者降序的索引值存储。
1)创建普通索引
最基本的索引类型,没有唯一性之类的限制,其作用只是加快对数据的访问速度。
例:在book表中的year_publication字段上建立普通索引,输入SQL语句如下:
\
执行后,查看表的信息可以看到已经使用了索引:
\
在MySQL控制台,使用show create table book\G;也可以在查看表的结构中看到已经建立了索引:
\
使用EXPLAIN语句查看索引是否正在使用:
\
EXPLAIN语句输出结果的各个行解释如下:
①select_type行指定所使用的SELECT查询类型,这里值为SIMPLE表示简单的SELECT,不使用UNION或子查询。其他可能的取值有:PRIMARY、UNION、SUBQUERY等。
②table行指定数据库读取的数据表名称,它们按被读取的先后顺序排列。
③type行指定了本数据表与其他数据表之间的关联关系,可能的取值有:system、const、eq_ref、ref、range、index和All。
④possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
⑤key行是MySQL实际选用的索引。
⑥key_len行给出索引按字节计算的长度,key_len数值越小,表示越快。
⑦ref行给出了关联关系中另一个数据表里的数据列的名称。
⑧rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
⑨extra行提供了与关联操作有关的信息。
可以看到possible_keys和key的值都为year_publication,查询时使用了索引。
2)创建唯一索引
创建索引的主要原因是减少查询索引列操作的执行时间,尤其是对比较庞大的数据库。唯一索引的列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
例:创建一个表t1,在表中的id字段上使用UNIQUE关键字创建唯一索引,输入SQL语句如下:
\
执行后,使用show create table t1;查看结构:
\
\
在PHPMyAdmin还可以通过点开“索引”前的“+”查看:
\
可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引。
3)创建单列索引
单列索引是在数据表中的某个字段上创建的索引,一个表中可以创建多个单列索引。
例:创建一个表t2,在表中的name字段上使用创建单列索引,输入SQL语句如下:
\
执行后,使用show create table t2;查看结构:
\
4)创建组合索引
组合索引是在多个字段上创建一个索引
例:创建表t3,在表中的id和info字段上建立组合索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”前的“+”,可以看到:
\
提示:组合索引可起几个索引的作用。但是使用时并不是随便查询哪个字段都可以使用索引,而是遵从“最左前缀”:利用索引中最左边的列集来匹配行,这样的列集称为最左前缀。如前面的例子里由id、name和info3个字段构成的索引,索引行中按id/name/info的顺序存放,索引可以搜索下面字段组合:(id,name,info)、(id,name)或者id。如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如(info)或者(name,info)组合则不能使用索引查询。
例:在t3表中查询id和name字段,使用EXPLAIN语句查看索引的使用情况:
\
可以看到,查询id和name字段时,使用了名称MultiIdx的索引。如果查询(name,info)组合或者单独查询name或info,其结果如下:
\
此时的查询中并没有使用所创建的索引进行查询。
5)全文索引
FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。
例:创建表t4,在表中的info字段上建立全文索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
可以看到在info字段上已经建立了一个名为FullTxtIdx的FULLTEXT索引。
全文索引非常适合大型数据集,对于小的数据集,它的用处可能比较小。
6)创建空间索引
空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
例:创建表t5,在空间类型为GEOMETRY的字段上创建空间索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
可以看到,t5表的g字段上创建了名为spatIdx的空间索引。
注意:创建时指定空间类型字段值的非空约束,并且表的存储引擎为MyISAM。
2、在已经存在的表上创建索引
在已经在存的表中创建索引,可以使用ALTER TABLE 语句或者CREATE INDEX语句。
1)使用ALTER TABLE语句创建索引
ALTER TABLE创建索引的基本语法如下:
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY] [index_name](col_name[length,…]) [ASC|DESC]
与创建表时创建索引的语法不同的是:在这里使用ALTER TABLE和ADD关键字,ADD表示向表中添加索引。
例:在book表中的bookname字段上建立名为BkNameIdx的普通索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
也可以用SHOW INDEX在MySQL控制台查看表的索引:
\
其中各个主要参数的含义为:
①Table表示创建索引的表
②Non_unique表示索引非唯一,1代表是非唯一索引,0代表唯一索引。
③Key_name表示索引名称
④Seq_in_index表示该字段在索引中的位置,单列索引该值为1,组合索引为每个字段在索引定义中的顺序。
⑤Column_name表示定义索引的列字段
⑥Sub_part表示索引的长度
⑦Null表示该字段是否能为空值
⑧Index_type表示索引类型
可以看到有2个索引。
例:在book表的bookId字段上建立名称为UniqidIdx 的唯一索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
用SHOW INDEX在MySQL控制台查看表的索引:
\
可以看到Non_unique属性值为0,表示名称为UniqidIdx的索引为唯一索引。
例:在book表的comment字段上建立单列索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
用SHOW INDEX在MySQL控制台查看表的索引:
\
可以看到Sub_part的属性值为50,在查询时只需要检索前50个字节。
例:在book表的authors和info字段上建立组合索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
用SHOW INDEX在MySQL控制台查看表的索引:
\
可以看到名称为BkAuAndInfoIdx的索引由2个字段组成,authors字段长度为30,在组合索引中的序号为1,该字段不允许为空值NULL;info字段长度为50,在组合索引中的序号为2,该字段可以为空值NULL。
例:创建表t6,在t6表上使用ALTER TABLE创建全文索引。
首先创建表t6,注意修改ENGINE参数为MyISAM,MySQL默认引擎InnoDB不支持全文索引,输入SQL语句如下:
\
使用ALTER TABLE语句在info字段上创建全文索引,输入SQL语句如下:
\
执行后,查看表结构,点开“索引”:
\
例:创建表t7,输入SQL语句如下:
\
在t7的空间数据类型字段g上创建名称为spatIdx的空间索引,
\
执行后,查看表结构,点开“索引”:
\
 
2)使用CREATE INDEX语句创建索引
 
待续