MYSQL 教程:§9, MYSQL表类型

§9, MYSQL表类型

       设计数据库管理系统由很多折衷。一些任务必须用transaction-safe的方法完成，但是这样增加了时间，磁盘，内存的开销。

       表类型又成为storage engines. 它揭示了一些表类型依靠大量单独的源代码来管理自己的caching, indexing, locking, and disk access. 也揭示数据库的根本目标：存储数据。

       transaction or transaction safe。一些查询必须一起执行。比如银行转账，要同时扣一个人钱和给另外一个人加钱。

       InnoDB and BerkeleyDB are transaction safe。The others (ISAM, MyISAM, MERGE, and HEAP) are not.

       We will also cover the special features of MyISAM tables in this chapter, specifically compressed tables and full-text searching。

      

§9.1  ISAM表

       ISAM是历史使用的表类型，在表移植性，支持大表，使用磁盘效率，key的使用等方面有很多缺陷。不推荐使用

        create table asset

(

  assetID int not null,

  description varchar(255)

) type=ISAM;

 

5.0 已经不支持这个类型。

 

§9.2  MYISAM表

create table article (

  articleID int not null auto_increment primary key,

  title varchar(255),

  body text

);

 

后面可以添加) type=MyISAM; 不过默认也是这样的。

MyISAM tables有3种类型：dynamic, static, or compressed. Compressed tables要使用myisampack tool来创建。

固定长度的表自动成为static tables，不固定的成为dynamic tables.含有varchar, text, and blob columns类型的为不固定表。

static table查找快速，易于cache，数据易于恢复。dynamic tables节约空间，但是由于不固定长度，后面附加的内容和前面的未必在一个地方，cache, find, or repair records比较困难。

＊    压缩MyISAM表

    使用myisampack压缩，压缩表是只读的。Myisampack所作的工作包含压缩(Huffman coding)和收缩表的优化，比如转换为更小的数据类型，用枚举代替列等。每个记录是单独压缩的，解压开销并不大。

＊    MyISAM表的全文本查找

全文本查找用于在一块文本中寻找词或字符串。创建举例：

create table article (

  articleID int not null auto_increment primary key,

  title varchar(255),

  body text,

  fulltext (title,body)

);

查找单词'merger'

select title

from article

where match (title,body) against ('merger');

 

查找'merge', 'acquisition', 'acquire', or 'takeover'.中的任意单词。

select title from article

where match (title,body) against ('merge acquisition acquire takeover');

       针对'acquire'和'acquisitions'要单独查询。即没有stemming功能。

以下部分暂不涉及，注意的是表的记录比较多的时候，效率并不高。

 

 

＊    布尔全文本查找

 

The following query will match only records than contain the word 'linux' and the string "Open Source", but not the word 'desktop'. The words 'Java' and 'Oracle' are optional, but when deciding on relevance, finding 'Java' in a record will improve its ranking, whereas finding 'Oracle' will degrade the ranking. The order of words in the search string or the record is not important.

select title

from article

where match (title,body)

      against ('+linux +"Open Source" -desktop Java ~Oracle' IN BOOLEAN MODE);

Table 9.1. Boolean Mode Search Operators
Operator	Meaning
+	This word is compulsory.
-	This word must not appear.
<	This word is less important.
>	This word is more important.
( )	Group words together as a subexpression.
~	This word may appear, but it has a negative effect on ranking.
*	Wildcard suffix. For example, merge will not match merger, but merge* will match both merge and merger. May be used only at the end of a word.
" "	This is a phrase. Matches only exactly the same content in the same order.

 

没有full-text indexes的也可以这样查找，不过速度比较慢。

以下的例子略。

 

§9.3  InnoDB表

       a fast, transaction-safe storage engine.

提供：

事务，见第10章

Row-level locking.其他表除了BDB外，更新时是锁定表的。该表此时不可访问。

支持外键

