最近做了一个简单的关于索引的 Demo ,遂与大家分享。之所以在文章的标题加上了“探讨”二字:
一是希望能作为园子里入门TX的学习示例(如何创建索引 + 如何创建存储过程 + ASP.NET简单的三层示例,还可以参考我的另一篇文章:ASP.NET应用程序的三层设计模型(学习))
二是希望能够抛砖引玉,听听大家对于索引如何使用的见解,以及关于数据库内(或者说是网站内部)的大量数据(或者说是百万级数据),大家是如何实现和处理搜索功能的?(当然也少不了“全文索引”的探讨) 希望不吝赐教,共同探讨 ^_^
我先简单介绍一下SQL Server中,索引的基本概念和需要注意的地方。然后把我的ASP.NET关于索引的Demo按步骤分解说明。
什么是索引?
索 引: 数据库中的索引与书籍中的索引类似,它为数据库提供一种方法来编排查询数据的路由。我们通过搜索索引找到需要的值,然后根据该值跟随指针到达包含该值的行。
一、索引存放在系统表 sysindexes中;
二、虽然可以指定数据库按照某个索引进行数据查询,但一般不需要人工指定。
SQL Server将会根据所创建的索引,自动优化查询。
索引页: 数据库中存储索引的数据页。类似于汉语字典中按拼音或笔画排序的目录页。
索引的作用:提高数据库的检索速度,改善数据库的性能。
注 意:索引自身也需要维护,并占用一定的资源,所以应该合理的使用,切忌滥用。
建立索引的列的情形:
一、该列用于频繁搜索;
二、该列用于对数据进行排序;
忌讳建立索引的列的情形:
一、该列仅包含几个不同的值;
二、包含该列的表中只有少量的几行数据;
索引的类型:
唯一索引(UNIQUE):不允许有重复值,当新的数据将使该列重复时,数据库将拒绝此数据。(创建了唯一约束,将自动创建唯一索引。建议不使用唯一索引,而使用主键约束。)
主键索引:为表定义一个主键将自动创建一个主键索引。(主键索引是唯一索引的特殊类型)
聚集索引(CLUSTERTED):表中各行的物理顺序与键值的索引顺序相同。(一个表只能包含一个聚集索引)
非聚集索引(NONCLUSTERTED):表中各行的物理顺序与键值的索引顺序不同。(一个表可以包含多个非聚集索引)
FILLFACTOR(填充因子):0~100范围内的值,指示索引页填满的空间所占的百分比。(一般很少指定)
关于全文索引
普通的 SQL 索引与全文索引的不同差异:(引用自 Microsoft 的帮助文档 )
普通 SQL 索引 |
全文索引 |
存储时受定义它们所在的数据库的控制。 |
存储在文件系统中,但通过数据库管理。 |
每个表允许有若干个普通索引。 |
每个表只允许有一个全文索引。 |
当对作为其基础的数据进行插入、更新或删除时,它们自动更新。 |
将数据添加到全文索引称为填充,全文索引可通过调度或特定请求来请求,也可以在添加新数据时自动发生。 |
不分组。 |
在同一个数据库内分组为一个或多个全文目录。 |
使用 SQL Server 企业管理器、向导或 Transact-SQL 语句创建和除去。 |
使用 SQL Server 企业管理器、向导或存储过程创建、管理和除去。 |
普通SQL索引查找的ASP.NET示例
步 骤 一 :
创建一个TestDB数据库,并为TestDB数据库创建TestTable数据表,包含ID、name字段
if exists ( select * from dbo.sysobjects where id = object_id(N ' [dbo].[TestTable] ') and OBJECTPROPERTY(id, N ' IsUserTable ') = 1)
drop table [ dbo ]. [ TestTable ]
GO
CREATE TABLE [ dbo ]. [ TestTable ] (
[ ID ] [ decimal ]( 18, 0) IDENTITY ( 1, 1) NOT NULL ,
[ name ] [ varchar ] ( 50) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
步 骤 二 :
为TestDB数据库的TestTable表的name字段,创建名称为IX_NAME 的非聚集索引
go
/* -- 检测 系统表 sysindexes 中是否存在该索引 -- */
if exists( select name from sysindexes
where name = ' IX_NAME ')
drop index TestTable.IX_NAME -- 删除索引
/* -- 创建非聚集索引,填充因子为30% -- */
create nonclustered index IX_NAME
on TestTable(name)
with fillfactor = 30
go
步 骤 三 :
创建一个存储过程mytest ,指定按索引IX_NAME进行查询
if exists ( select * from dbo.sysobjects where id = object_id(N ' [dbo].[TestProcedure] ') and OBJECTPROPERTY(id, N ' IsProcedure ') = 1)
drop procedure [ dbo ]. [ TestProcedure ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE procedure dbo.TestProcedure
@_name varchar( 50)
as
begin
select * from TestTable( index =IX_NAME) where name = @_name
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
注 意 :尽管我在这里,指定了数据库按照某个索引进行数据查询,但一般是不需要人工指定。 SQL Server 将会根据所创建的索引,自动优化查询。
步 骤 四 :
创建ASP.NET的数据访问层:DataLink.cs
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace mytest
{
public class DataLink
{
public string linkSql;
public DataLink()
{
//
// TODO: 在此处添加构造函数逻辑
//
linkSql= null;
}
public DataLink( string link)
{
linkSql=link;
}
public SqlConnection myConn;
public SqlCommand myComm;
public SqlDataAdapter myAdapter;
public DataSet _DataSet( string procedureName, string paraValue)
{
if (myConn== null)
myConn= new SqlConnection(linkSql);
myComm = new SqlCommand(procedureName,myConn);
myComm.CommandType = CommandType.StoredProcedure;
// SqlParameter para = new SqlParameter("@_name",SqlDbType.VarChar,50);
// para.Value = paraValue;
// myComm.Parameters.Add(para);
myComm.Parameters.Add( " @_name ",paraValue);
myAdapter = new SqlDataAdapter(myComm);
DataSet myDataSet = new DataSet();
myAdapter.Fill(myDataSet);
return myDataSet;
}
}
}
步 骤 五 :
创建ASP.NET的业务逻辑层:myWebForm.aspx.cs
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace mytest
{
public class myWebForm : System.Web.UI.Page
{
protected System.Web.UI.WebControls.TextBox myTextBox;
protected System.Web.UI.WebControls.Button myButton;
protected System.Web.UI.WebControls.DataGrid myDataGrid;
private void Page_Load( object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.myButton.Click += new System.EventHandler( this.myButton_Click);
this.Load += new System.EventHandler( this.Page_Load);
}
#endregion
private void myButton_Click( object sender, System.EventArgs e)
{
string strSearch = myTextBox.Text.Trim();
if( null==strSearch || ""==strSearch ) return;
string sqlLike = " workstation id=XUGANG;user id=sa;data source=.;persist security info=False;initial catalog=TestDB ";
DataLink myDataLink = new DataLink(sqlLike);
DataSet myData = myDataLink._DataSet( " TestProcedure ",strSearch); // TestProcedure 存储过程名称
myDataGrid.DataSource = myData;
myDataGrid.DataBind();
}
}
}
步 骤 六 :
关于ASP.NET的用户界面层:myWebForm.aspx
<! DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
< HTML >
< HEAD >
< title >WebForm </ title >
< meta name ="GENERATOR" Content ="Microsoft Visual Studio .NET 7.1" >
< meta name ="CODE_LANGUAGE" Content ="C#" >
< meta name ="vs_defaultClientScript" content ="JavaScript" >
< meta name ="vs_targetSchema" content ="http://schemas.microsoft.com/intellisense/ie5" >
</ HEAD >
< body MS_POSITIONING ="GridLayout" >
< form id ="Form1" method ="post" runat ="server" >
< asp:TextBox id ="myTextBox" runat ="server" ></ asp:TextBox >
< asp:Button id ="myButton" runat ="server" Text ="搜 索" ></ asp:Button >
< asp:DataGrid id ="myDataGrid" runat ="server" ></ asp:DataGrid >
</ form >
</ body >
</ HTML >
我没有提供 Demo 下载,只要按照步骤 Copy 代码便能实现。
( Visual Studio .NET 2003 + SQL Server 2000 环境下,测试通过。 )
相关文章推荐:
蝈蝈俊.net 索引基础知识(系列)
SQL Server 索引基础知识(1)--- 记录数据的基本格式
SQL Server 索引基础知识(2)----聚集索引,非聚集索引
SQL Server 索引基础知识(3)----测试中一些常看的指标和清除缓存的方法
SQL Server 索引基础知识(4)----主键与聚集索引
SQL Server 索引基础知识(5)----理解newid()和newsequentialid()
SQL Server 索引基础知识(6)----索引的代价,使用场景
SQL Server 索引基础知识(7)----Indexing for AND
SQL Server 索引基础知识(8)--- 数据基本格式补充
以及
SELECT * 的真相: 索引覆盖(index coverage)
在表连接查询的时候,如果select中包含了没有建立索引的列,可能对效率产生严重影响
本文转自钢钢博客园博客,原文链接:http://www.cnblogs.com/xugang/archive/2008/01/18/1044352.html,如需转载请自行联系原作者