mysql.note

1. Memory
	a) buffer pool
		* data page
		* index page
		* insert buffer
		* lock info
		* ...
	b) redo log_buffer
		* @@ innodb_log_buffer_size = 8M (default)
		* flush to disk when:
			. master thread flush per second
			. transaction commit flush
			. free size < 1/2 log_buffer flush
	c) additional memory pool
		* frame buffer
		* buffer control block(LRU, lock, wait)
		* when addition memory pool full, use buffer pool

2. Buffer Pool Management
	default page size = 16KB
	a) LRU List
		|_______5/8________|____3/8___|
	  (head)		   (midpoint)   (tail)
		* @@ innodb_old_blocks_pct
		* @@ innodb_old_blocks_time
		* LRU: contains unzip_LRU pages
		* unzip_LRU: buddy algorithm
	b) Free List
	c) Flush List
		* dirty pages in both Flush List and LRU List

3. Checkpoint:
	a) Sharp Checkpoint
	b) Fuzzy Checkpoint
		* Master Thread Checkpoint
		* FLUSH_LRU_LIST Checkpoint
		* Async/Sync Flush Checkpoint
		* Dirty Page too much Checkpoint

4. Master Thread
	a) before InnoDB 1.0.x
		* do per second:
			. [Always] log buffer flush to disk per second, no matter transaction commited or not
			. [Maybe] merge insert buffer, if io frequence < 5 io/s
			. [Maybe] flush 100 dirty pages to disk, if buf_get_modified_ratio_pct >  innodb_max_dirty_pct(90% default)
			. [Maybe] goto background loop, if no user active
		* do per 10 seconds:
			. [Maybe] flush 100 dirty pages to disk, if io frequence < 200 io/s
			. [Always] merge insert buffer
			. [Always] log buffer flush to disk
			. [Always] delete useless undo log
			. [Always] flush 100 dirty pages(if buf_get_modified_ratio_pct > 70%) 
					   or 10 dirty pages(if buf_get_modfied_ratio_pct < 70%) to disk
		* background loop
			. [Always] delete useless undo log
			. [Always] merge insert buffer
			. [Always] goto main loop
			. [Maybe] flush 100 pages

5. InnoDB
	a) Insert Buffer
		* secondary index
		* not unique
		* B+ tree(global unique, ibdata1 share space)
			. inner nodes' search key: space(4 Byte), marker(1B), offset(4B)
			. leaf nodes: search key(9B), metadata(4B), record
		* Insert Buffer Bitmap
		* Merge Insert Buffer happens on:
			. secondary index page load to buffer pool
			. Insert Buffer Bitmap detect secondary index page is full
			. Master Thread
	b) Double Write
		* doublewrite buffer(2MB = 128 * 16K)
		* [1st]doublewrite buffer ---memcpy---> share table space, once 1 MB
		* [2nd]doublewrite buffer ---flush---> .idb(table space file)
		* recover .idb from share table space
	c) Adaptive Hash Index
		* generated from buffer pool's B+ tree leaf
		* build hash requirements:
			. access page in continuous same mode(same query conditions)
			. over 100 times in the same access mode
			. over N times in the same access mode(N = records of page/16)
			. query by equal condition instead of range conditions
	d) Async IO
		* Native AIO(libaio)
	e) Flush Neighbor Page

6. Log
	a) error log
		@@ log_error
	b) slow log
		@@ log_slow_queries = OFF (default)
		@@ long_query_time = 10 (default)
		@@ log_queries_not_using_indexes = ON (default)
		@@ long_query_io
		@@ slow_query_type:
			. 0 -- not use slow log
			. 1 -- use slow log according to time
			. 2 -- use slow log according to io
			. 3 -- use slow log according to time & io
		* mysqldumpslow slow.log
	c) general log
	d) binary log
		update operation log
		* functionality:
			. recovery
			. replication
			. audit
		@@ binlog_format:
			. STATEMENT
			. ROW
			. MIXED

7. redo log
	a) binlog VS redo log
		* binlog contains all db update operations of all engines
		  redo log only belong to innodb's transaction
		* binlog content is logic operation(transaction's detail)
	      redo log content is modifaction of every Page
		* binlog write before transaction commit and write only once
	      redo log write several times during transaction lifetime
	b) redo log format
		* redo_log_type
		* space
		* page_no
		* redo_log_body
	c) @@ innodb_flush_log_at_trx_commit
		* 0 -- do not write redo log to disk when transaction commit
		* 1 -- (default) sync write redo log to disk when transaction commit
		* 2 -- async write redo log to disk when transaction commit

8. Compact Record Format
	a) length(1/2 Byte)
	b) NULL flag(1B)
	c) record header: 3B + 2B(next_recorder)
	d) col1, col2, ...
	e) TransactionID
	f) Roll Pointer
 
9. varchar max size 65535 for all varchar column length sum
	row flow data store in BLOB Page
	if one Page cannot store two rows, using BLOB Page

10. Dynamic Record Format
	20 Byte per row, containing offset to Off Page(real data)

