第11章--索引(完整版).pptxVIP

11

索引

第

章

11.1

索引概述

在关系型数据库中，索引主要用于对数据表中一列或多列的值进行排序，使用它可以有效提高数据库中特定数据的查询速度。

11.1.1索引的概念和特点

索引是一种单独的、存储在磁盘上的数据库结构，包含对数据表中所有记录的引用指针。它的作用就相当于书籍的目录，使用它可以快速找出在某个或多个列中有一特定值的行。

索引是在存储引擎中实现的，每种存储引擎支持的索引类型有所不同，应根据数据库所应用的存储引擎定义每个表的最大索引数和最大索引长度。MySQL目前支持BTREE和HASH两种索引。MyISAM和InnoDB存储引擎默认只支持BTREE索引；MEMORY存储引擎默认创建的是HASH索引，它也支持BTREE索引。每种存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

可以大大加快数据的检索速度，这也是创建索引最主要的原因。

创建唯一索引，可以保证数据库表中每行数据的唯一性。

加速表和表之间的连接。

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

11.1.2索引的分类

普通索引：普通索引（INDEX）是MySQL中最基本的索引，它没有任何限制，它的唯一任务是加快对数据的访问速度。

组合索引：组合索引是相对单列索引而言，单列索引是指索引中只包含单个列，一个表可以有多个单列索引；组合索引是指在表的多个字段组合上创建的索引。

唯一索引：唯一索引（UNIQUE）与普通索引类似，不同之处在于索引列的值必须唯一，但允许有空值。如果索引包含多个字段，则列值的组合必须唯一。创建唯一索引的主要目的不是提高查询速度，而是避免数据重复。

全文索引：全文索引（FULLTEXT）可以在CHAR，VARCHAR和TEXT类型的列上创建。在创建索引的列上支持值的全文查找，并且允许列值重复和为NULL。

空间索引：空间索引（SPATIAL）是对空间数据类型的字段创建的索引，MySQL中的空间数据类型主要有GEOMETRY、POINT、LINESTRING和POLYGON。创建空间索引的字段，必须将其声明为NOTNULL。

11.1.3索引的设计原则

数据量很小的表最好不要使用索引，否则通过索引查询记录可能比直接扫描整张表还要慢。

索引并非越多越好。过多的索引会占用大量磁盘空间，并且会影响插入、修改和删除等语句的性能。

对于经常执行修改操作的表不要创建过多索引，并且索引中的列应尽可能少。而对于经常执行查询操作的字段，应该创建索引。

在条件表达式中经常会用到的不同值较多的列上创建索引，不同值较少的列不要创建索引。因为这样不仅不会提高查询效率，反而会严重降低更新速度。

在频繁进行排序或分组（即进行groupby或orderby操作）的列上创建索引。如果待排序的列有多个，可以在这些列上创建组合索引。

11.2

创建和查看索引

11.2.1在创建表的时候创建索引

MySQL支持用多种方法在单个或多个列上创建索引，可以在创建表的同时创建索引，也可以使用ALTERTABLE或CREATEINDEX语句在已有的表上创建索引。

使用CREATE关键字创建表时，除可以定义列的数据类型外，还可以定义主键约束、自增约束、唯一约束等。不管创建哪种约束，在定义约束的同时都相当于在指定列上创建了一个索引。例如，如果定义字段时设置主键约束和唯一约束，MySQL会同时在指定列上创建对应的索引。

CREATETABLEtable_name(

[col_namedata_type,]

……

INDEX|KEYindex_name(col_name1[(length)])[ASC|DESC],

……

(col_namen[(length)])[ASC|DESC])

);

11.2.1在创建表的时候创建索引

普通索引是最基本的索引类型，没有唯一或自增之类的限制，其作用只是加快对数据的访问速度。

mysqlCREATETABLEpeople(

-idINT(11),

-nameVARCHAR(30),

-mobileVARCHAR(11),

-INDEXn_index(name)

-);

QueryOK,0rowsaffected(0.46sec)

创建和查看普通索引

执行SQL语句，创建数据表people，并为其中的字段name创建普通索引。

USEdb_shop;

步骤1执行SQL语句，选择一个数据库，此处为db_shop。

步骤2执行SQL语句，创建数据表people，同时为其name字段创建普通索引。SQL语句