索引

索引

索引是数据库技术的一个重要概念与技术,也是MySQL的一个数据库对象。对于任何DBMS,索引都是查询优化的最主要方式。当数据量非常大的时候,若没有合适的索引,数据库的查询性能会急剧下降。因此,建立索引的目的就是加快数据库检索的速度。

索引概述

对数据库中数据表进行查询操作时,系统对表中的数据主要有两种搜索扫描方式:一种是全表扫描、检索,另一种是利用数据表上建立的索引进行扫描。

全表扫描是将表中的所有数据记录从头至尾逐行读取,与查询结果进行对比,返回满足条件的记录。这种搜索方式需要读取相关表中的所有数据,需要进行大量的磁盘读写操作,当表中的数据量巨大的时候,查询效率会非常低。

索引访问这是通过搜索索引值,再根据索引值与记录的关系直接访问数据表中的记录行。例如,对学生表的姓名字段建立索引,即按照表中的姓名字段的数据进行索引,并为其建立指向数据表中记录所在位置的指针。

通过索引进行数据检索的方式类似与在图书馆查找图书的过程,即在图书馆首先通过检索书名或书号等信息,获得所需图书的位置信息,然后按照该位置信息直接到确定的书库、书架上拿取图书,而不是对图书馆的所有书籍进行逐库、逐书架的查找。因此,在数据库中建立适当的索引能有效提高检索的效率。

根据用途,MySQL中的索引主要分为普通索引、唯一性索引、主键索引、聚簇索引及全文索引几类。

  1. 普通索引
    普通索引(index)是最基本的索引类型。普通的索引列值可以取空值或重复值。创建普通索引时,通常使用的关键字是index或key。

  2. 唯一性索引
    唯一性索引(unique)与普通索引基本相同,区别仅是索引列值不能重复,但可以为空值。创建唯一性索引所使用的关键字是unique。

  3. 主键索引
    在MySQL中建立主键时,系统会自动创建主键索引。主键索引是一种唯一性索引。与唯一性索引不同在于其索引值不能为空。创建主键时,必须使用关键字primary key。一般是在创建表的时候指定主键,也可以通过修改表的方式创建主键索引。每个表只能有一个主键。

  4. 聚簇索引
    聚簇索引的索引顺序就是数据存储的物理存储数据,这样能保证索引值相近的元组所存储的物理位置也相近。对于非聚簇索引,索引顺序与数据的物理排列顺序无关。一个表只能又一个聚簇索引。并非所有的MySQL存储引擎都支持聚簇索引,目前只有solidDB和InnoDB支持。

  5. 全文索引(fulltext)
    全文索引只能创建在数据类型为varchar或text的列上。建立全文索引后,能够在建立了全文索引的列上进行全文查找。全文索引只能在MyISAM存储引擎的表中创建。

在实际使用中,索引可以建立在单一列上,称为单列索引。也可以建立在多个列上,称为组合索引。

  1. 单列索引
    单列索引就是一个索引只包含原表中的一个列。一个表上可以建立多个单列索引。例如,建立在学生表中关于学号的索引,还可以建立关于姓名的索引、所在班级的索引等。

  2. 组合索引
    组合索引也称为复合索引或者多列索引。组合索引是指在表的多个列上创建一个索引。例如,在班级表tb_class的“所属院系”和“年级”两列上建立一个索引,即为组合索引。这个索引的含义是先按所属院系排序,若所在院系相同,则按照年级排序。这就是左前缀法则。所谓的左前缀法则就是指先按照第一列(顺序排列在最左侧的字段)进行排序,当第一列的值相同的情况下再对第二列排序,依此类推。

查看数据表上所建立的索引

在MySQL中,使用show index语句能够查看数据表中是否建立了索引,以及所建立索引的类型及相关参数,其语法格式为:

1
show {index | indexs | keys} {from | in} tb_name [{from | in}] db_name;

该语句的功能是显示出表名为tbl_name的表上所有定义的索引名及索引类型。例如:

1
show index from db_school.tb_score;

