MySQL数据库基础实例教程（第3版）课件 第七章 索引与分区

第7章索引与分区项目知识要点知识能力目标学时任务1索引及其应用1索引的分类2创建索引3删除索引4索引对查询的影响【典型工作任务】创建索引【技能要求】1理解各种索引的作用与区别2掌握建立各种索引的方法【知识要求】1索引的作用与使用2建立索引的SQL语句4项目实训LibraryDB索引与分区2项目知识要点与目标索引及其应用索引——是一种提高查找速度的机制索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。索引文件学生表student中建立“学号”索引（升序）示意图学号记录号200601011200601023200602032…………2007020110000…………20809995000学号姓名性别年龄20060101张建男1820060202王欢女1720060102田静男18………..2080999李华女19…….…..20070201张丽女17学生表student索引文件索引文件如何影响原表没有索引文件时：如果要找位于第10000条的学号”20070201”的记录,计算机要在表中查找10000次指针在原表中顺序移动学号姓名性别年龄20060101张建男1820060202王欢女1720060102田静男18………..2080999李华女19…….…..20070201张丽女17使用索引查找计算机先在索引文件中学号为”20070201”的记录,找到相应的记录号,再到学生表中直接读取相关记录.有索引文件时：(二分法查找实例)学号记录号200601011200601023200602032…………2007020110000…………20809995000学号姓名性别年龄20060101张建男1820060202王欢女1720060102田静男18………..2080999李华女19…….…..20070201张丽女17学生表student索引文件B树索引示意图索引的分类01OPTION02OPTION03OPTION04OPTION普通索引（INDEX）这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。唯一性索引（UNIQUE）这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。主键（PRIMARYKEY）主键是一种唯一性索引，它必须指定为“PRIMARYKEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。全文索引（FULLTEXT）MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。创建索引1.使用CREATEINDEX语句CREATE[UNIQUE|FULLTEXT]INDEX索引名ON表名(列名[(长度)][ASC|DESC],...)使用CREATEINDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。语法格式：说明：索引名：索引的名称，索引名在一个表中名称必须是唯一的。列名：表示创建索引的列名。长度：表示使用列的前多少个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。BLOB或TEXT列必须用前缀索引。UNIQUE：表示创建的是唯一性索引FULLTEXT：表示创建全文索引；CREATEINDEX语句并不能创建主键。创建索引举例可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。

CREATEINDEXname­_bookONBook(书名(6)ASC);【例】在Sell表的用户号列和图书编号列上建立一个复合索引sfz_bh­_sell。

CREATEINDEXuser_bh­_sellONSell(用户号,图书编号);【例】根据Book表的书名列上的前6个字符建立一个升序索引name­_book。ALTERTABLE语句创建索引2.使用ALTERTABLE语句使用ALTERTABLE语句修改表，其中也包括向表中添加索引。语法格式如下：

ALTERTABLE表名

ADDINDEX[索引名](列名,...)

/*添加索引*/|ADDPRIMARYKEY[索引方式](列名,...) /*添加主键*/|ADDUNIQUE[索引名](列名,...) /*添加唯一性索引*/|ADDFULLTEXT[索引名](列名,...) /*添加全文索引*/ALTERTABLE创建索引举例【例】在Book表的书名列上创建一个普通索引。ALTERTABLEBookADDINDEX(书名);【例】假设Book表中主键未设定，为Book表创建以图书编号为主键索引，出版社和出版时间为复合索引，以加速表的检索速度。ALTERTABLEBookADDPRIMARYKEY(图书编号),ADDINDEX(出版社,出版时间);这个例子中，既包括PRIMARYKEY，也包括复合索引，说明MySQL可以同时创建多个索引。记住，使用PRIMARYKEY的列，必须是一个具有NOTNULL属性的列。如果想要查看表中创建的索引的情况，可以使用SHOWINDEX语句，例如：SHOWINDEXFROMbook;创建表时创建索引在前面两种情况下，索引都是在表创建之后创建的。索引也可以在创建表时一起创建。在创建表的CREATETABLE语句中可以包含索引的定义。语法格式：CREATETABLE表名(列名,...|[索引项])其中，索引项语法格式如下：PRIMARYKEY(列名,...)

