| 八神苍月之Sybase鏖战篇 |
|
|
|
|
| 来源： 作者： 添加日期：2005-9-4 19:19:56 点击次数： |
|
大家好，我是八神苍月，这篇文章是我自己工作时整理的一些笔录，希望对大家能够有些帮助，其中所有的操作都是基于Sybase SQL Server数据库的（以后简称SQL Server），所以有些代码在微软SQL Server里可能不再支持，大家也可以借此比较一下两个数据库的差异，怎么说两者也算得上是对孪生兄弟（市场上Sybase的身价好象更高一点哦，一个Sybase DBA的培训费用高得让我明白"金钱是万能的"这句话原来是永恒的哲理,而关于他的技术资料我在广州购书中心可是一本都没看到）水平有限，错误之处请谅解！！！
Distinct关键字会强迫服务器执行额外的排序和处理步骤
Select * 是许多人的爱好，这个并不好，性能问题不用说了，如果管理员对你只开放了表中的某几列的SELECT权限，你再用他试试就知道后果
Order by 子句会使服务器在返回结果集之后增加一个附加的排序步骤，所以会引发系统性能消耗，如果在排序列上使用了索引，效果就不一样 ，排序方式有：desc,asc,他们只影响单列
Count(*)和count(列名)有区别的，他们返回的值可能会不同，因为后者忽略了NULL值，不信你自己试试
select type,count(*) from titles group by type having count(*) > 1，请记住这个查询语句，我在面试时已经遇到5次了，他用来展现titles表中type列重复的行，此时是聚集在type列，面试时你就看情况而定吧，这是having子句的魅力所在
删除一张表的数据，一般用不带where子句的delete,如果表的数据很多，可以使用truncate table table_name语句，他是直接释放表及其索引所分配的所有空间，这个操作是不记录日志的（记录还是有的，他会报告系统你释放了一些数据页，但你不要指望事故发生时系统能为你找回这几页来），所以还是三思而行，免得到时候哭都没用，可别来找我啊，另外，这个操作也不会引发表上的trigger,对于有foreign key约束的表也不会成功
索引我讲不了，大家会建就算了，顺便看看我精华贴的那一篇，其中关于聚集索引的介绍有点错误，表上插入数据，如果不进行页分割的话，聚集索引的系统开销是要小于非聚集索引的，同一列上的聚集索引将比非聚集索引耗用更少的空间，聚集索引还有个独特的功能，就是移动表的位置，由于聚集索引的叶层就是数据，所以他建在哪里，表数据就跟着走到哪里，比如你的数据库有3个逻辑设备，你又添加了一个新设备来增大空间，数据库中有张表特别大，你想将他移到新设备上，给他一个新环境，就可以在这张表上建立聚集索引，索引最后的落脚点指定到一个段，这个段映射那个新设备，这样大表就聚集到目的地了，段的概念和MS SQL SERVER中的filegroup一样，你将数据库对象生成脚本时会看到经常 create句末有个on primary的东东，他是指对象创建到默认的primary文件组。你如果真的要学好索引设计，建议去了解一下SQL Server的存储结构，页是怎样分割的，在有索引的表上插入数据时数据库都干了些什么，查询优化器是怎么提供查询方案的，等等，这可是块不好啃的贱骨头，不过啃完了消化透了你就可以向比尔盖次投求职信了，如果有回信，COPY一份给我也沾沾光
text/image数据类型是两个比较特殊的类型，他们允许插入图片或大的文本信息，在SQL Server中，数据是以页的形式存储的，每页的大小是2K，而单行的信息是不能超过1页的，但有的表中某列需要更多的空间，比如备注，往往会超过255（varchar的最大长度），这时你就的使用text类型的字段了，前面说过一行不能超过一页，当使用text/image类型时，行的后面就会生成一个指针，他指向另外的一页，这一页就是存放text/image数据的地方，如果一页不够的话，就在后面添加新页，通过页链连接起来（最大可以达到2GB），所以要操作text/image字段，就要获得上面说的那个指针，一般将text/image字段设为NULL，这样做的话在初始化每行时系统就不会给你生成那个指针，换句话说他节省了2K的空间，这是很宝贵的拉！！！下面这个例子是微软帮助里的，写的很清楚拉（遇到text/image情况我总是千方百计的要求用户精简他们的备注，甚至骗他们说数据库最多只能这样拉，超过了会崩溃的，幸好他们相信，要知道这种类型有时候会很麻烦的，特别是对前台应用程序，有些程序员将文件以二进制的形式存放在数据库中，这真是极大的空间浪费，你可以将文件存在操作系统上，在数据库中保存文件对应的物理路径，然后前台应用程序按照路径去处理文件啊，要知道操作系统对文件的存储和处理能力可是你数据库远远比不上的，安全方面也不是太大的问题啊）
USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO
SQL Server的一件利器：sp_help,请大家记得使用他，在没有参数时，他显示当前数据库里的所有对象，当传递一个表名时，他显示表的字段，索引，约束关系，所在的segment等等，当传递一个存储过程时，他显示参数，功能够强了吧，省得你在图形界面里找的发疯！！！
Identity标识栏并不维护列的唯一性，他只是顺序的生成序号，如果Identity列使用的是tinyint类型，你的表最多只能有256行，所以使用时找个范围大点的数据类型
视图我用的比较少，对他的理解也就比较肤浅，你可以把他当成一张表，他反映的是母表的信息，需要注意的是视图的更新限制，with check option的含义，乱七八糟的一大堆，微软的联机帮助上很详细的！！！
定义一个局部变量declare @name varchar(20),向他付值：select @name = pub_name from…where…,如果select语句返回多行，@name被赋予最后一行所返回的值，如果select语句没有检索到数据，不要以为 @name会被赋予null值，他将保持select执行之前的值不变，如果你不知道这点，这可能会引起麻烦
创建一个数据库，这可能是某些人的拿手好戏，有的人说他学习SQL Server后就只会建库，点击下鼠标，什么都来了，确实是这样，数据库本来就可以那么建立，我只是想写出我自己的建库方法，你不一定要采纳
Sybase数据库一般是装在UNIX下面的，在别人公司你别指望可以摸到那台机器，如果你只认识WIN2000，就是摸到了你也弄不出个所以然来，他们经常会给你开放一个图形的客户端，你只要有SA的帐号，就可以连上服务器干活了，我比较倾向于字符界面的操作，自己先写好脚本，然后根据实际情况修改一下，感觉不比图形界面慢，调试起来也非常直接。
Sybase里创建数据库大致分为三步1：创建逻辑设备2：在相应的逻辑设备上建立数据库3：配置数据库选项
Disk init
Name = ‘data_device_1’,
Physname = ‘/dev/rsd03’,
Vdevno = 3,
Size = 512000
Disk init
Name = ‘log_device_1’,
Physname = ‘/dev/rsd04’,
Vdevno = 4,
Size = 512000
Create database test_db
On data_device_1 = 732
Log on log_device_1 = 120
上面的语句很清楚了，需要注意的是初始化逻辑设备时大小是按页计算，创建数据库时是按兆来计算，指定设备物理名时可以是原始分区或操作系统文件，如果是文件的话一定要保证这个文件不存在，SQL SERVER将为你创建他，另外你也必须有这个目录的写权限。在UNIX里请相信这句话：没有消息就是最好的消息，如果指令执行了5，6秒还没什么反映的话就说明他成功运行中了，好了，让他创建吧，我要去看部A片再过来，初始化分页得花好久的哦，如果想快一点，请在 create部分接上for load子句。
数据库还在创建之中，片子也正在热播，收看率颇高，声音也够大，但我还是回来了，够专业吧（别丢臭鸡蛋，我今天刚换的新衫），总觉得这样介绍数据库创建太敷衍了，这和点击一下鼠标没什么区别，好了，入正题吧
一个简单的create database db_name就可以创建数据库，他将在默认的逻辑设备上创建一个大小和model数据库一样的DB，这样你的数据库就和master数据库同房了，这并不是什么好事，建议为数据库指定别的多个设备名，将日志和数据分开，这样你可以获得较大的I/O性能，也避免了日志和数据在一棵树上掉死的情况（当然逻辑设备应该映射不同的磁盘驱动器），两个磁盘起火比一个磁盘起火的可能性会小很多，所以分开数据和日志，你总有办法抢出其中的一个，万一，两个都起火，你就忙着双抢吧（做为一个DBA，数据库都让你整的出火了，水平算是练到家了，换了是我，我会抓住备份这根救命稻草，不过说不准会将桌上的大瓶可乐浇上去“灭火”）
如果你不想让默认设备是master的话，可以改变他（省缺时就是master设备，建议装好服务器后就更改）
exec sp_diskdefault data_device_1,defaulton
exec sp_diskdefault master,defaultoff
查看设备信息，使用：sp_helpdevice
删除设备：sp_dropdevice 前提是先干掉他上面驻留的数据库
必要的话可以对设备进行磁盘镜象，Sybase支持软件级的镜象，相当于你在磁盘上拿出块地方来存放被镜象设备的一个副本，这个地方不能小于被镜象设备的大小，你可以对master设备镜象，这样可以使master设备在失败后，而你又没有master数据库备份的情况下感到欣喜若狂，看看下面的代码：
disk mirror
name = ‘master’
mirror = ‘/dev/rsd06’
这里rsd06可能是原始的磁盘，而不是文件系统的文件，这样/dev/rsd06这块地方就划给了master设备，用来存放数据副本，当master设备失败后，你可以试着从镜象设备启动数据库，比如master设备的物理路径是/dev/rsd08，请telnet上数据库UNIX服务器，进入到数据库的bin目录：
/home/Sybase/bin/dataserver –d /dev/rsd08 –r /dev/rsd06
这样数据库就会从镜象的设备启动了
解除磁盘镜象，请用下面的语句：
disk unmirror
name = ‘device_name’,
side = ‘primary|secondary’,
mode = ‘retain|remove’
数据库的选项配置，唉，要是会贴图多好啊，我到现在还是不知道这个网站是怎么贴图的，这人蠢了就是没办法，偷懒着写吧
sp_dboption db_name ,“option”,{true|false}
数据库创建了他的省缺选项都是套用MODEL数据库的，一般所有选项都是FALSE，TEMPDB的select into/bulkcopy是打开的，没有特别需要，你也不用打开那些选项，这里我想说的是trunc log on chkpt这个选项，他是在每个系统生成的检查点之后自动截断非活动的事务处理日志，他的好处是能防止日志增长而充满数据库空间，坏处是当数据库崩溃后你别指望可以通过日志备份恢复到事故发生点，权衡一下该用还是不该用吧，master数据库不能设置选项，大家还可以研究下sysdatabases这张表，主要的是status这一列，他表示了数据库的各个状态，各个值的意义我也说不清，印象深刻点的就是256，他说明数据库现在是可疑的（suspect）,这个时候你的数据库是起不来的，比如你在打开服务器时有个设备驱动没有插好，那么设备上的数据库将被置为suspect，这时你得改动数据库的状态位然后重起服务器才行，下面是代码：
use master
go
sp_configure “allow updates”,1
reconfigure with override
go
update sysdatabases
set status = status – 256
where dbname = your_db and status & 256 = 256
go
sp_configure “allow updates”,0
reconfigure
go
现在是重启，想快点就关掉UPS，直接拔电源吧
好拉，数据库创建就说到这拉，隔壁的片子也放完拉，数据库也成功建立拉，我也要吃饭拉，
哇，他妈的没带钱哦，各位能不能借了先？？？？
数据库会一天天的变大的，最大就是充满你指定给他的所有设备，这个时候你就得采取相应的措施：为数据库增加空间
alter database test_db
on device1 = 50
log on logdevice1 = 35
这个例子给test_db数据库增加了50M的数据空间，35M的日志空间，当然这里用到的两个设备应该是你的数据库没有引用的
请记住：Sybase数据库不能变小
tempdb是一个临时工作区，用他可以解决大型的查询或是要创建临时工作表的查询，一般是有ORDER BY或DISTINCT子句的，GROUP BY每次都会用到工作表，你不能限制用户使用临时表，临时表将保留到用户删掉他或是连接的用户退出系统为止，由存储过程创建的将在存储过程结束时删除，如果tempdb的空间不够，事务处理将终止，所以如果数据库的数据变得很大，你又经常用上面的关键字的话，就得考虑是否要为tempdb加大空间
segment,中文翻译叫段，他的作用等同与微软数据库中的filegroup,这个东西一般用的人很少，所以很多人认为他是很高深的东西，其实他很简单很简单很简单，很得我都不想说他
先来问个问题：一个数据库testdb有3个设备，data_1,data_2,data_3,建立一个表，create table table_name (…),你知道这个表到底在上面3个设备中的哪个上面吗？？？或是哪几个？？？
答案是随机的，不确定的，想知道的话你变成那张表进到数据库里晃一圈看看在说吧，这个问题是想说明：使用段可以决定数据库中对象的放置位置，控制对象的增长（有点勉强），这是使用段的第一个理由，第二个理由是段能增加IO性能，我门先来看看第一个
每个数据库创建时都有3个段system,default,logsegment，用户表，索引建立时如没有特别指定的话是划在default段的，每个对象的增长都不能操过他所属的段的大小（段的大小就是映射到这个段的所有设备的大小，段算是一个抽象的概念），比如一个房间有3头猪，其中一头长的特快，把房间都涨满了，这样别的猪想长都不行了，于是另开间房放这个大猪，他就是涨破也不影响另外猪的发育是不是？？？道理就是这样，多个对象放在同一个段中，他们将竞争空间，弱肉强食，解决方法是划多段或加空间，看看下面的代码：
//段名：segX 数据库：testdb 设备名：data_X
use testdb
go
sp_addsegment seg1,testdb,data_1
sp_addsegment seg2,testdb,data_2
sp_addsegment seg3,testdb,data_3
go
create table table_1(…) on seg1
create table table_2(…) on seg2
create nonclustered index index_1 on table_2(column_name) on seg3
go
这样表1和表2分别属于不同的段，索引在第3段，他们的增长互不相干，一个表可以放置在几个不同的段上面，方法有些笨拙，大家看到建表时指定了具体的段的，别以为表放不同段就是在后面加多几个段，语法不支持的，应该说系统机制不是这样的，首先建立表时，他只能指定一个段
create table table_1(…) on seg1
这个表已用于生产，记录数据了，3天后，
sp_placeobject seg2, table_1
再过3天
sp_placeobject seg3, table_1
再过3天，一共9天，sp_placeobject,大家看到通过他可以把表放于不同的段中，别被他的外表和语法骗了，他的作用是：把对象所有将来的增长放到指定的段中！将来，他只针对将来，对于以前的一概不管，一个表的数据存在于seg1上，然后你用sp_placeobject将表放到seg2，此时表的数据还是驻留在seg1上，只是后来添加进来的数据就会写到seg2上，现在你可以知道上面的每个3天里表的数据是怎么分布了的吧
如果你说你的大表已经不再会有数据进来了，想将他划分到几个段上，怎么做呢？？？方法还是很笨拙，就是你设法把表的数据分成几部分（这个简单吧，每次导出表的部分数据），然后删掉表上的数据，在分多次导入，每导一次就sp_placeobject下表的位置，可以了吧
如果你想将表直接从一个段移到另外一个段，而不是分成几个段的话，下面这个方法就很酷拉，比如表table_1在seg1上，目的地是seg2,看法宝：
create clustered index index_1 on table_1(column_name) on seg2
前面说过，聚集索引的叶层就是数据，他建在哪表数据就跟着走到哪，这样效果就实现了，兔死狗烹，如果你的表不需要这个索引的话，可以删除他，这没什么影响的，顶多我会鄙视下你，数据库是没任何怨言的
段的第二个好处我就不用说拉，IO性能，哎呀，他妈的说不清楚，下面就小结一下
段是数据库专有的，一个段可以映射多个设备，一个设备也可以被多个段引用
非聚集索引可以放在与表不同的段中，这样索引IO和表IO可以同时进行
一个大表可以放在不同的段中，这样可以一次读表的不同部分
文本与图象列可以放在另外的段中，他们在引用时是表名前加t，比如authors表有图象列
sp_addsegment testseg,testdb,dev_4
go
sp_placeobject textseg,” authors.tauthors”
go
还有，IO性能的实现需要不同的磁盘控制器。
好了，段就是这副德行，大家看清楚了吧，很简单的，有些事情是你作过一遍才会觉得他简单，做技术的不要羡慕别人技术的高超，你要知道，他已经老了，不中用了，而你，有的是激情与活力，这是你最骄傲的资本
啊啊啊，口干舌燥，大家鼓个掌表扬下吧
|
|
| |