11. char type lenght is character counts, not Byte counts

12. Page
	a) File Header(38 Byte)
	b) Page Header(56 Byte)
	c) Infimun & Supremum Records
		* virtual records for each page
		* infimun < user records < supremum
	d) User Records
	e) Free Space
	f) Page Directory
	g) File Trailer(8 Byte)
		* checksum(4 Byte)
		* FILE_PAGE_LSN(4 Byte)

13. constraint
	a) create
		* > alter table table_name add unique key uk_column_name (column_name);
	b) query
		* > select constraint_name, constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='db name' and table_name='**';
		* > select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='db name';
    c) not null | date type
		* > set sql_mode = 'STRICT_TRANS_TABLES';
	d) foreign key on update | delete
		* CASCADE
			update child table when parent table update
		* SET NULL
			set child table null when parent table update
		* NO ACTION
			raise error when parent table update, unless transaction meets constraints
		* RESTRICT(default)
			raise error when parent table update

14. partition
	a) RANGE
		successive range
	b) LIST
		discrete range
	c) HASH
		user defined hash function
	d) KEY
		mysql defined hash function
	e) belong to unique key
	f) partition conditions:
		* interger
	g) COLUMNS
		* all interger
		* date, datetime
		* char, varchar, binary
		* [not support] blob, text
	h) exchange data between partition and non-partition table

15. DB application
	a) OLTP
	b) OLAP

16. InnoDB index
	a) B+tree index
		* clustered index
			. primary key
			. index with data
		* secondary index
			. Fast Index Create(FIC)
				S Lock
			. Online Schema Change(OSC)
			. Online DDL
				secondary index create/drop
				modify auto_increase
				foreign key create/drop
				rename column
		* split
			. if insert randomly, split in the middle of page
			. if insert in direction, split in current page tail
		* union index
		* cover index
			. > select primary key keyname, union_key_name from table
		* index choose
			. > force index
			. > use index
	b) Full-text Search
		* inverted index
			. inverted file index
			. full inverted index
		* FTS Index Cache
			. RB Tree
	c) Hash index
		* Key = space_id<<20 + space_id + offset
		* hash link

17. Cardinality
	a) update strategy
		* 1/16 columns of table is updated
		* stat_modified_counter> 2 000 000 000
	b) update method
		* A = all leaf counts
		* randomly get 8 pages, count distinct keys in each page(P1, P2, ..., P8)
		* Cardinality = (P1+P2+...+P8)*A/8

18. optimize index
	a) Multi-Range Read
		* secondary index query result(key-value) ---> cache
		* sort by RowID
		* fetch data file in RowID order
	b) Index Condition Pushdown(ICP)
		* index query with where condition in engine layer
		* where condition partially overlap with index

19. LOCK
	a) latch
		* mutex
		* rwlock
	b) lock
		* target is transaction
		* table lock
			. IS Lock
			. IX Lock
			. AUTO-INC Locking
			. general table lock
		* row lock
			. Type
				- S Lock
				- X Lock
			. algorithm
				- Record Lock
				- Gap Lock
				- Next-Key Lock: Record Lock + Gap Lock
				  solve Phantom Problem
			. turn off Gap Lock
				- READ COMMITTED
				- innodb_locks_unsafe_for_binlog = 1
			. Next-Key Lock ---> Record Lock happens on
				- query unique index
		* choose table lock
			. read
			. read + write(unique index)
			. more select + insert, less delete + udpate
			. group by
		* not choose row lock
			. version
			. copy on demand
			. application level locks

20. Transaction Isolation Level
	a) READ COMMITTED
		* consistent nonlocking read
			. MVCC
		* newest data version
		* locking read
			. record lock
	b) REPEATABLE READ(default)
		* consistent nonlocking read
		* version on transaction beginning
		* locking read
			. next-key lock
			. > select for update (X Lock)
			. > select lock in share mode (S Lock)
	c) READ UNCOMMITTED
	d) SERIALIZABLE

21. transaction block for lock and raise timeout exception, uncommitted update impact current version, so commit or rollback explicitly
	* @@ innodb_lock_wait_timeout
	innodb do not rollback major exception, except dead lock

22. avoid dead lock
	* wait timeout
	* wait-for graph

23. Three normal form for relation model[from wiki]
	* A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.
	* A table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table.
	* Third normal form is a normal form that is used in normalizing a database design to reduce the duplication of data and ensure referential integrity by ensuring that (1) the entity is in second normal form, and (2) all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes.

24. Transaction
	* type
		. Flat Transactions
		. Flat Transactions with Savepoints
		. Chained Transactions
		. Nested Transactions
		. Distributed Transactions
	* Mechanism
		. redo log
		. undo log
		. binlog
	* SQL
		. > start transaction | begin
		. > commit
		. > rollback
		. > ...

25. Optimize DB
	* architecture
	* hardware
	* os
	* DBMS
		. thread count
		. buffer pool
		. flush disk
		. LRU
		. lock timeout
		. compact
		. connection pool
	* app
		. logic
		. SQL(CRUD)