Consistent nonlocking reads in SELECTs. (The idea for this is borrowed from Oracle.)

 

       InnoDB有自己的配置选项，目录和存储数据的方法。MyISAM中一个table一个文件。InnoDB则是在表空间存储tables and indexes，可能存储于多个文件中，为此大小不受操作系统单个文件大小的限制。不过与MyISAM比要浪费很多空间。

       它的配置部分参见1，12章。

       它也是使用dual-licensing agreement。

       Slashdot (www.slashdot.org), Google (www.google.com), and Yahoo! Finance (http://finance.yahoo.com)等都使用InnoDB。适合处理事务环境下大量数据的高速处理。

       它是世界上最快的transaction-safe systems之一，但是安全性不够。

       InnoDB由InnoBase Oy生产，它的网站：www.innodb.com。

 

§9.4  BerkeleyDB (BDB) Tables

略

 

§9.5  合并 Tables

MyISAM中，由于操作系统限制文件大小，可以使用合并表解决。查询时可以把多个表当作一个表。

Listing 9.1 A MERGE Table Example

create database logs;

use logs;

 

create table log2003Jan

(logid int auto_increment primary key,

logts datetime,

entry char(255));

insert into log2003Jan values

(NULL, '2003-01-01', 'first jan entry');

 

create table log2003Feb

(logid int auto_increment primary key,

logts datetime,

entry char(255));

insert into log2003Feb values

(NULL, '2003-02-01', 'first feb entry');

 

create table log2003Mar

(logid int auto_increment primary key,

logts datetime,

entry char(255));

insert into log2003Mar values

(NULL, '2003-03-01', 'first mar entry');

 

create table logs

(logid int auto_increment primary key,

logts datetime,

entry char(255))

type = merge

union = (log2003Jan, log2003Feb, log2003Mar)

insert_method = last;

 

 

INSERT_METHOD 表从后面的表开始插入。FIRST从前面，NO 表示不允许插入。

 

mysql> select * from logs;

+-------+---------------------+-----------------+

| logid | logts               | entry           |

+-------+---------------------+-----------------+

|     1 | 2003-01-01 00:00:00 | first jan entry |

|     1 | 2003-02-01 00:00:00 | first feb entry |

|     1 | 2003-03-01 00:00:00 | first mar entry |

+-------+---------------------+-----------------+

3 rows in set (0.00 sec)

       由于是3个表，primary key是有可能重复的。

       使用MERGE表的时候，可以查询单个表，但不可以DROP, ALTER, DELETE FROM TABLE, REPAIR, TRUNCATE, OPTIMIZE, or ANALYZE 单表。FLUSH TABLES可以关闭表。可以使用myisampack压缩，比如日志文件。

 

§9.6  HEAP Tables

       存储于内存的表，多用于临时存储。

create table testHeap

(id int not null primary key, 

data char(100))

type=heap

max_rows = 100;

       max_heap_table_size.可限制内存大小

缺陷如下：

·         They don't support AUTO_INCREMENT.

·         They don't support BLOB or TEXT types.

·         HEAP tables cannot use the leftmost prefix of an index to find rows. (If you would like more information about what this means, you can read more about indexing in Chapter 19.)

·         Indexes will be used only to find rows with queries that use the = or <=> operators in the search clause.

§9.7  小结

Summary

MySQL has six table types: ISAM, MyISAM, InnoDB, BDB, MERGE, and HEAP.

 

Only InnoDB and BDB tables are transaction safe.

 

Only MyISAM tables support full-text indexing and searching.

 

ISAM

ISAM had been deprecated and superceded by MyISAM.

 

ISAM tables have a hard size limit of 4GB.

 

ISAM tables are not portable.

 

You can have a maximum of 16 keys per table and a maximum key length of 256 bytes (characters).

 

MyISAM

MyISAM is the default table type. It is very fast, but not transaction safe.

 

MyISAM tables support table compression.

 

The size of MyISAM tables is limited only by the operating system, and this can be worked around with MERGE tables.

 

The data files that store MyISAM tables are portable from system to system.

 

You can have a maximum of 64 keys per table and a maximum key length of 1024 bytes.

 

InnoDB

InnoDB tables are transaction safe.

 

InnoDB supports row-level locking.

 

There is no theoretical maximum table size because tables may be stored in more than one file.

 

InnoDB provides consistent nonlocking reads in SELECT.

 

InnoDB tables are portable from system to system.

 

InnoDB tables take more disk space than MyISAM tables.

 

Foreign keys are supported between InnoDB tables.

 

BDB

Like InnoDB tables, BDB tables are transaction safe. BDB tables are not as widely used with MySQL as InnoDB.

 

BDB supports page-level locking.

 

BDB tables are not portable.

 

MERGE

MERGE tables are used to treat multiple MyISAM tables as a single table, and therefore, the maximum file size limitation is removed from MyISAM tables.

 

HEAP

HEAP tables are stored only in memory and need to be limited in size to avoid running out of memory.

 

Data stored in a HEAP table is volatile and will be lost in the event of a power failure.

 

HEAP tables are super fast, as long as you have enough physical memory to keep them.

 

HEAP tables do not support AUTO_INCREMENT, TEXT, or BLOB.