Old notes about Engine in High performance MySQL 1/e

很久以前寫在 Wiki 上的, 自己都忘了有寫過, 補貼到這增加日後回憶的機會。

Feature

• MyISAM: query fast, no transactino
• BDB: medium (BDB is in MySQL-Max, not in the default installation)
• InnoDB: good transaction

Locking

• MyISAM: lock a whole file
• BDB: lock a page (8K)
• InnoDB: lock a row

Index

• UNIQUE has the same efforts as INDEX
• the index order is important: e.g. index (first_name, last_name) means that selecting by last_name won't have effects of index
• MyISAM:
• B-Tree or R-Tree (spatial index, since v4.1)
• has FullText Index
• the indexes and data are stored in separated files
• index = (index column, row offset)
• Primary key = NOT NULL + UNIQUE + INDEX, which is not necessary
• R-Tree: http://jeremy.zawodny.com/blog/archives/000418.html
• the standard of spatial query syntax: http://www.opengeospatial.org/
• FullText Index: word base, so query partial words such as ”%est” can't take the advantage
• Heap: Hash, B-Tree
• BDB
• B-Tree only, although BDB should use hash tables in common sense
• PK is necessary, if there is not, MySQL will add a hidden one
• InnoDB:
• B-Tree only
• PK is necessary, if there is not, MySQL will add a hidden one
• clustered index, that is, the indexes and data are stored in the same file, and they sorted by PK
• called “index-organized table” in Oracle
• the data themselves are ordered by PK and the second index (if exists) stores PK instead of row offset
• PK can be looked up very fast since it only takes one query to fetch the data instead of two queries
• the efficiency of the second index is worse than the one of PK (while in MyISAM, PK has equivalent efficiency as INDEX)
• updating PK is slow