第一章、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 statusG来查看相关信息。

　　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 statusG命令查看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 statusG查看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 statusG来查看自适应哈西索引的使用情况。可以使用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缓存异步的写入磁盘。