2010年3月2日 星期二

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

2 則留言:

  1. 這故事告訴我們, 過期的筆記還是就讓它過期吧 XD

    回覆刪除

在 Fedora 下裝 id-utils

Fedora 似乎因為執行檔撞名,而沒有提供 id-utils 的套件 ,但這是使用 gj 的必要套件,只好自己編。從官網抓好 tarball ,解開來編譯 (./configure && make)就是了。 但編譯後會遇到錯誤: ./stdio.h:10...