第11章--索引.pptx

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,。创建,空间索引的字段，必须将其声明为,NOT NULL,。,11.1.3,索引的设计原则,数据量很小的表最好不要使用索引,，否则通过索引查询记录可能比直接扫描整张表还要慢。,索引并非越多越好。,过多的索引会占用大量磁盘空间，并且会影响插入、修改和删除等语句的性能。,对于经常执行修改操作的表不要创建过多索引,，并且索引中的列应尽可能少。而,对于经常执行查询操作的字段,，应该创建索引。,在条件表达式中经常会用到的不同值较多的列上创建索引，不同值较少的列不要创建索引,。,因为,这样不仅不会提高查询效率，反而会严重降低更新速度。,在频繁进行排序或分组（即进行,group by,或,order by,操作）的列上创建索引。,如果待排序的列有多个，可以在这些列上创建组合索引。,11.2,创建和查看索引,11.2.1,在创建表的时候创建索引,MySQL,支持用多种方法在单个或多个列上创建索引，可以在创建表的同时创建索引，也可以使用,ALTER TABLE,或,CREATE INDEX,语句在已有的表上创建索引。,使用,CREATE,关键字创建表时，除可以定义列的数据类型外，还可以定义主键约束、自增约束、唯一约束等。不管创建哪种约束，,在定义约束的同时都相当于在指定列上创建了一个索引,。例如，如果定义字段时设置主键约束和唯一约束，,MySQL,会同时在指定列上创建对应的索引。,CREATE TABLE table_name(,col_name data_type,INDEX|KEY index_name(col_name 1(length)ASC|DESC,(,col_name n(length)ASC|DESC),);,11.2.1,在创建表的时候创建索引,普通索引是最基本的索引类型，没有唯一或自增之类的限制，其作用只是加快对数据的访问速度。,mysql CREATE TABLE people(,-id INT(11),-name VARCHAR(30),-mobile VARCHAR(11),-INDEX n_index(name),-);,Query OK,0 rows affected(0.46 sec),创建和查看普通索引,1,【,实,例11,-1,】,执行,SQL,语句，创建数据表,people,，并为其中的字段,name,创建普通索引。,USE db_shop;,步骤,1,执行,SQL,语句，选择一个数据库，此处为,db_shop,。,步骤,2,执行,SQL,语句，创建数据表,people,，同时为其,name,字段创建普通索引。,SQL,语句及其执行结果如下：,11.2.1,在创建表的时候创建索引,mysql SHOW INDEX FROM people G,*1.row*,Table:people,Non_unique:1,Key_name:n_index,Seq_in_index:1,Column_name:name,Collation:A,Cardinality:0,Sub_part:NULL,Packed:NULL,Null:YES,Index_type:BTREE,Comment:,Index_comment:,1 row in set(0.00 sec),创建和查看普通索引,1,步骤,3,数据表,创建完成之后，可以使用,SHOW INDEX,语句查看索引。,SQL,语句及其执行结果,如下：,11.2.1,在创建表的时候创建索引,索引还可以创建在多个字段上，这种索引称为组合索引。,mysql CREATE TABLE people1(,-id INT(11),-name VARCHAR(30),-age TINYINT(4),-status TINYINT(4),-INDEX n_index(name,age,status),-);,Query OK,0 rows affected(0.25 sec),创建组合索引,2,【,实,例11,-2,】,执行,SQL,语句，创建数据表,people1,，并为其中的,name,，,age,和,status 3,个字段创建一个普通索引。,步骤,1,继续,在数据库,db_shop,中操作。,步骤,2,执行,SQL,语句，创建数据表,people1,，同时为其字段,name,，,age,和,status,创建普通索引。,SQL,语句及其执行结果,如下：,11.2.1,在创建表的时候创建索引,mysql SHOW CREATE TABLE people1 G,*1.row*,Table:people1,Create Table:CREATE TABLE people1(,id int(11)DEFAULT NULL,name varchar(30)DEFAULT NULL,age tinyint(4)DEFAULT NULL,status tinyint(4)DEFAULT NULL,KEY n_index(name,age,status),)ENGINE=InnoDB DEFAULT CHARSET=utf8,1 row in set(0.00 sec),创建组合索引,2,步骤,3,执行,SQL,语句，使用,SHOW CREATE TABLE,语句查看表结构。,SQL,语句及其执行结果如下：,提示,在使用多字段组合索引时，要遵循“最左前缀”的规则，就是在查询时，利用索引中最左边的一个或几个字段匹配数据。例如，在,people1,表中，索引是由,name,，,age,和,status 3,个字段组成的，可以使用的字段组合有（,name,age,status,）、（,name,age,）或,name,。,11.2.1,在创建表的时候创建索引,mysql EXPLAIN SELECT*FROM people1 WHERE name=tomand age=21 G,*1.row*,id:1,select_type:SIMPLE,table:people1,partitions:NULL,type:ref,possible_keys:n_index,key:n_index,key_len:95,ref:const,const,rows:1,filtered:100.00,Extra:NULL,1 row in set,1 warning(0.00 sec),创建组合索引,2,MySQL,提供,EXPLAIN,关键字用于查看索引的使用情况。执行,SQL,语句，以,name,和,age,字段为条件查询，结果如下：,mysql EXPLAIN SELECT*FROM people1 WHERE age=21 and status=1 G,*1.row*,id:1,select_type:SIMPLE,table:people1,partitions:NULL,type:ALL,possible_keys:NULL,key:NULL,key_len:NULL,ref:NULL,rows:1,filtered:100.00,Extra:Using where,1 row in set,1 warning(0.00 sec),执行,SQL,语句，以,age,和,status,字段为条件查询，结果如下：,11.2.1,在创建表的时候创建索引,创建索引的主要目的是减少查询索引列操作的执行时间。唯一索引与普通索引的不同之处在于：索引列的值必须唯一；如果是组合索引，则列值的组合必须唯一。,mysql CREATE TABLE people2(,-id INT(11),-name VARCHAR(30),-mobile VARCHAR(11),-UNIQUE m_unique(id),-);,Query OK,0 rows affected(0.52 sec),创建唯一索引,3,【,实,例11,-3,】,执行,SQL,语句，创建数据表,people2,，并为其中的,id,字段创建唯一索引。,步骤,2,执行,SQL,语句，创建数据表,people2,，同时使用,UNIQUE,关键字为,id,字段创建唯一索引。,SQL,语句及其执行结果如下：,步骤,1,继续,在数据库,db_shop,中操作。,11.2.1,在创建表的时候创建索引,mysql SHOW INDEX FROM people2 G,*1.row*,Table:people2,Non_unique:0,Key_name:m_unique,Seq_in_index:1,Column_name:id,Collation:A,Cardinality:0,Sub_part:NULL,Packed:NULL,Null:YES,Index_type:BTREE,Comment:,Index_comment:,1 row in set(0.14 sec),创建唯一索引,3,步骤,3,使用,SHOW INDEX,关键字查看,people2,表的索引，结果如下。,11.2.1,在创建表的时候创建索引,全文索引（,FULLTEXT,）可用于全文搜索，只能在数据类型为,CHAR,、,VARCHAR,和,TEXT,的列上创建，并且只有,MyISAM,存储引擎支持全文索引。,mysql CREATE TABLE people3(,-id INT(11),-name VARCHAR(30),-intro TEXT,-FULLTEXT i_fulltext(intro),-);,Query OK,0 rows affected(1.65 sec),创建,全文,索引,4,【,实,例11,-4,】,执行,SQL,语句，创建数据表,people3,，并为其中的,intro,字段创建全文索引。,步骤,2,执行,SQL,语句，创建数据表,people3,，同时使用,FULLTEXT,关键字为,intro,字段创建全文索引。,SQL,语句及其执行结果如下：,步骤,1,继续,在数据库,db_shop,中操作。,11.2.1,在创建表的时候创建索引,mysql SHOW INDEX FROM people3 G,*1.row*,Table:people3,Non_unique:1,Key_name:i_fulltext,Seq_in_index:1,Column_name:intro,Collation:NULL,Cardinality:0,Sub_part:NULL,Packed:NULL,Null:YES,Index_type:FULLTEXT,Comment:,Index_comment:,1 row in set(0.00 sec),创建,全文,索引,4步骤,3,使用,SHOW INDEX,关键字查看,people3,表的索引，结果如下。,11.2.1,在创建表的时候创建索引,mysql CREATE TABLE people4(,-id INT(11),-name VARCHAR(30),-geom GEOMETRY NOT NULL,-SPATIAL g_apatial(geom),-);,Query OK,0 rows affected(0.27 sec),创建,空间,索引,5,【,实,例11,-5,】,执行,SQL,语句，创建数据表,people4,，并为其中的,geom,字段创建空间索引。,步骤,2,执行,SQL,语句，创建数据表people4,，同时使用,SPATIAL,关键字为,geom,字段创建空间索引。,SQL,语句及其执行结果如下：,步骤,1,继续,在数据库,db_shop,中操作。,11.2.1,在创建表的时候创建索引,mysql SHOW INDEX FROM people4 G,*1.row*,Table:people4,Non_unique:1,Key_name:g_apatial,Seq_in_index:1,Column_name:geom,Collation:A,Cardinality:0,Sub_part:32,Packed:NULL,Null:,Index_type:SPATIAL,Comment:,Index_comment:,1 row in set(0.01 sec),创建,空间,索引,5,步骤,3,使用,SHOW INDEX,关键字查看,people4,表的索引，结果如下。,提示,创建空间索引时，空间类型的字段必须设置非空约束。,11.2.2,在已有的表上创建索引,mysql ALTER TABLE people ADD INDEX m_index(mobile);,Query OK,0 rows affected(0.30 sec),Records:0 Duplicates:0 Warnings:0,使用,ALTER TABLE,语句创建索引,1,【,实,例11,-6执行,SQL,语句，为实例,11-1,中创建的,people,表中的字段,mobile,创建普通索引。,步骤,1,继续,在数据库,db_shop,中操作。,步骤,2,执行,SQL,语句，使用,ALTER TABLE,语句为,people,表中的字段,mobile,创建普通索引。,SQL,语句及其执行结果如下：,如果数据表已经创建完成，可以使用,ALTER TABLE,或,CREATE INDEX,语句在已有表的字段上创建索引。,ALTER TABLE,表名,ADD UNIQUE|FULLTEXT|SPATIAL INDEX|KEY index_name(col_name(length)ASC|DESC,11.2.2,在已有的表上创建索引,mysql SHOW INDEX FROM people G,*1.row*,Table:people,Non_unique:1,Key_name:n_index,Seq_in_index:,1,*,2.row*,Table:people,Non_unique:1,Key_name:m_index,Seq_in_index:1,Column_name:mobile,Collation:A,Cardinality:,0,2,rows in set(0.01 sec),使用,ALTER TABLE,语句创建索引,1,步骤,3,使用,SHOW INDEX,语句查看,people,表的索引，结果如下：,11.2.2,在已有的表上创建索引,mysql CREATE UNIQUE INDEX n_unique ON people2(name);,Query OK,0 rows affected(0.16 sec),Records:0 Duplicates:0 Warnings:0,使用,CREATE INDEX,语句创建索引,2,步骤,2,执行,SQL,语句，使用,CREATE INDEX,语句为,people2,表中的字段,name,创建唯一索引。,SQL,语句及其执行结果如下：,CREATE UNIQUE|FULLTEXT|SPATIAL INDEX index_name ON,表名,(col_name(length)ASC|DESC,【,实,例11,-7,】,执行,SQL,语句，为实例,11-3,中创建的,people2,表中的字段,name,创建唯一索引。,步骤,1,继续,在数据库,db_shop,中操作。,11.2.2,在已有的表上创建索引,mysql SHOW INDEX FROM people2 G,*1.row*,Table:people2,Non_unique:0,Key_name:m_unique,Seq_in_index:1,Column_name:id,Collation:A,*,2.row*,Table:people2,Non_unique:0,Key_name:n_unique,Seq_in_index:1,Column_name:,name,2,rows in set(0.00 sec),使用,CREATE INDEX,语句创建索引,2,步骤,3,使用,SHOW INDEX,语句查看,people2,表的索引，结果如下：,11.3,删除索引,使用,ALTER TABLE,语句删除索引,1,如果要在,MySQL,中删除索引，可以使用,ALTER TABLE,或,DROP INDEX,语句。,ALTER TABLE,表名,DROP INDEX,索引名,;,mysql ALTER TABLE people DROP INDEX m_index;,Query OK,0 rows affected(0.12 sec),Records:0 Duplicates:0 Warnings:0,【,实,例11,-8,】,执行,SQL,语句，删除,people,表中的,m_index,索引，结果如下：,提示,删除主键索引时，语法形式与前面有所不同，结构如下：,ALTER TABLE,表名,drop PRIMARY KEY;,使用,SHOW INDEX,语句查看,people,表的索引，结果如下：,mysql SHOW INDEX FROM people G,*1.row*,Table:people,Non_unique:1,Key_name:n_index,Seq_in_index:1,Column_name:name,Collation:A,Cardinality:0,Sub_part:NULL,Packed:NULL,Null:YES,Index_type:BTREE,Comment:,Index_comment:,1 row in set(0.00 sec),使用,DROP INDEX,语句删除索引,2,DROP INDEX,索引名,ON,表名,;,mysql DROP INDEX n_index ON people;,Query OK,0 rows affected(0.13 sec),Records:0 Duplicates:0 Warnings:0,【,实,例11,-9,】,执行,SQL,语句，删除,people,表中的,n_index,索引，结果如下：,mysql SHOW INDEX FROM people G,Empty set(0.00 sec),使用,SHOW INDEX,语句查看,people,表的索引，结果如下：,感谢观看,