数据技术应用 9

项目8使用索引优化查询——拓展项目任务单任务名称“新闻发布系统”数据库索引设计与创建任务编号3-3任务目标会根据需求，合理设计关系表的索引；能够熟练创建索引；在实际应用开发中能够使用索引提高查询效率；了解查看索引碎片及维护索引的方法。任务描述请根据需求结合索引设计原则，完成新闻发布系统索引的设计与创建。具体任务如下：1.设计各关系表的索引。2.创建用户信息表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。（2）在用户编号列上创建非聚集索引。3.创建新闻信息表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。（2）在新闻ID列上创建非聚集索引。（3）在新闻标题列上创建非聚集索引。4.创建新闻评论表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。（2）在评论编号列上创建非聚集索引。5.创建新闻栏目分类表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。（2）在栏目ID列上创建非聚集索引。6.（选做）查看索引碎片：（1）查看新闻信息表的碎片情况，如果有，请使用删除重建方法整理碎片。（2）查看用户信息表的碎片情况，如果有，请使用重组索引方法整理碎片。（3）查看新闻评论表的碎片情况，如果有，请使用重建索引方法整理碎片。7.（选做）碎片整理综合训练：（1）创建一个用户信息表的副本，并使用WHILE循环向其中添加100000条记录。（2）查看该表副本的碎片情况，并记录下来。（3）删除其中10000条记录，再查看其碎片情况并记录下来。请使用三种方法整理碎片，查看整理效果，记录并分析三种方法的区别。完成方式¨小组协作完成¨个人单独完成负责人参与人员工作流程1.设计各关系表的索引。CREATETABLEtb_users(user_idINTIDENTITYPRIMARYKEY,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100)NOTNULL,created_atDATETIMEDEFAULTGETDATE());CREATETABLEtb_news(news_idINTIDENTITYPRIMARYKEY,titleNVARCHAR(200)NOTNULL,type_idINTNOTNULL,contentNVARCHAR(MAX)NOTNULL,author_idINTNOTNULL,publish_dateDATETIMEDEFAULTGETDATE(),FOREIGNKEY(type_id)REFERENCEStb_types(type_id),FOREIGNKEY(author_id)REFERENCEStb_users(user_id));CREATETABLEtb_comments(comment_idINTIDENTITYPRIMARYKEY,news_idINTNOTNULL,user_idINTNOTNULL,titleNVARCHAR(100)NOTNULL,contentNVARCHAR(MAX)NOTNULL,publish_timeDATETIMEDEFAULTGETDATE(),FOREIGNKEY(news_id)REFERENCEStb_news(news_id),FOREIGNKEY(user_id)REFERENCEStb_users(user_id));CREATETABLEtb_types(type_idINTIDENTITYPRIMARYKEY,type_nameNVARCHAR(50)NOTNULL);2.创建用户信息表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。IFNOTEXISTS(SELECT*FROMsys.indexesWHEREobject_id=OBJECT_ID('tb_users')ANDindex_id<=1)BEGINALTERTABLEtb_usersADDCONSTRAINTPK_tb_usersPRIMARYKEYCLUSTERED(user_id);END（2）在用户编号列上创建非聚集索引。CREATENONCLUSTEREDINDEXIX_tb_users_user_idONtb_users(user_id);3.创建新闻信息表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。IFNOTEXISTS(SELECT*FROMsys.indexesWHEREobject_id=OBJECT_ID('tb_news')ANDindex_id<=1)BEGINALTERTABLEtb_newsADDCONSTRAINTPK_tb_newsPRIMARYKEYCLUSTERED(news_id);END（2）在新闻ID列上创建非聚集索引。CREATENONCLUSTEREDINDEXIX_tb_news_news_idONtb_news(news_id);（3）在新闻标题列上创建非聚集索引。CREATENONCLUSTEREDINDEXIX_tb_news_titleONtb_news(title);4.创建新闻评论表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。IFNOTEXISTS(SELECT*FROMsys.indexesWHEREobject_id=OBJECT_ID('tb_comments')ANDindex_id<=1)BEGINALTERTABLEtb_commentsADDCONSTRAINTPK_tb_commentsPRIMARYKEYCLUSTERED(comment_id);END（2）在评论编号列上创建非聚集索引。CREATENONCLUSTEREDINDEXIX_tb_comments_comment_idONtb_comments(comment_id);5.创建新闻栏目分类表的索引：（1）查看现有索引状况，如果无聚集索引，则创建聚集索引或建立主键。IFNOTEXISTS(SELECT*FROMsys.indexesWHEREobject_id=OBJECT_ID('tb_types')ANDindex_id<=1)BEGINALTERTABLEtb_typesADDCONSTRAINTPK_tb_typesPRIMARYKEYCLUSTERED(type_id);END（2）在栏目ID列上创建非聚集索引。CREATENONCLUSTEREDINDEXIX_tb_types_type_idONtb_types(type_id);6.（选做）查看索引碎片：（1）查看新闻信息表的碎片情况，如果有，请使用删除重建方法整理碎片。SELECTindex_id,avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('tb_news'),NULL,NULL,'DETAILED')WHEREavg_fragmentation_in_percent>30;ALTERINDEXALLONtb_newsREBUILD;（2）查看用户信息表的碎片情况，如果有，请使用重组索引方法整理碎片。SELECTindex_id,avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('tb_users'),NULL,NULL,'DETAILED')WHEREavg_fragmentation_in_percent>10ANDavg_fragmentation_in_percent<=30;ALTERINDEXALLONtb_usersREORGANIZE;（3）查看新闻评论表的碎片情况，如果有，请使用重建索引方法整理碎片。SELECTindex_id,avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('tb_comments'),NULL,NULL,'DETAILED')WHEREavg_fragmentation_in_percent>30;ALTERINDEXALLONtb_commentsREBUILD;7.（选做）碎片整理综合训练：（1）创建一个用户信息表的副本，并使用WHILE循环向其中添加100000条记录。SELECT*INTOtb_users_copyFROMtb_usersWHERE1=0;DECLARE@iINT=1;WHILE@i<=100000BEGININSERTINTOtb_users_copy(username,email)VALUES('user'+CAST(@iASVARCHAR),'user'+CAST(@iASVARCHAR)+'@');SET@i=@i+1;END（2）查看该表副本的碎片情况，并记录下来。SELECTindex_id,avg_fragmentation_in_percentASinitial_fragINTO#frag_resultsFROMsys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('tb_users_copy'),NULL,NULL,'DETAILED');（3）删除其中10000条记录，再查看其碎片情况并记录下来。请使用三种方法整理碎片，查看整理效果，记录并分析三种方法的区别。DELETETOP(10000)FROMtb_users_copy;索引的分析与维护索引的分析与维护

