Database相关概念

Where data is stored controls performance and durability.

A file storage database is saved to disk continuously. Data is organized into large pages to take advantage of block device performance characteristics. The algorithms used to access data in a file storage database offer consistent overall performance, even as the size of the database grows to exceed the size of main memory.

A memory storage database is stored primarily in memory. Direct pointers are used internally so that individual operations always complete in a predictable amount of time. For this reason, the size of a memory storage database is limited by the size of main memory.

A hybrid database uses both file and memory storage to store both disk and memory tables in the same database. In this way, applications can balance requirements for durability and performance by creating some tables as memory tables. A hybrid database is created by setting the memory storage size when opening a file storage database.

	Warning
	Avoid creating a memory storage that is larger than main memory. Virtual memory page faults will occur frequently in a large memory storage database. Instead, use a file storage and store some or all data in disk tables. The paging algorithms used for disk tables are specifically designed to minimize paging for table data.

The storage model can be 改变d easily because it does not affect the way that tables are used. The only differences are in:

The fundamental difference between disk and memory storage is the index algorithms used to sort and search for rows. Disk tables use B+ tree indexes, which have a shallow tree structure to minimize disk I/O as depicted in Figure 3, “B+ Tree Index Organization”. Reading a page from disk is an expensive operation and each node in the B+ tree is a page. The organization of a B+ tree ensures that even in a very large table, any key can be located with very few page read operations. The B+ tree contains a full copy of the data in each index column, so that full rows do not need to be loaded from disk when using an index.

Figure 3. B+ Tree Index Organization

Memory tables use T-tree indexes, which have a deep binary tree structure as depicted in Figure 4, “T-Tree Index Organization”. Without disk I/O, traversing the 节点 of the tree is an inexpensive operation. Each node contains pointers to rows in the table rather than a full copy of the index columns.

Figure 4. T-Tree Index Organization

When a table is created, one of several table types can be selected. The table type affects the overhead and performance of storing and accessing data. ITTIA DB SQL™ supports the table types listed in Table 2, “Table Types”. The default table type depends on the storage model used by the database and some storage models do not support all types of tables. The default table type for disk storage is Key Heap, while the default table type for memory storage is Memory.

The table type controls how the data is represented inside the database. However, it does not affect how the data is accessed. From an application's perspective, a table is always a collection of rows and columns with zero or more indexes.

A key heap table uses a hidden 4 byte key to identify each row. Indexes on a key heap table use a copy of this key to locate rows when fetching data, as shown inFigure 5, “Key Heap Table Organization”. One unique index can be designated as the primary key.

Figure 5. Key Heap Table Organization

Tip

The overhead of a row in a key heap table uses the following formula: 4 bytes + 2 bytes per column + 12 bytes per index + 2 bytes per index column. This is added to the size of each field that is not null in the table and in each index, which contain a copy of the indexed fields, to get the total size of the row. This calculation is valid for the current implementation and is subject to 改变 in future releases.

A clustered table, shown in Figure 6, “Clustered Table Organization”, groups rows acording to the order specified by an index. This improves performance when accessing a batch of rows with the cluster index because related rows are often stored in the same page. However, 其他 indexes on the same table do not benefit from clustering. The clustering index is specified when the table is created and cannot be 改变d without dropping the table.

Figure 6. Clustered Table Organization

A clustered table has the following benefits:

A clustered table has a few drawbacks:

The clustering index must meet the following conditions:

	Tip
	The overhead of a row in a clustered table uses the following formula: 2 bytes per column + 36 bytes per non-clustered index + 2 bytes per non-clustered index column. This calculation is valid for the current implementation and is subject to 改变 in future releases.

The most efficient table type depends on the number of indexes created on the table and the size of the primary key. A clustered table has the most compact representation for a table with only one index or an integer primary key, while a key heap table is more compact when there are many indexes and a large primay key.