/*主键*/|{INDEX|KEY}[索引名](列名,...) /*索引*/|UNIQUE[INDEX][索引名](列名,...) /*唯一性索引*/|[FULLTEXT][INDEX][索引名](列名,...) /*全文索引*/说明：KEY通常是INDEX的同义词。在定义列选项的时候，也可以将某列定义为PRIMARYKEY，但是当主键是由多个列组成的多列索引时，定义列时无法定义此主键，必须在语句最后加上一个PRIMARYKEY列名，…）子句。创建表时创建索引举例CREATETABLEsell_copy(

用户号CHAR(18)NOTNULL,

图书编号CHAR(20)NOTNULL,

订购册数INT(5),

订购时间DATETIME, PRIMARYKEY(用户号,图书编号), INDEX(订购册数));【例】创建sell_copy表，设置用户号和图书编号为联合主键，并在订购册数列上创建索引。删除索引1.使用DROPINDEX语句删除索引DROPINDEX索引名ON表名语法格式：【例】删除book表上索引名为“书名“的索引。DROPINDEX书名ONbook;ALTERTABLE删除索引2.使用ALTERTABLE语句删除索引ALTER[IGNORE]TABLE表名

|DROPPRIMARYKEY /*删除主键*/|DROPINDEX索引名

/*删除索引*/语法格式：【例】删除Book表上的主键和mark索引。ALTERTABLEBookDROPPRIMARYKEY,DROPINDEXmark;如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。索引对查询的影响目前本书实例中所涉及的表最多只有几十行的数据，所以有没有建立索引，还体会不到查询速度上的差异，可是当一个表里有成千上万行数据的时候，差异就非常明显了。现在假设有一个表，表里只有一列，由数值1～1000的1000行组成，现在要想查找到数字1000所在的行。如果没有索引，要从第一行开始匹配，若数值不是1000，则转到下一行进行匹配，这样直到第1000行的时候才能找到数字1000所在行，也就是说服务器进行了1000次的运算。而当在该列上创建一个索引后，则可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表扫描至少快了100倍。当执行涉及多个表的连接查询时，索引将更有价值。索引的弊端首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。但是这些弊端并不妨碍索引的应用，因为索引带来的好处已经基本掩盖了它的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的。索引的设计原则选择唯一性索引为经常需要排序、分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数据量少的索引.尽量使用前缀来索引删除不再使用或者很少使用的索引数据库分区RANGE

分区：基于一个给定连续区间的列值，把多行分配给分区。LIST

分区：类似于按RANGE分区，区别是它基于列值匹配一个离散值集合中的某个值来进行选择。HASH分区：基于用户定义的表达式的返回值来进行选择，该表达式使用将要插入到表中的这些行的列值进行计算。这个表达式可以包含MySQL中有效的、产生非负整数值的任何表达式。KEY

分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。分区类型RANGE分区PARTITIONBYRANGE(表达式)（PARTITION分区1VALUESLESSTHAN(值1)，

….

PARITION分区nVALUESLESSTHAN(值n|[MAXVALUE])）语法格式：【例】将sell表的数据按订购时间进行分区，2020年前的放p1分区，2021-2022年放p2分区，2022年以后的放p3分区。altertablesellpartitionbyrange(year(订购时间))

(partitionp1valueslessthan(2020),

partitionp2valueslessthan(2022),

partitionp3valueslessthanmaxvalue);LST分区PARTITIONBYLIST(表达式)

（PARTITION分区1VALUESIN(值列表1)，

….PARITION分区nVALUESIN(值列表n）语法格式：【例】假设sell表中是否结清字段为整数类型，1表示结清，0表示未结清。将sell表的数据按是否结清进行分区，已结清的放p1分区，未结清的放p2分区。altertablesellpartitionbylist(是否结清)(partitionp1valuesin(1),partitionp2valuesin(0));HASH分区和KEY分区其中n是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包含“

PARTITIONS

n”子句，那么分区的数量会默认为1。【例】假设sell表中订单号为主键，字段为整数类型。将sell表的数据按订单号进行HASH分区，共分为3个分区。HASH分区语法格式：

PARTITIONBY[LINEAR]HASH(表达式)[PARTITIONSn]altertablesellpartitionbyhash(订单号)partitions3;HASH分区和KEY分区【例】假设sell表中订单号为主键，字段为整数类型。将sell表的数据按订单号进行KEY分区，共分为3个分区。KEY分区：altertablesellpartitionbyKey()partitions3;KEY分区和HASH分区相似，区别在以下几点：KEY分区允许多列，而HASH分区只允许一列。如果在有主键或唯一性索引的情况下，KEY分区列可不指定，默认为主键或唯一性索引列，如果没有主键或唯一性索引，则必须显性指定列。KEY分区对象必须为列，而不能是基于列的表达式。算法不一样，HASH分区，采用MOD(表达式，n)算法，而KEY分区基于的是列的MD5值。

分区管理【例7-13】在例7-10中，对sell表的数据按“是否结清”分成了2个分区，“是否结清”字段中1表示已结清，