信创技能拓展知识与技能拓展1达梦数据库索引介绍：索引是与表相关的可选的结构（聚集索引除外），它能使对应于表的SQL语句执行得更快，因为有索引比没有索引能更快地定位信息。DM8索引能提供访问表的数据的更快路径，可以不用重写任何查询而使用索引，其结果与不使用索引是一样的，但速度更快。DM8提供了几种最常见类型的索引，对不同场景有不同的功能：1.聚集索引：每一个普通表有且只有一个聚集索引；2.唯一索引：索引数据根据索引键唯一；3.函数索引：包含函数/表达式的预先计算的值；4.位图索引：对低基数的列创建位图索引；5.位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；6.全文索引：在表的文本列上而建的索引。索引在逻辑上和物理上都与相关的表的数据无关，作为无关的结构，索引需要存储空间。创建或删除一个索引，不会影响基本的表、数据库应用或其他索引。当插入、更改和删除相关的表的行时，DM8会自动管理索引。如果删除索引，所有的应用仍继续工作，但访问以前被索引了的数据时速度可能会变慢。知识与技能拓展2达梦数据库创建索引：1.聚集索引：CREATECLUSTERINDEX索引名称ON表名(列名);2.唯一索引：CREATEUNIQUEINDEX索引名称ON表名(列名)STORAGE(ON表空间);知识与技能拓展3达梦数据库重建索引：当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影响访问速度。另外，当删除表的大量数据后，若不再对表执行插入操作，索引所处的段可能占用了大量并不适用的簇，从而浪费了存储空间。可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，从而提高访问效率和空间效率。DM8提供的重建索引的系统函数为：SP_REBUILD