SQLite学习笔记（17）-B-tree（3）

2.5.3 页面重用及回收(PageReuse and Vacuum )

SQLite利用一个freelist进行页面回收。当一个页面的所有记录都被删除时，就被插入到该列表。当运行VACUUM命令时，会清除freelist，所以数据库会缩小，本质上它是在新的文件重新建立数据库，而所有使用的页在都被拷贝过去，而freelist却不会，结果就是一个新的，变小的数据库。当数据库的autovacuum开启时，SQLite不会使用freelist，而且在每一次commit时自动压缩数据库。

2.5.4 B-Tree记录

B-tree中页面由B-tree记录组成，也叫做payloads。每一个B-tree记录，或者payload有两个域：关键字域(keyfield)和数据域(data field)。Key field就是rowid的值，或者数据库中表的关键字的值。从B-tree的角度，data field可以是任何无结构的数据。数据库的记录就保存在这些data fields中。B-tree的任务就是排序和遍历，它最需要就是关键字。Payloads的大小是不定的，这与内部的关键字和数据域有关，当一个 payload太大不能存在一个页面内进便保存到多个页面。

B-tree按关键字排序，所有的关键字必须唯一。表采用B+tree，内部页面不包含数据。图2.10展示了一个利用B+tree表示表的例子。

B+tree中根页面(rootpage)和内部页面(internal pages)都是用来导航的，这些页面的数据域都是指向下级页面的指针，仅仅包含关键字。所有的数据库记录都存储在叶子页面(leaf pages)内。在叶节点一级，记录和页面都是按照关键字的顺序的，所以B-tree可以水平方向遍历，时间复杂度为O(1)。

图2.10 B+tree结构（tables）

2.5.5 记录和域（Recordsand Fields）

位于叶节点页面的数据域的记录由VDBE管理，数据库记录以二进制的形式存储，但有一定的数据格式。记录格式包括一个逻辑头（logical header）和一个数据区(data segment)，header segment包括header的大小和一个数据类型数组，数据类型用来表示存储在data segment的数据的类型，如图2.11所示。

图2.11 记录结构

2.5.6 B-Tree API

B-Tree模块有它自己的API，它可以独立于C API使用。另一个特点就是它支持事务。由pager处理的事务，锁和日志都是为B-tree服务的。

2.5.6.1 访问和事务函数

sqlite3BtreeOpen:Opens a new database file. Returns a B-tree object.

sqlite3BtreeClose:Closes a database.

sqlite3BtreeBeginTrans:Starts a new transaction.

sqlite3BtreeCommit:Commits the current transaction.

sqlite3BtreeRollback:Rolls back the current transaction.

sqlite3BtreeBeginStmt:Starts a statement transaction.

sqlite3BtreeCommitStmt:Commits a statement transaction.

sqlite3BtreeRollbackStmt:Rolls back a statement transaction.

2.5.6.2 表函数

sqlite3BtreeCreateTable:Creates a new, empty B-tree in a database file.

sqlite3BtreeDropTable:Destroys a B-tree in a database file.

sqlite3BtreeClearTable:Removes all data from a B-tree, but keeps the B-tree intact.

2.5.6.3 游标函数(CursorFunctions)

sqlite3BtreeCursor:Creates a new cursor pointing to a particular B-tree.

sqlite3BtreeCloseCursor:Closes the B-tree cursor.

sqlite3BtreeFirst:Moves the cursor to the first element in a B-tree.

sqlite3BtreeLast:Moves the cursor to the last element in a B-tree.

sqlite3BtreeNext:Moves the cursor to the next element after the one it is currently

pointingto.

sqlite3BtreePrevious:Moves the cursor to the previous element before the one it is

currentlypointing to.

sqlite3BtreeMoveto:Moves the cursor to an element that matches the key value passed in as aparameter.

2.5.6.4 记录函数(RecordFunctions)

sqlite3BtreeDelete:Deletes the record that the cursor is pointing to.

sqlite3BtreeInsert:Inserts a new element in the appropriate place of the B-tree.

sqlite3BtreeKeySize:Returns the number of bytes in the key of the record that the

cursoris pointing to.

sqlite3BtreeKey:Returns the key of the record the cursor is currently pointing to.

sqlite3BtreeDataSize:Returns the number of bytes in the data record that the cursor is

currentlypointing to.

sqlite3BtreeData:Returns the data in the record the cursor is currently pointing to.

2.5.6.5 配置函数(ConfigurationFunctions)

sqlite3BtreeSetCacheSize:Controls the page cache size as well as the synchronous

writes(as defined in the synchronous pragma).

sqlite3BtreeSetSafetyLevel:Changes the way data is synced to disk in order to increase

ordecrease how well the database resists damage due to OS crashes and powerfailures.

Level 1is the same as asynchronous (no syncs() occur and there is a high probabilityof

damage).This is the equivalent to pragma synchronous=OFF. Level 2 is the default. There

is avery low but non-zero probability of damage. This is the equivalent to pragma

synchronous=NORMAL.Level 3 reduces the probability of damage to near zero but with a

writeperformance reduction. This is the equivalent to pragma synchronous=FULL.

sqlite3BtreeSetPageSize:Sets the database page size.

sqlite3BtreeGetPageSize:Returns the database page size.

sqlite3BtreeSetAutoVacuum:Sets the autovacuum property of the database.

sqlite3BtreeGetAutoVacuum:Returns whether the database uses autovacuum.

sqlite3BtreeSetBusyHandler:Sets the busy handler