`

深入MySQL数据库的索引

 
阅读更多

 

摘要:本文介绍索引的类型,已经如何创建索引做了介绍,其中涉及三个比较重要 SQL语句——ALTER TABLECREATE/DROP INDEXCREATE TABLE,注意它们的用法。

索引是加速表 内容访问的主要手段,特别对涉及多个表的连接的查询更是如此。这是数据库优化中的一个重要内容,我们要了解为什么需要索引,索引 如何工作以及怎 样利用它们来优化查询。本节中,我们将介绍索引的特点,以及创建和删除索引的语法。

索引的特点

 有的MySQL列类型能被索引。在相关的列上的使用索引是改进 SELECT操作性能的最好方法

 个表最多可有16个索引。最大索引长度是256个字节,尽管这可以在编译MySQL时被改变。

对于CHARVARCHAR列,你可以索引 列的前缀。这更快并且比索引整个列需要较少的磁盘空间。对于BLOBTEXT列,你必须索引列的前缀,你不能索引列的全部。

MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。(在CHARVARCHAR列上,你也可以使用列的前缀 作为一个索引的部分)。

虽然随着 MySQL 的进一步开发创建索引的约束将会越来越少,但现在还是存在一些约束的。下面的表根据索引的特性,给出了 ISAM 表和 MyISAM 表之间的差别:

2-1  通道信息特征字对照表

索引的特点

ISAM 

MyISAM 

NULL 
BLOB
  TEXT 
每个表中的索引数
每个索引中的列数
最大索引行尺寸


不允许
 能索引
16
16
256
 字节

允许
只能索引列的前缀
32
16
500
 字节

从此表中可以看到,对于 ISAM 表来说,其索引列必须定义为 NOT NULL,并且不能对 BLOB  TEXT 列进行索引。MyISAM 表类型去掉了这些限制,而且减缓了其他的一些限制。两种表类型的索引特性的差异表明,根据所使用的 MySQL 版本的不同,有可能对某些列不能进行索引。例如,如果使用3.23 版以前的版本,则不能对包含NULL 值的列进行索引。

索引有如下的 几种情况:

INDEX索引:通常意义的索引,某些情况下KEY是它的一个同义词。索引的列可以包括重复的值。

UNIQUE索引:唯一索引,保证了列不包含重复的值,对于多列唯一索引,它保证值的组合不重复。

PRIMARY KEY索引:也UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有PRIMARY名称的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY

Alter Table语句创建与删除索引

 了给现有的表增加一个索引,可使用 ALTER TABLE  CREATE INDEX 语句。ALTER TABLE 最常用,因为可用它来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引,如:

ALTER TABLE tbl_name ADD INDEX index_name  (column_list)

ALTER TABLE tbl_name ADD UNIQUE index_name  (column_list)

ALTER TABLE tbl_name ADD PRIMARY KEY index_name  (column_list)

  tbl_name 是要增加索引的表名,而column_list 指出对哪些列进行索引。一个(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。如果索引由不止一列组成,各列名之 间用逗号分隔。索引名 index_name 是可选的,因此可以不写它,MySQL 将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改,因此可以在同时创建多个索引。

同样,也可以用ALTER TABLE语句产出列的索引:

ALTER TABLE tbl_name DROP INDEX index_name

ALTER TABLE tbl_name DROP PRIMARY KEY

 意上面第一条语句可以用来删除各种类型的索引,而第三条语句只在删除 PRIMARY KEY 索引时使用;在此情形中,不需要索引名,因为一个表只可能具有一个这样的索引。如果没有明确地创建作为 PRIMARY KEY 的索引,但该表具有一个或多个 UNIQUE 索引,则 MySQL 将删除这些 UNIQUE 索引中的第一个。

如果从表中删除了列,则 索引可能会受到影响。如果所删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

 如,对于上面所使用的student为例,你可能想为之创建这样的索引,以加速表的检索速度:

mysql> ALTER TABLE student

-> ADD PRIMARY KEY(id),

-> ADD INDEX mark(english,Chinese,history);


 个例子,既包括PRIMARY索引,也包括多列索引。记住,使用PRIMARY索引的列,必须是一个具有NOT NULL属性的列,如果你愿意产看创建的索引的情况,可以使用SHOW INDEX语句:

mysql> SHOW INDEX FROM student;

其结果为:

+---------+------------+----------+--------------+-------------+-

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

+---------+------------+----------+--------------+-------------+-

| student |          0 | PRIMARY  |            1 | id          |

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

+---------+------------+----------+--------------+-------------+-

 于列数太多,上表并没有包括所有的输出,读者可以试着自己查看。

再使用ALTER TABLE语句删除索引,删除索引需要知道索引的名字,你可以通过SHOW INDEX语句得到:

mysql> ALTER TABLE student DROP PRIMARY KEY,

    -> DROP INDEX mark;

再产看表中的索引,其语 句和输出为:

mysql> SHOW INDEX FROM student;

Empty set (0.01 sec)

CREATE\DROP INDEX创建索引

还可以用CREATE INDEX语句来创建索引.CREATE INDEX 是在 MySQL 3.23版中引入的,但如果使用3.23 版以前的版本,可利用ALTER TABLE 语句创建索引(MySQL 通常在内部将 CREATE INDEX 映射到 ALTER TABLE)。该语句创建索引的语法如下:

CREATE UNIQUE INDEX index_name ON tbl_name (column_list)

CREATE INDEX index_name ON tbl_name (column_list)

tbl_name index_name  column_list 具有与 ALTER TABLE 语句中相同的含义。这里索引名不可选。很明显,CREATE INDEX 可对表增加普通索引或UNIQUE 索引,不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

 利用 DROP INDEX语句来删除索引。类似于CREATE INDEX 语句,DROP INDEX 通常在内部作为一条 ALTER TABLE 语句处理,并且DROP INDEX是在 MySQL 3.22 中引入的。

删除索引语句的语法如下:

DROP INDEX index_name ON tbl_name

还是上一节的例子,由于CREATE INDEX不能创建PRIMARY索引,所以这里我们值创建一个多列索引:

mysql> CREATE INDEX mark ON student(english,chinese,history);

同样的检查student表,可知:

mysql> SHOW INDEX FROM student;

+---------+------------+----------+--------------+-------------+

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

+---------+------------+----------+--------------+-------------+

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

+---------+------------+----------+--------------+-------------+

 后使用下面的语句删除索引:

mysql> DROP INDEX mark ON student;

 创建表时指定索引

要想在发布 CREATE TABLE 语句时为新表创建索引,所使用的语法类似于 ALTER TABLE 语句的语法,但是应该在您定义表列的语句部分指定索引创建子句,如下所示:

CREATE TABLE tbl_name

(

INDEX index_name (column_list),

KEY index_name (column_list),

UNIQUE index_name (column_list),

PRIMARY KEY index_name (column_list),

)


ALTER TABLE 一样,索引名对于 INDEX  UNIQUE 都是可选的,如果未给出,MySQL 将为其选一个。另外,这里KEYINDEX的一个别名,具有相同的意义。

有一种特殊 情形:可在列定义之后增加 PRIMARY KEY 创建一个单列的PRIMARY KEY 索引,如下所示:

CREATE TABLE tbl_name

(

  i INT NOT NULL PRIMARY KEY

)

 

该语句等价于以下的语句:

 

CREATE TABLE tbl_name

(

  i INT NOT NULL,

  PRIMARY KEY (i)

)

前面所有表创建样例都对索引列指定了 NOT NULL。如果是 ISAM 表,这是必须的,因为不能对可能包含 NULL值的列进行索引。如果是MyISAM 表,索引列可以为 NULL,只要该索引不是 PRIMARY KEY 索引即可。

CREATE TBALE语句中可以某个串列的前缀进行索引(列值的最左边 n 个字符)。

如果对某个 串列的前缀进行索引,应用 column_list 说明符表示该列的语法为col_name(n) 而不用col_name。例如,下面第一条语句创建了一个具有两个 CHAR 列的表和一个由这两列组成的索引。第二条语句类似,但只对每个列的前缀进行索引:

CREATE TABLE tbl_name

(

name CHAR(30),

address CHAR(60),

INDEX (name,address)

)

CREATE TABLE tbl_name

(

name CHAR(30),

address CHAR(60),

INDEX (name(10),address(20))

)

你可以检查所创建表的索引:

mysql> SHOW INDEX FROM tbl_name;

+----------+------------+----------+--------------+-------------+-

| Table    | Non_unique | Key_name | Seq_in_index | Column_name |

+----------+------------+----------+--------------+-------------+-

| tbl_name |          1 | name     |            1 | name        |

| tbl_name |          1 | name     |            2 | address     |

+----------+------------+----------+--------------+-------------+-

 某些情况下,可能会发现必须对列的前缀进行索引。例如,索引行的长度有一个最大上限,因此,如果索引列的长度超过了这个上限,那么就可能需要利用前缀进行 索引。在 MyISAM 表索引中,对 BLOB  TEXT 列也需要前缀索引。

对一个列的前缀进行索引限制了以后对该列的更改;不能 在不删除该索引并使用较短前缀的情况下,将该列缩短为一个长度小于索引所用前缀的长度的列。

总结

 节对索引的类型,已经如何创建索引做了介绍,其中涉及三个比较重要的SQL语句——ALTER TABLECREATE/DROP INDEXCREATE TABLE,注意它们的用法。

索引最重要的功能是,通过使用索引加速表的检索,有关这方面的知识,将在第十章数据库优化中介绍。

思考题

1、建立一个如下所述的表:

dataFLOAT 列,使用随机函数填充数 

birthDATETIME列,填充当前时间。

然后,请录入几条数据。最后计算data列的平均值、总和、极值,并且按 data列降序排序检索值。

2、分别使用标准SQL模式和扩展正规表达式模式匹配,匹配上面创建的表,假设你创建表的当前日期为 2001-01-01,用模式匹配检索出birth列包含该日期的值。(实际上,上面的表中记录都是同一日期录入的,因此实际将返回全部记录。)

3 为前几章使用的数据表创建索引:

student:为id段创建一个PRIMARY索引,为englishchinesehistory 创建一个多列索引。

pet:为nameowner段创建一个多类索引。

4、删除为pet表创建的索引。

分享到:
评论

相关推荐

    书籍:Oracle与MySQL数据库索引设计与优化

    《Oracle与MySQL数据库索引设计与优化》这本书深入探讨了两个主流关系型数据库管理系统——Oracle和MySQL中的索引设计和优化策略。索引是数据库性能的关键因素,它们能够加速数据检索,提高系统效率,尤其在大数据量...

    深入浅出MySQL数据库开发、优化与管理维护.doc

    本文档旨在深入浅出 MySQL 数据库开发、优化与管理维护的知识点,涵盖了 MySQL 数据库开发的基本概念、优化技巧和管理维护方法。 数据库开发篇 在本篇中,我们将学习 MySQL 数据库开发的基础知识,包括数据类型、...

    MYSQL数据库大进级,是非常全面的MYSQL数据库进阶学习资料!

    MYSQL数据库大进级,是非常全面的MYSQL数据库进阶学习资料!

    MySQL数据库索引优化

    MySQL数据库索引优化是数据库管理员和开发人员在提升数据库性能方面的一个关键点,涉及BTree索引和Hash索引以及索引优化的策略。索引是数据库中一种非常重要的数据结构,它能够大幅提升查询的效率,但也需要恰当的...

    MySQL数据库基础与实例教程所有资源

    本教程的PPT版和PDF版提供了详细的讲解,配合源代码实例,将帮助你深入理解MySQL数据库的使用。无论你是初学者还是有一定经验的开发者,都能从中受益。通过学习和实践,你将能够熟练地设计、操作和管理MySQL数据库,...

    MySQL数据库原理及应用(第2版)(微课版)-教学用数据库(Mysql数据库备份文件).zip

    在《MySQL数据库原理及应用(第2版)(微课版)》中,我们通常会深入探讨数据库的基本概念、设计原则以及实际操作技巧。这份教学资料包含了一个Mysql数据库的备份文件,为学习者提供了实践平台,便于理解和掌握...

    国家开放大学 MySQL数据库应用 实验训练4:数据库系统维护

    MySQL 数据库应用实验训练 4:数据库系统维护 本实验训练旨在帮助学生理解视图和索引的概念和作用,掌握视图和索引的基本操作,并且了解它们在数据库系统维护中的应用。 一、视图的概念和作用 视图是一种基于基表...

    实验2 MySQL数据库对象管理.pdf

    本实验的目的是使学生理解并掌握MySQL数据库中关键对象(数据字典、表、索引、视图)的作用和操作方法。通过一系列的操作实践,学生将能更深入地了解数据库对象管理,并能在实际工作中进行相关维护和操作。

    实验2 MySQL数据库对象管理

    这样的实践不仅锻炼了学生的动手能力,也让他们深入理解了MySQL数据库管理系统的工作原理。 总结来说,这个实验全面覆盖了MySQL数据库管理的基础操作,包括数据字典的查询、数据库和表的创建与修改、索引的管理和...

    MySQL数据库入门.rar

    MySQL数据库是目前世界上最受欢迎的关系型数据库管理系统之一,尤其在互联网应用中被广泛使用。这个"MySQL数据库入门"压缩包提供了一系列的学习资源,包括PPT、PDF文档和代码示例,帮助初学者快速掌握MySQL的基础...

    MySQL数据库相关课件PPT

    MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一,它以其高效、可靠和易于维护的特点,在Web开发、企业级应用和数据分析等领域广泛应用。本课件PPT涵盖了MySQL的基础知识到高级特性的深入理解,旨在帮助...

    MYSQL数据库修复大师7.12

    MySQL数据库修复大师通过深入扫描这些受损文件,解析其内部结构,尝试恢复尽可能多的数据。 该工具支持最新的MySQL版本,这意味着无论是个人用户还是企业用户,都能在遇到数据库问题时得到及时的帮助。在数据库修复...

    mysql数据库优化(全)

    综上所述,通过合理的配置查询缓冲、利用MySQL的自动优化机制以及合理使用索引来进行排序,可以有效提升MySQL数据库的查询性能。这些方法不仅适用于简单的查询场景,也能在复杂的业务环境中发挥重要作用。

    MySQL数据库习题和实践答案.rar

    下面我们将深入探讨MySQL数据库的一些关键知识点。 1. **SQL基础**: SQL(Structured Query Language)是用于管理和处理关系数据库的标准语言。学习SQL包括了解如何创建、更新、查询和删除数据,以及如何管理...

    mysql数据库基础全部课件

    MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一,它以其高效、可靠和易于学习的特点,广泛应用于各种规模的企业和项目中。本课件"mysql数据库基础全部课件"旨在为初学者提供一个全面且实用的学习资源,...

    mysql数据库基本操作

    在本篇文章中,我们将深入探讨MySQL数据库的基本操作,包括数据的增、删、改、查(CRUD)以及一些核心概念。 1. **安装与配置**: 安装MySQL通常涉及下载安装包,按照向导进行安装,并设置root用户的密码。在...

    《MySQL数据库原理及应用》教案.rar

    《MySQL数据库原理及应用》是一门深入探讨关系型数据库管理系统MySQL的课程,旨在教授学生如何设计、创建和管理数据库,以及如何在实际应用场景中高效利用MySQL。教案详细涵盖了该课程的所有章节,为教学提供了全面...

    MySQL数据库应用与开发实训报告.doc

    【MySQL数据库应用与开发实训报告】 本实训报告主要围绕姜桂洪主编的《MySQL数据库应用与开发》一书,结合2018年清华大学出版社的教材内容,详细介绍了MySQL数据库在Windows10环境下5.7版本的实战操作,涵盖了从...

    全面深入Mysql数据库优化视频完整资料

    全面深入Mysql数据库优化视频【完整资料】包括索引 存储过程等

    深入浅出-MySQL数据库开发、优化与管理维护

    《深入浅出-MySQL数据库开发、优化与管理维护》是一本专为IT专业人士设计的全面指南,涵盖了MySQL数据库从基础到高级的各个方面。这本书旨在帮助读者深入理解MySQL的内部机制,提升开发效率,优化数据库性能,并掌握...

Global site tag (gtag.js) - Google Analytics