第9章 MySQL索引 第1节 索引

数据库原理及MySQL应用

——第九章（第1节）9.1索引索引概述索引的作用索引的特征1.索引概述在MySQL中，索引其实与书的目录非常的相似，由数据表中一列或多列组合而成，创建索引的目的是为了优化数据库的查询速度，提高性能的最常用的工具。所有MySQL列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。索引有两种存储类型：B型树（BTREE）索引和哈希（HARSH）索引。其中B型树为系统默认索引存储类型。2.索引的作用（1）索引优点通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。可以加速表和表之间的连接，特别是在实现数据的参照完整性方面特别有意义。可以大大加快数据的检索速度，这也是创建索引的最主要的原因。（2）索引缺点创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。（3）索引特征索引有两个特征，即唯一性索引和复合索引。复合索引就是一个索引创建在两个列或者多个列上。唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。3.索引的分类普通索引：在创建普通索引时，不附加任何限制条件。多列索引：多列索引是在表的多个字段上创建一个索引。单列索引：在表中的单个字段上创建索引。全文索引：使用FULLTEXT参数可以设置索引为全文索引。唯一性索引：使用UNIQUE参数可以设置索引为唯一性索引。9.2索引的定义和管理创建索引查看索引删除索引1.创建索引创建索引是指在某个表的一列或多列上建立一个索引。直接创建索引，有以下三种方式。在创建表的时候创建索引。语法格式：createtabletable_name(

属性名，数据类型[完整性约束],属性名，数据类型[完整性约束],...属性名，数据类型[完整性约束],index|key[索引名](属性名[(长度)][asc|desc]));在已存在的表上创建索引语法格式：createindex索引名ON表名(属性名[(长度)][asc|desc]);使用altertable语句来创建索引。语法格式：altertabletable_nameaddindex|key[索引名](属性名[(长度)][asc|desc])间接创建索引通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。主键约束或者唯一性键约束创建的索引的优先级高于使用CREATEINDEX语句创建的索引。（1）普通索引创建一个普通索引时，不需要加任何unique、fulltext或者sparial参数。【例1】创建一个新表newTable，包含int型的id字段、varchar(20)类型的name字段和int型的age字段。（2）唯一索引(uniqueindex)创建唯一性索引时，需要使用unique参数进行约束【例5】创建新表newTable1，在表的id字段上建立名为id_index的唯一索引，以升序排列。（3）全文索引(fulltextindex)全文索引只能创建在char、varchar或者text类型的字段上。【例8】创建表newTable2，并指定char(20)字段类型的字段Info为全文索引。（4）多列索引多列索引是在多个字段上创建一个索引【例9】创建表newTable3，在类型char(20)的name字段上和int类型的age字段上创建多列索引。2.查看索引在实际使用索引的过程中，有时需要对表的索引信息进行查询，了解在表中曾经建立的索引。语法格式：INDEXFROMtable_name[FROMdb_name]另一种语法格式：SHOWINDEXFROMmytableFROMmydb;SHOWINDEXFROMmydb.mytable;【例10】查看newTable中索引的详细信息。3.删除索引创建索引后，如果用户不再使用该索引，可以删除指定表的索引。语法格式：dropindexindex_nameontable_name;altertabletable_namedropindexindex_name;altertabletable_namedropprimarykey;【例11】下面删除newTable中的索引的name_index。注意：如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

9.3设计原则和注意事项索引的设计原则合理使用索引注意事项不合理使用索引的注意事项1.索引的设计原则选择唯一性索引尽量使用数据量少的索引限制索引的数目为常作为查询条件的字段建立索引为经常需要排序、分组和联合操作的字段建立索引尽量使用前缀来索引删除不再使用或者很少使用的索引2.合理使用索引注意事项在经常需要搜索的列上，可以加快搜索的速度。在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。3.不合理使用