网络编程
位置:首页>> 网络编程>> 数据库>> 关于数据库的alter table操作和索引概念

关于数据库的alter table操作和索引概念

 来源:asp之家 发布时间:2009-12-15 21:57:00 

标签:

  //设置已存在表中字段为auto_increment

  ALTER TABLE tablename change id id int(2) not null auto_increment;

  //添加字段并设置主键

  ALTER TABLE tabelname ADD new_field_id int(5) unsigned default 0 not null auto_increment ,ADD primary key (new_field_id);

  //加主关键字的索引

  ALTER TABLE tablename ADD primary key(id);

  //增加字段:

  ALTER TABLE table_name ADD field_name field_type;

  //删除字段

  ALTER TABLE table_name DROP field_name;

  //重命名列

  ALTER TABLE table_name CHANGE field_name1 field_name2 integer;

  //调整字段顺序

  ALTER TABLE `users` CHANGE `user_password` `user_password` varchar( 20 ) NOT NULL AFTER user_name;

  //改变列的类型

  ALTER TABLE table_name CHANGE field_name field_name bigint not null;

  ALTER TABLE infos CHANGE list list tinyint not null default '0';

  //修改原字段名称及类型:

  ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

  //重命名表

  ALTER TABLE table_name rename new_table_name;

  //级联更新 和 删除(红色部分,不区分大小写 )

  DROP TABLE IF EXISTS `mail_model`;create TABLE mail_model(id varchar(50) primary key not null ,mail_filename varchar(200),content varchar(2000))ENGINE=InnoDB DEFAULT CHARSET=gbk;

  DROP TABLE IF EXISTS `mail_model_extend`;create TABLE mail_model_extend(id int(6) auto_increment not null primary key,rid varchar(50) not null,content varchar(2000),INDEX (RID),FOREIGN KEY (RID) REFERENCES mail_model(ID) ON DELETE CASCADE ON UPDATE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=gbk;

  索引

  1.数据库引入了索引

  用户对数据库最频繁的操作是进行数据查询。一般情况下,数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时,搜索数据就需要很长的时间,这就造成了服务器的资源浪费。为了提高检索数据的能力,数据库引入了索引机制。

  2.有关“索引”的比喻

  从某种程度上,可以把数据库看作一本书,把索引看作书的目录,通过目录查找书中的信息,显然较没有目录的书方便、快捷。

  3.数据库索引实际是什么?(两部分组成)

  索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

  4.索引在表中的角色

  一个表的存储是由两部分组成的,一部分用来存放表的数据页面,另一部分存放索引页面。索引就存放在索引页面上,

  5.索引高效原理

  通常,索引页面相对于数据页面来说小得多。当进行数据检索时,系统先搜索索引页面,从中找到所需数据的指针,再直接通过指针从数据页面中读取数据。

  6.索引的分类

  在SQL Server 的数据库中按存储结构的不同将索引分为两类:簇索引(Clustered Index)和非簇索引(Nonclustered Index)。

  (1)簇索引对表的物理数据页中的数据按列进行排序,然后再重新存储到磁盘上,即簇索引与数据是混为一体,的它的叶节点中存储的是实际的数据。由于簇索引对表中的数据一一进行了排序,因此用簇索引查找数据很快。但由于簇索引将表的所有数据完全重新排列了,它所需要的空间也就特别大,大概相当于表中数据所占空间的120% 。表的数据行只能以一种排序方式存储在磁盘上,所以一个表只能有一个簇索引。

  (2)非簇索引具有与表的数据完全分离的结构,使用非簇索引不用将物理数据页中的数据按列排序。非簇索引的叶节点中存储了组成非簇索引的关键字的值和行 * 。行 * 的结构和存储内容取决于数据的存储方式。如果数据是以簇索引方式存储的,则行 * 中存储的是簇索引的索引键;如果数据不是以簇索引方式存储的,这种方式又称为堆存储方式(Heap Structure),则行 * 存储的是指向数据行的指针。非簇索引将行 * 按关键字的值用一定的方式排序,这个顺序与表的行在数据页中的排序是不匹配的。由于非簇索引使用索引页存储因此它比簇索引需要更多的存储空间且检索效率较低但一个表只能建一个簇索引,当用户需要建立多个索引时就需要使用非簇索引了。

  小结:Clustered Index 是与物理数据混在一起并对物理数据进重排,就像使用拼音查字典;Unclustered Index 是与物理数据完全分离的,利用额外空间对关键字进行重排,就像使用部首查字典。

  数据库索引应用

  一、索引的概念

  索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

  二、索引的特点

  1.索引可以加快数据库的检索速度

  2.索引降低了数据库插入、修改、删除等维护任务的速度

  3.索引创建在表上,不能创建在视图上

  4.索引既可以直接创建,也可以间接创建

  5.可以在优化隐藏中,使用索引

  6.使用查询处理器执行SQL语句,在一个表上,一次只能使用一个索引

  7.其他

  三、索引的优点

  1.创建唯一性索引,保证数据库表中每一行数据的唯一性

  2.大大加快数据的检索速度,这也是创建索引的最主要的原因

  3.加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

  4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

  5.通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。

  四、索引的缺点

  1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加

  2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大

  3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度

  五、索引分类

  1.直接创建索引和间接创建索引

  直接创建索引: CREATE INDEX mycolumn_index ON mytable (myclumn)

  间接创建索引:定义主键约束或者唯一性键约束,可以间接创建索引

  2.普通索引和唯一性索引

  普通索引:CREATE INDEX mycolumn_index ON mytable (myclumn)

  唯一性索引:保证在索引列中的全部数据是唯一的,对聚簇索引和非聚簇索引都可以使用

  CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

  3.单个索引和复合索引

  单个索引:即非复合索引

  复合索引:又叫组合索引,在索引建立语句中同时包含多个字段名,最多16个字段

  CREATE INDEX name_index ON username(firstname,lastname)

  4.聚簇索引和非聚簇索引(聚集索引,群集索引)

  聚簇索引:物理索引,与基表的物理顺序相同,数据值的顺序总是按照顺序排列

  CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH

  ALLOW_DUP_ROW(允许有重复记录的聚簇索引)

  非聚簇索引:CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

  六、索引的使用

  1.当字段数据更新频率较低,查询使用频率较高并且存在大量重复值是建议使用聚簇索引

  2.经常同时存取多列,且每列都含有重复值可考虑建立组合索引

  3.复合索引的前导列一定好控制好,否则无法起到索引的效果。如果查询时前导列不在查询条件中则该复合索引不会被使用。前导列一定是使用最频繁的列

  4.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案

  5.where子句中对列的任何操作结果都是在sql运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被sql优化器优化,使用索引,避免表搜索(例:select * from record where substring(card_no,1,4)=’5378’

  && select * from record where card_no like ’5378%’)任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边

  6.where条件中的’in’在逻辑上相当于’or’,所以语法分析器会将in (’0′,’1′)转化为column=’0′ or column=’1′来执行。我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用column上的索引;但实际上它却采用了"or策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用 column上索引,并且完成时间还要受tempdb数据库性能的影响。in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引

  7.要善于使用存储过程,它使sql变得更加灵活和高效。

0
投稿

猜你喜欢

  • 一:从版本3.23升级到4.104.10新特性:支持事务处理和存储过程升级过程的注意事项:1:升级采用直接复制安装文件的方法,简单实用,但要
  • 虽然说标题将的是首页的访问感受,但是同样适合于网站其它页面的用户体验设计,一个好的网站设计应当尽量做到首页和次页一视同仁。第一步(视觉设计)
  • 可用下列函数来产生你需要的东西,将它们发送给用户就可以啦:<%response.write makePassword(16)
  • 优秀的Stoyan Stefanov在他的新书中(《Javascript Patterns》)介绍了很多编写高质量代码的技巧,比如避免使用全
  • 如果说亲密性原则是对元素的归类组合,是将元素之间逻辑理解上的差异在视觉上表现出来,是属于信息分类的话,那么对齐原则即是在视觉上串起这些差异化
  •     這兩天﹐對xml作為數據庫產生了興趣﹐找了一些資料﹐也搞出了一點眉目﹐在這里記錄一下。算是對自己學習x
  • 其主要的优点便是无需再手工添加大量的信息了,可以指定对某一个站信息的截取进行批量录入,达到省时省力的目的。与其单纯的ASP小偷程序不同的是:
  • 今天发现了一个显示ORACLE语法的好网站。内容太多,就不一一摘录了,记在这里,也方便自己查找。http://ss64.com/ora/ 目
  • 兼容主流浏览器,独立样式表,可以很方便的进行视觉效果自定义截图:模拟下拉菜单演示代码:<!DOCTYPE html PUBLIC &q
  • 很多时候,用户可能会因为承受不了某一产品中的细节设计而放弃使用该产品,这无疑是让设计人员感很沮丧的事情,因为除去这个细节的问题外,这个产品可
  • ---- 一、 引言: ---- 回滚段是数据库的一部分,它记录数据库变更的信息。使用这些信息实现数据库的读一致性及其恢复。若回滚段出现故障
  • 视觉设计是什么,人们怎么认为它的,自己又是怎么对待和理解它,它的核心价值是什么。视觉设计,冒似很艺术,跟艺术相关的职业,给大多数人的印象是做
  • Sql Server的存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工作的一种方法,它支持用户
  • 如何使用Index Server建立一个网站导航地图?程序代码如下:<html><head><title>asp教程之网站导航 -
  • 教育信息化时代,考试成绩也要求上网公布。一次我将考试成绩制作成一个HTML文件,如图1所示,领导审查的意见是“将成绩按名次排列”,可是所有的
  • 如何搭建完整的网站架构并设计出一个出色的网站?关于这个问题,我们很难提出一个绝对权威和正确的设计思路,但任何网站的设计都需要遵循一个循序渐进
  • 这个格式是我自创的,经常有人问我为什么,这里做个简单总结:1、分类,一个模块或者同类功能定义为一类定义,每类定义之间用段落隔开。2、分级,每
  • 分离结构与表现的另一个重要方面是使用语义化的标记来构造文档内容。一个 XHTML 元素的存在就意味被标记内容的那部分有相应的结构化的意义,没
  • 如何向客户端推送“出错信息”?下面是推送代码:服务器端:on error resume nextconn=se
  • by yemoo有时在编写网页代码时发现,img底部莫名奇妙多出大约3px的空白,无论怎么调节css都不可以,今天再次遇到此问题,网上看了一
手机版 网络编程 asp之家 www.aspxhome.com