clustered index updated in-place,and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed. secondary index records do not contain hidden system columns nor are they updated in-place. when a secondary index column is updated,old secondary index records are delete-marked,new records are inserted,and delete-marked
innodb 结构
in memory
1 2 3 4
buffer pool change buffer
log buffer
buffer pool
1 2 3 4 5 6 7
caches tableand indexdata as it is accessed. frequently used data to be processed direct from memeory. 80% of physical memory often assigned to buffer pool.
divided in pages that can potentially hold multiple rows. used out of cache using a variation of the lru algorithm.
buffer pool lru algorithm
1 2
head(new 5/8) age tail(old 3/8)
change buffer
1 2 3 4 5 6
caches changes to secondary index pages when those pages are not in the buffer pool. the buffered changes,which may result from insert,update or delete,are merged later when the pages are loaded into the buffer pool by other read operations.
secondary index are usually nonunique,and insert secondary inidex happen in a relativelay rando order. similarly,deletes and updates may affect secondary index pages that are not adjacently located in an index tree. merging cached changes at a later time,when affected pages are read into thr beffer pool by other operations,avoids substantial random access i/o that,would be required to read secondary index pages into the buffer pool from disk.
apaptive hash index
log buffer
1
the memory area that holds data to be written to the log files on disk.
on disk
1 2 3 4 5 6 7 8 9 10 11 12 13
system tablespace(ibdata1) file-per-table [innodb data, innodb_file_per_table=on doublewrite buffer, t1.ibd t2.ibd change buffer, general tablespace undo logs] ts1.ibd t2.ibd
using auto-increment with replication "lost" auto-increment values and sequence gaps roll back will lost specifying null or 0 for the auto_increment column if a user specifier null or 0 for auto_increment column in an insert. assigning a negative value to the auto_increment column 负数是可分配,但是不会增加 if the auto_increment value becomes larger than the maximum integer for the specified integer type the behavior of the auto-increment mechanism is not defined if the value becomes larger than the maximum integer that can be stored in the specified integer type; gaps in auto-increment values for bulk inserts lock mode(0,1(defualt)) bulk insert without gaps lock mode(2) auto-increment values assigned by "mixed-mode inserts" modifyint auto_increment column values in the middle of a sequence of insert statemens.
innodb auto_increment counter initialization
1 2 3 4 5 6
this counter is stored only in main memory,not on disk. select max(ai_col) from table_name fro update; 超多数字最大值后,会返回最后的最大值,这个是mysql的行为 重启后会使用原来没有保存的值,重新获取ai_col ```` #### indexs
clustered and secondary idexs 每个表都有一个聚簇索引 其他的是非聚簇索引
1
##### physical structure of an inodb index
b+ tree 待续
1 2
##### tablespaces ###### system tablespace
storage area for the innodb data dictionary,the doublewrite buffer,the change buffer,and undo logs.
1
###### file-per-table tablespace
contains data and indexs for a single innodb table,and is stored on the file system in its own data file.
1
###### general tablespace
is a shared innodb tablespace that is created using create tablespace syntax.
1
###### undo tablespace
collections of undo log records that contain infomation about how to undo the latest change by a transation to a clustered index record.
1 2
###### temporary tablespace ##### innodb data dictionary
.frm
1
##### doublewrite buffer
is a storage area where innodb writes apges flushed from the buffer pool before writing the pages to their proper positions in th innodb data files.
1
##### redo log
is a disk-based data structuew used during crash recovery to correct data written by incomplete transactions.
1
##### undo log
is a collection of undo log records associated with a single redo-write transaction.
1 2 3
#### locking and transaction model ##### isolation level
select ... lock in share mode select ... for update
locks set by different sql statements in innodb
1
a locking read,an update,or a delete generally set record locks on every index record that is scanned in the processing of the sql statement.
1 2 3 4 5 6
select ... from is a consistent read,not locks. select ... for update/select ... lock in share mode,locks are acquired for scanned rows,and expected to be released for rows that do not qualify for inclusion in the result set. select ... lock in share mode,set shared next-key lock on all index records the search encouters select ... for update sets an execlusive next-lock on every record the search encounters.however,only an index record lock is required for statements that lock rows using a unique index to search for a unique row. update ... where 同上, 待续