1、innodb引擎概述 2、innodb引擎版本及特性 2.1)老版本innodb 2.2)1.0版本innodb 2.3)1.1版本innodb 2.4)1.2版本innodb 3、innodb体系架构 3.1)后台线程 3.1.1)master thread 3.1.2)io thread 3.1.3)purge thread 3.1.4)page cleaner thread 3.2)内存 3.2.1)缓冲池(innodb_buffer_pool_size) 3.2.2)LRU list、Free list、Flush list 3.3)重做日志缓冲(redolog_buffer) 3.4)额外的缓冲池(innodb_additional_mem_pool_size)
4、checkPoint技术 专业术语: WAL:write ahead log LSN:log sequence Number 目的: 缩短数据库的恢复时间 缓冲池不够用时将脏页刷新到磁盘 重做日志不可用时刷新脏页 LSN: 每个数据页 redo log checkPoint checkPoint分类: sharp checkPoint fazzy checkPoint fazzy checkPoint种类: Master Thread CheckPoint FLUSH_LRU_LIST Checkpoint Async/sync Flush CheckPoint Dirty Page too much CheckPoint 公式: checkPoint_age = redo_lsn - checkPoint_lsn async_water_mark = 75% * total_redo_log_file_size sync_water_mark = 90% * total_redo_log_file_size 场景: checkPoint_age < async_water_mark async_water_mark < checkPoint_age < sync_water_mark checkPoint_age > sync_water_mark
5、master thread工作方式 5.1)1.0版本之前 5.2)1.2版本之前 5.3)1.2版本 6、innodb关键特性 6.1)插入缓冲(insert buffer) 6.2)两次写(double write) 6.3)自适应hash索引(adaptive hash index) 6.4)异步Io(async IO) 6.5)刷新临接页(flush neighbor page)
7、mysql的启动、关闭和恢复 innodb_fast_shutdown 0 1 2 innodb_force_recovery 1:SRV_FORCE_IGNORE_CORRUPT 2:SRV_FORCE_NO_BACKGROUND 3:SRV_FORCE_NO_TRX_UNDO 4:SRV_FORCE_NO_IBUF_MERGE 5:SRV_FORCE_NO_UNDO_LOG_SCAN 6:SRV_FORCE_NO_LOG_REDO
2.3 2.3.2