Introduction to Physical DB

by K. Yue

1. Introduction

• Data in database is stored in storage medium to provide persistence.
  • What are the storage media?
  • How are the data stored?
• I/O operations are usually the most significant factor for database operation latency, not CPU or memory operations.
• The memory hierarchy distinguishes each level of computer storage by access time. Higher level memory is usually:
  1. faster in access time,
  2. lower in volume (size),
  3. more expensive in cost, and
  4. likely to be not persistent: volatile memory.
• Relational models: tuples and relations -> File systems: records and files.
• Principle of locality:
  1. More frequently used data should be stored in memory of higher level.
  2. Data frequently accessed together should be stored close to each other.
  3. Caching may be used to enhance performance.
• Considerations of selecting secondary storage devices:
  1. Speed
  2. Volume
  3. Cost
  4. Reliability
  5. Availability
• Each type of storage devices has important characteristics that should be considered carefully for uses in database architecture.

Advanced Example:

An article on flash memory and database lists important characteristics of flash memory for database consideration:

• No in-place update: to rewrite a block, one or more sectors may need to be erased and rewritten. Erasion may take time in the mini-second ranges.
• No mechanical latency: basic operation in micro-seconds, not mini-seconds.
• Limited number of writes before wearing down.
• Asymmetric read/write: write operations may be twice as long as read operations.

2. Physical Database Design

• Block:
  • A row (tuple) can be a record in a file system.
  • To improve performance, records can be grouped and stored in blocks to maximize the use of the seek operation in a hard disk.
  • Blocking factor: the number of records in a block.
• Files can be sequential files, direct access files, or others.
• Examples of important DB file structures:
  1. B+-tree: the primary key is used to navigate through an tree index structure to reach linked terminal nodes that store records.
     1. Fast sequential access through the primary key: O(n/B) read operation, where n is the number of records and B is the blocking factor.
     2. Fast random access through the primary key: O(lg (n/B)).
  2. Hashing: an address is computed from the primary key for storage:
     1. Fast random access through the primary key: O(1).
     2. Slow sequential access through the primary key.
• DBMS may allow you to select the physical structures, sometimes known as the database engines.

3. Denormalization, Partitioning and Clustering

• Denormalization: combining tables into one table for faster access.
• Partitioning: breaking down tables for faster access (as tables are smaller).
  • Horizontal partitioning: distributing rows to component tables.
  • Vertical partitioning: distributing columns to component tables.
• Clustering: related records from different tables can be stored together in the same disk area

4. MySQL Indexes

• An index is an access path created to search for records (tuples) more efficiently.
• There is a cost in creating and maintaining an index.
• Cost effectiveness analysis, including profiling, may be used for consideration of index creation.
• In MySQL, indices can be created when a table is created: syntax.
• An index creates a physical structure to speed up searching with the indexed attributes.
  1. Benefits: faster search
  2. Costs: maintaining the index structure

Example: Using AdventureWorks, a small data warehouse example created by Microsoft. You can try out the effect of indexes.

-- using AdventureWorks

-- Basic information about AdventureWorks.address.
use AdventureWorks;
desc address;
select count(*) from address;

-- Q1
select distinct AddressLine1, City, PostalCode
from address
order by PostalCode
limit 300;

-- Q2
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055;

-- Q3
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055 or PostalCode = 83402;

-- Q4
select distinct AddressLine1, City, PostalCode
from address
where PostalCode between 90000 and 92000
limit 300;

-- create index
create index postal_index on address(postalCode);
desc address;

-- Q1
select distinct AddressLine1, City, PostalCode
from address
order by PostalCode
limit 300;

-- Q2
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055;

-- Q3
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055 or PostalCode = 83402;

-- Q4
select distinct AddressLine1, City, PostalCode
from address
where PostalCode between 90000 and 92000
limit 300;

-- clean up
drop index postal_index on address;
desc address;