Sybase数据库简介

 

Sybase公司成立于1984年，公司名称“Sybase”取自“system”和“database”相结合的含义。Sybase公司的第一个关系数据库产品是1987年5月推出的Sybase SQLServer1.0。Sybase首先提出Client/Server 数据库体系结构的思想，并率先在Sybase SQLServer 中实现。Sybase觉得单靠一家力量，难以把SQLServer(那时不叫ASE)做到老大，于是联合微软，共同开发。后来1994年，两家公司合作终止。截止此时，应该是都拥有一套完全相同的SQLServer代码。Sybase SQLServer后来为了与微软的MS SQL Server相区分，改名叫：Sybase ASE(Adaptive Server Enterprise)，其实，应该改名字的是微软。Sybase ASE仍然保持着大型数据库厂商的地位。在电信、交通、市政、银行等领域，拥有强大的市场。

不过似乎多是大公司的遗留系统。正是上面的历史原因，Sybase中不少语法跟MS SQLServer的T-SQL很像。现在网上的Sybase资料和文档比较少，多是很多年以前的了。这个Sybase的在线帮助手册算是比较完整的了，地址是http://infocenter.sybase.com。

 

以下是手册里第十二章索引如何工作的，对Sybase索引的工作原理讲解的比较易懂。并且大部分理论应该同样适用于其它数据库，所以还是比较有参考价值的。

 

Chapter 12： How indexes work

 

Indexes are the most important physical design element in improving database performance:

• Indexes help prevent table scans. Instead of reading hundreds of data pages, a few index pages and data pages can satisfy many queries.

• For some queries, data can be retrieved from a nonclustered index without ever accessing the data rows.

• Clustered indexes can randomize data inserts, avoiding insert “hot spots” on the last page of a table.

• Indexes can help avoid sorts, if the index order matches the order of columns in an order by clause.

In addition to their performance benefits, indexes can enforce the uniqueness of data.

Indexes are database objects that can be created for a table to speed direct access to specific data rows. Indexes store the values of the key(s) that were named when the index was created, and logical pointers to the data pages or to other index pages.

Although indexes speed data retrieval, they can slow down data modifications, since most changes to the data also require updating the indexes.

 

索引可以防止全表扫描，对某些查询无需访问数据页（复合索引），聚集索引避免频繁插入新数据到最后一页，避免排序。

 

Types of indexes

Adaptive Server provides two types of indexes:

• Clustered indexes, where the table data is physically stored in the order of the keys on the index:

  • For allpages-locked tables, rows are stored in key order on pages, and pages are linked in key order.

  • For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key orderi