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.
• Example: nearline service by Google.
• Relational models: tuples and relations -> File systems: records and files.
• Principle of locality:
  1. More frequently used data stored in memory of higher level.
  2. 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.

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.

Physical Structures:

• A tuple can be a virtual 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 or direct access files.
• Examples of important DB file structures:
  • B+-tree: the primary key is used to navigate through an tree index structure to reach linked terminal nodes that store records.
    • 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.
    • Fast random access through the primary key: O(lg (n/B)).
  • Hashing: an address is computed from the primary key for storage:
    • Fast random access through the primary key: O(1).
    • Slow sequential access through the primary key.
  • R-Tree: for spatial data.
• DBMS may allow you to select the physical structures, sometimes known as the database engines.

2. Indexing

• 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.

MySQL Indexes

• In MySQL, indices can be created when a table is created: syntax.

Example:

The optional index clause:

{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...

or

{FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...

and

index_col_name: col_name [(length)] [ASC | DESC]

index_type: USING {BTREE | HASH}

index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'

Example:

Full text search in MySQL: functions.

In Sakila, we have two tables: film and film_text, in which film_text has full indexing:

CREATE TABLE film_text (
  film_id SMALLINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY  (film_id),
  FULLTEXT KEY idx_title_description (title,description)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

To search for a film that contains the keyword "teacher" without using full text search.

select film_id, title, description
from film
where description like '%teacher%';

Possible problems:

• Slow
• Relevance

Using full text indexing and searching:

select MATCH(title,description) AGAINST('teacher') AS score, film_id, title, description
from film
where MATCH(title,description) AGAINST('teacher')
order BY score DESC;

results in:

ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

The correct way:

select MATCH(title,description) AGAINST('teacher') AS score, film_id, title, description
from film_text
where MATCH(title,description) AGAINST('teacher')
order BY score DESC;
 

Can you guess the algorithm of MySQL used to calculate scores from the results of the query above?

• There is also a separate create index statement: syntax.

Example: Using AdventureWorks, a small data warehouse example created by Microsoft.

-- 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;