信息学院
实验报告
学号：114100136 姓名：熊忠飞班级：11B
课程名称：大型数据库管理系统实验名称：索引及查询效率分析
实验性质：验证性实验
实验时间：2013 年5 月2 日实验地点：睿智4号楼212 B区
一、实验目的
1、理解索引的概念和作用。
2、掌握索引的创建、更改和删除的方法。
3、了解索引前和索引后查询执行计划的差异。
4、掌握维护索引的方法。
二、实验内容及要求
1、使用对象资源管理器创建、管理索引
（1）为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80 % 。
（2）重命名索引，将索引“emp_id”重命名为“员工表_员工号”。
③删除索引“员工表_员工号”。
2、使用T-SQL语句创建、管理索引
（1）为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80 % 。
（2）重命名索引，将索引“emp_id”重命名为“员工表_员工号”。
（3）为员工参与项目表创建一个索引名为“员工_项目_index”的非聚集复合索引，索引关键字为“员
工号”，升序，项目编号，降序，填充因子50%。
（4）删除索引“员工表_员工号”和“员工_项目_index”。
3、索引前后的执行计划
（1）删除员工表中员工号上的主键。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表和员工参与项目表中的员工号都没有索引）
（2）为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序；按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU 开销。（员工表中员工号没索引，员工参与项目表中的员工号有非聚集索引）
（3）重建员工表中员工号上的主键，删除“员工参与项目_员工号”的非聚集索引。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号没有非聚集索引）
（4）为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU 开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号有非聚集索引。）
三、实验步骤及结果
（一）使用对象资源管理器创建、管理索引
1、为员工表创建一个非聚集型索引
（1）打开SQL Server 2005，在对象资源管理器中，展开“xmgl”数据库，选择员工表结点并单击右键，在快捷菜单中选择“修改”。如图：
2（2）在设计器窗口中单击右键，在快捷菜单中选择“索引/键”，则出现新建索引对话框。如图：
3
4（3）单击“添加”按钮，系统自动给出新索引名，在“标示”里重新命名为“emp_id”，然后在“常规”中选择要索引的关键字“员工表”，在填充因子中输入“80”。如图
5
6（4）单击“关闭”按钮，索引创建完毕。
72、重命名索引，将索引“emp_id”重命名为“员工表_员工号”。
8展开员工表，选择“索引”并打开，再选中“emp_id”，单击右键出现如图下拉菜单，选择“重命名”，输入“员工表_员工号”，即可重命名。
9
103、删除索引“员工表_员工号”。
11（1）如上图，选择删除，则完成索引的删除工作。
12（2）a、在对象资源管理器中，展开“xmgl”数据库，选择员工表结点并单击右键，在快捷菜单中选择“修改”。13b、在设计器窗口中单击右键，在快捷菜单中选择“索引/键”。
14c、在选定的索引列表中选择要删除的索引“员工表_员工号”，单击“删除”按钮，再单击“关闭”按钮完成索引的删除工作。
15（二）使用T-SQL语句创建、管理索引161、为员工表创建一个索引，如下：
use xmgl
go
create unique nonclustered index
emp_id on员工表(员工号)
with(fillfactor=80)
17
182、重命名索引，如下：
use xmgl
go
sp_rename'员工表.emp_id','员工表_员工号'
19
203、为员工参与项目表创建一个索引，如下：use xmgl
go
create nonclustered index员工_项目_index on员工参与项目表(员工号asc,项目编号desc)
21with(fillfactor=50)
22
234、删除创建的两个索引，如下：
use xmgl
go
drop index员工表.员工表_员工号
drop index员工参与项目表.员工_项目_index
24
（三）索引前后的执行计划
1、删除员工表中员工号上的主键。按员工姓名和项目名称查询对应的职责，然后观察执行计划信
息，计算总的I/O和CPU开销。（员工表和员工参与项目表中的员工号都没有索引。）
select姓名,项目名称,职责
from员工表,项目表,员工参与项目表
where员工表.员工号=员工参与项目表.员工号
and项目表.项目编号=员工参与项目表.项目编号
观察执行计划：
I/O=0.003125+0.003125+0.003125=0.009375。
CPU开销= 0.0000042+0.000046+0.0001581+0.0001691+0.0001581=0.0005355。
2、为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序；按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU 开销。（员工表中员工号没索引，员工参与项目表中的员工号有非聚集索引）
create nonclustered index员工参与项目表_员工号
on员工参与项目表(员工号asc)
with
fillfactor=80
select姓名,项目名称,职责
from员工表,员工参与项目表,项目表
where员工表.员工号=员工参与项目表.员工号
and项目表.项目编号=员工参与项目表.项目编号
观察执行计划：
I/O=0.003125+0.003125+0.003125=0.009375。
CPU开销=0.0178557+0.0000836+0.0001691+0.000179+0.0001581=0.0184455。
3、重建员工表中员工号上的主键，删除“员工参与项目_员工号”的非聚集索引。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号没有非聚集索引）
drop index员工参与项目表.员工参与项目表_员工号
select姓名,项目名称,职责
from员工表,员工参与项目表,项目表
where员工表.员工号=员工参与项目表.员工号
and项目表.项目编号=员工参与项目表.项目编号观察执行计划：
I/O=0.003125+0.003125+0.003125=0.009375。
CPU开销=0.0000042+0.000046+0.0001581+0.0001691+0.0001581=0.0005355。
4、为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU 开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号有非聚集索引）
create nonclustered index员工参与项目表_员工号
on员工参与项目表(员工号asc)
with
fillfactor=80
select姓名,项目名称,职责
from员工表,员工参与项目表,项目表
where员工表.员工号=员工参与项目表.员工号
and项目表.项目编号=员工参与项目表.项目编号
观察执行计划：
I/O=0.003125+0.003125+0.003125+0.003125=0.009375。
CPU开销=0.0178557+0.0000836+0.0001691+0.000179+0.0001581=0.0184455。
四、实验小结
通过本实验，掌握了索引的概念和作用，知道了创建唯一索引可以保证数据记录的唯一性，其次还知道了索引可以大大加快数据检索速度。学会了用对象资源管理器和T-SQL语句创建、更改和删除索引的方法，同时还了解了索引前和索引后查询计划的差异。在实验过程中，对T-SQL语句创建索引，要知道各代码的含义，这样才能完整无误的创建索引。其次就是查询中的执行计划，在查看查询执行计划的信息时按“Ctrl+L”查询。
五、教师评价
年月日
注：每学期至少有一次设计性实验。每学期结束请任课老师按时按量统一交到教学秘书处。
使用Oracle全文索引搜索文本 不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本.可以使用标准的INSTR 函数和LIKE操作符实现。 SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0; SELECT * FROM mytext WHERE thetext LIKE '%Oracle%'; 有很多时候，使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候.然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用oralce提供的全文检索功能建立全文检索的步骤步骤一检查和设置数据库角色首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys 的用户。步骤二赋权在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户，例： grant execute on ctx_ddl to pomoho; 步骤三设置词法分析器(lexer) Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为term)找出来，记录在一组以dr$开头的表中，同时记下该term 出现的位置、次数、hash 值等信息。检索时，Oracle 从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle 针对不同的语言提供了不同的lexer, 而我们通常能用到其中的三个： n basic_lexer: 针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if , is 等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer 分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。 n chinese_vgram_lexer: 专门的汉语分析器，支持所有汉字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term: ‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。 n chinese_lexer: 这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持utf8, 如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer. 如果不做任何设置，Oracle 缺省使用basic_lexer这个分析器。要指定使用哪一个lexer, 可以这样操作： 第一．当前用户下下建立一个preference(例：在pomoho用户下执行以下语句) exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
实验三数据处理 【实验目的】 1.学会处理表数据、查看表记录 2.学会使用SQL语句处理表数据 【实验内容】 1.使用SQL语句给课程表、成绩添加数据--INSERT语句 2.使用SQL语句给学生表、成绩表更新数据--UPDATE语句 3.使用SQL语句为学生表删除记录--DELETE语句 【实验准备】 1.复习与本次实验内容相关知识 2.对本次实验中要求自己完成的部分做好准备 【实验步骤】 特别说明：本实验中使用的数据仅为实验而已，无任何其他作用。 1.给班级表添加记录 o用自己的帐号、密码，注册并连接到SQL Server服务器。 o展开连接的服务器-->展开"数据库"-->展开你的数据库(你的学号)-->单击"表"。 o在右边的窗格内,右击班级表(U_CLASSES),在弹出的快捷菜单中,将鼠标移到"打开表(O)"上,再移到"返回所有行(A)"上单击.参见下图。 o o接着按下图输入数据,注意,ID列不用输入(为什么?)。 o
o输入完成后,若要对数据行(如：删除行)进行操作，可在某行上右击鼠标，在弹出菜单中选择要执行的命令。关闭该查询窗口。 2.修改表记录数据 o若要修改数据，可用上述方法打开数据表，直接修改即可。 3.用界面方式给学生表(U_STUDENTS)添加数据 o参照前面方法给用界面方式给学生表输入如下记录。在输入过程中，注意观察如果输入相同学号有什么现象(什么原因？),如果班级编号不输入,又会怎 样(为什么？)。 o 4.用SQL命令给课程表(U_COURSES)、成绩表(U_SCORES)添加数据、修改数据 o先运用界面方式给课程表(U_COURSES)增加一列CREDIT，数据类型为tinyint o启动数据库引擎查询（如下图所示），进入到查询编辑窗口。 o o输入(为减少输入工作量,可将下面的语句复制)如下语句并执行之,为课程表(U_COURSES)插入插入5条记录。 o INSERT INTO [U_COURSES] (COURSE,CREDIT) VALUES ('计算机文化基础',4) INSERT INTO [U_COURSES] (COURSE,CREDIT) VALUES ('C语言程序设 计',4) INSERT INTO [U_COURSES] (COURSE,CREDIT) VALUES ('数据结构',4) INSERT INTO [U_COURSES] (COURSE,CREDIT) VALUES ('数据库原理与 应用',4) INSERT INTO [U_COURSES] (COURSE,CREDIT) VALUES ('SQL Server',3) o输入"SELECT * FROM U_COURSES"查看课程表记录。
索引的是一种功能 索引是个既稳定又开放的信息结构，它有十一种功能。 1 分解功能 把文献中的资料单元(如篇名、机构、短语、概念、物名、地名、书名、人名、字词、符号等)一一分解，这就是索引的分解功能。它是索引工作的起跑线和索引编纂的基础，没有对文献内容的这种分解功能，就没有索引。 过去有些反对索引的人说，索引是把古人的著书“凌迟碎割”。他们对索引法的反对，实出于对流传已久的那种落后的皓首穷经的陋习的偏爱和对新的治学方法的无知，洪业曾鄙视他们为卧于涸辙的鲋鱼，以升斗之水济命，而不知西江水之可羡。虽然如此，但他们所谓的索引是把古人著书“凌迟碎割”的形象说法，却从反面十分正确地道破了索引的分解功能。 分解功能是索引作用于文献的特殊功能，是它和其他检索工作不同之处。 2 梳理功能 每种文献都包容着许多不同性质的资料单元，它们在文献中基本呈无序的状态。把这些无序状态的资料单元按外表特征或内容性质进行各归其类的整理，这就是索引的梳理功能。章学诚早就发现了这种功能，他在给《族孙守一论史表》信中要求其在治二十四史年表时一并把廿二史列传中的人名编成索引，两者互为经纬，这样便可使考古之士，于纷如乱丝之资料中，忽得梳通栉理。 梳理功能是索引分解的后继。如果只有分解功能而没有梳理的功能，那么分解功能就没有价值。 梳理是对资料单元的初分。如是字序，只要按笔划或音序归类即可；如是类序只要按大类归纳即可。就像小姑娘梳头，先把长发梳顺，而编什么辫子或梳什么发型则是下一步的要求了。 3 组合功能 把梳理后的资料单元按照分类的要求，严密地组织它们的类别层次以及类目下的专题和同类目下款目的序列关系；或按字序的要求，严密地把标目的结构正装或倒装、考虑限定词对标目的限定和修饰的级数、或考虑字序和类序相结合的可能。此外，不论是类序或字序都要考虑参照系统的建立方案，使相关款目形成网络，使用户检索的眼界得以拓宽。这些，都是索引的组合功能。 过去，国外的同行曾把圣经的页边索引以“串珠”命名；我国有人曾把本草的方剂编成索引，以“针线”命名，“串珠”和“针线”是索引组合功能很形象的描绘。它使文献资料单元成为一串串的明珠，成为被针线贯穿起来的资料单元的珍品。 4 结网功能 对某个领域的文献进行有计划的索引编纂，利用类型的结构从各种不同的角度和层次对这些文献的内容进行纵横交错和多维的揭示和组合，使之形成一个检索这些文献中的各种不同性质的资料单元的网络。这就是索引的结网功能。 由“主表”和“词族索引”、“范畴索引”、“英汉对照索引”等所组成的《汉语主题词表》是由几种不同性质的索引构建的一个主题词间的联系、辨析主题词词义和被标引的文献主题概念是否精确的一个隐含的语义网络，它对文献中的资料单元产生族性检索和扩大检索途径的作用。这个网络的结构和作用就是运用索引结网功能的一个范例。
MySQL索引分析和优化列：
由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！ 那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。
下面我们就来看看这个EXPLAIN分析结果的含义。 table：这是表的名字。 type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明： “对于每一种与另一个表中记录的组合，MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀，或者如果键不是UNIQUE或PRIMARY KEY类型（换句话说，如果连接操作不能根据键值选择出唯一行），则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录，则ref是一种好的连接类型。” 在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。 如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。 possible_keys： 可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“firstname”）。默认索引名字的含义往往不是很明显。 Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。 key_len：索引中被使用部分的长度，以字节计。在本例中，key_len是102，其中firstname 占50字节，lastname占50字节，age占2字节。如果MySQL只使用索引中的firstname部分，则key_len将是50。 ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。 rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。 Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL 只是提醒我们它将用WHERE子句限制搜索结果集。 索引的缺点 到目前为止，我们讨论的都是索引的优点。事实上，索引也是有缺点的。 首先，索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。 第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。 【结束语】在大型数据库中，索引是提高速度的一个关键因素。不管表的结构是多么简单，一次500000行的表扫描操作无论如何不会快。如果你的网站上也有这种大规模的表，那么你确实应该花些时间去分析可以采用哪些索引，并考虑是否可以改写查询以优化应用。要了解更多信息，请参见MySQL manual。另外注意，本文假定你所使用的MySQL是3.23版，部分查询不能在3.22版MySQL上执行。
本文介绍了数据库索引，及其优、缺点。针对MySQL索引的特点、应用进行了详细的描述。分析了如何避免MySQL无法使用，如何使用EXPLAIN分析查询语句，如何优化MySQL索引的应用。 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它 们包含着对数据表里所有记录的引用指针。 注：[1]索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。[2]另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。 从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但MySQL把同一个数据表里的索引总数限制为16个。 1. InnoDB数据表的索引 与MyISAM数据表相比，索引对InnoDB数据的重要性要大得多。在InnoDB数据表上，索引对InnoDB数据表的重要性要在得多。在InnoDB数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的苊、基础。"数据行级锁定"的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到(但不限于)SELECT...LOCK IN SHARE MODE、SELECT...FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。 出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。 2. 限制 如果WEHERE子句的查询条件里有不等号(WHERE coloum != ...)，MySQL将无法使用索引。 类似地，如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = ...)，MySQL也将无法使用索引。 在JOIN操作中(需要从多个数据表提取数据时)，MySQL只有在主键和外键的数 据类型相同时才能使用索引。
我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的[url=javascript:;]技术[/url]，但是最有效的莫过于恰当地使用索引了。在MySQL 的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。 本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论MySQL 的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。） 索引对单个表查询的影响 索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000 行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。 例如对下面这样的一个student表： mysql>SELECT * FROM student +------+---------+---------+---------+---------+ | id | name | english | chinese | history | +------+---------+---------+---------+---------+ | 12 | Tom | 66 | 93 | 67 | | 56 | Paul | 78 | 52 | 75 | | 10 | Marry | 54 | 89 | 74 | | 4 | Tina | 99 | 83 | 48 | | 39 | William | 43 | 96 | 52 | | 74 | Stone | 42 | 40 | 61 | | 86 | Smith | 49 | 85 | 78 | | 37 | Black | 49 | 63 | 47 | | 89 | White | 94 | 31 | 52 | +------+---------+---------+---------+---------+ 这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生： mysql>SELECT name,english FROM student WHERE english<60; +---------+---------+ | name | english | +---------+---------+ | Marry | 54 | | William | 43 | | Stone | 42 | | Smith | 49 |
—94— 一种基于Lucene 的中文全文检索系统 苏潭英1，郭宪勇2，金 鑫3 （1. 解放军信息工程大学电子技术学院，郑州 450004；2. 北京飞燕技术公司，北京 100072；3. 解放军通信指挥学院，武汉 430010）摘 要：在开源全文索引引擎Lucene 的基础上，设计了一个中文全文检索系统模型，该模型系统由7个模块组成，索引模块、检索模块是其中的核心部分。论述了模型的整体结构，分析设计了索引及检索模块，通过具体的索引技术和检索技术来提高整个系统的检索效率。该系统增加了加密模块，实现对建立的全文索引进行加密处理，增强了信息的安全性。 关键词：全文检索；Lucene ；倒排索引 Chinese Full-text Retrieval System Based on Lucene SU Tan-ying 1, GUO Xian-yong 2, JIN Xin 3 (1. Institute of Electronic Technology, PLA Information Engineering University, Zhengzhou 450004; 2. Technology Company of Beijing Feiyan, Beijing 100072; 3. Institute of PLA Communication Command, Wuhan 430010) 【Abstract 】This paper proposes a model of Chinese full-text retrieval system based on Lucene which is an open source full-text retrieval engine,and expatiates its frame. This model is composed of seven modules, among which the index module and the search module are the core parts. It designs them concretely, and improves the search efficiency of the full-text retrieval system with index technology and search technology. The system model concludes an encryption module to encrypt the index and increases the system security. 【Key words 】full-text retrieval; Lucene; inverse index 计 算 机 工 程Computer Engineering 第33卷 第23期 Vol.33 No.23 2007年12月 December 2007 ·软件技术与数据库· 文章编号：1000—3428(2007)23—0094—03 文献标识码：A 中图分类号：TP391 1 中文全文检索系统 全文检索技术是一个最普遍的信息查询应用，人们每天在网上使用Google 、百度等搜索引擎查找自己所需的信息，这些搜索引擎的核心技术之一就是全文检索。随着文档处理电子化、无纸化的发展，图书馆、新闻出版、企业甚至个人的电子数据激增，如何建立数据库、管理好自己的数据，是亟待解决的问题，而全文检索是其中一个非常实用的功能。全文检索产品实际上是一个内嵌该项技术的数据库产品[1]。 西文的全文检索已有许多成熟的理论与方法，其中，开放源代码的全文检索引擎Lucene 是Apache 软件基金会Jakarta 项目组的一个子项目，它的目的是为软件开发人员提供一个简单易用的工具包，方便在目标系统中实现全文检索的功能。很多项目使用了Lucene 作为其后台的全文索引引擎，比较著名的有： (1)Jive ：Web 论坛系统； (2)Cocoon ：基于XML 的Web 发布框架，全文检索部分使用了Lucene ； (3)Eclipse ：基于Java 的开放开发平台，帮助部分的全文索引使用了Lucene 。 Lucene 不支持中文，但可以通过扩充它的语言分析器实现对中文的检索。本文在深入学习研究Lucene 的前提下，设计了一个中文的全文检索系统，对其核心的索引模块和检索模块进行了阐释，并添加了加密模块对索引信息加密，增强了系统的安全性。 2 系统的总体结构 本模型总体上采用了Lucene 的架构。Lucene 的体系结构如表1所示，它的源代码程序由7个模块组成。 表1 Lucene 的组成结构 模块名 功能 org.apache.Lucene.search 搜索入口 org.apache.Lucene.index 索引入口 org.apache.Lucene.analysis 语言分析器 org.apache.Lucene.queryParser 查询分析器 org.apache.Lucene.document 存储结构 org.apache.Lucene.store 底层IO/存储结构 org.apache.Lucene.util 一些公用的数据结构 本文通过扩充Lucene 系统来完成中文的全文检索系统，Lucene 包含了大量的抽象类、接口、文档类型等，需要根据具体应用来定义实现，本文对其作了如下扩充修改： (1)按照中文的词法结构来构建相应的语言分析器。Lucene 的语言分析器提供了抽象的接口，因此，语言分析(analyser)是可以定制的。Lucene 缺省提供了2个比较通用的分析器SimpleAnalyser 和StandardAnalyser ，但这2个分析器缺省都不支持中文，因此，要加入对中文语言的切分规则，需要对其进行修改。 (2)按照被索引的文件的格式对不同类型的文档进行解析，进而建立全文索引。例如HTML 文件，通常需要把其中的内容分类加入索引，这就需要从org.apache.lucene.子document 中定义的类Document 继承，定义自己的HTMLDocument 类，然后将之交给org. apache.lucene.index 模块写入索引文件。Lucene 没有规定数据源的格式，只提供 作者简介：苏潭英(1981－)，女，硕士研究生，主研方向：数据库全文检索；郭宪勇，高级工程师；金 鑫，硕士研究生 收稿日期：2007-01-10 E-mail ：sutanyingwendy@https://www.doczj.com/doc/8014371520.html,
记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. 使用索引，在一般情况下，将能明显提高查询的性能，但系统为维护索引，也必将增加许多额外的开销。所以，何时应建立索引，查询时是否使用索引，对系统性能的影响将是非常大的。在这里，我想对这个问题谈一下自己的认识。 首先，在下列情况下，不适合建立索引： 1、表的规模不大，在这种情况下，直接查找表的开销比搜索索引 再定位的开销要小。 2、表被频繁更新，在这种情况下，维护索引的开销要大于使用索 引所带来的性能提高。 3、表上已经建立了许多索引。 4、用户的查询方式经常发生变化。 上述这些情况都是比较直观的，但是，即使建立了索引，在具体查 询时，系统也未必会使用该索引。 不管是何种数据库系统，其查询优化过程由两个层次构成：代数优 化（或称基于规则的优化）和物理优化（或称基于代价的优化）（部分 数据库系统可能不含物理优化过程）。 代数优化是使用一组预定义的规则来对查询进行优化，在这种优化 方式下，如果表上建有索引，系统将使用该索引。 物理优化是在代数优化的基础上，根据物理统计信息，来估计各种 执行方案的执行代价，从中选取一种最优（代价最小）的执行方案。在 这种优化方式下，如果表上建有索引，是否使用索引，将取决于查询的 “选中度”（selectivity）。 什么是选中度？举个例子，假设表中有一名为“年龄”的字段，有 一查询需要查出该表中所有“年龄”不超过50岁的记录，如果表中有70% 的记录满足这一条件，则称该查询的选中度为70。 当选中度超过某一预先给定的值P（P的大小取决于系统的具体实现） 时，遍历整个表的开销比搜索索引再定位的开销要小，此时系统将不使 用索引。 通过统计字段的值分布，可以估计查询的选中度，如果它大于P，系 统将不使用索引，直接遍历表。这是一种非常重要的统计信息，它还可 用于估计连接操作结果集的大小。 当然，当查询比较固定时，用户也可以根据自己对应用的理解预先估
一、数据查询-----单表查询 (1)查询语句格式 Select [all|distinct] <目标列表达式> [,<目标列表达式>]…… From <表名或视图> [,<表名或视图>]…… [where <条件表达式>] [group by <列名1> [having <条件表达式>]] [order by <列名2> [asc|desc]] ; 注：[all|distinct]中all为缺省值，取消结果中的重复列则用distinct； [asc|desc]中asc为缺省值，表示按照升序排列。对于空值，若按照升序排，则含空值的元组显示在最后面；若按降序排，则空值的元组最先显示。 (2)查询指定列 a)查询部门表dept中所有部门的详细信息，并且列名用汉字表示。 select DNO,DNAME,ADDR from dept; b)查询部门表dept中人力资源部的部门编号。 select DNO from dept where DNAME='人力资源部'; <目标列表达式>中各个列的先后顺序可以与表中的顺序不一致. (3)查询全部列 查询全体学生的详细记录 Select * From Student ; (4)将查询结果的列名用别名显示 查询部门表dept中所有部门的详细信息，并且列名用汉字表示。 select DNO部门编号,DNAME部门名称,ADDR部门地址 from dept; (5)在查询的结果中插入新的一列用来显示指定的内容 Select Sname NAME ,’Year of Birth:’BIRTH ,Sbirth BIRTHDAY ,Sdept DEPARTMENT From Stuent ; 则显示的结果中，每个元组的第二列均为”Year of Birth:”，此列在原数据库中是不存在的. (6)查询经过计算的值 Select 子句的<目标列表达式> 不仅可以是表中的属性列，也可以是表达式。 例：查询全体学生的姓名及其出生年月 Select Sname ,2004 – Sage /*当时年份减去年龄为出生年月
为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如： 在经常需要搜索的列上，可以加快搜索的速度； 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
mysql性能优化-慢查询分析、优化索引和配置目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) interactive_timeout 4) key_buffer_size 5) query_cache_size 6) record_buffer_size 7) read_rnd_buffer_size 8) sort_buffer_size 9) join_buffer_size 10) table_cache 11) max_heap_table_size 12) tmp_table_size
13) thread_cache_size 14) thread_concurrency 15) wait_timeout 一、优化概述 MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。 除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。 二、查询与索引优化分析 在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。 1 性能瓶颈定位 Show命令 我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈： Mysql> show status ——显示状态信息（扩展show status like ‘XXX’） Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’） Mysql> show innodb status ——显示InnoDB存储引擎的状态 Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
1. 1.索引作用 2. 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术, 能够大大提高查询效率。特别是当数据量非常大, 查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。 3. 4. 例如,有 3个未索引的表 t1、 t2、 t3,分别只包含列 c1、 c2、 c3,每个表分别含有 1000行数据组成,指为 1~1000的数值,查找对应值相等行的查询如下所示。 5. 6. SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3 7. 8. 此查询结果应该为 1000行, 每行包含 3个相等的值。在无索引的情况下处理此查询, 必须寻找 3个表所有的组合, 以便得出与 WHERE 子句相配的那些行。而可能的组合数目为 1000×1000×1000(十亿,显然查询将会非常慢。 9. 10. 如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。 11. 12. (1从表 t1中选择第一行,查看此行所包含的数据。 13. 14. (2使用表 t2上的索引,直接定位 t2中与 t1的值匹配的行。类似,利用表 t3上的索引,直接定位 t3中与来自 t1的值匹配的行。
15. 16. (3 扫描表 t1的下一行并重复前面的过程, 直到遍历 t1中所有的行。 17. 18. 在此情形下,仍然对表 t1执行了一个完全扫描,但能够在表 t2和 t3上进行索引查找直接取出这些表中的行, 比未用索引时要快一百万倍。 19. 20. 利用索引, MySQL 加速了 WHERE 子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。 21. 22.2. 创建索引 23. 在执行 CREATE TABLE语句时可以创建索引, 也可以单独用 CREATE INDEX或 ALTER TABLE来为表增加索引。 24. 25.1. ALTER TABLE 26.ALTER TABLE用来创建普通索引、 UNIQUE 索引或 PRIMARY KEY索引。 27. 28. 29. 30.ALTER TABLE table_name ADD INDEX index_name (column_list 31. 32.ALTER TABLE table_name ADD UNIQUE (column_list 34.ALTER TABLE table_name ADD PRIMARY KEY (column_list 35.
ACCESS数据库操作必须更新查询的解决办法 1、在通常情况下，Web应用程序只读属性并不影响Web系统运行。在需要写入、更新数据库时，Web程序操作数据库因权限不够会提示"操作必须使用一个可更新的查询。"这种情况可能会在NTFS分区环境下出现，FTA32一般分区不会出现。将Web应用程序放在FTA32分区下运行时，不会出现因权限等问题而导致系统不能正常运行的情况，但是其安全性不如NTFS好。因此，一般网站软件可在FTA32下测试运行，单位正式网站软件建议放置在NTFS 下运行。 2、现以XP环境下，NTFS格式为例。进入网站根目录，工具—＞文件夹选项—＞查看，将“使用简单文件共享”前的勾选去掉。 3、网站根目录赋予Everyone完全控制、读写权限。 下面以一个例子更详细的介绍解决此类问题的方法和过程 出错举例： Microsoft JET Database Engine (0x80004005)操作必须使用一个可更新的查询。/LeadBBS/inc/Board_Popfun.asp, 第569 行需要权限：服务器管理员，否则联系服务器管理员进行示例操作系统：Windows 2000 Server1.找到你存放网站的文件夹,比如你的网站存放在D:\WEB\https://www.doczj.com/doc/8014371520.html,右键点击文件夹，选择属性 2.出来新窗口，选择安全，点击按钮添加(D)
3.在出来的窗口中，找到IUSER_开头的名称，并双击，点击确定． 4.确定后的结果是这个窗口，在安全的名称列表中多了刚才选择的用户点击下面的按钮高级(V)...
5.在弹出的新小窗口中，继续点击查看/编辑(V)按钮 6.出来新窗口..
实验三：索引的创建与使用 一、实验目的： 1、理解索引的概念和索引的作用。 2、掌握创建索引的方法。 3、学会使用索引。 4、了解聚簇索引和非聚簇索引。 二、实验要求：（必做） 硬件：Intel Pentium 120或以上级别的CPU，大于16MB的内存。 软件：Windows 95/98/2000操作系统，关系数据库管理系统SQL SERVER 2000。 学时：2学时 三、实验内容： 1、用create index在学生表student的学号sno上建立聚簇索引。 2、在学生表student中，为姓名sname建立非聚簇索引。 3、在课程表的课程号Cno上建立唯一索引。 4、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号相同时 成绩为降序。 5、用drop删除学生表student的索引。 数据库设计与管理实验报告
实验名称评分 实验日期年月日指导教师 姓名专业班级学号 一、实验目的 二、实验步骤及结果 1、用create index在学生表student的学号sno上建立聚簇索引。 create clustered index stusno on student(sno); 2、在学生表student中，为姓名sname建立非聚簇索引。 create index stusname on student(sname); 3、在课程表的课程号Cno上建立唯一索引。 create unique index coucno on course(cno); 4、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号相同时成绩为降序。
在应用中加入全文检索功能 ——基于java的全文索引引擎lucene简介 作者：车东 email: https://www.doczj.com/doc/8014371520.html,/https://www.doczj.com/doc/8014371520.html, 写于：2002/08 最后更新： 版权声明：可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明 https://www.doczj.com/doc/8014371520.html,/tech/lucene.html 关键词：lucene java full-text search engine chinese word segment 内容摘要： lucene是一个基于java的全文索引工具包。 1.基于java的全文索引引擎lucene简介：关于作者和lucene的历史 2.全文检索的实现：luene全文索引和数据库索引的比较 3.中文切分词机制简介：基于词库和自动切分词算法的比较 4.具体的安装和使用简介：系统结构介绍和演示 5.hacking lucene：简化的查询分析器，删除的实现，定制的排序，应用接口的扩展 6.从lucene我们还可以学到什么 基于java的全文索引/检索引擎——lucene lucene不是一个完整的全文索引应用，而是是一个用java写的全文索引引擎工具包，它可以方便的嵌入到各种应用中实现针对应用的全文索引/检索功能。 lucene的作者：lucene的贡献者doug cutting是一位资深全文索引/检索专家，曾经是v-twin搜索引擎(apple的copland操作系统的成就之一)的主要开发者，后在excite担任高级系统架构设计师，目前从事于一些internet底层架构的研究。他贡献出的lucene的目标是为各种中小型应用程序加入全文检索功能。 lucene的发展历程：早先发布在作者自己的https://www.doczj.com/doc/8014371520.html,，后来发布在sourceforge，2001年年底成为apache基金会jakarta的一个子项目：https://www.doczj.com/doc/8014371520.html,/lucene/ 已经有很多java项目都使用了lucene作为其后台的全文索引引擎，比较著名的有： ?jive：web论坛系统； ?eyebrows：邮件列表html归档/浏览/查询系统，本文的主要参考文档“thelucene search engine: powerful, flexible, and free”作者就是eyebrows系统的主要开发者之一，而eyebrows已 经成为目前apache项目的主要邮件列表归档系统。 ?cocoon:基于xml的web发布框架，全文检索部分使用了lucene ?eclipse:基于java的开放开发平台，帮助部分的全文索引使用了lucene