上面的结果会是一个很长的表,如果是在终端运行,可以使用\G参数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
show index from db_school.tb_score \G;
-- 结果
*************************** 1. row ***************************
Table: tb_score
Non_unique: 1
Key_name: FK_score1
Seq_in_index: 1
Column_name: studentNo
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
*************************** 2. row ***************************
Table: tb_score
Non_unique: 1
Key_name: FK_score2
Seq_in_index: 1
Column_name: courseNo
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
2 rows in set (0.00 sec)

  1. Table:索引所在表的名称;
  2. Non_unique:索引是否为唯一性索引,如果为唯一性索引,该值为0,如果不是,该值为1。
  3. Key_name:索引的名称;
  4. Column_name:建立索引的列名称;
  5. Collation:说明以何种顺序(升序或降序)索引。如果是升序,取A,如果显示为NULL,表示无分类。

创建索引

MySQL提供了三种创建索引的方法。一种是在创建表的同时创建索引,另外两种分别是在已存在的表上使用create index语句创建索引,或使用alter table语句添加索引。

使用create table语句创建索引

语法格式为:

1
2
3
create table tbl_name [col_name data_type]
[constraint index_name] [unique] [index | key]
[index_name] (index_col_name[length]) [asc | desc]

语法说明如下:

  1. tbl_name:指定需要建立索引的表名。
  2. index_name:指定所建立的索引名称。一个表中可以建立多个索引,而每个索引的名称必须是唯一的。
  3. unique:可选项,指定所创建的是唯一性索引。
  4. index_col_name:指定要创建索引的列名。通常可考虑将查询语句在where子句和join子句里出现的列作为索引列。
  5. length:可选项,用于指定使用列的前length个字符创建索引。使用列值的一部分创建索引有利于减小索引文件的大小,节省磁盘空间。当然,在某些情况下,只能对列的前缀进行索引。例如,由于索引列的长度有一个最大上限,所以如果索引列的长度超过的超过了这个上限,那么此时就需要利用前缀进行索引。另外,为blob或text类型的数据列建立索引时必须使用前缀索引,前缀最长为255字节,但对于MyISAM和InnoDB表,前缀最长为1000字节。
  6. asc | desc:可选项,指定索引是按升序还是降序排列,默认为升序。

例如,创建新表的同时创建普通索引:

1
2
3
4
5
6
7
8
9
10
11
create table tb_student1
(
studentNo char(10) not null,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
index(studentName)
);

创建新表时,建立唯一性索引:

1
2
3
4
5
6
7
8
9
10
create table tb_student2
(
studentNo char(10) not null unique,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
)

创建新表的同时建立主键索引:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
create table tb_score1
(
studentNo char(10),
courseNo char(5),
score float,
constraint PK_score primary key (studentNo, courseNo),
constraint FK_score11 foreign key (studentNo) references tb_student(studentNo),
constraint FK_score12 foreign key (courseNo) references tb_course(courseNo)
);
-- 查看索引(在终端运行)
mysql> show index from db_school.tb_score1\G;
*************************** 1. row ***************************
Table: tb_score1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: studentNo
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
*************************** 2. row ***************************
Table: tb_score1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: courseNo
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
*************************** 3. row ***************************
Table: tb_score1
Non_unique: 1
Key_name: FK_score12
Seq_in_index: 1
Column_name: courseNo
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
3 rows in set (0.02 sec)

凡是被定义为主键或外键的序列,系统俊晖自动建立相应字段的索引。
需要提醒的是,并非所有的数据库管理系统都会自动对主键、外键建立索引。在使用其他数据库管理系统的时候,应该查看手册或测试以下。

使用create index语句创建索引

用create index语句能够在一个已存在的表上创建索引,其语法格式是:

1
2
create [unique] index index_name
on tbl_name (col_name [(length) ] [asc | desc], ...);

创建普通索引:

1
2
3
create index index_stu on db_school.tb_student(studentNo);
# 查看
show index from db_school.tb_student;

创建基于字段值前缀字符的索引:

