第一章、mysql体系结构和存储引擎
1.1、数据库和实例的区别
数据库：物理操作系统或其他形式文件类型的集合。在mysql下数据库文件可以是frm，myd，myi，ibd结尾的文件。
数据库实例：由数据库后台进程/线程以及一个共享内存区组成。
mysql数据库是但进程多线程的程序。
1.2、mysql的体系结构
mysql由连接池组件、管理服务和工具组件、sql接口组建、查询分析器组件、优化器组件、缓存组件、插件是存储引擎、物理文件。
示意图见书：P4.
1.3、mysql存储引擎
1.3.1、innodb存储引擎，特点支持外键、行锁、非锁定读(默认情况下读取不会产生锁)、mysql-4.1开始支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC来获取高并发性，并且实现sql标准的4种隔离级别，同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。
1.3.2、myisam存储引擎，myisam特点是不支持事物，适合olap应用，myisam表由MYD和MYI组成。mysql-5.0版本之前，myisam默认支持的表大小为4G，从mysql-5.0以后，myisam默认支持256T的表单数据。myisam只缓存索引数据，mysql-5.1.23版本之前无论32、64位操作系统环境下，缓存索引的缓冲区最大只能4G，在之后的版本中，64位系统可以支持大于4G的索引缓冲区。
1.3.3、NDB存储引擎，2003年mysql从索爱公司收购的NDB引擎，NDB的特点是数据放在内存中，mysql-5.1版本开始可以将非索引数据放到磁盘上。NDB之前的缺陷是join查询是mysql数据库层完成的，而不是存储引擎完成的，复杂的join查询需要巨大的网络开销，速度很慢。当前mysql cluster7.2版本中已经解决此问题，join查询效率提高了70倍。
1.3.4、memeory存储引擎，将数据放到内存中，默认使用hash索引，不支持text和blob类型，varchara是按照char的方式来存储的。mysql数据库使用memory存储引擎作为临时表还存储中间结果集(intermediate result)，如果中间集结果大于memorg表的容量设置，又或者中间结果集包含text和blog列类型字段，则mysql会把他们转换到myisam存储引擎表而放到磁盘上，会对查询产生性能影响。
1.3.5、archive存储引擎，压缩能力较强，主要用于归档存储。
1.3.6、federated存储引擎，不存储数据，他指向一台远程mysql数据库上的表。
1.3.7、maria存储引擎，myisam的后续版本,支持缓存数据和索引，行锁设计，支持mvcc，支持事务和非事务安全的选项，以及更好的BLOG字符类型的处理性能。
1.3.8、其他存储引擎，sphinx用于全文索引，infobright用于数据仓库
1.4、各引擎之间的对比
可以通过 show engines查看mysql对存储引擎的支持情况。
1.5、连接mysql
第二章、innodb存储引擎
2.1、第一章已经大概介绍innodb的特点
2.2、innodb引擎架构
维护所有进程/线程需要访问的多个内部数据结构
缓存磁盘上的数据，方便快速的读取，并且在对磁盘文件的数据进行修改之前在这里缓存
重做日志缓存
……….
后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据，此外、将已经修改的数据文件刷新到磁盘文件，同时保证数据库发生异常情况下innodb能恢复到正常运行状态。
2.2.1、后台线程
innodb存储引擎后台有7个线程，—–4个IO线程，1个master thread，一个lock监控线程，一个错误监控线程。(当前5.5版本，默认IO线程是18个，8个读，8个写,一个insert buffer thread、一个log thread，加上master线程、lock监控线程、错误监控线程一共是21个)。可以通过 show variables like ‘innodb_%io_threads’\G和show engine innodb status\G来查看相关信息。
2.2.2、内存
innodb存储引擎内存由以下三个部分组成：缓冲池(buffer pool),重做日志缓存(redo log buffer),额外的内存池(additional memory pool)。分别使用innodb_buffer_pool_size和innodb_log_buffer_size、innodb_additional_mem_pool_size的大小决定。可以使用 show engine innodb status来查看innodb_buffer_pool的事情情况。在BUFFER POOL AND MEMORY里可以看到存储引擎缓存池的使用情况，buffer pool size表明一共有多少缓冲帧(buffer frame),每个buffer frame为16K.buffer pool size表明一共有多少缓冲帧、free buffers表示当前空闲的缓冲帧、database pages表示已经使用的缓存帧、modified db pages表示脏页的数量。
innodb_buffer_pool_size：具体看，缓冲池中的数据库类型有：索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hash index)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)
示意图见书P24.
注意：在32位windows下innodb_buffer_pool_size可以通过开启AWE功能突破内存限制，但是会自动禁用自适应hash(adaptive hash index).
innodb_log_buffer_size：一般情况下innodb会每秒刷新log buffer到硬盘，因此保证每秒产生的事务梁在这个缓存大小之内就可以了。
innodb_additional_mem_pool_size：当你的innodb_buffer_pool_size很大的时候，这个值也需要扩大。
2.3、master thread
2.3.1、master thread源码分析
|
void masteor_thread(){ loop: //主循环,间隔10s for(int i=0;i thread_sleep(1) //sleep 1 s do log buffer flush to disk //每秒都要刷新日志缓存到硬盘 if(last_one_second_iosinnodb_max_dirty_pages_pct){//如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新100个脏页到硬盘 do buffer pool flush 100 dirty page } if(no user activity){ //如果当前没有活跃用户或者数据库关闭时，就跳入background loop goto backgroud loop } sleep 1 second if necessary } //每10秒执行的操作 if(last_ten_second_ios70%){ //如果缓存中脏页比例大于70%，就刷新100个脏页到硬盘，否则只刷新10个 do buffer pool flush 100 dirty page }else{ buffer pool flush 10 dirty page } do fuzzy checkpoint //产生一个检查点 goto loop: background loop: //backupgroud循环 do full purge //总是删除bufferpool中无用的undo页 do merge 20 insert buffer //总是合并20哥插入缓存 if not idle：//如果不空闲，就跳回主循环，如果空闲就跳入flush loop goto loop： else goto flush loop flush loop: do buffer pool flush 100 dirty page //总是刷新100个脏页到硬盘，直到缓存中的脏页比例小于innodb_max_dirty_pages_pct if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){ goto flush loop } goto suspend loop //完成刷新脏页的任务后，跳入suspend loop suspend loop: suspend_thread() //将master线程挂起，等待事件激活 waiting event goto loop: } |
可以通过show engine innodb status\G命令查看BACKGROUND THREAD段关于mster thread的信息。这里主循环执行了2857554次，每秒的thread执行了2857350次，每十秒的tread执行了285310次，6239次background thread和6238次flush thread。这里的每秒和每十秒的比例差不多，证明服务器压力不是很大。
|
----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2857554 1_second, 2857350 sleeps, 285310 10_second, 6239 background, 6238 flush srv_master_thread log flush and writes: 3040886 |
2.3.2、master thread的潜在问题
1、由于硬件的发展，现在的硬件性能已经提高了很多，如果innodb每秒最大刷新100个脏页，那么效率会很低，为了解决这个问题，innodb plugin提供了一个参数innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值是200，规则如下：
在合并插入缓存时，合并插入缓存的数量为innodb_io_capacity的5%。
在从缓冲区刷新脏页时，啥新脏页的数量为innodb_io_capacity。
如果你使用的是ssd或者raid10，你可以调高这个参数，知道符合你的硬件。
2、关于innodb_max_dirty_pages_pct值的争议，如果值过大，内存也很大或者服务器压力很大，那么效率很降低，如果设置的值过小，那么硬盘的压力会增加，建议是在75-80.并且innodb plugin引进了innodb_adaptive_flushng(自适应的刷新)，该值影响每秒刷新脏页的数量。他会通过innodb_flush_get_desired_flush_rate的函数判断需要刷新脏页最合适的量，这个函数是通过重做日志产生的速度来判断的。
通过上述改进，innodb master thread的为代码如下：
|
void masteor_thread(){ loop: //主循环,间隔10s for(int i=0;i thread_sleep(1) //sleep 1 s do log buffer flush to disk //每秒都要刷新日志缓存到硬盘 if(last_one_second_iosinnodb_max_dirty_pages_pct){//如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新innodb_io_capacity个脏页到硬盘 do buffer pool flush 100% innodb_io_capacity dirty page } if(no user activity){ //如果当前没有活跃用户或者数据库关闭时，就跳入background loop goto backgroud loop } sleep 1 second if necessary } //每10秒执行的操作 if(last_ten_second_ios< innodb_io_capacity){ //如果最后10s内IO小于innodb_io_capacity次，那么就刷新innodb_io_capacity个脏页到硬盘 do buffer pool flush 100% * innodb_io_capacity dirty page } do merge at most 5 insert buffer //总是合并最多5个插入缓存 do log buffer flush to disk //总是将日志缓存刷新到磁盘 do full purge //总是删除buffer_pool中无用的undo页，一次最多20个 if(buf_get_modified_ratio_pct>70%){ //如果缓存中脏页比例大于70%，就刷新innodb_io_capacity个脏页到硬盘，否则只刷新10%*innodb_io_capacity个 do buffer pool flush 100% * innodb_io_capacity dirty page }else{ buffer pool flush 10% * innodb_io_capacity dirty page } do fuzzy checkpoint //产生一个检查点 goto loop: background loop: //backupgroud循环 do full purge //总是删除bufferpool中无用的undo页 do merge 100% * innodb_io_capacity insert buffer //总是合并innodb_io_capacity个插入缓存 if not idle：//如果不空闲，就跳回主循环，如果空闲就跳入flush loop goto loop： else goto flush loop flush loop: do buffer pool flush 100% * innodb_io_capacity dirty page //总是刷新innodb_io_capacity个脏页到硬盘，直到缓存中的脏页比例小于innodb_max_dirty_pages_pct if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){ goto flush loop } goto suspend loop //完成刷新脏页的任务后，跳入suspend loop suspend loop: suspend_thread() //将master线程挂起，等待事件激活 waiting event goto loop: } |
2.4、关键特性,为innodb提高性能的技术
2.4.1、插入缓存
当一个表有非聚集索引时，对于非聚集索引的叶子节点的插入不是顺序的，这时候需要离散的访问非聚集索引页，性能就在这里降低了，这是由于b+树的原理导致的。插入缓存就是用来解决这个问题的。
对于非聚集索引的插入和更新操作，不是每一次都直接插入索引页，而是先判断插入的非聚集索引页是否在缓存中，如果在就直接插入，如果不在就放入到一个插入缓冲区中，好似欺骗数据库这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。
插入缓存的使用需要满足以下两个条件(也就是非唯一的辅助索引)：
索引是辅助索引
索引不是唯一的
可以通过show engine innodb status\G查看INSERT BUFFER AND ADAPTIVE HASH INDEX段的信息来了解插入缓存的使用情况。
|
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 30, seg size 32, 8069 merges merged operations: insert 6063, delete mark 2347, delete 73 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 8850419, node heap has 1562 buffer(s) 34.71 hash searches/s, 127.48 non-hash searches/s --- LOG --- Log sequence number 94417057034 Log flushed up to 94417055332 Last checkpoint at 94415342503 Max checkpoint age 650641675 Checkpoint age target 630309123 Modified age 1714531 Checkpoint age 1714531 0 pending log writes, 0 pending chkp writes 3724702 log i/o's done, 1.39 log i/o's/second |
seg size显示了当前插入缓冲的大小为32*16K，size代表使用了的插入缓冲，free list len代表空闲的插入缓冲。等待完善标记
2.4.2、两次写
两次写给innodb带来的是可靠性，主要用来解决部分写失败(partial page write)。doublewrite有两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另外一部分就是物理磁盘上的共享表空间中联系的128个页，即两个区，大小同样为2M。当缓冲池的张也刷新时，并不直接写硬盘，而是回通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次写，每次写入1M到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘。以下命令可以查看doublewrite的使用情况。
|
mysql> show global status like 'innodb_dblwr%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Innodb_dblwr_pages_written | 60511554 | | Innodb_dblwr_writes | 828734 | +----------------------------+----------+ |
这台DB doublewrite一共写了60511554个页，但实际写入次数为828734，60511554/828734=73。说明这台DB的压力一般。slave上可以通过设置skip_innodb_doublewrite参数关闭两次写功能来提高性能，但是master上一定要开启此功能，保证数据安全。
2.4.3、自适应哈西索引
由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了 adaptive hash index功能，innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。可以通过show engine innodb status\G来查看自适应哈西索引的使用情况。可以使用innodb_adaptive_hash_index来禁用和启用hash索引，默认开启。
2.5、启动、关闭、恢复
在关闭数据库时，innodb_fast_shutdown影响innodb存储引擎的行为。该参数有0、1、2三个参数。0代表mysql关闭时，innodb需要完成所有的full purge和merge insert buffer操作。1是默认值，表示不需要完成上述的full purge和merge insert buffer操作，但是缓冲池的一些数据脏页还是回刷新到磁盘。2表示不完成full purge和merge insert buffer操作，也不将缓冲池中的脏页回写磁盘，而是将日志都写入日志文件。
innodb_force_recovery影响整个innodb存储引擎的恢复状况，该值默认为0，表示当需要恢复时，需要执行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，mysql数据库可能宕机，并把错误写入错误日志中。
innodb_force_recovery还可以设置1-6这个几个值。大的数字包含小数字的影响。
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO)不知行事务回滚操作
4(SRV_FORCE_NO_IBUF_MERGE)不执行插入缓冲的合并操作
5(SRV_FORCE_NO_UNDO_LOG_SCAN)不查看撤销日志undo log。innodb存储引擎会将未提交的事务视为已提交
6(SRV_FORCE_NO_LOG_REDO)不执行前滚的操作
2.6、innodb plugin = 新版本的innodb存储引擎
mysql-5.1使用了插件式的架构。在mysql-5.1.38前，安装innodb plugin必须下载plugin的文件，再进行一系列的安装。
第三章、文件
3.1参数文件
my.cnf文件。
3.1.1、什么是参数
键=值
3.1.2、参数类型
分为global和session
3.2、日志文件
3.2.1、错误日志
show global variables like ‘log_error’;
3.2.2、慢查询日志
show global variables like ‘%long%’;
show global variables like ‘log_slow_queries’; //mysql-5.1.2开始支持微妙级慢查询。
show global variables like ‘log_queryies_not_using_indexs’; //未使用索引的查询
mysql-5.1开始可以将慢查询放入log_output表中。
3.2.3、查询日志
mysql-5.1开始可以将查需日志放入general_log表中
3.2.4、二进制日志
主要功能
恢复 基于时间点的恢复
复制
以下参数影响二进制日志
max_binlog_size
binlog_cache_size
sync_binlog
binlog-do-db
binlog-ignore-db
log-slave-update
binlog_format
将binlog_format设置成row，可以支持事务隔离级别为READ COMMITTED，以获得更好的并发性。
在使用MIXED格式下，mysql采用STATEMENT格式进行二进制日志文件的记录，但是有一些情况下会使用ROW格式，可能的情况如下：
1、表的存储引擎为NDB，这个时候DML操作都会以ROW格式记录
2、使用了uuid()、user(),current_user(),found_rows(),row_count(),等不确定函数。
3、使用了insert delay语句
4、使用了用户定于的函数(UDF)
5、使用了临时表(temporary table)
注意：针对系统库mysql里面的表发生变化的处理规则如下：
1、如果采用insert，update，delete直接操作表，则日志根据binlog_format设定的格式记录。
2、如果使用grant，revoke，set password等DCL语句，那么无论如何都会使用SBR模式记录。
3、blockhole引擎不支持row格式，ndb引擎不支持statement格式。
3.3、套件字文件
show variables likea ‘socket’;
3.4、pid文件
show variables like ‘pid_file’;
3.5、表结构定义文件
*.frm
3.6、innodb引擎文件
重做日志文件，表空间文件
3.6.1、表空间文件
默认会有一个大小10M的tablespace file，名字叫ibdata1.可以通过innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend 来配置。这里使用了两个文件来组成的表空间，如果他们不在一个磁盘上的话，对IO性能会有所优化。
还有一个innodb_file_per_table，这个参数会让每个innodb引擎的表都单独产生一个表空间。
注意：单独表空间只保存该表的数据，索引和插入缓冲等信息，其余信息还是保存在共享表空间的(undo页，系统事务信息，二次写)。
3.6.2、重做日志文件(redo log)
redo log是在实例或者介质失败的时候，用来保证数据完整性。
每个innodb存储引擎至少有一个重做日志组,每个重做日志文件组下至少又2个重做日志文件，如默认的ib_logfile0、ib_logfile1.为了得到更高的可靠性，你可以设置多个重做镜像日志组(mirrored log groups)。
innodb_log_file_size //指定重做日志文件的大小
innodb_log_files_in_group //指定重做日志组中文件的数量，默认为2
innodb_mirrored_log_groups //指定日志镜像文件组的数量，默认为1
innodb_log_group_home_dir //指定了日志文件组所在的路径
还有一个很重要的参数与redo log相关，那就是innodb_flush_log_at_trx_commit的值，对innodb的性能影响很大。他有0，1，2三个值，0代表提交事务时，并不同步写redo log，而是等master threas每秒写。1代表commit的时候就将redo log缓存写入磁盘，2代表commit的时候将redo log缓存异步的写入磁盘。