High Performance MySQL chapter 1

A logical view of MySQL server architecture

 

Read/Write Locks
Lock granularity: table locks/row locks

Transactions: ACID
Isolation Levels: Read uncommitted, read committed, repeatable read, serializable .
Read uncommitted permits dirty reads.
Read committed permits nonrepeatable reads. It means you can run the same statement twice in a transaction and see different results.
Repetable read guarantees that any rows a transaction reads will "look the same" in subsqeuent reads within the same transaction, but it still allows phantom reads.
Serializale solves the phantom read problem by forcing transactions to be ordered.


Deadlocks
Deadlocks cannot be broken without rolling back one of the transactions.


Transaction Logging
It helps make transactions more efficient. The storage engine can change its in-memory copy of the data instead of updating the dables on disk each time a change occurs. The storage engine can then write a record of the change to the transaction log. At some later time, a process can update the table on disk. This technique is known as write-ahead logging .


Transactions in MySQL
Autocommit
Implicit and explicit locking


Multiversion Concurrency Control (MVCC )
It allows nonlocking reads, while locking only the necessary records during write operations.
InnoDB implements MVCC by storing with each row two additional and hidden values that record when the row was created and when it was expired (or deleted).
书中简单介绍了InnoDB是如何实现MVCC的
每个transaction的version是它创建的时间
SELECT：只找version不晚于它的记录（保证是在这个事务之前或就是这个事务创建的），并且delete version未定义或者比它大（保证这条记录在事务开始前没被删除）
INSERT：插入的记录的version为该事务的version
DELETE：记录的delete version就是该事务的version
UPDATE：复制一条该记录，新记录的version是事务的version。同时用事务的version作为老记录的delete version。


MySQL's Storage Engines
MyISAM
MyISAM Merge
InnoDB
Memory
Archive
CSV
Federated
Blackhole
NDB Cluster
Falcon
soildDB
PBXT
Media Storage


如何选择合适的存储引擎？我们主要关注以下几点：
Transactions
Concurrency
Backups
Crash recovery
Special features 例如，如果应用依赖于clustered index optimizations，则需要选择InnoDB/solidDB。而只有MyISAM支持full-text search。


一些例子

Logging
我们最关注speed，这时可以考虑MyISAM和Archive。或者PBXT。但如果想对logging结果进行统计分析，则会很慢。
一个解决方案是MySQL的replication feature，clone data onto a slave server，然后在slave上运行查询，这样不影响master上实时的log。
另一个办法是使用Merge Table。

Read-only or read-mostly tables
read far more often than write 考虑用MyISAM，但要考虑万一crash之后数据丢失的风险。InnoDB不一定就比MyISAM慢，要受很多因素影响。

Order processing
transactions required。InnoDB是个好选择，其它transactional的引擎也可以考虑

Stock quotes
如果用户不多，MyISAM很合适。但如果用户很多，都要获取实时最新的数据，这时候可能会有很多客户端同时要读写表，所以要考虑row-level locking或者一个最小化更新的设计。

BBS和论坛
如果用户量很大…………