1
2
3
# 在数据库db_school中课程表tb_score上建立一个索引,
# 要求按照课程名称courseName字段值前三个字符建立降序索引
create index index_course on db_school.tb_course(courseName(3) desc);

对于字符类型的排序,若是英文,按照字母序排序;若是中文,不同的系统有不同的处理规则。MySQL是按照汉语拼音对应的英文字母顺序进行排序的。

创建组合索引:

1
2
3
4
5
6
7
8
# 在数据库db_school中表tb_book上建立图书类别(升序)和书名(降序)的组合索引,索引名称为index_book
create table db_school.tb_book
(
bclassNo char(10) unique not null,
bookName varchar(20) not null
);
create index index_book on db_school.tb_book(bclassNo, bookname desc);
show index from db_school.tb_book;

使用alter table语句创建索引

在MySQL中,除了使用create index语句创建一个已存在的表上创建索引之外,还可以使用alter table语句实现类似的功能。语法格式如下:

1
2
alter table tbl_name add [unique | fulltext]
[index | key] [index_name] (col_name [length] [asc | desc], ...);

使用alter table创建普通索引

1
2
alter table db_school.tb_student1
add index idx_studentName(studentName);

除了使用alter table语句的add index建立索引,使用add constraint子句能够在表中添加主键或外键约束,与此同时,系统会自动创建相应的索引。

删除索引

在MySQL中,使用drop index语句或alter table语句能够删除一个不再需要的索引。

  1. 使用drop index语句删除索引
    例如:

    1
    drop index idx_studentName on db_school.tb_student1;
  2. 使用alter table语句删除索引
    例如:

    1
    alter table db_school.tb_student drop index index_stu;

使用alter table语句的drop constraint 子句能够在表中删除主键或外键约束,同时也就删除了相应于主键、外键的索引。

注意

  1. 若删除表中的某一列,而该列是索引项,则该列也会从索引中被删除;
  2. 如果组成索引的所有列都被删除,则整个索引将会被删除。

对索引的进一步说明

使用索引时的问题:

尽管使用索引可以大大加快查询响应的速度,提高MySQL的检索性能,但是过多使用索引将会影响系统的性能,其主要原因如下:

  1. 降低更新表中数据的速度
    索引在提高查询速度的同时,会降低更新数据表的速度。在更新数据表时,系统会自动地更新索引列的数据,以确保索引与表中的数据保持一致,这可能需要重新组织一个索引。如果表上建立的索引很多,会非常浪费更新操作的时间,由此降低insert、update、delete及其他写入操作的效率。表中建立的索引越多,则更新表的时间越长。
  2. 增加存储空间
    无论是采取InnoDB还是MyISAM存储引擎,索引都需要占用磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。特别是如果在一个大表上创建了多种组合索引,索引文件的大小会膨胀得非常快。

因此,索引只是提高检索效率的一个手段。如果MySQL数据库中存在大量数据表,而这些表的更新操作比较多,则需要认真分析,设计有效的索引,或者优化所使用的查询语句。

使用索引的建议

  1. 使用索引有利于提高数据查询的效率,但是会影响数据更新的速度。不恰当的索引会降低系统性能。因此,在插入、修改、删除操作较多的数据表上避免建立过多的索引。
  2. 数据量较小的数据表最好不要建立索引。
  3. 使用组合索引时,严格遵循左前缀法则。
  4. 在查询表达式中经常使用、有较多不同值的字段上建立索引。避免在不同值较少的字段上建立索引。例如,“性别”字段的值只有两个:“男”和“女”。在这类字段上建立索引不仅不会提高查询效率,反而会降低更新速度。
  5. where子句中尽量避免将索引列作为表达式的一部分。在使用like时,避免开头使用通配符。例如,“like %aaa%”会使索引失效,而“like aaa%”子句可以使用索引。
  6. 为了提高索引的效率,若char或varchar列的字符数很多,则可视具体情况选取字段的前N个字符值进行检索,即对索引列的前缀建立索引,这样可以节约存储空间。
# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了659.4k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×